Practice
Flux analysis practice case
A made-up company's September close, in one Excel file. You do the flux review: decide what needs explaining, explain it, and find what's booked wrong. Then check your work against the answer key at the bottom of this page.
Reading about flux analysis only gets you so far. This is the other half: a full month of a small distributor's books, with the kinds of movements a real close throws at you, and an answer key that shows what a reviewer would accept and what they'd send back. Everything in it is invented. Plan on an afternoon.
What's in the file
- README: the task, the thresholds and the sign convention.
- TB: the trial balance for August and September, 53 accounts.
- GL_Detail: the transactions behind the accounts that move.
- Product_Sales: units and revenue by product, for splitting the revenue movement.
- Close_Notes: what you'd find out by asking around during a close (a price letter, the payroll calendar, a new customer, an insurance renewal). Each note has an ID, so your explanations can cite it.
How to work it
- Find what needs explaining. Paste the TB into the free flux template and set the two thresholds from the README. It flags every account that moved past both.
- Split revenue. Paste Product_Sales into the price volume mix calculator. It breaks the revenue movement into volume, mix, price, new and lost products.
- Read the detail. For any flagged account, paste the GL lines into the account breakdown tool to see August against September by customer or vendor. This is where most of the answers are.
- Write it up. For each flagged account, write what drove the movement, with an amount and a source (a close note or GL lines). Then paste your lines into the commentary test to check they add up and point the right way.
What you're looking for: a set of accounts that moved enough to need an explanation, at least one thing that's booked wrong, and one problem the thresholds won't show you at all.
Show the answer key (write your own first)
The 15 accounts to explain
Thresholds: more than $10,000 and more than 10%. The CloseOps system, which flags on either threshold, gives the same list for this data.
| Account | Name | Movement | What drove it |
|---|---|---|---|
| 1000 | Cash - operating | down $109,973.55 | Receipts fell short of billings (see AR), plus the insurance renewal paid in advance and the truck down payment |
| 1100 | Accounts receivable | up $145,496 | Two month-end billings: the new customer's opening order and an annual service contract |
| 1300 | Prepaid insurance | up $77,750 | Renewal premium paid in September for a policy that starts in October |
| 1510 | Vehicles | up $62,400 | A new box truck |
| 2100 | Accrued expenses | up $22,247.33 | Error: August's freight accrual was never reversed |
| 2110 | Accrued payroll and payroll taxes | up $15,921.60 | Three unpaid workdays at month-end instead of one, because of where payday fell |
| 2300 | Deferred revenue | up $41,500 | An annual service contract billed in advance |
| 2500 | Note payable - vehicle | up $52,000 | New account: the truck's financing |
| 3950 | Current-year earnings through prior month | up $82,123.62 | August's net income closed in. Mechanical, no business driver |
| 4000 | Product sales | up $111,796 | More units (mostly safety gloves), price increases and a new product, partly offset by mix and a discontinued line |
| 5000 | Cost of goods sold | up $60,260 | Follows units by product, at unchanged standard costs |
| 5100 | Freight-in | up $22,100 | Error: August freight expensed twice (the other side of the 2100 error) |
| 6000 | Salaries and wages | up $21,800 | Two new hires and one more paid day |
| 6400 | Office supplies | up $22,580 | Error: a trade-show booth coded to office supplies |
| 6500 | Professional fees - legal | up $31,800 | One-time legal fees on a dispute that settled in September |
The two errors
1. A freight accrual that was never reversed. August's freight was accrued at month-end, then August's carrier invoices were entered in September, but the August accrual wasn't reversed on September 1. August's freight got expensed twice. The tell: freight-in roughly doubled while shipments rose less than a third, and the GL shows a reversal line in August with no equivalent in September.
Dr 2100 Accrued expenses $21,400 / Cr 5100 Freight-in $21,400
2. A trade-show booth in the wrong account. A $22,500 expo booth was coded to office supplies. Office supplies jumped more than tenfold on a single line from an expo organizer, while marketing barely moved in an expo month.
Dr 6410 Marketing and trade shows $22,500 / Cr 6400 Office supplies $22,500
After both corrections, accrued expenses, freight-in and office supplies drop off the list, and marketing joins it: the booth, now in the right place, needs its own explanation.
The one the thresholds miss
September is a quarter-end, and the allowance for doubtful accounts is reviewed every quarter. It didn't move, and bad debt expense is zero, while receivables over 90 days nearly doubled. Nothing flags because nothing moved, and that's the problem. The right response is to ask for the quarter-end allowance review, not to estimate a number yourself.
Two worked examples
The answer workbook has a model explanation for every flagged account, a typical first attempt and the reason a reviewer sends it back. Two of them:
Accrued expenses
Why it gets sent back: The GL lines are real, but the balance is wrong. Compare the ending balance with what should be there (one month of freight, not two). Listing the entries hides the reversal that never happened.
Product sales
Why it gets sent back: Names categories without amounts. Volume and mix offset each other by about $99,066 here, and without the split a reviewer can't see that the price increase added only $16,196.
If you work this in Excel: the free flux template flags what needs explaining and shows the residual your drivers don't cover. 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. The case's TB tab includes the debit and credit columns it uses.
Related: a worked flux analysis example, month-end flux analysis from start to finish, reversing entries, and accounts that should have moved and didn't.
Found something wrong or missing? Tell me here — anonymous, thirty seconds.