CASE STUDY · FOOTWEAR BRAND · UNITED STATES

Margin and inventory a footwear brand could finally act on

Everything the business needed was already in NetSuite. It just could not be analysed there. I built reporting that answers what each style actually earns and what the inventory is really worth — starting inside a data model I was not allowed to change, and finishing with one I designed.

reports covering margin, inventory and performance

SKU

level visibility, down to style, colour and size

star schema built from NetSuite transactions

delivery phases — constrained, then rebuilt with a mandate

None

manual extract-and-calculate cycles left

Built with NetSuite · Power BI · Star schema modelling · Advanced DAX · Calculation groups · Retail 4-5-4 calendar · Scheduled refresh

Role Lead — solution architecture, data modelling, margin and inventory logic in DAX, report design, and building the case that earned the mandate to rebuild the model.

PROJECT AT A GLANCE

Business problem I solved

A margin figure nobody would stand behindOne agreed, documented definition

Inventory that was a week stale on arrivalInventory that reflects today

A manual extract-and-calculate cycleScheduled, always-on refresh

Aggregate reporting hiding broken size runsSKU-level visibility by style, colour, size

Technology stack

Industry

Footwear retail — margin and inventory analytics

Location

United States

Project Type

Retail analytics platform built directly from NetSuite — margin, inventory and performance reporting

My Role

Lead — solution architecture, data modelling, margin and inventory logic in DAX, report design, and building the case that earned the mandate to rebuild the model.

Technology

NetSuite · Power BI · Star schema modelling · Advanced DAX · Calculation groups · Retail 4-5-4 calendar · Scheduled refresh

Scale

3 reports — margin performance, inventory health, sales and product performance — on one star schema, at SKU-level detail

Users

Merchandising, planning, finance and leadership

SECTION 1 · WHAT BUSINESS PROBLEM I SOLVED

Two questions, and neither could be fully trusted

A footwear brand lives or dies on two questions: what is each style actually earning, and what is sitting in the warehouse that will not sell at full price. Both answers existed somewhere. Neither could be produced quickly, and neither could be fully trusted. NetSuite held every order, shipment, receipt, return and adjustment. But an ERP records transactions; it does not organise them for analysis. Getting from what the system stored to what the merchandising and finance teams needed meant exporting data and rebuilding the calculation by hand, every time.

Margin was a number nobody would fully stand behind

Gross margin was calculated in spreadsheets, and the answer depended on who built it. Whether landed cost included freight and duty, whether returns and markdowns were netted off, whether allowances were counted — each choice changed the result, and none were written down.

Inventory reporting was out of date the moment it was produced

Stock positions were assembled manually, so by the time anyone reviewed them the picture had moved. Decisions about markdown and reorder were being made against a photograph of last week.

Every question restarted the work

The extract-and-calculate cycle had to run again for each new question. That put a hard ceiling on how many questions got asked, and the ones that did not get asked were often the most valuable.

Decisions were made at the wrong level of detail

Footwear does not sell as a style. It sells as a style, colour and size combination, and a style that looks healthy in total can be hiding broken size runs that will never sell without markdown. Aggregate reporting hid exactly the problem the business needed to see.

The reporting could not connect cost, price and outcome

What a shoe cost to land, what it was intended to sell for, what it actually sold for, and what came back as a return were held in different places. Without joining them, nobody could say which styles genuinely earned their margin and which only appeared to.

What I was actually asked to deliver

Reporting the merchandising and finance teams could act on without preparing it first — margin that is defined once and defensible, inventory that reflects today, and enough granularity to see the size and colour level where the money is actually won or lost.

SECTION 2 · WHICH TECHNOLOGY I USED

A deliberately small stack

The value in this project was not in the tools — it was in the modelling and the calculation logic, which is where retail reporting usually goes wrong.

Layer	Technology	Why it is there
Source	NetSuite	The ERP and system of record. Sales orders, invoices, fulfilments, receipts, purchase orders, returns, credits and inventory adjustments all originate here.
Staging	Microsoft Azure, Azure SQL	NetSuite transactions extracted and staged in Azure SQL, giving the rebuild a proper, queryable structure to model from before anything reaches Power BI.
Phase one	Existing live NetSuite data model	What I was given at the start. Read access only, no ability to change the structure. Everything in the first delivery had to be achieved inside those constraints.
Phase two	Purpose-built star schema	Once the client granted the mandate, NetSuite's transactional structure was modelled properly into facts and dimensions designed for analysis rather than for recording.
Data model	Power BI import mode	Chosen for query speed at SKU-level detail. Merchandising analysis means slicing large volumes across many attributes, and that has to feel instant or people stop doing it.
Business logic	Advanced DAX	Margin, inventory position, cohort and period-over-period logic written once as named measures, so every report uses the same definitions.
Time intelligence	Retail 4-5-4 calendar	Retail compares like weeks, not like dates. A retail calendar dimension makes prior-year comparison meaningful rather than misleading.
Reusability	Calculation groups	Period-over-period, year-to-date and prior-year variants applied across every measure without rewriting each one.
Validation	Excel	Used to reconcile the new margin and inventory outputs against the finance team's existing manual figures before phase two went live.
Refresh	Scheduled refresh	Reporting updates on a schedule, so the extract-and-calculate cycle disappears entirely from the team's workload.

No lakehouse, no orchestration platform, no additional licensing. Adding infrastructure would have added cost and delay without improving the answer — the problem was structural, not one of capacity.

SECTION 3 · HOW I BUILT IT — THE METHOD

Two phases, and the first is why the second happened

This project ran in two distinct phases, and the first one was the reason the second was possible. It is worth describing both, because working inside someone else's constraints is a large part of consulting that rarely gets written about.

Figure 1. Stages 1 and 2 are the ones most projects skip. Asking for control of the data model on day one is how consultants get refused; earning it is how they get it.

Working without the keys

I began with read-only access to an existing live data model I could not alter. That is a real constraint, and the wrong response is to refuse to start. I built what could be built inside it, and delivered working reporting that the business used.

While doing that, I documented precisely what the structure was preventing — which calculations could not be made correctly, which analysis was impossible at the required grain, and what that was costing in decisions the business could not make. That documentation was not a complaint. It was a business case, backed by evidence from work I had already delivered. The client granted the mandate to design the model properly, and phase two rebuilt the foundation.

Figure 2. Clients grant control over their data when they have already seen you deliver without it.

How the data travels

The chain is short by design. Fewer steps between NetSuite and the report means fewer places where the reported margin can drift away from the ledger it came from.

Figure 3. Every report reads one model, so margin on the inventory report and margin on the sales report cannot disagree.

Turning an ERP into something analysable

NetSuite stores transactions the way an accounting system needs to: one broad transactional structure holding many different document types together, each line meaning something different depending on what kind of document it belongs to. A sales order line, a fulfilment line, a receipt line, a credit memo line and an inventory adjustment line all live in the same shape while representing entirely different business events.

That is correct for an ERP and unusable for analysis. Modelling it meant separating those events into distinct facts at their own declared grain, then connecting them to shared dimensions so a single filter behaves consistently across all of them.

Figure 4. The ERP structure is not wrong. It is built for recording transactions, and analysis needs a structure built for asking questions.

Where the margin actually goes

Most retail reporting compares revenue against cost and calls the difference margin. That skips everything interesting. The gap between what a shoe was priced at and what it finally earned is made up of a series of deductions, and each one is a different business decision with a different owner.

Figure 5. Landed cost is where retail margin reporting most often goes wrong. Excluding freight and duty on imported footwear overstates margin on every single style.

The retail measures built into the model

Each of these is a standard retail concept. Each also has a modelling decision behind it, and getting that decision wrong produces a number that looks plausible and is not.

Measure	What it tells the business	The modelling decision behind it
Landed cost	What a unit truly cost to have available to sell — product cost plus freight, duty and import charges.	Allocating non-product costs down to unit level so margin is honest. Using invoice cost alone overstates margin on everything imported.
Gross margin, value and rate	What each style, colour and size earns after all deductions.	Margin rate must be recalculated from its components at every level, never averaged up from below — averaging margin percentages produces a wrong answer at every subtotal.
Sell-through	What proportion of the units received has actually sold. The clearest early signal of whether a buy was right.	Requires receipts and sales as separate facts joined on product and time, rather than inferred from stock movement.
Weeks of supply	How long current stock will last at the recent rate of sale — the number that drives markdown and reorder timing.	Sensitive to the rate-of-sale window chosen. Defined once and applied consistently, rather than each analyst picking their own.
Inventory turns and GMROI	How hard the inventory investment is working, and what each dollar tied up in stock returns in margin.	Needs average inventory across the period, not a closing snapshot. A point-in-time figure distorts both measures.
Aged inventory	How long stock has been sitting, bucketed — the leading indicator of future markdown exposure.	Ageing is measured from receipt, which means retaining receipt history rather than only the current position.
Size curve health	Whether a style still has a saleable size run, or has broken into sizes nobody wants.	Only visible if product is modelled to size level. Aggregated to style, the problem is invisible until markdown.
Cohort analysis	How a style performs in the weeks after launch, compared with previous launches at the same point in their life.	Needs a launch date per product and time measured relative to it, not on the calendar — so a style launched in March compares fairly with one launched in September.
Period-over-period	How the business is trading against last year, last quarter and the prior period.	Retail compares like weeks, not like dates. A 4-5-4 retail calendar means prior-year comparison lines up trading weeks and holiday timing correctly.
Channel margin	What the same product earns through different routes to market, which is rarely the same figure.	Channel as a conformed dimension across sales, returns and allowances, so the comparison includes the deductions, not just the revenue.

Following the inventory, not just counting it

Inventory reporting is usually a single number: what is on hand today. That answers almost nothing. What the business needed was the whole flow — what is committed, what is arriving, what is sitting, and how fast it is moving — because a markdown decision depends on all four.

Figure 6. Markdown is the most expensive decision in retail and the most time-sensitive. Seeing the flow early is what turns it from a write-off into a planned action.

SECTION 4 · WHAT THE BUSINESS GOT OUT OF IT

The point was never to produce three reports

It was to change what the merchandising and finance teams could see, and how quickly they could act on it.

Margin became a number the business trusts

One definition, built from landed cost and every deduction between list price and net sales, applied identically everywhere. Meetings stopped opening with a debate about whose margin figure was right.

Outcome · one defensible margin, agreed and documented

The manual extract-and-calculate cycle disappeared

Reporting refreshes on a schedule. The recurring work of exporting from NetSuite and rebuilding calculations by hand is gone, and with it the delay and the risk of a different answer each time.

Outcome · recurring manual effort removed from the cycle

Inventory problems surface while they are still fixable

Ageing stock, falling sell-through, weeks of supply drifting out and size runs breaking are all visible early. Markdown becomes a planned decision taken at the right moment rather than a write-off recognised too late.

Outcome · earlier markdown decisions, less margin given away

Detail where the money actually is

Analysis runs at style, colour and size. A style that performs well in total but is breaking at the edges of its size run is now visible as a problem, not as a success.

Outcome · SKU-level visibility instead of misleading averages

New products can be judged early and fairly

Cohort analysis compares a launch against previous launches at the same point in their life rather than against the calendar, so a style's trajectory is readable within weeks — early enough to reorder, reprice or stop.

Outcome · faster, better-evidenced product decisions

Comparisons that mean something

Trading performance is compared on a retail calendar, so prior-year comparisons line up trading weeks and holiday timing instead of calendar dates. It is a small technical point with a large effect on whether a comparison can be believed.

Outcome · period-over-period analysis that holds up

A foundation that outlasted the engagement

The star schema built in phase two is designed to be extended. Adding a fourth report, a new channel or a new dimension is routine work rather than a rebuild — which is the difference between a deliverable and an asset.

Outcome · reporting that can keep growing with the business

SECTION 5 · SUMMARY

In business terms, and in technical terms

In business terms

A footwear brand can now see what each style, colour and size actually earns after markdowns, returns, allowances and landed cost.
The manual cycle of exporting from NetSuite and rebuilding calculations in spreadsheets was removed entirely.
Inventory is followed as a flow — on order, in transit, on hand, sold — so markdown decisions are taken early rather than recognised late.
New product launches are judged against previous launches at the same point in their life, not against the calendar.
Trading comparisons run on a retail calendar, so prior-year figures compare like with like.
Delivery began under read-only access to an existing model, and the reporting delivered in that phase earned the mandate to rebuild the foundation properly.

In technical terms

NetSuite as the source of record — orders, invoices, fulfilments, receipts, returns, credits and inventory adjustments.
Phase one delivered against an existing live data model with read-only access and no structural control.
Phase two remodelled NetSuite transactions into a star schema, separating events into distinct facts at declared grain.
Conformed dimensions for product to style/colour/size, customer, channel, location, season and retail calendar.
Power BI import mode for query speed at SKU-level detail across large volumes.
Advanced DAX for landed cost allocation, margin at every level, sell-through, weeks of supply, turns, GMROI and ageing.
Cohort measures indexed to launch date rather than calendar date.
4-5-4 retail calendar dimension driving all period-over-period and prior-year comparison.
Calculation groups applying time variants across every measure without duplication.
Scheduled refresh replacing the manual extraction cycle.

My role. I led the engagement end to end — delivering within the client's existing constraints, building the evidence that justified redesigning the data model, then architecting the star schema, writing the margin and inventory logic in DAX, designing the reports, and validating the output back to NetSuite.

What this project demonstrates. Two things. That I can model an ERP's transactional structure into something a merchandising team can actually use, and that I can work productively inside a client's constraints until the case for changing them is proven rather than asserted. The second is usually what determines whether the first ever gets the chance to happen.

NEXT PROJECT

Enterprise Embedded Analytics Platform

Canada · Auto Glass Industry

View project