SOLVE IN EXCEL SHOW FORMULAS

Wonderful Snacks, Inc. is considering adding a new line of cookies and bars to its current product offer. The project's life is 7 years. The firm estimates selling 500k packages at a price of $2 per unit the first year, but this volume is expected to grow at 10% per year over the life of the project. The price per unit is expected to grow at a rate of 3% per year. Variable costs are 20% of revenue and the fixed costs will be $850K per year. The equipment required to produce the cookies and bars has an upfront cost of$1.4M. It will be depreciated using straight-line depreciation over the life of the project. After seven years, the equipment will be worthless. No additional net working capital is required for this project. The project's discount rate is 15% and the firm's marginal tax rate is 35%. 7. (15 points) in the "FE #7-8" worksheet, build a model to calculate the project's free cash flows (FCF) and net present value. Create driver cells that allow the user to vary the following inputs: volume growth rate, price per unit growth rate, and discount rate. Below are calculations you may find useful in setting up your model: FCF = EBIT x (1 - tax rate) + Depreciation - Capital Expenditures - ANWC + Terminal CF EBIT = (Price x Units) - Variable costs - Fixed Costs - Depreciation Terminal Cash Flow = Salvage Value - Tax Ratex (Salvage Value - Book Value)