The budget and forecast worksheets have several built-in options for distributing a value over the period, which saves you a lot of time when entering values. You can spread values at any level in the budget hierarchy. The impact of the spread differs depending on where you spread from and what spread method you use.
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 initiate a spread from different locations in the worksheet. Regardless of the starting point, you access the spread feature in the same way. Hover over the right side of the cell (or selection of cells) and click the Spread button that displays. In the Spread window, you can select from a range of ways to apply the spread.
Press Enter to submit your spread changes rather than click the Apply button.
Access the spread options
Hover over the right side of any Total cell (in either a row or column) and click the Spread button that displays.
In a budget worksheet, when you start entering a value in a cell in the Total column, the Spread window automatically displays. You can also right-click a cell in the Total column to access the Spread options.
Spread a total
This spread option is suitable when you know the specific total amount you want to spread. You enter that defined total amount and select how you want to spread it. The options that are available depend on whether you have one or multiple rows selected, and if any comparison rows have been added to the worksheet.
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 (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).
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.
Enter the amount, select the Evenly by row option and click Apply.
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).
Based on actuals or other comparison stream
This spread option references a selected comparison stream's values, such as the Actuals, Last year’s budget, Sales and so on, and spreads the total amount across the period in the same proportion as the selected comparison data. The Actuals option is always available, as this is the data on which the budget or forecast was built. Other options might be available, if additional comparison rows were added to the worksheet.
Enter the amount, select the Based on [stream] option and click Apply.
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.
Spread proportionally by row (actuals, other comparison stream or current data)
This spread option is suitable when you do NOT know the total amount you want to spread. This spread option references either a selected comparison stream's values (such as the Actuals or Sales) or the current displayed data, then inserts a total (either as it is or changed by an amount you specify) and spreads it across the period according to the comparison stream or current data’s phasing (proportionality).
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 phasing of the comparison stream or current data 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.
In the dropdown list, select the required option:
The stream you want to reference.
The Displayed data option.
Enter the percentage you want to change the values by.
Click Apply.
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 evenly by row based (actuals or other comparison stream)
This spread option is suitable when you do NOT know the total amount you want to spread. Similar to the option above, this spread option references a selected comparison stream's values, such as the Actuals or Sales, then inserts that total (either as it is or changed by an amount you specify) and spreads it evenly across the 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, 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.
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 vertically
You can spread a value in the Total column, or any other column, vertically into the rows in that column. You can select multiple columns and spread a value vertically into their rows.
Spread a target (calculated measures only)
Spread a target across input rows that are based on calculated measures. This is particularly useful for sales budgets, where you need to budget sales margin percentages directly in conjunction with either the sales value or cost, rather than budgeting value and cost and having margin be calculated.
Do a partial spread
The partial spread option allows you to spread a value into a selection of cells, which could be in multiple rows and columns. This option is particularly useful when you create a budget for the next period based on the current year’s actuals but the last few months in the new budget have zero values, as those months have not happened yet. In this case, you can spread a value from another comparison row into those zero cells to fill in the gaps and get a better baseline on which to start the new budget.
(Optional) Customize the comparison setup, if you plan on spreading proportionally.
In the grid, select the cells in which you want to spread the value.
Select the required spread option; you can spread a total, proportionally by row or evenly by row. Enter the total value or change the value percentage as applicable.
Click Apply.
Snap to budget (forecasts only)
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 (forecasts only)
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.
Suppose the average of the actuals is 3,730 (11,190 / 3), each month in the forecast period gets 3,730.
