Miguel Paredes has run two carnicerías in the Chicago area for years. The first one opened in Pilsen 11 years ago. The second, in Little Village 3 years ago. He knew one was performing better than the other. But until 6 months ago, he had no idea by how much, why, or what to do about it.
His process for reviewing numbers: call each store manager on Friday afternoon, ask them to read the week's sales over the phone, write the numbers in a notebook, and compare. If the manager was busy, the call got pushed. If the numbers didn't add up, he'd wait until his accountant sent the monthly statements.
Miguel was making decisions about two businesses with $1.2M in combined annual revenue based on phone calls and handwritten notes.
This isn't an extreme case — it's the operational reality of most small businesses with more than one location in the United States.
The real risk of running without cross-location visibility
The danger of not being able to compare locations in real time isn't just inconvenience — it's that problems scale in silence.
In Miguel's case, the initial analysis once we connected the data revealed three situations he didn't know about:
1. Little Village had a 48% cost of goods sold, vs. 39% at Pilsen. For a butcher shop, that gap is devastating: 9 points of gross margin disappearing with no visible explanation. The source turned out to be a combination of untracked shrinkage and supplier prices that hadn't been renegotiated in 18 months.
2. Peak sales days were different at each location. Pilsen sold most on Saturdays; Little Village, on Thursdays (payday for several nearby industrial plants). Miguel was sending extra staff to both stores on Saturdays — leaving Little Village understaffed on its best day.
3. Three high-margin products consistently sold out at Little Village on Tuesdays. Customers asked for them and left for a competitor. The restocking system didn't catch the pattern because nobody was looking at it weekly.
None of these problems was catastrophic on its own. Together, they represented $80,000 to $110,000 annually in lost revenue or eroded margins.
The multi-location comparison dashboard in Power BI
The solution was connecting the two data sources that already existed — Square POS and QuickBooks — to Power BI, and building a dashboard specifically designed to answer the questions Miguel needed answered every week.
Data sources connected
- Square POS (API): Sales by product, hour, day, average ticket, payment method — updated in real time
- QuickBooks Online (API): Cost of goods, operating expenses, payroll by location, net margins — updated daily
- Google Sheets (manual): Beginning-of-week inventory — entered by each store manager on Mondays
The 5 dashboard views
View 1 — Weekly executive summary One page with the most important KPIs for both stores side by side: total sales, gross margin, average ticket, and variance vs. the prior week. Takes under 60 seconds to read. Miguel reviews it on his phone every Monday before 8 AM.
View 2 — Product comparison Which products sell most at each location, which have the best margin, and which run out before the weekend. This view revealed the Tuesday problem at Little Village in the very first week of operation.
View 3 — Hourly and daily patterns A heat map of sales by hour and day of week for each location. This is the view that showed the mismatch between Pilsen's Saturday peak and Little Village's Thursday peak.
View 4 — Costs and margins Side-by-side comparison of cost of goods sold (COGS), payroll as a percentage of sales, and net margin. This view automatically flags when a store's margin deviates more than 2 points from its historical average — and turns the indicator red.
View 5 — Month-end projection Based on the current sales pace in the first weeks of the month, the dashboard projects how the full month will close for each location. This lets Miguel make inventory purchasing decisions before it's too late to course-correct.
Technical implementation
Tools used
- Power BI Desktop + Power BI Service: Dashboard build and publishing
- Power Query (within Power BI): Data transformation and cleaning before visualization
- Square API: Real-time sales data via Power BI's native connector
- QuickBooks Online API: Financial data via Power BI's native connector
- n8n: Automation of the weekly summary report delivered to Miguel via WhatsApp every Monday
Implementation time: 2 weeks
Days 1–3 — Data connection and cleaning: The biggest challenge was that product names in Square didn't match exactly with names in QuickBooks (one said "Beef ribeye" and the other "Ribeye beef steak"). Power Query resolved this with mapping tables that unified the naming convention.
Days 4–8 — Dashboard build: The 5 views were built and the alert logic was calibrated. The 2-point margin deviation rule required tuning: in early testing, natural end-of-month spikes triggered false alarms.
Days 9–14 — Validation with real data: Miguel reviewed the dashboard with his accountant to verify that Power BI numbers matched the official financial statements. There was a payroll discrepancy that turned out to be a QuickBooks categorization issue for overtime hours — fixed at the source.
Results at 90 days
- Little Village gross margin: from 52% to 59% — The 7 points recovered came from renegotiating pricing on three cuts with the supplier and reducing shrinkage with a simple daily count system
- Little Village Thursday sales: +22% — Adjusting staffing for that day meant they could serve the demand that was previously lost to long lines
- High-margin product stockouts: reduced 80% — With weekly visibility of the pattern, the Tuesday order now arrives before stock runs out
- Miguel's time reviewing numbers: from 3 hours/week (calls + notebook) to 15 minutes/week
- Estimated revenue and margin impact in the first 90 days: $28,000 additional
What we learned
1. The first month always reveals a surprise.
In every multi-location dashboard project, the first month of real data always uncovers at least one problem the owner didn't know existed. Not because they're careless — but because without systematic visibility, those patterns are invisible.
2. The dashboard must answer specific questions the owner actually has.
A generic dashboard with 30 charts is useless. Before building anything, we spent a session with Miguel asking: what are the three questions you need to answer every Monday morning without calling anyone? Those answers become the dashboard views.
3. The resistance doesn't come from the owner — it comes from the managers.
Both store managers initially felt the dashboard was a way to "watch them." It was critical to include them in the project presentation and show them the goal was to give Miguel information for better decisions, not create a surveillance system. Today, both managers check the dashboard themselves to anticipate what Miguel will ask.
4. The dashboard doesn't replace the accountant — it changes the conversation.
Miguel's accountant no longer spends meeting time explaining what happened last month. Now the monthly meeting focuses on what decisions to make next month, because Miguel already arrives with the numbers understood.
Do you have more than one location and make decisions based on phone calls and gut feelings?
If you can't see in 60 seconds how each location is performing on a Monday morning, you're operating without the information you need. A decision dashboard can change that.
Schedule a 30-minute call and we'll review what data you already have and how to connect it.