Excel Report Building: One Definition, One Number for Management
An Excel report that answers a manager’s question is built on definitions before formulas. At a fictional valve manufacturer, the managing director asks: “W-01, our biggest dealer in the West, wants its off-invoice discount raised from 4% to 6%. Can it carry that?” Sales comes back with a 35.0% gross margin for dealer W-01, finance with 31.9%. Both calculated correctly, but they put different things into “revenue”: returns, the off-invoice discount, the exchange rate and the period. When I build an Excel report, I set up a written definitions sheet, a bridge that links the two numbers line by line, and formulas that write each definition once.
This page is for sales and finance managers and report analysts. You can build the setup yourself from the formulas below.
What do you get when I build an Excel report?
You get a setup that answers one question the same way every period, in four parts:
- The question. “Sales report” is not a question. “What was gross margin by region and dealer last quarter, and what changed it?” is.
- A definitions sheet. A one-line definition of revenue, gross profit, returns, discounts, exchange rate and period, on the workbook’s first tab.
- A bridge. A table from the sales number to the finance number, line by line. If the unexplained difference exceeds a set tolerance, the report does not go out.
- A formula or pivot layout. The ERP export is pasted into the same sheet each period and the report updates with Refresh.
For one meeting, a one-off delivery is enough; for a recurring question we build the setup once, and while the export layout holds, only the export changes.
The most common mistake I see with reports is trying to put every piece of data on the dashboard, so I write the question first. For a one-screen visual summary, see the Excel dashboard page; this page is about whether the number under the chart is right.
Sample quarter: two numbers for the same question
Sample data, fictional; exchange rates are examples. A company makes industrial valves and fittings and sells in four regions through dealers and project customers. It reports in Turkish lira (TL), so all amounts below are TL. Its largest West dealer, W-01, gets a 4% off-invoice discount. Some project sales are priced in euros and converted at the invoice-date rate.
- Sales works from CRM orders: commission is order-based, so order date and order-date rate; no returns or off-invoice discounts.
- Finance works from the ERP export of invoice lines: document date, returns and discounts as separate line types, euro sales at the invoice rate.
Cost is the same on both sides: unit cost from the product record times quantity. I assumed returned goods go back into stock at that cost; that is an example assumption, and the result changes below if the goods are scrapped. The table on the managing director’s desk:
| region | sales margin | finance margin | gross profit gap |
|---|---|---|---|
| Central | 33.0% | 33.0% | −19,800 |
| North | 33.0% | 32.6% | −90,000 |
| South | 31.0% | 31.3% | +61,200 |
| West | 35.0% | 33.5% | −635,500 |
| Total | 33.3% | 32.7% | −684,100 |
The sales margin is on order intake, the finance margin on net sales; the gap does not run one way. In the sales view every West order carries a 35% margin, so region and dealer W-01 both show 35.0%. Finance separates them: region 33.5%, dealer 31.9%.
Where the gap came from: the West bridge
A bridge lists, one by one, the items that take you from one number to the other. For the West region:
| item | amount | gross profit |
|---|---|---|
| Order intake, CRM total (P5) | 10,400,000 | 3,640,000 |
| − Open orders (Q5) and unexplained difference (R5) | 0 | 0 |
| = Order intake calculated from the ERP (F5) | 10,400,000 | 3,640,000 |
| Order taken at quarter end, invoiced next quarter | −640,000 | −224,000 |
| Order taken last quarter, invoiced this quarter | +210,000 | +73,500 |
| Exchange-rate effect: 40,000-euro sale (48.20 instead of 47.00 TL per euro) | +48,000 | +48,000 |
| W-01’s return | −780,000 | −273,000 |
| W-01’s 4% off-invoice discount | −260,000 | −260,000 |
| Net sales (finance, by document date) | 8,978,000 | 3,004,500 |
Of the West’s 635,500 gross profit gap, 533,000 comes from W-01: the return and the discount. The period and exchange-rate items come from other West customers. Rate effect and discount do not touch cost; the 780,000 return puts 507,000 of goods back into stock, so gross profit falls by 273,000. If the goods are scrapped, cost does not come back, the effect is −780,000 instead, and the West margin falls to 27.8%.
The bridge starts from the sales team’s CRM figure (P5), subtracts open orders (Q5, status “not invoiced”) and the unexplained difference (R5) to reach order intake from the ERP (F5), then four items lead to net sales (B5). My starting point: R5 should not exceed one-thousandth of net sales, held in cell S5. If it does, look for a cancelled or changed order; the report waits until it is found. In the sample the order invoiced next quarter is already in the export, so there are no open orders and the gap is zero.
The decision: no extra discount
The question is about a dealer, not a region. W-01’s numbers:
| amount | gross profit | margin | |
|---|---|---|---|
| Order intake (sales) | 6,500,000 | 2,275,000 | 35.0% |
| Net sales (return and 4% discount deducted) | 5,460,000 | 1,742,000 | 31.9% |
| Net sales, with a 6% discount | 5,330,000 | 1,612,000 | 30.2% |
In the sample, the discount on the returned invoice is not reversed. On that basis the discount, not the return, lowers the margin. The return brings goods back at cost, so alone it leaves the margin at 35.0%; the 4% discount alone takes it to 32.3%. Together: 31.9%, about 2.7 points from the discount and 0.4 from the return, and those 0.4 points push W-01 below the 32% threshold.
If the 4% discount on the returned goods were reversed (31,200), W-01 would stay at 32.3%, above the threshold; a 6% discount still takes it to 30.9%, so the decision is the same either way.
The company’s rule (an example): a discount that takes a dealer below 32% gross margin needs the managing director’s approval. In the sales view an extra 2% gives 33.7% and the rule never triggers. On net sales W-01 is already at 31.9% and would fall to 30.2%. Today the West is the highest-margin region at 33.5%; with the extra discount it would fall to 32.5%, below Central’s 33.0%. The regional number was hiding the dealer.
The managing director turned the extra discount down and asked for a proposal tying any discount to an annual volume commitment.
The second decision was about naming. The sales figure was not wrong; its name was. Commission is still calculated on orders, but that figure is now called “order intake”. “Net sales” and “gross profit” appear only with the meaning on the definitions sheet.
Definitions sheet: my starting point
A definitions sheet gives every term a one-line definition and its source column. This is my starting point, not an accounting standard; your finance manager signs off your version and owns changes to it.
| term | my starting point |
|---|---|
| Gross sales | Invoice line amount after line discount, excluding VAT |
| Off-invoice discount | A discount taken off the invoice total, as its own line type |
| Net sales (revenue) | Gross sales − off-invoice discount − returns, excluding VAT |
| Order intake | CRM order amount, by order date and order-date rate |
| Period | Date of the invoice or return document |
| Returns | In the return document’s period, on a separate line; past reports do not change silently |
| Return rate | Returns ÷ gross sales, from the same period’s documents |
| Exchange rate | The invoice rate; the gap to the order rate is a separate “exchange-rate effect” line |
| Cost of sales | Product-record unit cost × quantity, reversed on returns (if the goods are resaleable) |
| Gross profit, margin | Net sales − cost of sales; margin = gross profit ÷ net sales |
| Region | Region on the customer record, not the delivery address; one customer, one region |
Three rules I suggest writing under the sheet:
- A line type not on the sheet (say “volume rebate” appears for the first time) waits for the definition owner. In Excel the K5 check below shows it; in an app it never enters the totals.
- A definition change becomes a dated version; old reports are read with the version they used.
- If cost in the accounts differs from cost here, the difference shows as its own line.
Building it in Excel: column layout and formulas
Paste the ERP export into a “Lines” sheet each period in the same column order, one document line per row. Take it up to today, not just to period end, so orders taken in the period and invoiced later appear. I assumed your export has order date and order rate columns; if not, they are matched from the CRM by order number. Lines columns:
- A Document no, B Document date, C Order date (document date on returns and discounts), D Region, E Customer code
- F Line type: “Sale”, “Return” or “Off-invoice discount”
- G Amount in document currency, excluding VAT, negative on return and discount lines
- H Invoice rate, I Order rate (1 for TL lines; on returns and discounts I = H)
- J Cost: negative on returns, 0 on discounts
- K Amount:
=G2*H2; L Amount at order rate:=G2*I2 - M Document in period:
=AND(B2>=Report!$B$1,B2<=Report!$B$2) - N Order in period:
=AND(C2>=Report!$B$1,C2<=Report!$B$2)
On the “Report” sheet, B1 is the period start, B2 the period end, row 4 holds headings and regions run down from A5. Report cells are written with row numbers (B5, K5, P5; L–O empty); Lines columns always carry the Lines! prefix. Row 5:
- B5 Net sales:
=SUMIFS(Lines!K:K,Lines!D:D,$A5,Lines!M:M,TRUE) - C5 Cost of sales:
=SUMIFS(Lines!J:J,Lines!D:D,$A5,Lines!M:M,TRUE) - D5 Gross profit:
=B5-C5, E5 Margin:=IF(B5=0,"",D5/B5) - F5 Order intake:
=SUMIFS(Lines!L:L,Lines!D:D,$A5,Lines!F:F,"Sale",Lines!N:N,TRUE) - G5 Period effect:
=SUMIFS(Lines!L:L,Lines!D:D,$A5,Lines!F:F,"Sale",Lines!M:M,TRUE)-F5 - I5 Returns:
=SUMIFS(Lines!K:K,Lines!D:D,$A5,Lines!F:F,"Return",Lines!M:M,TRUE) - J5 Off-invoice discount: the same with
"Off-invoice discount" - K5 Check:
=ROUND(B5-(F5+G5+H5+I5+J5),0) - P5 CRM order total, Q5 CRM open orders (typed or from a CRM export), R5 Unexplained difference:
=P5-Q5-F5, S5 Tolerance:=ROUND(B5*0.001,0); if R5’s absolute value exceeds S5, the report waits
H5, the exchange-rate effect, is long; I split it for reading, and one line works too:
=SUMIFS(Lines!K:K,Lines!D:D,$A5,
Lines!F:F,"Sale",Lines!M:M,TRUE)
-SUMIFS(Lines!L:L,Lines!D:D,$A5,
Lines!F:F,"Sale",Lines!M:M,TRUE)B5 adds every line type; the bridge items (F5–J5) add only three. With only those three in the export, K5 is zero. A line type not on the sheet enters B5 but not the bridge, and shows in K5 with its amount. The real CRM-to-ERP check is R5. Build the gross profit bridge with the same formulas on column J.
To skip typing regions, use dynamic arrays. =SORT(UNIQUE(FILTER(Lines!D2:D5000,Lines!D2:D5000<>""))) in A5 spills the list; with A5# instead of $A5, SUMIFS spills one result per region. On 16 October 2026 Microsoft listed UNIQUE for Microsoft 365, Excel 2024 and Excel 2021 (Microsoft Support); its spilled range operator page lists Microsoft 365 only, as does the GROUPBY page.
I recalculated the logic in Python with the quarter’s 17 fictional rows: every cell matched, and an added “volume rebate” row showed up in K5. I did not run it in Excel; try it on a copy of your file.
PivotTables and the Data Model: writing a definition once
A PivotTable does the same by drag and drop; watch where the definition lives. Drag a column into Values and Excel creates an implicit measure. According to Microsoft (read 16 October 2026), an implicit measure works only in the PivotTable or chart it was created in, while an explicit measure written in Power Pivot can be used by any PivotTable or PivotChart in the workbook (Microsoft Support).
Write “Net Sales” and “Gross Profit” once as explicit measures and both teams’ pivots use the same formula. With the Data Model table named Lines, column K headed Amount and J headed Cost (I did not run these in Excel):
Measure name: Net Sales; formula: =SUM(Lines[Amount])
Measure name: Gross Profit; formula: =[Net Sales]-SUM(Lines[Cost])Filter the pivot on “Document in period” = TRUE. With several arguments the separator depends on regional settings; these lines have none.
Two traps: a line-type filter left on changes the number (print the filter state on the report), and a PivotTable does not refresh itself in every version: Microsoft says automatic refresh is currently for Microsoft 365 Insider participants; others use Refresh (Alt+F5 on Windows), Refresh All or refresh-on-open (Microsoft Support, 16 October 2026).
If Excel is enough, you do not need an app
First check: if your ERP’s own report matches your definitions sheet, build nothing new and use it. If not, four questions decide. If all four left-hand conditions hold, Excel is enough; if even one turns into its right-hand partner, consider an app:
| Excel is enough | Consider an app |
|---|---|
| One person prepares the report; everyone reads that file | Three or four people build it from their own copies, and three numbers reach the meeting |
| Every reader may see every region | A regional manager should see only their region |
| The figure feeds no decision someone will later question with “who changed this?” | It feeds commission or a dealer discount, and a definition’s history will be asked for |
| Data comes from one system, once per period | Data comes from several systems, often within the period |
The sample company falls on the app side in rows 3 and 4. I would still start in Excel and decide on an app once the bridge closes.
Do not build visibility limits in Excel by hiding or protecting sheets. Microsoft: “Worksheet level protection isn’t intended as a security feature” (Microsoft Support, 16 October 2026).
In an app the definition is applied on upload: an unknown line type lands on the definition owner’s screen, and a regional manager’s query returns only their region. If month-end checks, tying to the trial balance and report versioning are your real concern, see the month-end management report page.
Budget against actual spend by department is a separate job: the budget vs actual expense dashboard page.
If you choose an app: roles and audit trail
My basic rule: whoever changes a definition does not approve the change.
- Definition owner (finance manager): proposes sheet changes, classifies unknown line types.
- Approver (managing director): approves sheet changes, sees every region.
- Uploader (reporting analyst): uploads exports, cannot change the sheet.
- Regional manager: only their region’s rows, bridge and dealers.
- Auditor: reads everything, changes nothing.
Everyone signs in with their own account; the approval rule needs separate accounts.
The audit trail keeps a permanent line per upload and definition decision: who uploaded what and when, row count and total, sheet version, unexplained difference, approvals. The W-01 decision stays recorded as taken on “net sales, sheet version 2”.
What does AI do, and where does it stop?
AI calculates no number here; every figure comes from a defined formula or query. A model can help in two places, built as needed:
- Classifying an unknown line type. If “volume rebate” appears for the first time, the model suggests the sheet item it resembles (“looks like an off-invoice discount”). The definition owner decides; until then the line stays out of the totals.
- Drafting a summary from the bridge. “Of the West’s 635,500 gross profit gap, 533,000 comes from W-01’s return and discount.” Figures come from the table; nothing is sent before the owner reads it.
No row goes to the model without passing the permission filter first: a regional manager’s question is answered only from rows that manager can see, and never names another region’s dealer.
A customer name can be personal data, such as a sole trader’s, so the model gets customer codes, not names, in line with data minimisation under the EU GDPR and UK GDPR. I list in writing which fields go to the model; your legal adviser assesses it.
In Turkey, sending personal data to a service abroad falls under KVKK Article 9 as amended by Law No. 7499 (current text read on mevzuat.gov.tr, 16 October 2026); the same design applies.
How do we start, and who owns the data and code?
The first step looks at the manager’s question, last period’s two reports and the exports (customer names masked). Its output puts in writing a draft definitions sheet, the bridge, my recommendation on Excel or an app, the first version’s scope and estimated timeline. The first version answers one question; a second is added once the first bridge closes.
If we stay in Excel, you get your workbook: definitions sheet, Lines and Report sheets, bridge and formulas in one file. How it is updated if export columns change is written down in the first step.
If you choose an app, it runs on a server (a VPS) that can be opened in your company’s name; the data is yours, exportable as Excel or CSV whenever you want; a small or mid-sized team pays no per-user licence (cost is server plus maintenance); and the source code sits in a repository you can access, with run instructions, a dependency list and known limitations. Where personal data is involved, access, security, hosting and maintenance are assessed separately.
What if your ERP or CRM changes? Earlier exports, bridges, sheet versions and the audit trail stay. Mapping the new export to the old columns, and which definitions apply during the transition, are scoped in writing in the first step.
Send last period’s two reports and the exports, customer names masked, through the contact page; in the first meeting we find which definition the gap comes from.
Frequently Asked Questions
Do you fix our existing Excel file, or build from scratch?
I tell you in the first step whether your file is fixed or rebuilt, marking which formulas match the definitions sheet. The sheet and bridge can be added as new tabs; if your export columns do not fit, the Lines sheet is rebuilt.
Formula layout or PivotTable?
Both feed from the Lines sheet. For the bridge I suggest formulas: each item sits open in a cell beside the K5 and R5 checks, while a pivot needs a separate measure per item. A pivot is quick for exploring a product or customer breakdown; if you use one, write explicit measures.
Can the same setup be built in Google Sheets?
The definitions sheet, column layout and bridge stay the same. Google’s help pages list SUMIFS and UNIQUE for Sheets (16 October 2026). In Sheets the definition lives in the formula and on the definitions sheet.