AMOLEGBE OLADIMEJI

Problem

Nura Ibrahim at Meridian Retail needed a three-month view of revenue split by order status to judge whether October cancellations were a trend or an isolated event. The deliverable had to show October, November, and December 2023 side by side, with a chart that made the monthly pattern immediately visible.

What I did

A PivotTable on a new Status Breakdown sheet placed order status on rows and month-grouped order dates on columns, with the sum of total_naira as the value field. All seven statuses appear, including the Grand Total row, and every value cell carries the naira currency format consistently. To give Nura the visual comparison she needed, I added a stacked bar chart referencing the pivot data directly, so each bar shows one month divided into coloured segments by status.

Outcome

Nura could see at a glance whether the cancellation share grew, shrank, or stayed flat across Q4 2023, without any manual data manipulation on her side.

Next step

Adding a calculated field for cancellation rate as a percentage of each month's grand total would make the trend easier to quantify than reading bar segment heights visually.

Problem

Funmi Alade, Head of Customer Experience at FlowRoute Logistics (simulated logistics company), needed a headline complaints analysis her team could maintain without manual updates. The output required a total complaint count, a category breakdown sorted by frequency, and a single sentence telling her which category to lead with at the board.

What I did

On a dedicated Complaints Summary sheet, a COUNTA formula against the structured Table1[complaint_id] column produced the total as a live figure. COUNTIF formulas against Table1[category] generated counts for each of the four categories, and each percentage divides its COUNTIF result by the locked denominator in B4. Sorting the categories from most to least common was done in the output range rather than the source data, preserving the original table. The board narrative in A10 names both the total and the leading category directly.

Outcome

Funmi received a summary sheet with fully live formulas that will update automatically when new complaints are added, plus a ready-to-use board sentence requiring no editing.

=COUNTA(Table1[complaint_id])

Referencing the structured table column by name rather than a fixed range means the total updates automatically if rows are added to the Complaints sheet.

Next step

Breaking the category counts down by destination hub would show whether lost-item complaints concentrate in specific locations or spread evenly across the network.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed the October 2023 revenue figure verified against the Finance system. The specific question was how much of gross revenue disappeared once cancelled, returned, and refunded orders were removed, and by how much those two figures differed.

What I did

On a dedicated Oct Revenue Analysis sheet, I built three formulas rather than typing in results. The gross revenue cell uses SUMIFS against Table1[total_naira] filtered to October 2023; the net revenue cell chains three separate criteria pairs to exclude each of the three problem statuses individually. Subtracting one from the other in a live formula meant any upstream data refresh would carry through automatically. Throughout, I referenced structured table columns rather than raw column letters to keep the formulas readable and maintainable.

Outcome

Nura received a single sheet with three live, formula-driven figures formatted in naira that she could hand directly to the Finance team for cross-checking without any manual edits.

=SUMIFS(Table1[total_naira],Table1[order_date],">="&DATE(2023,10,1),Table1[order_date],"<="&DATE(2023,10,31),Table1[status],"<>cancelled",Table1[status],"<>returned",Table1[status],"<>refunded")

This net revenue formula chains three not-equal criteria pairs inside a single SUMIFS call, isolating only the statuses Nura wants to count without requiring a helper column.

Next step

A useful next step would be to extend the same gross-versus-net comparison across all twelve months so Nura can see whether the cancellation gap is seasonal or consistent throughout the year.

Problem

Maria Castellanos at Pitchside Soccer Co. (simulated sports merchandise retailer) needed a group-stage demand forecast for all 48 qualified 2026 teams before the tournament began. The question covered how to baseline four first-time qualifiers, which ten teams posed the highest stock risk, and which teams had shown unpredictable demand across the 2018 and 2022 tournaments.

What I did

SUMIFS formulas filtered each tournament's group stage using DATE() boundaries rather than hard-coded serials, then divided by 15 and 13 days respectively to produce per-team daily averages. For the 2026 projection I multiplied each team's average by 17 days. The debutant baseline uses AVERAGEIFS constrained to Tier 4 teams that actually have historical data, avoiding the distortion that would come from averaging in other debutants. Volatility in column AB is flagged using ABS(R-P)/MIN(P,R), which measures the spread relative to the smaller of the two years rather than an arbitrary fixed value, and a RANK plus COUNTIF combination in column AC prevents duplicate rankings in the top-ten output.

Outcome

Maria received a ranked top-ten list of highest-demand teams and a volatility flag column that highlighted which teams had swung more than 50 percent between tournaments, giving the buying team a clear priority list before stock commitments were made.

Next step

The knockout-stage projection is still missing: extending the model to cover the 16 advancing teams across knockout rounds would complete the full-tournament demand picture Maria flagged as the next piece of work.

Problem

Funmi Alade at FlowRoute Logistics needed to know which of the company's hubs should concern her most. The task required combining delivery rate data from one sheet with live complaint counts from another and normalising complaints by shipment volume so that busier hubs were not unfairly penalised.

What I did

A new Hub_Performance_Analysis sheet pulls hub attributes using XLOOKUP with absolute references against the Hub_Performance source range, keeping the lookup stable if rows shift. Complaint counts come from COUNTIF against the named Complaints table's destination_hub column, so the figures stay live rather than becoming stale pasted values. The complaints-per-1,000 calculation divides each hub's own total_shipments into the count, which controls for volume as Funmi requested. Sorting the table worst delivery rate first placed the most concerning hubs at the top without hiding the full picture.

Outcome

Funmi received a ranked, formula-driven table she could filter or extend as new complaint data arrived, along with an honest one-line verdict grounded in the actual spread of the numbers.

Next step

Splitting the complaints-per-1,000 rate by complaint category per hub would reveal whether certain hubs have a specific failure mode, such as a high lost-item rate, even if their overall rate looks average.