Forecasting makes a crucial element of every business. Accurate business forecasting can help your business progress much faster than ever.
But forecasting is usually not that simple. You’d need to have specialists (statisticians mostly) on board. Or you’d need to buy specialized forecasting software. Both of which can cost you thousands of dollars. 💰
Good News! Microsoft Excel offers many tools and functions that will make forecasting easier and cheaper for you.
Let’s jump into the guide below and learn them all. Download our free sample workbook here to tag along with the guide.
Forecasting is the technique to estimate future trends based on historical data.
For example, Company A made sales worth $5000 in 2020 and $5500 in 2021. How many sales will it achieve in 2022?
The historical data of sales shows a 10% increase ($5000 to $5500) in sales over the year.
Given the historical trend of increase, we can forecast sales of $6050 in 2022. (Sales of $5500 increased by 10%).
That’s the simplest version of forecasting, and it only gets more complex.
Forecasting is only based on probabilities and estimates – which can change anytime. And the actual results may vary from the forecasted stats.
Kasper Langmann , Microsoft Office Specialist
Microsoft Excel offers many tools, graphs, trendlines, and built-in functions for forecasting.
You can use these tools to build cash flow forecasts, profit forecasts, budgets, KPIs, and whatnot.
The three main (and relatively simpler) forecasting tools of Excel include the following.
Let’s not wait anymore and dive right into each of these smart techniques.
Moving Averages are used to see a wider picture of how the numbers have been changing over periods. See here.
The image above shows the sales made over the past 12 months.
Create a simple line chart out of it to see the trend of sales over the last year.
Seems like a series of mountains 😉
The graph of sales shows steep rises and falls in sales throughout the year. It’s hard to study the trend of sales over the year, let alone forecast it for future years.
Let’s make the moving average of these sales to analyze the trend of sales better. The moving average for every two months’ sales.
There are three ways how you can apply the moving average method to forecast numbers.
We are making a two-months moving average so the first average would be calculated at the end of month 2.
1. So, activate a cell in a new column parallel to February (2nd month of our data):
2. Write the AVERAGE function as below:
= AVERAGE (B2:B3)
3. Excel calculates the average for the first months.
4. Drag and Drop this formula to the whole list.
What has Excel done? As the cell references were not absolute but only relative, the cell references change for each next average.
And so, we get the moving average for the next two months (like February and March) in the example above.
5. Turn both these series (sales and moving average of sales) into a line graph to study the trend.
6. Select the whole data set.
7. Go to Insert tab > Charts > 2D Line Chart Icon.
8. Choose any 2D line chart, as desired.
Excel plots both series on the line chart (each with a different color).
Note how the blue line has sharp highs and lows. Whereas the orange line is smoothed out. This makes it easier to study the trends over a given period.
Based on this orange trend line, you can study the historical trend of sales over the year. Assuming sales would seek the same trend in the future, you can forecast future sales.
Excel offers an in-built tool to calculate moving averages in Excel.
1. Go to Data Tab > Analysis > Data Analysis.
