Variance Analysis in Excel: What ERPS Leave to You
Close the month and the variance column fills itself in. Actual minus budget, actual minus last year, a percentage beside each. Every ERP does that subtraction. What none of them does is the part the CFO reads, the sentence that says revenue came in eight percent under last year because two enterprise deals slipped into next quarter, and freight ran twelve percent over plan on a fuel surcharge nobody forecast. That sentence is the analysis, and it gets written in Excel on top of whatever ERP produced the numbers, because the explanation lives in the analyst’s read of the business and the system has no field for it. Variance analysis in Excel is the recurring discipline this piece is about: comparing live actuals against budget, prior period, and forecast, isolating what moved, and explaining why. It runs at two altitudes, the summarized financial statement and the granular detail underneath it, and neither stays current on its own.
What variance analysis asks of you
A variance is a subtraction. Variance analysis in Excel is the work that follows it. The number is a comparison, and budget is only one of the things you compare against. This period against the same period last year, this quarter against the prior one, this month against the trailing average, actuals against the latest forecast: each is a variance, and each answers a different question. Budget tells you whether you’re on plan. Year over year and quarter over quarter tell you which way the business is moving and how fast. Forecast against actual tells you whether your own recent estimate held. The months worth studying are the ones where those answers disagree, where you’re under budget but down on last year, or on plan but slowing quarter to quarter. Then comes the judgment, separating the variances worth a paragraph from the rounding noise, tracing the material ones to the transactions behind them, and writing the explanation the people above you read instead of the spreadsheet.
The same discipline runs below the summarized line. The P&L tells you freight is over plan; the work that explains it tracks the AP bill lines by carrier and lane across six months until the month it started shows up. The same goes for expense reports that run over policy, labor hours charged to the wrong projects, vendor costs rising line by line. These are variances too, measured across a granular data set over time rather than against a single budget figure, and they’re usually where the cause sits.
What each ERP gives you natively
Most ERPs will compute and show the variance, and each draws the line in about the same place. Business Central puts actual against budget on screen through the G/L Balance/Budget view, and for a formatted report you build column definitions in Financial Reports: one column for actual, one for budget, one for the variance, one for the percentage. Acumatica goes further out of the box, with financial statements built in its Analytical Report Manager that place actual, budget, and variance columns side by side at the account and subaccount level and compare across periods and against last year, with drill-down to the period detail. Sage Intacct’s Financial Report Writer adds actual, budget, and difference columns with a variance percentage and visual indicators, and Sage Copilot can now draft a variance explanation inside the close. Different systems, real capability, one shared boundary. Each produces the variance numbers for the reporting structure it owns, in the layout it allows, one entity at a time. The pack that compares three baselines, consolidates several entities, and carries the controller’s narrative in the margin is not what any of them is built to assemble.
The variances that hide below the P&L
A summarized variance points; it rarely explains. When labor cost runs over, the answer lives in the timesheet lines: which projects absorbed the hours, which weeks they spiked, whether a contractor rate changed. When G&A runs over, it’s in the expense reports, a category at a time. When margin falls, it’s in the AP bill line detail, vendor by vendor and item by item. Each ERP holds all of it, and each makes you pull it one inquiry at a time, for one period, with no easy way to lay six months of line-level detail side by side and compare them. That comparison is what catches a rising cost before it reaches the P&L, and it’s the cut the native subledger reports aren’t shaped to produce.
Why variance analysis in Excel keeps going stale
Finance does the analysis in Excel for a plain reason. It’s the only surface that holds actual, budget, prior year, and forecast side by side in a layout you control, with a column left for the sentence that explains the swing. The grid is easy to build; keeping it true to the ledger is the hard part. Numbers pasted from an export are correct the moment they land and wrong as soon as someone posts a late invoice or reclassifies a cost after you pulled the file. The analysis you finished Thursday no longer ties to the ERP on Friday, and nobody notices until the next export reopens the gap. The valuable part gets the least time, because the plumbing ate the morning.
Variance analysis in Excel that refreshes
Velixo connects Excel directly to Acumatica, Business Central, and Sage Intacct through each system’s API, under your own ERP sign-in and the permissions that come with it. The figures that used to arrive by export arrive as live cells: actuals, budget entries, prior-period balances, posted transactions down to the line. The variance and the percentage stay ordinary Excel formulas in a layout you format once and reuse every month, and each baseline is just another live pull, so budget, last year, and the current forecast line up in one view. The same connection reaches past the GL into subledger and operational detail, so AP bill lines, expense report lines, and timesheet entries arrive live too, six months of them in one grid where the trend is readable. Multi-entity and multi-instance consolidation runs through single functions, so a group pack pulls every subsidiary into one workbook rather than one export per company. When a variance looks wrong or interesting, right-click and choose Drilldown, and Velixo opens the entries behind the number with a link into the ERP screen they came from. The analysis starts on refresh instead of after the re-paste.
Asking the variance a question
Once the data’s live, the explanation can start in the workbook too. Select a variance, right-click, and choose Summarize, and Velixo Intelligence reads the range and returns an analyst-level account of what moved and by how much, then takes follow-up questions in the same panel as you press on a line. Point it at a six-month table of expense or bill lines and it reads the period-over-period change the same way it reads a budget variance, naming the categories and vendors behind the increase. For the commentary that repeats every close, a VX.ASK (formerly VX.ANALYSIS) cell sits next to the variance and writes the plain-language read in place, updating when the numbers change on the next refresh. None of this replaces the controller’s judgment about what’s material. It removes the blank-page tax on the first draft and keeps the wording current with the figures. Velixo Intelligence is in private preview today, and it runs on the same live data as the rest of the workbook.
Variance analysis in Excel on any ERP
The work that defines variance analysis sits above the ERP, in the workbook where finance compares the baselines and writes the story, so the same approach holds whichever ERP a company runs. The payoff customers report is in refresh time, and a variance pack refreshes every close. Rob Wood, CFO at Yardnique and a Velixo customer, took his board management report from six hours to one. For a controller running the variance every month, that’s the difference between a morning of rebuilding and a refresh followed by the analysis itself. Velixo pulls live actuals, budget, prior-period, and subledger data into your variance model on any ERP it connects to, drills to the transactions behind any number, drafts the commentary with Intelligence, and writes approved revisions back, all under your ERP sign-in. See how Velixo works with your ERP.