Retail Reporting in Dynamics 365: 12 KPIs GCC Retailers Should Track
- Ayesha Binte Habib
Retail reporting in Dynamics 365 means tracking 12 KPIs: gross profit %, inventory ageing, inventory-to-sales ratio, inventory turnover, sell-through rate, GMROI, sales per square metre, like-for-like sales growth, average basket value, stock-out rate, shrinkage and returns and markdown rate. Dynamics 365 Finance and Operations holds the data for all of them. A Power BI model on top of it turns that data into dashboards a CFO can use, and for GCC retailers the model must also handle VAT, multiple currencies, several countries and Arabic and English reporting.
Â
Most KPI lists describe retail in general and stop at the formulas. This one is written for retailers running Dynamics 365 F&O in the GCC, and it covers what happens after the formula: where the data sits, which KPIs the CFO asks for first, and where standard reports fall short. It also names the hard part honestly. Stock ageing by SKU is difficult at retail data volumes, and we explain how to handle it instead of skipping it.
The 12 retail KPIs to build into your Dynamics 365 dashboard
The table lists each KPI with the formula, the cut that makes it useful and why a GCC retailer needs it. With the formulas follow standard retail definitions.
Â
# | KPI | Formula | Slice by |
|---|---|---|---|
1 | Gross profit % | (Net sales – COGS) / net sales | Store, category, SKU, brand |
2 | Inventory ageing | Stock value by age band | Category, store, SKU |
3 | Inventory-to-sales ratio | Inventory at cost / net sales | Store, category |
4 | Inventory turnover | COGS / average inventory | Category, store |
5 | Sell-through rate | Units sold / units received | Season, category, supplier |
6 | GMROI | Gross margin / average inventory cost | Category, brand |
7 | Sales per square metre | Net sales / floor area | Store, region |
8 | Like-for-like sales growth | Sales this period vs same stores last period | Store, country |
9 | Average basket value | Revenue / number of transactions | Store, channel |
10 | Stock-out rate | Lines out of stock / lines listed | Store, SKU |
11 | Shrinkage | Book stock value – counted stock value | Store, category |
12 | Returns and markdown rate | Returns and discounts / gross sales | Store, promotion |
The three KPIs CFOs ask for first
1. Gross profit %.
This is the first number a CFO checks. Report it by store, category and SKU on the same net-of-VAT basis in every country, otherwise stores in different VAT regimes cannot be compared.
Â
2. Inventory ageing.
Inventory ageing is the report most retailers want and find hardest to produce. Microsoft’s standard F&O inventory ageing report works at warehouse level, which means an inter-store transfer can reset a product’s age. It also uses a fixed set of age bands and does not link to provisioning, so finance ends up rebuilding it in Excel. At SKU level the data volume is the other problem. The practical answer is to calculate age in a prepared data layer, refreshed on a schedule, so Power BI shows category and store ageing instantly and opens SKU detail only on drill-through.
Â
3. Inventory-to-sales ratio.
Define it once and keep the same basis everywhere: inventory at cost against net sales for the same period. Show it by store and category with a target line. A ratio that climbs while sell-through falls is an early warning of overstock.
How each KPI is built
The sales and margin KPIs (1, 7, 8, 9, 12) come from sales and retail transaction data. The stock KPIs (2, 3, 4, 5, 6, 10, 11) come from inventory transactions and on-hand data. Floor area and targets are usually not in the ERP, so they are added as small maintained tables. One governed Power BI model joins all of it, so every report uses the same definitions.
What GCC retailers need from retail reporting in Dynamics 365
The KPIs are the same everywhere. The reporting model is not, and it is where GCC projects succeed or fail.
- VAT: The UAE standard rate is 5% and Saudi Arabia’s is 15%, and Saudi Arabia also requires e-invoicing through ZATCA. Margin and sales KPIs should be reported net of VAT so stores in different countries compare fairly.
- Multiple currencies and countries: Show results in the local currency and in one group currency, with the exchange-rate basis stated on the report.
- Arabic and English: Management and store teams often read different languages. The model should carry both labels so one dashboard serves both.
- Franchise and distributor models: Where stock sits with partners, separate owned-store and franchise sales so margin and ageing are not mixed.
Retail reporting options for Dynamics 365 F&O compared
Option | Best for | Limit for retail reporting |
|---|---|---|
Standard F&O reports and inquiries | Day-to-day operational lookups | Fixed layouts, such as the warehouse-level ageing report |
Excel exports | One-off analysis | Manual, repeated every month, no single set of definitions |
Power BI with a governed model | CFO and management dashboards across stores and countries | Needs a prepared data layer at SKU volumes |
Jet Analytics | A governed cube and data warehouse layer under Excel and Power BI | Needs implementation; it is a data layer, not a dashboard on its own |
Jet Reports | Finance teams that work in Excel | Excel reports, not visual dashboards |
Atlas | Pulling and editing F&O data from Excel with transaction drill-down | Excel based, so not suited to boardroom visuals |
Microsoft Business Performance Analytics | Finance reporting inside the Microsoft stack | Confirm coverage of retail stock KPIs before relying on it |
Â
Most retailers end up with two layers: a governed data layer and Power BI dashboards for the CFO, with Excel-based reporting for finance teams who prefer it. See our Power BI dashboard samples and our Finance and Operations dashboards.
How to get Retail Dashboard live on Dynamics 365
- Agree the definitions with finance. Fix the formula, VAT basis and currency for each of the 12 KPIs before any build.
- Prepare the data layer. Build ageing, margin and stock calculations once, with incremental refresh, so dashboards stay fast.
- Build the dashboards by audience. Start with a CFO page (gross profit %, inventory-to-sales, ageing), then store and category pages with drill-through to SKU.
- Hand over and train. Finance should be able to read, filter and extend the reports without a developer.
We can scope this against your own F&O data. Request pricing or book a demo to see retail reporting in Dynamics 365 running on a model like yours.
Find right BI Tool for your retail business.
Search Blog
Related Resources

Healthcare Analytics Dashboard for Hospitals

Business Central Dashboards with Power BI
