Tools
Free flux analysis Excel template
Paste two periods of account balances, set your thresholds, and it flags what needs explaining — with a residual that tells you whether your drivers actually account for the movement.
What's in it
| Tab | What it does |
|---|---|
| Instructions | How to use it, what the two thresholds do, and why the residual is a dollar amount rather than a coverage percentage. |
| Parameters | Entity, both period ends, comparison type, and your two thresholds. It doesn't suggest threshold numbers — those belong to your risk assessment, not to a template. |
| Flux | Paste account, name, prior balance and current balance. Movement, percentage and the flag calculate. Three driver slots per account, a commentary field, and the residual. |
How the flag works
An account flags when the movement exceeds both a dollar threshold and a percentage threshold. That combination is what keeps the queue useful: a large account drifting one percent doesn't flag, and a tiny account moving four hundred percent doesn't either.
Both thresholds live on the Parameters tab and apply to every row, so changing one number re-scopes the whole queue.
The residual is the part that matters
For each account you name drivers and amounts. The template sums them and subtracts from the movement. What's left is the residual — the part of the movement your explanation doesn't cover.
It's reported as a dollar amount, deliberately. A coverage percentage scales wrong: 89 percent coverage on a $1.8M account leaves $200,000 unexplained, while 89 percent on a $60,000 account leaves almost nothing. The number worth looking at is the dollars, judged against your own materiality. The reasoning behind that is here.
There's also a direction check. If your drivers sum in the opposite direction from the movement they're supposed to explain, the row says CHECK. That catches a specific error that reads perfectly well in prose — naming a real driver, but pointing the wrong way.
How to use the flux analysis template during a close
To watch the same two-threshold rule run in the browser on a synthetic company first, try the demo. One difference: the demo can’t flag a brand-new account with no prior balance, which this template can, on the dollar test alone. The order that tends to work, once the balances are in:
- Set the thresholds before you look at the results. Deciding what counts as material after seeing which accounts moved invites tuning the threshold until the queue is a comfortable size. Set it from your own risk assessment, then see what it catches.
- Work the flagged list in size order, not account order. The largest unexplained movements are where a reviewer starts, so they're where you want your best work.
- Name drivers before you write prose. Fill the driver slots and amounts first. If the residual won't close, no amount of careful wording fixes it — you're missing a driver and the commentary field is the wrong place to discover that.
- Check the direction column before you consider a row done. A CHECK there means your drivers sum opposite to the movement. That's a real error and it reads perfectly well in prose, which is exactly why it survives review.
- Read the summary last. Flagged accounts still unexplained, and total unexplained residual, are the two numbers that tell you whether the schedule is finished.
Why flux analysis needs two thresholds, not one
A dollar threshold on its own flags every large account that drifted slightly, because a one percent move on a multi-million dollar balance clears any sensible dollar figure. A percentage threshold on its own flags every small account, because a $4,000 account going to $16,000 is a 300 percent move that nobody needs explained.
Requiring both is what keeps the queue to things that are actually worth a sentence. The template applies both to every row, so changing either number on the Parameters tab re-scopes the whole schedule at once.
What those numbers should be isn't something a template can tell you. Materiality gets set during risk assessment and control scoping, against the size and risk profile of the entity — not by whoever is filling in the workbook that month.
What the residual actually catches
The most common failure in flux commentary isn't a wrong explanation. It's an incomplete one: three drivers named, correctly, that together account for perhaps two thirds of the movement, with the rest unaddressed and nobody noticing.
That's hard to catch by reading, because the prose sounds finished. It's trivial to catch by subtraction, which is all the residual column does. If you named $600,000 of drivers against a $742,500 movement, the template says $142,500 — and you get to decide whether that's immaterial or whether you're missing something.
Judging it in dollars rather than as a coverage percentage matters more than it sounds. The full argument is here, but briefly: the same percentage means completely different things at different account sizes, and the threshold you care about is a dollar amount because that's how materiality is set.
Adapting the template
It's a plain formula workbook with no protection and no macros, so changing it is expected rather than discouraged.
Common adjustments: adding driver slots beyond the three provided (copy the column pattern and extend the drivers-total formula), adding a column for the preparer and reviewer per account, adding a prior-period commentary column if you carry explanations forward, or splitting the schedule by statement so income statement and balance sheet accounts sit on separate tabs. They need different data pulls anyway.
The one thing worth preserving if you rebuild it: keep the residual as a calculated dollar figure rather than a percentage, and keep the direction check. Those are the two columns that catch errors a careful reader misses.
What it doesn't do
It flags on size, which is one kind of exception and not the only one. A size threshold can't see an account that should have moved and didn't, a sign flip too small to exceed the dollar threshold, or a new account nobody set up a comparison for.
Those need a different comparison entirely — covered here. The template is the arithmetic layer, not the whole review.
It also doesn't write commentary for you, and doesn't try to. Finding the reason a balance moved is the part that needs a person in the detail.
If you'd rather grade commentary you've already written
There's a free browser tool that runs the same four computed checks against driver lines you paste in — coverage, direction, sourcing and specificity. Nothing leaves your browser and it saves between visits. Useful if the commentary already exists and you want it checked before it goes to a reviewer.
Want the next step up? The CloseOps Flux & Variance System ($79) starts from your trial balance instead: confirm each account's classification and it builds the income statement and balance sheet flux statements, then ranks what to investigate. It flags on your dollar and percent thresholds and holds up to 80 trial balance lines.
Related: flux analysis vs variance analysis, what flux analysis is, four of the five tests your commentary fails are arithmetic, and month-end flux, start to finish.
Found something wrong or missing? Tell me here — anonymous, thirty seconds.