PLEASE ANSWER USING EXCEL AND SHOW STEP BY STEP. THANK YOU

9. (Cash-flow analysis) The "Cold and Sweet" (C&S) company manufactures ice cream bars. The company is considering the purchase of a new machine that will top the bar with high-quality chocolate. The cost of the machine is $900,000. The machine will be depreciated over 10 years to zero salvage value. However, the company intends to use the machine for only 5 years. Management thinks that the sale price of the machine at the end of 5 years will be $100,000. The machine can produce up to 1 million ice cream bars annually.

The marketing director of C&S believes that if the company will spend $30,000 on advertising in the first year and another $10,000 in each of the following years, the company will be able to sell 600,000 bars for $1.30 each. The cost of producing each bar is $0.50, and other costs related to the new products are $40,000 annually. C&S's cost of capital is 14%, and the corporate tax rate is 30%. (PLEASE ANSWER USING EXCEL AND SHOW STEP BY STEP. THANK YOU)

a. What are the capital gains/losses from selling the machine after 5 years?

b. What is the NPV of the project if the marketing director's projec-tions are correct?

c. What is the minimum price that the company should charge for each bar if the project is to be profitable? Assume that the price of the bar does not affect sales.

d. The C&S Marketing Vice President suggested canceling the adver-tising campaign. In his opinion, the company sales will not be reduced significantly due to the cancellation. What is the minimum quantity that the company needs to sell in order to be profitable if the Vice President's suggestion is accepted?

e. The Marketing Vice President would like some sensitivity analysis done. He asks, "What would be the NPV of the project if annual unit sales vary from 400,000, 450,000, ..., 900,000 and if the unit price per bar varies from $1.20, $1.30, ..., $1.70?" Show the Data Table that answers his question.

PS: you can find the template on http learninglink.oup.com/ access/ benninga-3e-student-resources#tag_chapter-06

Chapter 6, exercise 9