Home > Blogs > Automated Forecasting with Jet Reports

Automated Forecasting with Jet Reports

The purpose of this article is to introduce the Break-back concept and how you can do automated forecasting in Jet Reports. Organizations, in their budgeting and forecasting process, may use top-down, bottom-up, or a combination of both methodologies. They may also put together different versions of the budget using what-if planning. Break-back can be used throughout the organization to assist in these processes.

Break-back is an effective tool for the management of any organization. One of the capabilities of break-back is the ability to use it as a what-if tool and in assisting management with key decision-making that improves the company’s ability to quickly react to changing market conditions.

This document will detail one specific example and describe how Jet Reports can assist in making the Break-back concept a useful tool in your organization.

Break-back Definition

Break-back is a process that allows the automatic calculation of transaction detail (i.e. budget or forecast figures) based on summary value(s). For instance, forecast 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 calculates 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

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 that is based on the prior year 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 in order to achieve the Net Income projected amount. If only 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 setup like 3% increase in Revenue, it will predict other GL accounts like COGS and Expenses to achieve target goal of Net Income.

Once 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.

Want to try Jet Reports?

Get Free License
for 30 Days

Want to try Jet Reports?

Get Free License
for 30 Days

Benefits

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, please reach out to one of our Jet Reports expert.