Batch & Brew: Sales Performance Review & Data Integrity
Category:
Data Analysis, Data Cleaning, Business Intelligence, Excel
Client:
Fictional Client: Batch & Brew (UK Cafe)
The Challenge
The client, Batch & Brew, a growing single-location cafe in London, needed a clear path for expansion but lacked reliable data. The daily sales records from their POS system were heavily corrupted and inconsistent. The core problem was a major data integrity failure: nearly 40% of their total revenue was functionally invisible. Key fields like Sales Channel and Payment Method were full of blanks and errors, making it impossible for the owner to calculate Net Profit or decide where to invest (Takeaway vs. In-store).
The Solution
I executed a rigorous, 12-step Data Integrity Audit in Excel, prioritizing data preservation over deletion.
Imputation over Deletion: I used algebraic imputation (Quantity = Total / Price) to successfully calculate and save thousands of transaction records that would have otherwise been lost.
Standardization: I standardized all blanks and text errors into a single, measurable category:
UNKNOWN/MISSING. This allowed us to quantify the exact size of the 40% blind spot.Documentation: Every step was recorded in a Data Transformation Log to provide a clear audit trail for future analysts.
The process resulted in a clean dataset with a verified total revenue of £76,605.50, ready for accurate analysis.
The Result
The clean data immediately delivered several high-value, actionable insights:
Identified Financial Risk: The unclassified data created a Cost Blind Spot, preventing the accurate calculation of Net Profit due to unknown credit card transaction fees.
Revenue Drivers: Confirmed that Salad and Sandwich items dominate sales, informing inventory strategy.
Customer Value: Analysis confirmed the In-store channel has a higher Average Order Value (£9.12) than Takeaway, justifying investment in the dining area.
Final Recommendation: The top priority was correcting the POS system to make 'Sales Channel' and 'Payment Method' mandatory, thereby eliminating the 40% data blind spot and enabling reliable future decision-making.
Click Any of The Deliverables Below To View
