Excel for Retail and E-commerce: SKU Sales, Returns, Inventory and Promo Trackers (With Templates)
This guide is for people in the retail industry who use Excel every day. You will see every common file, the formulas people actually type, and downloadable templates with made-up Indian ₹ data. Related short lessons are linked — we do not re-teach them here.
What you'll learn
- Which roles use Excel in retail
- Every common Excel file type for this industry
- Sample tables with realistic ₹ examples (made-up names)
- Formulas: ASP, Returns %, WOS, Margin % and more
- Ready templates you can download and reuse
Downloads: retail-ecommerce-sku-inventory.xlsx (SKU sales + returns + inventory WOS + promo + ASP margin)
Example data only
All names, amounts, rates and dates are made up for practice. They are not real client or employer records. Follow your organisation's own rules and software for live work.
In short
One industry → many Excel files. Each file is one clear list. Yellow cells are inputs; green cells are formulas. Start with the Roles table, then open the matching template.
थोडक्यात
एक industry → अनेक Excel files. प्रत्येक file = एक स्पष्ट यादी. Yellow = input; green = formula. Roles table पासून सुरू करा, नंतर matching template उघडा.
संक्षेप में
एक industry → कई Excel files. हर file = एक साफ सूची. Yellow = input; green = formula. Roles table से शुरू करें, फिर matching template खोलें.
Related chapters (already on this site)
Why
Some topics already have a focused lesson. Use those for depth; use this guide for the full industry picture.
What
| Chapter | What it covers |
|---|---|
| Sales Dashboard with PivotTables | Slicers on sales. |
| Excel for Sales (profession) | Leads and targets. |
| Excel Dashboards gallery | Brand dashboards. |
How
- Skim the link if you need that one skill.
- Come back here for the full file list and templates.
1. Who uses Excel
Why
Platform dashboards help; category reviews still use Excel.
What
| Role | Typical Excel work |
|---|---|
| Store manager | Daily sales, cash, stock. |
| E-com ops | Orders, returns, channel GMV. |
| Category merchant | SKU performance, promo calendar. |
| Inventory planner | WOS, reorder, dead stock. |
How
- Download retail-ecommerce-sku-inventory.xlsx.
2. Every common Excel file
Why
If you only learn one dashboard, you miss the daily registers that feed it.
What
| File | What one row / sheet holds |
|---|---|
| Daily SKU sales | Date, SKU, channel, qty, GMV, discount. |
| Returns register | Order, SKU, reason, refund ₹. |
| Inventory by location | SKU, store/warehouse, on hand, WOS. |
| Promo tracker | Promo code, lift %, spend, incremental ₹. |
| ASP and margin | GMV, units, ASP, COGS, margin %. |
How
- Pick the file that matches your role.
- Download the template that contains that sheet.
- Replace yellow sample rows with your practice data (still made-up if you share screenshots).
3. Formulas people use
Why
Typed numbers go stale. Formulas update when inputs change.
What
| Name | Formula | What it does |
|---|---|---|
| ASP | =GMV/units |
Average selling price. |
| Returns % | =return_units/sold_units |
Return rate. |
| WOS | =on_hand/avg_weekly_sales |
Weeks of cover. |
| Margin % | =(GMV-COGS)/GMV |
Sell margin. |
| Promo lift % | =(promo_sales-baseline)/baseline |
Lift vs baseline. |
| Dead stock flag | =IF(WOS>12,"Dead risk","OK") |
Teaching slow stock. |
How
- Open the Formulas used sheet inside each template.
- Change only yellow cells.
- Watch green cells recalculate.
3. Sales and returns
Why
GMV without returns overstates health.
What
Daily SKU sales + returns %.
How
- Enter qty and GMV.
- ASP green.
- Returns % = Return units ÷ Sold units.
Figure. ASP and margin
4. Inventory and promo
Why
Too much WOS ties cash; too little loses sales.
What
WOS and dead-risk flag; promo lift.
How
- WOS = On hand ÷ Avg weekly sales.
- Filter Dead risk.
- Promo lift compares promo week to baseline.
5. Sheet previews
Why
See the layout before you open Excel.
What
Screenshots of the main sheets in the downloadable templates.
How
Scroll the figures, then download the matching .xlsx.
Figure. Inventory WOS
Figure. ASP and margin
6. Common mistakes
| Mistake | What goes wrong | Fix |
|---|---|---|
| Ignoring channel returns | Marketplace looks more profitable than it is. | Keep Returns register. |
| WOS with one odd week | False dead stock. | Use 4-week average sales when possible. |
7. FAQ
GST in GMV?
Teaching GMV is gross of tax unless you label net — be consistent.
8. Try at home
Try at home
- ASP for SKU Tee-Blue?
- Which SKUs show Dead risk?
- Returns % for Order week sample?
Recap
In short
- 4 roles use Excel in this industry.
- 5 common file types are listed above.
- 1 downloadable templates with yellow inputs and green formulas.
- Rates and rules here are teaching examples only.
- Use Related chapters for focused skills.
Quick revision cards
Quick revision
1 interview card, 1 comparison card and 1 revision card. Save on your phone or download as PNG. Numbers are made up for practice.
झटपट उजळणी
1 interview card, 1 comparison card आणि 1 revision card. Phone वर save करा किंवा PNG download करा. आकडे सरावासाठी काल्पनिक आहेत.
झटपट दोहराई
1 interview card, 1 comparison card और 1 revision card. Phone पर save करें या PNG download करें. आंकड़े अभ्यास के लिए काल्पनिक हैं.
