When developing budgets and financial models you may need the same rate/value (say debtor days) for 10 months of the year but need to adjust it for 2 months. It is usually December and January as they tend to be a little different due to the Christmas / New Year period. Here’s a couple of ways to handle exceptions in Excel.

In Australia our financial year starts in July, the examples below use that structure.

The example file can be downloaded via a button at the bottom of the post. There are separate sheets for each example.

Adjust vs Override

There are at least two ways to amend the standard input.

• Adjust a base rate
• Over-ride a rate

Which one you choose will depend on the rate and to some extent how you think about it and measure it.

Both techniques require an extra range.

Standard calculation

The image below has the standard Debtor Days calculation. The only input cell is B2.

The formulas are listed below. Each has been copied across.

Cell C2

=$B2

Cell C5

=DAY(EOMONTH(C1,0))

The EOMONTH function returns the last day of the month and the DAY function extracts that day to return the number of days in the month.

Row 6 has inputted values for this demonstration. It would be linked to total sales in a real model.

Cell C8

=ROUND(C6/C5*C2,0)

The calculations in rows 5, 6 and 8 won’t change in the following examples.

Adjustment example

We need an extra row to handle the adjustment input. Since we want to ability to change any month we need an input cell for each month. The image below has the amended structure.

In this example you enter a percentage value that adjusts the existing value up (positive) or down (negative), rather than inputting a new value.

In my experience changes usually relate to a base rate. A percentage adjustment that adjusts the base rate up or down usually requires less maintenance over time.

The only formula that changes is C2 the amended formula is

=ROUND($B2*(1+C3),0)

I have rounded the result as estimated debtor days are usually expressed in whole days.

Instead of a percentage you may also use an adjustment value that adjusts the base rate up or down. Eg entering 4 would increase the debtor days by 4.

Over-ride example

The image below has the revised row 3.

The formula in cell C2 is now

=IF(C3="",$B2,C3)

This simple IF function looks at row 3. If it is empty it uses the base rate, otherwise it uses the over-ride value.

Pros and Cons of both

Adjustment Pros

1. If the base rate changes usually the adjustment doesn’t need to change as it is percentage based.
2. In the finance area percentages are well understood.

Adjustment Cons

1. If you trying to reach a specific rate then some trial and error may be required to get the percentage right.
2. Outside of finance, percentages aren’t always understood, so it may be confusing to non-finance staff.

Over-Ride Pros

1. You enter the figure you want to use. This makes sense to both finance and non-finance people.

Override Cons

1. If the base rate changes you may also need to change the over-ride figures as well. In our case if we are trying to reduce debtor days from 42 to 38 then the override figures in December and January may now be too high and will also need to be changed.

Download Example File