please answer with excel

COLD AND SWEET COMPANY
Chocolate covering machine:
Cost	$ 900,000.00			Corporate tax rate	30%
Salvage value	$ -			Cost of capital	14%
Depreciation schedule (years)	$ 10.00
=> annual depreciation	$ 90,000.00
Sell the machine after 5 year for	$ 100,000.00
Net book value of the machine at selling point	$ 450,000.00
Capital gains from selling the machine:	$ (350,000.00)
Tax shield on capital gains loss
Net after-tax terminal value
Year	0	1	2	3	4	5
Additional bars sold		600,000	600,000	600,000	600,000	600,000
Bar price ($/unit)		1.3	1.3	1.3	1.3	1.3
Additional revenues on bars sold
Production cost per bar ($/unit)		0.5	0.5	0.5	0.5	0.5
Production costs on bars sold
Other costs
Marketing expenses
Depreciation
Earnings before taxes
Corporate tax
Net operating profit after tax
Capital investment and terminal value)
Add back depreciation
Free cash flow
Section b. NPV calculations
Section c.
Minimum bar price ($/unit)
Year	0	1	2	3	4	5
Additional bars sold
Bar price ($/unit)
Additional revenues on bars sold
Production cost per bar ($/unit)
Production costs on bars sold
Other costs
Marketing expenses
Depreciation
Earnings before taxes
Corporate tax
Net operating profit after tax
Capital investment
Add back depreciation
Free cash flow
NPV
Section d.
Minimum number of additional bars sold
Year
Additional bars sold
Bar price ($/unit)
Additional revenues on bars sold
Production cost per bar ($/unit)
Production costs on bars sold
Other costs
Marketing expenses
Depreciation
Earnings before taxes
Corporate tax
Net operating profit after tax
Capital investment
Add back depreciation
Free cash flow
NPV
Section e.
Annual unit sales; Unit price
	1.2	1.3	1.4	1.5	1.6	1.7
400,000
450,000
500,000
550,000
600,000
650,000
700,000
750,000
800,000
850,000
900,000

UI puillasing the air-conditioning system if the discount rate is 12% and corporate tax rate is 35%? 9. (Cash-flow analysis) The Cold and Sweet" (C&S) company manufact ice cream bars. The company is considering the purchase of a new machin that will top the bar with high-quality chocolate. The cost of the machine $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%. 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 CHAPTER 6 Capital Budgeting: Valuing Business Cash Flows 215 e. quantity that the company needs to sell in order to be profitable if the Vice President's suggestion is accepted? 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