Problem: The expected annual free cash flow for the GPS tracker investment from problem 3-1 is computed as follows:
Revenues 1,250,000
Variable cost 750,000
Fixed expenses 250,000
Gross profit 250,000
Depreciation 100,000
Net operating income 150,000
Income tax expense 51,000
NOPAT 99,000
Plus: depreciation 100,000
Less: CAPEX
Less: working capital investment
Free cash flow 199,000
Q1. Construct a spreadsheet model to compute free cash flow that relies on the following assumptions or estimates:
Base Case Estimates Values
Initial cost of equipment 1, 000,000
Project and equipment life 10 years
Salvage value of equipment 0
Working capital requirement 0
Depreciation method Straight-line
Depreciation expense 100,000
Discount rate 10.00%
Tax rate 34.00%
Unit sales 10,000
Price per unit 125.00
Variable cost per unit 75.00
Fixed costs 250,000
Q2. What level of annual unit sales does it take for the investment to achieve a zero NPV? Use your spreadsheet model to answer this question. (Hint: Use the Goal Seek function in Excel.)
Q3. If unit sales were 15% higher than the base case, what unit price would it take for the investment to achieve a zero NPV?