Blog · How-to guides · Finance and FP&A teams
Organize a finance workbook around clean actuals, explicit budget calculations and control totals. Follow a worked revenue and cost comparison, then check that the operating profit variance reconciles.
MS Excel for finance works best when clean inputs, explicit calculations and control totals form a single reviewable chain. Start with the business export you already have, map it into consistent revenue and cost categories, and compare it with the approved budget. Reconcile the totals before drawing conclusions from a variance.
Use separate places for source data, reviewed mappings, calculations and checks. The aim is to make a finance reviewer able to trace a reported amount back to the records that produced it.
| Worksheet | What it contains | Control to retain |
|---|---|---|
| Actuals | Ledger or finance-system export | Source total and row count |
| Budget | Approved plan for the comparison period | Budget version and approved totals |
| Mapping | Account codes and reporting categories | Unmatched and multiply mapped accounts |
| Report | Revenue, costs, margins and variances | Formulas that refer to the input tables |
| Controls | Period, entity, currency and reconciliation | Differences that must be resolved |
Record whether cost amounts are positive expenses or signed ledger balances. The example below uses positive revenue and positive expenses, then subtracts expenses in the profit calculation. A different sign convention can work, but it must be explicit throughout the workbook.
Do not mix posted actuals with an unapproved working forecast and call the comparison budget versus actual. Name the version, cutoff date and included entities beside the report.
An illustrative US distributor reviews October 2026. Actuals and budget have been summarized to the same categories and departments, in USD. Put this table in A1:D7, with columns Category, Department, Actual and Budget.
| Category | Department | Actual (USD) | Budget (USD) |
|---|---|---|---|
| Revenue | East | 120,000 | 125,000 |
| Revenue | West | 80,000 | 75,000 |
| COGS | East | 72,000 | 73,000 |
| COGS | West | 48,000 | 42,000 |
| Operating expense | East | 30,000 | 28,000 |
| Operating expense | West | 20,000 | 22,000 |
The input totals are $370,000 actual and $365,000 budget. Those totals are checks on the exported positive amounts, not revenue or profit: the table mixes income and expense categories.
For a larger file, format the range as an Excel table named Finance. Microsoft explains that structured references use table and column names and adjust when table data changes. A clear name also makes the calculation easier to review than an unexplained range.
If a source account needs a reporting category, maintain a reviewed mapping instead of manually rewriting its description. The XLOOKUP finance guide covers the lookup formula and unmatched-account handling in depth.
Create a report with Revenue, COGS and Operating expense as category labels. If the category label is in H2, actual and budget totals are:
=SUMIFS(Finance[Actual],Finance[Category],H2)
=SUMIFS(Finance[Budget],Finance[Category],H2)
Microsoft documents SUMIFS as summing values that meet multiple criteria. To restrict the same total to East, add the department criterion:
=SUMIFS(Finance[Actual],Finance[Category],H2,Finance[Department],"East")
In a detailed ledger, apply the period and entity criteria too, or confirm that the imported file is already restricted to the approved scope. A correct formula over the wrong population gives the wrong business answer.
With report actuals in I2:I4 and budgets in J2:J4, the resulting summary is:
| Report line | Actual (USD) | Budget (USD) | Actual minus budget (USD) |
|---|---|---|---|
| Revenue | 200,000 | 200,000 | 0 |
| COGS | 120,000 | 115,000 | 5,000 |
| Operating expense | 50,000 | 50,000 | 0 |
| Gross profit | 80,000 | 85,000 | -5,000 |
| Operating profit | 30,000 | 35,000 | -5,000 |
Gross profit in I5 and operating profit in I6 are:
=I2-I3
=I5-I4
Copy those formulas into column J for budget. The gross profit margin guide explains the financial definition and cost classification; the workbook should apply the same approved classification to actuals and budget.
For any report line, actual minus budget is:
=I2-J2
For a percentage variance, use the budget as the denominator and flag a zero budget explicitly:
=IF(J2=0,"REVIEW",(I2-J2)/J2)
At department level, East revenue is $5,000 below budget, or -4.00%. West revenue is $5,000 above budget, or 6.67%. The revenue total is exactly on budget because those dollar differences offset. Percentage variances here are rounded to two decimal places; the formulas retain full precision.
Do not average the departmental percentages to calculate the company variance. Divide total actual revenue minus total budget revenue by total budget revenue.
The profit bridge is equally explicit: zero revenue variance, minus the $5,000 additional COGS, minus zero additional operating expense, equals a $5,000 reduction in operating profit. Thus $35,000 budget profit minus $5,000 equals $30,000 actual profit.
Gross margin is $80,000 divided by $200,000, or 40.0%, against $85,000 divided by $200,000, or 42.5%, a decline of 2.5 percentage points. Margin percentages are shown to one decimal place. Stable total revenue does not establish stable profitability.
Use the original source totals, not a second copy of the report formulas, as the independent comparison. Put the actual export control of $370,000 in Controls!B5 and the approved budget control of $365,000 in Controls!B6:
=SUM(Finance[Actual])-Controls!B5
=SUM(Finance[Budget])-Controls!B6
Both differences must be zero for these example inputs. Also confirm that every included account maps to exactly one intended reporting category. An aggregate match alone cannot detect an equal omission and duplication.
Check that departmental revenue, COGS and operating expense each sum to the corresponding company line. Then verify the profit bridge independently: actual profit minus budget profit must equal revenue variance minus cost variances under the positive-expense convention.
Use conditional formatting for finance reports to make unresolved checks visible. For a complete reporting layout, the monthly financial report template owns the broader statement presentation.
Covirage is not affiliated with Microsoft.
Bring the reviewed actuals and budget workbook, with periods, reporting categories and source controls clearly identified. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for finance teams for the reporting context.
Start with consistent tables, account mappings, conditional totals and explicit variance formulas. Equally important are checks for missing accounts, duplicate rows and agreement with source totals. Learn a formula in the context of the financial question it answers, rather than building a workbook around a list of functions.
They need a consistent reporting structure, even when the original files use different account codes or detail. Maintain a reviewed mapping into common categories. Keep unmatched accounts visible, and confirm that both files cover the same entity, currency and period before comparing the resulting totals.
State the sign convention. Actual minus budget is a useful numeric variance, but a positive cost variance means spending above plan while a positive revenue variance means revenue above plan. Add a separate profit-impact calculation or a clearly labeled favorable or unfavorable interpretation.