FIN301Case4TemplateYourLastName.xlsx
Question 1
| Trident University | ||
| FIN301: Principles of Finance | ||
| Module 4: Case Template | ||
| FILL IN ALL CELLS THAT ARE HIGHLIGHTED IN YELLOW | ||
| Please remember to save this file with your last name in the file name. For example: FIN301 Module 4 Case Template, Doe.doc | ||
| Name: | ||
| QUESTION 1: The table below gives the initial investment (the negative numbers at “Year 0”) for two projects. Compute the payback period, the NPV, and the IRR using Excel. Then rank the two projects based on each of these three criteria, and discuss which projects should be funded based on your computations. | ||
| Firm Cost of Capital: | 11% | |
| Year | Project A | Project B |
| 0 | -100,000 | -150,000 |
| 1 | 25,000 | 30,000 |
| 2 | 25,000 | 30,000 |
| 3 | 25,000 | 90,000 |
| 4 | 25,000 | 20,000 |
| 5 | 25,000 | 20,000 |
| 6 | 25,000 | 20,000 |
| PAYBACK PERIOD = | ||
| NPV = | ||
| IRR = | ||
| RANK THE TWO PROJECTS BASED ON THESE THREE CRITERIA. | ||
| RANK BASED ON PAYBACK PERIOD | ||
| PROJECT A | ||
| PROJECT B | ||
| RANK BASED ON NPV | ||
| PROJECT A | ||
| PROJECT B | ||
| RANK BASED ON IRR | ||
| PROJECT A | ||
| PROJECT B | ||
| WHICH PROJECTS SHOULD BE FUNDED BASED ON YOUR CALCULATIONS? | ||
| ANSWER: | ||
| DISCUSS YOUR REASONING FOR YOUR ANSWER ABOVE. WHY SHOULD THE PROJECT(S) BE FUNDED? | ||
| ANSWER: |
NOTE: YOU CAN USE THE EXCEL FORMULA FOR NPV. HELP IN USING THIS FORMULA IS IN THE MODULE 2 CASE TEMPLATE. THE TAB IS LABELED "Help for Multiple Cash Flows Q3."
NOTE: YOU CAN USE THE EXCEL FORMULA FOR IRR. HELP IN USING THIS FORMULA IS IN THE TAB LABELED "Help for IRR Q1" IN THIS WORKBOOK.
PROJECT A
PROJECT B
1st
1st
2nd
2nd
1st
1st
2nd
2nd
1st
1st
2nd
2nd
Question 2
| a. Calculate the NPV for both factories and for both scenarios (rainy versus sunny). What is the range of NPV for each factory based on your scenario analysis? | |||
| FACTORY A | |||
| NPV FOR FACTORY A IF SUNNY = | |||
| NPV FOR FACTORY A IF RAINY = | |||
| FACTORY B | |||
| NPV FOR FACTORY B IF SUNNY = | |||
| NPV FOR FACTORY B IF RAINY = | |||
| THE RANGE FOR FACTOR A IS = | 0 | AND | 0 |
| THE RANGE FOR FACTOR B IS = | 0 | AND | 0 |
| b1. Based on your answer to a) above, do you think ACME should use the same discount rate of 9% for each factory? | |||
| ANSWER: | |||
| b2. Should they use a risk-adjusted discount rate (RADR)? | |||
| ANSWER: | |||
| b3. If you answered YES above, which factory should have a higher RADR? | |||
| ANSWER: | |||
| b4. Explain your answer to b2 and b3. Use references from the background readings. | |||
| ANSWER: |
QUESTION 2: The ACME Umbrella Company is deciding between two different umbrella factories. Both factories will cost $500,000 to get started. However, the cash flows for each factory will depend on whether the next five years are rainier than average or sunnier than average. Factory A will have cash flows of $130,000 per year for the next five years if the weather is sunnier than average. But if it is rainier than average the cash flows will be $150,000 per year for the next five years. Factory B will have cash flows of $100,000 per year for the next five years if it is sunnier than average, but if it is rainier than average it will have cash flows of $200,000 per year. ACME has a cost of capital of 9%. Based on this information, answer the following:
NOTE: USE THE EXCEL FORMULA FOR NPV. HELP IN USING THIS FORMULA IS IN THE MODULE 2 CASE TEMPLATE. THE TAB IS LABELED "Help for Multiple Cash Flows Q3."
NOTE: THESE RANGES WILL AUTOFILL BASED ON YOUR ANSWERS TO THE NPV QUESTIONS.
YES, USE THE SAME RATE
NO, DO NOT USE THE SAME RATE
YES, USE A RADR
NO, DO NOT USE A RADR
FACTORY A
FACTORY B
Questions 3 and 4
| QUESTION 3. The Carpet Company’s shareholders require an 11% return on their investment, and stock comprises 50% of the company’s capital structure. The other half of the company’s capital structure is comprised of debt. The interest rate on this debt is 7%. Assume the corporate tax rate is 21%. What is carpet company’s WACC? |
| ANSWER: |
| QUESTION 4. Assume the Carpet Company’s corporate tax rate falls to 0%. The company borrows money to buy back and retire half of its outstanding common stock. What happens to the company’s WACC? |
| ANSWER: |
Help for IRR Q1
| TO COMPUTE THE IRR FOR MULTIPLE CASH FLOWS IN EXCEL: | |
| STEP 1: INSERT THE INTERNAL RATE OF RETURN (IRR) FORMULA. CHOOSE THE FORMULA TAB – FINANCIAL – IRR (SEE BELOW). YOU CAN ALSO INSERT THE FORMULA BY TYPING =IRR IN ANY CELL. | |
| STEP 2: FILL OUT EACH OF THE FIELDS IN THE IRR WINDOW | |
| VALUES = THE CASH FLOWS (POPULATE THIS FIELD BY CLICKING ON IT AND THEN HIGHLIGHTING THE CASH FLOWS) | |
| GUESS = THIS IS THE RATE YOU THINK THE IRR IS CLOSE TO; EITHER LEAVE BLANK OR INPUT THE COST OF CAPITAL | |
| FOR THE SAMPLE WINDOW BELOW, THE CELLS WITH THE CASH FLOWS ARE IN THE 'VALUES' FIELD AND THE COST OF CAPITAL IS LEFT BLANK. THE IRR FOR THIS PROJECT IS 8.8% OR ABOUT 9%. | |
