Analyze branch performance · MS-4004

By the end of this task, you will be able to move from a national average to a site-level finding, and turn that finding into a recommendation a sales leader can act on.

Your situation

Nestor Wilke said it plainly on the January call: he cannot act on a national average, and Calgary "drags the whole number down."

In Task 2 you looked at the account as one line. Now you break it apart. The branch data covers five distribution centres across twelve months — 60 rows. One of them has been below target every single month.

Important

Deliverable: a short analytic summary naming the site that needs intervention, what the evidence shows, what it does not establish, and the recommended next step.

Files you'll use

File	What's in it
CanadaPost_Branch_Performance.xlsx	5 distribution centres × 12 months: volume, on-time %, cost, returns %, satisfaction
CanadaPost_Branch_Performance.csv	The same data as CSV
CanadaPost_Parcel_Performance.xlsx	The national roll-up, for comparison
CanadaPost_Account_Research_Notes.txt	Context on what has already been escalated, and by whom

Note

Branch volumes sum exactly to the national totals, so you can reconcile the two files against each other.

How to read the prompts

Each prompt below is one block you can copy whole. The # lines are labels, not instructions — they mark where the prompt stops setting the goal and starts giving the context, the sources, and the expectations. Copying them along with the prompt does no harm.

Those four moves, in that order, are what makes a prompt work. Use the same shape when you write your own.

Steps

Open CanadaPost_Branch_Performance.xlsx in Excel and open the Copilot pane.
Start by ranking the sites. Do not assume you know which one is the problem.
Prompt t5-a
goal
Rank the five distribution centres on on-time delivery across the twelve months in this workbook.
context
The customer’s requirement is 95% on-time, so I need to know which sites meet it and which do not.
sources
Use only this workbook.
expectations
Give me a table of site, province, average on-time, best month, worst month, and how many of the twelve months fell below 95%. Sort worst first and give me the figures.
One site should be clearly worst. Now find out whether it is a volume problem or something else.
Prompt t5-b
goal
Test whether the worst site’s on-time performance is explained by its parcel volume.
context
The easy assumption is that the busiest site struggles most, and I need to know whether that is actually true here before I say it to a customer.
sources
Use only this workbook.
expectations
Compare each site’s volume share against its on-time average and say whether volume explains the pattern. If it does not, say what the data rules out - and be clear that ruling things out is not the same as finding a cause.
Check whether the problem is getting better or worse. A site that is recovering needs a different conversation from one that is not.
Prompt t5-c
goal
Show me the trend for the worst-performing site over the twelve months.
context
I need to know whether this is deteriorating, stable, or recovering, and whether the two national failure months show up there too.
sources
Use only this workbook.
expectations
Give me month-by-month on-time, returns rate, and satisfaction for that site, one sentence on the direction of travel, and the months that are worse than the site’s own average.
Ask Copilot to build the chart that proves the point.
Prompt t5-d
goal
Create a chart of on-time delivery by site across the twelve months, with the 95% requirement marked.
context
This goes into a customer briefing, so it has to make the gap obvious in about three seconds.
sources
Use the data in this workbook.
expectations
One chart, five series, one per site, plus a horizontal line at 95%. Tell me which chart type you used and why, and keep the axis starting somewhere that does not exaggerate the difference.
Turn it into the recommendation.
Prompt t5-e
goal
Write a short analytic summary for my sales manager.
context
She needs to decide whether to commit account-team time to a site-level service recovery before the March review.
sources
Use the analysis in this workbook and the account notes.
expectations
Four short sections: what the data shows, what it does not establish, what doing nothing would cost us, and the one thing I recommend. Under 250 words, with no causal claims.
Save the summary. Tasks 6 and 7 both use it.

Check your work

You ranked all five sites before drawing a conclusion.
You tested the volume explanation instead of assuming it.
The summary says what the data rules out, not what caused the problem.
Your chart's axis does not exaggerate the gap.
The recommendation names one action, not five.

Stretch prompts

Starting out:

Summarize this branch performance data: which site performs
best, which performs worst, and by how much.

Comfortable:

Reconcile this branch workbook against the national parcel
performance file. Confirm the branch volumes sum to the
national totals each month, then tell me how much of the
national on-time shortfall comes from a single site.

Confident:

Give me three plausible explanations for the worst site's
performance that are consistent with this data, and for each
one, the specific evidence I would need to confirm or rule it
out. Rank them by how quickly I could check them.

Tip

Step 3 is the discipline that separates analysis from storytelling. The obvious explanation is often wrong, and testing it takes one prompt.