Import Builder
Import Builder/Guides/Franchise sales report errors
Franchises

Why Franchise Sales Reports Don't Combine, and How to Fix Them

Every period, each franchisee sends head office their sales so royalties can be worked out, usually as a spreadsheet. These are the problems that stop the reports combining, or put the wrong royalty on a location.

What to ask for

One location ID, written as head office writes it
A start and end date for every period
Gross sales, discounts, refunds and tax in separate columns
Net sales as your agreement defines it
Amounts as plain numbers, discounts and refunds positive
The same columns for every location
No totals or blank rows

Why it matters

Royalties and advertising fund contributions are a percentage of each location's sales, as the franchise agreement defines them. When reports arrive in different shapes, someone at head office spends days rebuilding them before anything can be invoiced, and a mistake means charging a franchisee the wrong amount. Agree the format once, and every period gets easier.

Common errors and their fixes

The same store written three ways

Cause: 104, Store #104 and Downtown are all the same location to a person, but three locations to a spreadsheet. Totals per location come out wrong, and some reports don't match any location at all.

Fix: Give every location one ID and ask for it exactly as head office writes it. In your own form, make it a dropdown of your real IDs so it can't be retyped.

Periods that overlap or leave gaps

Cause: One location reports the 1st to the 30th, another the 1st to the 31st, and a third sends a week that overlaps last month's report. Sales are counted twice or missed.

Fix: Agree the reporting periods up front (calendar months, or set weeks), and ask for a start and an end date on every report.

Gross and net mixed up

Cause: Some franchisees report sales before discounts and refunds, some after, and the column just says Sales. The royalty base ends up different for every location.

Fix: Ask for gross sales, discounts, refunds and sales tax in separate columns, and net sales as your agreement defines it. Then the base is the same for everyone.

Sales tax included, or not

Cause: Many agreements leave sales tax (or VAT or GST) out of the royalty base. If some locations include it and others don't, royalties are overcharged at some and undercharged at others.

Fix: Ask for tax collected in its own column, so it can be taken out consistently.

Amounts written as text

Cause: $12,450.00, 12.450,00 and 12450 AUD can't be added up until someone cleans them, and a European-style 12.450,00 can be read as 12.45.

Fix: Ask for plain numbers with a full stop for decimals, such as 12450.00, and keep each location in one currency. The currency symbols guide shows how to clean a column.

Negative discounts and refunds

Cause: Some locations enter refunds as -350, others as 350. Subtract them both ways and the net figure is wrong.

Fix: Ask for discounts and refunds as positive amounts that are taken away from gross sales.

Columns renamed, added or moved

Cause: A franchisee adds a Notes column in the middle, renames Gross Sales to Revenue, or deletes a column they don't use. When the files are combined, figures land under the wrong heading.

Fix: Lock the template down, or use a form where the columns can't be changed. See how to stop people changing your Excel template.

Totals rows and blank rows

Cause: A Total row at the bottom of a location's report is counted as another location, and blank rows between weeks break the combined file.

Fix: Remove totals, subtotals and blank rows before sending. Head office adds them up.

Dates that read two ways

Cause: 03/04/2026 is 3 April in Australia and the UK, and 4 March in the US, so a network across countries reports the wrong months.

Fix: Use YYYY-MM-DD for every date. See the date formatting guide.

The same report sent twice

Cause: A corrected report sent alongside the original, or a location that reports through two people, doubles that location's sales for the period.

Fix: Check for more than one row per location per period before combining, and ask for corrections to replace the earlier report, not add to it.

Locations missing from the period

Cause: With dozens of reports arriving by email, a late or missing location is easy to miss until royalties are already invoiced.

Fix: Compare the locations that reported against your location directory every period, and chase the gaps before closing it.

A last check

Before invoicing royalties, check every location in your directory has exactly one report for the period, and compare each location's sales with its previous periods. A big jump or drop usually means a missing zero, tax included this time, or a report sent twice.

Send the same form, with the same columns, every period. Once franchisees are used to it, most of these errors stop happening on their own.

Checklist before you send

Location IDs exactly as head office writes them
Periods that follow on, with no gaps or overlaps
Gross, discounts, refunds and tax reported separately
Sales tax handled the same way at every location
Plain-number amounts, one currency per location
Discounts and refunds as positive amounts
Columns unchanged from the template
No totals or blank rows
Dates as YYYY-MM-DD
One report per location per period

Collecting sales reports from franchisees?

Send franchisees a form instead of a spreadsheet. The franchise sales report template keeps every location on the same columns, dates and number formats, and the location directory template keeps one row per location ID.

Try the franchise sales report template

Related Guides

Relevant Templates

Import Builder is an independent product. This guide isn't legal or accounting advice: your franchise agreement defines the royalty base and reporting rules.