Rick has decided to try the cupcake business.Being a bit slow he always enters into things two years after they hit their peak and are on the downswing. Nevertheless he wants to do an analysis to see whether or not he should start a cupcake business.

He figures that upfront costs will be $1,400,000, and in a CCA class with a CCA rate of 30%.The project is expected to last nine years and the equipment will have a salvage value of $150,000.(At the end of the project you do not need to worry about residual tax effects.)

Fixed costs for operations for the first year are estimated to be $300,000, and are expected to grow 2% per year. Variable costs for each cupcake will be 40% of the selling price. Initial upfront Net Working Capital needed will be $50,000 and thereafter will be 15% of Sales. All Net Working Capital will be recovered at the end of the project.

The number of units sold in the first year is expected to 200,000 and the growth in unit sales is expected to be 5% through the fifth year of sales. After that, from year 6 onward, the number of units sold will stay constant. The initial price of a cupcake will be $4.00, and the selling price of the cupcakes is expected to grow 3 percent per year for all nine years.

The Discount Rate has been determined to be 14% and the Tax Rate is 28%.

Prepare a “live” spreadsheet that calculates the Net Present Value of the project as well as the Payback Period and the Internal Rate of Return. Note: By a ”live” spreadsheet, I mean one that you can change the assumptions in – for instance, answer the question as to what the NPV would be if any of the assumptions changed.