All Projects
Finance
Excel P&L Dashboard for Sandwich Shop
Automated P&L dashboard pulling POS and expense data into a real-time Excel dashboard for a sandwich shop owner.
Financial visibility
Real-time P&L
Tools
Google SheetsPOS APIn8n
The Client's Problem
My Approach
The Workflow Breakdown
1
Power Query M code normalizing 6 inconsistent store report formats into unified schema 2. 23-point validation: date match, row totals, control sums, labor and food cost sanity checks 3. 4-week rolling average food cost trending with red-flag threshold at 35% 4. Variance analysis against budget and prior year with drill-down by location 5. VBA macro generating 6 PDF scorecards and emailing each manager via Outlook
Results & Impact
- Weekly consolidation time dropped from 4 hours (manual) to 15 minutes (one click)
- Data entry errors went from regular (e.g. $14K masked waste) to zero since deployment
- Report delivery moved from Monday afternoon to Monday 7am automatic
- Food cost visibility went from monthly (delayed) to weekly with 4-week rolling trend
- Manager scorecard delivery went from emailed manually (sometimes) to auto-generated PDF per location
- Variance analysis went from none to budget + prior year by location
- Monthly time savings of 16+ hours, worth $3,600 in redirected staff time
Technical Highlights
More Finance projects