Webbo3 Data Analytics · Cohort 2 · Excel Track · Pod Task, Week 4
Pod Task Week 4: Build the Verdant Foods Dashboard
One page that a Verdant Foods manager can read in thirty seconds and act on. Everything your pod has built so far exists to make this page true.
By the end of this class you can
- Build a KPI row from formulas
- Build three PivotCharts from the cleaned data, in one colour system
- Write chart titles that state findings, not field names
- Connect a Customer_Type slicer to every chart
- Write three insights in plain business language
Your Cleaning Log proves the data is right. Your Analysis tab proves you can answer questions. The dashboard is the only part most people will ever look at, so it has to carry all of that work in one glance.
The Brief
Pod Task Week 4: Build the Verdant Foods Dashboard
- Create a tab called Dashboard in the shared workbook.
- A KPI row at the top: Total Revenue, Total Transactions, Average Revenue per Transaction. Use formula boxes (ordinary cells with formulas), not PivotTables.
- PivotTable and chart 1: Revenue by Region, as a bar chart, sorted highest to lowest.
- PivotTable and chart 2: Revenue by Category, as a bar chart.
- PivotTable and chart 3: Revenue by Month, as a line chart showing the trend across the year.
- One slicer for Customer_Type (Retail and Wholesale), connected to all three PivotTables.
- One colour system across every chart: navy plus one accent colour.
- Every chart title states the finding, not the field name: "South West leads with 35% of total revenue", never "Revenue by Region".
- No 3D charts. No default Excel rainbow colours. No piece of information shown twice.
- A text box at the bottom with 3 insights from the data, one sentence each, in plain business language.
Hand in: A one-page Dashboard tab in the shared workbook. This is the pod's main Week 4 submission; Week 5 improves it from feedback.
Skills tested: PivotTables, PivotCharts, slicers, conditional formatting, dashboard design, data storytelling
"South West leads with 35% of total revenue" shows the shape of a good title. Your numbers come from your own PivotTables, and they will not be 35%.
What You Are Aiming For
Here is a finished dashboard built to the same brief, from TechPlug. It shows the layout and the standard without giving away a single Verdant Foods number.
| Brief line | How the model meets it |
|---|---|
| KPI row from formulas | Three cells: =SUM, =COUNTA, =AVERAGE, in large type with a navy top border |
| Three charts | Zone bar, Category bar, and a weekly line (TechPlug's workbook covers only three months; your Verdant data covers a year, so yours is monthly) |
| Titles state findings | "South West brings in 30% of revenue", "Laptops lead, with 32% of revenue", "The best week began 29 Jan, at N97.0m" |
| One colour system | Navy (#1f3864) for everything; the accent (#e8a33d) only on the leading bar and the peak week |
| One slicer | Customer_Type, top right, connected to all three PivotTables |
| Three insights | Each one compares, explains and suggests an action |
Building It, Step by Step
Step 1: The PivotTables Live on Their Own Sheet
Three PivotTables on a Pivots sheet
Your Revenue column has 20 empty cells on the Missing rows, so Excel may offer Count of Revenue. Change it to Sum in Value Field Settings, exactly as in Week 4, Live 2.
Step 2: The Charts Move to the Dashboard
PivotCharts onto the Dashboard tab
The charts stay connected to their PivotTables on the Pivots sheet, so the Dashboard shows only what the reader needs. On each bar chart, use Format Axis, Categories in reverse order, so the biggest bar is on top.
Step 3: The KPI Row
Leave the first rows of the Dashboard free for the KPIs. Each KPI is two cells: a big number with a formula, and a small label under it. The formulas read the Cleaned Data tab directly:
| KPI | Formula |
|---|---|
| Total Revenue | =SUM('Cleaned Data'!L2:L456) |
| Total Transactions | =COUNTA('Cleaned Data'!A2:A456) |
| Average Revenue per Transaction | =AVERAGE('Cleaned Data'!L2:L456) |
Make the numbers big (Font size 20 or more, bold, navy), add a thin top border, and use Comma Style. A KPI should be readable from across the room.
Step 4: Colour
Navy and one accent
Step 5: Titles, Slicer, Insights, and Tidying
Finishing touches
Writing the Three Insights
An insight is not a description of a chart. "Beverages is the biggest category" describes. "Beverages earns the most, so a stock-out in Beverages costs more than in any other category; it should be reordered first" is an insight: it compares, explains and suggests what to do. (That example is invented: check what your own data says.)
- One sentence each, readable in one breath.
- Use a number from the dashboard, so the reader can see where it came from.
- End with what it means for Verdant Foods: what to do, watch or ask.
The Rules, and Why
| Rule | Why |
|---|---|
| Titles state the finding | Most readers read the title and nothing else. Give them the answer there |
| One colour system | Colour should mean something. Five colours for five regions means nothing and distracts |
| No 3D | 3D bars make values look bigger or smaller depending on the angle. Flat is honest |
| No rainbow | Excel's default palette makes every bar shout. Navy with one accent lets one bar speak |
| No duplicate information | Every chart answers a different question; repeating one wastes space and attention |
| KPI boxes from formulas | They are always in sync with the data and easy to check in the formula bar |
Checkpoint
Q1. Why are the PivotTables on a separate Pivots sheet?
So the Dashboard shows only the charts and KPIs the reader needs.
Q2. A pod mate titles a chart "Revenue by Category". What do you suggest?
A title that states what the chart shows, for example which category leads and by how much.
Q3. The slicer changes one chart but not the others. Why?
It is not connected: use Report Connections.
Q4. What makes a sentence an insight rather than a description?
It compares, explains why it matters, and says what to do.
Tick Before You Move On
- ☐ A Dashboard tab with a KPI row built from formulas
- ☐ Region and Category bar charts, sorted, biggest bar on top
- ☐ A Month line chart in calendar order
- ☐ Every title states a finding
- ☐ Navy plus one accent, no 3D, no rainbow, nothing shown twice
- ☐ A Customer_Type slicer connected to all three PivotTables
- ☐ Three insights that compare, explain and suggest
- ☐ Gridlines hidden, everything fits on one screen
Quick Recap
A dashboard is the analysis in one glance: KPIs at the top, charts in the middle, insights at the bottom. PivotTables on their own sheet, PivotCharts on the Dashboard, one slicer connected to all. Titles state findings; one colour system; no 3D; no rainbow; nothing twice. Insights compare, explain and suggest.
Next, Week 5, Live 1: dashboard design principles, a bad dashboard against a good one, the most common mistakes from the Week 4 submissions, and KPI cells that turn green or red.