Scribd Logo
Upload

Welcome to Scribd!

  • 2K views

    Business Valuation Template

    Uploaded by

    Akshay Mathur
    The discounted cash flow model forecasts revenue, expenses, profits, cash flows, and valuation for Pie in the Sky Company from 2001-2011. It allows the user to change various forecasting variables in red to observe their effects. The business value conclusion is most sensitive to changes in the discount rate and least sensitive to changes in the net working capital to sales ratio. The model values the company's operations at $11.1 million and assets at $11.6 million.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as XLS, PDF, TXT or read online from Scribd

    Business Valuation Template

    Uploaded by

    Akshay Mathur
    2K views2 pages
    The discounted cash flow model forecasts revenue, expenses, profits, cash flows, and valuation for Pie in the Sky Company from 2001-2011. It allows the user to change various forecasting variables in red to observe their effects. The business value conclusion is most sensitive to changes in the discount rate and least sensitive to changes in the net working capital to sales ratio. The model values the company's operations at $11.1 million and assets at $11.6 million.

    Original Description:

    Business Valuation Template

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    XLS, PDF, TXT or read online from Scribd

    Read this document in other languages

    Did you find this document useful?

    Is this content inappropriate?

    The discounted cash flow model forecasts revenue, expenses, profits, cash flows, and valuation for Pie in the Sky Company from 2001-2011. It allows the user to change various forecasting variables in red to observe their effects. The business value conclusion is most sensitive to changes in the discount rate and least sensitive to changes in the net working capital to sales ratio. The model values the company's operations at $11.1 million and assets at $11.6 million.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as XLS, PDF, TXT or read online from Scribd
    Download as xls, pdf, or txt
    2K views2 pages

    Business Valuation Template

    Uploaded by

    Akshay Mathur
    The discounted cash flow model forecasts revenue, expenses, profits, cash flows, and valuation for Pie in the Sky Company from 2001-2011. It allows the user to change various forecasting variables in red to observe their effects. The business value conclusion is most sensitive to changes in the discount rate and least sensitive to changes in the net working capital to sales ratio. The model values the company's operations at $11.1 million and assets at $11.6 million.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as XLS, PDF, TXT or read online from Scribd
    Download as xls, pdf, or txt
    of 2

    Cash Flow Business Valuation Model

    Instructions: Change any of the forecasting variables shown in red below and observe the effect on the model's
    outputs in the section below. To what variable is the business value conclusion most sensitive? To what variable
    is it least sensitive?

    Discounted Cash Flow Model for a Complete Business


    Pie In the Sky Company
    Forecasting Variables:

    2002 2003 2004 2005 2006


    Revenue growth factor
    Expected gross profit margin
    S, G, & A expense % of revenue
    Depr. & Amort. % of revenue
    Capital expenditure growth factor
    Net working capital to sales ratio
    Income tax rate
    Assumed long-term sustainable growth rate
    Discount rate

    2007

    2008

    2009

    2010

    2011

    60%
    54%
    28%
    10%
    20%
    15%

    50%
    55%
    27%
    10%
    -10%
    14%

    40%
    56%
    26%
    10%
    -15%
    13%

    30%
    57%
    25%
    10%
    -20%
    12%

    20%
    58%
    24%
    10%
    -25%
    11%

    10%
    59%
    23%
    10%
    -30%
    10%

    2002 2003 2004 2005 2006

    2007

    2008

    2009

    2010

    2011

    50% 51% 52% 53% 54%


    -10%
    1% 12% 10% 10%
    ($1.3) ($1.4) ($1.3) ($1.4) ($1.0)

    55%
    11%
    $0.7

    56%
    12%
    $2.8

    57%
    13%
    $5.2

    58%
    14%
    $7.3

    59%
    16%
    $8.8
    $61.8

    20%
    50%
    50%
    10%
    40%
    19%

    30%
    51%
    40%
    10%
    35%
    18%

    40%
    5% per year after 2011
    20%

    40%
    52%
    30%
    10%
    30%
    17%

    50%
    53%
    29%
    10%
    25%
    16%
    40%
    5%
    20%

    Valuation Model Outputs:

    Gross profit margin


    Net operating profit margin
    Free cash flow ($ mil)
    Terminal value ($ mil)
    PV of Company Operations ($ mil)

    $11.1

    Market Value of Company Assets ($ mil)

    $11.6

    The Discounted Free Cash Flow Model for a Complete Business


    Pie In the Sky Company
    Years Ending December 31
    Actual |------------------------------------------------------------------------------------------ Forecast ----------------------------------------------------------------------------------------|
    2001
    2002
    2003
    2004
    2005
    2006
    2007
    2008
    2009
    2010
    2011
    Total revenue

    $2,000,000

    $2,400,000

    $3,120,000

    $4,368,000

    $6,552,000 $10,483,200 $15,724,800 $22,014,720 $28,619,136 $34,342,963

    $37,777,260

    Cost of Goods Sold


    Gross profit

    1,200,000
    800,000

    1,200,000
    1,200,000

    1,528,800
    1,591,200

    2,096,640
    2,271,360

    3,079,440
    3,472,560

    4,822,272
    5,660,928

    7,076,160
    8,648,640

    9,686,477
    12,328,243

    12,306,228
    16,312,908

    14,424,045
    19,918,918

    15,488,676
    22,288,584

    Selling, general and administrative expenses


    Earnings before interest, taxes, depr. & amort. (EBITDA)

    1,200,000
    (400,000)

    1,200,000
    0

    1,248,000
    343,200

    1,310,400
    960,960

    1,900,080
    1,572,480

    2,935,296
    2,725,632

    4,245,696
    4,402,944

    5,723,827
    6,604,416

    7,154,784
    9,158,124

    8,242,311
    11,676,607

    8,688,770
    13,599,814

    Depreciation and amortization


    Earnings before Interest and taxes (EBIT)
    Available tax-loss carryforwards
    Net taxable earnings

    200,000
    (600,000)
    0
    0

    240,000
    (240,000)
    (600,000)
    0

    312,000
    31,200
    (840,000)
    0

    436,800
    524,160
    (808,800)
    0

    655,200
    917,280
    (284,640)
    632,640

    1,048,320
    1,677,312
    0
    1,677,312

    1,572,480
    2,830,464
    0
    2,830,464

    2,201,472
    4,402,944
    0
    4,402,944

    2,861,914
    6,296,210
    0
    6,296,210

    3,434,296
    8,242,311
    0
    8,242,311

    3,777,726
    9,822,088
    0
    9,822,088

    Federal and State Income Taxes


    Net Operating Profit After-Tax (NOPAT)

    0
    (600,000)

    0
    (240,000)

    0
    31,200

    0
    524,160

    253,056
    664,224

    670,925
    1,006,387

    1,132,186
    1,698,278

    1,761,178
    2,641,766

    2,518,484
    3,777,726

    3,296,924
    4,945,387

    3,928,835
    5,893,253

    1,572,480
    (3,316,950)
    733,824
    $687,632

    2,201,472
    (2,819,408)
    817,690
    $2,841,521

    2,861,914
    (2,255,526)
    792,530
    $5,176,644

    3,434,296
    (1,691,645)
    629,621
    $7,317,659

    3,777,726
    (1,184,151)
    343,430
    $8,830,257

    Add back depreciation and amortization


    Subtract Capital Expenditures
    Subtract New Net Working Capital
    Free Cash Flow

    200,000
    (1,000,000)

    240,000
    312,000
    436,800
    655,200
    1,048,320
    (1,400,000) (1,890,000) (2,457,000) (3,071,250) (3,685,500)
    76,000
    129,600
    212,160
    349,440
    589,680
    ($1,400,000) ($1,324,000) ($1,417,200) ($1,283,880) ($1,402,386) ($1,041,113)

    Terminal value, 2011

    $61,811,799

    Present Value of Free Cash Flows @ 20%

    (1,103,333)

    (984,167)

    (742,986)

    (676,305)

    Total Present Value of Company Operations


    $11,129,331
    Plus Current Assets
    500,000 from Pie in the Skys' December 31, 2001 Balance Sheet
    Total Market Value of Great Expectations' Assets

    $11,629,331

    (418,400)

    230,287

    793,016

    1,203,922

    1,418,211

    11,409,086

    You might also like