Retail · Beginner
Clean four years of order lines for a Kenyan office and home retailer, find where profit is made and lost, and build a profit dashboard in Excel.
Tausi Office & Home is a fictional Kenyan retailer that sells furniture, office supplies and technology to consumers, corporate buyers and home offices in five regions. Sales have grown every year since 2021, yet the finance director says margins feel thin, and some sales teams give big discounts to hit their targets. Before next year's discount policy is set, leadership wants to see where profit is really made and where it leaks away. You have one CSV file: every order line from January 2021 to December 2024, with prices in Kenyan shillings.
Your role
Junior Commercial Analyst, Tausi Office & Home
Leadership tracks sales but not profit by product, region or discount level. Nobody can say which product groups lose money, whether deep discounts buy profitable sales, or which regions deserve more support. The discount policy for next year depends on the answer.
Open retail-orders.csv, copy it to a sheet called Raw, turn it into an Excel Table and leave it untouched. Count the rows, then look for duplicate rows, blank cells and text written in different ways, and note each problem in a cleaning log. Checkpoint (before cleaning): the Raw table has 9,502 rows of data, not counting the header.
On a Clean sheet, remove the duplicate rows, make the segment names consistent (Consumer, Corporate, Home Office) and fill each blank town from the same customer's other orders. Then add formula columns: Order Year, Order Month, Ship Days (ship date minus order date), Profit Margin (profit divided by sales), Discount Band (No discount for 0, Low for up to 20%, Medium for 21% to 30%, High for over 30%) and Profit Flag (Profit or Loss). Checkpoint (after cleaning): 9,493 rows, and total sales of KES 386,738,802.
Synthetic data. Tausi Office & Home is fictional; the order lines were generated to follow the structure of the well-known Tableau "Sample - Superstore" dataset, re-set in Kenya with Kenyan customers, counties, towns and prices. One file, retail-orders.csv: 9,502 rows × 19 columns. One row is one order line (one product on one order), so an order with three products has three rows. Count distinct order_id for the number of orders. Orders run from 1 Jan 2021 to 31 Dec 2024. Money is in Kenyan shillings (KES), whole shillings. sales_kes is the line value after the discount; discount is a fraction (0.2 means 20% off); profit_kes is negative when the line lost money. Left for you to clean (on purpose): 9 rows appear twice (exact duplicates, same row_id); the segment column has inconsistent capitalisation on 46 rows (for example "consumer", "CORPORATE", "home office"); and the town is blank on 14 rows (the county is always filled, and the same customer's other orders show their town). Everything else is clean: dates are ISO (YYYY-MM-DD) and numbers are plain numbers.
How to do this project
The scenario
Objectives
Tasks and business questions
Dataset and data dictionaryretail_orders9,502 order lines (including 9 duplicates to remove) with dates, customer, location, product, sales, quantity, discount and profit in KES. · 9,502 rows
Build PivotTables on the Clean sheet for sales, profit and margin by year, category, sub-category, region and segment. Add a small KPI block with SUMIFS so the headline figures do not depend on a PivotTable.
Compare sales, profit and margin across your four discount bands. Then filter to the High band (over 30%) and see which sub-categories and regions it comes from. Rank products by total profit and list the bottom 10.
On a Dashboard sheet, add KPI cards (sales, profit, margin, orders, average discount), a monthly sales and profit trend, profit by sub-category, profit by region and margin by discount band, with slicers for year, region, segment and category connected to every PivotTable. Finish with three recommendations, each quoting a figure.
| Column | Type | Description | Example |
|---|---|---|---|
row_id | integer | Line number in the export. Should be unique; a repeated row_id marks a duplicate row. | 1 |
order_id | text | Order number. One order can have several rows, one per product. | TOH-2021-100001 |
order_date | date | Date the customer placed the order (YYYY-MM-DD). | 2021-01-01 |
ship_date | date | Date the order was dispatched. | 2021-01-05 |
ship_mode | text | Delivery option: Standard Class, Second Class, First Class or Same Day. | Standard Class |
customer_id | text | Unique customer code. | BN-10128 |
customer_name | text | Customer's name (fictional). | Barasa Nyongesa |
segment | text | Type of customer: Consumer, Corporate or Home Office. Some rows use different capitalisation and need fixing. | Corporate |
county | text | Customer's county. Always filled. | Uasin Gishu |
town | text | Customer's town. Blank on 14 rows. | Eldoret |
region | text | Sales region: Nairobi, Central, Coast, Rift Valley or Western. | Rift Valley |
product_id | text | Unique product code. The first part shows the category (FUR, OFF, TEC). | OFF-BI-1174 |
category | text | Furniture, Office Supplies or Technology. | Office Supplies |
sub_category | text | Product group within the category, for example Tables, Binders or Phones (17 in all). | Binders |
product_name | text | Product description (fictional brands). | Duma Spring File |
sales_kes | integer (KES) | Value of the line in shillings after the discount. | 4200 |
quantity | integer | Number of units on the line. | 2 |
discount | decimal (0 to 1) | Discount given on the line as a fraction: 0.2 means 20% off. Ranges from 0 to 0.7. | 0.2 |
profit_kes | integer (KES) | Profit on the line in shillings after cost and delivery. Negative means the line lost money. | 1139 |
The cleaned data matches the checkpoints in milestones 1 and 2; calculated columns use formulas, not typed values; discount bands are stated and applied consistently. The dashboard works with slicers and a manager can read it unaided, and every recommendation quotes a specific figure.
Recommended tools
Skills covered
Excel workbook (.xlsx) with Raw, Clean, Analysis and Dashboard sheets