The budget and forecast worksheets have several built-in options for distributing a total value over the period, which can save you a lot of time when it comes to entering values.
You select the spread option and the system spreads the total value across the row using a formula. You have the option to spread a specific amount evenly or in the same proportion as selected comparison data. You can also insert values from a comparison stream for the budget, changed by a specified percentage. For example, you might want to take last year’s actual values and uplift the values by 5% or take last year’s budget and reduce the values by 10%. In a forecast worksheet, you have additional spread options; snap to budget and follow actuals.
You can apply a spread to individual or multiple account rows, or to a group of rows via the total row. The Spread window displays the number of rows that will be impacted by the spread. For example, if you do a spread on the Operating Expenses total row, the spread applies to all the Operating Expenses rows.
| Info |
|---|
It is important to note that in a forecast, the spread applies to the editable forecast cells only. For example, if you spread 5 million evenly, the system will deduct the actuals and apply an average of the remaining value evenly to the remaining periods to get the full year to 5 million. |
| Tip |
|---|
You can use the keyboard shortcut of CTRL+Z to undo bulk spread actions. |
Access the spread options
To access the Spread options, use one of these methods:
In a budget, either enter a value in a total cell (the Spread window automatically displays) or right-click a total cell > Spread.
In both a budget and forecast, hover over the right side of a total cell and click the Spread button
that displays.
Spread a total evenly
This spread option divides the total amount by the number of units in the period and spreads it evenly across that period. If you have selected multiple rows or a total row, the total is firstly spread evenly down the Total column of each applicable row, then each of those subtotals are spread evenly across the period.
Enter the amount, select the Evenly option and click Apply.
Budget exampleExamples:
Spread evenly across one row in a budget: Suppose you want to budget 84,000 for the Marketing Advertising Expense across a 12-month period, giving the same amount to each month. The 84,000 is divided by 12, so each month in the budget period gets 7,000.
Spread evenly across one row in a forecast: Suppose you have a in a 3 + 9 forecast and want to forecast 84,000 for the Marketing Advertising Expense, giving the same amount to each month. Firstly, any actual amounts are subtracted from that total figure (84,000 - 3,549 = 80,451). Then the remainder of 80,451 is divided by 9, so each month in the forecast period gets 8,939.
Spread evenly across multiple rows in a budget: Suppose you want to spread $120,000 evenly across two Marketing expenses. Each expense row gets $60,000 (120,000/2), then each cell in those rows gets $5,000 (60,000/12).
Spread a total evenly by row
This spread option is available when you have selected multiple rows or a total row. The total is firstly spread evenly down the Total column of each applicable row, according to the underlying proportionality of the original row values, then each of those subtotals are spread evenly across the period.
Example: Suppose you want to spread $120,000 across two Marketing expenses, evenly but proportionately by row. The first expense row initially has $64,664 and the second row has $82,054. After the spread, the rows get $52,888 and $67,112 respectively. Then those subtotals are spread evenly across each cell in the period. In the case of the $52,800 subtotal, each cell in that row gets $4,407 (52,800/12).
Spread a total based on a comparison stream
This spread option references a selected comparison stream's values, such as the Actuals or Sales, and spreads the total amount across the period in the same proportion as the selected comparison data.
Enter the amount, select the Based on [stream] option and click Apply.
Example: Suppose you expect the Marketing Expense to follow the pattern of last year, due to the seasonality of your business activities. If you base the 84,000 on Actuals, each month in the budget or forecast gets a proportion of the 84,000 based on the corresponding month last year.
Budget:
Forecast:
Spread a total proportionally based on a comparison stream
This spread option references a selected comparison stream's values, such as the Actuals or Sales, then inserts the total and spreads it across the period according to the reference stream’s phasing.
Select the second option on the Spread window (this might say Actuals, Sales or whatever the default data stream is called). Select the stream you want to use, then enter the percentage you want to multiply the values by, then click Apply.
Example: Suppose you expect the Marketing Expense to be proportionality higher this year compared to last year, due to rising costs. If you select last year’s Actuals and enter 10% as your expected increase, last year’s budget or forecast values multiplied by 10% are spread across this year’s budget/forecast period.
Budget:
Forecast:
Spread a total based on a comparison stream, proportionally or evenly by row
These spread options are available when you have selected multiple rows or a total row. These options reference a selected comparison stream's values, such as the Actuals or Sales. The total is firstly spread evenly down the Total column of each selected row, according to the underlying proportionality of the original row values, then each of those subtotals are either spread:
Proportionally by row - The seasonality (phasing) of the comparison stream is taken into account when the subtotal is spread across the period, so the values might differ each month.
Evenly by row - The subtotal evenly across the period, so each month gets the same value.
Snap to budget
The Snap to budget spread option is available in forecast worksheets only. It aligns the forecasted values to the original budgeted values. This spread option is useful if you know you are to going to spend a specific amount per month for the remaining periods.
For example, suppose you usually spend around 6,400 a month on Consulting fees and therefore, have a budget of 77,264 for 12 months. You have already spent 11,190 in the first month, so you might want to snap back to budget, to keep on track. The actuals are subtracted from the budget total (77,264 - 11,901) and the remaining 66,074 is divided by the number of forecast periods (9), so each month in the forecast period gets 7,342.
In the Forecast Spread window, select the Snap to budget option and click Apply.
Follow actuals
The Follow actuals spread option is available in forecast worksheets only. It takes the average of the actual values and applies that average equally to each of the forecast periods.
In the Forecast Spread window, select the Follow actuals option and click Apply.
For example, if the average of the actuals is 3,730 (11,190 / 3), each month in the forecast period gets 3,730.
On this page
| Table of Contents | ||||
|---|---|---|---|---|
|