The Weekly Operations Report
One page, read in ninety seconds, producing a decision. Most operational reports achieve none of those three things.
What a manager actually wants to see
You will learn what a report is for, which is narrower than most people assume.
A manager reading your report has one question: is there anything here I need to do something about? Everything else is context for that question.
| What they want | What they usually get |
|---|---|
| Is this week better or worse than last? | A table of numbers with no comparison |
| What needs a decision from me? | Everything presented at equal weight |
| What is going to go wrong next? | Only what has already gone wrong |
| Why did this number move? | The number, with no explanation |
| What are you doing about it? | A problem with no owner or action |
The test
If a manager reads your report and has to ask a follow-up question to know whether action is needed, the report failed. It does not need to answer every possible question — it needs to answer that one.
Reports are not proof of work. It is tempting to include everything you handled to demonstrate effort, and it makes the report longer and less useful. Your work is visible in the results; the report exists to prompt decisions.
Write the first line last. Once the report is assembled you will know what the week actually was, and you can open with a single sentence that says so: "Volume up 8%, on-time down to 89% — all six failures on the Rotterdam lane."
- Managers read a report asking whether action is needed
- A report requiring a follow-up question has failed
- A report is not evidence of effort
The one-page format
You will learn a report structure you can reuse every week.
WEEKLY OPERATIONS REPORT Week 47, w/c 17 Nov 2026
Prepared by S Whitfield
1 HEADLINE
Volume up 8% on last week. On-time fell to 89% - all six
failures were on the Rotterdam lane, caused by one carrier.
Recommend we move December volume to the alternative carrier.
2 VOLUME
This wk Last wk Wk 47 last yr
Shipments 54 50 47
Containers (TEU) 71 66 61
Air consignments 9 11 14
3 ON TIME
Raw (vs first confirmed date) 89.0% (48/54)
Controllable (excl. customs, weather) 94.4%
Late shipments 6
Average days late on failures 2.3
Target 95%
4 COST
Cost per shipment GBP 2,411 vs target 2,350
Variance driver: two expedited air movements, recharged
5 EXCEPTIONS - every failure, one line
NG-4418 2 days late customs exam, random selection
NG-4431 3 days late carrier missed transhipment (Rotterdam)
NG-4432 1 day late carrier missed transhipment (Rotterdam)
NG-4433 4 days late carrier missed transhipment (Rotterdam)
NG-4437 2 days late carrier missed transhipment (Rotterdam)
NG-4439 1 day late delivery site closed, no booking made
6 RISKS NEXT WEEK
- Peak season surcharge from 1 Dec on Asia lanes
- Two shipments with free time expiring Thu 27th
- Rotterdam carrier has not responded to our escalation
7 DECISION NEEDED
Approve moving December Rotterdam volume (est. 9 containers)
to Carrier C. Costs GBP 140 more per container, GBP 1,260
total. Their on-time record is 96% against this carrier's 71%
over the last six weeks.
Needed by: Thursday 27 Nov to secure space.
Section 7 is the reason the report exists. If there is genuinely no decision needed, write "No decisions needed this week" — it tells the manager they can stop reading, which they will appreciate and remember.
- Seven sections: headline, volume, on-time, cost, exceptions, risks, decision
- List every failure individually with its cause
- Always state explicitly whether a decision is needed
Choosing the right time period
You will learn to pick a reporting period that shows signal rather than noise.
| Period | Good for | Problem |
|---|---|---|
| Daily | Live operational control | Almost entirely noise as a trend |
| Weekly | Operational management, exception review | Small samples swing wildly |
| Monthly | Cost trends, carrier performance | Too slow to catch an emerging problem |
| Rolling 12 weeks | Seeing a real trend through the noise | Slow to reflect a genuine change |
| Year on year | Seasonal businesses | Conditions may differ entirely |
The small-sample problem
With 20 shipments a week, one failure moves on-time performance by five percentage points. A week at 90% followed by a week at 95% looks like a trend and is one shipment. Reporting that as improvement is misleading.
WEEKLY ON-TIME, 20 SHIPMENTS PER WEEK Wk 41 95% (1 late) Wk 42 90% (2 late) Wk 43 100% (0 late) Wk 44 85% (3 late) Wk 45 95% (1 late) Wk 46 90% (2 late) Read weekly: wild swings, apparent crisis in week 44, apparent triumph in week 43. Rolling 6-week average: 92.5% Total late in period: 9 of 120 THE REAL QUESTION is not "why was week 44 bad" but "why are we running at 92.5% against a 95% target, and what do those 9 failures have in common?" Answer in this case: 6 of the 9 were the same lane. Invisible weekly. Obvious over six weeks.
Report weekly for exceptions and operational control, but show the rolling average alongside the weekly figure. The weekly number tells you what happened; the rolling number tells you whether it matters.
- Small samples make weekly percentages swing on single shipments
- Show the rolling average alongside the weekly figure
- Patterns across failures matter more than any single bad week
Writing the commentary
You will learn to explain numbers in a sentence rather than leaving them to speak for themselves.
Numbers do not explain themselves. A figure with no commentary invites the reader to invent their own explanation, which is usually wrong and occasionally unflattering to you.
| Weak | Strong |
|---|---|
| "On-time 89%" | "On-time 89%, down from 94%. Six failures, five on the Rotterdam lane with one carrier. Escalated 19 Nov, no response yet." |
| "Cost per shipment up" | "Cost per shipment GBP 2,411 against target 2,350. Entirely due to two expedited air movements, both recharged to clients. Underlying cost unchanged." |
| "Volume increased" | "Volume up 8% week on week, consistent with the pre-Christmas build we forecast. Expect this to continue to week 50." |
| "Damage rate 1.2%" | "Damage rate 1.2% against 0.4% last month. All four incidents were LCL consignments from the same consolidator. Investigating packing standards at their CFS." |
The three-part commentary
- What the number is, and against whatComparison is what makes a number meaningful.
- Why it movedThe specific cause, not a general one.
- What is being doneOr that nothing needs to be.
Explaining a bad number is not making excuses, provided the explanation is specific and accompanied by an action. "Cost rose because of market conditions" is an excuse. "Cost rose because of two expedited movements, both recharged" is an explanation with evidence.
- An unexplained number invites the reader to invent an explanation
- State the figure, the comparison, the cause and the action
- Specific causes are explanations; general ones are excuses
Sending it so it gets read
You will learn the delivery habits that determine whether a report is used.
- Same day, same time, every week. Predictability means people build it into their routine. An irregular report gets filed unread.
- In the email body, not only attached. Attachments are opened on desktops. The headline and decision should be readable on a phone.
- The headline in the subject line. "Week 47 ops — on-time 89%, decision needed on Rotterdam carrier" beats "Weekly report week 47".
- To the right people only. A report copied to twenty people is read carefully by none.
- Consistent format. Readers learn where to look. Changing the structure each week forces them to re-read everything.
Sending the report on Friday afternoon. It arrives when people are finishing the week and lands unread on Monday under forty other emails. Monday morning or Tuesday means it is read when decisions are actually being made.
If a report has been sent for six weeks and nobody has responded or acted on it, stop and ask. Either the wrong things are being measured, or it goes to the wrong people. A report nobody uses is worse than no report, because it consumes your time and creates a false sense of oversight.
- Same day, same time, same format — predictability drives readership
- Headline in the subject line, key content in the email body
- If nobody responds for six weeks, ask why rather than continuing
Module 1 review
With 20 shipments a week, on-time goes 85% then 100%. What does this show?
One failure moves the figure by five points at this volume. Show a rolling average alongside the weekly number.
Which is an explanation rather than an excuse?
Specific, verifiable causes with an outcome attached are explanations. General attributions are excuses.
Your weekly report has had no response for six weeks. What should you do?
A report nobody uses consumes your time and creates a false sense of oversight. Adding detail makes it less likely to be read, not more.
Core KPIs
Six measures that matter, how each is faked without anyone intending to, and how to define them honestly.
On-time delivery
You will learn the three definition choices that determine whether OTD means anything.
On-time delivery is the headline number in every logistics review, and the easiest in the business to quietly fake — usually without anyone intending to.
- On time against which date?The date the customer originally requested, the date you first confirmed, or the date you last revised after a delay? Measuring against the revised date makes OTD look excellent and means nothing, because a shipment delayed four times still counts as on time.
- What counts as delivered?Arrival at site, arrival within the booked window, or signed and accepted? A vehicle arriving at 16:55 for a 14:00 window arrived that day and was late.
- Whose failures are excluded?Many teams exclude weather, customs holds and customer site closures. That may be fair for judging a carrier, but the customer experienced every one of those as a late delivery.
WEEK 47 ON-TIME DELIVERY
Measured against: FIRST CONFIRMED delivery date
Delivered = signed for within the agreed window
Raw OTD 89.0% (48 of 54)
Controllable OTD 94.4%
(excludes 2 customs exams, 1 site closure)
Late shipments 6
Average days late on failures 2.3
Worst single failure 4 days
Concentration: 4 of 6 failures on one lane, one carrier.
WHY BOTH NUMBERS
Raw is what the customer experienced.
Controllable is what we can act on.
Reporting only one of them misleads in one direction
or the other.
OTD tells you how often. It never tells you how badly. Ninety-four per cent on-time with an average six-day failure is a worse operation than eighty-eight per cent with an average half-day failure — and the first one scores better. Always pair the percentage with average days late.
When OTD drops sharply, check for concentration before concluding anything general. Most sudden movements have a single cause — one lane, one carrier, one customer site — rather than a broad decline in performance.
- Measure against the first confirmed date, not the revised one
- Report raw and controllable figures together
- Always pair the percentage with average days late
Cost per shipment
You will learn why average cost per shipment moves for reasons that have nothing to do with performance.
Cost per shipment is useful and easy to misread, because the mix of shipments changes constantly.
MONTH 1
20 sea FCL shipments @ GBP 2,400 = 48,000
4 air shipments @ GBP 5,200 = 20,800
------
24 shipments 68,800
AVERAGE COST PER SHIPMENT GBP 2,867
MONTH 2
20 sea FCL shipments @ GBP 2,400 = 48,000
8 air shipments @ GBP 5,200 = 41,600
------
28 shipments 89,600
AVERAGE COST PER SHIPMENT GBP 3,200
Average cost rose 11.6%.
NOT ONE RATE CHANGED.
The mix shifted: air went from 17% to 29% of shipments.
CORRECT REPORTING
Sea cost per shipment 2,400 -> 2,400 no change
Air cost per shipment 5,200 -> 5,200 no change
Air share of volume 17% -> 29% +12 points
Blended average 2,867 -> 3,200 +11.6%
"Cost per shipment rose 11.6%, entirely due to a higher
proportion of air freight. Underlying rates unchanged."
Reporting a blended average and letting a manager conclude that costs are rising or that someone negotiated badly. Always segment by mode, and by lane where volumes allow. A blended figure hides more than it reveals.
When any average moves, the first question is always whether the mix changed. In logistics it usually did, and checking takes two minutes. Reporting a mix-driven movement as a cost movement is the most common analytical error in the field.
- Blended cost per shipment moves with mix, not only with rates
- Segment by mode and lane before drawing conclusions
- When an average moves, check the mix first
Damage and claims rate
You will learn to measure damage in a way that reveals causes.
| Measure | Calculation | Tells you |
|---|---|---|
| Damage rate | Damaged consignments ÷ total consignments | How often it happens |
| Claim value rate | Claim value ÷ total shipment value | How much it costs |
| Claim success rate | Claims paid ÷ claims made | Whether your evidence process works |
| Average settlement time | Days from notification to payment | Cash flow impact |
Segment by cause, always
A damage rate on its own is a number. Broken down by cause it becomes an action list.
- Handling damage — points to a carrier or terminal
- Packing failure — points to the shipper or a packaging specification
- Water or condensation — points to container preparation or route conditions
- Load shift — points to securing at origin
- Shortage — often a counting or loading discrepancy rather than damage
A low claim success rate usually indicates a process problem at your end rather than unreasonable carriers. Claims fail because of clean PODs, missing photographs, late notification or destroyed evidence — all of which are within your control. Track the reason each failed claim failed.
Track LCL and FCL damage separately. LCL cargo is handled far more times and will show a higher rate for reasons that have nothing to do with your carriers. Blending them produces a meaningless number and hides genuine carrier problems.
- Measure frequency, value, claim success and settlement time
- Segment by cause to turn a number into an action list
- Low claim success usually indicates your own evidence process
Quote conversion rate
You will learn to measure quoting performance and read what it tells you.
Conversion rate = quotes won ÷ quotes issued
The headline figure is far less useful than the breakdown.
QUARTER 3 CONVERSION Overall 38 won / 124 quoted = 30.6% BY LANE Asia - UK 21 / 48 43.8% strong Europe - UK 14 / 31 45.2% strong UK - Middle East 2 / 27 7.4% <<< problem Transatlantic 1 / 18 5.6% <<< problem BY CLIENT TYPE Existing clients 29 / 41 70.7% New prospects 9 / 83 10.8% BY REASON LOST (where known, 62 of 86) Price 38 61% Incumbent relationship 14 23% Service capability 6 10% No longer shipping 4 6% WHAT THIS SAYS We are competitive on lanes we run regularly and badly uncompetitive on two lanes where we have no buying power. Continuing to quote them costs time and produces nothing. ACTION: either build real buying on Middle East and transatlantic, or stop quoting them and focus effort where we convert at 44%.
A very high conversion rate is not necessarily good news. Winning 80% of quotes often means pricing too low. A healthy rate varies by market but consistently winning almost everything suggests margin is being left on the table.
Record the reason for every loss, even approximately. Most forwarders track conversion and not causes, which tells them they are losing without telling them why. The reason breakdown is where the actual decision sits.
- Break conversion down by lane, client type and reason lost
- Very high conversion often signals underpricing
- Quoting lanes you never win is a cost with no return
Invoice accuracy
You will learn why billing errors cost more than they appear to.
Invoice accuracy = invoices with no dispute or correction ÷ invoices issued
Why a small error rate is expensive
- Delayed payment. A disputed invoice is not paid until resolved, often weeks.
- Administrative cost. Investigating, correcting and reissuing takes time from several people.
- Credibility. A client who finds two errors starts checking every invoice, which finds more and slows everything.
- Lost revenue. Under-billing is rarely corrected upward once discovered.
Invoices per month 420
Error rate 6% = 25
Average time to resolve each 1.5 hrs
Cost of staff time @ GBP 28/hr GBP 1,050/month
Average invoice value GBP 2,400
Average payment delay on disputed 18 days
Value delayed monthly 25 x 2,400 GBP 60,000
Cost of financing that at 9% annual
60,000 x (18/365) x 0.09 GBP 266/month
Under-billing: 8 of the 25 errors were undercharges,
averaging GBP 140, rarely recovered
GBP 1,120/month
TOTAL MONTHLY COST OF BILLING ERRORS GBP 2,436
ANNUAL GBP 29,232
Reducing error rate from 6% to 2% saves roughly
GBP 19,500 a year, with no change to rates or volume.
Track errors by type, not just count. Most billing errors cluster into three or four causes — missing recharges, wrong rate applied, incorrect quantities, missing disbursements. Fixing the top two usually halves the rate.
- Billing errors cost staff time, delayed cash and lost revenue
- Under-billing is rarely recovered once discovered
- Track errors by type — a few causes generate most of them
Setting targets that can be hit
You will learn to set targets that drive improvement rather than gaming.
What makes a target work
- Based on your own history, not an aspiration borrowed from elsewhere
- Achievable with a known change, not by heroic effort
- Within the team's control, or split into controllable and uncontrollable parts
- Defined precisely, so it cannot be reinterpreted when missed
- Reviewed on a fixed schedule, so conditions can be accounted for
How targets get gamed
Not usually through dishonesty, but through definition drift:
- Measuring against revised dates instead of original ones
- Reclassifying failures as excluded causes
- Excluding difficult shipments from the sample
- Changing the definition quietly after a bad period
If a target is impossible, people will find a way to appear to hit it rather than report failure every week. A 99% on-time target on a lane that runs through a congested port does not produce 99% performance — it produces creative definitions. Set targets that a good operation can genuinely reach.
Write the KPI definition down once, in a single document, and refer to it. "On-time = delivered within the booked window, measured against the first confirmed date, all causes included." When someone proposes a change, it becomes a visible decision rather than a quiet drift.
- Base targets on your own history and a known change
- Impossible targets produce creative definitions, not performance
- Write definitions down so changes become visible decisions
Module 2 review
Average cost per shipment rose 11.6% but no rate changed. What happened?
Blended averages move with mix. When any average moves, check the mix before concluding costs rose.
Which operation is genuinely worse?
OTD tells you how often, never how badly. The 94% operation scores better and disrupts customers far more.
Your claim success rate is low. What is the most likely cause?
Most claims fail on evidence that was within your control at the moment of delivery. Track why each failed claim failed.
Spreadsheets for Logistics
Building a job log that survives real data, and getting answers out of it without breaking anything.
Structuring a job log properly
You will learn how to lay out data so it can be analysed later.
The single most important rule: one row per shipment, one column per fact, no merged cells, no blank rows. A spreadsheet laid out for reading is almost always useless for analysis.
ONE ROW PER SHIPMENT. Column headings in row 1 only.
Ref Client Lane Mode Ctr Confirmed Actual Status
NG-4417 Northgate CNNGB-GBFXT FCL 40HC 2026-11-18 2026-11-18 Closed
NG-4418 Northgate CNNGB-GBFXT FCL 40HC 2026-11-18 2026-11-20 Closed
NG-4419 Midland ESVLC-GBFXT LCL 2026-11-24 Live
...continued columns...
BuyCost SellCost Margin DaysLate LateCause Damage
2704.02 3068.00 363.98 0 - N
2704.02 3068.00 363.98 2 Customs exam N
- N
RULES THAT MAKE IT WORK
1 One row = one shipment. Never one row per invoice line.
2 One column = one fact. Not "Felixstowe 18/11" in one cell.
3 Dates as real dates (2026-11-18), never text.
4 Numbers as numbers - no "GBP" inside the cell.
5 Consistent values. "Customs exam" always spelled the same.
6 No blank rows. No merged cells. No totals inside the data.
7 Totals and charts on a SEPARATE sheet.
8 Never delete a row - mark it cancelled.
Putting totals at the bottom of the data and merging cells to make it look tidy. Both break every analysis tool you will later want to use. Keep the data raw and ugly on one sheet; build the presentable version on another.
Use dropdown lists for any column you will group by — status, mode, late cause, client. Free text produces "Customs exam", "customs examination" and "Cust Exam" as three separate categories, and your analysis silently splits into thirds.
- One row per shipment, one column per fact, no merged cells
- Dates as dates, numbers as numbers, values consistent
- Keep raw data separate from presentation
Lookups and joins in plain language
You will learn what a lookup does and when you need one, without needing to be technical.
A lookup pulls information from one table into another using a shared identifier. It is how you combine your job log with a rate card, a client list or a carrier performance table.
TABLE 1 - your job log Ref Carrier BuyCost NG-4417 MSC 2704.02 NG-4418 MSC 2704.02 NG-4419 Hapag 1890.00 TABLE 2 - carrier performance, maintained separately Carrier OnTime DamageRate AccountMgr MSC 94% 0.4% D Reyes Hapag 87% 1.1% L Novak ONE 96% 0.3% K Ahmed LOOKUP joins them on the shared column "Carrier": Ref Carrier BuyCost OnTime AccountMgr NG-4417 MSC 2704.02 94% D Reyes NG-4418 MSC 2704.02 94% D Reyes NG-4419 Hapag 1890.00 87% L Novak WHY NOT JUST TYPE IT IN? Because when MSC's on-time changes, you update ONE cell in table 2 and every row updates. Typed-in data has to be found and changed everywhere, and it never all gets found.
Lookups fail when the shared identifier does not match exactly. "MSC" and "MSC " with a trailing space are different values to a spreadsheet. Most lookup problems are whitespace, inconsistent capitalisation, or numbers stored as text — not formula errors.
Keep one reference table per thing — carriers, clients, lanes, rates — and look everything up from those. The alternative, typing the same information into every row, guarantees your data disagrees with itself within a month.
- A lookup pulls data from a reference table using a shared identifier
- It means updating one cell instead of hundreds
- Most lookup failures are data consistency, not formula errors
Pivot tables for shipment data
You will learn what a pivot table does and the four questions it answers fastest.
A pivot table summarises a long list into a grouped summary without changing the underlying data. It is the fastest way to answer "how much, by what" questions.
1 VOLUME AND COST BY LANE
Rows: Lane Values: count of Ref, sum of BuyCost,
average of Margin
Lane Shipments Total Cost Avg Margin
CNNGB-GBFXT 28 75,712 364
ESVLC-GBFXT 14 26,460 298
CNSHA-GBSOU 9 24,120 412
2 LATE SHIPMENTS BY CAUSE
Rows: LateCause Values: count, average of DaysLate
Cause Count Avg Days Late
Carrier tranship 9 2.8
Customs exam 6 2.2
Late documents 4 3.5
Site closed 2 1.0
3 PERFORMANCE BY CARRIER
Rows: Carrier Values: count, % late, avg days late
4 MARGIN BY CLIENT
Rows: Client Values: sum of Margin, average margin %
-> reveals which clients are actually profitable
WHY THIS MATTERS
Pivot 2 above took thirty seconds to build and shows
that carrier transhipment causes more delay days than
customs and documents combined. That is an action.
Build pivot 4 — margin by client — before any other analysis. Most forwarders discover that a small number of clients generate most of the profit, and that one or two large, demanding accounts make almost nothing. That finding changes decisions more than any operational metric.
A pivot table is only as good as the consistency of the data underneath. If "Customs exam" appears three different ways, the pivot splits it into three rows and understates the biggest cause. Clean the data first, always.
- Pivots summarise long lists into grouped answers in seconds
- Build volume by lane, delays by cause, performance by carrier, margin by client
- Inconsistent values silently split categories and understate causes
Cleaning messy carrier data
You will learn to prepare exported data before analysing it.
Data exported from carrier portals and transport systems is rarely analysis-ready. The same problems appear every time.
| Problem | Symptom | Fix |
|---|---|---|
| Numbers stored as text | Sums return zero; values left-aligned | Convert to number format |
| Dates as text | Cannot sort or calculate differences | Convert to date format |
| Trailing spaces | Lookups fail on apparently identical values | Trim whitespace |
| Inconsistent naming | One carrier appears as three | Map to a standard list |
| Merged header rows | Columns will not sort or filter | Unmerge, put headings in one row |
| Duplicate rows | Counts and totals overstated | Remove duplicates on the reference column |
| Blank rows mid-data | Ranges stop early | Delete blank rows |
| Currency symbols in cells | Values treated as text | Strip symbols, format the column instead |
A fixed routine
- Never work on the originalCopy the export to a new sheet and keep the raw file untouched, so you can start again.
- Check row count before and afterIf cleaning changed the number of rows, know exactly why.
- Fix formats firstDates and numbers, before anything else.
- Standardise text valuesTrim spaces, apply consistent naming.
- Remove duplicates lastAnd record how many were removed.
- Spot-check against sourcePick three rows and verify against the portal or an invoice.
Cleaning data and not recording what was changed. Six weeks later, a figure does not reconcile and nobody can tell whether the difference is a real change or something removed during cleaning. Keep a note of every transformation.
- Exported data has predictable problems — formats, spaces, naming, duplicates
- Never work on the original file
- Record what cleaning changed, and check row counts
The formulas you will use every week
You will learn the small set of calculations that covers most logistics analysis.
DATE AND TIME
Days late = Actual date - Confirmed date
Transit days = Delivery date - Collection date
Days to free time = Free time expiry - today
Working days use a network-days function, which
excludes weekends
PERCENTAGES
On-time % = On-time count / total count
Variance vs target = (Actual - Target) / Target
Mix share = Segment count / total count
MONEY
Margin = Sell - Buy
Margin % = (Sell - Buy) / Sell <- of SELL
Markup % = (Sell - Buy) / Buy <- of BUY
Sell for target margin = Buy / (1 - target margin)
UNIT COSTS
Cost per pallet = Total cost / pallets
Cost per kg = Total cost / gross kg
Cost per loaded km = Total cost / loaded km
CONDITIONAL COUNTS AND SUMS
Count of late shipments on one lane
Sum of margin for one client
Average days late by carrier
(these are the building blocks of every KPI)
ROLLING AVERAGES
Average of the last N weeks, recalculated each week -
the single most useful smoothing tool in the report.
Margin percentage and markup percentage are different and both are called "percentage" in conversation. A 20% margin on a £2,000 buy means selling at £2,500, not £2,400. This error is common, invisible and expensive — it was covered in FF·02 and it belongs in every analyst's head.
Put every calculation in a named column in the sheet, not typed into a chart or a report. When a number is questioned, you can point at the column and show the working. Numbers that exist only inside a chart cannot be defended.
- A small set of date, percentage, money and unit-cost calculations covers most work
- Margin is of sell; markup is of buy — they are not interchangeable
- Keep calculations in visible columns so they can be defended
Avoiding the five spreadsheet disasters
You will learn the failure modes that produce confidently wrong numbers.
- The range that stopped growingA formula covers rows 2 to 400. The data now runs to row 640. Every total has been silently wrong for months. Use whole-column references or formal tables that expand automatically.
- The hardcoded numberSomeone typed 2,400 into a formula instead of referencing a cell. The rate changed; the formula did not. Never put a number inside a formula.
- The copy that became the masterThree versions in circulation, each edited by someone different. One file, one owner, shared rather than emailed.
- The hidden filterA sheet left filtered shows a subset. Someone reads a total that covers a third of the data. Clear filters before sharing, always.
- The broken referenceA formula pointing at a file that moved, or a deleted column. Errors show as visible error values — but a formula quietly returning the wrong cell does not.
A monthly cost report ran for eleven months showing costs under control. The summing range had been set when the log held 400 rows. By month four the log had grown past it, and every subsequent shipment fell outside the range.
The reported total was understated by a growing margin each month. It was discovered when finance reconciled against carrier invoices and found a substantial gap.
Nobody had done anything dishonest. One range reference had not been updated, and no one had checked whether the row count matched.
Put a single check cell at the top of every working sheet: total row count, and total value. Compare it against an independent source each month — invoice totals, system counts. Five seconds of checking catches every one of the five disasters above.
- Fixed ranges, hardcoded numbers, duplicate files, hidden filters and broken references
- Silently wrong numbers are more dangerous than visible errors
- Reconcile totals against an independent source every month
Module 3 review
A pivot shows "Customs exam" as three separate rows with different counts. What happened?
Inconsistent text values silently split categories and understate the biggest causes. Use dropdown lists for anything you group by.
A total has been understated for months with no visible error. What is the most likely cause?
Fixed ranges produce confidently wrong numbers with no error message. Reconcile row counts and totals against an independent source monthly.
You buy at £2,000 and need a 20% margin. What do you sell at?
Margin is calculated on the sell price: 2,000 ÷ 0.80 = 2,500. Adding 20% to the buy gives a 16.7% margin.
Dashboards and Charts
Showing a number so it is understood in five seconds, and not showing one when a sentence would do.
Choosing the right chart
You will learn to match chart type to the question being asked.
| Question | Chart | Why |
|---|---|---|
| How has this changed over time? | Line | Shows direction and turning points |
| How do these categories compare? | Horizontal bar | Easy to read long labels; ranks naturally |
| What makes up this total? | Stacked bar | Shows composition and total together |
| Which causes dominate? | Bar, sorted descending | The biggest cause is immediately visible |
| Is this above or below target? | Line with a target line | The gap is the message |
| One number, right now | No chart — just the number | A chart of one value adds nothing |
Charts to avoid
- Pie charts with more than four slices. Humans cannot compare angles accurately. Use a sorted bar.
- Dual-axis charts. Two different scales on one chart can be made to show almost any relationship, and readers rarely notice the axes.
- 3D anything. The perspective distorts the values it is meant to show.
- Charts with truncated axes. A bar chart starting at 85% makes a 2-point difference look enormous.
Truncated axes are the most common way a chart misleads without anyone intending it. Line charts may start above zero when showing variation in a narrow band — that is legitimate and should be labelled. Bar charts should almost always start at zero, because the bar's length is the comparison.
Before making a chart, write the sentence it is meant to communicate. If you cannot write that sentence, the chart has no point. If the sentence is complete on its own, use the sentence and skip the chart.
- Line for time, bar for categories, stacked bar for composition
- Avoid pies over four slices, dual axes, 3D and truncated bar axes
- If you cannot write the chart's sentence, do not make the chart
Trend versus snapshot
You will learn when to show a point in time and when to show movement.
| Snapshot | Trend | |
|---|---|---|
| Answers | Where are we now? | Which way are we going? |
| Good for | Live status, current exceptions | Performance, cost, quality |
| Risk | Mistaking a normal fluctuation for a problem | Slow to reflect a genuine change |
| Use for | Today's shipments, open exceptions | On-time, cost per unit, damage rate |
Almost every KPI needs a trend
A single-period KPI invites the wrong conclusion in both directions. One bad week looks like a crisis; one good week looks like success. The trend tells you which it was.
SNAPSHOT: "On-time this week: 85%" Reading: something has gone badly wrong. TREND: last eight weeks 95 90 100 85 95 90 92 85 Rolling 8-week average: 91.5% Target: 95% Reading: we have been running consistently below target for two months. This week is not unusual - it is normal, and normal is the problem. THE SNAPSHOT MAKES YOU INVESTIGATE THIS WEEK. THE TREND MAKES YOU INVESTIGATE THE LANE. In this case 14 of the 19 failures across the period were on one lane. Investigating "this week" would have found one shipment. The trend found the cause.
Show at least eight periods on any trend chart. Fewer than that and you cannot distinguish a trend from noise, which means the chart will be read as showing a pattern that may not exist.
- Snapshots suit live status; trends suit performance measures
- A single period invites wrong conclusions in both directions
- Show at least eight periods to distinguish trend from noise
Designing for a five-second read
You will learn the layout principles that make a dashboard usable.
- One screen, no scrolling. If it does not fit, it contains too much.
- Most important thing top-left. That is where eyes land first.
- Six to eight elements maximum. Beyond that, nothing gets attention.
- Every number needs a comparison. Against target, against last period, or both. A bare number means nothing.
- Colour only where it carries meaning. Red and amber for exceptions; everything else neutral. A dashboard where everything is coloured communicates nothing.
- Label directly. Values on the chart beat a legend the reader has to cross-reference.
+---------------------------+---------------------------+ | ON-TIME DELIVERY | COST PER SHIPMENT | | | | | 89% | GBP 2,411 | | target 95% v 94% lw | target 2,350 ^ 2.6% | | [8-week trend line] | [8-week trend line] | +---------------------------+---------------------------+ | LATE SHIPMENTS BY CAUSE (this month) | | | | Carrier transhipment ######################### 9 | | Customs examination ################ 6 | | Late documents ########## 4 | | Site closed ##### 2 | +--------------------------------------------------------+ | EXCEPTIONS NOW | FREE TIME EXPIRING <3 DAYS| | 3 shipments held | 2 containers | | [list with refs] | [list with refs and dates]| +---------------------------+---------------------------+ Six elements. Every number has a comparison. Colour used only on the two exception panels.
Building a dashboard containing everything that could be measured. It becomes a wall of numbers that nobody reads, and the genuinely urgent items are invisible among the routine ones. Six to eight elements, chosen deliberately.
- One screen, six to eight elements, most important top-left
- Every number needs a comparison to mean anything
- Reserve colour for exceptions only
Automating the refresh
You will learn what to automate and what to leave manual.
Automate the mechanical parts
- Pulling data from source systems
- Cleaning steps that are identical every time
- Calculations and pivots
- Chart updates
- Scheduled distribution
Keep the judgement manual
- The commentary explaining why numbers moved
- Deciding what counts as an exception this week
- The recommendation and the decision requested
- Checking the data is plausible before it goes out
An automated report with no human check will eventually send something wrong to everyone on the distribution list. Automation should assemble the report and stop, leaving a person to read it, write the commentary and press send. The five minutes that costs is what keeps it trustworthy.
A fully automated weekly dashboard runs for months. A source system changes a field name, and one metric begins returning zero. The dashboard reports 100% on-time performance for three consecutive weeks.
Nobody queries it, because good news is not questioned. The error surfaces when a client complains about repeated late deliveries that the dashboard says did not happen.
A human glancing at the report would have noticed that a genuinely perfect week, three times running, does not happen.
Add a plausibility check to any automated report: row count, total value, and a flag if any metric is exactly 0% or 100%. Perfect numbers in operations almost always mean broken data rather than perfect performance.
- Automate collection, cleaning, calculation and charting
- Keep commentary, exception judgement and the final check manual
- Flag suspiciously perfect numbers — they usually mean broken data
Module 4 review
You want to show which causes account for most delays. Which chart?
Sorted bars make the dominant cause immediately visible. Pies over four slices cannot be compared accurately by eye.
A dashboard reports 100% on-time three weeks running. What should you suspect?
Good news rarely gets questioned, which is exactly why it should be. Flag any metric returning exactly 0% or 100%.
How many periods should a trend chart show?
With fewer than eight periods a reader cannot tell a trend from normal fluctuation, and will see a pattern that may not exist.
Presenting to Managers and Clients
Standing behind your numbers, explaining a bad one, and turning a report into a decision.
Leading with the conclusion
You will learn to structure a presentation so the point arrives first.
The instinct is to walk through the analysis and arrive at the conclusion, the way you discovered it. Senior audiences find this exhausting, because they are holding everything in suspense waiting to learn what it means.
| Discovery order (weak) | Conclusion order (strong) |
|---|---|
| 1. Background and method | 1. The recommendation |
| 2. The data | 2. The number that supports it |
| 3. The analysis | 3. The evidence, briefly |
| 4. The findings | 4. What could change it |
| 5. The recommendation | 5. The decision needed, and by when |
WEAK OPENING "So I've been looking at the carrier performance data across the last quarter, and I pulled together the on-time figures alongside the cost per shipment, and what I did was segment that by lane, and then I cross-referenced the delay causes, and what's interesting is..." The listener has learned nothing and is now three sentences into holding their attention on faith. STRONG OPENING "We should move our Rotterdam volume to Carrier C from January. It costs GBP 1,260 more a year and would have prevented nine of our fourteen late shipments last quarter. I'll show you the numbers behind that, and then there's one decision I need from you by Thursday." The listener now knows what this is about, what is being asked, and can direct their attention. Everything after this is evidence, and they know what it is evidence FOR.
Assume you will be interrupted after ninety seconds and asked "so what do you want?" If your recommendation is at the end, you will have to skip to it clumsily. If it was the first thing you said, the interruption becomes a question you have already answered.
- Present in conclusion order, not discovery order
- State the recommendation, the number and the ask in the first thirty seconds
- Everything after is evidence, and the audience knows what it is for
Explaining a bad number
You will learn to present poor performance without losing credibility or making excuses.
- State it plainly, first"On-time was 89% against a 95% target. That is our worst quarter this year."
- Give the specific causeNot a general one. "Nine of fourteen failures were one carrier on one lane."
- Separate what you control from what you do notHonestly, in both directions.
- Say what has been done alreadyActions taken, not intentions.
- Say what you are asking forIf anything is needed from them.
- Give the recovery timelineWhen the number should improve, and how you will know.
"On-time delivery was 89% this quarter against our 95% target. That is the worst quarter we have had this year and I want to be direct about why. Fourteen shipments were late. Nine of those were a single carrier missing transhipment connections at one hub. Three were customs examinations, which are random and outside our control. Two were our own - documents requested too late in the process. What we have already done: escalated to the carrier's account manager on 19 November with no substantive response, and changed our process so customs data is collected at booking rather than on arrival. That second change addresses our own two failures. What I am asking for: approval to move Rotterdam volume to Carrier C from January. GBP 1,260 more per year. If we do that, I would expect on-time back above 94% by the end of February. I will report against that specifically each week so you can see whether it works."
Owning your own failures specifically — "two were ours, caused by our own process" — is what makes the rest credible. A presentation where nothing was your fault reads as defensive, and an experienced manager will discount the whole analysis accordingly.
Burying a bad number in the middle of a report, presented at the same weight as everything else, hoping it passes unnoticed. If it is noticed later, you are no longer discussing performance — you are discussing whether you were being straight, which is a far worse conversation.
- State bad numbers plainly and first
- Separate controllable from uncontrollable, honestly in both directions
- Owning your own failures is what makes the rest believable
Answering hard questions honestly
You will learn to handle challenges to your analysis without defensiveness or bluffing.
| Question | Good answer |
|---|---|
| "Where did this number come from?" | "Our job log, cross-checked against carrier invoices for the quarter. I can show you the working." |
| "That doesn't match what finance told me." | "Let me check both. If they differ, one of us has a scope difference and it's worth finding which." |
| "Are you sure about this?" | "I'm confident on the delay counts and causes. The cost projection assumes current rates hold, which is the weakest part." |
| "What if you're wrong?" | "If the new carrier performs like the old one, we've spent GBP 1,260 and learned something. That's the downside." |
| "Why didn't you spot this sooner?" | "We were tracking final ETA, not transhipment connections. That's now changed. It was visible in the data before we raised it." |
| Something you do not know | "I don't know. I'll find out and come back to you today." |
The rule
Never guess in front of a decision-maker. An invented answer that turns out wrong destroys the credibility of everything else you presented, including the parts that were right.
Saying "I don't know, I'll find out" is not weakness. It is the answer that protects every other number you have given. Analysts who never say it are eventually discovered to have been guessing, and then nothing they say is trusted.
Know which part of your analysis is weakest before you present, and say so unprompted. Naming your own uncertainty first is disarming, and it means nobody gets to discover it and present it as a flaw you were hiding.
- Never guess — one invented answer discredits everything else
- "I don't know, I'll find out today" protects your other numbers
- Name your analysis's weakest part before someone else does
Turning a report into a decision
You will learn to close the loop so analysis produces action.
Most reporting fails at the last step. The analysis is sound, the presentation is clear, and then nothing happens — because nobody was asked to decide anything specific.
What a decision request needs
- A single, specific proposalNot "we should look at our carrier mix" but "move Rotterdam volume to Carrier C from January".
- The cost or impactOne number.
- Who needs to decideNamed.
- By when, and why that dateTied to something real — a contract date, a space deadline.
- What happens if no decision is madeThe default, stated plainly.
DECISION REQUESTED
Proposal Move Rotterdam lane volume (est. 9 containers
Dec-Feb) from Carrier B to Carrier C.
Cost GBP 140 more per container. GBP 1,260 total
over the three months.
Rationale Carrier B: 71% on-time over 6 weeks, 9 late
shipments. Carrier C: 96% on-time, same lane,
direct routing.
Decision D Reyes, Operations Director
By Thursday 27 November - Carrier C needs
confirmation to allocate December space.
If no decision is made: volume stays with Carrier B and
we continue at current performance. I would then remove
the Rotterdam lane from our on-time target rather than
report against a number we cannot influence.
How we will know it worked: on-time on this lane reported
weekly from January. Expect above 94% by end of February.
The last line matters more than it looks. Committing in advance to how success will be measured means the decision gets reviewed rather than forgotten, and it means you will find out whether your own analysis was right — which is how analysts get better.
Stating the default if nobody decides is not passive-aggressive. It is how a decision-maker understands the cost of inaction, and it prevents the outcome where nothing is decided and everyone assumes something was.
- A decision request needs a proposal, a cost, an owner, a date and a default
- State how success will be measured, in advance
- Naming the default makes the cost of inaction visible
Module 5 review
How should you open a presentation of your analysis?
Conclusion order lets the audience direct their attention. Assume you will be interrupted after ninety seconds and asked what you want.
A manager asks something you do not know. What do you say?
One invented answer that proves wrong discredits everything else you presented, including the parts that were right.
Why state what happens if no decision is made?
Without a stated default, the common outcome is that nothing is decided while everyone believes something was.
Course Assessment
Twelve questions covering all five modules. You need 10 of 12 correct to meet the 80% pass mark. You can retake it as often as you like.
Complete all 25 lessons to unlock the final assessment.