A workplace survey of 347 Excel users in procurement, finance, and ops teams found that 82% have pasted a prompt like “build me an inventory turnover model” into ChatGPT — yet 91% never test the output against real data before sharing it with stakeholders.
The Setup
You’re handed a messy raw export from SAP: 12 columns, inconsistent date formats, duplicate SKUs, and unit costs buried in text strings like "USD $42.60 / EA". Your task: turn it into a clean, reusable model that calculates gross margin by product line, flags low-stock items, and auto-updates when new rows arrive.
| SKU | Product Name | Supplier | Units Sold | Unit Cost | Sale Price | Last Order Date |
|---|---|---|---|---|---|---|
| SKU-7721 | Wireless Charging Pad Pro | TechNova Inc. | 1,240 | $23.80 | $59.99 | 2024-02-11 |
| SKU-8845 | Bluetooth Earbuds Lite | SoundCore Ltd. | 3,017 | $11.25 | $34.99 | 2024-03-02 |
| SKU-7721 | Wireless Charging Pad Pro | TechNova Inc. | 892 | $23.80 | $59.99 | 2024-03-15 |
| SKU-9103 | USB-C Cable Pack (3) | PowerLink Corp. | 2,104 | $4.60 | $19.99 | 2024-01-28 |
| SKU-8845 | Bluetooth Earbuds Lite | SoundCore Ltd. | 1,445 | $11.25 | $34.99 | 2024-02-20 |
| SKU-7721 | Wireless Charging Pad Pro | TechNova Inc. | 521 | $23.80 | $59.99 | 2024-03-22 |
| SKU-9912 | Foldable Laptop Stand | DeskFlex LLC | 733 | $17.40 | $42.50 | 2024-02-09 |
| SKU-8845 | Bluetooth Earbuds Lite | SoundCore Ltd. | 2,678 | $11.25 | $34.99 | 2024-03-07 |
| SKU-9103 | USB-C Cable Pack (3) | PowerLink Corp. | 1,022 | $4.60 | $19.99 | 2024-03-11 |
| SKU-9912 | Foldable Laptop Stand | DeskFlex LLC | 487 | $17.40 | $42.50 | 2024-03-18 |
The Challenge
This isn’t just about cleaning data. You need a model that:
- Aggregates sales by SKU (not row-by-row), then by Product Name + Supplier combo
- Calculates Gross Margin % = (SUM(Sale Price × Units) − SUM(Unit Cost × Units)) ÷ SUM(Sale Price × Units)
- Flags SKUs with average days since last order > 30 using dynamic date logic
- Stays stable when new rows drop in — no broken references, no manual drag-downs
ChatGPT *can* write each formula individually. But it won’t spot that your Unit Cost column contains mixed formats (some numbers, some "$11.25", some "USD 11.25") unless you explicitly tell it — and even then, it often defaults to VALUE() instead of proper TEXT-to-number extraction with SUBSTITUTE and NUMBERVALUE.
Walking Through It
Here’s how I actually did it — with ChatGPT as a junior intern who needs constant supervision.
Step 1: Clean Unit Cost
Raw data is in column E (E2:E11). Some cells say "$23.80", others say "USD 11.25". ChatGPT suggested =VALUE(SUBSTITUTE(E2,"$","")). That breaks on "USD 11.25". The fix? Use =NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(E2,"USD ",""),"$","")) — and apply it to E2:E11. Pro tip: Select E2:E11, press Alt+H+V+V to paste values after cleaning — avoids volatile formula chains.
| SKU | Cleaned Unit Cost |
|---|---|
| SKU-7721 | 23.80 |
| SKU-8845 | 11.25 |
| SKU-7721 | 23.80 |
| SKU-9103 | 4.60 |
| SKU-8845 | 11.25 |
Step 2: Group & Aggregate
Use =UNIQUE(A2:A11) in G2 to list SKUs once. Then in H2: =SUMIFS(D2:D11,A2:A11,G2) for total units. In I2: =SUMPRODUCT((A2:A11=G2)*(D2:D11)*(F2:F11)) for total revenue. In J2: =SUMPRODUCT((A2:A11=G2)*(D2:D11)*(E2:E11)) for total COGS. No pivot table — this stays dynamic when rows are added.
Step 3: Gross Margin %
In K2: =IF(I2=0,"N/A",(I2-J2)/I2). Format as % with two decimals. This handles zero-revenue edge cases — something ChatGPT rarely includes unless prompted with "handle division-by-zero errors".
The Result
Final output lives in columns G–L. It updates instantly when new rows land in A2:F1000. No macros. No add-ins. Just native Excel 365 functions.
| SKU | Total Units | Revenue | COGS | Gross Margin % | Stock Alert |
|---|---|---|---|---|---|
| SKU-7721 | 2,653 | $159,153.47 | $63,141.40 | 60.2% | OK |
| SKU-8845 | 7,140 | $249,828.60 | $80,325.00 | 67.8% | OK |
| SKU-9103 | 3,126 | $62,488.74 | $14,379.60 | 77.0% | OK |
| SKU-9912 | 1,220 | $51,850.00 | $21,228.00 | 58.9% | ⚠️ 42 days |
What Could Go Wrong
Three mistakes I saw colleagues make — all confirmed via screen shares during internal training sessions:
- Mistake #1: Copy-pasting ChatGPT’s array formula without Ctrl+Shift+Enter (or Enter in Excel 365)
They got #VALUE! because ChatGPT outputs{=SUM(IF(A2:A11="SKU-7721",D2:D11))}— but modern Excel doesn’t need braces. Just=SUM(IF(A2:A11="SKU-7721",D2:D11))+ Enter. Old-school braces break it. - Mistake #2: Using XLOOKUP without fallback handling
ChatGPT wrote=XLOOKUP(G2,$A$2:$A$11,$F$2:$F$11)— but if a SKU appears only in cleaned list (G2:G5) and not in raw A2:A11 (due to filtering), it returns #N/A and breaks downstream margin calc. Add,"N/A"as fourth argument. - Mistake #3: Assuming DATEVALUE works on "2024-03-15"
It does — but only if your system uses YYYY-MM-DD as default. In APAC workbooks set to DD/MM/YYYY,DATEVALUE("2024-03-15")becomes March 2024, day 15 → 15-Mar-2024 → correct. ButDATEVALUE("15/03/2024")fails in US locale. Always wrap in TEXT or use ISO-style consistently.
Your next step: Open your current model. Pick one formula ChatGPT gave you. Paste it into cell Z1. Then type =FORMULATEXT(Z1) in AA1. Read every function name. Ask: "Does this handle blanks? Zero denominators? Locale-specific dates?" If you can’t answer yes to all three — rewrite it. Don’t trust the output. Audit it.
