Step 1 / 10

PROJECT OVERVIEW

Steps
  1. 01PROJECT OVERVIEW
  2. 02DATASET REQUIREMENTS
  3. 03PROJECT ROADMAP
  4. 04STEP-BY-STEP IMPLEMENTATION
  5. 05EXPECTED OUTPUT AFTER EVERY MAJOR STEP — SUMMARY TABLE
  6. 06VISUALIZATION AND DASHBOARD
  7. 07INSIGHTS AND RECOMMENDATIONS
  8. 08FINAL PROJECT DELIVERABLES
  9. 09FURTHER READING & REFERENCES
  10. 10FINAL PROJECT CHECKLIST

PROJECT OVERVIEW

Project Title: E-commerce Sales Dashboard (Excel)

Business Scenario: You are working as a Data Analyst for a UK-based online retailer that sells gift and homeware items to both individual shoppers and wholesale customers, worldwide. Leadership has one full year of transaction-level order data (December 2010 – December 2011, ~540,000 order lines) sitting in a raw export, but nobody has turned it into a usable sales report. The Commercial Director wants a single Excel workbook that shows real, trustworthy revenue — not a number inflated by cancelled orders or admin fees.

Company / Industry Context: Like most e-commerce operations, this business deals with real-world messiness: returned/cancelled orders, guest checkouts with no customer record, postage and bank-fee line items mixed into the sales ledger, and a small number of pricing errors. A dashboard built on the raw file without addressing these would overstate revenue and misrepresent customer behavior.

Business Problem: Nobody currently has a single trusted view of net revenue, which markets are actually driving growth, or how much of the business is anonymous “guest” checkout traffic versus known, repeat customers.

Project Objective: Build a cleaned, PivotTable-driven Excel dashboard that reports true net revenue (excluding cancellations and non-product entries), shows the sales trend across the year, and compares performance by country and by customer type.

Key Stakeholders: - Commercial Director (main sponsor — wants trustworthy net revenue, not a gross/inflated number) - Marketing (wants to know which countries and months to focus campaigns on) - Customer team (wants to understand how much revenue comes from guest vs. registered customers)

Final Business Goal: Give leadership one Excel workbook they can open monthly to see real net revenue, top products/markets, and the guest-vs-registered customer split — without re-doing the cleaning work each time.

What Will the Student Finally Build?

  1. A cleaned, dashboard-ready sales dataset (as an Excel Table)

  2. A documented cleaning process handling duplicates, cancellations, missing data, and non-product entries

  3. A set of PivotTables answering the core business questions

  4. An interactive Excel dashboard sheet with PivotCharts and slicers

  5. Business insights and recommendations

  6. A final summary/presentation of findings

Tools used in this project: Microsoft Excel (Tables, PivotTables, PivotCharts, Slicers, XLOOKUP/VLOOKUP, conditional formatting).