14.2 Forecast

Problem. Given actual financial statements for Cutting Edge B2B Inc., forecast their financial

statements for the next three years. Explore the impact of the financing choice variables: debt or equity.

Solution Strategy. Analyze the historical financial statements to determine which income statement and

balance sheet items are close to being a constant percentage of sales and which items are not. Then,

forecast sales as accurately as possible. Then, apply the average historical percentage of sales to generate

most of the income statement and balance sheet items. Forecast other key assumptions to generate most of

the rest and work out the implications for additional financing. Make the Balance Sheet balance by

calculating long-term debt as the plug item. Raise (or lower) the portion of equity relative to the portion

of debt by raising (or lowering) paid-in capital.

FIGURE 14.3 Forecast Percent of Sales for Cutting Edge B2B Inc.

How To Build Your Own Spreadsheet Model.

1. Calculate the Percent of Sales. For the historical financials, calculate each Income Statement

and Balance sheet item as a percentage of sales (see Figure 14.3). In cell C66, enter =C13/C$13

The $ sign in C$13 locks in Sales in row 13. Copy the formula in cell C66 across the entire range

C66:H112. This calculates the historical percent of sales and it sets up the forecasted percent of

sales. Delete ranges that correspond to blank sections of the Income Statement and Balance Sheet.

That is, delete ranges C69:H69, C73:H73, C79:H79, C83:H86, C91:H91, C95:H95, C97:H99,

C103:H103, C106:H107, and C111:H111. Looking at the historical financial statements, we see

that most Income Statement and Balance Sheet items are nearly a constant percentage of sales.

The main exceptions are: Interest Expense, Taxes, Accumulated Depreciation, Short-term

Debt, Long-term Debt, and Shareholders’ Equity.

2. Average Historical Percent of Sales. In Figure 14.3, column I is the average historical percent

of sales. This is simply the average of the percent of sales for the three historical years (1997 –

1999). In cell I66, enter =AVERAGE(C66:E66). Copy the formula in cell I66 across the entire

range I67:I112. Average historical percent of sales are used to forecast all financial items that are

nearly constant as a percentage of sales. Delete cells and ranges that correspond to blank parts of

the Income Statement and Balance Sheet. That is, delete cells I69, I73, I91, I95, I103, and I111

and delete ranges I79:I80, I83:I86, I97:I99, and I106:I107.

FIGURE 14.4 Forecast Assumptions & Income Statement for Cutting Edge B2B Inc.

3. Forecasting Sales and Other Key Assumptions. Looking at the historical sales growth rate in

Figure 14.1, it is clear that the rate of growth is slowing down. This may reflect such factors as

increasing competition or relative saturation of the market. It seems prudent to forecast a

continued slowdown in the rate of growth. Hence, sales are forecast to grow at 16%, 13% and

11% over the next three years. Enter these sales forecasts in the range F5:H5. Interest rates on

short-term and long-term debt are forecasted based on their current levels with a slight declining

trend. The rest of the key assumptions are forecasted at or near their average historical levels.

Enter the forecast values shown in Figure 14.4 in the range F6:H10.

4. The Income Statement. Some Income Statement items are based on the Key Assumptions

section, others are forecasted as a percentage of sales, and others are simple additions or

subtractions.

o Sales on date t = (Sales on date t-1) * (1 + Sales Growth Rate). Enter =E13*(1+F5) in

cell F13 and copy it across.

o Cost of Goods Sold = (Ave. Hist. Goods Sold / Sales) * Sales. For convenience, reference

the Ave. Hist. Goods Sold / Sales by entering =I67 in cell I14. Then enter =$I14*F$13

in cell F14 and copy it across. The $ sign in $I14 locks in column I, which is the Ave.

Hist. Goods Sold / Sales and the $ sign in F$13 locks in row 13, which is Sales.

o Gross Margin = Sales - Cost of Good Sold. This is the same as the Actual Gross Margin.

Copy cell E15 across.

o Selling, Gen and Adm Expense = (Ave. Hist. SG&A / Sales) * Sales. Depreciation is

forecast in the same way. This is the same format as the Cost of Good Sold. Copy the

range F14:I14 to the range F17:F18.

o Earnings Before Interest and Taxes (EBIT) = Gross Margin - SG&A Expense. -

Depreciation. This is the same as the Actual EBIT. Copy cell E19 across.

o Interest Expense = (Interest Rate on Short-term Debt) * (Amount of Short-term Debt at

the End of the Previous Year) + (Interest Rate on Long-term Debt) * (Amount of Shortterm

Debt at the End of the Previous Year). Enter =F7*E48+F8*E51 in cell F21 and

copy it across.

o Taxes = (EBIT- Interest Expense) * (Tax Rate). Enter =(F19-F21)*F6 in cell F22 and

copy it across.

o Net Income = EBIT- Interest Expense - Taxes. This is the same as the Actual Net

Income. Copy cell E23 across.

o Dividends = (Net Income) * (Dividend Payout Rate). Enter =F23*F9 in cell F28 and

copy it across.

o Change in Equity = Net Income - Dividends. This is the same as the Actual Change in

Equity. Copy cell E29 across.

FIGURE 14.5 Forecast Balance Sheet for Cutting Edge B2B Inc.

5. The Balance Sheet. The Balance Sheet works as follows:

o Current Asset Item = (Ave. Hist. Current Asset Item / Sales) * Sales. This is the same

format as Depreciation. Copy the range F18:I18 to the range F34:F36.

o Total Current Assets = Sum of the Current Asset Items. This is the same as the Actual

Total Current Assets. Copy cell E37 across.

o Net PPE = (Ave. Hist. PPE / Sales) * Sales. This is the same format as Inventories. Copy

the range F36:I36 to the cell F41.

o Accumulated Depreciation on date t = (Accumulated Depreciation on date t-1) +

Depreciation. Enter =E40+F18 in cell F40 and copy it across.

o Property Plant and Equipment = Net PPE + Accumulated Depreciation. Enter =F40+F41

in cell F39 and copy it across.

o Total Assets = Total Current Assets + Net PPE. This is the same as the Actual Total

Assets. Copy cell E43 across.

o Total Liabilities and Shareholders Equity is set equal to Total Assets. This is the same as

the Actual Total Liabilities and Shareholders Equity. Copy cell E59 across.

o Paid-in Capital is a key choice variable. Enter any values for right now. Perhaps you want

to continue the trend of recent years. At the end, we will come back and adjust this

category to achieve the desired mix of debt and equity.

o Retained Earnings on date t = (Retained Earnings on date t-1) + (Change in Equity from

the Income Statement). This is the same as the Actual Retained Earnings. Copy cell E56

across.

o Total Shareholders’ Equity = Paid-in Capital + Retained Earnings. This is the same as the

Actual Total Shareholders’ Equity. Copy cell E57 across.

o Total Liabilities = Total Liabilities and Shareholders Equity - Total Shareholders’ Equity.

This is the same as the Actual Total Liabilities. Copy cell E52 across.

o Accounts Payable is forecasted based on the Percentage of Sales. This is the same format

as Net PPE. Copy the range F41:I41 to the cell F47.

o Short-term Debt = (Ave. Hist. Short-term Debt / Sales) * Sales. This is the same format

as Inventories. Copy the range F36:I36 to the cell F48.

o Total Current Liabilities = Accounts Payable + Short-term Debt. This is the same as the

Actual Total Current Liabilities. Copy cell E52 across.

o Long-term Debt = Total Liabilities – Total Current Liabilities. This is the same as the

Actual Long-term Debt. Copy cell E51 across.

6. Adjust Paid in Capital and Check Long-Term Debt. Most companies try to maintain a target

proportion of debt vs. equity. Adjustment Paid in Capital, which is a component of equity, will

accomplish this. Debt / (Debt + Equity) is the same as the Actual ratio. Copy cell E61 across.

Historically, the company has maintained a Debt / (Debt + Equity) proportion between 28.0% and

30.2%. In this forecast, the company chooses to maintain a Debt / (Debt + Equity) proportion of

slightly more than 29%. Raise (or lower) Paid in Capital in the range F55:H55 to the percentages

shown in Figure 14.5, in order to lower (or raise) the Debt / (Debt + Equity) proportion. After all

of the forecasting is done, it is important to check Long-term Debt to make sure that it isn’t

growing explosively or dropping rapidly (perhaps going negative!). If it is going wild, then

backtrack to identify the source of sharp up or down movements and check for errors.

7. Shares Outstanding and Earnings Per Share. The formula for Shares Outstanding on date t =

(Shares Outstanding on date t-1) + (Paid in Capital on date t - Paid in Capital on date t-1) *

(Market Price / Share on date t-1). This assumes that you issue shares at the beginning of the year.

Enter =E24+(F55-E55)/E62 in cell F24 and copy it across. Earnings Per Share = Net Income /

Shares Outstanding. This is the same as the Actual Earnings Per Share. Copy cell E25

across. Some of these cells will temporarily display an error message until the Market Price /

Share is calculated in the step below.

8. Other Comparisons. The formula for Market Price / Share = (Price / Earnings) * (Earnings /

Share). Enter =F10*F25 in cell F62 and copy it across. The formula for the firm’s External Funds

Needed = (Increase in Total Assets) – (Increase in Retained Earnings) – (Increase in Accounts

Payable). This is the same as the Actual External Funds Needed. Copy cell E63 across. We see

that there continues to be a significant amount of External Funds Needed.

The forecast for the next three years is a steady increase in Earnings Per Share from $0.23 to $0.25 to

$0.26.

Problem. Given actual financial statements for Cutting Edge B2B Inc., forecast their financial

statements for the next three years. Explore the impact of the financing choice variables: debt or equity.

Solution Strategy. Analyze the historical financial statements to determine which income statement and

balance sheet items are close to being a constant percentage of sales and which items are not. Then,

forecast sales as accurately as possible. Then, apply the average historical percentage of sales to generate

most of the income statement and balance sheet items. Forecast other key assumptions to generate most of

the rest and work out the implications for additional financing. Make the Balance Sheet balance by

calculating long-term debt as the plug item. Raise (or lower) the portion of equity relative to the portion

of debt by raising (or lowering) paid-in capital.

FIGURE 14.3 Forecast Percent of Sales for Cutting Edge B2B Inc.

How To Build Your Own Spreadsheet Model.

1. Calculate the Percent of Sales. For the historical financials, calculate each Income Statement

and Balance sheet item as a percentage of sales (see Figure 14.3). In cell C66, enter =C13/C$13

The $ sign in C$13 locks in Sales in row 13. Copy the formula in cell C66 across the entire range

C66:H112. This calculates the historical percent of sales and it sets up the forecasted percent of

sales. Delete ranges that correspond to blank sections of the Income Statement and Balance Sheet.

That is, delete ranges C69:H69, C73:H73, C79:H79, C83:H86, C91:H91, C95:H95, C97:H99,

C103:H103, C106:H107, and C111:H111. Looking at the historical financial statements, we see

that most Income Statement and Balance Sheet items are nearly a constant percentage of sales.

The main exceptions are: Interest Expense, Taxes, Accumulated Depreciation, Short-term

Debt, Long-term Debt, and Shareholders’ Equity.

2. Average Historical Percent of Sales. In Figure 14.3, column I is the average historical percent

of sales. This is simply the average of the percent of sales for the three historical years (1997 –

1999). In cell I66, enter =AVERAGE(C66:E66). Copy the formula in cell I66 across the entire

range I67:I112. Average historical percent of sales are used to forecast all financial items that are

nearly constant as a percentage of sales. Delete cells and ranges that correspond to blank parts of

the Income Statement and Balance Sheet. That is, delete cells I69, I73, I91, I95, I103, and I111

and delete ranges I79:I80, I83:I86, I97:I99, and I106:I107.

FIGURE 14.4 Forecast Assumptions & Income Statement for Cutting Edge B2B Inc.

3. Forecasting Sales and Other Key Assumptions. Looking at the historical sales growth rate in

Figure 14.1, it is clear that the rate of growth is slowing down. This may reflect such factors as

increasing competition or relative saturation of the market. It seems prudent to forecast a

continued slowdown in the rate of growth. Hence, sales are forecast to grow at 16%, 13% and

11% over the next three years. Enter these sales forecasts in the range F5:H5. Interest rates on

short-term and long-term debt are forecasted based on their current levels with a slight declining

trend. The rest of the key assumptions are forecasted at or near their average historical levels.

Enter the forecast values shown in Figure 14.4 in the range F6:H10.

4. The Income Statement. Some Income Statement items are based on the Key Assumptions

section, others are forecasted as a percentage of sales, and others are simple additions or

subtractions.

o Sales on date t = (Sales on date t-1) * (1 + Sales Growth Rate). Enter =E13*(1+F5) in

cell F13 and copy it across.

o Cost of Goods Sold = (Ave. Hist. Goods Sold / Sales) * Sales. For convenience, reference

the Ave. Hist. Goods Sold / Sales by entering =I67 in cell I14. Then enter =$I14*F$13

in cell F14 and copy it across. The $ sign in $I14 locks in column I, which is the Ave.

Hist. Goods Sold / Sales and the $ sign in F$13 locks in row 13, which is Sales.

o Gross Margin = Sales - Cost of Good Sold. This is the same as the Actual Gross Margin.

Copy cell E15 across.

o Selling, Gen and Adm Expense = (Ave. Hist. SG&A / Sales) * Sales. Depreciation is

forecast in the same way. This is the same format as the Cost of Good Sold. Copy the

range F14:I14 to the range F17:F18.

o Earnings Before Interest and Taxes (EBIT) = Gross Margin - SG&A Expense. -

Depreciation. This is the same as the Actual EBIT. Copy cell E19 across.

o Interest Expense = (Interest Rate on Short-term Debt) * (Amount of Short-term Debt at

the End of the Previous Year) + (Interest Rate on Long-term Debt) * (Amount of Shortterm

Debt at the End of the Previous Year). Enter =F7*E48+F8*E51 in cell F21 and

copy it across.

o Taxes = (EBIT- Interest Expense) * (Tax Rate). Enter =(F19-F21)*F6 in cell F22 and

copy it across.

o Net Income = EBIT- Interest Expense - Taxes. This is the same as the Actual Net

Income. Copy cell E23 across.

o Dividends = (Net Income) * (Dividend Payout Rate). Enter =F23*F9 in cell F28 and

copy it across.

o Change in Equity = Net Income - Dividends. This is the same as the Actual Change in

Equity. Copy cell E29 across.

FIGURE 14.5 Forecast Balance Sheet for Cutting Edge B2B Inc.

5. The Balance Sheet. The Balance Sheet works as follows:

o Current Asset Item = (Ave. Hist. Current Asset Item / Sales) * Sales. This is the same

format as Depreciation. Copy the range F18:I18 to the range F34:F36.

o Total Current Assets = Sum of the Current Asset Items. This is the same as the Actual

Total Current Assets. Copy cell E37 across.

o Net PPE = (Ave. Hist. PPE / Sales) * Sales. This is the same format as Inventories. Copy

the range F36:I36 to the cell F41.

o Accumulated Depreciation on date t = (Accumulated Depreciation on date t-1) +

Depreciation. Enter =E40+F18 in cell F40 and copy it across.

o Property Plant and Equipment = Net PPE + Accumulated Depreciation. Enter =F40+F41

in cell F39 and copy it across.

o Total Assets = Total Current Assets + Net PPE. This is the same as the Actual Total

Assets. Copy cell E43 across.

o Total Liabilities and Shareholders Equity is set equal to Total Assets. This is the same as

the Actual Total Liabilities and Shareholders Equity. Copy cell E59 across.

o Paid-in Capital is a key choice variable. Enter any values for right now. Perhaps you want

to continue the trend of recent years. At the end, we will come back and adjust this

category to achieve the desired mix of debt and equity.

o Retained Earnings on date t = (Retained Earnings on date t-1) + (Change in Equity from

the Income Statement). This is the same as the Actual Retained Earnings. Copy cell E56

across.

o Total Shareholders’ Equity = Paid-in Capital + Retained Earnings. This is the same as the

Actual Total Shareholders’ Equity. Copy cell E57 across.

o Total Liabilities = Total Liabilities and Shareholders Equity - Total Shareholders’ Equity.

This is the same as the Actual Total Liabilities. Copy cell E52 across.

o Accounts Payable is forecasted based on the Percentage of Sales. This is the same format

as Net PPE. Copy the range F41:I41 to the cell F47.

o Short-term Debt = (Ave. Hist. Short-term Debt / Sales) * Sales. This is the same format

as Inventories. Copy the range F36:I36 to the cell F48.

o Total Current Liabilities = Accounts Payable + Short-term Debt. This is the same as the

Actual Total Current Liabilities. Copy cell E52 across.

o Long-term Debt = Total Liabilities – Total Current Liabilities. This is the same as the

Actual Long-term Debt. Copy cell E51 across.

6. Adjust Paid in Capital and Check Long-Term Debt. Most companies try to maintain a target

proportion of debt vs. equity. Adjustment Paid in Capital, which is a component of equity, will

accomplish this. Debt / (Debt + Equity) is the same as the Actual ratio. Copy cell E61 across.

Historically, the company has maintained a Debt / (Debt + Equity) proportion between 28.0% and

30.2%. In this forecast, the company chooses to maintain a Debt / (Debt + Equity) proportion of

slightly more than 29%. Raise (or lower) Paid in Capital in the range F55:H55 to the percentages

shown in Figure 14.5, in order to lower (or raise) the Debt / (Debt + Equity) proportion. After all

of the forecasting is done, it is important to check Long-term Debt to make sure that it isn’t

growing explosively or dropping rapidly (perhaps going negative!). If it is going wild, then

backtrack to identify the source of sharp up or down movements and check for errors.

7. Shares Outstanding and Earnings Per Share. The formula for Shares Outstanding on date t =

(Shares Outstanding on date t-1) + (Paid in Capital on date t - Paid in Capital on date t-1) *

(Market Price / Share on date t-1). This assumes that you issue shares at the beginning of the year.

Enter =E24+(F55-E55)/E62 in cell F24 and copy it across. Earnings Per Share = Net Income /

Shares Outstanding. This is the same as the Actual Earnings Per Share. Copy cell E25

across. Some of these cells will temporarily display an error message until the Market Price /

Share is calculated in the step below.

8. Other Comparisons. The formula for Market Price / Share = (Price / Earnings) * (Earnings /

Share). Enter =F10*F25 in cell F62 and copy it across. The formula for the firm’s External Funds

Needed = (Increase in Total Assets) – (Increase in Retained Earnings) – (Increase in Accounts

Payable). This is the same as the Actual External Funds Needed. Copy cell E63 across. We see

that there continues to be a significant amount of External Funds Needed.

The forecast for the next three years is a steady increase in Earnings Per Share from $0.23 to $0.25 to

$0.26.