Copperfield Kitchens' loan needed rolling twelve-month revenue of at least £1,500,000 at every quarter end. In 2025 the sheet reported four passes. Every one of them was a breach, and the bank found out first.
The sheet added up the last twelve rows of a column of monthly totals. That is twelve months until a month goes missing, and in February 2025 one did.
| Covenant test, 2025 | Figure |
|---|---|
| Floor, rolling 12 months | £1,500,000 |
| December, as the sheet read it | £1,653,000 |
| December, real twelve months | £1,470,600 |
| Counted twice: December 2024 | £182,400 |
| Tests passed on the sheet | 4 of 4 |
| Tests passed in fact | 0 of 4 |
Scope.
SUMIFS,COUNTIFS,AVERAGEIFS,EOMONTHandEDATEwork the same in every current version of Excel on Windows, Mac and the web, and in Google Sheets.SEQUENCEin section 6 needs Excel 2021 or Microsoft 365; a fill-down version is given beside it.Throughout, A2:A24 holds month-end dates and B2:B24 that month's revenue. A rolling twelve-month total for a month means that month and the eleven calendar months before it.
01The Covenant Passed on a Number With Two Decembers
The kitchen shut for a refit in February 2025 and raised no invoices that month. The accounts export lists only months with postings, so the monthly table went straight from January 2025 to March 2025.
Nobody noticed, because the rolling column kept producing numbers. From March onwards each one was twelve rows deep and thirteen months wide.
In October 2026 the bank's annual review rebuilt the tests from the monthly management accounts. Its December 2025 figure was £1,470,600. The sheet's was £1,653,000, and the difference was exactly December 2024's revenue.
Twenty-Three Monthly Totals, One Missing Month and Two Rolling Totals
Copperfield Kitchens' monthly revenue from the accounts export, in A1:F24. Column A holds month-end dates shown as mmm yyyy; there is no February 2025, because the kitchen was shut for a refit and the export lists only months with postings. Column C is the sheet's `=SUM(B2:B13)` filled down, column D the calendar months those twelve rows span, column E the twelve-month window by date with `SUMIFS` and `EOMONTH`, and column F the months that window actually found.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
02Twelve Rows Are Not Twelve Months
The sheet's formula in C13 was =SUM(B2:B13), filled down to C24. Each copy adds the twelve rows ending on its own row. It counts rows, and it trusts every row to be a month.
For December 2024 and January 2025 that holds, and the totals are right. From March 2025 the twelve rows start a month earlier than they should, because one of the months they are meant to cover has no row.
| Test | 12-row total | Months spanned | Real 12 months | Verdict |
|---|---|---|---|---|
| Mar 2025 | 1,534,000 | 13 | 1,421,400 | Breach |
| Jun 2025 | 1,569,700 | 13 | 1,437,900 | Breach |
| Sep 2025 | 1,579,600 | 13 | 1,452,400 | Breach |
| Dec 2025 | 1,653,000 | 13 | 1,470,600 | Breach |
Every wrong total is too high by the revenue of the month thirteen back. In December that is the biggest month of the year, which is why the error was largest exactly where the annual figure is read.
Scenario: Enter the monthly table with no February 2025 row, put =SUM(B2:B13) in C13 and fill it to C24. Then check C24 against =SUM(B14:B24), the eleven rows that really fall in 2025.
03Define the Window by Date, Not by Position
A window defined by dates cannot be stretched by a missing row. Ask for every row whose month falls after the month-end a year back, and on or before this month-end.
=SUMIFS($B$2:$B$24,$A$2:$A$24,">"&EOMONTH(A13,-12),$A$2:$A$24,"<="&A13)
EOMONTH(A13,-12) returns the last day of the month twelve months before A13. For December 2025 that is 31 December 2024, and the > leaves it out. The window is January to December 2025, whatever rows exist inside it.
Filled down column E, it returns £1,470,600 for December 2025. On a sheet with whole columns free of other data, the short form reads the same way: =SUMIFS(B:B,A:A,">"&EOMONTH(A24,-12),A:A,"<="&A24).
Scenario: Put the SUMIFS formula above in E13 and fill it to E24. Confirm E13 and E14 match column C, then confirm E24 reads 1,470,600 where C24 reads 1,653,000.
04The Off-by-One That Makes It Thirteen Again
The date window has one fragile character. With month-end dates, >= instead of > adds a thirteenth month, because the month-end a year back is itself a row in the table.
| Window for | With > | With >= | Months with >= |
|---|---|---|---|
| Jan 2025 | 1,521,400 | 1,619,800 | 13 |
| Mar 2025 | 1,421,400 | 1,534,000 | 12 of 13 |
| Dec 2025 | 1,470,600 | 1,653,000 | 12 of 13 |
From March 2025 the >= version reproduces the 12-row column to the pound. Two wrong methods agreeing with each other is not a check. In January 2025 it is wrong even where the row count was right.
If your table holds first-of-month dates instead, the boundaries move: ">="&EDATE(A13,-11) and "<="&A13. Pick one date convention for the column and write the window for that convention.
Scenario: Copy column E to column G and change the first ">" to ">=". Compare G14 with E14 for January 2025 and find the 98,400 of January 2024 that the extra character pulled in.
05Count the Months in Every Window
A total cannot tell you how many months it contains. A count can. Put the same two criteria into COUNTIFS beside every rolling total.
=COUNTIFS($A$2:$A$24,">"&EOMONTH(A13,-12),$A$2:$A$24,"<="&A13)
It returns 12 for December 2024 and January 2025, then 11 from March 2025 onwards. That 11 is the missing February, visible on every row the 12-row column got wrong.
The 12-row column needs a check of its own: the calendar months its rows span. =(YEAR(A13)-YEAR(A2))*12+MONTH(A13)-MONTH(A2)+1, filled down, returns 12 and then 13. Either check would have stopped the March certificate.
Scenario: Add the COUNTIFS column beside your rolling totals, then add =COUNTIF(F13:F24,"<>12") above it. Copperfield's table returns 10, one for every month-end since the gap.
06A Closed Month Is a Zero, Not a Gap
The covenant is right to count February 2025 as a month with no revenue. The table was wrong to leave it out. Build the months first, then fill in what each one earned.
=EOMONTH(DATE(2024,1,1),SEQUENCE(24,1,0))
That spills 24 month-ends from January 2024 to December 2025. Without SEQUENCE, type 31/01/2024 in A2 and fill =EOMONTH(A2,1) down from A3. Then fetch each month's revenue from the export with SUMIFS, which returns 0 for a month with no postings.
With a row for every month, the 12-row sum is right again and the date window still agrees with it. Keep the date window anyway. The next gap may come from a filter, a deleted row or a second export.
Scenario: Build the month spine in a new column A, then enter =SUMIFS(Export!B:B,Export!A:A,A2) in B2 and fill down. Check that the February 2025 row reads 0 rather than being absent.
07Leave the First Eleven Months Blank
Filled to the top of the table, the date window returns a total for January 2024 of £98,400. It is one month of revenue labelled as twelve. A partial window should be blank, not small.
=IF(EOMONTH(A2,-12)<EOMONTH(MIN($A$2:$A$24),-1),"",
SUMIFS($B$2:$B$24,$A$2:$A$24,">"&EOMONTH(A2,-12),$A$2:$A$24,"<="&A2))
The test asks whether the window starts before the data does. For November 2024 it does, and the cell stays empty. From December 2024, the first month with a full year behind it, the total appears.
This is not the same as a missing month in the middle. The start of the data is known and expected; a gap inside it is a fault, and the month count in section 5 is what reports it.
08Year on Year Means Two Windows of Twelve
The board pack said rolling revenue was up 8.9% on the year. It compared C24, thirteen months, with C13, twelve. The real movement was down 3.2%.
| December window | Sheet | Real |
|---|---|---|
| 2024 | 1,518,500 | 1,518,500 |
| 2025 | 1,653,000 | 1,470,600 |
| Change | +134,500 | −47,900 |
| Change, % | +8.9% | −3.2% |
The comparison is =E24/E13-1, and it is only fair when both cells hold twelve months. Show the two month counts beside it, so a reader can see they match before trusting the percentage.
Scenario: Enter =E24/E13-1 and =C24/C13-1 side by side, formatted as percentages. Then put =F24=F13 beneath them and read why one of the two answers is fair.
09A Rolling Average Divides by Twelve
A rolling monthly average is the rolling total divided by twelve. AVERAGEIFS over the same window divides by the rows it finds instead, which after the gap is eleven.
| December 2025 | Formula | Result |
|---|---|---|
| Rolling total ÷ 12 | =E24/12 | 122,550.00 |
AVERAGEIFS over the window | rows found: 11 | 133,690.91 |
The covenant treats February as a month that earned nothing, so £122,550 is the honest average. AVERAGEIFS silently describes a business that was open every month and earned over £11,000 more in each.
Neither is wrong as arithmetic. Decide whether a closed month counts, write it down beside the figure, and use the formula that matches.
10Six Traps
- Twelve rows for twelve months. Any missing month stretches the window, and nothing in the total shows it.
>=on month-end dates. It includes the month-end a year back and makes the window thirteen months long.- Checking one method against another. The 12-row sum and the
>=window agree from March 2025, and both are wrong. - Partial windows at the start. A January "rolling total" of one month looks like a collapse, not like missing history.
- Year on year across uneven windows. Thirteen months against twelve is growth that never happened.
AVERAGEIFSfor a rolling average. It divides by the rows it finds, so a closed month disappears from the denominator.
11Four Exercises
- Reproduce it. Enter the 23 monthly rows with no February 2025, fill
=SUM(B2:B13)from C13 to C24 and find the first wrong row. - Fix the window. Replace column C with the
SUMIFSandEOMONTHwindow from section 3, and check that December 2025 reads 1,470,600. - Break it on purpose. Change
>to>=and find the 98,400 it adds to January 2025. - Prove it. Add the
COUNTIFSmonth count, then insert a February 2025 row with 0 revenue and watch every count return to 12.
What to Take Away
Copperfield's sheet was right for two months and wrong for ten, and the wrong ones were the covenant tests. A spot check of December 2024 would have passed, which is how four certificates went to the bank.
A month is a date, not a position. Define the window with dates, count the months inside it, and keep a row for every month, including the ones that earned nothing.
The bank now has a breach to discuss for every quarter of 2025. The sheet gave Copperfield four quarters of notice and showed none of it.