Flux Analysis in QuickBooks Online: Explain the Variances That Matter

A flux analysis asks one question of every group of accounts: is this month what the recent months said it should be? Here is how auditors and controllers do it, and how to do it on QuickBooks Online data without a spreadsheet rebuild each month.

Comparing this month to last month is the usual first pass at a review, and it is a weak one. Every line moves a little. A freight bill goes up because sales went up, a subscription renews, and a salary line is flat. If every change is a question, the useful ones get lost, and if you only look at the biggest dollar changes you miss a small line that has doubled.

A flux analysis (sometimes called an analytical review) fixes this by comparing the actual to an expectation. You build the expectation from the recent past and from whatever should be driving the number, then investigate only what is far from it.

Step 1: Group accounts the way the business thinks about them

Reviewing 80 GL accounts one at a time is slow, and one account can swing because of a reclass that nets out in the group. So group accounts that behave alike: all freight together, all software together, all salary-related lines together. These are sometimes called natural classes. If you already have a budget, your budget lines are a good start, since someone has already decided what belongs together.

Step 2: Decide whether each group is fixed or variable

• Fixed. Rent, insurance, software subscriptions. The expectation is the average of the last several months.
• Variable. Freight, packaging, sales commissions, product cost. The expectation is a rate times a driver: the share of revenue this group has usually been, applied to this month's revenue.

Revenue is the simplest driver. A better one is whatever the cost really follows: units sold for product cost and freight, headcount for payroll and benefits, a single revenue line instead of all revenue. The test is practical. A good driver makes the history steadier. If the group's ratio to the driver swings more than the plain average does, the driver is not helping and the average is the better expectation.

Step 3: Set a threshold with two tests

A percent alone flags a $200 line that went to $400. A dollar amount alone ignores a small line that has doubled in a business where small lines add up. Use both: investigate a group only when it is off by at least, say, 10% and at least $1,000. Set the numbers to your materiality, and review them when the business changes size.

Step 4: Explain the flagged groups, and write it down

For each flagged group, drill to the accounts and then the transactions until you can say what happened: a one-time courier bill, a price increase from a supplier, a pull-forward of shipments, a coding error that needs a correction. Record the explanation next to the number. When someone asks in three months, or an auditor asks in twelve, the answer is already there. If a later change alters the difference, the explanation should reopen rather than quietly standing for a number it no longer describes.

Doing this on QuickBooks Online data

QuickBooks Online has no flux report. You can build it in a spreadsheet by exporting a profit and loss by month, grouping accounts in a second tab, adding the drivers by hand and rewriting the formulas for each close. It works, but it is the kind of spreadsheet that gets skipped in a busy month.

DrillFi's flux analysis does the same steps on your own budget lines. Each line is a group to start with, and you can combine lines into your own groups. By default a group is expected to be the average of its recent months. You can instead expect last month's amount after a step change, the same month last year for a seasonal group, the budget, or a rate times a driver: net revenue, units sold, another line, or a driver of your own such as headcount, with a flag when a month of that data is missing. DrillFi suggests one of these only when it explains the history better than the average, and marks it as suggested so you can keep it or change it. Flagged groups show which vendors and GL accounts moved, drill to the transactions, and keep your explanation against the month.