Blog · Board and management reporting
Build a retail dashboard in Excel from store sales and inventory exports. This template calculates net sales, gross margin and stock movement, with checks tying the workbook back to source reports.
A retail dashboard in Excel should combine store net sales, gross margin and a stock movement reconciliation for the same period. Calculate from sales and inventory exports before drawing charts. A useful store comparison shows where profit came from and whether the units still tie to closing stock.
Use Inputs for approved store totals, Dashboard for calculations and Checks for reconciliation differences. Keep a store key and period ending on every source row. The retail dashboard overview covers the wider management review; this template concentrates on a reusable workbook.
The example is a US home-goods retailer's June 2026 report for three stores. Sales exclude tax and are already net of discounts. Returns are shown separately as positive amounts to subtract. The cost of goods sold supplied by accounting already reflects the cost reversal for returns.
Inventory units use the same base unit across the selected product category. Net units sold already subtract saleable customer returns; do not add those returned units to inventory again. The example has no interstore transfers. A real workbook with transfers needs separate inbound and outbound columns.
Create this layout on Inputs:
| Column | Field | Rule |
|---|---|---|
| A | Store | Stable store name or identifier |
| B | Sales before returns (USD) | After discounts, excluding tax |
| C | Returns (USD) | Positive amount to subtract |
| D | Cost of goods sold (USD) | Matching net units sold |
| E | Opening units | Start of the reporting period |
| F | Receipt units | Received during the period |
| G | Net units sold | Sales less saleable returns |
| H | Shrink units | Positive stock reduction |
| I | Reported closing units | Independent stock report |
Use the same cost policy across stores. IRS Publication 538 describes purchased merchandise cost as invoice price less discounts plus transportation and other acquisition charges. For this dashboard, use the approved accounting cost rather than estimating cost from the shelf price.
Put these detail rows in Inputs rows 2 through 4. Do not paste the total row into the source range.
| Store | Sales B (USD) | Returns C (USD) | COGS D (USD) | Opening E | Receipts F | Net sold G | Shrink H | Closing I | |---|---:|---:|---:|---:|---:|---:|---:|---:|---:| | Columbus | 120,000 | 8,000 | 67,200 | 1,800 | 900 | 1,500 | 50 | 1,150 | | Richmond | 90,000 | 6,000 | 54,600 | 1,200 | 700 | 1,100 | 25 | 775 | | Tucson | 60,000 | 3,000 | 34,200 | 800 | 500 | 700 | 20 | 580 | | Total | 270,000 | 17,000 | 156,000 | 3,800 | 2,100 | 3,300 | 95 | 2,505 |
Add calculated columns J through M for net sales, gross profit, gross margin and calculated closing units. The formulas in row 2 are:
=B2-C2
=J2-D2
=IF(J2>0,K2/J2,NA())
=E2+F2-G2-H2
Fill them down through row 4. Keep currency totals as whole dollars and show margins to two decimal places. Preserve full precision in the cells.
Net sales = Sales after discounts − Returns
Gross margin = (Net sales − Cost of goods sold) / Net sales
Closing units = Opening units + Receipts − Net units sold − Shrink
| Store | Net sales (USD) | Gross profit (USD) | Gross margin | Calculated closing units |
|---|---|---|---|---|
| Columbus | 112,000 | 44,800 | 40.00% | 1,150 |
| Richmond | 84,000 | 29,400 | 35.00% | 775 |
| Tucson | 57,000 | 22,800 | 40.00% | 580 |
| Total | 253,000 | 97,000 | 38.34% | 2,505 |
Columbus produces 120,000 − 8,000 = $112,000 of net sales and 112,000 − 67,200 = $44,800 of gross profit. Its margin is 44,800 / 112,000 = 40.00%. Richmond's lower margin is a reason to examine cost and sales mix, not proof that its manager discounted too heavily.
The combined margin is 97,000 / 253,000 = 38.34%. Use =SUM(K2:K4)/SUM(J2:J4), rather than an average of the store percentages. The gross profit margin guide explains the revenue weighting behind this check.
Define unit sell-through for this workbook as net units sold divided by opening units plus receipts. Add it in N2:
=IF(E2+F2>0,G2/(E2+F2),NA())
The IF function can return a visible exception when the denominator condition fails. Do not turn missing stock into a zero sell-through rate.
| Store | Available units | Net units sold | Unit sell-through |
|---|---|---|---|
| Columbus | 2,700 | 1,500 | 55.56% |
| Richmond | 1,900 | 1,100 | 57.89% |
| Tucson | 1,300 | 700 | 53.85% |
| Total | 5,900 | 3,300 | 55.93% |
Richmond sells the largest share of available units while recording the lowest gross margin. Review the category rows before ordering more of the same mix. Sell-through here uses units; inventory turnover uses cost and average inventory, so give those measures separate labels.
When storing multiple periods, add period ending in Inputs column O. Put the chosen store in Dashboard A2 and the selected period in P1. Net sales for that selection can be pulled with:
=SUMIFS(Inputs!$J$2:$J$1000,Inputs!$A$2:$A$1000,$A2,Inputs!$O$2:$O$1000,$P$1)
Microsoft documents SUMIFS for sums matching multiple criteria. Use matching ranges for gross profit and inventory inputs, then calculate the selected margin from selected totals.
On Checks, subtract reported closing units from calculated closing units with =Inputs!M2-Inputs!I2. Every store should return zero. The company movement is 3,800 + 2,100 − 3,300 − 95 = 2,505 units, matching the independent closing stock total.
Tie net sales to the approved revenue report and COGS to the same period's cost report. Explain differences against payment settlements separately, since settlements can contain tax, timing differences and fees. For grouping transaction detail before entering approved totals, use the Excel pivot table guide.
Covirage is not affiliated with Microsoft.
Bring a store sales and inventory file with returns, matching costs and stock movements for the same period. Covirage maps the columns, its tools compute and check the figures, and 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 export.
See Excel analysis in Covirage for the matching file workflow.
Show net sales, gross profit, gross margin and closing inventory beside the reporting period and store scope. Add stock movement checks before interpreting sales performance. A sales increase alone cannot show whether the store sold profitably or whether its inventory report is complete.
For this workbook, sales exclude tax collected on behalf of taxing authorities. Use the revenue amount supplied by your approved accounting reports and apply the same boundary across stores. Separate tax and other settlement items when reconciling sales to the point-of-sale cash or payment report.
Use actual net sales and the matching recorded cost of goods sold. Ticket prices do not capture discounts, returns or the cost assigned to the units sold. Keep inventory at cost separate from inventory quantities and from the retail value of stock.
No. This template defines unit sell-through as net units sold divided by opening units plus receipts for the selected period. Inventory turnover usually compares cost of goods sold with average inventory at cost. They use different inputs and answer different operating questions.