Forecasting Sales Run Rate: Leveraging Historical Performance for Accurate SaaS Projections

A run rate is a forecast based on current performance. For SaaS companies, this metric is interesting to compute as their future performance is highly influenced by the actual ARR of the company. For example, if a company sells yearly subscriptions, the sales “seasonality” will be influenced by contract anniversary.

Business intent

The purpose of this article is to show how to compute a forecasting for the current quarter, based on performance of the same quarter during the last 2 fiscal years. The approach is quite simple. Let’s illustrate our business intent with an example. We want to forecast the sales for the current quarter, based on 5 weeks of data and 2 years’ history. We just started week 5 of the quarter.

• TFQ = This Fiscal Quarter
• Cum. = Cumulative
• SPLY = Same Period Last Year
• SP2FYA = Same Period 2 Fiscal Years Ago
• SPP2FY = Same Period Previous 2 Fiscal Years
• % GT = Percent Grand Total

So, at the end of week 4, we forecast ending the quarter at 1,496 k€, because our weekly cumulative performance for the same quarter for the same period the last 2 fiscal quarters represented 27.4% of the grand total. If we divide our current cumulative performance by the % GT SPP2FY, we get 1,496 k€.

Preparing a data model

We’ll reproduce this Excel table with Power BI, using a sample SQL database AdventureWorks. Our data model has only 2 tables. I created the date table with the DAX Template from SQLBI, because if offers much more possibilities than the date table available from AdventureWorks. Indeed, we’ll need columns such as the relative position of the current quarter.

First, I created a measure to store the sum of sales amount.

Sum Sales Amount = CALCULATE( Sum(FactInternetSales[SalesAmount]) )

Then I created 2 calculated columns on the date table to have the week number for each quarter. Firstly, I created “FW Day Of Quarter”, which ranks days of each quarter.

FW Day Of Quarter Number = ‘Date'[Date] – [Fiscal StartOfQuarter] + 1

From this field, I created “FW WeekOfQuarterNumber”, which create a week number out of the field above.

FW WeekOfQuarterNumber = INT ( CEILING ( ‘Date'[FW Day Of Quarter Number] / 7, 1 ) )

Creating the run rate measure

Lastly, I created the whole “Forecast” measure, step by step, based on the Excel table above.

I copy pasted this measure 6 times to create a table in my Power BI page, to give some context to the forecast measure. Only the name of the measure and the RETURN line are different.

The SQL database AdventureWorks isn’t the best to show this formula. If we look at the full quarter, here’s what it shows.