I am doing a spreadsheet assignment to calculate the npv given this info, but I am confused on where to start and what to do. Please show me how to calculate the npv while properly using the formats above, thank you!

1. You are working with a consumer product company on estimating the expected cash flows from a new brand of toothpaste that they plan to introduce. From your prior experiences with earlier introductions of similar products, you have collected the following information: You are likely to sell 8 million tubes in the first year that you introduce the project, 10 million in the second year, 12 million in the third year and back down to 8 million in the fourth year. At the end of the fourth year, you will have to replace the brand with a new one. You price toothpaste at $ 3 a tube currently, and expect to increase prices at 5% a year for the next 4 years. You will have to advertise most heavily in the first year, spending $ 10 million on advertising, but you expect this to drop to $ 6 million in year 2, $ 4 million in year 3 and $ 2 million in year 4. Your costs associated with producing the toothpaste are $0.50 a tube currently, and you expect this to increase at 5% a year for the next 4 years. You will have to invest $ 10 million in new equipment at an old plant to create the manufacturing facilities and you expect to depreciate this expenditure straight line over the 4 years down to zero. You have a 40% tax rate. The firm's weighted average cost of capital is 12%. This is the required return on the project. What is the NPV? Input Area: 0 $3.00 Year units price/unit Revenues cost per unit COGS Fixed Costs (advertisement cost) 1 $8.00 $3.15 $25.20 $0.53 $4.20 $10.00 2 $10.00 $3.31 $33.08 $0.55 $5.51 $6.00 3 $12.00 $3.47 $41.67 $0.58 $6.95 $4.00 4 $8.00 $3.65 $29.17 $0.61 $4.86 $2.00 $0.50 Year Beg BV Dep 0 10 1 10 2 7.5 3 5 4 2.5 End BV 10 2.5 7.5 2.5 5 2.5 2.5 2.5 0 Output Area: Pro Forma Income Statements 1 2 3 Year Revenues COGS Fixed Costs Dep |EBIT Taxes(40%) NI OCF 0 1 2 4 Total Cash Flow Year OCF Cap Spending Total CF PV @ 12% NPV =