Module 5 Forecasting - Advanced
Module 5 Forecasting - Advanced
Table of Contents
Module 5: Forecasting- Advanced ......................................................................................... 1
Lesson 5-1: Introduction to Detailed Forecasting .............................................................................2
Detailed Forecasting Process ................................................................................................................................ 2
Building a Forecast Model ..................................................................................................................................... 6
Income Statement Forecasting ........................................................................................................................... 22
Balance Sheet Forecasting .................................................................................................................................. 26
Cash Flow Forecasting ......................................................................................................................................... 26
1
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
Lesson 5-1: Introduction to Detailed Forecasting
Module 4 Objectives and Overview
Hi, everyone. As we previously discussed, there are four parts to financial statement analysis
and valuation. It starts by building a solid foundation, by obtaining an understanding of
the contents of the financial statements, and knowing how to access valuable information
within the SEC Filings. Next, we go beyond the financials to assess a company's strategies and
competitive position. We use all of this information in insights to perform ratio analysis,
to identify trends, analyze performance, and benchmark results versus other companies.
This allows us to make numbers come to life and truly understand the drivers of performance
and explain the why. We can use our ratio analysis and trends to perform our next step
in financial statement analysis and valuation. Forecasting; forecasting involves putting all of
the analysis performed thus far to work to estimate a company's future results.
Throughout this video, we'll discuss how to perform a detailed forecast.
2
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
Let's first start by discussing the forecast process in general. Forecasting is the process of
making informed estimates of future performance using historical data, trends, evaluation of
strategic choices, and assess impacts of macro-economic events. Let's break this down into
more detail. First, informed estimates. Forecasting is not a guessing game. It involves
considering several pieces of information gathered during the financial statement analysis
process to develop an estimate of future performance. Next, historical data and trends. A
starting point for developing a forecast is to use past results as an indicator of future
performance by initially assuming that the trajectory of a company's results will continue in
the future. Finally, after developing the baseline forecast using historical data and trends, you
should adjust your forecast for your assessment of a company's strategic choices and impacts
of macro-economic events. As you know, a firm's future performance can vary greatly depend
upon the strategic decisions they made, their competitive position, and the industry
dynamics they operate within.
3
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
Forecasting is very important and is an integral part of many business and investment
decisions.
Forecasting serves many purposes and allows a management team to evaluate if their
business is on the right path or highlight if adjustments are needed to deliver the earnings
commitment made to the board and shareholders. It's used to evaluate alternative strategic
investment decisions and allows an investor to value stocks.
4
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
There are two approaches to forecasting: simple or over the top forecast, or detailed or a
bottoms up built. Simple or over the top forecasting implies that you take
a high level approach to calculate the final inputs needed for your forecast.
5
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
For example, you may forecast future results in line with GDP or gross domestic product
projections or based upon historical trends. Detailed forecasting involves a line by line
bottoms up build of the financial statements to ultimately develop the final inputs needed for
a forecast. Our focus within this video will be creating a detailed or bottoms-up forecast.
6
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
When performing a detailed forecast, the order in which we perform the forecast is very
important as the financial statements are linked together and certain items drive the
forecast results of other line items. We first start with the income statement. Initially
focusing on net revenue. Net revenue is a critical driver of many income
statement, balance sheet, and statement of cash flow line items. Next, we forecast the
balance sheet. We start from the bottom and work our way up, first focusing on the
equity section by using net income from the income statement to forecast shareholder's
equity. Next, we use sales growth from our income statement forecast as a driver
of many liability and asset balances. And we ultimately solve for the cash balance. And
finally, the statement of cash flows. We start by forecasting cash from operations which
begins with net income on the accrual basis of accounting obtained from the forecasted
income statement. And we reconcile to the cash basis by adding back non cash
income statement items and adjusting for the change in operating asset and liability
balance is obtained from the balance sheet. We then use a trend-based approach
and assumptions developed from our assessment of a company's strategies
and initiatives to forecast cash from investing and financing activities. Whether you're
forecasting the income statement, balance sheet, or statement of cash flow, the general
process is the same.
7
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
We start by building a forecast model and upload historical gap results obtained from
the 10-K. We then adjust for one time non recurring items to ensure our base results
and analysis focus on the core ongoing business results. Next, we analyze trends by
performing ratio analysis, including horizontal and vertical analysis. And incorporate our
assessment of a company's strategies, competitive position and impacts of
macroeconomic events. So ultimately develop and document the assumptions needed
to build the future outlook. Our final step is to calculate our forecast, usually a 3-5 year
projection of future results.
8
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
Hi everyone. An important part of forecasting the income statement, balance sheet and
statement of cash flows and executing the forecast process is to build a forecast model.
A forecast model includes historical financial results, adjustments for non-GAAP items,
ratio analysis allowing us to identify trends, inputs for growth rates and an area to document
your assumptions. Finally, a forecast model should include output for the future 3-5 year
forecast results. I like to build a forecast model that includes the income statement,
balance sheet and statement of cash flows as the financial statements work together and
assumptions for revenue when certain other items are often drivers for forecast results of
many line items within the balance sheet and statement of cash flows.
9
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
As we discussed, building a forecast model starts with obtaining the GAAP financial results
for the past 2-3 years from the latest 10K, for the income statement, balance sheet and
statement of cash flows. The next step is to adjust for onetime nonrecurring items to ensure
our results and analyses focus on the core ongoing business. My primary focus is on the
income statement, but adjustments can also be made to the balance sheet and statement of
cash flows if appropriate.
10
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
Non-GAAP adjustments either increase or decrease GAAP results in order to remove the
impact of onetime nonrecurring items. An important item to remember is that you will need
to tax effect the adjustments by applying the applicable effective tax rate. Companies are
required to provide GAAP to non-GAAP reconciliations when disclosing adjusted or non-GAAP
results. Oftentimes, non-GAAP adjustments can be found in the company's press release
schedule as part of their quarterly earnings announcement. It's your job as an investor to
assess whether you agree with the company's adjustments. If so, these items should
be included within your forecast model so that you'll be able to better analyze core or base
business trends.
11
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
We're now ready to calculate our adjusted income statement after removing the onetime
nonrecurring items. Next, I'm ready to perform our ratio analysis to identify trends and
past results that may be used as a starting point for developing our future forecast.
I typically perform horizontal analysis to identify your performance trends and growth rates
and vertical analysis to calculate certain items such as gross profit and operating expenses on
12
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
a percent of sales basis. Analyzing the progression or trend of the percent of sales items can
be a key input for developing forecast assumptions. Next we're ready to put all of
our analyses to work by assessing a company's strategies, competitive position, and impacts
of macroeconomic events to develop and document our forecast assumptions.
I typically start with the historical trends. For example, if gross margin is percent of sales has
historically been in the 45 percent range over the past three years it would be reasonable to
assume that this trend would continue. I would then adjust up if there's a favorable outlook
for the overall industry in which the company operates within. Maybe the company
possesses a sustainable competitive advantage such as a new product innovation or if
there's a positive macroeconomic outlook for factors impacting the company.
13
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
However, I would adjust down if historical trends are in the opposite direction such as an
unfavorable outlook for the overall industry. The company is at a competitive disadvantage
or if the macroeconomic outlook for factors impacting the company is negative.
One of the most important parts of forecasting is documenting your assumptions. This will
allow you to post-audit the results by comparing actual versus the forecast. You can identify
14
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
where there is a mess in an effort for continuous improvement. Our final step is to calculate
our forecast. Usually a 3-5 year projection of future results.
I typically create input areas to apply a growth rate or input a percent of sales ratio into the
model to drive the calculated forecast. You can see from the income statement forecast
model excerpt I created yellow input areas for revenue growth rates and gross margin
percent of sales to calculate these forecasts with line items. The same approach could be
applied to other line items within the income statement, balance sheet, and statement of
cash flows.
15
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
16
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
First is net sales. I believe it's important to forecast revenue by each reportable segment.
This is how the chief operating decision maker analyzes the business. Oftentimes there are
different trends in growth rates by segment. I first look at historical trends and then adjust up
or down depending upon company guidance, the overall industry outlook,
the company's strategies, and their competitive position.
17
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
You can see from this excerpt that this company has four reportable segments: household
products, personal care products, consumer international, and especially products division
or SPD. The growth rates vary by segment. Within the forecast model I insert an area to
document my assumptions so that I can use for post-auditing purposes after actual results
are reported. I then input future growth rates based upon historical trends, company
guidance, industry outlook, and my assessment of the company strategies and competitive
position in order to calculate my 2021-2023 net sales forecast. Calculating the net sales
forecast is extremely important as this serves as a driver for many other line items within the
income statement, balance sheet, and statement of cash flows.
Next is gross profit. It's important to analyze gross margin percent of sales results for the past
2-3 years. Oftentimes trends emerge. They can be used as a starting point for your forecast.
Also many companies discuss gross margin percentage of sales within the MD&A section
of the 10-K and include as part of their company guidance. Specific initiatives focused on
gross margin improvement can have an impact on forecast and should be considered when
developing the future outlook.
18
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
You can see from this excerpt that gross margin as a percent of sales has range from 44.4
percent to 45.5 percent in the latest three fiscal years. In addition, the company has a track
record of focusing and improving gross margin. In fact, per review of their proxy filing, gross
margin percent of sales is a bonus metric for the management team. As a result, we have
forecasted a 20 basis points improvement for each of the next three years. Forecasted gross
margin percent of sales rates multiplied by the net sales forecast for 2021, 2022, and
2023 allows us to calculate gross profit dollars, and then back into cost of goods sold.
19
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
The approach to forecasting operating expenses is similar to forecasting gross profit. The
focus is on a percent of sales. It's important to analyze operating expenses as a percent of
sales results for the past 2-3 years. Oftentimes, trends emerge they can be used as starting
point for your forecast. Also many companies discuss operating expenses as a percentage
sales within their MD&A section of the 10-K and include as part of their company earnings
guidance. Specific initiatives focused on expense improvement can have an impact on
forecast and should be considered when developing the future outlook. Finally, it's important
that your forecast is internally consistent, meaning, accelerated growth and revenue will
likely need to be supported by investments in key drivers such as advertising and a research
and development. Using a percent of sales approach will help in driving this internal
consistency.
20
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
You can see from this excerpt that SG&A as a percent of sales has ranged from 13.6 percent to
14.1 percent in the latest three fiscal years. In addition, advertising as a percent of sales has
increased steadily from 11.7 percent in 2018 to 12.1 percent in 2020. Per review, the earnings
call transcripts, the MD&A, and investor presentations, the company has indicated
a focused effort to reducing SG&A cost, and a commitment to increasing support of their
brands through investments in advertising to drive continued sales growth. As such, we have
forecasted 10 basis points improvement within SG&A as a percent of sales and forecasted to
reinvest these savings by increasing advertising as a percent of sales, by 10 basis points each
year. Forecasted SG&A and advertising as a percent of sales rates multiplied by net sales for
2021, 2022 and 2023 allows us to calculate SG&A and advertising expenses for 2021 through
2023.
21
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
22
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
Due to the complexity and unique aspects of a company's tax situation, many companies
provide guidance for investors as to their expected future tax rate. If company guidance is not
provided, it's reasonable to forecast using a prior year trend-based approach. Alternatively,
primarily US-based companies can be forecasted at the 21 percent federal tax rate plus an
average of the state tax rate.
23
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
As you can see, the effective tax rate calculated as income tax expense divided by pre-tax
income has remained relatively consistent over the past three years. Based on company
guidance and consistent with prior year trend, the 2021 through 2023 effective tax rate
is forecasted to equal 18.5 percent, consistent with 2020 levels. The forecasted effective tax
rate of 18.5 percent multiplied by pre-tax income allows us to calculate tax expense in each of
the forecasted years.
Finally, the approach to forecasting common shares outstanding is often based upon
trend. However if there's significant volatility in prior year levels due to share issuances or
repurchase activity, it is reasonable to forecast shares equal to the prior year. This approach
allows you to compare earnings per share based upon base business results absent capital
allocation decisions such as share repurchases.
24
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
When reviewing the past three years of activities for shares outstanding, there is a consistent
trend of modest increases from year to year. This activity is likely driven by equity
compensation awarded to members of management. Given the consistent trend, it is
reasonable to forecast a continuation of an increase in share count of 20 basis points in each
of the forecasted years.
25
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
26
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
In order to maintain the focus on operating results, we exclude the impact of potential share
issuances. Net income, which is close to retain earnings, is obtained from the income
statement forecast. Annual dividend increases are usually consistent for many companies, as
such their forecast is based upon trends. Other comprehensive income is often unpredictable
as such amounts are forecasted to equal prior year levels. And finally, share repurchase
activities can vary significantly by company and by year to year, some companies repurchase
a consistent amount each year, as such forecasts can be based upon trend. Other companies
are more opportunistic and thus less predictable, as such forecast amounts should either be
excluded or consistent with prior year levels.
27
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
As we previously discussed, we obtain prior year results from the 10-K and
input forecast data for 2021 through 2023. In order to focus on core operating results, we
assume no new share issuances for preferred stock or common stock. Next we roll forward,
retained earnings balances by adding in that income from the income statement forecast and
subtract dividend payments. In this example, dividend payments have averaged an increase
of 5 to 6% for the past two years. As such, we believe it's reasonable to continue this trend
also to maintain your over your comparability. We do not consider the impacts of new
accounting pronouncements within the forecast periods. Given the unpredictability of other
comprehensive income, we simply hold this balance flat to the prior year levels throughout
the forecast horizon. And finally, treasury stock repurchase forecasts are usually based upon
trend. Per view, the past two years, activity share repurchases net of reassurances
for executive compensation have increased approximately 8-10% per year. As such, it's
reasonable to maintain this trend also company guidance can be used as an indicator of
future repurchase activity.
28
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
For the liabilities forecast, we based many forecast items using trend or prior year
levels. However, some are directly tied to certain income statement line items. For example,
accounts payable is forecasted based upon days payable outstanding targets or cost of goods
sold as a can put. Oftentimes, debt balances remain consistent from year to year, as such,
forecasts are usually held flat to prior levels. Absent a stated plan from the company to
increase or decrease leverage levels. Tax items such as deferred taxes and income tax
payables, are forecasted based upon net income growth rates. And finally, other liability
items are usually held flat to the prior year or forecasting consistent with trend.
29
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
As previously mentioned, we forecast debt levels consistent with the prior year unless there's
a stated plan to increase or decrease leverage. As such, this assumes refinancing,
incomparable interest rates and the attached to example, debt levels have been held flat to
the prior year throughout the forecast period. Accounts payable and accrued expenses are
forecasted based upon days payable outstanding projections. DPO for the past two years has
equaled approximately 100 to 126 days. As such, it's reasonable to hold DPO consistent with
2020 levels using the cost of goods sold, forecasts from the income statement and DPO
targets were able to calculate accounts payable and accrued expenses. Next, our income tax
payable and deferred income taxes, we forecast these amounts consistent with the net
income forecasted growth rates. And finally differed in other liabilities and business
acquisition liabilities are held flat to the prior year as there are no observable trends.
30
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
As we complete the balance sheet forecast next focusing on assets, we use a variety of
methods to forecast asset line items. For example, accounts receivable and inventory
balances are forecasted based upon days sales outstanding and days, and inventory targets.
Next, other current assets are typically forecasted at trend or prior year levels. Plant property
equipment, or PP and E , is often forecasted to align with sales growth rates. As investments
in Capex are often used to drive growth activity, other long term assets are forecasted a trend
or prior year levels and finally cashes a plug such that assets equals liabilities plus equity.
31
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
As we forecast the assets section of the balance sheet, we initially skip cash as this will later
be used as a plug bouncing figure. Accounts receivable and inventories are forecast based
upon day sales outstanding and days and inventory projections. Days sales outstanding for
the past two years has equaled approximately 28-29 days. And days, and inventory has
averaged 62 days, as such, it's reasonable to hold DSO and DII consistent with 2020
levels. Using the sales and cost of goods sold forecasts from the income statement and
DSO and DII targets were able to calculate accounts receivable and inventories. Next, other
current assets are held flat to prior year levels, which is consistent with trends. PP any growth
rates are forecasted to align with the sales growth rates as investments and compacts are
often used to drive growth, all other assets are forecasted to equal prior year levels.
Finally is previously mentioned, cash is a plug or bouncing figure to enable assets to equal
liabilities plus equity.
32
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
Hi, everyone. As we discussed, the sequence in which you execute your financial statement
forecast is important. As the financial statements are linked together in many items such as
revenue, serve as the forecast driver for individual line items within the income statement,
balance sheet, and statement of cash flows. Within this video, we'll discuss our approach
to forecasting the statement of cash flows. We start by forecasting cash from operations
which begins with net income on the accrual basis of accounting obtained from the
forecasted income statement and reconcile to the cash basis by adding back non-cash
income statement items and adjusting for the change in operating assets and liabilities
obtained from the balance sheet. We then use a trend-based approach and assumptions
developed from our assessment of a company's strategies and initiatives to forecast cash
from investing and financing activities.
33
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
We start by preparing the forecast for cash from operating activities. We use the following
assumptions and inputs, net incomes obtained from the income statement. Many non-cash
items are forecasted to align with income statement growth rates aligned with prior-year
levels or forecasted to equal zero. The change in operating asset and liabilities are calculated
by the difference between the current year and prior year applicable balance sheet line
items. Finally, we use other as a plug so that cash on the statement of cashflows ties to the
balance sheet.
34
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
As previously mentioned, the starting point for calculating cash from operating activities is
net income obtained from the forecasted income statement. We then add back or
deduct non-cash items such as depreciation, amortization, deferred tax items, compensation
expense, asset impairments, gains, and other items. Depreciation is forecasted to
align with the PP&E forecast. Amortization is held flat to the prior year. Given the volatility
and unpredictability of the change in fair value of business acquisition liabilities, asset
impairments, and gains, we forecast that these items will not have an impact on cash and
thus forecast them to equal zero. Next, the change in deferred income taxes are estimated to
increase in line with net income growth rates. Non-cash compensation expense is held flat to
prior-year levels and other is used as a balancing plug figure so that the overall cash balance
equals the amount forecasted on the balance sheet. Finally, the impacts from change in
operating assets and liabilities are calculated directly from the difference between the
current year and prior year applicable balance sheet line items.
Now turning to cash from investing activities, the following assumptions and inputs are
used. Cash used to purchase capital equipment is forecasted to align with sales and PP&E
growth rates. In order to maintain compatibility with our core base business and given the
unpredictability, we do not forecast the sale of assets or impact of acquisitions, and finally,
other is forecasted at trend levels.
35
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
As previously mentioned, CapEx is forecasted to align with sales and PP&E as investments
in CapEx are often used to drive growth. Next, we do not typically forecast proceeds from the
sale of assets or acquisitions due to their unpredictability and in an effort to maintain your
comparability with the base business, and finally, others forecasted to align with trend or
prior-year levels.
Finally, cash from financing activities. Many of the assumptions and forecast inputs are
obtained from the balance sheet. For example, repayment, new issuances of debt,
36
Forecasting Financial Statements & Valuation for Accountants
Professor Briann Ham
are aligned to changes in debt balances on the balance sheet. Proceeds from stock option
exercise, dividend payments, and share repurchases are aligned with the shareholders'
equity forecast. Finally, it's important to ensure that the ending cash balance ties to the
balance sheet, we use the other line item and cash from operating activities as a plug balance
figure.
As we previously mentioned, debt activity is aligned with the balance sheet. As we were
forecasting the balance sheet, we assume that debt would remain at prior year levels
thus indicating no new issuances or repayments. Next, we assume the proceeds from
stock option exercises will remain equal and consistent to prior levels. Payment of cash
dividends and purchase of treasury stock are closely linked to the equity forecast on the
balance sheet. Finally, due to their unpredictability and limited prior year activity,
we do not forecast for the payment of business acquisition liabilities, deferred financing
items, and the effective exchange rates on cash. Finally, it's important to ensure that
the change in cash balance plus the beginning balance equals the cash forecasted on the
balance sheet.
37