Are you interested into understanding how to make a manual forecast?
This post will guide you through the steps to build a manual forecast, using excel. If you want to understand more about the sales and financial forecasting techniques, we recommend you first to check our ultimate page about forecasting.You might also be interested into checking our product offer about automated forecast.
What are the objectives of this forecasting methodology
- Getting a forecast at a daily level, using historical data available
- Having a forecast reflecting both the general growth trend and the daily seasonality
- Using only Excel or any spreadsheet based tool (Google doc, Open Office, ...) or doing it automatically via an SQL automated programming script.
As usual when it comes to forecasting, this is a discipline between science and art. There is no way to predict the future with 100% accuracy, so it is your responsibility to gather as much as possible relevant information to build a forecast where you will be confortable with.
Step 1 - Collect and sanitize historical data at a daily level
The objective here is very simple. You should get a table with only 2 columns:
- the past date
- the past actual value
This sounds easy, right? Well, in most of the companies, even getting to this level can be more complex than thought in first place. You need to make sure that the data perimeter you will use to build your forecast is 100% correct, meaning, in line with the financial guidelines of your company. Exclude some dimensions, include all countries, exclude foreign exchange variations and some products and so on.
Make sure to spend some time on this data preparation and consolidation step before going further. This will save you time and prevent you from losing time and having to re-do it again.
Upload a CSV and start generating sales forecasts at scale with time series analysis.
Step 2 - Aggregate the past actuals at a weekly level
Get the general historical growth trend
You now have cleaned, vetted and read-to-use data. Good job.
The next step will be to transform these data and to aggregate them at a weekly level. The purpose of this setp is to then to estimate a week over week growth rate. This will make it possible for you to project an ideal growth trend.
Add an extra column with the week number or, even better, the last day of the week. You will be able to visualize a general growth pattern, once cleaned from daily variations. Just use a pivot table to do so.
Project an estimated average weekly growth rate
You have two solutions to do so, depending on the general growth pattern observed.
Option A: If you see that the week over week growth rate is relatively stable over the past few weeks and you know that this will go on like that, then you can use a CAGR. Compouned annual growth rate makes it possible to compute the average growth observed between two historical points.
Choose carefully the first and last weeks, calculate the CAGR and apply it to the future weeks.
Option B: if you see that the week over week growth is not steady enough, you could change the view and use a year over year progression. If, for some reasons related to the very nature of your business activity, some weeks are growing faster than others, then you should definitely try to compare each particular week with the same week last year. You might get a growth rate more stable which, once tweaked with your business accumen, might sounds good to go.
Other ideas to go further...
You could also compute a quarter over quarter growth rate as a general guide. This super aggregated piece of data can also help you understand where the business is going to, in case the week over week seems to be too much volatile.
Take time to review and adjust manually all the weeks. Is there a special event occurring, such as a bank holiday, black friday or Christmas? Then don't hesitate to adjust the weekly aggregated number in consequence.
For your forecast, develop multiple scenarii: one conservative scenario, one bullish and one average. You will have multiple level of guidance which you can use to adjust your numbers.
Step 3 - Compute the daily seasonality and apply it to the weekly aggregated number
At this stage, you have a prediction which sounds reasonable to you, at a weekly level. You want your manual forecast to be a daily level, hence combining weekdays and weekends effects.
Build a multiplier table
Before going through the details, the general idea is the following.
You take the weekly total number, divide it by 7. At this stage, each day has the same number and weights the same. Then, for each day of the week, you will apply a multiplier factor to reflect its actual weight.
How to build this multiplier / daily seasonal factor?
Go back to your original dataset.
- Add an extra column, which will be the day of the week corresponding to the date: monday, tuesday, wednesday... or its corresponding number, that's similar.
- Add another column, for the quarter of the year
Now, build a pivot table and aggregate the weekly value with the 1) quarter and 2) day of the week. Transform this absolute number into a relative (%) index of the average.
You will have something like this:
|Day of week in Q1||Multiplier factor|
|Average of Mondays last years in Q1||0.89|
|Average of Tuesdays last years in Q1||0.92|
|Average Wednesday last years in Q1||1.12|
Apply now this coefficient to the weekly values you got. You should see the magic happening and get a manual forecast reflecting the general trend and the daily seasonality.
What's next regarding our manual forecast?
Get it automatized with an SQL script!
Once you have the methodology, the next step will be to collaborate with a IT programmer or a data analyst to get this routine automatized via SQL scripts. The whole process should be easy to develop, provided you can write your methodology down and translate each steps and corrections.
You should then be able to build a high level rolling forecast and get the latest adjustement, based on the latest development.
An idea could be, for the manual forecast, to use the latest 7 days and not the calendar week. You could have J-7, J-7/14, ... so that you could build every day a new forecast, using the daily actuals from yesterday and won't have to wait until the calendar end date of the week. It's up to you and depends on the needs of your business
Complete it with a more sophisticated automated forecast
This methodology is quite simple. You might want to build in parallel a second forecast, this time using proper data science tools. There a plenty of algorithms available when it comes to forecasting, some more simple than others. You should check our solution to quickly build an algorithmic based forecast.
By doing so, you will have two complementory approaches to use and to see where your business is heading to.