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.
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.
You can use the keyboard shortcut of CTRL+Z to undo, or CTRL+Y to redo, bulk spread actions. You can press Enter to submit your spread changes rather than click the Apply button.
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.
Examples:
Budget (one row): 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.
Forecast (one row): 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.
Budget (multiple rows): 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.
If 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 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.
Select the Proportionally by Row option, select the stream you want to use, enter the percentage you want to change 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 (one row):
Budget (multiple rows):
Spread a total evenly by row based on a comparison stream
Similar to the option above, 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.
If 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 the subtotal is spread evenly across the period, so each month gets the same value.
Select the Proportionally by Row option, select the stream you want to use, enter the percentage you want to change 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 (one row):
Budget (multiple rows):
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.
