Big Muzz Motors manufactures a line of traditional (two wheel) motor cycles. Given the recent success of moto-cars like the Polaris slingshot, management is considering a new business proposal to produce a line of aggressive 3-wheeled motorcycles. Estimates of the cost and benefits include the following information:

Cost of new production equipment and machinery including freight and setup $2,000,000 with a 5 year life (assume straight line depreciation applies)

Expense of hiring and training new employees pre-startup $125,000

Pre-start-up advertising and other miscellaneous expenses $75,000

Initial inventory required to service sales $150,000 at t0 (does not vary over the life of the project will be sold off at end of project). This is the only working capital for this project.

Sales are forecast to be 125 units the first year and will grow 35% in the second year and 20% in the third.

Sales Price: $24,000 / unit

Unit cost to manufacture (60% of revenue): $14,400

Additional selling and administrative expense per year after start-up 12% of sales

If Big Muzz does this project to make three-wheeled car alternatives they expect to lose some of their current motorcycle sales to the new product. Ten percent of the new unit forecast is expected to come out of sales that would have gone to the old line of motorcyles (e.g. someone comes in to buy a motorcycle but sees the new product and decides to buy one of them instead). You can assume that prices and profit margins are identical for both product lines.

Make sure that you remember that this project is part of an existing company and so any tax savings on losses could be used to offset taxes on other lines of business. Assume that these tax savings can be fully absorbed in the year they are incurred.

Big Muzz has a 35% tax rate and wants to estimate the cash flows of this project over the next 3 years. Assume that the initial inventory is liquidated at its current value at the end of three years and that the new equipment purchased today is sold at its book value in 3 years.

1. Determine the free cash flows for this investment.
2. If Big Muzz thought they could earn 12% on other investments, would you recommend they do this project? Why or why not?
3. What is the most you should pay for the new equipment and machinery? In other words what is the purhcase price of the new equipment that makes this a a break-even ($0 NPV) project?
4. Build a one dimensional data table that varies Initial Sales (ceteris paribus) from 25 to 175 in steps of 25. Graph and format the results.
5. Run the Texas Tornado on the problem varying the cost of capital from 8% to 18%, first year Sales from 25 to 125, Year 2 sales growth from 0-45%, year 3 sales growth from 0 - 30% and purchase price of new equipment from 1.9M to 2.2 M. Use the breakeven Tornado option and interpret the graph and other information. Using the output data from this analysis, what is the IRR of the project? Recall that the inputs you want to vary need to be in contiguous cells.
6. Build a two-dimensional data table that varies the year 2 sales growth from 0-45% in steps of 5% and year 3 sales growth from 0 - 30% in steps of 5% (dont use the Tornado tool to make this table; use the built in Excel functionality). Format the data so that it is obvious where the "good" outcomes occur using Conditional Formatting.
7. What is the main difference between the Tornado chart from e) and the two-dimensional data table from f)? Specifically, there are many bars on a Tornado chart but only two margins in the table. How many inputs are moving in each type of sensitivity analysis?
8. Your boss Pat and her boss Chris both think that your base case assumptions for Cost of Capital, Initial Sales and Year 2 and Year 3 sales growth. Pat thinks those quantities should be 10%, 120, 30% and 15%, respectively, while Chris thinks they should be 14%, 150, 40% and 10%. Create a base scenario that includes the base-case parameter values and add two additional scenarios named Pat and Chris that show the impact on NPV based on each scenario (this should be achieved via the Scenario Manager). Show the effect on both IRR and NPV in a single output table. What does this say about the desirability of the project?