Explore BrainMass
Share

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: CAPEX
    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 April 1, 2020, 11:12 pm ad1c9bdddf
    https://brainmass.com/business/capital-structure-and-firm-value/breakeven-sensitivity-analysis-612507

    Attachments

    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.

    $2.19