Docstoc

Corporate Valuation

Document Sample
Corporate Valuation Powered By Docstoc
					d57e31e4-8204-420f-81b6-d059d79b6c40.xls

Corporate Valuation

5/18/2004

Chapter 13. Tool Kit for Corporate Valuation, Value-Based Management, and Corporate Governance
This spreadsheet has two major components, one for Corporate Valuation and one for Value Based Management. Click on the tabs in the lower left of the screen to switch between sections. The value of a company is the sum of: (1) the value of its assets-in-place, including their associated growth opportunities, which is called the value of operations and (2) the value of its nonoperating assets, such as marketable securities and investments in non-controlled affiliates. The value of operations is the present value of the free cash flows produced by the assets-in-place and their associated growth opportunities. PROBLEM You are given the current and projected financial statements of MagnaVision. Growth is expected to be 5% for each year after the projections. If the WACC is 10.84%, what is the value of operations? INPUT DATA SECTION: Current and Projected Data Used in the Analysis MagnaVision, Inc.: Income Statements for Years Ending December 31 (in millions of dollars) Actual 2005 2006 Net Sales $700.0 $850.0 Costs (except depreciation) $599.0 $734.0 Depreciation 28.0 31.0 Total operating costs $627.0 $765.0 Earning before int. & tax $73.0 $85.0 Less: Net interest 13.0 15.0 Earning before taxes $60.0 $70.0 Taxes (40%) 24.0 28.0 Net income before pref. div. $36.0 $42.0 Preferred div. 6.0 7.0 Net income avail. for com. div. $30.0 $35.0 Common dividends $0.0 $0.0 Addition to retained earnings $30.0 $35.0 Number of shares Dividends per share 100 $0.000 100 $0.000

Projected 2007 2008 $1,000.0 $1,100.0 $911.0 $935.0 34.0 36.0 $945.0 $971.0 $55.0 $129.0 16.0 17.0 $39.0 $112.0 15.6 44.8 $23.4 $67.2 7.4 8.0 $16.0 $59.2 $0.0 $44.2 $16.0 $15.0 100 $0.000 100 $0.442

2009 $1,155.0 $982.0 38.0 $1,020.0 $135.0 19.0 $116.0 46.4 $69.6 8.3 $61.3 $45.3 $16.0 100 $0.453

MagnaVision Inc.: December 31 Balance Sheets (in millions of dollars) Actual 2005 Cash $17.0 Marketable Securities 63.0 Accounts receivable 85.0 Inventories 170.0 Total current assets $335.0 Net plant and equipment 279.0 Total Assets $614.0 Liabilities and Equity Accounts Payable Notes payable Accruals Total current liabilities Long-term bonds Preferred stock Common Stock (par plus paid in capital) Retained earnings Common equity Total liabilities and equity

2006 $20.0 70.0 100.0 200.0 $390.0 310.0 $700.0

Projected 2007 $22.0 80.0 110.0 220.0 $432.0 341.0 $773.0

2008 $23.0 84.0 116.0 231.0 $454.0 358.0 $812.0

2009 $24.0 88.0 121.0 243.0 $476.0 376.0 $852.0

$16.0 123.0 44.0 $183.0 124.0 62.0 $200.0 45.0 $245.0 $614.0

$20.0 140.0 50.0 $210.0 140.0 70.0 $200.0 80.0 $280.0 $700.0

$22.0 160.0 55.0 $237.0 160.0 80.0 $200.0 96.0 $296.0 $773.0

$23.0 168.0 58.0 $249.0 168.0 84.0 $200.0 111.0 $311.0 $812.0

$24.0 176.0 61.0 $261.0 176.0 88.0 $200.0 127.0 $327.0 $852.0

Michael C. Ehrhardt

Page 1

8/14/2008

d57e31e4-8204-420f-81b6-d059d79b6c40.xls

Corporate Valuation

Step 1: Calculate FCF 1. Net operating working capital 2. Net Plant 3. Net operating capital 4. Investment in operating capital 5. NOPAT 6. Less: Investment in op. capital 7. Free cash flow Step 2: Calculate the value of operations Long-term growth rate Weighted Avg. Cost of Cap. (WACC) Free Cash Flow Find the horizon value.

Actual 2005 $212.0 279.0 $491.0 $43.8

2006 $250.0 310.0 $560.0 $69.0 $51.0 69.0 ($18.0)

Projected 2007 $275.0 341.0 $616.0 56.00 33.00 56.0 ($23.0) Projected 2007

2008 $289.0 358.0 $647.0 31.00 77.40 31.0 $46.4

2009 $303.0 376.0 $679.0 32.00 81.00 32.0 $49.0

Actual 2005 5% 10.84%

2006

2008

2009

($18.0)

($23.0)

$46.4

$49.0

The horizon value is the value as of 12/31/2009 of all the free cash flows in 2010 and beyond, discounted back to 12/31/2009. The formula is:

HV2009 = [FCF2009 * (1+g)] / [ WACC - g].

Horizon Value (Value at 12/31/2009) Find the PV of the horizon value and of the free cash flows. PV of Horizon Value @ WACC $583.70 PV of free cash flows @ WACC $31.58 Value of Operations PROBLEM Find the value of equity and the price per share. Step 1: Find the total value of the firm. Value of operations Plus: Value of nonoperating assets Total value of firm Step 2: Find the value of equity. Total value of firm Minus: Value of debt Minus: Value of preferred stock Value of equity Step 3: Find the price per share. Value of equity Divided by number of shares Price per share Step 4: Find Market Value Added (MVA) Total value of the firm Minus: Book value of debt Minus: Book value of preferred stock Minus: Book value of equity Market value added (MVA) $678.27 $247.00 $62.00 $245.00 $124.27 $369.27 100 $3.69 $615.27

$880.99

$615.27 $63.00 $678.27

$678.27 $247.00 $62.00 $369.27

Michael C. Ehrhardt

Page 2

8/14/2008

d57e31e4-8204-420f-81b6-d059d79b6c40.xls

Corporate Valuation

$700 $600 $500 $400 $300 $200 $100 $0
Nonoperating assets = $63

Market Value Added

See right for inputs to chart.

Market value of equity = $369
Value of operations = $615

Book value of equity = $245
Preferred stock = $62

Preferred stock = $62

Debt = $247
Debt = $247 Market Value: Sources Market Value: Claims Book Value: Claims

PROBLEM The cost of equity, rs, for Magnavision is 14%. Using the dividend growth model, what is the stock price per share?

Dividend Growth Model Year Diviend per share Cost of equity, rs Long-term growth rate Price of stock at 2009 = [D2009 * (1+g)] / [rs - g] PV of Year 2009 stock price @ rs PV of dividends @ rs Current stock price Click the tab labeled "Value Based Management"

Actual 2005 14.0% 5.0%

2006 0.000

Projected 2007 0.000

2008 0.442

2009 0.453

5.29 $3.13 $0.57 $3.70 (Difference from price in text is due to rounding.)

Michael C. Ehrhardt

Page 3

8/14/2008

d57e31e4-8204-420f-81b6-d059d79b6c40.xls

Corporate Valuation

Michael C. Ehrhardt

Page 4

8/14/2008

d57e31e4-8204-420f-81b6-d059d79b6c40.xls

Corporate Valuation

Michael C. Ehrhardt

Page 5

8/14/2008

d57e31e4-8204-420f-81b6-d059d79b6c40.xls

Corporate Valuation

Value ofNonoperating assets operations Market Value: Sources Market Value: Claims Book Value: Claims $615 63

Debt

Preferred stock

Market Value of Equity

Book Value of Equity

247.00 247.00

62.00 62.00

369.27 245.00

Michael C. Ehrhardt

Page 6

8/14/2008

d57e31e4-8204-420f-81b6-d059d79b6c40.xls

Corporate Valuation

Michael C. Ehrhardt

Page 7

8/14/2008

d57e31e4-8204-420f-81b6-d059d79b6c40.xls

Corporate Valuation

Michael C. Ehrhardt

Page 8

8/14/2008

d57e31e4-8204-420f-81b6-d059d79b6c40.xls

Corporate Valuation

Market Value Added (MVA)

124.27

Michael C. Ehrhardt

Page 9

8/14/2008

VALUE-BASED MANAGEMENT: BELL ELECTRONICS, INC.

5/14/2004

This is the second section of the Chapter 13 model. Click the tab labeled "Corporate Valuation" to see the other section. Bell Electronics (Bell) has two divisions, Bell Memory and Bell Instruments. The preliminary strategic plans of the two divisions are shown below. At this point, the plans have partial income statements and partial balance sheets containing only the items needed to calculate free cash flow. After the plans are finalized, Bell will complete the statements to determine the necessary financing using the techniques shown in Chapter 12.

Initial Projections for Bell Memory Division (Millions of Dollars, Except for Percentages) Actual Projected Projected Projected Projected Projected 2005 2006 2007 2008 2009 2010 Panel A: Inputs Sales Growth Rate 5% 5% 5% 5% 5% Costs / Sales 81% 81% 81% 81% 81% 81% Depreciation / Net Plant 10% 10% 10% 10% 10% 10% Cash / Sales 1% 1% 1% 1% 1% 1% Acct. Rec. / Sales 8% 8% 8% 8% 8% 8% Inventories / Sales 30% 30% 30% 30% 30% 30% Net Plant / Sales 59% 59% 59% 59% 59% 59% Acct. Pay. / Sales 5% 5% 5% 5% 5% 5% Accruals / Sales 6% 6% 6% 6% 6% 6% Tax rate 40% 40% 40% 40% 40% 40% Panel 2: Partial Income Statement Net Sales Costs (except depreciation) Depreciation Total operating costs Op. profit before int. & tax Panel C: Partial Balance Sheets Operating Assets Cash Accounts receivable Inventories Operating current assets Net plant and equipment Operating Liabilities Accounts Payable Accruals Operating current liabilities 2005 $1,000.0 $810.0 $59.0 $869.0 $131.0 2006 $1,050.0 $850.5 $62.0 $912.5 $137.6 2007 $1,102.5 $893.0 $65.0 $958.1 $144.4 2008 $1,157.6 $937.7 $68.3 $1,006.0 $151.6 2009 $1,215.5 $984.6 $71.7 $1,056.3 $159.2 2010 $1,276.3 $1,033.8 $75.3 $1,109.1 $167.2

2005 $10.0 $80.0 $300.0 $390.0 $590.0

2006 $10.5 $84.0 $315.0 $409.5 $619.5

2007 $11.0 $88.2 $330.8 $430.0 $650.5

2008 $11.6 $92.6 $347.3 $451.5 $683.0

2009 $12.2 $97.2 $364.7 $474.0 $717.1

2010 $12.8 $102.1 $382.9 $497.7 $753.0

$50.0 $60.0 $110.0

$52.5 $63.0 $115.5

$55.1 $66.2 $121.3

$57.9 $69.5 $127.3

$60.8 $72.9 $133.7

$63.8 $76.6 $140.4

Intitial Projections for Bell Instruments Division (Millions of Dollars, Except for Percentages) Actual Projected Projected Projected Projected Projected 2005 2006 2007 2008 2009 2010 Panel A: Inputs Sales Growth Rate 5% 5% 5% 5% 5% Costs / Sales 85% 85% 85% 85% 85% 85% Depreciation / Net Plant 10% 10% 10% 10% 10% 10% Cash / Sales 1% 1% 1% 1% 1% 1% Acct. Rec. / Sales 5% 5% 5% 5% 5% 5% Inventories / Sales 15% 15% 15% 15% 15% 15% Net Plant / Sales 30% 30% 30% 30% 30% 30% Acct. Pay. / Sales 5% 5% 5% 5% 5% 5% Accruals / Sales 6% 6% 6% 6% 6% 6% Tax rate 40% 40% 40% 40% 40% 40% Panel B: Partial Income Statement Net Sales Costs (except depreciation) Depreciation Total operating costs Earning before int. & tax Panel C: Partial Balance Sheets Operating Assets Cash Accounts receivable Inventories Operating current assets Net plant and equipment Operating Liabilities Accounts Payable Accruals Operating current liabilities 2005 $500.0 $425.0 $15.0 $440.0 $60.0 2006 $525.0 $446.3 $15.8 $462.0 $63.0 2007 $551.3 $468.6 $16.5 $485.1 $66.2 2008 $578.8 $492.0 $17.4 $509.4 $69.5 2009 $607.8 $516.6 $18.2 $534.8 $72.9 2010 $638.1 $542.4 $19.1 $561.6 $76.6

2005 $5.0 $25.0 $75.0 $105.0 $150.0

2006 $5.3 $26.3 $78.8 $110.3 $157.5

2007 $5.5 $27.6 $82.7 $115.8 $165.4

2008 $5.8 $28.9 $86.8 $121.6 $173.6

2009 $6.1 $30.4 $91.2 $127.6 $182.3

2010 $6.4 $31.9 $95.7 $134.0 $191.4

$25.0 $30.0 $55.0

$26.3 $31.5 $57.8

$27.6 $33.1 $60.6

$28.9 $34.7 $63.7

$30.4 $36.5 $66.9

$31.9 $38.3 $70.2

PROBLEM Using the projected statements, calculate the free cash flow for each division. Assume growth remains constant FCF Valuation of the Bell Memory Division (Millions of Dollars, Except for Percentages) Actual 2005 $280.0 $590.0 $870.0 $78.6 Projected 2006 $294.0 $619.5 $913.5 $43.5 $82.5 $39.0 Projected 2007 $308.7 $650.5 $959.2 $45.7 $86.7 $41.0 Projected 2008 $324.1 $683.0 $1,007.1 $48.0 $91.0 $43.0 Projected 2009 $340.3 $717.1 $1,057.5 $50.4 $95.5 $45.2 Projected 2010 $357.4 $753.0 $1,110.4 $52.9 $100.3 $47.4

Calculation of FCF: Net operating working capital Net Plant Net operating capital Req'd new investment in op cap NOPAT (Tax rate = 40%) Free cash flow (FCF) Value of Operations: WACC Free cash flow Growth in FCF Horizon value Total cash flow Value of operations = Divisional MVA (Val. of ops - net op. capital) =

10.5% $39.0 $41.0 5% $ 41.0 $43.0 5% $ 43.0 $45.2 5% $ 45.2 $47.4 5% $905.7 $953.1 $ 49.8

$ 39.0 $709.6

($160.4)

FCF Valuation of the Bell Instruments Division (Millions of Dollars, Except for Percentages) Actual 2005 $50.0 $150.0 $200.0 $36.0 Projected 2006 $52.5 $157.5 $210.0 $10.0 $37.8 $27.8 Projected 2007 $55.1 $165.4 $220.5 $10.5 $39.7 $29.2 Projected 2008 $57.9 $173.6 $231.5 $11.0 $41.7 $30.6 Projected 2009 $60.8 $182.3 $243.1 $11.6 $43.8 $32.2 Projected 2010 $63.8 $191.4 $255.3 $12.2 $45.9 $33.8

Calculation of FCF Net operating working capital Net Plant Net operating capital Req'd new investment in op cap NOPAT (Tax rate = 40%) Free cash flow (FCF) Value of Operations WACC Free cash flow Growth in FCF Horizon value Total cash flow Value of operations = Divisional MVA (Val. of ops - net op. capital) =

10.5% $27.8 $29.2 5% $ 29.2 $30.6 5% $ 30.6 $32.2 5% $ 32.2 $33.8 5% $645.1 $678.9 $ 35.5

$ 27.8 $505.5

$305.5

Notes: The horizon value at 2010 is calculated using the constant growth formula for free cash flows: HV 2010 = [FCF2010 * (1+g)] / (WACC-g). The value of operations at 2005 is equal to the PV of horizon value plus the PVs of the free cash flows for 2006 through 2009. You should recognize (1) that the 2005 cash flow has already been realized, hence it does not enter into the calculation of the firm's value, and (2) that the formula used is in exactly the same format as the one for the discounted dividend model. PROBLEM Summarize the key inputs and results for each division. Use Scenario Manager to analyze several different projections for each division: (1) Preliminary projections for each division, based on the initial inputs; (2) Proposal 1, with high growth for Bell Memory (g=6%) and no changes for Bell Instruments; and (3) Final plan, with improved capital requirements for Bell Memory (g=5%, Inventory/Sales = 20%, PPE/Sales = 50%) and high growth for Bell Instruments (g=6%, Inventory/Sales=16%). Scenarios are exactly what the name implies--the model is run using different values for the key drivers so as to show results under different sets of operating conditions. To create a Scenario, click on Tools > Scenarios. In the first dialog box, click Add, give the scenario a name, and then define the cells that you want to let change in the different scenarios. Click OK, and then in the dialog box give the different cells the values that you want for the scenario. Click OK. Click Show, and the values for the scenario will be placed in the cells in the sheet. Repeat for the other scenarios. Click on Close to return to the sheet. Following is a demonstration. Click Tools > Scenarios to bring up the Scenario Manager dialog box shown below. Then click Add.

Now type in the name, such as "Preliminary." Then select the cells that you want to let vary. Then click OK.

Now enter the desired values for the scenario. Click Cancel.

Now click Add to add additional scenarios. Then click Close.

To show a Scenario, click on Tools > Scenarios. Select a scenario and then click on Show. Click on Close to return to the worksheet. For example, to show the Proposal 1, follow the instructions below. Results are shown below this.

Note: The Preliminary scenarios are always shown in columns C and E. The current scenarios are shown in columns D and F. To change the current scenario, Click Tools > Scenarios, and then select the desired scenario. Bell Memory Bell Instruments Preliminary Preliminary Preliminary Preliminary 5% 30% 59% 5% 7.9% 87.0% 10.5% 5% 30% 59% 5% 7.9% 87.0% 10.5% 5% 15% 30% 5% 7.2% 40% 10.5% 5% 15% 30% 5% 7.2% 40% 10.5%

Scenario name Inputs: Growth, g Inventory/Sales PPE/Sales Value Drivers: Growth, g Profitability (NOPAT2010 / Sales2010) Capital requirement (Capital2010 / Sales2010) WACC Results: Expected ROIC (NOPAT2010 / Capital2010) Operating Capital (2010) Current value of operations (2005) Current MVA (2005)

9.5% $1,110.4 $709.6 ($160.4)

9.5% $1,110.4 $709.6 ($160.4)

18.9% $255.3 $505.5 $305.5

18.9% $255.3 $505.5 $305.5

Notice that in the Preliminary scenario, Bell Memory has a negative MVA. This is because its Expected ROIC is less than the WACC, so Bell Memory is earning less on its capital than its investors require. Notice that in Proposal 1, growth goes up for Bell Memory but value goes down. Again, this is because the Expected ROIC is less than the WACC. In the Final scenario, Bell Memory's Expected ROIC improves because it becomes the division requires less capital to support its sales. This causes the ROIC to become greater than the WACC, leading to positive MVA.


				
DOCUMENT INFO
Shared By:
Stats:
views:770
posted:1/14/2008
language:English
pages:18