Blog · Board and management reporting
Automate stable calculations before relying on recurring BI results. Use monthly exports to work through revenue, gross profit, order value, and the checks a business owner should review.
Automated business intelligence starts with repeating agreed calculations and checks on comparable business files. Automate stable arithmetic first, then keep a person accountable for source completeness, exceptions, and the decisions that follow. Faster reporting is useful when the same measure still means the same thing each month.
The broader business intelligence definition explains the discipline. Here the focus is the business workflow from a monthly export to a reviewed KPI comparison, without assuming that a file arrives or a report is delivered unattended.
Choose a question your team already answers regularly, such as whether revenue growth is producing more gross profit. Identify the source file, reporting period, row level, amount signs, and owner. A measure with unresolved definitions is not ready for a recurring calculation.
For a US distributor, the controller approves net revenue and cost definitions. Sales operations supplies the completed order summary. An analyst prepares the comparison, and a finance reviewer checks the results against source controls before managers act on them.
Use the same inclusion rules in every month. For example, compare posted net invoice revenue after credits with the cost of those sales. Do not pair booked orders with shipped-product costs or a partial current month with a completed prior month.
The following workbook contains the complete example. Its revenue is net of credits, cost is the associated cost of goods sold, and each order belongs to one region. Amounts are USD; the source owner has confirmed both months are complete.
| Month | Region | Revenue (USD) | Cost of goods sold (USD) | Orders |
|---|---|---|---|---|
| August 2026 | East | 120,000 | 78,000 | 60 |
| August 2026 | West | 80,000 | 56,000 | 40 |
| September 2026 | East | 130,000 | 84,500 | 65 |
| September 2026 | West | 90,000 | 58,500 | 45 |
These rows are region-month summaries, not invoice lines. Summed order counts are valid here because orders appear in one region and one month only. If a source instead repeats an order on several product lines, obtain a distinct-order measure before using order counts.
The repeated task is to summarize the rows, calculate the same KPIs, and compare the results with independently supplied source totals. Changing the business question or definition requires another review.
In Excel, convert the source range to a table named Results. A summary month in G2 can drive the revenue calculation:
=SUMIFS(Results[Revenue (USD)],Results[Month],G2)
Use the same pattern for the cost and order columns. Microsoft's SUMIFS documentation describes summing values that meet supplied criteria. Match the month text exactly here; in a transaction export with real dates, use consistent month boundaries instead.
Structured table references help avoid fixed ranges that stop before newly added rows. Microsoft documents that structured references adjust when table data changes. That behavior does not prove the new rows belong to the correct month or that the source export is complete.
Calculate the business measures from the summarized values:
Gross profit = Revenue − Cost of goods sold
Gross margin = Gross profit / Revenue
Average order value = Revenue / Orders
| Measure | August 2026 | September 2026 | Calculation of September result |
|---|---|---|---|
| Revenue (USD) | 200,000 | 220,000 | 130,000 + 90,000 |
| Cost of goods sold (USD) | 134,000 | 143,000 | 84,500 + 58,500 |
| Orders | 100 | 110 | 65 + 45 |
| Gross profit (USD) | 66,000 | 77,000 | 220,000 − 143,000 |
| Gross margin | 33.0% | 35.0% | 77,000 / 220,000 |
| Average order value (USD) | 2,000 | 2,000 | 220,000 / 110 |
Dollar and order figures are exact. Percentages are displayed to one decimal place. For August, revenue is $120,000 plus $80,000, cost is $78,000 plus $56,000, and gross profit is $200,000 minus $134,000.
The following identities prove internal consistency:
| Check | August calculation (USD) | September calculation (USD) |
|---|---|---|
| Cost plus gross profit equals revenue | 134,000 + 66,000 = 200,000 | 143,000 + 77,000 = 220,000 |
| Orders times average order value equals revenue | 100 × 2,000 = 200,000 | 110 × 2,000 = 220,000 |
Internal identities will still pass if an entire source row is missing. Separately compare revenue and cost totals with the approved invoice and cost reports, and compare orders with the source order summary. Keep unmatched amounts visible and require an owner to explain them.
Check expected business categories too. A region disappearing from the file may mean missing activity rather than a closed territory. Confirm the source reason before treating an absent region as zero.
Revenue rises by $20,000, or 10.0%, calculated as $220,000 divided by $200,000 minus one. Orders rise from 100 to 110, also 10.0%, while average order value stays at $2,000. The aggregate comparison points to more orders rather than a higher average order value.
Gross profit rises by $11,000, from $66,000 to $77,000, and gross margin increases by 2.0 percentage points. Finance should investigate the cost and sales mix before attributing that improvement to supplier negotiations or pricing. The summary identifies a movement; source evidence supports a causal explanation.
Use business intelligence charts to display the approved comparison. A new question that falls outside the recurring definition belongs in ad hoc business intelligence, with its assumptions reviewed separately.
Maintain the definitions and checks through BI reporting best practices. Covirage is not affiliated with Microsoft.
Bring a sample of your own monthly exports to Covirage; it maps the columns, and its tools compute and check the comparisons. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your file.
See self-service analytics for the supporting business use case.
Start with a recurring measure whose source export, calculation, period, and owner are already agreed. Repeated summaries and reconciliations are suitable candidates. Resolve changing definitions and unreliable source files before making a calculation part of the routine.
No. A repeated calculation can still use an incomplete export, changed column meaning, or incorrect department assignment. A business reviewer should check source completeness and exceptions, then decide whether the reported movement needs action or further investigation.
Compare the same defined process before and after the change. Record preparation time, unresolved exceptions, reconciliation differences, and whether the review was completed when needed. Keep the scope constant so a simpler report is not mistaken for a faster process.