Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
Financial forecasting methods and models
1. Learning outcomes
• Differentiate kinds of forecasting methods and models
• Utilize MS Excel in financial forecasting
2. Introduction
Financial forecasting arms executives with specific and accurate predictions they can
use to make plans for the company, barring unforeseen circumstances. Those plans can affect
everything from budgeting, hiring, sales goals and earnings predictions to financing decisions
and institutional investment goals.
In short, financial forecasting is at the very heart of every decision executives make.
Without it, they’re blindly leading the company forward and possibly off a cliff.
Financial forecasting is not a one-size-fits-all practice. It’s a collection of techniques and
methods that executives choose from depending on the data they’re using and the purpose of
the output.
3. What is financial forecasting?
Financial forecasting arms executives with specific and accurate predictions they can use to
make plans for the company, barring unforeseen circumstances. Those plans can affect
everything from budgeting, hiring, sales goals and earnings predictions to financing decisions
and institutional investment goals.
In short, financial forecasting is at the very heart of every decision executives make. Without
it, they’re blindly leading the company forward and possibly off a cliff. Financial forecasting is
not a one-size-fits-all practice. It’s a collection of techniques and methods that executives
choose from depending on the data they’re using and the purpose of the output.
4. Financial forecasting methods and models
a) Qualitative vs. quantitative methods
Financial forecasting methods fall into two broad categories: quantitative and qualitative.
The first relies on data that can be measured and statistically controlled and rendered. The
latter relies on data that cannot be objectively measured.
It’s important to note that no financial forecast is foolproof since you are mapping the road
ahead by looking in the rearview mirror. However, when done properly, forecasting is generally
reliable.
b) Top-down vs. bottom-up approach
Top-down forecasting, as the name suggests, starts from a high-level perspective, and
then trickles down to the specifics. This vantage point makes it easier to gather essential intel
to predict a company's financial trajectory, including:
Historical company performance
Growth rates across the industry
Key economic indicators
Example:
Page 1 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
Bottom-up forecasting takes a more granular approach to sales forecasting. It starts from
the ground level and builds up toward the overall financial outlook. This method is all about
nurturing the wisdom of individual departments and teams. It's a great option because it
focuses on using their insights to create detailed forecasts tailored to specific areas of the
business.
Bottom-up forecasting as "breaking a business apart into the underlying components that
ultimately drive its revenue generation, profits, and growth." (see formula image below)
Distinction between Top-Down and Bottom-Up Approaches
5. Top Forecasting Methods
There are four main types of forecasting methods that financial analysts use to
predict future revenues, expenses, and capital costs for a business. While there are a
wide-range of frequently used quantitative budget forecasting tools, in this article we
focus on four main methods: (1) straight-line, (2) moving average, (3) simple linear
regression and (4) multiple linear regression.
Page 2 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
1. Straight-line Method
The straight-line method is one of the simplest and easy-to-follow forecasting
methods. A financial analyst uses historical figures and trends to predict future revenue
growth.
In the example provided below, we will look at how straight-line forecasting is
done by a retail business that assumes a constant sales growth rate of 4% for the next
five years.
1. The first step in straight-line forecasting is to determine the sales growth rate
that will be used to calculate future revenues. For 2016, the growth rate was 4.0%
based on historical performance. We can use the formula =(C7-B7)/B7 to get this
number. Assuming the growth will remain constant into the future, we will use the same
rate for 2017 – 2021.
2. To forecast future revenues, take the previous year’s figure and multiply it by
the growth rate. The formula used to calculate 2017 revenue is =C7*(1+D5).
Page 3 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
3. Select cells D7 to H7, then use the shortcut Ctrl + R to copy the formula all the
way to the right.
2. Moving Average
Moving averages are a smoothing technique that looks at the underlying pattern of
a set of data to establish an estimate of future values. The most common types are the
3-month and 5-month moving averages.
1. To perform a moving average forecast, the revenue data should be placed in
the vertical column. Create two columns: 3-month moving average and 5-month moving
average.
2. The 3-month moving average is calculated by taking the average of the current
and past two months’ revenues. The first forecast should begin in March, which is cell
C6. The formula used is =AVERAGE(B4:B6), which calculates the average revenue from
January to March. Use Ctrl + D to copy the formula down through December.
Page 4 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
3. Similarly, the 5-month moving average forecasts revenue starting in the fifth
period, which is May. In cell D8, we use the formula =AVERAGE(B4:B8) to calculate the
average revenue for January to May. Copy the formula down using shortcut Ctrl + D.
4. It is always a good idea to create a line chart to show the difference between
actual and MA forecasted values in revenue forecasting methods. Notice that the 3-
month MA varies to a greater degree, with a significant increase or decrease in historic
revenues compared to the 5-month MA. When deciding the time period for a moving
average technique, an analyst should consider whether the forecasts should be more
reflective of reality or if they should smooth out recent fluctuations.
Page 5 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
3. Simple Linear Regression
Regression analysis is a widely used tool for analyzing the relationship between
variables for prediction purposes. In this example, we will look at the relationship
between radio ads and revenue by running a regression analysis on the two variables.
1. Select the Radio ads and Revenue data in cell B4 to C15, then go to Insert >
Chart > Scatter.
Page 6 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
2. Right-click on the data points and select Format Data Series. Under Marker
Options, change the color to desired and choose no borderline.
Page 7 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
3. Right-click on data points and select Add Trendline. Choose Linear line and
check the boxes for Display Equation on the chart and Display R-squared value on the
chart. Move the equation box to below the line. Increase line width to 3 pt to make it
more visible.
Page 8 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
4. Choose no fill and no borderline for both chart area and plot area. Remove vertical
and horizontal grid lines in the chart.
5. In the Design ribbon, go to Add Chart Element and insert both horizontal and
vertical axis titles. Rename the vertical axis to “Revenue” and the horizontal axis to
“Number of radio ads.” Change chart title to “Relationship between ads and revenue.”
Page 9 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
6. Besides creating a linear regression line, you can also forecast the revenue
using the FORECAST function in Excel. For example, the company releases 100 ads in
the next month and wants to forecast its revenue based on regression. In cell C20, use
the formula = FORECAST(B20,$C$4:$C$15,$B$4:$B$15). The formula takes data from
the Radio ads and Revenue columns to generate a forecast.
7. Another method is to use the equation of the regression line. The slope of the line is
78.08 and the y-intercept is 7930.35. We can use these two numbers to calculate forecasted
revenue based on certain x value. In cell C25, we can use the formula =($A$25*B25)+$A$26 to
find out revenue if there are 100 radio ads.
Page 10 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
4. Multiple Linear Regression
A company uses multiple linear regression to forecast revenues when two or more
independent variables are required for a projection. In the example below, we run a
regression on promotion cost, advertising cost, and revenue to identify the relationships
between these variables.
1. Go to Data tab > Data Analysis > Regression. Select D3 to D15 for Input Y
Range and B3 to C15 for Input X Range. Check the box for Labels. Set Output Range at
cell A33.
Page 11 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
2. Copy the very last table from the summary output and paste it in cell A24. Using
the coefficients from the table, we can forecast the revenue given the promotion cost and
advertising cost. For example, if we expect the promotion cost to be 125 and the
advertising cost to be 250, we can use the equation in cell B20 to forecast revenue:
=$B$25+(B18*$B$26)+(B19*$B$27).
6. Quiz / MS Excel Exercise
References
Page 12 of 13
Professorial Lecturer: Module 3_Financial forecasting
Dr. Domingo T. Balse, Jr, LPT methods and models Lecture Notes
Book References:
Benninga, S. (2022). Financial Modeling. MIT Press
Brigham, E.F., Ehrhardt, M.C. (2017). Financial Management: Theory and Practice. Cengage Learning.
Mayes, T.R. (2017). Financial Analysis with Microsoft Excel 2016, Cengage Learning.
Pignataro, P. (2015). Mergers, Acquisitions, Divestitures, and Other Restructurings. Wiley.
Rosenbaum, J., Pearl, J. (2013). Valuations, Leveraged Buyouts, and Merge & Acquisitions. Wiley.
Samonas, M. (2015). Financial Analysis Forecasting and Modelling. A Framework for Long Term
Forecasting. Wiley.
Titman, S., Martin, J.D. (2015). Valuation: The Art and Sciences
Online Resource: https://corporatefinanceinstitute.com/resources/financial-modeling/forecasting-methods/
Page 13 of 13
