Instructions: Provide solutions on excel with the work

The Pickering Company (PC) manufactures food products. The company is considering the purchase of a new machine that will allow the company to produce meatless burgers and enter the national meatless burger market. The cost of the machine is $ 800,000. The machine will be depreciated over 10 years to a 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 $150,000.

The machine can produce 350,000 packages of burgers annually. The marketing director of PC believes if the company will spend $40,000 on advertising in the first year and another $20,000 in each of the following years for national advertising, the company will be able to sell 200,000 packages at $4.50 each for the national market. The cost of producing the packages is $2.60, and the other costs related to the new product are $40,000 annually, PCs cost of capital is 14% and the corporate tax rate is 30%

1. What is the capital gain/loss from selling the machine after 5 years?
2. What is the NPV of the project if the marketing directors projections are correct? Show your detailed calculation of yearly free cash flows.
3. What is the minimum price the company should charge for each bar if the project is to be profitable? Assume the price of the bar does not affect sales.
4. The PC Marketing Vice President suggested canceling the advertising campaign. If his opinion, the companys sales will not be reduced significantly due to the cancellation, what is the minimum quantity the company needs to sell in order to be profitable if the Vice Presidents suggestion is accepted?
5. The Marketing Vice President would like some sensitivity analysis done. He asks; What would the NPV of the project be if annual unit sales were 100,000, 150,000, 200,000, 250,000, 300,000, and 350,000 and if the unit price of the bars varies from $3.50, $4.00, $4.50, $5.00, $5.50 and $6.00. Show the Data Table that answers this question.