2/1/12

Module 09. Student Ch 09-11 Build a Model

Figure 1. Financial Statements and Other Data (Millions except per share data)

Balance Sheet, Matthews, 12/31/12

Income Statement, Matthews

2012

Cash and securities

$120

Sales

$12,000.0

Accounts receivable

1,584

Total operating costs

11,040.0

Inventories

2,145

EBIT

$960.0

Total current assets

$3,849

Interest

250.0

Net fixed assets

2,748

EBT

$710.0

Total assets

$6,597

Taxes(40%)

284.0

Net income

$426.0

Accounts payable + accruals

$747

Dividends (45%)

$191.7

Notes payable

1,200

Add'n to retained earnings

$234.3

Total current liabilities

$1,947

Shares outstanding

150.000

Long-term debt

1,300

EPS

$2.84

Total liabilities

$3,247

DPS

$1.28

Common stock

2,250

Year-end stock price

$22.72

Retained earnings

1,100

Total common equity

$3,350

Total claims on assets

$6,597

Selected Ratios and Other Data 2012

Matthews

Industry

Sales, 2012 (S0):

$12,000

$12,000

Expected growth in sales:

10.0%

10.0%

Profit margin (M, or PM):

3.55%

4.50%

Assets/Sales (A₀*/S₀):

55.0%

50.0%

Payout ratio (POR):

45.0%

40.0%

Equity multiplier (Assets/Equity):

1.97

1.67

Debt ratio (Total debt/Total claims):

49.2%

40.0%

Times interest earned (EBIT/Interest):

3.84

4.20

Increase in sales (S = gS₀):

$1,200

$1,200

(Payables + Accruals)/Sales (L₀*/S₀):

6.2%

4.0%

Operating costs/Sales:

92.0%

85.0%

Cash/Sales:

1.0%

1.0%

Receivables/Sales:

13.2%

10.0%

Inventories/Sales:

17.9%

15.0%

Fixed assets/Sales:

22.9%

20.0%

Tax rate:

40.0%

40.0%

Interest rate on all debt:

10.0%

9.5%

Price/Earning (P/E):

8.0

12.5

ROE (Net income/Common equity):

12.72%

15.00%

a. Assume that the firms 2012 profit margin, payout ratio, capital intensity ratio, and spontaneous liabilities to sales ratio remain constant. If sales grow by 10% in 2013, what is the required external capital the firm will need in 2013 as calculated by the AFN equation?

AFN_Matthews =

Add'l Req'd Assets

-

Spontaneous liabilities

-

Add'n to RE

=

(A₀*/S₀)S

-

(L₀*/S₀)S

-

S₁ M (1POR)

=

(A₀*/S₀)(gS₀)

-

(L₀*/S₀)(gS₀)

-

S₁ M (1POR)

=

-

-

AFN_Hatfield =

million

b. If 2012 ratios remain constant, what is Matthews self-supporting growth rate? How will the self-supporting growth rate change if each of the following changes occur: (1) the profit margin declines, (2) the payout ratio increases, or (3) the capital intensity ratio declines?

Self-Supporting Growth Rate. This is the maximum growth rate that can be attained without raising external funds, i.e., the value of g that forces AFN = 0, holding other things constant. We found this rate, g = 4.1722%, with Excel's Goal Seek function and also algebraically, as explained below.

1. Using algebra. The self-supporting growth rate can also be found by solving the equation as shown on the 3rd row above AFN, then finding the value of g that causes AFN to equal zero. This results in the same value as we find with Goal Seek. The algebriac solution is easy if we give you the equation, but if you had to solve the AFN equation for g, you would probably find the Goal Seek solution easier.

PM(1 POR)(S₀)

Self-supporting g =

=

=

A₀* L₀* PM(1 POR)S₀

Therefore, if the firm's ratios remain constant, the company can grow at about 4.17% without

external financing.

2. Using Goal Seek. To find the self-supporting growth rate with Goal Seek, first highlight cell B56. Then, with Excel 07, on the Main Menu bar click Data>What-If-Analysis>Goal Seek. With Excel 03 click Tools>Goal Seek. Then complete the dialog box as shown to the right. When you click OK, Cell D25 will change to 4.1722%, which will cause Cell B56 to change to $0.00. Record the new growth rate and then return to the base case by clicking Cancel. Or, you could click OK to leave the new growth rate in Cell D25 and then over-type it with 10% in that cell to get back to the base case.

Goal Seek is one of Excel's most useful features. We use it elsewhere in this chapter to find the required amount of new capital. In capital budgeting, we use it to see how high the WACC can go before the NPV becomes negative, how low the WACC must be for the NPV to be positive, how low the initial cost must be to achieve a positive NPV, how long a project must last to achieve a positive NPV, and so forth. We have worked on real world cases dealing with almost every chapter in the text, and we almost always have occasion to use Goal Seek. We can't overemphasize its usefulness.

c. Matthews management has reviewed its financial statements and arrived at two possible scenarios for 2013. The first scenario assumes a steady state while the second scenario, the target scenario, shows some improvement in ratios toward industry-average values. Forecasted values for the scenarios are shown in the partially completed file Ch09 P11 Build a Model.xls. If Matthews assumes that external financing is achieved through notes payable and financing feedbacks are not considered because the new notes payable are added at the end of the year, what are the firms forecasted AFN, EPS, DPS, and year-end stock price under each scenario?