Financial Analysis: Perform a scenario analysis with data provided

Use Excel Spreadsheet

Assume that a selected ASX company is considering a potential new project with a new product launch. You are to factor this new project as part of your evaluation on the company.

The new product is expected to sell for an average price of $25 per unit and the company expects it can sell 500,000 unit per year at this price for a period of five years. Launching this project will require purchase of a $5,500,000 equipment that has residual value in five years of $500,000 and adding $ 80,000 in working capital which is expected to be fully retrieved at the end of the project.

Other information is available below:

Depreciation method: straight line (n = 5 years)

Variable cost per unit: $15

Cash fixed costs per year $550 000

Discount rate: 9%

Tax Rate: 25%

Do a scenario analysis with cash flows of the assumed project to determine the sensitivity of the projects NPV to different scenarios that are defined in terms of the estimated values for each of the projects value drivers.

Please work on two scenarios corresponding to the worst and best-case outcomes for the project. You need to provide your results in (a) relevant tables:

Worst case: Unit sales decrease by 25%; price per unit decreases by 20%; variable cost per unit increases by 25 %; cash fixed cost per year increases by $90 000

Best case: Unit sales increase by 25%; price per unit increases by 20%; variable cost per unit decreases by 25%; cash fixed cost per year decreases by $90 000

Based on the scenario analysis outcome, draw relevant conclusion about project NPVs sensitivity.