Case Study
1. Project overview
An e-commerce business ships from four warehouses (Adama, Addis Ababa, Bahir Dar, and Hawassa) and buys from twelve suppliers. After cleaning the data in Power Query and building a Power BI model with DAX, a four-page dashboard answers five operational questions.
2. Business problem
Leadership wants to cut stockouts and excess inventory without hurting profit or fulfillment. These goals pull against each other: more stock protects sales but ties up cash, and less stock frees cash but risks lost orders.
The work is operational diagnostics (what is wrong and where), not forecasting.
3. Project objectives
Answer five operational questions: which products need replenishment, which are overstocked, how much stock value is tied up, which warehouses show high cancellation and return rates, and which suppliers pose risk.
Deliverables: cleaned data and a Power BI model (Power Query and DAX), a four-page dashboard, five evidence-based insights, and three recommendations.
4. Dataset overview
The workbook contains four tables:
OrderLines_Raw: 901 rows (898 after cleaning), one row per order line (sales facts).
Inventory_Snapshot: 60 rows, one row per product on 2025-12-31 (stock facts).
Products: 60 rows, one row per product (the hub dimension).
Suppliers: 12 rows, one row per supplier (dimension).
5. Data quality issues
Duplicate and invalid rows: one exact duplicate order line (LINE00016) and one line with an invalid ProductID (P999).
Wrong or missing values: a negative UnitPrice (-12.50 on product P006), a missing Quantity (LINE00061), a missing DiscountRate (LINE00046), and an invalid ReturnedFlag value of 2.
Inconsistent text in the Warehouse and FulfillmentStatus fields.
Conflicting definitions: three different lead-time fields, and a ReorderPoint below SafetyStock for 12 products.
6. Data cleaning and transformation
Every change was made in Power Query, with the raw table left untouched. Corrected values are flagged in the data, so a reviewer can filter them out.
Removed: the duplicate line (it would double count sales), the line with invalid ProductID P999 (no category, supplier, or stock record to link to), and the line with missing Quantity (it cannot be inferred, and imputing would invent values).
Corrected and flagged: the negative price was replaced with the master price of 81.50 (all other lines matched their master price); the missing discount was set to 0%, the most common rate; the invalid ReturnedFlag was recoded to 0, because the status was Completed and all returned lines carry flag 1.
Standardized: text in Warehouse and FulfillmentStatus was trimmed and capitalization fixed, merging duplicate categories.
Resolved definitions: product lead time is used for replenishment, the supplier average on the supplier view, and the snapshot copy is hidden. The effective reorder point is the higher of ReorderPoint and SafetyStock, since a trigger below the buffer would under-flag low stock.
7. Data model and analytical approach
Products is the hub of the model. Order lines and the inventory snapshot connect only through ProductID, so stock totals are never multiplied by sales rows.
Stock status: Reorder Now means on hand plus incoming is at or below the effective reorder point. Low - Inbound means on hand is at or below it, but incoming stock lifts it above. Overstock means on hand is above 5x the effective reorder point. Healthy covers all others.
Supplier risk: High means reliability below 0.80 and an average lead time of 13+ days. Medium means either one. Low means neither.
8. KPIs and metrics
Headline numbers: Net Sales $190,938; Gross Profit $73,047 (38.3% margin); 17.9% of booked sales lost to cancellations and returns; overstock value $95,000 (12 products); inventory value $306,274 (4,348 units, 60 products); $99,603 of stock held by 3 high-risk suppliers.
Rates: cancellation rate 8.8% (79 of 898 lines) and return rate 7.9% (65 of 819 delivered lines).
Definitions: Net Sales is booked sales after discount on Completed and Shipped lines (Cancelled and Returned excluded). Gross Profit is Net Sales minus unit cost x quantity on the same lines, an estimate with no shipping or handling costs. Cancellation rate is cancelled lines divided by all order lines. Return rate is returned lines divided by non-cancelled lines.
9. Dashboard design
Executive Overview: how the business is performing, with sales, profit, and cancellation and return rates by month, category, warehouse, and product.
Inventory Analysis: what needs ordering, what is overstocked, and where cash is tied up. Status colors flag urgency.
Supplier Analysis: where revenue leaks by warehouse, and which suppliers carry risk.
Findings & Recommendations: the five insights and three recommendations next to the headline numbers.
10. Key findings
1. 17.9% of booked sales never becomes revenue. Of $232.6K booked, $23.6K was cancelled (10.2%) and $18.0K was returned (7.7%), leaving $190.9K in net sales.
2. Electronics carries the business. It earns $74.1K, about 39% of net sales, at the best margin (40.2%, against 34% to 38% elsewhere). All five top-selling products are Electronics, which is a concentration risk as well as a strength.
3. Overstock ties up 31% of stock value. Twelve products hold $95.0K of the $306.3K inventory. The five largest hold $69.8K, led by Electronics Product 051 with $30.5K and 272 weeks of cover. At the 2025 sales pace, total stock covers about 121 weeks.
4. Stock-out risk is small, but sits with a risky supplier. Only 8 of 60 products are low, and five of those have stock incoming. Three have nothing incoming: Fashion 003, Electronics 047, and Home 045. Two of the three come from Supplier 10, rated High risk (reliability 0.75, 13-day average lead time), though their cover is 12 to 28 weeks. The three High-risk suppliers hold $99.6K, or 32.5% of stock.
5. Warehouse differences are modest. Addis Ababa has the highest cancellation rate (10.1%) against 7.8% at Adama. Hawassa has the highest return rate (9.0%). Only about 6 lines separate the best and worst warehouse, so this is a signal to monitor, not proof of a problem. Products from High-risk suppliers cancel more often (9.5% of lines) than those from Low-risk suppliers (7.1%), on a modest sample.
11. Business recommendations
Priority 1, immediate (Purchasing): order the three products with nothing incoming. Start with Supplier 10's two items, ask for confirmed delivery dates, and line up a backup supplier. Track the Low Stock Products count.
Priority 2, next (Inventory planning): stop reordering overstock and run it down, starting with the five largest overstocked products ($69.8K). Set a stock ceiling (the dashboard uses 5x the reorder point) so overstock does not rebuild. Track Overstock Value.
Priority 3, ongoing (Operations): find out why orders are cancelled and returned by recording a reason for every cancellation and return. Review Addis Ababa cancellations and Hawassa returns first, and add supplier performance to the review. Track the cancellation and return rates.
12. Limitations
One inventory snapshot: there is no stock history or demand variability, so the analysis shows position, not a stockout forecast. Weeks of cover use 2025 sales and are indicative only.
OrderID is not a reliable order header: many orders span several dates, warehouses, and statuses, so all rates are measured on order lines.
No warehouse on inventory: stock cannot be analyzed by warehouse, and inventory figures ignore the Warehouse and Date slicers.
Missing detail: there are no reason codes for cancellations or returns, and profit is an estimate based on unit cost only. Supplier reliability is a given score, and the status and risk thresholds are analyst judgments that can be changed.
Small samples and synthetic data: each warehouse has about 220 lines and about 20 cancellations, so warehouse differences are fragile. The data is for practice and portfolio use, and the text on the Findings page is static and does not update on refresh.
13. Tools used
Microsoft Excel, Power BI Desktop, Power Query, and DAX, with a four-page dashboard.
14. Screenshots
See the four dashboard screenshots above.
15. Conclusion
Replenishment is a small, specific problem: 3 products need an order now, and 5 more are covered by incoming stock. Overstock is the larger problem, with 12 products holding $95,000, or 31% of all stock value. Revenue leakage is material, since cancellations and returns remove 17.9% of booked sales. Warehouse differences are modest, and supplier risk is concentrated in three suppliers that hold about a third of the stock.