How will the demand and sales volume change in the near future? Should we expect traffic growth or decrease? You do not have to be an analyst or do research to answer these questions. Read below how to find out general trends and spend a minimum to do so. Konstantin Rachyn, a marketer at Ringostat, shares his experience and a template of the report.
Middle HL Marketing Specialist at Ringostat
The report that I will describe is the easiest forecasting method. And it won’t take more than half an hour 🙂 It is useful if you want to see the growth or decrease of a specific metric. The report can also be used if you need to explain the management or the client a situation with income trends.
Of course, such a forecast won’t replace full research that takes into account additional factors. But you will see the main trends. This will help you to prepare yourself for changes.
Examples of use
- For a business that does not have a full-time analyst, and the company’s specialists don’t have in-depth knowledge of building forecasts. Even so, it is important to keep follow trends.
- To understand the expected volume of sales and make an approximate plan. Having these data, you can see a future increase in the demand and purchase products on time. Or, on the contrary, you have a chance to change the situation if you see that the revenue is about to decrease.
- To forecast traffic. To see what indicators to expect at current growth rates.
As a marketer, I regularly use such a report in my work. For example, recently I was building a plan for organic leads that will request a demonstration of our service. I will give a simpler example below — how to build a forecast for sales. As a tool, I described Google Sheets as I usually work there but you can build a similar forecast in Excel — it will work as well.
For clarity, I created an example of a report that I describe below. If you wish, you can make a copy and substitute the necessary data.
Step 1. Data collection
Historical data is the basis of the report. In other words, the formula “understands” patterns between previous events. Thus, it builds a forecast for the next period. Circumstances may interfere with any business processes: change in the exchange rate, a large competitor entered the market, rising prices for raw materials, etc. This forecast shows how the situation will develop if the conditions stay the same.
First of all, you need to create a spreadsheet in Google Sheets or Excel. As we are interested in sales, we take data on income, for example, over the past 20 months. The longer this period, the more accurate will be your forecast. Where can you get the data on sales:
- request from the accountant department;
- from the CRM;
- from Google Analytics, if you have an e-commerce module and data on deals are transferred there.
Depending on your goal, you can substitute here any variable that you need to predict. But pay attention that the variable has to be connected with a certain date. Next, we enter data for each month in the table. In our case, this is income:
Step 2. Enter FORECAST formula
FORECAST formula determines the estimated value of the Y parameter, based on the existing value of X.
Consider this on our example:
- X is the point in time for which we make a forecast;
- known values of Y is the number of revenue from sales for the past months;
- known values of X are dates or numbers of past periods.
Let’s say we want to find out the change in sales in the next four months. The smaller the forecast range, the more accurate the data will be. I do not recommend building such a report more than for the next six months.
We add to our document four more lines with ordinal numbers of months. We enter the following formula to each cell with the future data on the forecasted income:
=ROUND(FORECAST(A22;$B$2:$B$21;$A$2:$A$21))
Let’s analyze its components.
1. ROUND — rounds the received value. Our task is not to get the most accurate amount possible, but to identify the trend. That’s why integer values will be more clear.
2. FORECAST — the received tendency. There is an additional description of this function in the document template.
3. A22 is the cell with the future forecast.
4. $B$2:$B$21 is the data range of Y, the known values of the dependent variable. In this example, it’s the total amount of sales. $ symbol is required to not to change the fields that we take for the forecast when we will drag the formula in the table.
5. $A$2:$A$21 is the date range of the previous months with the known indicators.
We enter this formula in the cells for future months, and data with the forecast appears there.
Step 3. Create a chart
It is better to visualize the numbers for the forecast, so the data will be more clear. Go to the Insert — Chart — Chart editor — Setup section and select the type of visualization Line chart. We indicate all cells with data as a range:
- X-axis — time range;
- Y-axis — variable.
Then go to Customization — Series, scroll down and put a tick next to the Trendline. A line will appear instantly inside of the chart. This is our sales. If you build a chart without this option, it will be more difficult to understand whether the desired indicator is increasing or decreasing.
The example below clearly shows that sales tend to fall. You can see this from a trendline that goes down:
Keep in mind that if you have a seasonal business, you also need to consider a time of the year. In this case, it is better to upload data for several years. Other tools are as well used for season businesses. For example, a more advanced forecasting method, Time Series. But the described report also considers peaks, so it’s suitable for a generalized forecast.
You can easily change the appearance of the chart. To do so, simply read our tips in the article “Making Google Sheets pretty: tips on data visualization”.
Summary
We have analyzed a report that can be used for forecasting, even without being an analyst. It is useful to build it even for yourself. Indeed, in business, it is important to always focus on the goal in order to understand the possible risks.
Using the forecast, you can notice in time whether traffic is growing or decreasing, or whether it is necessary to purchase products. It will also help to discover non-obvious insights. Sometimes there are cases when it seems that the forecast is positive, according to the chart. But the trendline shows that everything is not so positive.
