Question

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%.

1. using excel 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.

Answer 1

The value of Free Cash Flow and NPV is arrived as below:

	Free Cash Flows
	Year
	0	1	2	3	4	5	6	7
Sales in Units (A)		500,000	550,000	605,000	665,500	732,050	805,255	885,781
Sales Price Per Unit (B)		$2.00	$2.06	$2.12	$2.19	$2.25	$2.32	$2.39
Sales Revenue (C=A*B)		1,000,000.00	1,133,000.00	1,283,689.00	1,454,419.64	1,647,857.45	1,867,022.49	2,115,336.48
Less Variable Costs (C*20%)		200,000.00	226,600.00	256,737.80	290,883.93	329,571.49	373,404.50	423,067.30
Fixed Costs		850,000.00	850,000.00	850,000.00	850,000.00	850,000.00	850,000.00	850,000.00
Depreciation (1,400,000/7)		200,000.00	200,000.00	200,000.00	200,000.00	200,000.00	200,000.00	200,000.00
Earnings before Taxes		-250,000.00	-143,600.00	-23,048.80	113,535.71	268,285.96	443,617.99	642,269.18
Less Taxes (Earnings before Taxes*35%)		-87,500.00	-50,260.00	-8,067.08	39,737.50	93,900.09	155,266.30	224,794.21
Earnings After Taxes		-162,500.00	-93,340.00	-14,981.72	73,798.21	174,385.87	288,351.69	417,474.97
Add Depreciation		200,000.00	200,000.00	200,000.00	200,000.00	200,000.00	200,000.00	200,000.00
Initial Investment	-1,400,000.00
Free Cash Flow	-$1,400,000.00	$37,500.00	$106,660.00	$185,018.28	$273,798.21	$374,385.87	$488,351.69	$617,474.97

The value of NPV is calculated as below:

NPV = Cash Flow Year 0 + Cash Flow Year 1/(1+Discount Rate)^1 + Cash Flow Year 2/(1+Discount Rate)^2 + Cash Flow Year 3/(1+Discount Rate)^3 + Cash Flow Year 4/(1+Discount Rate)^4 + Cash Flow Year 5/(1+Discount Rate)^5 + Cash Flow Year 6/(1+Discount Rate)^6 + Cash Flow Year 7/(1+Discount Rate)^7

Substituting values in the above formula, we get,

NPV = -1,400,000.00 + 37,500.00/(1+15%)^1 + 106,660.00/(1+15%)^2 + 185,018.28/(1+15%)^3 + 273,798.21/(1+15%)^4 + 374,385.87/(1+15%)^5 + 488,351.69/(1+15%)^6 + 617,474.97/(1+15%)^7 = -$379,147.91