Amadeus Corporation is considering the issue of a new product to be added to its product mix. They hired you, a recent business graduate from MacEwan, for conducting the analysis. The production line would be set up in an unused space at the company$$s main plant. The plant space could be leased out to another firm at $$25,000$ per year. They have to buy new machinery. The approximate cost of the machine would be $$200,000,$ with another $$10,000$ in shipping and handling charges. It would also cost an additional $$30,000$ to install the equipment. The machinery has an economic life of $5$ years and would be in Class $8$ with a CCA rate of $20\%.$ The machinery is expected to have a salvage value of $$25,000$ after $5$ years of use.

The new product line would generate incremental sales of $1,450$ units per year for $5$ years and they are expected to grow $10\%$ per year. The cost per unit is estimated in $$75$ per unit in the first year. Each unit can be sold for $$200$ in the first year. The sales price and cost are both expected to increase by $2\mathrm{.}5\%$ per year due to inflation. The fixed costs are estimated to be $$100,000$ at the end of $1$st year and would increase with inflation. To handle the new product line, the firm$$s net operating working capital would be an amount equal to $15\%$ of sales revenues. The firm tax rate is $35\%,$ and its overall weighted average cost of capital $($WACC$)$ is $8\%.$ The project is considered by the financial department to be as risky as the company.

Requirements

$1.$ Using an Excel spreadsheet:

$$ Find the NPV$,$ IRR and discounted payback period $($PBP$)$ of the project by using the pro forma financial statement method to determine cash flows.

$$ Enter the input variables in cells of their own at the top of the spreadsheet $($so it is easier to do sensitivity analysis calculations$).$

$$ Set up the necessary equations by referencing to the input variable cells. The spreadsheet must be formula driven; do not put any numbers in equations, only cell references.

$$ Use Excel$$s built$-$in functions wherever possible $($e$.$g$.$ NPV and IRR functions$).$

$2.$ Breakeven analysis $($cash B$/$E point only$)$

Set up a formula in Excel $($this formula is not built$-$in$)$ to calculate the cash breakeven point for the base case.