Finance
Chapter 5
Edward C. Boyer, 2013
FINANCIAL FORECASTING
In this chapter you will learn how to:
1. Develop simple financial forecasts based on the percent of sales methodology.
2. Use the Goal Seek command to perform this forecast.
3. Develop multi-year financial forecasts.
Financial Forecasting 78
Financial forecasting can be a fairly tedious exercise but one that is quite important for
both managers and analysts nonetheless. Corporate managers may use financial forecasting in
order to understand the value implications of various operating strategies. In addition, financial
forecasting is absolutely necessary for financial planning as the firm looks out into the future.
That is, as managers forecast sales, financial forecasting will assist in determining the assets
required to support those sales, the amount of financing necessary to acquire those assets, and the
implications of various alternative financing strategies. For the analyst, financial forecasting is
necessary in order to forecast the firm’s cash flow, which is the foundation of the valuation task.
The purpose of this chapter is to teach students about the basics of financial forecasting
and to draw out the important complexities. We will begin with the iterative approach taught in
most financial management and corporate finance textbooks. Then we will see how the Goal
Seek command makes this process very easy also enabling us to develop multi-year forecasts.
More importantly, the Goal Seek approach focuses students’ attention to the implications for
financial planning resulting from different growth prospects. Although the approach in this
chapter does shed some light on the financial planning task, financial planning is not the subject
of this chapter.
Important points to remember:
1. Financial forecasting involves developing pro forma income statements and balance sheets
the purpose of which is to determine the financing requirements necessary to sustain the
firm’s growth over a short-term horizon.
2. Financial forecasting is not to be confused with financial planning. Financial planning is
determining how to finance investments, with, for example, debt, equity or some combination
of those.
3. The percent of sales forecasting methodology, discussed below, is based on an assumption
that the firm’s expenses on the income statement, as well as its assets on the balance sheet
will also increase in proportion to the growth in sales.
5.1 Financial Forecasting
Financial forecasting involves developing pro forma income statements and balance
sheets the purpose of which is to determine the financing requirements necessary to sustain the
firm’s growth over a short-term horizon. Why is financial forecasting important? If the firm
expects sales to increase, then this sales growth will require an increase in assets necessary to
support the growth in sales. For example, the firm will perhaps need to carry more inventories
among other assets. In fact, it may need to increase investments in fixed plant and equipment. If
such increases in assets are going to be necessary, the firm must finance these additions to
inventory and to plant and equipment.
Financial Forecasting 79
Financial forecasting is not to be confused with financial planning. Financial planning
involves decisions regarding the firm’s capital structure and dividend policy. That is, once the
firm has decided what projects it will invest in and how much capital it will need, then it must
decide whether to use debt, equity or some combination. Financial planning goes another step
beyond financial forecasting.
There are various financial forecasting models. This chapter focuses on the percent of
sales method. The real purpose of this discussion is to familiarize you with some very basic ideas
and concepts of financial forecasting. These basic ideas are as follows: The firm will first
forecast sales over a given horizon, say five years. The next step is to determine what impact an
increase in sales will have on the assets the firm needs in order to produce and sell its products as
dictated by these forecasts. Finally, the firm will determine the amount of financing required in
order to purchase these assets.
5.2 Percent of Sales Method
The percent of sales forecasting methodology is based on an assumption that the firm’s
expenses on the income statement, as well as its assets and certain liabilities on the balance sheet
will also increase in proportion to the growth in sales. We assume that expenses and depreciation
change in the same manner. However, it is not necessarily true that this latter assumption will
hold in practice. For example, if the firm is experiencing economies of scale, then expenses
should be increasing at a slower rate than sales. It also might be necessary for the firm to invest
in a new plant that would result in a big increase in the asset base so that plant and equipment
will not increase smoothly as sales increase. Nonetheless we will maintain these assumptions for
simplicity.
The basic mechanics of financial forecasting begin with a sales forecast. 1 The reason for
this is that the forecast in sales drives everything else on the forecasted income statement and
balance sheet. If sales are expected to increase, then the firm will need additional assets on the
forecasted balance sheet to support these sales and expenses on the income statement forecast
will also increase. Once we have determined the amount of assets necessary to support sales, we
need to determine how much financing is necessary to acquire the augmented assets. If the firm
is all-equity financed this is an easy task. When the firm also uses debt financing, what makes
financial forecasting problematic is that there are certain interrelationships between the financing
of assets on the balance sheet and the cost of financing those assets on the income statement.
This means that forecasting the income statement and balance sheet is an iterative procedure.
In order to see this problem, let us first assume that the firm is all-equity financed. The
income statement and balance sheet are shown in figure 1. Equity consists of common stock, par
plus paid-in capital, and retained earnings. The firm has no debt financing.
1 Analysts will determine sales forecasts based on assumptions about the growth in the economy, the industry and the
firm’s competitive position within the industry.
Financial Forecasting 80
Figure 1
Income Statement Assets
Sales Current Assets
COGS Fixed Assets
Gross Profit Total Assets
Expenses Liabilities and Equity
EBIT Current Liabilities
Tax Common Stock
Net Income Retained Earnings
Total Liabilities and Equity
To focus on the financing element, suppose that we subtract current liabilities from both sides of
the balance sheet. This is illustrated in Figure 2. We will now call the asset side of the balance
sheet Net Assets, meaning net of current liabilities. The financing side of the balance sheet
consists solely of equity financing. As sales increase so will net total assets required to support
sales. In addition, net income will increase and, assuming the firm does not pay dividends,
retained earnings on the balance sheet will increase. To finance these assets the firm must rely of
retained earnings and possible issue new common stock. During the forecasting process, we can
determine how much new financing the firm would need by simply subtracting total equity from
net total assets. If the firm does not want to issue new stock then growth is constrained to how
much the firm can finance through retained earnings. Let us see how the forecasting process
differs when the firm also uses debt financing.
Figure 2
Income Statement Net Assets
Sales Current Assets
COGS (Current Liabilities)
Gross Profit Fixed Assets
Expenses Net Total Assets
EBIT Equity
Tax Common Stock
Net Income Retained Earnings
Total Equity
Figure 3 adds debt financing to the balance sheet in Figure 2. Let us assume that growth
exceeds what could be financed with retained earnings and the firm does not want to issue new
equity, but chooses instead to issue long-term debt. The use of long-term debt will require that
the firm pay interest and so net income will not increase proportionally and neither will retained
earnings. The example below shows why this forecasting exercise becomes an iterative process.
Financial Forecasting 81
Figure 3
Income Statement Net Assets
Sales Current Assets
COGS (Current Liabilities)
Gross Profit Fixed Assets
Expenses Net Total Assets
EBIT Long-Term Debt and Equity
Interest Expense Long-Term Debt
EBT Common Stock
Tax Retained Earnings
Net Income Total Liabilities and Equity
Suppose, for example, we take a very naïve approach to forecasting the income statement
and balance sheet as illustrated in Figures 4 and 5. If the forecast calls for an increase in sales
revenue then operating expenses on the income statement will also increase. We can then
calculate net income as the new revenue minus all expenses, as usual. As the sales increase, we
will need additional assets to support these sales. Inventories, for example, would probably
increase. To purchase these assets, the firm may likely have to borrow more money, to the extent
that internal financing using retained earnings falls short. We could estimate how much by
simply subtracting total liabilities and equity from total assets. This difference is often referred
to as the additional funds needed. However, this would ignore some very important relationships
between the income statement and the balance sheet.
The first important relationship that the above procedure ignores is the interest expense
on the income statement. If the firm borrows more money the interest expense on the income
statement will increase and that implies that the net income on the forecasted income statement
will be overestimated. However, the impact does not end here. What is left of net income after
the payment of dividends is the addition to retained earnings. This addition to retained earnings
feeds onto the balance sheet account of retained earnings and provides a very important source of
financing for new assets. So, our forecasted balance sheet would include an overestimate of the
amount of retained earnings available for new investment and an underestimate of the additional
funds needed to finance investment. Therefore, the implied new amount of debt financing would
be understated. The forecasted income statement and balance sheet shown below illustrate how
we can handle this situation and provide consistent forecasts.
Before we go into the forecasting in detail, let us take a quick look at the process. In
Figure 4 column C gives the actual values for 2011. Columns D through G represent 4 iterations
required to solve the forecasting of the income statement and balance sheet for 2012. Note that
the interest expense is on the first iteration is $74 and the addition to retained earnings is $85. In
the remaining iterations, the interest expense increases and the addition to retained earnings
declines. (If these items were carried out to 3 decimal places this claim would be more obvious.)
Figure 5 is the balance sheet forecast. Note that the additional funds needed at the bottom of
column D is $232.73. Note also that this figure declines in succeeding iterations. This number
represents the difference between the forecasted assets for 2012 and the total liabilities and equity
for 2012. The total liabilities and equity is $3586 and includes the new retained earnings from
column D on Figure 4. The additional funds needed of $232.73 is going to be the increase in
long-term debt shown in column E on the balance sheet, which is now $1249. This means that
Financial Forecasting 82
the interest expense on the income statement must be higher than predicted by iteration 1 on the
income statement. Column E shows that the interest expense will be $91 instead of $74 and
therefore, the addition to retained earnings will be $82 instead of $85. If we continue this
procedure for several more iterations, we will achieve a level of debt that will make the balance
sheet balance and where the additional funds needed will be $0.00.
Figure 4
A B C D E F G
1 Growth 12.0%
2 Income Statement 2011 2012 (1) 2012 (2) 2012 (3) 2013 (4)
3 Revenue $4,291 $4,806 $4,806 $4,806 $4,806
4 Cost of goods sold 58.1% (2,494) (2,793) (2,793) (2,793) (2,793)
5 Gross profit 1,797 2,013 2,013 2,013 2,013
6 Selling, general and administrative 15.8% (680) (762) (762) (762) (762)
7 R&D expenses 11.9% (510) (571) (571) (571) (571)
8 Depreciation 2.7% (118) (132) (132) (132) (132)
9 EBIT $489 $548 $548 $548 $548
10 Interest expense 8.00% (75) (74) (91) (91) (91)
11 Earnings before tax $414 $474 $457 $457 $457
12 Tax 40.0% (166) (189) (183) (183) (183)
13 Net income $249 $284 $274 $274 $274
14
15 Dividends 70.0% (174) (199) (192) (192) (192)
16 Addition to retained earnings $75 $85 $82 $82 $82
Let us now look at how the numbers are calculated. For the forecast above, it is assumed
that the growth in sales revenue will be 12% for year 2012 so cell D3 reads =C3*(1+D1). (See
Chapter 1 for writing formulas in cells.) To begin the forecast, we must calculate certain line
items as a percent of sales. We will forecast cost of goods sold; selling, general and
administrative; depreciation and R%D on a percent of sales basis. Cost of goods sold is 58.1%
of sales. (This is calculated as =-C4/C3.) Therefore, if revenues increase by 10%, expenses will
increase to $2,793 which is calculated as =$B4*D3. All profit items are calculated as the sum of
the line items above them so that gross profit is =D3+D4. Interest expense, taxes and dividends
are not percent of sales calculations. Interest expense, in this case based on an interest rate of
8.0%, is calculated as the interest rate times the average long-term debt on the balance sheet. So,
the interest expense of $74 in the first iteration of 2012 is the 8.0% times the long-term debt for
2011 and 2012 divided by 2 or =B6*(‘Balance Sheet’!C18+‘Balance Sheet’!D18)/2. (Recall
that the name Balance Sheet is the name in the tab of that particular Excel worksheet.) The tax
forecast is based on a tax rate of 40% and is based on taxable income. In this case, the $189 in
tax expense is 40% of $474. Finally, dividends are 70% of net income. The difference between
net income and dividends is called the addition to retained earnings. This addition to retained
earnings goes to the stockholders’ equity account on the balance sheet.
Note that in the previous paragraph there were two linkages between the income
statement and the balance sheet. The first linkage was the interest expense since the amount of
Financial Forecasting 83
interest paid depends on the amount of debt the firm assumes. The more debt, the greater the
interest expense and the lower will be net income and therefore, retained earnings. The second
link is the addition to retained earnings which feeds onto the forecasted balance sheet equity
account of retained earnings.
Let us now set up the balance sheet to see how it participates in the forecast. The balance
sheet is also divided into two iterations for 2012. The first step is to calculate the percent that
each line item is of sales revenues, with several exceptions. The exceptions are the totals of each
category (such as total current assets), long-term debt, common stock and retained earnings.
Long-term debt will be a plug item that will be explained later. Common stock is the addition of
common valued at par and additional paid-in capital. We will assume that the firm will not issue
new common or buy back existing shares, although these assumptions can be relaxed. Finally,
retained earnings will be determined as the retained earnings on the balance sheet for the
previous year plus the addition to retained earnings from the income statement for the current
year. The formula in cell D23 is =C23+’Income Statement’!D16.
Figure 5
A B C D E F G
1 Assets 2011 2012 (1) 2012 (2) 2012 (3) 2013 (4)
2 Operating cash 1.28% $55 $62 $62 $62 $62
3 Accounts receivable 8.46% 363 407 407 407 407
4 Inventories 11.58% 497 557 557 557 557
5 Other current assets 3.61% 155 174 174 174 174
6 Total current assets $1,070 $1,198 $1,198 $1,198 $1,198
7
8 Net PP&E 38.73% $1,662 $1,861 $1,861 $1,861 $1,861
9 Goodwill 14.50% 622 697 697 697 697
10 Other assets 1.31% 56 63 63 63 63
11 Total assets $3,410 $3,819 $3,819 $3,819 $3,819
12
13 Liabilities and Equity
14 Accounts payable 2.45% $105 $118 $118 $118 $118
15 Accrued liabilities 7.74% 332 372 372 372 372
16 Total current liabilities $437 $489 $489 $489 $489
17
18 Long-term debt Plug $1,016 $1,016 $1,249 $1,251 $1,251
19 Other long-term liabilities 3.10% 133 149 149 149 149
20 Deferred income taxes 4.68% 201 225 225 225 225
21 $1,350 $1,391 $1,623 $1,625 $1,625
22 Common stock Constant $1,548 $1,548 $1,548 $1,548 $1,548
23 Retained earnings 75 159 157 157 157
24 Total shareholders' equity $1,623 $1,706 $1,705 $1,705 $1,705
25 Total liabilities and equity $3,410 $3,586 $3,818 $3,819 $3,819
26 Additional funds needed $0.00 $232.73 $1.68 $0.01 $0.00
Financial Forecasting 84
Note on the balance sheet below that column B is the percent of sales for the appropriate
line items as discussed above. (For example, this is calculated as =B2/’Income Statement’!C3.)
The percent for each line item is multiplied by the sales revenue forecast to generate a forecast
for the appropriate line items. Therefore, the cash forecast for the first iteration of 2012 is
=$B2*’Income Statement’!D3. The accounts receivable forecast is =$B3*’Income
Statement’!D3. All of the asset accounts are calculated in this manner.
The change in liabilities is different than the change in assets. This is because certain
liabilities will change spontaneously as growth increases and others will not. Accounts payable
and accruals will change spontaneously as growth in sales changes, however, long-term financing
will not. For purposes of illustration, it is assumed that common stock will remain constant. All
financing will come from retained earnings and long-term bonds. Keep in mind that this is for
illustration purposes only and also to demonstrate the feedback process.
The feedback and forecasting process works as follows: The amount of funds needed
(AFN) for 2012 is the difference between total assets and total liabilities and equity. Remember
that for the first iteration, we have kept all non-spontaneous financing constant. On the balance
sheet the initial AFN is $232.73 at the bottom of column D. To make up this shortfall in funds
needed, we increase long-term bonds by the amount of the shortfall. Remember we are assuming
that financing will come from long-term bonds and retained earnings only. Now that we have
increased the funding from new bond sales, this means that interest charges on the income
statement are going to change reflecting this larger amount of debt. The interest in cell D9 on the
income statement will read =B9*(‘Balance Sheet’!C18+‘Balance Sheet’!D18)/2. Note that on
the income statement the interest charge under the second iteration, 2012 (2) is up to $100 from
$92. (Note that for the second iteration the formula in E10 would read =B9*(‘Balance
Sheet’!$C18+‘Balance Sheet’!E18)/2.) If the interest expense on the income statement
increases then net income declines and so does the addition to retained earnings. Looking at the
income statement you can see that retained earnings have decreased to $82 from $85 for the
second iteration. Therefore, there will still be a shortfall of funds needed, which is indicated by
the $1.68 under column E. Our goal is to find that level of financing which reduces the
additional funds needed (AFN) to zero. These feedbacks are easy to program in Excel, however,
there is a simpler way. We will turn to that now.
Before turning to the Goal Seek procedure, there is an important question we should
address. Why not forecast the financial statements using the growth rate instead of the percent of
sales? This procedure would be easier and by doing so the forecast would be no different than
the one using the percent of sales. The reason gets back to planning. We can use the percent of
sales to test different strategies that may impact profit margins, etc. If we want to know the
implications of achieving greater efficiencies, for example, we could change the assumptions on
the cost of goods sold or SG&A, etc. So, forecasting line items with the growth rate would
sacrifice the planning benefits provided by the percent of sales methodology.
5.3 Financial Forecasting Using Goal Seek
This chapter uses the Goal Seek command because of what it teaches students about the
financing of the assets and the relationship between growth, capital structure and dividend
policy. 2 Note that the rate of growth on the income statement forecast in Figure 6 is 4.8% and
2 It is possible to set up integrated forecast of the income statement and balance sheet that allows Excel to iterate
automatically, but this becomes a black box and students do not gain from the insights here.
Financial Forecasting 85
not 12% as in the example above. The explanation of this will be discussed later. The Goal Seek
command enables us to perform the financial forecasting in one easy step. Below is the income
statement and balance sheet forecast for 2012. Note that in order to forecast 2012 only two
columns and not five are necessary.
The linkages between the income statement and balance sheet are set up exactly as
before. However, in using the Goal Seek command, we define the long-term debt account as a
“plug.” This means that we are going to use this line item to balance the balance sheet when
using the Goal Seek command. 3 Notice that at the bottom of the balance sheet, there is an
additional funds needed of $59.
Figure 6
Income Statement 4.80%
2011 2012
Revenue $4,291 $4,497
Cost of goods sold 58.12% (2,494) (2,614)
Gross profit 1,797 1,883
Selling, general and administrative 15.85% (680) (713)
R&D expenses 11.89% (510) (534)
Depreciation 2.75% (118) (124)
EBIT $489 $512
Interest expense 8.00% (75) (136)
EBT 414 376
Taxes 40.00% (166) (151)
Net income $249 $226
Dividends 70.00% (174) (158)
Retained earnings $75 $68
3 Alternatively we could use short-term debt as the plug if we think that the firm would have a preference for short-
term debt. Also, if growth in sales is declining, we could use cash or cash equivalents as the plug.
Financial Forecasting 86
Balance Sheet Figure 7
Assets 2011 2012
Operating cash 1.28% $55 $58
Accounts receivable 8.46% 363 380
Inventories 11.58% 497 521
Other current assets 3.61% 155 162
Total current assets $1,070 $1,121
Net PP&E 38.73% $1,662 $1,742
Goodwill 14.50% 622 652
Other assets 1.31% 56 59
Total assets $3,410 $3,574
Liabilities and Equity
Accounts payable 2.45% 105 $110
Accrued liabilities 7.74% 332 348
Total current liabilities $437 $458
Long-term debt Plug $1,016 $1,016
Other long-term liabilities 3.10% 133 139
Deferred income taxes 4.68% 201 211
$1,350 $1,366
Common stock Constant $1,548 $1,548
Retained earnings 75 142
Total shareholders' equity $1,623 $1,690
Total liabilities and equity $3,410 $3,514
Additional funds needed $0 $59
In order to begin the Goal Seek process, click on the Data tab at the top of the worksheet.
Then click on What if Analysis and then Goal Seek as shown below.
Financial Forecasting 87
Having clicked on the Goal Seek command, a dialogue box will appear, as shown below.
Type a 0 in the box labled To Value, click on the box labled By changing Cell and click on the
cell containing the long-term debt. (Alternatively you could type the cell reference D19.) Make
sure that the cell containing the amount of long-term debt contains a number, in this case 1016,
and not a formula. Click OK and a new box will show. Click OK a second time.
Financial Forecasting 88
Figures 8 and 9 show the completed forecast of the income statement and balance sheet.
To set up multi-year forecasts we simply extend the linkages out for as many years as we require.
A step-by-step procedure with some nice shortcuts will be illustrated later in this chapter.
Figure 8
Income Statement 4.80%
2011 2012
Revenue $4,291 $4,497
Cost of goods sold 58.12% (2,494) (2,614)
Gross profit 1,797 1,883
Selling, general and administrative 15.85% (680) (713)
R&D expenses 11.89% (510) (534)
Depreciation 2.75% (118) (124)
EBIT $489 $512
Interest expense 8.00% (75) (136)
EBT 414 376
Taxes 40.00% (166) (151)
Net income $249 $226
Dividends 70.00% (174) (158)
Retained earnings $75 $68
Financial Forecasting 89
Balance Sheet Figure 9
Assets 2011 2012
Operating cash 1.28% $55 $58
Accounts receivable 8.46% 363 380
Inventories 11.58% 497 521
Other current assets 3.61% 155 162
Total current assets $1,070 $1,121
Net PP&E 38.73% $1,662 $1,742
Goodwill 14.50% 622 652
Other assets 1.31% 56 59
Total assets $3,410 $3,574
Liabilities and Equity
Accounts payable 2.45% 105 $110
Accrued liabilities 7.74% 332 348
Total current liabilities $437 $458
Long-term debt Plug $1,016 $1,075
Other long-term liabilities 3.10% 133 139
Deferred income taxes 4.68% 201 211
$1,350 $1,425
Common stock Constant $1,548 $1,548
Retained earnings 75 142
Total shareholders' equity $1,623 $1,690
Total liabilities and equity $3,410 $3,574
Additional funds needed $0 $0
D/E 62.65% 63.62%
Aside from forecasting financial statements, what can we learn from this process? First,
we noted that 4.8% was used to forecast sales. From where did we get this number? This is the
predicted sustainable rate of growth, SGR. This rate of growth is defined as the maximum rate
of growth achievable without raising external equity financing while also maintaining a constant
ratio of debt to equity. Notice that at the bottom of the balance sheet above, the debt-to-equity
ratio is calculated and this ratio is roughly constant. We can determine the SGR using the
following formula. 4 If we use the end-of-period equity, then the formula is
. *1
*
bROE
bROE SGR
In this formula, b is the retention rate or 1 minus the dividend payout ratio. The calculations are
as follows:
4 If we use equity at the beginning of the period then the formula is SGR=ROE*b.
Financial Forecasting 90
%1.16 1623
249
Equity
NetIncome ROE
%30 226
158 11
NetIncome
Dividends b
%82.4 3.*161.1
3.*161.
*1
*
bROE
bROE SGR
By using the Goal Seek command, we are forced to make several choices and can result
in some interesting discussion questions: First, we need to decide whether we want to finance
with short-term or long-term debt. There are strategic issues here related to risk and profit.
Second, if we want to grow beyond the SGR, then we must either sell new equity issues, which
firms typically do not like to do, or we need to take on more debt, raising the potential for
financial distress. Third, if the predicted growth rates are very low, then we might have to use
cash or cash equivalents as a plug. This situation also begs several questions: If the slow growth
is due to a maturing firm, then should we increase dividends? If the slow growth is due to a poor
strategy resulting in a loss of market share, then should we expect the firm to build a reserve of
cash to be used for changing the strategic direction of the company?
Multi-Year Forecasts
This section shows how to quickly prepare the spreadsheet for multi-year forecasts.
Figure 10 shows the formulas in each cell for the income statement and Figure 11 shows the
balance sheet. Please take note of the $ in front of some of the letters in the formula. The
formula in cell D5 is =$B5*D$4. This formula is multiplying the percent of sales for the cost of
goods sold times sales for 2012. The $ freezes reference to that cell so that as you copy across
cells, each cell will multiply the percent of sales for cost of goods sold times sales for that
particular year. (See section Copying Formulas to Adjacent Cells in Chapter 1 page 5.)
Financial Forecasting 91
Once the spreadsheets are set up in this manner, highlight column D of the income statement and
drag across as shown below. Do the same for the balance sheet and your multi-year forecasts are
complete, with the exception of using the Goal Seek command, and this must be done one year at
a time.
Financial Forecasting 92
Summary
This chapter explored the concept of financial forecasting. We presented the percent of
sales method and illustrated how to accomplish on the spreadsheet. We then demonstrated how
to use the Goal Seek command to accomplish the task more easily and extended this to multi-
year forecasts.
Financial Forecasting 93
Exercises: