a. Develop a spreadsheet model, and use it to find the projects NPV, IRR, and payback.
Input Data (in thousands of dollars)
Equipment cost			$10,000
Net operating working capital/Sales			10%
First year sales (in units)			1,000
Sales price per unit			$24.00
Variable cost per unit (excl. depr.)			$17.50
Nonvariable costs (excl. depr.)			$1,000
Market value of equipment at Year 4			$500
Tax rate			40%
WACC			10%
Inflation in prices and costs			3.0%
Estimated salvage value at year 4			$500
Intermediate Calculations				0	1	2	3	4
Units sold					1,000	1,000	1,000	1,000
Sales price per unit (excl. depr.)					$24,000.00	$24,720.00	$25,461.60	$26,225.45
Variable costs per unit (excl. depr.)					$17,500.00	$18,025.00	$18,565.75	$19,122.72
Nonvariable costs (excl. depr.)					1,000	1,030	1,061	1,093
Sales revenue					$24,000,000	$24,720,000	$25,461,600	$26,225,448
Required level of net operating working capital
Basis for depreciation				$10,000
Annual equipment depr. rate					20.00%	32.00%	19.20%	11.52%
Annual depreciation expense					$2,000	$3,200	$1,920	$1,152
Ending Bk Val: Cost Accum Dep'rn				$10,000	$8,000	$4,800	$2,880	$1,728
Salvage value								$500,000
Profit (or loss) on salvage
Tax on profit (or loss)
Net cash flow due to salvage
				Years
Cash Flow Forecast				0	1	2	3	4
Sales revenue					$24,000,000	$24,720,000	$25,461,600	$26,225,448
Variable costs					$17,500,000.00	$18,025,000.00	$18,565,750.00	$19,122,722.50
Nonvariable operating costs					1,000,000	1,030,000	1,060,900	1,092,727
Depreciation (equipment)					2,000,000	3,200,000	1,920,000	1,152,000
Oper. income before taxes (EBIT)					$3,500,000	$2,465,000	$3,914,950	$4,857,999
Taxes on operating income (40%)					1,400,000	986,000	1,565,980	1,943,199
Net operating profit after taxes					$2,100,000	$1,479,000	$2,348,970	$2,914,799
Add back depreciation					2,000,000	3,200,000	1,920,000	1,152,000
Equipment purchases				$10,000,000
Cash flow due to change in NOWC				$2,400,000	$72,000	$74,160	$76,385	-$2,622,545
Net cash flow due to salvage								$991,200
Net Cash Flow (Time line of cash flows)				-$12,400,000	$4,028,000	$4,604,840	$4,192,585	$7,680,544