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.