The departments do not have equal budgets
The synthetic October report has three departments. Sales is $14,500 over a $179,000 budget; Engineering is $24,000 over $350,000; G&A is $3,000 over $116,000. Their variance percentages are approximately 8.101%, 6.857% and 2.586%.
The simple mean of those percentages is 5.848%. It gives the smallest and largest budget the same influence. The total row instead divides the $41,500 combined variance by the $645,000 combined budget, giving 6.434%. These are arithmetic results from the published fixture, not a claim about any real company.
An agent can produce the wrong total with syntactically valid formulas and no Excel error values. That is why an error scan is only one control. The acceptance test must also express the meaning of the measure: in this report, aggregate variance divided by aggregate budget.
| Department | Budget | Actual | Variance percentage |
|---|---|---|---|
| Sales | $179,000 | $193,500 | 8.101% |
| Engineering | $350,000 | $374,000 | 6.857% |
| G&A | $116,000 | $119,000 | 2.586% |
| Total | $645,000 | $686,500 | 6.434% |
Zero budget is not zero variance
We then set the four October Sales budget rows to zero and recalculated in Excel. Sales actual remained $193,500 and its dollar variance became $193,500. The percentage formula returned #N/A intentionally: =IF(B6=0,NA(),D6/ABS(B6)).
The total still had a valid denominator. Budget fell to $466,000, actual stayed $686,500, and variance became $220,500. Dividing those totals produced approximately 47.318%. An undefined department percentage did not make the total denominator zero.
This fixture uses #N/A to represent a percentage it cannot calculate. That is a documented business rule, not an unexpected spreadsheet defect. Your output may instead require a label such as “no budget.” Specify the policy before generating charts or narrative; replacing an undefined percentage with 0% would assert a different result.
Validate the measure, including its exception
The useful controls here are the source budget sum, actual sum, dollar variance identity and percentage rule. Apply them to each department and the total. Keep the zero-denominator case separate so a broad “zero formula errors” rule does not silently erase the intended exception.
The formula uses an absolute budget in the denominator. Our tested budgets are nonnegative. It does not establish how a business should report negative budgets, credits or mixed-sign allocations. Those require an agreed definition and their own test cases.
Our first reporting script also failed for a controller reason: its PowerShell aggregation did not read properties from the hashtable records as intended. We replaced it with explicit arithmetic across the three observed values and reran. The published result retains that correction so the native workbook result and the controller’s summary are distinguishable.
Inspect the altered workbook before reusing the formula
Download the zero-budget workbook, native result and script. The script expects expense-original.xlsx from the synthetic expense fixture, copies it and changes only the test budget rows.
The output is an intentional exception case, not the original accepted October report. Its #N/A cell is part of the expected evidence. Keep it in your test set alongside a normal positive-budget case.
Run through Panes desktop execution, then use the workbook verification guide for source-to-report reconciliation. If the result enters a deck, also check PowerPoint chart data consistency; correct workbook arithmetic does not by itself validate the chart’s embedded data.