THYU has decided to set a project timeline of 5 years. > The new system will cost $1,900,000. It will be depreciated (straight line) over a six- year period (its estimated useful life), assuming a salvage value of $100,000. The old system, which has been fully depreciated, could be sold today for $253,165. The company has received a firm offer for the system from Turning Tables and THYU will sell it only if they purchase the new system. > Additional sales generated by the superior products made by the new system would be $1,500,000 in Year 1. In Years 2 and 3 sales are projected to grow by 5.5% per year. However, in Years 4 and 5, sales are expected to decline by 25% per year as the market starts to become saturated. Total expenses have been estimated at 67.4% of Sales. The firm's tax rate 21%. THYU requires a minimum return on the replacement decision of 9.5%. A representative from Browns Business has told THYU that they will buy the system from them at the end of the project (the end of Year 5) for $200,000. THYU has decided to include this in the terminal value of the project. The project will require $150,000 in additional Net Working Capital, 67% of which will be recovered at the end of the project. Part 1: Base Case (65 points): Complete the DCF Model using the above data. Calculate NPV and IRR, and somewhere on the Base Case sheet state whether you would accept or reject the project just on the base case data alone. Note: MMMI has estimated that there is a 55% chance that the base case will occur. Before continuing to Parts 2-5, copy the entire Base Case sheet, and paste to the Part 2, Part 3, and Part 4 tabs. Part 2: Alternate Scenario 1 (5 points): Assume that expenses as a percentage of sales will be 67.8% instead of 67.4% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 2 sheet state whether you would accept or reject the project based on this scenario alone. Note: this scenario has a 10% chance of occurring. Part 3: Alternate Scenario 2 (5 points): Reverting back to the Base Case estimates and assumptions, now assume that sales growth in Years 2 and 3 will be 5.3% each year instead of 5.5%, and that sales in Years 4 and 5 will decline by 27.5% per year rather than 25% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 3 sheet state whether you would accept or reject the project based on this scenario alone. Note: this scenario has a 10% chance of occurring. Part 4: Alternate Scenario 3 (5 points): Revert back to the Base Case estimates and assumptions. Turning Tables Co. has announced that they will be producing a competing product using the old system they purchased from THYU. Financial analysts at THYU have estimated that Turning Tables Co.' new product will be released to the marketplace in Year 5 and has a 25% chance of success; this will cause sales to decline by 35% in Year 5 only instead of 25% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 4 sheet state whether you would accept or reject the project based on this scenario alone. Part 5: Conclusion (20 points): Make a decision regarding whether or not to recommend to THYU that they purchase the new system. Write 3-4 sentences giving your recommendation and explain why. Note: you must clearly state whether THYU should accept or reject this project. Simply restating what would happen in each scenario without making a clear recommendation will earn 0 points for this section of the assignment. It should be very clear as to what THYU should do and why. VALUE DRIVERS Sales Growth yrs 2-3 Sales Growth yr 4 Sales Growth yr 5 Expenses as a % of sales Cost of the new system Salvage value of new system Old system Resale New system resale in yr 5 Tax Rate Required Rate of Return Project Time 5.5% -25.0% -25.0% 67.40% $1,900,000 $100,000 $253,165 $200,000 21.00% 9.50% 5 Years 0 $1,900,000 1 2 3 4 5 $100,000 Capital Spending OCF: Revenues Expenses Depreciation EBIT Taxes Net Income Depreciation OPERATING CASH FLOW $1,500,000 $1,011,000 $300,000 $189,000 $39,690 $149,310 $300,000 $449,310 $1,582,500 $1,669,538 $1,252,153 $1,066,605 $1,125,268 $843,951 $300,000 $300,000 $300,000 $215,895 $244,269 $108,202 $45,338 $51,297 $22,722 $170,557 $192,973 $85,480 $300,000 $300,000 $300,000 $470,557 $492,973 $385,480 $939,115 $632,963 $300,000 $6,151 $1,292 $4,860 $300,000 $304,860 Net Working Capital $150,000 $100,500 Total Cash Flow $150,000 $449,310 $470,557 $492,973 $385,480 NPV IRR THYU has decided to set a project timeline of 5 years. > The new system will cost $1,900,000. It will be depreciated (straight line) over a six- year period (its estimated useful life), assuming a salvage value of $100,000. The old system, which has been fully depreciated, could be sold today for $253,165. The company has received a firm offer for the system from Turning Tables and THYU will sell it only if they purchase the new system. > Additional sales generated by the superior products made by the new system would be $1,500,000 in Year 1. In Years 2 and 3 sales are projected to grow by 5.5% per year. However, in Years 4 and 5, sales are expected to decline by 25% per year as the market starts to become saturated. Total expenses have been estimated at 67.4% of Sales. The firm's tax rate 21%. THYU requires a minimum return on the replacement decision of 9.5%. A representative from Browns Business has told THYU that they will buy the system from them at the end of the project (the end of Year 5) for $200,000. THYU has decided to include this in the terminal value of the project. The project will require $150,000 in additional Net Working Capital, 67% of which will be recovered at the end of the project. Part 1: Base Case (65 points): Complete the DCF Model using the above data. Calculate NPV and IRR, and somewhere on the Base Case sheet state whether you would accept or reject the project just on the base case data alone. Note: MMMI has estimated that there is a 55% chance that the base case will occur. Before continuing to Parts 2-5, copy the entire Base Case sheet, and paste to the Part 2, Part 3, and Part 4 tabs. Part 2: Alternate Scenario 1 (5 points): Assume that expenses as a percentage of sales will be 67.8% instead of 67.4% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 2 sheet state whether you would accept or reject the project based on this scenario alone. Note: this scenario has a 10% chance of occurring. Part 3: Alternate Scenario 2 (5 points): Reverting back to the Base Case estimates and assumptions, now assume that sales growth in Years 2 and 3 will be 5.3% each year instead of 5.5%, and that sales in Years 4 and 5 will decline by 27.5% per year rather than 25% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 3 sheet state whether you would accept or reject the project based on this scenario alone. Note: this scenario has a 10% chance of occurring. Part 4: Alternate Scenario 3 (5 points): Revert back to the Base Case estimates and assumptions. Turning Tables Co. has announced that they will be producing a competing product using the old system they purchased from THYU. Financial analysts at THYU have estimated that Turning Tables Co.' new product will be released to the marketplace in Year 5 and has a 25% chance of success; this will cause sales to decline by 35% in Year 5 only instead of 25% (all other estimates remain the same). Recalculate NPV and IRR, and somewhere on the Part 4 sheet state whether you would accept or reject the project based on this scenario alone. Part 5: Conclusion (20 points): Make a decision regarding whether or not to recommend to THYU that they purchase the new system. Write 3-4 sentences giving your recommendation and explain why. Note: you must clearly state whether THYU should accept or reject this project. Simply restating what would happen in each scenario without making a clear recommendation will earn 0 points for this section of the assignment. It should be very clear as to what THYU should do and why. VALUE DRIVERS Sales Growth yrs 2-3 Sales Growth yr 4 Sales Growth yr 5 Expenses as a % of sales Cost of the new system Salvage value of new system Old system Resale New system resale in yr 5 Tax Rate Required Rate of Return Project Time 5.5% -25.0% -25.0% 67.40% $1,900,000 $100,000 $253,165 $200,000 21.00% 9.50% 5 Years 0 $1,900,000 1 2 3 4 5 $100,000 Capital Spending OCF: Revenues Expenses Depreciation EBIT Taxes Net Income Depreciation OPERATING CASH FLOW $1,500,000 $1,011,000 $300,000 $189,000 $39,690 $149,310 $300,000 $449,310 $1,582,500 $1,669,538 $1,252,153 $1,066,605 $1,125,268 $843,951 $300,000 $300,000 $300,000 $215,895 $244,269 $108,202 $45,338 $51,297 $22,722 $170,557 $192,973 $85,480 $300,000 $300,000 $300,000 $470,557 $492,973 $385,480 $939,115 $632,963 $300,000 $6,151 $1,292 $4,860 $300,000 $304,860 Net Working Capital $150,000 $100,500 Total Cash Flow $150,000 $449,310 $470,557 $492,973 $385,480 NPV IRR