For this activity, you will view the following case study:Mini Case: Allied Products

Based on the Allied Products case, use the following spreadsheet template to solve the problem.

Download the Allied Products spreadsheet template

Please notethat this spreadsheet template is an example, andthe numbers will not match the mini case. You will need to update the numbers based on the mini case study.

Submit yoursolutions to the case using the spreadsheet template provided to you. Be sure to show your work. You will be graded on how well you complete the following:

1. Explain and show how you calculated depreciation for each year.
2. Explain and show how you arrived at your WACC (weighted average cost of capital).
3. Explain and show for one year how you used probabilities to calculate total market for the products and how you arrived at Allieds share.
4. Explain how you arrived at working capital for the different years.
5. Explain how you arrived at salvage value less taxes for the last year.
6. Explain how you calculated NPV and IRR in Excel and decided whether to accept or reject the project.

Assumptions PP&E Investment Useful life of PP&E Investment (years) Salvage Value of PP&E Investment Annual Depreciation Expense (7 year MACRS) Year 1 2 3 4 Last year of project 5 6 7 8 42,000,000 7 12,000,000 MACRS % Depreciation 14.29% 6,001,800 24.49% 10,285,800 17.49% 7,345,800 12.49% 5,245,800 8.93% 3,750,600 8.93% 3,750,600 8.93% 3,750,600 4.45% 1,869,000 Ending Book Value 35,998,200 25,712,400 18,366,600 13,120,800 9,370,200 5,619,600 1,869,000 0 NEW GPWS price/unit (Year 1) NEW GPWS variable cost/unit (Year 1) UPGRADE GPWS price/unit (Year 1) UPGRADE GPWS variable cost/unit (Year 1) 70,000 50,000 35,000 22,000 Year 1 marketing and admin costs Annual inflation rate Corporate Tax rate 3,000,000 3.00% 40.00% Beta (9/27 Valueline) Rf (30 year U.S. Treasury Bond) Rm (S&P 500 30 year average) Re (from CAPM) WACC New Aircraft Production (i.e. NEW GPWS Market) Probability Strong Growth 0.15 Moderate Growth 0.45 Mild Recession 0.3 Severe Recession 0.1 Expected New Airplane Production NEW GPWS Market Growth (Strong Growth) NEW GPWS Market Growth (Moderate Growth) NEW GPWS Market Growth (Mild Recession) NEW GPWS Market Growth (Severe Recession state of economy) Year 1 Year 2 350 250 150 50 215 Year 3 15.00% 10.00% 6.00% 3.00% Total Annual Market for UPGRADE GPWS (units) Allied Signal Market Share in each market 2,500 45.00% Year Sales NEW Units Price Total NEW UPGRADE Units Price Total UPGRADE Total Sales Variable Costs NEW UPGRADE Total Variable Costs SG&A Depreciation EBIT Interest Tax Net Income EBIT + Dep - Taxes NWC Less: Change in NWC Less: Captial Spending CF from Assets: Discounted CF from Assets Total Discounted CF from Assets Less: Investment Net Present Value IRR 0 1 2 3 Results Payback Discounted Payback AAR NPV PI Year 4 Year 5 4 5