Retail Reporting in Dynamics 365: 12 KPIs GCC Retailers Should Track

Retail Reporting in Dynamics 365 F&O and Power BI

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

  1. Agree the definitions with finance. Fix the formula, VAT basis and currency for each of the 12 KPIs before any build.
  2. Prepare the data layer. Build ageing, margin and stock calculations once, with incremental refresh, so dashboards stay fast.
  3. 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.
  4. 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.

Share this blog on:

Search Blog

About Us

Global Data 365 is composed of highly skilled professionals who specialize in streamlining the data and automate the reporting process through the utilization of various business intelligence tools.

Follow us on:

Still using Jet Basics?

Get Free Upgrade
to Jet Reports

jet services

Subscribe to Our Newsletter