Back to storiesPractical guides

Analyze business spreadsheets with AI: three checks from 630 sales records

An actual analysis of tutorial sales and customer tables: recorded margins, repeat-customer definitions and a bad join. Includes aggregates, charts and reproducible scripts.

10 min readMarkdown
On this page8 sections

Does lower revenue justify closing a channel?

Mall Kiosk records $9,001 in revenue, compared with $33,832 online. Yet their recorded-profit margins are 71.98% and 70.77%. A recommendation to close the kiosk based on revenue ranking alone would skip cost scope and operating conditions.

We analyzed the files accompanying a coffee-business tutorial, checking arithmetic, aggregating channels, comparing repeat-customer definitions and running a deliberately incorrect join. The outputs include reproducible figures, charts and specific missing inputs. This work ran in Codex on teaching data; it is not a report of real customer business performance.

Identify the two tables first

The files come from Teacher’s Tech’s business-analysis tutorial and its author-provided download. The package contains sales-data.xlsx, customers.xlsx and project instructions. We read those files. Full video captions were unavailable, so this is an independent analysis of the accompanying material, not a reproduction of the video’s agent run.

Sales contains 630 rows and 630 distinct Order IDs, dated April 1, 2025 through March 30, 2026. Customers contains 200 rows and 200 distinct Customer IDs. “Per record” refers to an input row. In your own data, establish whether that row is an order, an item or a monthly aggregate.

Amounts retain the $ notation in the author’s project notes. Profit equals Revenue minus Cost, but complete cost coverage is not documented. We call it recorded profit rather than assuming it is net profit.

Ask for definitions and recalculation before recommendations

We checked headers, grain, dates, missing values and repeated IDs. Quantity multiplied by Unit Price and Revenue minus Cost match their supplied columns on every row within a tolerance of 0.005. The checked sales fields have no missing values.

Independent totals are revenue $74,428.00, recorded cost $21,246.00 and recorded profit $53,182.00. Overall recorded-profit margin is 53,182 divided by 74,428, or 71.45%. We did not average row percentages.

The main aggregation uses Decimal, with a separate sum to cross-check totals. Source workbooks remain read-only. A reusable request is:

Read the sales and customer tables without editing them.
Identify grain, keys, date coverage, missing values and duplicates.
Recalculate quantity times unit price and revenue minus cost.
Summarize revenue, recorded profit and weighted margin by channel.
Compare the supplied repeat label with the named Orders >= 2 definition.
Do not join customers to sales without a valid shared key.
Separate observations, hypotheses and missing decision inputs.
Save aggregates, a short report and reproducible calculations.
Do not export customer names or IDs.

Read amounts alongside rates

These are the actual aggregates from this task:

ChannelRecordsRevenueRecorded profitRecorded margin
Online180$33,832.00$23,943.3070.77%
Downtown Shop180$24,672.00$17,794.0072.12%
Mall Kiosk180$9,001.00$6,478.6071.98%
Farmer’s Market90$6,923.00$4,966.1071.73%

Mall Kiosk and Online each have 180 input records. Revenue per record is approximately $50.01 versus $187.96. Quantity, product mix and discounts are possible next investigations; these totals do not establish a cause.

Farmer’s Market has records only in May–October 2025, while the other channels have records in twelve months. Those totals do not represent equal operating exposure, and doubling six months is not a justified annual forecast. Months with records do not establish days or hours open.

Actual revenue and recorded margin by channel
Source: Teacher’s Tech teaching data; calculated and plotted in this task.

Why the repeat-customer percentage changes from 28% to 78.5%

The supplied Repeat Customer field labels 56 of 200 people Yes: 28.0%. A separately defined test of Total Orders at least two identifies 157 people: 78.5%. The two judgments disagree on 101 records.

We preserved the original labels and did not declare the higher percentage correct. The data provider needs to clarify the label’s rule and time window, and the period covered by Total Orders.

The chart keeps the definitions separate. A later report should state its chosen definition beside the result. Until the meaning is confirmed, these values should not drive a customer win-back plan or a channel evaluation.

Supplied repeat labels versus the at-least-two-orders definition
Source: Teacher’s Tech teaching data; calculated and plotted in this task.

A shared Channel column is not a customer join key

Sales has no Customer ID, and the customer table has no order reference that matches individual sales records. Channel is a category, not a record-level key.

We deliberately merged the tables on Channel in an in-memory copy to show the consequence: 630 sales rows became 32,670 rows, and revenue was repeated from $74,428 to $4,353,295. This is a controlled counterexample, not an observed accidental AI failure.

Online has 63 customer records, so each online sale repeats 63 times in that join. The pack’s demonstrate_bad_join.py reproduces the inflation through those multiplicities without exporting a customer-level merged table.

The current data supports separate descriptions by channel. Studying individual purchasing behavior requires a reliable common identifier and consistent time coverage.

Finish with three actions tied to missing evidence

The report’s next actions are specific:

  1. Establish cost coverage. Check whether Cost includes rent, labor, logistics and marketing before discussing net returns or closure.
  2. Investigate revenue per kiosk record. The sample difference is calculated; explaining it requires product mix, quantities, discounts and operating conditions.
  3. Align customer definitions and keys. Clarify the repeat label and its window, then obtain an identifier that can match sales records.

A colleague can now see which material to obtain and which decisions remain unsupported. The recommendations follow the gaps in this analysis rather than turning a ranking into a strategy.

Download the calculations and repeat them

Download our analysis pack. It contains scripts, aggregates, the bad-join counterexample, chart code, a request and review notes. It does not redistribute the author’s original workbooks, project instructions or customer records. Obtain the ZIP from the author’s resource link; analysis.json records the source hashes used here.

With openpyxl in an isolated Python environment, run:

python analyze.py /path/to/source.zip --output my-analysis.json
python demonstrate_bad_join.py /path/to/source.zip

Compare the result with analysis.json. If the source changes, explain the difference rather than forcing the old numbers. Charts are generated from the aggregate results so conclusions can be traced to calculations.

This was an actual analysis and cross-check of teaching data, not a model-accuracy or time-saving experiment. For your own work, start with a bounded question, known field definitions and material you are authorized to use.