Webbo3 Data Analytics · Cohort 2 · Excel Track · Pod Task, Week 3
Pod Task Week 3: Analyse the Verdant Foods Data
Your pod puts a value on every transaction it can, then answers eight questions a Verdant Foods manager would actually ask, each with a visible formula and one sentence on what it means.
By the end of this class you can
- Add a Revenue column that leaves the flagged transactions out honestly
- Write formulas on one sheet that read data from another
- Answer eight business questions with SUM, SUMIFS, COUNTIFS and AVERAGEIFS
- Keep every formula visible next to its answer
- Write a one-sentence business meaning for every answer
Last week your pod made the data trustworthy. This week it makes it useful. By Friday a manager should be able to open your Analysis tab and understand Verdant Foods' year without asking you a single question.
The Brief
Pod Task Week 3: Analyse the Verdant Foods Data
- Add a Revenue column to the Cleaned Data tab: Quantity times Unit_Price, left empty for every row whose Quantity_Flag is Missing.
- Q1. What is the total revenue for the whole company? (SUM)
- Q2. What is the total revenue of each of the 5 regions? (SUMIFS)
- Q3. What is the total revenue of each of the 4 categories? (SUMIFS)
- Q4. How many transactions did each of the 8 sales reps make? (COUNTIFS)
- Q5. What is the average revenue per transaction for Retail and for Wholesale customers? (AVERAGEIFS)
- Q6. Which product brought in the most revenue? (SUMIFS for each of the 10 products, then compare)
- Q7. What share of total revenue came from Wholesale customers? (SUMIFS divided by SUM)
- Q8. How many transactions could not be valued because Quantity was missing, and what share of all transactions is that? (COUNTIF and COUNTA)
- Next to every answer, write one sentence: what does this number mean for the business?
Hand in: An Analysis tab in the shared workbook with all eight answers, every formula visible, and a meaning sentence for each.
Skills tested: The Revenue formula, SUMIFS, COUNTIFS, AVERAGEIFS, cross-sheet references, business interpretation
Step 1: The Revenue Column
The Cleaned Data tab ends at column K, Quantity_Flag. Revenue goes in L. The obvious formula, =G2*H2, has a trap: when Quantity is blank, Excel treats it as 0, and the transaction gets a revenue of 0. A transaction with an unknown quantity did not earn nothing; it earned an unknown amount. So the formula checks the flag first and leaves the cell empty:
Why the Empty Cell Matters
Both versions give the same total, because adding 0 changes nothing. Averages are where they part. With =G2*H2, the average revenue per transaction comes out at N186,436.92, because twenty zeros are averaged in. With the IF version, AVERAGE skips the empty cells and gives N195,008.74, the true average of the 435 transactions that can be valued. Your Q5 answers depend on getting this right.
Check figure: =SUM(L2:L456) must give N84,828,800, and =COUNT(L2:L456) must give 435. If they do, your Week 2 cleaning and your Revenue column are both right.
Step 2: The Analysis Tab, and Formulas That Read Another Sheet
Add a new tab called Analysis after Cleaned Data. The answers live here; the data stays on Cleaned Data. A formula on one sheet can read another by putting the sheet's name and an exclamation mark in front of the range. Because "Cleaned Data" has a space in it, the name goes in single quotes:
=SUM('Cleaned Data'!L2:L456)
You rarely need to type that. Start the formula, then click the Cleaned Data tab and select the range with the mouse, and Excel writes the reference for you. Here are the two check figures from Step 1, worked out on the Analysis tab:
Keeping Formulas Visible
The brief says every formula must be visible. There are two ways, and the second is better:
- Formulas > Show Formulas (or Ctrl + `) switches the whole sheet to show formulas instead of answers. Useful for checking, but then nobody sees the answers.
- FORMULATEXT(cell) returns the formula in a cell as text. Put it in the column next to each answer, as in column C above, and the reader sees both at once. It updates if you change the formula.
Picture a teacher's marked script with the working written in the margin next to every answer. The answer earns the mark; the working earns the trust.
Step 3: The Eight Questions
A Layout That Reads Like a Report
Give each question its own block on the Analysis tab, with a bold heading, a small table, and the meaning sentence underneath. For the questions with several answers (Q2, Q3, Q4, Q6) type the list of names in one column and write one formula in the first row that refers to the name beside it, then fill it down, exactly like the region table in Week 3, Live 2.
| Question | Rows in the block | Formula pattern (first row) |
|---|---|---|
| Q1 | 1 | =SUM('Cleaned Data'!L$2:L$456) |
| Q2 | 5 regions, plus a total | =SUMIFS('Cleaned Data'!L$2:L$456,'Cleaned Data'!C$2:C$456,A10) |
| Q3 | 4 categories, plus a total | The same, with the Category column, F |
| Q4 | 8 reps, plus a total | =COUNTIFS('Cleaned Data'!I$2:I$456,A30) |
| Q5 | Retail and Wholesale | AVERAGEIFS with Revenue first and Customer_Type, J, as the condition |
| Q6 | 10 products | SUMIFS with Product_Name, E; then sort the block largest to smallest |
| Q7 | 1 | SUMIFS for Wholesale divided by the Q1 cell; format as a percentage |
| Q8 | 2: the count and the share | COUNTIF on Quantity_Flag, K, for "Missing"; then divide by Q1's transaction count |
The cell references in the pattern column (A10, A30) are examples: use whichever cell holds the first name in your own block.
Built-In Checks
| Check | Must be true |
|---|---|
| Q2: the five regions | Add up to the Q1 total, N84,828,800 |
| Q3: the four categories | Add up to the Q1 total, N84,828,800 |
| Q4: the eight reps | Add up to 455 transactions (every transaction has a rep, valued or not) |
| Q6: the ten products | Add up to the Q1 total, N84,828,800 |
| Q5: the two averages | Both lie between the smallest and the largest single Revenue in column L |
| Q8: the count | Matches the Missing count from your Week 2 Cleaning Log |
Put a total row under every block and compare it with the figure above. A block that does not add up has a misspelt name or a range that stops short, and it is far better that you find it than your tutor.
Meaning Sentences
Here is Q8 worked in full, because its answer is one you already know from Week 2: 20 transactions out of 455.
That one sentence compares (a share of all transactions), says why it matters (the totals are understated) and recommends something. Aim for the same in all eight.
Ranges that stop at row 455 instead of 456 (the last transaction is in row 456). Forgetting the $ on the data ranges before filling a block down. Using =G2*H2 for Revenue, which drags the averages down. Typing an answer instead of a formula. A meaning sentence that only repeats the number.
Checkpoint
Q1. Why is Revenue left empty, not 0, when Quantity is missing?
The revenue is unknown, not zero. A 0 would be averaged in and pull every average down.
Q2. Why is 'Cleaned Data' in single quotes in a formula?
Because the sheet name contains a space.
Q3. Q4's eight counts add up to 455, but Q2's regions add up to revenue from only 435 transactions. Is that a mistake?
No. Every transaction has a rep, so Q4 counts all 455. Only the 435 with a known quantity have revenue.
Q4. How do you show a formula and its answer side by side?
FORMULATEXT in the next column.
Tick Before You Move On
- ☐ Revenue is in column L, empty on the 20 Missing rows
- ☐ =SUM(L2:L456) gives N84,828,800 and =COUNT(L2:L456) gives 435
- ☐ The Analysis tab answers all eight questions with formulas, not typed numbers
- ☐ Every formula is visible next to its answer
- ☐ Every block has a total row, and it matches
- ☐ Every answer has a meaning sentence that compares and explains
Quick Recap
Revenue respects the flag: unknown stays empty, so averages stay honest. Formulas can read another sheet; quotes are needed when the sheet name has a space. One formula filled down per block, with anchored ranges and a total row to check it. FORMULATEXT keeps the working visible next to every answer. A meaning sentence turns each number into something a manager can act on.
Next, Week 4, Live 1: VLOOKUP, why it breaks, and XLOOKUP, which fixes it. Pulling a value from one table into another.