Chapter 12. Tool Kit for Financial Planning and Forecasting Financial Statements PDF

Preview

Summary

Chapter 12. Tool Kit for Financial Planning and Forecasting Financial Statements, Chapter 12. Tool Kit for Financial Planning and Forecasting Financial Statements...

Description

Tab 1 Chapter 12. Tool Kit for Financial Planning and Forecasting Financial Statements On Tab 1 we do the calculations for the AFN formula, using MicroDrive's data as presented in Ch Then, on Tab 2, we forecast the firm's financial statements and analyze the results. On Tab 3 we f using different finanical policies from those in Tab 2. In Tab 4 we explain financing feedback. On demonstrate multi-year forecasts.

SALES FORECAST (Section 12.2) Strategic planning is one of the core functions of an organization, and it involves the coordinatio operating plans with financial plans. While operational plans outline how the firm intends to reac corporate objectives, financial plans outline the manner in which the firm will obtain the necessa productive assets to operate. Financial planning generally begins with a sales forecast, and that generally starts with a review of the firm's recent history. Here are MicroDrive Inc.'s sales over th years:

Sales $2,058 2,534 2,472 2,850 3,000

2006 2007 2008 2009 2010

Annual Growth Rate 23.1% -2.4% 15.3% 5.3%

Figure 12-1. MicroDrive Inc.: Historical Sales (Millions of Dollars)

Net Sales $3,500

$3,000

$2,500

$2,000

$1,500

$1,000

$500

$0 2006

2007

2008

2009

2010

Year2

There are several ways to estimate the historical growth rate, ranging from the simple to the com ways are to estimate the arithmetic average annual growth rate and the compound (geometric) an Arithmetic average annual growth rate =

10.3%

Compound (geometric) annual growth rate =

9.9%

(Use the RATE function

Both approaches have flaws. For example, suppose sales start at 100, drop by 20% to 80, then in arithmetic approach would find the average of -20% and +25%, which is +2.5%, even though sale at 100. The geometric average is too senstive to the starting and ending values (the PV and FV us

It would be easy to estimate the projected sales by fitting a regression to the graph and plotting t ahead. An easy way to do this is with the TREND function. This allows you to specify the past ye specify a projected year. It then fits the regression line and gives you the projected value. See b Projected sales for Implied growth rate =

2011 8.1%

=

3,243

(Using the TREND func

The compound growth rate is very sensitive to the particular starting and ending dates that are c this out is to regress the natural log (LN) of sales versus the years. The slope coefficient is the e sales growth rate. Instead of doing a full regression with the Y variable being the log of sales, we "log" regression directly using the LOGEST function. In this function, we simply specify the orig the years as the X variable, and the function finds the "log-based" slope coefficient, which is an e (1+g) rate using LOGEST = 1.091035801 g= 9.1% MicroDrive's managers took these different estimates into consideration, along with their knowle economy, the industry, and MicroDrive's own situation. Their best estimate was a 10% forecast g sales for the next year. But as you will see, MicroDrive's managers also performed sensitivity ana this critical input when they projected their financial plans.

Additional Funds Needed (AFN) (Section 12.3) Figure 12-2. MicroDrive's Most Recent Financial Statements (Millions of Dollars Except INCOME STATEMENTS Sales Costs except depreciation Depreciation Total operating costs EBIT Less interest (INT) Earnings before taxes (EBT) Taxes (40%)

2009 $2,850.0 2,497.0 90.0 $2,587.0 263.0 60.0 $203.0 81.2

2010 $3,000.0 2,616.2 100.0 $2,716.2 283.8 88.0 $195.8 78.3

BALANCE SHEETS Assets Cash ST Investments Accounts receivable Inventories Total current assets Net plant and equip. Total assets

2009 $15.0 65.0 315.0 415.0 $810.0 870.0 $1,680.0

Income before pref. dividends Preferred dividends Net income for common (NI)

$121.8 4.0 $117.8

$117.5 4.0 $113.5

Dividends to common (DIVs) Add. to retained earnings: (NI – DIVs) Shares of common stock Earnings per share (EPS) Dividends per share (DPS) Price per share (P)

$53.0

$57.5

$64.8 50 $2.36 $1.06 $26.00

$56.0 50 $2.27 $1.15 $23.00

Liabilities and equity Accounts payable Accruals Notes payable Total current liab. Long-term bonds Total liabilities Preferred stock Common stock Retained earnings Total common equity Total liab. & equity

$30.0 130.0 60.0 $220.0 580.0 $800.0 40.0 130.0 710.0 $840.0 $1,680.0

Figure 12-3. Additional Funds Needed (AFN), in millions The AFN model forecasts MicroDrive's need for external funds to support its forecasted 2011 sal has just ended, and Year 1 is 2011, which has just begun. (Ignore rounding differences.) Part I. Inputs and Definitions S 0: Last year's sales, i.e., 2010 sales: g: Forecasted growth rate in sales: Coming year's sales, i.e., 2011 sales = S 0 × (1 + g): S 1: gS0: A0*: A0* / S 0: L0*: L0* /S0: Profit margin (M): Payout ratio (POR):

Change in sales = S 1 – S0 = ΔS: Assets that must increase to support the increase in sales: Required assets per dollar of sales: Last year's spontaneous assets, i.e., payables + accruals: Spontaneous liabilities per dollar of sales: 2010 profit margin = net income/sales: Last year's dividends / net income = % of income paid out:

Part II. Additional Funds Needed (AFN) to Support Growth AFN

= =

Required Increase in Assets (A0*/S0)∆S

=

(A0*/S0)(gS0)

= = AFN =

(0.667)($300) $200 $118.42 million

− − − − − − −

in Payables and (L0*/S0)∆S (L0*/S0)(gS0) (0.067)($300) $20.00

− − − − − − −

Under the assumed conditions, the firm must raise $118.42 million externally to support its plann model assumes (1) that no excess capacity existed in 2010, so all assets were needed to produce that the key ratios will remain constant at their 2010 levels. We explain later how to relax these a to use forecasted financial statements to deal with these issues, as we do on Tab 2 of the model. Self-Supporting Growth Rate. This is the maximum growth rate that can be attained without raisi value of g that forces AFN = 0, holding other things constant. We found this rate, g = 3.20986%, w function and also algebraically, as explained below. 1. Using algebra. The sustainable growth rate can also be found by solving the equation as show then finding the value of g that causes AFN to equal zero. This results in the same value as we fi algebriac solution is easy if we give you the equation, but if you had to solve the AFN equation fo find the Goal Seek solution easier.

PM(1 – POR)(S0) Sustainable g

=

$55.98 =

= 3.20986% A0* – L0* – PM(1 – POR)S 0 $1,744.00 Therefore, if MicroDrive's ratios remain constant, the company can grow at about 3.21% without

2. Using Goal Seek. To find the sustainable growth rate with Goal Seek, first highlight cell B152. Main Menu bar click Data>What-If-Analysis>Goal Seek. With Excel 03 click Tools>Goal Seek. The as shown below. When you click OK, Cell I133 will change to 3.209862%, which will cause Cell B you will see the dialog box below and to the right. Record the new growth rate now in Cell I133 a case by clicking Cancel. Or, you could click OK to leave the new growth rate in Cell I133 and the 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 capital. In capital budgeting, we use it to see how high the WACC can go before the NPV become WACC must be for the NPV to be positive, how low the initial cost must be to achieve a positive N must last to achieve a positive NPV, and so forth. We have worked on real world cases dealing w the text, and we almost always have occasion to use Goal Seek. We can't overemphasize its use

EXCESS CAPACITY ADJUSTMENTS production with its fixed assets. Also, assume that it could sell off its redundant FA at their book year were $3 000 million while fixed assets were $1 000 million We can use this information to 2010 Sales 2011 Sales 2010 Total Assets 2010 Fixed Assets Original 2010 FA/Sales ratio = $1,000/$3,000 FA capacity was used only to this percent FA that were actually needed during 2010 = 0.96($1,000) Excess Fixed Assets for 2010 A0* / S0 if Fixed Assets had been operated at full capacity = (TA – Excess)/Sales Previously calculated AFN AFN using revised ratio in the AFN equation Reduction in AFN due to reduced need for Fixed Assets Obviously, the reduction in AFN would have been greater if there had been more excess capacity be sold at book value, MicroDrive could (1) sell off $40 of fixed assets to bring them down to the new FA/S ratio for the forecasted AFN, taking AFN down to $114.42. Since the company would re bring its total AFN for 2011 down to $114.42 – $40 = $74.42. The $40 is a one-time reduction and FCF.

7/21/2010

hapter 2. forecast Tab 5 we

on of ch its ary forecast he past 5

r2011

mplicated. The simplest nnual growth rate.

n.)

ncrease by 25% to 100. The s started at 100 and ended sed in the rate function.

the predicted sales 1 period ears and sales, and then below for details. ction)

chosen. One way to smooth estimate of the historical e could find the slope of the ginal sales as the Y variable, estimate of (1+g).

edge of the growth in alysis on

Per Share Data) 2010 $10.0 0.0 375.0 615.0 $1,000.0 1,000.0 $2,000.0

$60.0 140.0 110.0 $310.0 754.0 $1,064.0 40.0 130.0 766.0 $896.0 $2,000.0

es. Year 0 is 2010, which

$3,000 10.000000% $3,300 $300 $2,000 66.67% $200 6.67% 3.78% 50.67%

Addition to Retained Earnings S1 × M × (1 – POR) (1+g)S0 × M × (1 – POR) $3,300(0.0378)(1 – 0.507) $61.58

ned growth. However, the e the indicated sales, and (2) assumptions, but it is better . ing external funds, i.e., the with Excel's Goal Seek

wn on the 3rd row above g, ind with Goal Seek. The or g, you would probably

external financing.

. Then, with Excel 07, on the en complete the dialog box B152 to change to $0.00, and and then return to the base n over-type it with 10% in

required amount of new es negative, how low the NPV, how long a project with almost every chapter in efulness.

k value. Sales for the last o calculate the firm's full $3,000 $3,300 $2,000 $1,000 33.33% 96% $960 $40 65.333% $118.42 $114.42 $4.00 y. Also, assuming FA can required level, then use the eceive the $40, this would thus a one-time increase in

Tab 2

7/21/2010

FINANCIAL STATEMENT FORECASTING

(Section 12.4)

On this tab we forecast MicroDrive's financial statements for the upcoming year and then calculate rat other data to analyze the firm's projected financial condition. Initially, we base the projection on the m year's data, which amounts to a "Status Quo" forecast. We then add three scenarios, one where the c achieves industry average ratios (the Best-Case Scenario), one designed to show what would happen economy goes into an even deeper recession (the Worst-Case Scenario), and a Final forecast that was at the end of the firm's planning conference. We begin by repeating MicroDrive's most recent financial statements for convenience, including addit concerning interest rates and investor-supplied capital.

MicroDrive's Most Recent Financial Statements (Millions of Dollars Except Per Share Data) INCOME STATEMENTS Sales Costs except depreciation Depreciation Total operating costs EBIT Less Interest (INT) Earnings before taxes (EBT) Taxes (40%) Income before pref. dividends Preferred dividends Net income for common (NI)

2009 $2,850.0 2,497.0 90.0 $2,587.0 263.0 60.0 $203.0 81.2 $121.8 4.0 $117.8

Dividends to common (DIVs) $53.0 Add. to retained earnings: $64.8 (NI – DIVs) Shares of common stock 50 Earnings per share (EPS) $2.36 Dividends per share (DPS) $1.06 Price per share (P) $26.00 2010 interest rate on notes payable: 2010 interest rate on long-term bonds:

2010 $3,000.0 2,616.2 100.0 $2,716.2 283.8 88.0 $195.8 78.3 $117.5 4.0 $113.5 $57.5 $56.0 50 $2.27 $1.15 $23.00 9.00% 11.00%

BALANCE SHEETS Assets Cash ST Investments Accounts receivable Inventories Total current assets Net plant and equip Total assets

2009 $15.0 65.0 315.0 415.0 $810.0 870.0 $1,680.0

Liabilities and equity Accounts payable Accruals Notes payable Total current liabs Long-term bonds Total liabilities Preferred stock Common stock Retained earnings Total common equity Total liabs. & equity Investor-supplied capital*

$30.0 130.0 60.0 $220.0 580.0 $800.0 40.0 130.0 710.0 $840.0 $1,680.0 $1,520.0

*Investor-supplied capital consists of notes payable, long-term bonds, preferred stock, and total comm It is also equal to total liabilities and equity minus accounts payable and accruals. Accounts payable a are not included in investor-supplied capital because they are a part of operations and are not sources from investors. Input Data for the Forecast Following is an explanation of the input data and structure of the model shown below. Forecasting financial statements requires the use of a set of equations that must be solved in a specif sequence. However, the steps are relatively straightforward, so you should not have trouble following

if you proceed slowly and carefully. Financial managers typically make such models, but managers fr departments provide inputs and are affected by the results, hence need a general understanding of fin forecasts.

MicroDrive initially forecasts a 10% sales growth rate. Prior year data for the industry and MicroDrive in Columns C and D for Figure 12-4 below. The most recent data are used for the Status Quo forecast. Best-Case forecast assumes the company operates at industry average levels except for capital struct used in the Final forecast were developed after a lengthly planning meeting attended by all of the firm' managers. Data for these four cases are shown in Columns F, G, H, and I, which are color coded. No data in Columns F, G, H, and I are not used directly in the calculations. Rather, when we "show" scena the Scenario Manager, the values in the corresponding scenario are placed into Column E, which we as the "active column" because its data are being used in the actual calculations.

The orange colored rows in the Financing Data section deal with capital structure. Those 4 items mus 100%. The capital structure ratios are held constant in all cases except for the Final scenario. If you change any individual number in Column E, this will lead to a new forecast, using this one new n along with the other numbers then in Column E. Thus, you can do sensitivity analyses for each variab The model uses the Column E data to forecast the statements shown in Figure 12-5. The model is buil series of equations, and Excel inserts data from Figure 12-4 into the equations in Figure 12-5. The seq the equations as discussed in Part 3 of Figure 12-5 is important. We use Excel's Scenario Manager to execute the different scenarios. Scenario Manager in essence ta from the four color coded sets of inputs in Figure 12-4, inserts this data into the calculating model in F and then gets results for the different scenarios. For those who are interested, we explain how to use scenario tool off to the right, starting in Column M. Figure 12-4. Input Data and Key Results for the Forecast (Millions Except Percentages and Per Share

Inputs

2011 Forecasted Input Values for

2010 Actual Values Industry

MicroDrive

Operating Ratios: Growth rate in sales Op costs except depr'n / Sales Depr'n / Net plant & equip. Cash / Sales Accounts Rec. / Sales Inventory / Sales Net plant & equip. / Sales Accounts Pay. / Sales Accruals / Sales Tax rate:

10.00% 83.00% 10.20% 0.25% 9.80% 11.11% 33.33% 2.00% 4.00% 40.00%

5.26% 87.21% 10.00% 0.33% 12.50% 20.50% 33.33% 2.00% 4.67% 40.00%

Financing Data: Notes payable/Investor-sup cap LT bonds/Investor-sup capital Pref. stock/Investor-sup cap. Comm equity/Investor-sup cap

5.00% 32.00% 3.00% 60.00%

6.11% 41.89% 2.22% 49.78%

Active Scenario: Final 10.00% 86.00% 10.20% 0.25% 11.00% 16.00% 33.33% 2.00% 4.00% 40.00% 5.00% 37.00% 3.00% 55.00%

Status Quo 10.00% 87.21% 10.00% 0.33% 12.50% 20.50% 33.33% 2.00% 4.67% 40.00% 6.11% 41.89% 2.22% 49.78%

Best 10.00% 83.00% 10.00% 0.33% 9.80% 11.11% 33.33% 2.00% 2.00% 40.00% 6.11% 41.89% 2.22% 49.78%

Interest rate on notes payable Interest rate on L-T bonds Dividend rate on pfd stock Target dividend payout ratio

8.00% 10.00% 9.00% 40.00%

Key Results Free cash flow (FCF) Return on inv. capital (ROIC) Earnings per share (EPS) Return on equity (ROE) # Shares, end-of-year Dividends per share (DPS)

9.00% 11.00% 10.00% 50.67% 2010 MicroDrive -$174.70 9.46% $2.27 12.67% 50.00 $1.15

8.50% 10.50% 9.50% 40.00% 2011 Final $208.61 11.65% $3.11 15.70% 50.06 $1.24

9.00% 11.00% 10.00% 50.67% 2011 Status Quo $7.35 9.46% $2.43 12.64% 51.22 $1.23

8.50% 10.50% 9.50% 50.67% 2011 Best $401.57 16.21% $5.07 25.97% 42.54 $2.57

The forecasting model is in Figure 12-5. It starts with the firm's 2010 data, pulls "input factors" down f Column E of Figure 12-4, and then uses these factors as indicated in the "Forecast Basis" column to m forecasted balance sheet and income statement shown in Column G. The factors change with each s causing the forecasts to change. The forecasts for operating current assets and spontaneous current straightforward, and the model calculates them first. The way the other items are forecasted is a bit co but we explain them in Part 3 of the figure, the Supplemental calculations section.

Figure 12-5. Forecasted Financial Statements (Millions Except Per Share Data) Scenario Shown: Final Part 1. Balance Sheet Assets Cash Accounts receivable Inventories Total current assets Net plant and equipment Total assets (TA) Liabilities and equity Accounts payable Accruals Notes payablea Total current liabilities Long-term bondsa Total liabilities Preferred stocka Common stock Retained earnings Total common equitya Total liabilities and equity a

Investor-supplied capital

Most Recent 2010 Factors $10.0 375.0 615.0 $1,000.0 1,000.0 $2,000.0 $60.0 140.0 110.0 $310.0 754.0 $1,064.0 $40.0 130.0 766.0 $896.0 $2,000.0

Basis for 2011 Forecast

0.25% 11.00% 16.00%

Factor × 2011 Sales Factor × 2011 Sales Factor × 2011 Sales

33.33%

Factor × 2011 Sales

2.00% 4.00% 5.00%

Factor × 2011 Sales Factor × 2011 Sales % of investor-sup. cap.

37.00%

% of investor-sup. cap.

3.00%

% of investor-sup. cap. Tot. Com. Eq – Ret. Earn Old RE + Add. to RE % of investor-sup. cap.

55.00%

$1,800.0

TA − accts. pay. − accruals

Scenario Shown: Final Part 2. Income Statement Sales

Most Recent 2010 Factor $3,000.0 110%

Basis for 2011 Forecast Factor × 2010 Sales

Final Forecast 2011 $8.25 $363.00 $528.00 $899.25 $1,100.00 $1,999.25 $66.00 $132.00 $90.06 $288.06 $666.46 $954.52 $54.04 $131.37 $859.32 $990.69 $1,999.25 $1,801.25 Final Forecast 2011 $3,300.00

Costs except depreciation Depreciation Total operating costs EBIT Less: Interest on notes Interest on bonds Earnings before taxes (EBT) ...