This Sage Intelligence tip will show you how you can create a rolling income statement, which will combine actuals, budgets and forecasts into a single layout, such as the one below:
So let’s start off with a few definitions:
Budgeting:
In essence, a budget is a quantified expectation for what a business wants to achieve. Here are a few characteristics of a budget:
- A budget is a detailed representation of the future results, financial position, and cash flows that management wants the business to achieve during a certain period of time.
- A budget may only be updated once a year, depending on how frequently senior management wants to revise information.
- A budget is compared to actual results to determine variances from expected performance.
- Management takes remedial steps to bring actual results back in line with the budget.
Forecasting:
Conversely, a forecast is an estimate of what will actually be achieved. Its characteristics are:
- A forecast is updated at regular intervals, perhaps monthly or quarterly.
- A forecast may be used for short-term operational considerations, such as adjustments to staffing, inventory levels, and the production plan.
- There is no variance analysis that compares the forecast to actual results.
Thus, the key difference between a budget and a forecast is that the budget is a plan for where a business wants to go, while a forecast is the indication of where it is actually going.
How to use budgeting and forecasting in a Sage Intelligence rolling income statement
So imagine this scenario, you are in period 3 of a 12 period financial year. You need to be able to see if you are going to hit you year-end target based on a variety of possible scenarios. So here’s a tip of what you should do:
- In the Sage Intelligence Financial Report Designer open a blank Excel worksheet
- Add report headings including a title, and the fields Company Name, Current Year, Month End Period for Actuals and Month End Period for Budgets
3. From the lists menu pull in the accounts that you would like to see reflecting in the income statement. Arrange your income statement in the way that makes sense to you and fill in the headings as in the example below
- Now it’s time to think of a few scenarios you want to forecast for your business. Here are some examples:
The percentages shown in this image can be easily adjusted using the sliders. These will intern update the forecasting columns in your report.
To set up your sliders, do the following:
- Firstly make sure you have the Developer tab in your ribbon
If you do not, go to File > Options > Customize Ribbon and check the Developer tick box
- Insert the forecasting sliders and connect them to your report
a. From the Developer tab, add a slider
b. Once you have inserted the slider, you need to tell it which cell you want it to reference. To do this right click on the slider and select Format Control. In the cell link box, select the column that you would like to reference. In this case I have used X24 and applied absolute referencing. X24 is my Revenue row and is to the right of my report data
c. In column S, I have turned the contents of X24 into a percentage. This makes it easier for you to understand the logic behind the forecasting. Column S is also to the right of my report data.
d. Repeat steps a to c for each slider you want to include in your layout
- Update your column headings and insert your formulas
To make the income statement roll, you need to use Excel if statements to logically deduce whether a column is an Actual, Budget or Forecast column and update the column headings and formulas accordingly.
You would do this based on the following rules:
- If the period for a column (B above) is less than or equal to the Month End Period for Actuals (A above) then the column is an Actual The financial formula that should be used for the column is the Actual formula.
- If the period for a column (B above) is greater than the Month End Period for Actuals (A above) and less than or equal to the Month End Period for Budgets (A above), then the column is a Budget The financial formula that should be used for the column is the Budget formula.
- If the period for a column (A above) is greater than the Month End Period for Budgets (A above) then the column is a Forecast The financial formula that should be used for the column is the Budget formula for the previous period multiplied by the forecast slider for the row.
Once you have finished updating the layout, you can move the forecast sliders from left to right and you will see your report updates dynamically to reflect the scenarios.
Now you can clearly see if any of your scenarios are realized, this will be what your year-end finances will be.
To finish off the report layout, you can use standard Excel functionality to insert a graph to clearly depict the profit or growth of your business.
