Hi I need help using excel and doing a cash flow estimation, thank you.

5. Cash Flow Estimation (40 points)

- We assume that the company that you selected is considering a new project. We assume that the

company you selected is considering a new project. The project has 8 years life. This project requires

initial investment of $500 million to construct building and purchase equipment, and $30 million for

shipping & installation fee. The fixed assets fall in the 7-year MACRS class. The salvage value of the fixed

assets is $32 million. The number of units of the new product expected to be sold in the first year is

1,500,000 and the expected annual growth rate is 7.8%. The sales price is $285 per unit and the variable

cost is $235 per unit in the first year, but they should be adjusted accordingly based on the estimated

annualized inflation rate of 1.8%. The required net operating working capital (NOWC) is 13.6% of sales. Use

the corporate tax rate (TAX RATE IS 22.13%) obtained in Step (4) for this project. The project is assumed to

have the same risk as the corporation, so you should use the WACC you obtained from prior steps as the

discount rate (3.99%). Note: you may revise the partial model in the file Ch11 P18 Build a Model.xls provided by

the textbook (Posted in this final project learning module in Blackboard) for capital budgeting analysis,

but you are NOT required to strictly follow this partial model. Actually, you are encouraged to build a

model by yourself.

- Compute the depreciation basis and annual depreciation of the new project. (Please refer to Table 11A2 MACRS allowances in the textbook)

- Estimate annual cash flows for the 8 years.

- Draw a time line of the cash flows. (6) Capital Budgeting Analysis (40 points)

- Using the WACC obtained from in Step (4) (WACC IS 3.99%) as the discount rate for this project,

apply capital budgeting analysis techniques (NPV, IRR, MIRR, PI, Payback, Discounted Payback) to analyze

the new project.

- Perform a sensitivity analysis for the effects of key variables (e.g., sales growth rate, cost of capital, unit

costs, sales price) on the estimated NPV or IRR in order to demonstrate the sensitivity of the model. The

Scenario analysis of several variables simultaneously is encouraged (but not required). A PDF document

named Sensitivity Analysis in Excel is provided in this learning module. The article introduces the Data

Table method that you can use for performing sensitivity analysis in Excel.

- Discuss whether the project should be taken and summarize your report