SPREADSHEETS - 30.06.2020

Using Excel’s MOD function when drawing up budgets

Allocating amounts across months

When creating budgets and forecasts you need to allocate amounts across months. For example, you might want to budget for the electricity bill on a quarterly basis. Using a powerful, but underused, function in Excel called MOD, you can simplify the process.

Tip. For this allocation technique to work, the number of allocations need to divide evenly into twelve, so it will only work for amounts that are paid bi-monthly, quarterly, tri-annually and six-monthly.

Using the MOD function

The MOD function returns the remainder of two numbers after division. For example, MOD(10,3)=1 because, if you remember your maths from school, 10 ÷ 3 = 3 remainder 1. In most cases, this doesn’t appear to be a very useful calculation but for allocating amounts over months it can be very useful. Taking the example of the quarterly electricity bill, let’s say the annual amount to include in the forecast is £3,800 and this is entered in cell B3, and in cell C3 you have entered the number of payments to be made in the year.

Note. Row 1 is a helper row which contains the sequential month numbers to simplify calculations.

Formula. In cell D3, you need to type the formula:

=IF(MOD(D$1,12/$C3)=0,$B3/$C3,0)

The IF function uses the MOD function as its logical test to determine whether to make an allocation in the month.

Within the MOD function, 12 is divided by the number of allocations from column C. This represents the number of months between each allocation. Four allocations will result in an allocation every three months: 12/4 = 3.

The MOD function divides the month number in row one by the number of months for the allocation and returns the remainder. When the remainder equals zero (the number of months divides evenly into the month number in row one) then the allocation will be made. In all other months, zero will be returned. In our case of four allocations, the allocation will be made in every third month.

Allocation in advance

The formula above works in arrears with the allocation made in the last month. You may want the ability to choose whether to allocate in arrears or advance on a row-by-row basis. In that case, you need to add an extra column to the worksheet and amend the formula. The extra column (D in our example spreadsheet) allows you to allocate whether the expense should be at the start or end of the period. Once you’ve done that, you need to amend the formula to: =IF(MOD(E$1,12/$C3)-IF($D3=”Start”,1,0)=0,$B3/$C3,0).

If column D contains the word “Start” then the formula deducts one from the result of the MOD function which puts the allocation in the first month rather than the last month.

For an example spreadsheet showing the MOD function, visit http://tipsandadvice-financialcontroller.co.uk/download (FC 12.10.11).