Cap. Budgeting Using Excel Worksheet. Please find Attached files. Thanks.

0 Unit Production Unit Price Unit cost Sales Variable costs Depreciation Expense EBIT Tax (28%) NOPAT= EBIT - Tax Depreciation Fixed Assets Shipping & Installation fee Gross Fixed Assets Expenditure Net Working Capital Changes in Net Working Capital Salvage Value Annual after-tax Cash Flows Payback Period PV of Expected FCF Discounted Payback NPV IRR MIRR Production Growth Rate Inflation rate Years of straight-line depreciation Salavage Value (% of purchaing costs) Working Capital Growth rate Corporate Tax Rate Cost of Capital 1 2 3 4 5 A Company is planning a new project (use the EXCEL WORKSHEET attached): (1) Costs of the project is $1,400,000 for purchase fixed assets, and $50,000 for shipping & installation fee. (2) Five (5) years project life. The salvage value of fixed assets is 20% of the gross fixed assets (including shipping & installation fee). (3) The needed working capital is $35,000 in 2017, and 4% expected increase each year thereafter. (4) The project can produce 180,000 units in 2017, and 5% expected production growth each year. The current unit price is $7, and the cost of each unit is $4.5. (5) 2.2% inflation rate each year. The company will adjust both the unit price and the variable cost of each unit based on the inflation rate. (6) The company uses the 5-year straight-line depreciation policy for its fixed assets, and it is in the 28% tax bracket. (7) The cost of capital for the firm is 10%. A) Calculate the initial outlay, terminal cash flow for this project, annual after-tax cash flows. B) Calculate the payback period, discounted payback period, PI, NPV, IRR, and MIRR. C) Perform sensitivity analysis of the effects of cost of capital on the project's NPV. D) Scenario analysis of the combined effects of the following three (3) variables on payback, discounted payback, NPV, IRR, and MIRR. Scenario Production Best Case 250,000 Unit Price Unit Cost $9 $4 Expected Case 180,000 $7 $4.5 Worst Case 120,000 $6 $5