Forecasting by Historical Performance

[intapiuser]

 Analytical Need 

While tracking your sales performances during the quarter and compare it with the quarterly target, wouldn’t be helpful if you’ll have an indication for your current pace and whether you’re going to meet your quota by the end of the quarter?

This article will suggest an approach to forecasting it by using last year(s) pace.

Solution

Prerequisite: having a daily target. You can use quarterly target and divide it by the count of total days to having for each day the relative portion of it.

The idea is to take historical performance* and compare its pace to the current. To do it we'll want to calculate the expected pace, where we should be at some point of time during the quarter, in terms of % of the quota according to last year (LY) pace during the same time. The formula would look like this:

For example, let’s assume that for this Q (Q1) the target is $100M, LY sales during the entire Q1 were $75M and the sales for Q1 LY till Feb 28^th (1/1/17-2/28/17) were $50M, so the Pace for 2/28/18 is expected to be $66.67M:

Let’s assume that today’s sales (QTD for 1/1-2/28 2018) are $60M. it means that according to LY pace we’re $6.67M behind of where we need to be as of Feb 28^th.

It means that if the sales team won’t do anything about it (and continue to work according to the same pace of last year) most chances they will miss their quota.

Here is an example of a dashboard which describes this case from few different angles:

From top left to bottom right, we can learn about:

1. Current quarter sales performance (and compare it with LY QTD performance). 
   note: It's a good example where the total sales year over year (YoY) is greater, but comparing to the quarterly target or to the desired pace it's not good enough.
2. How far we got in terms of % of the quarterly target:
      
   and compare it to where we should be using the following formula: 
3. The current gap we have by comparing the actual to the expected pace:
   
4. The estimated $ amount we should close the quarter with and the expected delta from the quarterly target:
   
5. How did the gap between actual plan build during the quarter (using running sum (RSUM) for both the Actual and Target).
6. And finally, a visual view of where we are (green area), where we should be at this point of time (black line) and what we left to achieve by the end of the quarter (blue area).

 Attached are the dash and ecdata files.

* In this example we took last year performance, but it can also be the average of the last 3 years, the weighted average of the last 5 years, the median of the last 6 quarters or any other approach you can think of and make the most sense for your use case.

Attachments

  - 589 KB - ForecastingbyLYPace.dash

  - 671 KB - Forecast by LY Pace.ecdata