Forecasting Sales Techniques

This document discusses various techniques for forecasting sales, including time series analysis, econometric analysis, smoothing techniques, and economic barometer tools. It explains the components of time series data and the models used for analysis. It also covers the use of regression analysis in econometric analysis and the concept of smoothing techniques. Additionally, it introduces the concept of leading indicators as a tool for sales forecasting.

Future reference: Aswath Damodaran, Penman
Valuation is a forward-looking phenomenon. Valuation is a micro variable. It differs for each and
every people. That is why, for the same security, some are buyers and others are sellers.
Issues 01: Major approaches to valuation:
 1st approach - ABV (Asset based valuation)
Value of firm/Book value of equity = Asset (BS) – liability (BS) = Anchor value
It is applicable for financial service providing companies (bank, NBI, trading companies, mutual
fund). If a bank operates in an economy where interest rate is more stable; here book value of
equity is a good proxy for its value.
Problems: 1. Asset side of a firm is generally understated because internally generated intangibles
are not a part of the asset.
2. At times, there will be some assets whose reported value will be distinctly different from their fair
value. Ex – Accounts receivable (quasi-fair value because allowances are created); PPE (carrying
value), land (historical cost)
3. Not always quoted price is fair value. (Price can be influenced by buyer if market cap is smaller)
4. price paid to acquire an asset (BS) ≠ Future value extraction from an asset (laptop usage differs
across users)
 2nd approach - Real option-based valuation/contingent valuation
Titas valuation = Value (no-option to expansion) + value of option to expansion [B-S, decision-tree
approach]
 3rd approach Absolute valuation
Value0 = BV of equity/Anchor value0 + value 1
( 1+discount rate ) 1 + value 2
( 1+discount rate )2 +
value3
(1+discount rate)3 + Continuing value/Terminal value 3
(1+discount rate)3 ;
Anchor Value = value as per last available BS
where terminal value = value 4=[value 3∗( 1+terminal∨ perpetual growthrate ) ]
discount rate−terminal growthrate
[note: we are assuming that our investment horizon is 3 years]
Major models of absolute valuation – DDM, FCFF, RE
Differences among the major absolute valuation models
Particulars DDM – Dividend
discount model
FCFF – free cash flow model RE-residual earnings model
Value definition Cash dividend & stock
repurchase/treasury
stock
Free cash flow to the firm =
EBIT (1-tax rate) – capital
expenditure – change in
working capital + depreciation
Residual earnings = net
incomet – benchmark incomet =
Net incomet – [Equityt-1 *
discount rate]

& amortization
Anchor value NO NO YES
Risk adjustment No No Yes
Discount rate Ke WACC Ke
Valuation is a forward-looking activity, no doubt about that. But whenever I make
payment, I pay for both present value of the firm and future value. So, RE is better from
this perspective.
Two generic approaches to project future dividend, FCFF, RE:
a. Pro-forma approach – Here we project the forward-looking IS & BS.
b. Parameter-led approach – Here we will depend only on some key ratios and figures.
Equity analyst’s target: Next year’s dividend
Pro-forma approach Parameter-led approach
Step 1: Next year’s sales [ past year’s sales * SGR – sales growth rate]
Step 2: Next year’s COGS [historical COGS/sales]
Step 3: Next year gross profit
Step 4: Next year’s administrative expense [historical average]
Step 5: Next year’s selling cost [projection done by selling cost/sale]
Step 6: Next’s year’s EBITDA
Step 7: Deduct the depreciation & amortization [projection - historical
average depreciation rates]
Step 8: Next EBIT
Step 9: Deduct next year’s interest expense [Projection – next year’s
loan balance, interest rate & loan tenure]
Step 10: EBT
Step 11: Deduct tax [ projection – historical average tax rate]
Step 12: Net income
Step 13: Next year’s dividend [ projection – historical DPR]
Summary: In order to estimate next year’s dividend, under pro-forma
approach, we need to forecast the whole I.S. it required a number of
projections, data management and all that
Step 1: last year’s sales is a known
variable. So,
Next year’s sales = past sales * SGR
Step 2: Historical NPM (net profit margin)
is a known variable.
Next year’s Net income = next year’s
sales * NPM
Step 3: Historical DPR (dividend payout
ratio) is also known.
Next year’s dividend = Next year’s net
income * DPR
Summary: In order to estimate next
year’s dividend, under parameter-led
approach, we do not need to forecast
the whole I.S; rather we need only three
parameters – SGR, NPM, DPR. it
required very little projections, data
management and all that. So, chances of
error is less.
What are necessary key parameters we need to project future dividend, FCF & RE?
Absolute valuation models Parameters needed
DDM SGR, NPM, DPR, ke, g (terminal growth rate)
FCFF SGR, OPM [operating profit margin], WACC, g, capital utilization rate,
tax rate
RE SGR, NPM, DPR, ke, g (terminal growth rate)
 4th approach - Relative valuation –
Major model of relative valuation – P/E, P/BV, P/CF, P/Sales
Model Criteria Square
Source:
Beximco
Source:
Square price
(in taka)
Multiple Beximco valuation

annual/Quartey
(
annual/Quartey
(
Source: market
P/E multiple EPS 10 taka 7 taka 50 5 times 35 taka
P/BV multiple BVPS 20 taka 10 taka 50 2.5 times 25 taka
P/Sales multiple SPS 25 taka 17 taka 50 2 times 34 taka
P/CF multiple CFPS 5 taka 2 taka 50 10 times 20 taka
Advantages:
 Data quality is rich – third-party verified data, public domain availability/ Free Data
 Implementation is super easy
Disadvantage:
 It is difficult to find a proper peer.
 GIGO – Garbage in garbage out. Do not use relative valuation when the market is either
overvalued or undervalued
Future reference: Penman
Issues 02: What makes a valuation model good?
 Parsimony – The little data used, the better model it becomes
 Verifiable – A better model should come up with a verifiable number – EPS range, price
range
 Finite time frame – not exceedingly extended

Today, we will discuss about the theoretical foundation about how to forecast crucial
variables like sales, growth rate etc. in excel.
Issue 1: Sales forecast techniques
Reference – Salvatore (Managerial economics)
Technique 1 – Time series analysis: [Reference: Lind & Marshal; Henke]
Time Series data means collecting data for a single subject (company) over different time
intervals.
Components -
1. trend (long-term average pattern is trend, for example trend of our GDP is
increasing),
2. cyclical variation, (Sales may see peak and trough, recovery, recession which refers
cyclicality)
3. seasonality & (in summer, demand of sales of cold drinks increases)
4. noise (irregular variation like war, natural calamity, sales may decrease)
Models: Additive model: Yt = T+C+S+I; multiplicative model: Yt = T*C*S*I (so, components
can be used in summation format or product format, however product format is better)
Here, y = sales
T = time
Technique 2 – Econometric analysis
In countries like Bangladesh, we cannot relate economy with sale of a company. But for
developed economies, sales are highly correlated with the specific industry and economy.
Yt = f(GDPt, Pricet, Competitor Pricet, Advertisement budgett)
Estimated equation: Y= 3million + 1.2*GDP - .34 Price + .09 CP+ 1.2*Advertisement Budget
Here, we will run regression in excel. If we know/guess next year’s GDP, Price, competitor
price & advertisement budget, then using the above equation, I can easily project next
year’s Y (sales). But sometimes, things might not be that simple and we need to develop a
system of equation.
System of equations: GDPt = C+ I+ G+ Net export [macro-economic identity]
C= α1 +β GDPt + ut [Equation 1]
I = α2 +β*It-1 + β*rate of returnt-1 +ut [Equation 2]
G= α2 +β Gt-1 +ut [Equation 3]
We can see GDP/G etc. are both independent and dependent variable here. So, we cannot
simply run the Ordinary least squares (OLS) regression to predict sales.

Technique 3 – Smoothing techniques: This technique is used for the data set where there is
no visible trend, cyclical variation or seasonality. Rather there is too much noise (irregular
variation) in the data set. We can use time series analysis for matured companies. But what
about startups where there is too much volatility? To bring them into model, we use
smoothing technique.
There are two ways to conduct smoothing technique-
1. Moving average: Here, we take average of three months or four months sales so that
sales become more stable (neutralize noise to some extent)
2. exponential smoothing:
Exponential smoothing: Yt+1 (next year sales) = a*Yt +b*Yt-1; [a+b=1; b= 1-a]
Yt+1 = a*Yt +(1-a) *Yt-1
Yt = a*Yt-1 +(1-a) *Yt-2
Here, a and b are weight. Last year will get more weight than 10 years weight.
Technique 4 – Economic barometer tools: Leading indicators are the indicators which
changes before the changes in the aggregate economy.
Leading indicators (convergence/divergence among indicators (Convergence means in the
same direction. If among 10 leading indicators, all the indicators are saying sales will
increase, so the strong is signal and vice versa), duration of signals (the higher the duration,
the strong the signal is)]