To make a budget vs. actual variance column add up to the change in profit, enter revenues and expenses as positive amounts and set the formula by line type: Actual minus Budget for revenue, Budget minus Actual for expenses. A positive variance then always helps profit, and a negative one always hurts it. Check the inputs, add up only the detail rows, show margin changes in percentage points, and confirm the total matches the change in net operating income.
The common shortcut gets this wrong. In the example below, using =Actual-Budget on the six detail rows and summing those rows gives +$7,300. Net operating income actually fell $1,300. Revenue rose $3,000, and expenses rose $4,300, and the shortcut adds both increases as if they were good news.
The Example Worksheet
This is a monthly P&L on a sheet named BvA, with headers in row 1, six detail lines in rows 2 to 7, and subtotals, margin, and the bottom line in rows 8 to 11, made up for educational purposes. Type the labels in columns A and B and the amounts in C2:D7. The result cells are formulas; unused cells stay blank.
Open in Google Sheets. Scroll horizontally to see all columns.
Step 1: Choose and State Your Sign Convention
A raw change column uses Actual minus Budget on every row and shows whether each account went up or down. COGS shows +$4,200 because spending rose. That’s a valid display if you label favorable and unfavorable separately and never add the column across revenue and expense rows.
A profit-impact column, used here, signs each row by its effect on net operating income. COGS ran $4,200 above budget, a -$4,200 profit impact. Payroll came in $1,500 under budget, a +$1,500 profit impact. The six detail variances in E2:E7 then add up to -$1,300, which is $18,700 actual minus $20,000 budgeted net operating income.
Put a note beside the table saying “Positive variance helps profit; negative variance hurts profit.” Keep the row positions shown here.
Step 2: Build the Detail Rows (E2:G7)
Add a Data Validation dropdown to B2:B7 allowing only Revenue and Expense. In Excel for the web, enable Show Alert on the Error Alert tab and select Blocking so invalid typed entries are rejected. In Google Sheets, use Reject input for invalid data. Keep the formula’s line-type check as a second guard. Enter this in E2 and copy it down to E7, no further:
=IF(OR(TRIM(A2)="",ISBLANK(C2),ISBLANK(D2),NOT(ISNUMBER(C2)),NOT(ISNUMBER(D2)),C2<0,D2<0),"CHECK INPUTS",IF(B2="Revenue",D2-C2,IF(B2="Expense",C2-D2,"CHECK LINE TYPE")))
The simpler =IF(B2="Revenue",D2-C2,C2-D2) fails quietly. A typo like “Rev” lands in the expense branch with the wrong sign, and subtraction treats a blank Budget or Actual as zero, turning a missing number into a full-size variance. This version returns CHECK INPUTS for a missing account name or a blank, text, or negative amount, and CHECK LINE TYPE for any other label. Negative amounts are rejected on purpose: if a line can legitimately go negative, such as a net credit, decide how to present it before loosening the guard.
In F2, copied to F7, and formatted as a percentage:
=IF(NOT(ISNUMBER(E2)),E2,IF(C2<=0,"N/A (Check Base)",E2/C2))
A typed budget of 0 is valid, so E shows the dollar variance while F shows N/A (Check Base). A blank budget shows CHECK INPUTS. The sheet keeps those two problems apart.
In G2, copied to G7:
=IF(NOT(ISNUMBER(E2)),E2,IF(E2>0,"Favorable",IF(E2<0,"Unfavorable","On Budget")))
Any warning in E passes straight through to F and G.
Step 3: Add Gated Subtotals, Margin, and the Bottom Line (Rows 8 to 11)
Don’t drag the detail formula into these rows. Each needs its own formula that checks inputs first, because SUM skips text in referenced cells. If E3 and E5 both showed CHECK LINE TYPE, a plain =SUM(E2:E7) would leave out -$1,500 and +$1,500 and still return -$1,300, a correct-looking total with two lines missing. The gates use COUNT, which counts only numbers, plus the expected labels:
- C8, copied to D8:
=IF(OR(COUNT($E2:$E3)<2,$B2<>"Revenue",$B3<>"Revenue"),"CHECK DETAIL ROWS",SUM(C2:C3)) - E8, copied to E9:
=IF(OR(NOT(ISNUMBER(C8)),NOT(ISNUMBER(D8))),"CHECK DETAIL ROWS",D8-C8) - C9, copied to D9:
=IF(OR(NOT(ISNUMBER(C8)),NOT(ISNUMBER($E4)),$B4<>"Expense"),"CHECK DETAIL ROWS",C8-C4) - C10, copied to D10:
=IF(OR(NOT(ISNUMBER(C8)),NOT(ISNUMBER(C9))),"CHECK DETAIL ROWS",IF(C8<=0,"N/A",C9/C8)) - E10:
=IF(OR(C10="CHECK DETAIL ROWS",D10="CHECK DETAIL ROWS"),"CHECK DETAIL ROWS",IF(OR(NOT(ISNUMBER(C10)),NOT(ISNUMBER(D10))),"N/A",(D10-C10)*100)) - F10:
=IF(OR(C10="CHECK DETAIL ROWS",D10="CHECK DETAIL ROWS"),"CHECK DETAIL ROWS",IF(OR(NOT(ISNUMBER(C10)),NOT(ISNUMBER(D10)),C10<=0),"N/A",(D10-C10)/C10)) - C11, copied to D11:
=IF(OR(COUNT($E2:$E7)<6,NOT(ISNUMBER(C8)),$B4<>"Expense",$B5<>"Expense",$B6<>"Expense",$B7<>"Expense"),"CHECK DETAIL ROWS",C8-SUM(C4:C7)) - E11:
=IF(OR(COUNT(E2:E7)<6,NOT(ISNUMBER(C11)),NOT(ISNUMBER(D11))),"CHECK DETAIL ROWS",SUM(E2:E7)) - H11:
=IF(OR(COUNT(E2:E7)<6,NOT(ISNUMBER(C11)),NOT(ISNUMBER(D11)),NOT(ISNUMBER(E11))),"CHECK DETAIL ROWS",IF(ROUND(E11-(D11-C11),2)=0,"RECONCILED","MISMATCH"))
Then copy F2 into F8, F9, and F11, and G2 into G8:G11. The relative references adjust themselves.
Format C10:D10 and F8:F11 as percentages. E10 holds the margin change in percentage points (-2.195…), so give that cell alone the custom format +#,##0.00" pp";-#,##0.00" pp";0.00" pp". Margin fell from 50.00% to 47.80%: 2.20 points, or a 4.39% relative decline. Keep values unrounded and let formatting round the display.
Each gate checks what its own row depends on. Row 8 needs both revenue lines, and rows 9 and 10 also need COGS. A bad payroll or marketing row leaves rows 8 to 10 numeric while row 11 and H11 show CHECK DETAIL ROWS. A bad service revenue row blocks every row from 8 down, so in the two-bad-rows case, H11 reports CHECK DETAIL ROWS instead of a false RECONCILED.
COUNT(E2:E7)=6 only confirms that six formula results are numbers. It doesn’t prove your six lines are the whole P&L, that the amounts match your books, or that nobody typed over a formula. Tie columns C and D back to your accounting report separately.
Step 4: Split Revenue Into Order Count and Average Order Value
Commercial sales beat budget by $4,500, but that hides two movements. Put these headers in A14:J14: Line_ID, Account_Name, Budget_Orders, Budget_AOV, Actual_Orders, Actual_AOV, Order_Count_Variance, AOV_Variance, Reconciled_Total_Variance, Driver_Check. In row 15, enter L-01 in A15, Commercial Sales Revenue in B15, 500 budgeted orders in C15, and 550 actual orders in E15. Then:
| Cell | Formula | Result |
|---|---|---|
| D15 | =IF(OR(NOT(ISNUMBER(C2)),C2<=0,NOT(ISNUMBER(C15)),C15<=0),"CHECK ORDERS/REV",C2/C15) |
$200.00 |
| F15 | =IF(OR(NOT(ISNUMBER(D2)),D2<=0,NOT(ISNUMBER(E15)),E15<=0),"CHECK ORDERS/REV",D2/E15) |
$190.00 |
| G15 | =IF(OR(NOT(ISNUMBER(D15)),NOT(ISNUMBER(F15))),"CHECK ORDERS/REV",(E15-C15)*D15) |
+$10,000.00 |
| H15 | =IF(OR(NOT(ISNUMBER(D15)),NOT(ISNUMBER(F15))),"CHECK ORDERS/REV",(F15-D15)*E15) |
-$5,500.00 |
| I15 | =IF(OR(NOT(ISNUMBER(G15)),NOT(ISNUMBER(H15))),"CHECK ORDERS/REV",G15+H15) |
+$4,500.00 |
| J15 | =IF(OR(NOT(ISNUMBER(I15)),NOT(ISNUMBER(E2))),"CHECK ORDERS/REV",IF(ROUND(I15-E2,2)=0,"RECONCILED","MISMATCH")) |
RECONCILED |
Fifty extra orders at the budgeted $200 add $10,000. The $10 drop in average order value across all 550 actual orders subtracts $5,500. Together they equal E2. The guards stop the block if an order count or revenue amount is zero, blank, or not a number.
This split is arithmetic, not a diagnosis. A lower AOV can come from discounting, but also from customers buying fewer items per order or shifting toward cheaper products. More orders don’t explain the COGS overrun either, because an order isn’t a physical unit. Use the split to decide what to investigate.
Step 5: Write the Variance Commentary
Three short statements cover it:
- The bridge. Total revenue was $3,000 above budget (+2.50%): commercial sales +$4,500, partly offset by service revenue -$1,500. COGS ran $4,200 above budget, a -$4,200 profit impact. Adding the signed impacts, +$3,000 + -$4,200 = -$1,200, so gross profit was $58,800, $1,200 below budget (-2%), and gross margin was 47.80%, down 2.20 points. Payroll saved $1,500, marketing cost an extra $1,600, and rent was on budget, leaving net operating income $1,300 below budget at $18,700.
- The split. Within commercial sales, order count contributed +$10,000 (550 vs. 500 orders) and lower AOV contributed -$5,500 ($190 vs. $200).
- The questions and owners. Sales and purchasing check by Friday whether the AOV drop and the COGS overrun reflect discounting, smaller baskets, product mix, or supplier costs. The service lead reviews the $1,500 service shortfall.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Six detail variances total +$7,300, not -$1,300 | Raw Actual − Budget summed across revenue and expenses | Use the profit-impact E2 formula and sum only E2:E7 |
| The variance total includes subtotals, the bottom line, or gross margin | Dollar impacts are counted twice, or dollars are mixed with percentage points | Sum only E2:E7; keep rows 8 to 11 out of the detail total |
Two rows show CHECK LINE TYPE, yet a plain SUM still gives -$1,300 |
SUM skips text, so offsetting rows cancel | Use the gated formulas in rows 8 to 11 |
| A blank amount produces a full-size variance | Subtraction treats blanks as zero | Use the guarded E2 formula |
| E10 shows -$0.02 or #VALUE! | Rates subtracted without ×100 under currency format, or text subtracted when revenue is zero | Use the E10 formula and the pp format |
| Driver parts total $5,000, not $4,500 | Both parts multiplied by budget figures | Use budget AOV for the order change and actual orders for the AOV change, unrounded |
| #N/A or #REF! instead of a CHECK label | The guards catch blanks and text, not error values in source cells | Fix the source cell |
Frequently Asked Questions
What if my workbook stores expenses as negative numbers?
Then Actual minus Budget already gives profit impact: -64,200 minus -60,000 is -4,200. To use the worksheet above unchanged, convert expense inputs to positive amounts first. Keeping signed expenses requires changing the detail calculation, subtotal formulas, and input checks together; removing the negative-amount check alone is not enough. If you keep expenses positive but want raw change, label the column “Raw Change,” set favorable and unfavorable by line type, and don’t sum it.
Does “Favorable” always mean good news?
No. Payroll’s +$1,500 is favorable because it raised this month’s profit. If the underspend came from an open technician role, it could be connected to the service revenue miss. The label is arithmetic. Judge the decision separately.
Can I show F and U without turning column E into text?
Yes. Keep E numeric so COUNT and SUM work, and either rely on column G or apply a custom format such as $#,##0.00" F";($#,##0.00)" U";$0.00 to E2:E9 and E11, leaving E10 in points.
What to Do Next
Once the table reconciles, make it part of a regular report. A Monthly Reporting Pack Template for Small Business shows where it fits, and What to Include in a Monthly Business Report covers turning variances into commentary. For the metrics behind the numbers, see how to build a monthly KPI scorecard.