Jet Reports Training
June 27, 2024
Automated Forecasting with Jet Reports
- Global Data 365
In today’s fast-paced business environment, organizations need efficient methods to streamline budgeting and forecasting processes. This article introduces the Break-back concept and demonstrates how it can be implemented with Jet Reports to automated forecasting. Organizations typically employ top-down, bottom-up, or hybrid methodologies in their budgeting and forecasting. Additionally, they often create multiple budget versions using what-if planning to simulate different scenarios. Break-back is a powerful tool that can assist in these processes across an organization.
The Power of Break-back in Decision Making
Break-back is a valuable management tool that facilitates what-if analysis and supports key decision-making. It empowers organizations to quickly adapt to changing market conditions by providing accurate automated forecasting based on historical trends and assumptions. This article explores how Jet Reports can harness Break-back to enhance forecasting and budgeting processes, making it an indispensable tool for any organization.
What is Break-back?
Break-back is a process that allows the automatic calculation of transaction details (i.e. budget or forecast figures) based on summary value(s). For instance, automated forecasting all natural accounts based on the user entering a single net income target value.
It is calculated based on assumptions that can be determined on historical averages, trends, or an infinite number of other factors. The example below will allow a user to enter a Net Income value and then automatically calculate values for all GL accounts. It also enables the user to increase or decrease values from a macro or micro level, which allows for deeper what-if analysis.
As an example, project a 25% increase in Net Income while knowing that revenues will only increase by 3%, then the Break-back will calculate the amount that all expenses need to decrease to match the 25% increase in Net Income.
Example of break-back:
This example will detail the building of a Break-back template that has year-to-date actual figures and will calculate the general ledger-level forecast based on a few assumptions.
The main driver and the only mandatory input is the desired Net Income. This alone will enable the calculation of the general ledger (GL) forecast for the remaining months.
The first step is to create a profit and loss report with a column for year-to-date actual data. Then we will use the concept of annualized actual based on the prior year’s actual revenue. There is a hidden section that brings in the prior year’s revenue, calculates the percentage of revenue for year-to-date, and then assumes that the annualized total will follow the same trend as last year. As an example, if the period parameter is entered April 2019, then the annualized actual will be year-to-date actual figures divided by the percentage of the sum of the revenue for Jan 2018 to April 2018 against all of 2018.
– Revenue Year-to-Date April 2019: $8,675,315
– Total Revenue Year-to-Date 2018 (January – April): $5,889,672
– Total Revenue 2018: $20,400,768
– Percentage of Total Revenue 2018 Year-to-Date: 28.87%
– Annualized Product Revenue 2019 (8,675,315/28.87%): $30,049,584
The next column is of What-If Scenario that is based on two drivers:
– Net Income Projected Amount
– Account Increase/Decrease %
This column projects what GL Accounts should be like to achieve the Net Income projected amount. If only the Net Income driver is set up, Excel will calculate all GL Accounts based on the historical trend and multiplying it by the new set goal for Net Income. However, if account Increase/Decrease % is also set up like 3% increase in Revenue, it will predict other GL accounts like COGS and Expenses to achieve target goal of Net Income.
Once the What-if scenario is completed, it is then divided across months to predict forecasting of each month. This uses historical trend of how each month contributes to the total year’s figures.
Benefits of break-back:
This template could help businesses in many ways such as:
- Helps create top-down budgets and forecasts based on organizational targets.
- Quickly create multiple budget or forecast versions (i.e. optimistic, pessimistic, and most likely).
- Starting point of forecast or budget.
- Can use as guidelines for budget users.
- Save labor hours by eliminating time consuming end user data entry.
- Helps business to stay on track each month.
- Helps in decision making.
- Helps in future cash projection.
Resources
Using Jet suite of products, you are now able to pull your actual data right into Excel and can-do automated forecasting using break-back. In case you need to design What-If scenario templates for your organization, you can reach out to one of our Jet Reports experts.
Get 30 days free license for Jet Reports.
Search Blog
Related Resources
Management Reporter vs. Jet Reports
February 13, 2024
Jet Basics vs Jet Reports – Should You Upgrade?
March 26, 2021
