You are the manager at K&B Inc. You have been told that the company

Case Assignment

You are the manager at K&B Inc. You have been told that the company has an opportunity to expand into a new market, but will need to make a major investment to do it. Sara Harris, Division Vice-President, ask you to evaluate the different opportunities and complete a report for her including your recommendation on which expansions to do (using the methods discussed in the course for capital budgeting). The table below contains the information for each project. Assume all the information given to you is accurate and all three expansions have the same risk.

Options for capital structure (determine the WACC and choose the best capital structure for the project – Chapter 10):
1. 35% debt at 10% with tax-rate of 40%; no preferred stock; 65% common stock at 15% or
2. 50% debt at 11% with tax-rate of 40%; no preferred stock; 50% common stock at 15%.

Total available funds the company can raise for the expansion(s) is $10,000,000.

The expansions cash flows are as follows:
Expansion Florida Georgia Alabama
Initial Cost -10,000,000 -10,000,000 -10,000,000
Year 1 Cash Flow 1,690,000 3,850,000 0
Year 2 Cash Flow 1,690,000 3,000,000 0
Year 3 Cash Flow 1,690,000 2,500,000 725,000
Year 4 Cash Flow 1,690,000 2,000,000 800,000
Year 5 Cash Flow 1,690,000 1,000,000 900,000
Year 6 Cash Flow 1,690,000 700,000 2,000,000
Year 7 Cash Flow 1,690,000 0 3,000,000
Year 8 Cash Flow 1,690,000 0 4,000,000
Year 9 Cash Flow 1,690,000 0 5,000,000
Year 10 Cash Flow 1,690,000 0 6,000,000

After your evaluation, write a memo (in word) to Sara Harris indicating your recommendation. In the memo, you should indicate the rank of the projects, which projects are viable, then you should indicate which project(s) you recommend.

The memo is required to be in proper business memo style.
You should submit the memo and your excel file to blackboard. (I have to see the formulas – not just the answers to be given full credit.
Reminder: +5 pts bonus if you can calculate the discounted payback for all three states (I have to see the formulas in Excel – not just the answers)

Grading Rubric:
Action Grading Rubric Total
NPV –
Evaluation Technique Formula correctly entered into Excel for each state
(6pts total / 2 per state) Answer correct for each state (3 pts total / 1 per state)
IRR –
Evaluation Technique Formula correctly entered into Excel for each state
(6pts total / 2 per state) Answer correct for each state (3 pts total / 1 per state)
Payback –
Evaluation Technique Formula correctly entered into Excel for each state
(6pts total / 2 per state) Answer correct for each state (3 pts total / 1 per state)
Excel Spreadsheet Professionally formatted and easy to follow the evaluation; Setup to print in a professional and neat manner (4pts)
WACC –
Capital Structure Formula Correctly entered in Excel (2pts) Answer correct (1pt) Choose correct Capital Structure based off of student’s calculated numbers (1pts)
Memo Correct Recommendation (clearly identified in the Memo) based off of the student’s numbers and the correct Evaluation Technique(s) used to determine the recommendation – (5 pts) Memo written clearly and concisely (5 pts) Professional Formatted, Proofread, and in correct memo format (5pts)
Total Grade

Calculating Net Present Value
Suppose you want to calculate the net present value for the following problem: You are considering an investment in a machine that costs $100,000 and the additional cash inflows (net income + depreciation) from the machine will be $40,000, $ 50,000, and $60,000 over the next three years. The firm’s cost of capital is 16% (This is the WACC).
Suppose you enter the data in an Excel spreadsheet as follows:
-100,000 in cell C1
40,000 in cell C2
50,000 in cell C3
60,000 in cell C4
To calculate the net present value, go to the Function Wizard. The function you are going to use is in the function category of FINANCIAL and is NPV. You will have to provide the following information:
RATE: .16
VALUE 1: C2:C4
For rate, enter the cost of capital, .16
For VALUE 1 enter the values that get discounted, C2:C4 Do not enter the initial cost (C1) since it does not get discounted. The value you will get from Excel for the above problem is 110,080.36. To get the net present value, you have to subtract the 100,000 (remember that adding a negative number is the same as subtracting) from the 110,080.36 and your answer is: 10,080.36.
Calculating Internal Rate of Return (IRR)
Go to the function wizard and get the function IRR. You will have to provide the following information:
VALUES: C1:C4
GUESS: (leave Blank)
For Values, enter all the flows, including the cost. The cost should be a negative number.

For Guess: It is not required. The reason you may use the guess is because there may be several IRR’s. You learned why in class and is a weakness of IRR.
Make sure that you get the answer to two decimal places.