Export each store's weekly numbers from its POS into one standard KPI sheet, one row per store, with last week and the same week last year beside them. Flag exceptions with formulas, then give the sheet to ChatGPT or Claude with a fixed prompt for a one-page summary, an exceptions list and a question for each manager.
Most pages for this phrase are about buying or growing a multi-unit franchise. The owner who already runs five or six units has a different problem: by nine on Monday morning they need to know which store slipped, by how much, and whom to ring. The approach that works does the arithmetic in the sheet, where it can be checked, and uses the assistant for what it is good at, which is reading across rows and writing plain sentences. The examples follow an illustrative owner of five yoga studios; the same method fits any business that has a till and a rota.
A Monday-morning routine in five steps
| Step | When | Who | Minutes |
|---|---|---|---|
| 1. Run the saved weekly report and send the file | Monday by 7:00 | Each manager | 10 each |
| 2. Paste each file into the Data tab | 8:00 | Owner or office assistant | 10 |
| 3. Check control totals and blank cells | Straight after | Owner | 5 |
| 4. Run the prompt and read the summary | 8:20 | Owner | 5 |
| 5. Call managers with exceptions, log the actions | From 9:00 | Owner | About 15 per call |
The owner's own time is about 20 minutes before the first call. The routine lives or dies at step 1. In the first month the work is getting five managers to send the same file by 7:00; make the summary depend on it, and let a late store show up on a "missing" line where everyone can see it. Nothing improves a Monday habit like an empty row with a store's name on it.
The standard KPI sheet: one row per studio, same columns each week
The sheet is the whole system. Every store gets one row and every week uses the same columns in the same order. For the yoga studio example:
| Col | Column | Source and note |
|---|---|---|
| A | Studio | A short code, the same in every export |
| B | Net sales | POS, after discounts and refunds, before tax; one basis for every store |
| C | Net sales last week | Copied from last week's sheet |
| D | Net sales, same week last year | Aligned by week number so a Monday meets a Monday; blank for a new store |
| E | Paid visits | Count of paid transactions or class bookings |
| F | Average sale | Formula: B divided by E |
| G | Labour hours | Rota or payroll export; sometimes a second file |
| H | Labour cost | Same source, same basis in every store |
| I | Labour % | Formula: H divided by B |
| J | Discounts and refunds | POS, in dollars |
| K | Discounts and refunds % | Formula: J divided by B |
| L | Class fill rate | Attendance divided by capacity, from the booking system |
| M | Manager note | One line: anything unusual this week |
| N | Vs last year | Formula: B divided by D, minus 1 |
| O | Flag | Formula: CHECK when any rule is broken (next section) |
Add a column only when you would act on it. A column that nobody questions on a call is clutter that the assistant will dutifully comment on every week. The weekly retail KPI sheet tutorial works through the same till-data layout for a shop, if your stores sell goods more than classes.
Here is an illustrative week for the five studios, using columns B to D and I to L. The total row uses sums, not averages of the percentages:
| Studio | Net sales | Last week | Same week last year | Labour % | Discounts and refunds % | Fill rate |
|---|---|---|---|---|---|---|
| 1 | $18,400 | $17,900 | $17,200 | 30.0% | 2.5% | 71% |
| 2 | $14,200 | $14,500 | $14,900 | 31.0% | 2.5% | 68% |
| 3 | $12,600 | $13,100 | $13,000 | 35.0% | 5.0% | 55% |
| 4 | $21,300 | $20,800 | $19,000 | 27.0% | 2.0% | 78% |
| 5 | $9,800 | $9,900 | $10,500 | 34.0% | 7.0% | 61% |
| Total | $76,300 | $76,200 | $74,600 | 30.7% | 3.4% |
Formulas first: exception flags and weighted totals in the sheet
Decide the rules before the assistant sees a number, and write them as formulas. Starting thresholds for the illustrative owner, to be replaced with ones set from your own last 12 weeks:
- Net sales more than 5% below the same week last year.
- Labour cost above 32% of net sales (the target is 30%).
- Discounts and refunds above 4% of net sales.
- Class fill rate below 60%.
Vs last year (N2): =IF(D2=0,"",B2/D2-1)
Labour % (I2): =IF(B2=0,"",H2/B2)
Discounts % (K2): =IF(B2=0,"",J2/B2)
Flag (O2): =IF(OR(N2<-0.05,I2>0.32,K2>0.04,L2<0.6),"CHECK","")
Company labour %: =SUM(H2:H6)/SUM(B2:B6) not =AVERAGE(I2:I6)The last line matters more than it looks. The simple average of the five studios' labour percentages is 31.4%; the true company figure, $23,415 of labour on $76,300 of sales, is 30.7%. The 0.7-point gap is about $543 of labour in a week, and it appears because the biggest studio, at 27%, counts the same as the smallest, at 34%. An assistant left to itself will average percentages unless told otherwise, so the sheet computes the total and the prompt forbids averaging. If you are weighing labour targets as well as reporting them, the tutorial on testing rota changes against labour cost uses the same percentage logic.
With these rules, the week above produces a short flag list: Studio 5 (sales 6.7% below last year, labour 34%, discounts and refunds 7%) and Studio 3 (labour 35%, discounts and refunds 5%, fill rate 55%). Studio 2 is 0.3 points short of breaking the sales rule (4.7% below last year against a 5% limit), which makes it a watch item, not an exception.
One more column turns a list into a ranking. Add the dollars each store sits above its thresholds: =MAX(0,H2-0.32*B2)+MAX(0,J2-0.04*B2). For Studio 3 that is $378 of labour above 32% plus $126 of discounts above 4%, or $504. For Studio 5 it is $196 plus $294, or $490. Studios 1, 2 and 4 score zero. Ranking by dollars matters because a store that breaks three rules by a hair can cost less than one that breaks a single rule by a mile, and your first call should go to the biggest cost, not the longest list of flags.
The summary prompt, and a good answer for five studios
Store the standing prompt in a ChatGPT or Claude Project, so each Monday you only paste the table and the manager notes. The prompt works the same in either.
You are preparing the weekly owner's summary for a franchisee with five studios. Use ONLY the table below. Do not guess causes; if the table cannot explain something, say so.
Output in this order:
1. Headline, two sentences: total net sales, change vs last week and vs the same week last year, and company labour % (total labour cost divided by total net sales; never an average of studio percentages).
2. Exceptions: each studio breaking a rule, one line each, with the number and the rule. Rules: sales more than 5% below last year; labour above 32%; discounts and refunds above 4%; fill rate below 60%.
3. Watch list: studios with no exception that are within one point of a rule.
4. Manager notes: for each exception studio, quote the note column word for word. Do not reinterpret it.
5. One question per exception studio for the manager call. Questions only, no explanations.
6. Missing or odd data: any blank, zero or impossible cell. If a studio row is missing, write MISSING, leave it out of the totals and say the totals are partial.
For every number you cite, use the figure exactly as it appears in the table.
[paste table]A good first answer, illustrative, looks like this:
Headline: Total net sales were $76,300, up 0.1% on last week ($76,200) and 2.3% on the same week last year ($74,600). Company labour was 30.7% of net sales ($23,415 on $76,300).
Exceptions: Studio 5: sales 6.7% below last year (rule: below -5%); labour 34.0% (rule: above 32%); discounts and refunds 7.0% (rule: above 4%). Studio 3: labour 35.0%; discounts and refunds 5.0%; fill rate 55% (rule: below 60%).
Watch list: Studio 2: sales 4.7% below last year, 0.3 points from the rule; labour 31.0%.
Manager notes: Studio 3: "Head instructor on leave; two cover classes paid at overtime." Studio 5: no note.
Questions: Studio 5: What made up the $686 of discounts and refunds, and which were the largest? Where are the extra labour hours going? Studio 3: What did the two overtime cover classes cost, and how long will the cover continue? How many classes ran with low attendance?
Missing or odd data: none.
Now read it as a sceptic. In an illustrative first draft, four things were wrong. It had averaged the five labour percentages and reported 31.4%. It called Studio 4's 12.1% growth "a record week", although the sheet holds no history. It explained Studio 3's low fill rate as "probably a holiday week", which nobody had said. And it listed Studio 2 as an exception when 4.7% does not break a 5% rule. The fixes are in the prompt: say weighted, say no causes, and say how a watch item differs from an exception. Run the corrected prompt, then compare two numbers with the sheet by hand before you pick up the phone.
Getting every store's export into one shape
The assistant is the easy part. The harder work is making five different exports into one row each. If every store runs the same POS, ask each manager to save one custom weekly report with the same columns and the same date range, Monday to Sunday. If stores run different systems, perhaps after an acquisition, keep a mapping table: "Net Sales (POS A)" and "Total Revenue (POS B)" both feed column B. Then use a lookup that finds each figure by store code and week, so the KPI tab is built by formulas from the Data tab and nobody types into it.
Check what your POS can actually do before you promise a Monday-at-seven routine. As one example, Square's help article on setting up analytics and reporting describes downloading transactions as a CSV file and email notifications for daily, monthly and annual sales summaries, but it describes no scheduled export of detailed reports. So a Monday export may be a manual step for each manager. If your system differs, use whatever it offers, and the weekly sales report with n8n tutorial shows what automation can take over once the manual version works. If exports arrive in messy shapes, the tutorial on cleaning messy data covers the tidy-up.
Five cases to decide once and write down:
- A store has no last-year figure. Leave the cell blank, make the formula return nothing, and tell the prompt "not comparable" so the assistant does not flag it.
- A store was closed for part of the week. Put the reason in the manager note and add it to the prompt's missing-or-odd list.
- A holiday moves between years. Add a calendar note column, so that nobody, human or assistant, mistakes a holiday shift for decline.
- A file is late. The summary runs without it, says MISSING and labels the totals as partial. Never let the assistant estimate a row.
- Totals disagree with the books. Compare the sum of the five net sales figures with the accounts or the figure you report to the franchisor. A gap usually means one export covered a different date range.
From exceptions list to manager calls and an action log
The summary is only half a routine. The point is what happens on the call. Turn each exception question into a three-line call sheet: the number, the question, and the action you want by next Monday. Keep a running action log:
| Week | Studio | Issue | Action and owner | Due | Result |
|---|---|---|---|---|---|
| 42 | 5 | Discounts and refunds 7.0% | List every refund over $20; manager to review each with the front desk | Next Monday | |
| 42 | 3 | Labour 35.0%, fill rate 55% | Manager to propose which two classes to merge or move | Wednesday |
Next Monday, paste the open actions beneath the new table and add one line to the prompt: "Say which of last week's exceptions appear again, and mark any that have now run two weeks in a row." An exception that repeats goes to the top of your list, and the same log shows whether a manager's fix worked, with a number to prove it. For one-line reminders to managers, the assistant can draft the follow-up message from the call sheet, but read it before it goes out, since a message that credits a manager with a fix the numbers do not show does damage. The weekly exceptions report tutorial takes the same idea further for operations teams.
Here is what the repeat line looks like in an illustrative week 43. Studio 5 comes back with net sales of $10,200 (3.8% below the same week last year), labour at 32.5% and discounts and refunds at 3.4%. Studio 3 has labour at 34.0%, discounts and refunds at 4.6% and a fill rate of 57%. The summary reads: "Second week running: Studio 3 breaks all three rules again, though each has improved (labour 35.0% to 34.0%, discounts and refunds 5.0% to 4.6%, fill rate 55% to 57%). Studio 5 has cleared the sales and discount rules; labour is 32.5%, half a point over." That is the sentence you wanted: one store still needs a hard conversation, and the other has earned a thank-you.
Swapping the columns for other kinds of franchise
The method does not depend on the business. Replace the columns that do not fit and keep the layout, the formulas, the prompt and the call routine:
| Business | Replace fill rate with | Other columns | Example rule |
|---|---|---|---|
| Landscaping company | Revenue per crew hour | Jobs completed, rework visits, crew overtime | Revenue per crew hour below $65, or rework above 5% of jobs |
| Tutoring centre | Sessions delivered against sessions booked | New enrolments, trial-to-enrolment conversion, tutor cost % | Delivered below 92% of booked, or tutor cost above 45% |
| Removals firm | Crew utilisation | Jobs completed, average job value, damage claims | Utilisation below 70%, or any claim above $500 |
The thresholds are examples; set yours from 12 weeks of your own history. If you run two kinds of franchise, keep one sheet per brand. Mixing them lets the assistant compare a landscaping crew with a tutoring centre, which no sentence can usefully do.
When the group grows past ten stores
A table of 12 stores is still readable, but 12 exception lines are not a summary. Add a layer. Give each area manager their stores' rows and run the same prompt for each area, so that three summaries of four stores each come back. Then paste the three exception lists, with their dollar rankings, into a second prompt: "Rank the exceptions across all areas by dollars above threshold and list the five largest. Say which area each belongs to. Do not add any exception that is not in the lists." The owner reads one page of five lines, and each area manager has already had their own list. The second prompt works from the summaries alone, so check it for dropped exceptions: count the flags in the sheet and compare them with the lines it returns.
What to settle before you paste sales data into an assistant
- Your franchise agreement. Some agreements limit what system data can go to outside tools, or require approved systems for reporting. Read yours, and ask the franchisor in writing if it is unclear. The tutorial on what a franchise agreement allows before adding AI tools goes through the question in more detail.
- The data you send. Store-level totals are enough. Leave out customer details and named staff pay; labour cost per store is what the sheet needs.
- The plan and its settings. Individual ChatGPT and Claude plans, at about $20 a month, have a switch in their privacy settings for whether chats are used for model training; business plans do not train on business content by default. Check the current label in the settings yourself.
- Where the files live. Keep the master sheet in a shared drive with limited access, not in a chat.
A two-minute audit of each week's summary
Even after the three-week parallel run described below, spend two minutes on every summary before you act on it:
- Recompute the company labour percentage with a calculator from the total row: labour cost divided by net sales. It must match the headline.
- Pick the largest store and one flagged store. Recompute vs last year and the labour percentage for each, and compare with the sheet.
- Count the CHECK flags in column O. Count the exceptions in the summary. The numbers must agree.
- Check that the thresholds written in the prompt match the thresholds in the sheet's formulas. When they drift apart, usually because someone edited one and not the other, the assistant and the sheet start disagreeing and each looks right.
If the summary and the sheet disagree, the sheet wins. Ask the assistant to quote the cell for each figure it cited, find the line that differs, and fix the prompt. A summary that cannot point to its numbers should not be sent to a manager.
Three weeks in parallel before you trust it
For three Mondays, produce your usual summary by hand and run the new one alongside. At the end of each week, ask four questions: did the exceptions list contain everything you would have flagged; did it flag anything you would not have; did the headline numbers match the sheet; and how long did it take? Count the false alarms. If one rule creates two or more a week, change its threshold, not the assistant. Spot-check two cells against the POS each week.
When three weeks in a row agree, retire the manual version. Keep two controls: the monthly reconciliation to the accounts, and a quarterly review of the thresholds against what your managers actually fixed. The summary you read on Monday should be short enough to finish before the first call, and specific enough that you know what to ask.
Questions franchise owners ask about weekly AI reporting
Can I paste raw POS exports into the assistant instead?
You can, but the arithmetic gets less reliable and the result harder to audit. Let the sheet do the sums and flags, where a formula can be checked, and give the assistant the finished table to read across and put into sentences. If you paste raw files, ask it to show its working, and spot-check two totals against your POS before you act.
How many stores can one summary cover?
The table has one row per store, so size is rarely the limit; readability is. A summary you read in five minutes rarely holds more than ten or so exceptions. Beyond a dozen stores, add an area-manager layer: summarise each area first, then run a second pass that compares the areas and lists only the worst exceptions.
Does this replace my accountant's monthly reports?
No. It is a weekly operations view built from POS and rota data, aimed at spotting problems while there is time to act. Month-end numbers still come from the accounts. Once a month, compare the sum of your weekly net sales with the accounts or the figure you report to the franchisor, and chase any gap.
What if my franchisor already sends reports?
Keep using them for brand measures. Your own sheet adds what theirs probably does not: all your stores side by side, your own thresholds, and a list of questions for managers. Before you start, read your franchise agreement for any limits on sharing system data with outside tools, and ask the franchisor if it is unclear.
Further reads
- Franchise Review Management: Replies for Several Stores, On Brand — Replying to reviews for several stores, on brand.
- Franchise Audit Checklist: Prepare for a Brand Inspection With AI — Prepare each store for a brand inspection with the same checklist.
- Franchise Local Marketing With AI: Staying Inside the Brand Rules — Local marketing for each store that stays inside the brand rules.
- How to Get a Weekly Business Summary Emailed to You by AI — The general version: a weekly business summary emailed to you.
- AI Drive-Thru Ordering: What a Fast-Food Franchisee Controls — Who really decides on AI drive-thru ordering in a franchise: three models, the clauses to read, questions to put in writing and a lane-level sum.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: Square's help article on setting up analytics and reporting (checked October 2026) as one example of POS export options. All stores, figures, thresholds and wording are illustrative.