Understand how the Actuals Import from Google Sheets impacts your Forecast Model
You may encounter a situation where your Forecast deviates from your expectations, especially after rolling forward your Forecast to include the most current Actual month. This may be due to the import of Actuals from Google Sheets if you have forecasted data driven by Custom Actuals.
Google Sheets or Excel Import of Custom Data is one of the Month-End Close Steps. There can be multiple custom calculations where the Actual values impact the Forecast such as the number of customers, website traffic, and number of demo requests, etc.
This article explains the importance of Google Sheets import for the simple Bookings & Revenue model.
Be Aware: The example below is a common methodology used for MRR planning. This example should be adapted as needed to fit your business planning requirements.
Importance of Custom Actuals Import
Below Bookings & Revenue Model is based on the ARR and MRR Rollforward schedule where Actuals are imported from Google Sheets:
Forecast calculation follows the below logic:
ARR Rollforward:
➕ ARR Beginning: Last Month's ARR Ending
➕ New ARR: manual input of New Bookings
➖ Churn ARR: manual input of Lost Bookings
equals ARR Ending
MRR Rollforward:
➕ MRR Beginning: Last Month's MRR Ending
➕ New MRR: New ARR / 12 months
➖ Churn MRR: Churn ARR/ 12 months
equals MRR Ending
ARR and MRR Beginning balances are based on the Last Month's Ending balances, so on the values imported from Google Sheets:
MRR Ending Custom line is linked to the Revenue presented under the planned Financial Statement:
Bookings & Revenue Custom table maintain also Accounts Receivable Section where:
- Invoicing equals New ARR (linked to the Accounts Receivable via Balance Sheet Driver Increase)
- Collection is based on Net 60 delay payment terms, so Invoicing from 2 months ago (linked to the Accounts Receivable via Balance Sheet Driver Decrease)
Impact of missing Custom Import
Plan 3/2024 includes Actuals up to February 2024 and the Forecast period starts from March 2024. To include the most current month of Actuals we need to roll forward that Plan.
Additional Resources:
- Complete these steps each month to streamline your month-end close reporting process: Month-End Close Steps
- A Rolling Forecast has YTD Actuals + Forecast. When an additional month of actuals is available, a new Rolling Forecast should be created. Complete these steps each month to roll forward your forecast: Rolling Forecast Checklist
Rollforwared 4/2024 Plan includes Actuals up to March 2024 and the Forecast period starts from April 2024.
As mentioned above, Google Sheets Import of Actual Custom Data is one of the Month-End Close Steps. This step is very important to complete because as it's visible in the Bookings & Revenue Model, Actuals coming from Google Sheets impact Forecasted ARR Beginning, MRR Beginning and Collection lines.
In case, when the above step is skipped, both planned Revenue and A/R Collection do not flow correctly to the model:
Correction of Custom Update
To correct the 4/2024 Plan we need to populate March Actuals under Google Sheets and import it back to Jirav:
Once, Google Sheets import is done Actual ARR and MRR are uploaded to Jirav, ARR Beginning and MRR Ending in April 2024 are based on the populated in Excel ARR Ending and MRR Ending from March 2024 and ARR Ending, MRR Ending and A/R Collections are correctly maintained under the Forecast Period:
