The Cold & Sweet Company manufactures ice-cream bars. They are considering the purchase of a new machine that will top the bar with high quality chocolate. The cost of the machine is $900,000; it has a life of 10 years and the company will have to increase its net working capital by $20,000 to use it. The machine can produce up to 1,000,000 ice-cream bars annually. The marketing director believes that if the company will spend $20,000 in advertising every year, the company will be able to sell 400,000 bars for $1.30 each. The cost of producing each bar is $0.50; and other costs related to the new product are $40,000 annually. The companys cost of capital is 14% and the corporate tax rate is 30%. The company will depreciate 10% of the machines value every year. The company expects the equipment to be actually sold for $200,000 after 10 years.

1. Calculate the initial outlay, annual operating cash flow and terminal cash flows. Complete the table with the timeline and calculate the NPV and IRR of the project. (10 points)

2. Create a Data Table that shows changes in NPV, when required rate changes (11%, 12%, 13%, 14%, 15%) (10 points)

3. The annual depreciation as % of machine cost can be 5% or 10%. Create combo box for the two different depreciation %. (10 points)

4. The company expects the increase in net working capital to be $10,000, $20,000, $30,000. Create option buttons for the three different increases in NWC and group together the option buttons. (10 points)

5. Create a scenario summary table (using the scenario manager tool) to show what happens to the NPV and IRR for the following three scenarios. (10 points)

Three Scenarios for quantity sold and unit price :
	Quantity Sold		Unit Price
Worst	200,000		1.4
Expected	400,000		1.3
Best	600,000		1.1