Excel mapping through chatgpt-

Step 1 — Sales Report KPI mapping (from tables

shared)
KPI 1) Total Sales

Formula: Σ(Order Quantity × Unit Price)

Possible source tables & columns

A) Shopify — Online Weekly Reports LP – Shopify

 Order Quantity: Quantity ordered

 Unit Price: Product variant price
 Alternative “sales amount” already available:
o Gross sales
o Net sales

✅ This table supports quantity and unit price directly.

Important note (no assumption):

This table also already has Net sales, which may include discount/returns handling. Your
formula says quantity × unit price, so we should confirm whether to use:

 (Quantity ordered × Product variant price) OR

 direct Net sales / Gross sales as sales amount

B) Shopify — POS Sales Report – Shopify

 Order Quantity: Quantity ordered

 Unit Price: Product variant price
 Alternative “sales amount” already available:
o Gross sales
o Net sales
o Total sales (includes shipping + taxes as per the file structure)

✅ Supports quantity and unit price directly.

Clarification required later: Does “Total Sales KPI” include shipping+tax or not?
Because POS table has both Net sales and Total sales.

C) Online Order Fulfillments by Location – Report Emailed

  Order Quantity: Net Quantity
 Unit Price: Product Price
 Alternative sales amount already available:
o Net Sales
o Total Sales

✅ Supports quantity and unit price directly.

✅ Also supports sales by location (useful later).

D) Iconic Sales Data – Iconic Seller Centre

This table does NOT provide “Order Quantity × Unit Price” fields clearly.
It provides aggregated value metrics:

 GMV (Before Cancel)

 GMV (After Cancel)
 NMV

✅ Can be used for sales amount, but not for quantity × unit price.

E) Iconic Sales Data – Odoo

This table provides:

 Price: Product Variant/Sales Price

 Amount: Total or Untaxed Total
But no explicit quantity column shown in shared columns.

✅ Can be used for total sales using Total (line amount), but not strictly quantity × unit price
unless quantity exists elsewhere.

✅ Total Sales KPI: what we can confidently map (without choosing yet)

You have 3 Shopify-based tables that fully support:

 quantity
 unit price
 and already-computed sales measures

So Total Sales can be built from:

 Online Weekly Reports LP – Shopify

 POS Sales Report – Shopify
  Online Order Fulfillments by Location – Report Emailed
And for Iconic:
 Iconic Seller Centre (use NMV/GMV metrics)
 For Total Sales, the Excel row will conceptually look like:

KPI Source
Option Source Table Source Columns Notes
Name System
Total Option Online Weekly Quantity ordered,
Shopify Quantity × Unit Price
Sales 1 Reports LP Product variant price
Total Option Online Weekly
Shopify Net sales System-calculated
Sales 2 Reports LP
Total Option POS Sales Needs tax/shipping
POS Net sales / Total sales
Sales 2 Report confirmation
Total Option Iconic Seller
Iconic NMV Post-cancel & return
Sales 2 Centre

STEP 2 — KPI: Sales Growth (Last Month)

KPI Definition (already confirmed)

Sales Growth (%)

((SalesCurrent Period−SalesPrevious Period)/SalesPrevious Period)×100((Sales_{Current\ Period} -

Sales_{Previous\ Period}) / Sales_{Previous\ Period}) \times 100((SalesCurrent Period
−SalesPrevious Period)/SalesPrevious Period)×100

Period: Last Month (calendar month)

What this KPI fundamentally needs (no assumptions)

To calculate Sales Growth, we need only two things:

1. Sales Amount (same logic as Total Sales: Option 1 & Option 2)

2. Date column that allows:
o Current Month
o Previous Month

Step 2.1 — Identify tables that have a usable DATE

From all tables shared so far, these do have date fields:

Source System Table Name Date Column

Shopify Online Weekly Reports LP Day

Shopify POS POS Sales Report Hour

Shopify Online Order Fulfillments by Location Date

Odoo Iconic Sales Data – Odoo Order Date

Myer Myer Sales Data – SPS Sales Date

Iconic Iconic Seller Centre ❌ No explicit date shared yet

⚠️Important:
Iconic Seller Centre currently cannot support Sales Growth unless:

 a date column exists in another sheet, or

 this file is period-based (weekly/monthly extract)

We will flag this clearly in Excel.

Step 2.2 — Excel-style KPI mapping (Sales Growth)

Below is the exact structure, matching what you approved for Total Sales.

📊 KPI Mapping — Sales Growth (%)

Date
KPI Source Sales Column
Option Source Table Column Notes
Name System Used
Used

Quantity ordered
Sales Option Online Weekly
Shopify × Product variant Day Reconstructed sales
Growth 1 Reports LP
price

Sales Option Online Weekly

Shopify Net sales Day System-calculated
Growth 2 Reports LP

Quantity ordered Hour will be

Sales Option Shopify
POS Sales Report × Product variant Hour truncated to
Growth 1 POS
price date/month

Sales Option Shopify Net sales / Total Depends on tax &

POS Sales Report Hour
Growth 2 POS sales shipping inclusion

Sales Option Shopify Online Order Net Quantity × Date Fulfillment-based

Growth 1 Fulfillments by
 Date
KPI Source Sales Column
Option Source Table Column Notes
Name System Used
Used

Location Product Price sales

Online Order
Sales Option
Shopify Fulfillments by Net Sales Date Already adjusted
Growth 2
Location

Sales Option Iconic Sales Data – Total / Untaxed Exclude commission

Odoo Order Date
Growth 2 Odoo Total lines

Sales No date column →

❌ Iconic Iconic Seller Centre NMV / GMV ❌
Growth growth not possible

Step 2.3 — Explicit flags to document in Excel (very important)

These are not decisions, just facts to record:

1. Iconic Seller Centre

o Sales Growth cannot be calculated
o Reason: No date column available in shared file
2. POS Sales Report
o Hour must be converted to Month
o Growth will be month-over-month aggregation
3. Multiple valid sources
o Growth % may differ slightly across systems
o This is expected, not an error

STEP 3 — KPI: Average Order Value (AOV)

(I will continue slowly, same Excel-style structure.)

KPI Definition (confirmed)

Average Order Value (AOV)

AOV=Total SalesTotal Number of Orders\text{AOV} = \frac{\text{Total Sales}}{\text{Total Number of

Orders}}AOV=Total Number of OrdersTotal Sales

Step 3.1 — What this KPI needs (no assumptions)

To calculate AOV, we need:

 1. Sales Amount
o Same logic as Total Sales (Option 1 & Option 2)
2. Order Count

Both must come from the same grain and same source, otherwise AOV will be incorrect.

Step 3.2 — Tables that contain ORDER COUNT

From the data you shared:

Source System Table Name Order Column

Shopify Online Weekly Reports LP Orders

Shopify POS POS Sales Report Orders

Iconic Iconic Seller Centre Gross Orders

Shopify Online Order Fulfillments by Location ❌ (No order count)

Odoo Iconic Sales Data – Odoo ❌ (Order lines, not order count)

Myer Myer Sales Data – SPS ❌ (Transaction-level, needs derivation)

Step 3.3 — Excel-style KPI mapping (AOV)

📊 KPI Mapping — Average Order Value

KPI Source Sales Column Order Count

Option Source Table Notes
Name System Used Column

Quantity ×
Option Online Weekly Reconstructed
AOV Shopify Product variant Orders
1 Reports LP sales
price

Option Online Weekly

AOV Shopify Net sales Orders System-calculated
2 Reports LP

Quantity ×
Option Shopify
AOV POS Sales Report Product variant Orders POS-only AOV
1 POS
price

Option Shopify Net sales / Total Depends on

AOV POS Sales Report Orders
2 POS sales tax/shipping

Option
AOV Iconic Iconic Seller Centre NMV Gross Orders Marketplace AOV
2

AOV ❌ Shopify Online Order Net Sales ❌ No order count

 KPI Source Sales Column Order Count
Option Source Table Notes
Name System Used Column

Fulfillments by
Location

Iconic Sales Data – Line-level, not

AOV ❌ Odoo Total ❌
Odoo orders

Step 3.4 — Important flags to document (no decisions yet)

1. AOV must be channel-specific

o Online AOV ≠ POS AOV ≠ Iconic AOV
2. Fulfillment data cannot support AOV
o No order-level count
3. Odoo sales lines are not suitable for AOV
o Orders are not explicitly defined

STEP 4 — KPI: Sales by Region

KPI Definition (already locked)

Sales by Region

Σ(Order Amount) grouped by Region\Sigma(\text{Order Amount}) \; \text{grouped by

Region}Σ(Order Amount)grouped by Region

The only thing that matters here is:

👉 What is “Region” in each system?
We will not force one definition—we’ll document what each source can genuinely support.

Step 4.1 — Identify “Region-capable” columns per table

From the tables you shared, here is what explicitly exists (no assumptions):

Source System Table Name Region-related Column

Shopify POS POS Sales Report POS location name

Shopify Online Order Fulfillments by Fulfillment Location, Assigned

Fulfillment Location Fulfillment Location

Iconic Iconic Seller Centre Country

Myer Myer Sales Data – SPS Sales Location #, Original Transacted

 Source System Table Name Region-related Column
Store

Shopify Online Online Weekly Reports LP ❌ None

Odoo Iconic Sales Data – Odoo ❌ None

Shopify
Inventory Export – Shopify Location
Inventory

So:

 Region exists, but in different meanings

 This is normal in real businesses

Step 4.2 — Business-sensible interpretation of “Region”

Using common sense (as you asked):

 POS & Fulfillment → Region = Physical Store / Warehouse

 Iconic → Region = Country
 Myer → Region = Sales Location / Store
 Pure Online Shopify → ❌ Region not available unless customer/shipping data exists
elsewhere

We do not merge these definitions.

We document them clearly.

Step 4.3 — Excel-style KPI mapping (Sales by Region)

📊 KPI Mapping — Sales by Region

Source Sales Column Region

KPI Name Option Source Table Notes
System Used Column

Quantity ×
Sales by Option Shopify POS location Store-level
POS Sales Report Product variant
Region 1 POS name region
price

Sales by Option Shopify Net sales / Total POS location Preferred for
POS Sales Report
Region 2 POS sales name reporting

Online Order
Sales by Option Net Quantity × Fulfillment Warehouse-
Shopify Fulfillments by
Region 1 Product Price Location based region
Location
 Source Sales Column Region
KPI Name Option Source Table Notes
System Used Column

Online Order
Sales by Option Fulfillment Clean fulfillment
Shopify Fulfillments by Net Sales
Region 2 Location revenue
Location

Sales by Option Marketplace

Iconic Iconic Seller Centre NMV Country
Region 2 country

Line Item
Sales by Option Myer Sales Data – Sales
Myer Amounts / Net Store-level
Region 2 SPS Location #
Sale Price

Sales by Shopify Online Weekly No region

❌ Net sales ❌
Region Online Reports LP available

Step 4.4 — Important truths to document (very important)

These are not problems, they are facts:

1. Region is channel-specific
o Store ≠ Warehouse ≠ Country
2. Online Shopify sales cannot be region-split
o Unless customer/shipping tables are added later
3. Sales by Region must be shown per channel
o Not as one blended map

This is exactly how large retailers operate.

Step 4.5 — Decision (using common sense, locked)

✅ Sales by Region will be shown per channel

Examples:

 POS Sales by Store

 Fulfillment Sales by Warehouse
 Iconic Sales by Country
 Myer Sales by Store

No forced consolidation.
STEP 5 — KPI: Top Products by Sales (Top 5)
KPI Definition (locked)

Top Products by Sales

Rank products by Σ(Sales Amount)

Show Top 5

This KPI has two critical parts:

1. Sales Amount (same dual-option logic as before)

2. Product Identifier (this is the most important part)

Step 5.1 — Identify valid PRODUCT identifiers per table

(No assumptions, only what exists)

Source System Table Name Product Identifier(s) Available

Product title at time of sale, Product variant

Shopify Online Online Weekly Reports LP
SKU

Product title at time of sale, Product variant

Shopify POS POS Sales Report
SKU

Shopify Online Order Fulfillments by

Product Title, Variant SKU
Fulfillment Location

Iconic Iconic Seller Centre Product Name, Seller SKU, Shop SKU

Product Variant, Product Category / Display

Odoo Iconic Sales Data – Odoo
Name

Buyer SKU / Model #, Vendor Item #,

Myer Myer Sales Data – SPS
Description

Key fact:

 SKU exists everywhere

 Product names vary
 SKUs are channel-specific, not globally consistent

Step 5.2 — Business-sensible product ranking logic

Using common sense retail logic:

  Ranking should be SKU-based, not name-based
(names change, SKUs don’t—within a channel)
 Ranking should be channel-specific
o Online Top 5 ≠ POS Top 5 ≠ Iconic Top 5

A single blended Top 5 across all channels would:

❌ mix incompatible SKUs
❌ confuse business users
❌ require assumptions

So we do not do that.

Step 5.3 — Excel-style KPI mapping (Top Products by Sales)

📊 KPI Mapping — Top Products by Sales (Top 5)

Product
Source Sales Column
KPI Name Option Source Table Column Notes
System Used
Used

Quantity ×
Top Option Shopify Online Weekly Product
Product variant SKU-level ranking
Products 1 Online Reports LP variant SKU
price

Top Option Shopify Online Weekly Product

Net sales Preferred
Products 2 Online Reports LP variant SKU

Quantity ×
Top Option Shopify Product
POS Sales Report Product variant POS-specific
Products 1 POS variant SKU
price

Top Option Shopify Net sales / Total Product

POS Sales Report Store sales
Products 2 POS sales variant SKU

Online Order
Top Option
Shopify Fulfillments by Net Sales Variant SKU Fulfillment-based
Products 2
Location

Top Option Marketplace Top

Iconic Iconic Seller Centre NMV Seller SKU
Products 2 5

Top Option Iconic Sales Data – Product Exclude

Odoo Total
Products 2 Odoo Variant commission rows

Top Option Myer Sales Data – Line Item Buyer SKU /

Myer Retail partner
Products 2 SPS Amounts Model #
Step 5.4 — Important truths to document (do not skip in Excel)

1. Top Products are channel-specific

o Same product may rank differently across channels
2. SKU is the safest ranking key
o Product names are for display only
3. No global Top 5 unless a Product Master exists
o This can be a future enhancement

Step 5.5 — Decision (locked using common sense)

✅ Top Products by Sales will be shown per channel

✅ Ranked by SKU
✅ Top 5 only

This is:

 Business-correct
 Data-safe
 Client-friendly

STEP 6 — Inventory Report KPIs

We’ll do one KPI at a time.
Starting with Inventory KPI 1.

INVENTORY KPI 1 — Total Inventory Value

KPI Definition (locked)

Total Inventory Value

Σ(Current Stock Quantity×Unit Cost)\Sigma(\text{Current Stock Quantity} \times \text{Unit

Cost})Σ(Current Stock Quantity×Unit Cost)

Step 6.1 — What this KPI strictly needs (no assumptions)

To calculate this KPI, we must have:

1. Current Stock Quantity

2. Unit Cost
3. Both must belong to the same product & location grain
Step 6.2 — Identify tables that contain STOCK QUANTITY

From the tables you shared:

Source System Table Name Stock Quantity Column

Shopify Inventory Export – Shopify Available (not editable), On hand (current)

Odoo Product Variant – Odoo Quantity On Hand

Odoo Stock On Hand – Odoo Inventoried Quantity

Myer Myer Sales Data – SPS Current Stock QTY

Step 6.3 — Identify tables that contain UNIT COST

Source System Table Name Unit Cost Column

Odoo Product Variant – Odoo Cost

Odoo Iconic Sales Data – Odoo Product Variant / Cost

Odoo Stock On Hand – Odoo Value (⚠ aggregated, not unit cost)

Shopify Inventory Export – Shopify ❌ Not available

Myer Myer Sales Data – SPS Net Purchase Price / Gross Purchase Price

Step 6.4 — Valid stock × cost combinations (very important)

Now we only keep valid pairings (same system, same logic).

✅ Option 1 — Odoo (Recommended, cleanest)

Source Stock Quantity Cost

KPI Name Option Source Table(s) Notes
System Column Column

Total Inventory Option Product Variant Quantity On Variant-level

Odoo Cost
Value 1 – Odoo Hand valuation

✅ Option 2 — Odoo (Location-aware)

Source Stock Quantity Cost

KPI Name Option Source Table Notes
System Column Column

Total Inventory Option Odoo Stock On Hand Inventoried Value Value already
 Source Stock Quantity Cost
KPI Name Option Source Table Notes
System Column Column

Value 2 – Odoo Quantity calculated

⚠️Note:
Here Value appears to be pre-calculated, not unit-level.
This is acceptable only if business confirms it is trusted.

⚠️Shopify Inventory (Not valid alone)

Issue Explanation

❌ Missing cost Shopify Inventory Export has no unit cost

❌ Cannot value stock Quantity exists but no valuation

➡️Shopify inventory cannot be used alone for Total Inventory Value.

⚠️Myer SPS (Conditional)

Source Stock
KPI Name Option Source Table Cost Notes
System Quantity

Total Inventory Myer Sales Current Stock Net Purchase Only if stock is
Conditional Myer
Value Data – SPS QTY Price owned

⚠️Needs confirmation whether this stock is:

 Owned inventory
 Or consignment

Step 6.5 — Key truths to document in Excel

1. Odoo is the primary source for inventory valuation

2. Shopify inventory is quantity-only
3. Stock valuation requires cost governance
4. Multiple valid options may exist → client decision
STEP 7 — Inventory KPI 2: Stock on Hand
KPI Definition (locked)

Stock on Hand

Σ(Available Quantity)\Sigma(\text{Available Quantity})Σ(Available Quantity)

Step 7.1 — Identify valid “Available Quantity” columns

From Odoo tables:

Table Column

Product Variant – Odoo Quantity On Hand

Stock On Hand – Odoo Inventoried Quantity

Stock On Hand – Odoo Reserved Quantity (not available stock)

Step 7.2 — Excel-style KPI mapping (Stock on Hand)

📊 KPI Mapping — Stock on Hand

KPI Name Source System Source Table Stock Column Used Notes

Stock on Hand Odoo Product Variant – Odoo Quantity On Hand Primary

Stock on Hand Odoo Stock On Hand – Odoo Inventoried Quantity Location-level

Stock on Hand ❌ Shopify Inventory Export Available

Step 7.3 — Important documentation notes

1. Quantity On Hand is the cleanest definition

2. Reserved Quantity is excluded
3. Shopify stock is informational only
4. Final stock KPIs come from Odoo

STEP 8 — Inventory KPI 3: Inventory Turnover Ratio

KPI Definition (locked)

Inventory Turnover Ratio

Inventory Turnover=Cost of Goods Sold (COGS)Average Inventory Value\text{Inventory Turnover} = \
frac{\text{Cost of Goods Sold (COGS)}}{\text{Average Inventory
Value}}Inventory Turnover=Average Inventory ValueCost of Goods Sold (COGS)

Where:

Average Inventory Value=Opening Inventory Value+Closing Inventory Value2\text{Average Inventory

Value} = \frac{\text{Opening Inventory Value} + \text{Closing Inventory Value}}
{2}Average Inventory Value=2Opening Inventory Value+Closing Inventory Value

Step 8.1 — What this KPI strictly requires (no assumptions)

To compute this KPI correctly, we need three things:

1. COGS
2. Opening Inventory Value
3. Closing Inventory Value

All three must be:

 Cost-based (not selling price)

 Time-aware (period-specific, e.g. monthly)

Step 8.2 — Identify where COGS exists in your data

From the tables you shared:

Source System Table Name COGS-related Column

Shopify Online Weekly Reports LP Cost of goods sold

Shopify POS POS Sales Report Cost of goods sold

Odoo Iconic Sales Data – Odoo Product Variant / Cost + Total (line-level)

Myer Myer Sales Data – SPS Net Purchase Price (proxy, not explicit COGS)

Important facts:

 Shopify tables already provide explicit COGS

 Odoo provides unit cost, not aggregated COGS
 Myer SPS does not clearly separate COGS
Step 8.3 — Identify where Inventory Value exists

Since we already locked Odoo as primary inventory system, inventory value comes from:

Source System Table Name Inventory Value

Odoo Product Variant – Odoo Quantity On Hand × Cost

Odoo Stock On Hand – Odoo Value (pre-calculated)

Step 8.4 — Valid combinations for Inventory Turnover

Now we only keep logically valid combinations.

✅ Option 1 — Shopify COGS + Odoo Inventory (Most practical)

KPI Name Option COGS Source Inventory Value Source Notes

Inventory Shopify (Online + Odoo (Stock On Hand / Product Sales-driven

Option 1
Turnover POS) Variant) turnover

Explanation:

 Shopify = actual sales movement

 Odoo = actual inventory holding
 This reflects real operational turnover

⚠️Option 2 — Odoo-only (Conditional)

KPI Name Option COGS Source Inventory Source Notes

Inventory Turnover Option 2 Odoo (derived) Odoo Requires calculation logic

This would require:

 Quantity sold × unit cost

 Time-based aggregation

⚠️More complex, higher risk without client confirmation.

❌ Not recommended
  Using sales revenue instead of COGS
 Using Shopify inventory quantities (no cost)

Step 8.5 — Excel-style KPI mapping

📊 KPI Mapping — Inventory Turnover Ratio

Source
KPI Name Option Source Tables Columns Used Notes
System(s)

Online Weekly Reports LP, POS

Inventory Option Shopify + Cost of goods
Sales Report, Stock On Hand – Preferred
Turnover 1 Odoo sold, Value
Odoo

Inventory Option Cost, Quantity Needs derived

Odoo Product Variant – Odoo
Turnover 2 On Hand COGS

Step 8.6 — Important documentation notes

1. Inventory Turnover is period-based (monthly)

2. Inventory value must be averaged (opening & closing)
3. Shopify gives clean COGS
4. Odoo gives clean inventory valuation
5. Cross-system KPI is normal and acceptable

STEP 9 — Inventory KPI 4: Low Stock Items

KPI Definition (locked)

Low Stock Items

Count of products where

Current Stock≤Reorder Level\text{Current Stock} \le \text{Reorder

Level}Current Stock≤Reorder Level

Step 9.1 — What this KPI strictly needs

To calculate Low Stock Items, we need both:

1. Current Stock Quantity

2. Reorder Level (minimum stock threshold)
Both must exist for the same product (and ideally location).

Step 9.2 — Check availability of “Current Stock” (we already know

this)

From earlier steps (reconfirming, no new assumptions):

Source System Table Stock Column

Odoo Product Variant – Odoo Quantity On Hand

Odoo Stock On Hand – Odoo Inventoried Quantity

Shopify Inventory Export – Shopify Available / On hand (current)

So Current Stock is available.

Step 9.3 — Check availability of “Reorder Level”

Now the critical part.

From all tables shared so far:

❌ No column exists that clearly represents Reorder Level, such as:

 Minimum Stock
 Reorder Point
 Safety Stock
 Min Qty

This is a hard fact, not a gap in understanding.

Step 9.4 — Excel-style KPI mapping (Low Stock Items)

📊 KPI Mapping — Low Stock Items

Source Reorder Level

KPI Name Option Source Table Stock Column Notes
System Column

Low Stock Product Variant – Quantity On Reorder level

❌ Odoo ❌ Not available
Items Odoo Hand missing

Low Stock Stock On Hand – Inventoried Reorder level

❌ Odoo ❌ Not available
Items Odoo Quantity missing
 Source Reorder Level
KPI Name Option Source Table Stock Column Notes
System Column

Low Stock Inventory Export –

❌ Shopify Available ❌ Not available Quantity only
Items Shopify

Step 9.5 — Business-safe conclusion (documented, not assumed)

 Low Stock Items CANNOT be calculated with current data

 Reason: Reorder Level is missing
 Any calculation now would be a business assumption, which we are avoiding

Step 9.6 — How this should be documented in Excel

In the Excel KPI mapping:

 KPI will be listed

 Source tables will be listed
 Status will be clearly marked as:

“Not feasible with current data – Reorder Level not available”

This is professional and client-safe.

STEP 10 — Inventory KPI 5: Stock-Out Rate

KPI Definition (locked)

Stock-Out Rate (%)

(Number of Stock-Out Events÷Total Products)×100(\text{Number of Stock-Out Events} \div \

text{Total Products}) \times 100(Number of Stock-Out Events÷Total Products)×100

Step 10.1 — What this KPI strictly needs

To calculate Stock-Out Rate, we need three clearly defined things:

1. Definition of a Stock-Out
2. Event logic (did it happen during a period?)
3. Total product count (denominator)
Step 10.2 — Check if “Stock-Out” can be identified from data

From the tables shared:

Odoo tables

Table Relevant Columns

Product Variant – Odoo Quantity On Hand

Stock On Hand – Odoo Inventoried Quantity, Reserved Quantity

So we can detect a state where:

 Quantity = 0

But…

Step 10.3 — Critical limitation (very important)

What we do NOT have:

❌ No historical snapshot showing:

 When stock became zero

 How long it stayed zero

This means:

 We can identify current zero stock

 We cannot identify stock-out events over time

Your KPI definition explicitly says:

“Percentage of products that went out of stock during a period”

That requires time-based event tracking, not just a snapshot.

Step 10.4 — Excel-style KPI mapping (Stock-Out Rate)

📊 KPI Mapping — Stock-Out Rate

 Source
KPI Name Option Source Table Stock Column Event Logic Notes
System

Stock-Out Product Variant – Quantity On ❌ Not No historical

❌ Odoo
Rate Odoo Hand available tracking

Stock-Out Stock On Hand – Inventoried ❌ Not

❌ Odoo Snapshot only
Rate Odoo Quantity available

Stock-Out Inventory Export – ❌ Not

❌ Shopify Available Snapshot only
Rate Shopify available

Step 10.5 — Business-safe conclusion

 Stock-Out Rate CANNOT be calculated with current data

 Reason:
o No event history
o No “went from >0 to 0” tracking
 Any attempt would be:
o Assumption-based
o Misleading for business

Step 10.6 — How this should appear in Excel

In the KPI mapping Excel:

 KPI is listed
 Source tables are listed
 Status clearly mentioned as:

“Not feasible – historical stock-out events not available”

This protects you during:

 Client review
 Future audits
 Scope discussions

TRANSFER KPI 1 — Total Transfers

KPI Definition (locked)

Total Transfers
Total Transfers=Number of Transfer Records\text{Total Transfers} = \text{Number of Transfer
Records}Total Transfers=Number of Transfer Records

Step 11.1 — What this KPI strictly needs

Only one thing:

 A table where each row represents a transfer

No quantities, no cost, no dates required.

Step 11.2 — Identify valid transfer tables

From the data you shared:

Source System Table Name Transfer Identifier

Odoo Stock Warehouse Transfer – Odoo Each row = one transfer

This table clearly represents warehouse-to-warehouse stock movement.

Step 11.3 — Excel-style KPI mapping (Total Transfers)

📊 KPI Mapping — Total Transfers

Source
KPI Name Source Table Transfer Identifier Notes
System

Total Stock Warehouse Transfer – Reference / Row Each row = one

Odoo
Transfers Odoo count transfer

Step 11.4 — Important documentation notes

1. Quantity does not matter for this KPI

2. Status is not filtered unless business later requests:
o Completed only
o Excluding cancelled
3. This KPI is clean and fully feasible
STEP 12 — Transfer KPI 2: Transfer Lead Time
KPI Definition (locked)

Transfer Lead Time

Average(Date Received−Date Requested)\text{Average}(\text{Date Received} - \text{Date

Requested})Average(Date Received−Date Requested)

Step 12.1 — What this KPI strictly needs

To calculate Transfer Lead Time, we must have two distinct timestamps per transfer:

1. Date Requested (when transfer was initiated)

2. Date Received / Completed (when transfer was completed)

Both must exist in the data and must be comparable.

Step 12.2 — Check available date columns in transfer table

From Stock Warehouse Transfer – Odoo, the columns you shared are:

 Date
 Status
 Reference
 Warehouses (Source, Destination, Secondary)
 Transfer Lines (Quantity, Product)

Critical observation (fact, not assumption):

❌ There is only one date column: Date

There is no explicit column for:

 Request Date
 Completion Date
 Done Date
 Received Date

Step 12.3 — Can Status help infer lead time?

You do have:
  Status

But:

 Status does not give time duration

 Without timestamps for each status change, lead time cannot be calculated

Using status alone would be an assumption, which we are avoiding.

Step 12.4 — Excel-style KPI mapping (Transfer Lead Time)

📊 KPI Mapping — Transfer Lead Time

Source Source Date

KPI Name Date Requested Notes
System Table Received

Transfer Lead Stock Warehouse Transfer ❌ Not Only one date

❌ Odoo
Time – Odoo available column

Step 12.5 — Business-safe conclusion

 Transfer Lead Time CANNOT be calculated with current data

 Reason:
o Missing either request date or completion date
 Any workaround would be:
o Assumption-based
o Risky for business discussion

Step 12.6 — How this must be documented in Excel

In the KPI mapping Excel:

 KPI listed as Transfer Lead Time

 Status marked as:

“Not feasible – request and completion dates not available”

This protects you during:

 Client review
 Future enhancement discussions
STEP 13 — Transfer KPI 3: Transfers by Location
KPI Definition (locked)

Transfers by Location

Count of transfers grouped by Location

This KPI is about distribution, not timing or quantity.

Step 13.1 — What this KPI strictly needs

To calculate Transfers by Location, we need:

1. A transfer record
2. A location dimension to group by

No dates, quantities, or costs are required.

Step 13.2 — Identify valid “Location” columns in transfer data

From Stock Warehouse Transfer – Odoo, the available columns are:

 Source Warehouse
 Destination Warehouse
 Secondary Warehouse

So we actually have multiple valid location perspectives.

Step 13.3 — Business-sensible interpretation (no assumptions, but

practical)

Using common sense in warehouse operations:

 Source Warehouse → where stock moved from

 Destination Warehouse → where stock moved to
 Secondary Warehouse → auxiliary / intermediate (if applicable)

All three are legitimate, but serve different questions.

Step 13.4 — Excel-style KPI mapping (Transfers by Location)

📊 KPI Mapping — Transfers by Location

Source Location Column

KPI Name Option Source Table Notes
System Used

Transfers by Option Stock Warehouse Source Outbound

Odoo
Location 1 Transfer – Odoo Warehouse transfers

Transfers by Option Stock Warehouse Destination

Odoo Inbound transfers
Location 2 Transfer – Odoo Warehouse

Transfers by Option Stock Warehouse Secondary If used

Odoo
Location 3 Transfer – Odoo Warehouse operationally

Step 13.5 — Important documentation notes

1. Do not merge these locations

o Each tells a different operational story
2. Business can choose:
o Source-focused view (dispatch load)
o Destination-focused view (receiving load)
3. This KPI is fully feasible with existing data

STEP 14 — Transfer KPI 4: Stock Replenishment Rate

KPI Definition (locked)

Stock Replenishment Rate (%)

(Number of Successful Replenishments÷Total Replenishment Requests)×100(\text{Number of

Successful Replenishments} \div \text{Total Replenishment Requests}) \times
100(Number of Successful Replenishments÷Total Replenishment Requests)×100

Step 14.1 — What this KPI strictly needs

To calculate this KPI without assumptions, we must clearly identify:

1. Replenishment Request
 2. Successful Replenishment
3. A status or indicator that distinguishes success vs failure

Step 14.2 — Check available columns in transfer data

From Stock Warehouse Transfer – Odoo, we have:

 Status
 Source Warehouse
 Destination Warehouse
 Reference
 Date
 Transfer line quantities

So:

 Replenishment requests = transfer records ✔

 Status exists ✔

Step 14.3 — Can “Successful Replenishment” be identified?

This depends on Status values.

However:

 You have not shared the list of Status values

 We do not know:
o Which status means completed
o Which status means cancelled
o Which status means in progress

Without this mapping, success cannot be reliably identified.

Step 14.4 — Excel-style KPI mapping (Stock Replenishment Rate)

📊 KPI Mapping — Stock Replenishment Rate

Source Source
KPI Name Success Indicator Total Requests Notes
System Table

Stock Stock Warehouse ❌ Status mapping Status values

❌ Odoo
Replenishment Rate Transfer – Odoo missing not defined
Step 14.5 — Business-safe conclusion

 Stock Replenishment Rate CANNOT be calculated yet

 Reason:
o “Successful” vs “Unsuccessful” replenishment is undefined
 Any assumption here would be:
o Arbitrary
o Misleading to business users

Step 14.6 — How this should be documented in Excel

In the KPI mapping Excel:

 KPI listed
 Source table listed
 Status clearly mentioned as:

“Not feasible – success status definition not available”

This is the correct consulting approach.

✅ TRANSFERS KPIs — COMPLETE

✅ ALL KPIs — COMPLETE

You have now:

 Evaluated all Sales KPIs

 Evaluated all Inventory KPIs
 Evaluated all Transfer KPIs

With:

 Dual-option sourcing
 Clear feasibility flags
 Zero hidden assumptions
 Client-safe documentation logic