Explore BrainMass

Breakeven Sensitivity Analysis

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

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: working capital investment
Free cash flow 199,000

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

b. 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.)
c. If unit sales were 15% higher than the base case, what unit price would it take for the investment to achieve a zero NPV?

© BrainMass Inc. brainmass.com March 22, 2019, 3:22 am ad1c9bdddf


Solution Preview

Please see the attached Excel 97-2003 spreadsheet. Please note ...

Solution Summary

This solution illustrates how to build an manipulate a spreadsheet model in order to perform a sensitivity analysis on a product's sales volume and unit selling price.