Automate a Logistics Dashboard: Shipment Spreadsheets, AI Summaries

Keep one clean shipment sheet, calculate on-time rate, exceptions and cost per shipment with spreadsheet formulas, run a short set of data checks, then give the finished summary table to an AI assistant to write the weekly commentary. Formulas do the arithmetic and AI does the explaining, which keeps the figures right and the report readable.

That split matters because assistants are fluent and not always accurate with sums. If you ask one to calculate a percentage from raw rows, it will usually give a plausible number and occasionally a wrong one. Ask it to describe a table you have already computed, and it is dependable, provided you check that the words match the figures.

Follow me on Instagram@sagnikteaches

What this approach is, and what it is not

This is a spreadsheet with a summary tab and a weekly routine, not a business intelligence product. It suits a small logistics company with perhaps 300 to 5,000 shipments a month, two to six carriers or subcontractors, and one or two people who need to know how things went. Dashboard templates and software dashboards come later, if ever. Firms that buy a dashboard tool first often find that the hard part was never the charts. It was getting clean, consistently defined data into one place.

Connect on LinkedInSagnik Bhattacharya

The aim is a one-page weekly summary that answers three questions: did we deliver when we said we would, what went wrong and why, and what did it cost. Everything below serves those three questions.

Subscribe on YouTube@sagnikteaches

A worked example: a small firm with three carriers

Say a small logistics company moves about 1,240 shipments in a month using three carriers, which this example calls A, B and C. Each carrier sends a CSV of deliveries, and the office pastes them into one master sheet. Here is the illustrative month, and the summary the sheet produces.

Last month the overall on-time rate was 93.7%, so this month is down 1.8 percentage points. The table shows where: carrier B delivered 94.0% on time last month and 88.5% this month, a drop of 5.5 points, while it is also the cheapest per shipment at $8.10. The owner's question is whether B's lower price is still worth its late deliveries. That is a decision for a person. The dashboard's job was to put the question in front of them on Monday morning, not three weeks later when customers started to complain.

Step one: one master sheet with fixed columns

Every carrier names columns differently, and every carrier changes its format occasionally. Build a master sheet with your own column names and a short import routine for each carrier. The master columns for this firm are below. Keep the order and names fixed, because every formula points at them.

• shipment_id, a unique reference, never reused
• carrier, from a fixed list (A, B, C)
• ship_date and promised_date
• delivered_date, blank if not yet delivered
• status: delivered, in transit, failed, returned
• exception_code: none, delay, failed delivery, damage, address
• exception_note, free text from the carrier or your staff
• base_cost and surcharges, kept apart, with total_cost as a formula
• customer_ref, a code rather than a name

The two cost columns are separate on purpose. Fuel and redelivery surcharges are often where a cheap carrier stops being cheap, and combining them early hides the cause. Using a customer reference rather than a name also means the sheet can go to an AI tool later without carrying personal details.

Step two: write down the definitions before the formulas

Disagreements about a dashboard are nearly always disagreements about definitions. Put them in a tab called Definitions, one line each, and treat changes as a formal event.

• On time: delivered_date is on or before promised_date. Shipments still in transit past their promised date count as late. If you give a grace period, say 24 hours, state it here.
• Exception: any shipment with an exception_code other than none, whether or not it was eventually delivered on time.
• Cost per shipment: total cost, including surcharges, divided by every shipment in the period, delivered or not. Returned and failed shipments count, since you paid for them.
• Reporting period: by promised_date or by delivered_date? Choose promised date, so a shipment is judged in the week it was due, not in the week it finally arrived.

That last choice has teeth. If you count by delivery date, a bad week can hide inside the next week's numbers. If you count by promised date, a late delivery counts against the week it was meant to arrive, which is the week the customer remembers.

Step three: the summary tab, with formulas rather than prompts

The summary tab pulls everything from the master sheet. Add a helper column on the master, is_on_time, with a formula along the lines of =IF(AND(E2<>"", E2<=D2), 1, 0), where, in the column order above, column E holds the delivered date and column D the promised one. Then the summary figures are single COUNTIFS and SUMIFS lines.

• Shipments for a carrier in a week: =COUNTIFS(carrier, "A", promised_date, ">="&start, promised_date, "<="&end)
• On-time count: the same with is_on_time, 1 added.
• On-time rate: on-time count divided by shipments, wrapped in IFERROR(..., "") so an empty week shows blank, not an error.
• Exceptions by code: COUNTIFS on exception_code, one row per code.
• Cost per shipment: SUMIFS on total_cost divided by the shipment count.

Put the date range in two input cells at the top, so the whole tab recalculates when you change them. Add a second block that shows the same figures for the previous period and a difference column. A trend without a comparison is just a number.

The exceptions block for the example month looks like this: 112 exceptions, 9.0% of shipments, made up of 61 delays, 27 failed deliveries, 14 damaged and 10 address problems. Delays dominate, which fits carrier B's slide, and the 27 failed deliveries are worth a look on their own, since each one costs a second attempt.

Step four: data checks before any summary goes out

A dashboard that is confidently wrong does more harm than none. Add a Checks tab with a handful of counts, each of which should read zero or a number you expect, and look at it before you read the summary. Checks that catch most problems in small firms:

1. Duplicates: any shipment_id appearing twice. Carrier files often overlap at month ends, and duplicates inflate both volume and cost.
2. Missing dates: delivered shipments with no delivered_date, or a delivered date earlier than the ship date.
3. Unknown carriers: a carrier name not on the fixed list, which usually means a typo that will drop rows out of the summary.
4. Cost outliers: shipments costing more than three times the carrier's average, listed for a human to look at.
5. Row count reconciliation: the number of rows imported against the count in the carrier's own file or invoice.
6. Stale in-transit: shipments still marked in transit more than ten days after the promised date, which are probably delivered or lost and need chasing.

The fifth check is the most valuable and the least used. Carrier invoices rarely match carrier delivery files exactly, and the gap between them is money or missing data. If the file says 620 rows and the master holds 612, eight shipments are missing from every figure you have computed.

Where AI helps with the checks

The checks above are formulas. AI is useful for the messy part: reading free-text notes. Carriers write "NOT HOME - CARDED", "refused by recipient", "damaged box, goods ok" and so on, and the exception_code column is often blank or wrong. Paste a batch of fifty notes into a chat assistant with your list of codes and ask it to classify each. A prompt that works:

Classify each delivery note into exactly one of these codes:
delay, failed delivery, damage, address, none.
Use "none" only if the note describes a normal delivery.
If a note fits two codes, choose the one that caused the problem
first (for example "address wrong, so failed" is address).
If you cannot tell, write "unclear" and I will check it.

Return a table: row number, note, code. Do not add commentary.

Notes:
1. NOT HOME - CARDED
2. refused by recipient
3. damaged box, goods ok
4. left with neighbour no 14
5. gate locked, no access code on label

A plausible reply, illustrative: 1 failed delivery, 2 failed delivery, 3 damage, 4 none, 5 address (the label lacked the access code). Row 3 is the interesting one: "damaged box, goods ok" is damage to packaging, and you should decide in your definitions whether it is an exception at all. The assistant has followed your list; the policy question is yours. Spot check ten rows from each batch against the original notes before pasting codes into the master.

Step five: the AI summary, grounded in the table

Now the commentary. Copy the summary block, both periods side by side, and paste it with a prompt that sets the rules. The main rule is that the AI may only use the numbers on the page.

You write a weekly performance note for the owner of a small
logistics firm. Use ONLY the figures in the table below. Do not
calculate new percentages; if you need a difference, use the
"change" column. Do not guess at causes: where the table suggests a
question, phrase it as a question.

Format:
1. Headline: one sentence on on-time rate versus last period.
2. Three bullets: best carrier, worst carrier, biggest exception type.
3. One cost line: cost per shipment and what changed.
4. Two questions the owner should ask a carrier this week.
Maximum 150 words.

Table:
[paste summary block]

A sample output from the example month, illustrative: "On-time delivery fell to 91.9% from 93.7%, down 1.8 points. Carrier A was strongest at 94.2% on time. Carrier B was weakest at 88.5%, down 5.5 points while remaining the cheapest at $8.10 a shipment. Delays were the largest exception type at 61 of 112. Cost per shipment was $9.28. Questions: what changed in B's collection times this month? Are B's delays concentrated on particular routes?"

What you would fix: nothing in the figures, but check each number against the table, as you would a junior's report. The commonest slips are "percent" written where "points" is meant, a carrier ranked by volume rather than rate, and a cause stated as fact. The prompt guards against the third by asking for questions rather than explanations. If you want to know whether the assistant can read your particular file format, this tutorial on what breaks with uploaded files lists the usual snags.

Cost per shipment: reading the surcharge column

The headline cost figure hides most of what is interesting. Split it in two. In the example month, carrier C averages $11.20 a shipment, which looks expensive next to A at $9.40. Suppose the base cost columns tell a different story: C's base cost is $8.60 and surcharges average $2.60, mostly redelivery and fuel fees, while A's surcharges average $0.70. The difference between the carriers is mostly in the surcharge column, and that is something you can negotiate or avoid by fixing the address problems that trigger redelivery.

Ask the assistant to explain the difference only from the cost columns: give it base cost and surcharge totals per carrier and request a three-line comparison. Then add a line to the weekly note: surcharges as a share of total cost, per carrier. When that share creeps above 20% for any carrier, it is time for a call. Treat the 20% as a starting threshold to tune against your own history, not as a norm.

A small rule helps with interpretation. Compare cost per shipment only across shipments of a similar kind. A carrier that takes your heavier pallets will always look dearer than one that takes parcels. If you ship both, add a service_type column and report cost per shipment within each type, so the average does not hide a change in mix. A month with more pallets and fewer parcels can raise the blended figure while every individual rate is unchanged.

Refresh schedule: who does what, and when

Pick a rhythm that matches the meeting where the report is read. For most small firms a weekly cycle with a monthly review is enough.

That is about 35 minutes a week. Time your first manual week before you start, so you know what the routine replaces; assembling the same view by hand from three carrier files can easily take two to three hours, and your own figure is the one that counts. If your carriers provide data through an automatic feed, or your transport software exports on a schedule, you can connect that to the sheet and cut the paste step. Do the manual version for at least a month first, so you know what normal looks like before you automate any of it. The tutorial on automating a weekly task helps you decide whether the extra step pays back.

A carrier review email drafted from the numbers

The dashboard exists to change what you do. After a month like the example, the owner wants to raise carrier B's slide with the account manager. Give the assistant the carrier's row, the two periods and the exception breakdown for that carrier only, and ask for a short, factual email. A sample, illustrative: "Hi [first name], in October 410 of our shipments went with you, and 88.5% were delivered by the promised date, compared with 94.0% in September. 38 of the 47 late ones were marked as collection delays. Could you tell me what changed, and what you plan to do before the end of the month? I would like to review the figures again on the first of next month."

The email names figures, asks a question and sets a date, and the assistant could not have written any of it without a computed table. Always read it before sending, and check the counts. The 47 late shipments in this sample are the 410 minus the 363 on time, and the 38 would come from the exception codes on the master sheet.

When the on-time rate jumps for the wrong reason

Consider an illustrative removals and storage firm whose on-time rate rises from 90% to 97% in one month, to general celebration. The cause turns out to be a change in the way one carrier reports deliveries: its file now gives the date of the first delivery attempt as the delivery date, so failed visits count as delivered. Nothing about service has improved.

It shows up in the exceptions, not the headline: failed deliveries fall to almost zero in the same month, which is not plausible, and the customer service inbox is busier than ever. A figure that moves sharply deserves a question before applause. Add one more check to the Checks tab: flag any carrier whose on-time rate moves by more than five points in a period, and ask the assistant to list questions to put to that carrier about reporting changes before anyone concludes that service improved. For a take on what to do next about failed attempts, the tutorial on parcel-tracking updates covers messaging the customer.

Keeping customer details out of the AI tool

The summary table contains carriers and counts, nothing personal, so the weekly note is low risk. The note-classification step is where personal data can creep in, because carrier notes sometimes include names, door codes and phone numbers. Strip them before pasting: a find-and-replace on digits is a crude but useful first pass, and a column of shipment IDs lets you map the results back without exposing anyone. If you handle commercially sensitive rates, prefer a business plan with no training on your data by default, and read the plan's terms.

When to graduate to a dashboard tool

A spreadsheet is enough until one of three things happens: more than a handful of people need a live view, data volume makes the sheet slow, or you need alerts the moment something goes wrong rather than on Monday. Then a dashboard tool connected to the same cleaned data makes sense, and the definitions and checks you built carry over unchanged. That is another reason to do the spreadsheet stage properly. If you would like help choosing, an AI implementation consultation can look at your carrier files and recommend the lightest route.

How to check the dashboard is doing its job

• Reconciliation: the master row count matches carrier files and invoices to within a few rows, every week.
• Checks tab: reads clean before the summary is sent, and you can say why when it does not.
• Number match: every figure in the AI note equals the table. Spot check three each week.
• Action: at least one decision a month traces back to the report, such as a carrier conversation, a route change or a rate review.
• Readers: the people it is for open it. If nobody reads the Monday note by the third week, shorten it or change the day.

Questions about spreadsheet dashboards for shipping data

Why not let the AI calculate the percentages?

Language models miscount and misround more often than spreadsheet formulas do. Let COUNTIFS and SUMIFS produce every figure, then give the finished table to the AI to explain. If a number in the commentary does not match the table, the table is right.

What counts as on time?

Decide and write it down: usually delivered on or before the promised date, sometimes with a grace period of a few hours. Pick one definition, apply it to every carrier, and keep it fixed. Changing the rule mid-year makes trend lines meaningless.

How often should the dashboard refresh?

Weekly suits most small firms, plus a deeper monthly review. Daily refreshes help only if someone acts on them daily. A dashboard nobody opens is just a file, so match the cadence to the meeting where it is used.

Is it safe to upload shipment data to an AI tool?

Send only what the summary needs. Drop customer names, street addresses and phone numbers from the extract, and use shipment IDs instead. Check the privacy terms of your plan, and prefer a business plan if the data is commercially sensitive.

Do I need a dashboard tool as well?

Not at the start. A summary tab, two or three charts and an AI-written paragraph cover most small firms. Add a dashboard tool only when several people need a live view and the sheet can no longer be shared sensibly.

Further reads

• Can a Small Courier Firm Use AI to Plan Delivery Routes? — Use the same data to look at routes and drops per run.
• AI Route Planning for Small Delivery Fleets and Mobile Teams — Plan runs for small fleets using the same shipment data.
• How Marketing Agencies Use AI to Automate Client Reporting — Another example of automated, AI-written recurring reports.
• AI Inventory and Check-Out Reports: A Letting Agent's Guide — Another recurring report built from a spreadsheet and AI.
• Can AI Read Delivery Notes and Update Stock Automatically? — Capture delivery paperwork into your data sheet.
• AI Courier Quotes: Reply to Delivery Enquiries From Your Rate Card — Quote from the rate card using the cost data you now track.
• Cost per Drop: Work Out Delivery Cost From Route and Fuel Data — A step-by-step cost-per-drop calculation from your own route, fuel and driver-time exports, with a worked van example.
• Fuel Surcharge Calculator: Set a Weekly Rate for Haulage Quotes — A weekly fuel surcharge policy for haulage and courier quotes: base price, fuel share, step table, spreadsheet formulas and customer clause wording.
• AI for Freight Business Analytics: Margin by Lane From a Spreadsheet — A 200-shipment example shows how to find thin lanes without trusting an unchecked chatbot total.
• AI for Freight Business Sustainability: Emissions Figures for Tenders — A haulier's tender sheet separates measured fuel, allocation, factor source and the emissions figure a customer requested.
• AI for Hydroponic Farming: Review Nutrient and pH Logs Each Week — A grower's daily log becomes a weekly exception review, with readings checked against the crop's own target sheet.
• AI Agents for Logistics Companies: Which Tasks They Handle Today — Which logistics tasks an AI agent can take on today, which need a person to approve, and which to leave alone.
• ChatGPT Apps for Logistics: 7 Jobs for a Small Carrier or 3PL — Seven jobs a small carrier or 3PL can give ChatGPT, from quote emails to telematics audits, each with a prompt, a sample and a limit.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: no vendor figures are used. Every shipment count, rate, cost and carrier in this tutorial is illustrative, and the arithmetic is shown so you can check it against your own carrier files.