Most organizations tend to have a sales plan or forecast for the next 1 year.This is done for internal planning as well as give guidance to financial investment analysts covering the listed company.
However a lot of organizations use simplistic linear models of
1) either growth based on previous history (Last year Sales * Factor of forecast (e.g 10 % growth in sales) -TIME SERIES APPROACH
OR
2) growth based on macro economic causal factors (e.g economy is in recession hence sales will grow by 3 %) REGRESSION BASED APPROACH and
3) A consensus of industrial factors (We have spare capacity of 10 % so we will likely slash prices and have sales growth of 2 % but profit growth of -3%) DELPHI BASED APPROACH (this is also based on bottoms up market feedback and top down sales pressure).
A better approach is to combine all these approaches in one or different models .
This can help build a much more robust forecasting model for organizations using nothing more than simple combination of excel cells.
The following model assumes only seven factors and tries to build a stable and relatively easy to understand forecast model.
Forecasted Sales for this quarter =
Historic Sales for this quarter last year *A1
+ Historic Average Sales for this quarter for past three -five years (based on industry cycle ups -downs)*A2
+ Historic Sales for this quarter/Actual Sales of Last Quarter( for seasonal factors )*A3
+Causal Factor 1 ( Eg. Outsourcing is likely to grow by 15 % in this year) *A4
+Causal Factor 2 (Foreign Exchange Movement.Dollar is likely to depreciate by 10 %)*A5
+ Causal Factor 3 (Our bench strength is likely to grow by 3 % in this quarter)*A6
+ Percentage Error Factor *A7 (There will always be +-5 to15 % error in forecasts.Capturing this error also helps provide a feedback loop for planning).
Here A1- A7 are constants
In order to get actual values of A1-A7 , run this a regression (use the add-in and tools menu in excel) on actual data for past three years quarters (keeping last six months seperate)
Then run the actual equation on last two quarters and check for actual error. If error exceeds the comfort level (+-3 % for critical industries and +-15 % for harder to predict industries) . Iterate the last two steps till you get a good equation.
Then substitute in the 7 factor predictive model to build your simple and robust sales plan for this quarter.
Happy forecasting !!!
DECISION STATS 