Oladipupo Samuel Gabriel

Problem

Maria Castellanos at Pitchside Soccer Co. (simulated sports retailer) needed a baseline demand forecast before the 2026 tournament. The question covered 48 qualified teams: project each team's expected group-stage unit sales forward from 2018 and 2022 historical data, handle four debutants who have no history, and flag teams whose year-on-year demand had been volatile.

What I did

The forecast structure used per-team average daily units from the group-stage windows of both prior tournaments, then multiplied the combined daily average by the 17-day 2026 group stage to get projected units. For the four debutants, the brief called for the Tier 4 team average as the baseline, so I calculated that group mean and applied it to Cape Verde, Curacao, Jordan, and Uzbekistan. Volatility flags were added for teams where the 2018 and 2022 figures differed by more than 50 percent, a condition that surfaced teams with zero-data years as genuinely unpredictable. The deliverable was split across four sheets: Team_Forecast, Top_Teams, Notes, and the originals, keeping raw data separate from outputs.

Outcome

Maria received a ranked list of the ten highest-exposure teams and a volatility flag column identifying where historical inconsistency makes the forecast less reliable, both inputs for pre-tournament stock decisions.

Next step

The submission was missing 15 teams and all values appeared hardcoded rather than formula-driven; rebuilding the Team_Forecast sheet with live AVERAGEIFS formulas referencing the historical_demand table would make the model updatable and cover the full 48-team set.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a PivotChart showing monthly customer acquisition as the centrepiece of an investor presentation slide. The chart had to distinguish new non-duplicate customers per month from the running cumulative total, using Meridian brand colours.

What I did

To count only non-duplicate customers in the PivotTable, I added a helper column to the Customers sheet using an IF formula: if is_duplicate equals FALSE the row scores 1, otherwise 0. Summing that column in the PivotTable then naturally filtered out duplicates without needing a manual slicer. The cumulative total was built as a running formula in column C beside the PivotTable output. For the chart, I set the monthly new-customer series as clustered bars and the cumulative series as a line on a secondary axis, applied orange bars and a navy line to match the Meridian palette, and added data labels to each bar for immediate readability.

Outcome

Chidi received a self-contained, branded chart sheet with accurate monthly and cumulative figures that could be pasted directly into the investor presentation.

Next step

Breaking the monthly acquisition bars down by acquisition channel would show whether certain months spiked because of a specific channel, giving Chidi a more precise story for the investor Q and A.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) asked for a structured familiarisation exercise on the Meridian_Data.xlsx file before any formal analysis began. The deliverable was a Data Audit sheet flagging at least three data quality issues found through direct inspection of the file.

What I did

Working through each sheet systematically, I documented issues by examining column statistics, distinct value counts, and data types. The category format inconsistency stood out immediately because the distinct value count for Order_Items Category was 17, far more than the number of actual product categories, pointing to mixed formats such as Womens Footwear alongside womens-footwear. Duplicate customer rows and negative stock quantities were confirmed against the is_duplicate flag and the Inventory column minimum respectively. Beyond the minimum requirement, I built a separate Pivot Analysis sheet with seven pivot tables and six charts covering monthly revenue, order status distribution, regional channel mix, and KPI metrics including gross revenue, net revenue, and a leakage rate.

Outcome

Nura received a documented audit sheet that names specific columns and mechanisms for each issue, plus a bonus Pivot Analysis sheet that demonstrated the data was usable for revenue and channel reporting once quality issues were controlled.

Next step

The category format inconsistency was flagged but not resolved; a follow-up step would be building a standardisation lookup that maps each of the 17 observed variants to its canonical form, which would unblock any analysis that groups by product category.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed the October 2023 revenue figure calculated in Excel to cross-check against the Finance system. The specific question was: how much separates gross revenue, which counts all orders, from net revenue, which strips out cancelled, returned, and refunded orders? Both figures needed to be produced with live formulas and formatted in naira.

What I did

To isolate October, I used date boundaries of DATE(2023,10,1) and DATE(2023,11,1) as the criteria in SUMIFS against the order_date column, which avoided any partial-month leakage. Net revenue was built as a subtraction pattern: start with the gross SUMIFS result, then subtract three separate SUMIFS calls each filtering for one cancellation status. Rather than hardcoding the gap, I wrote the B4 cell as a live formula referencing B2 and B3, so any upstream correction would propagate automatically. All three output cells carry the NGN #,##0.00 number format to meet the thousand-separator requirement.

Outcome

Nura received a single-sheet summary with three live, auditable figures that matched the Finance system benchmark exactly, giving her a reliable cross-check without manual recalculation.

B2: =SUMIFS(Orders!$I:$I,Orders!$P:$P,">="&DATE(2023,10,1),Orders!$P:$P,"<"&DATE(2023,11,1))
B3: =SUMIFS(Orders!$I:$I,Orders!$P:$P,">="&DATE(2023,10,1),Orders!$P:$P,"<"&DATE(2023,11,1))
 -SUMIFS(Orders!$I:$I,Orders!$P:$P,">="&DATE(2023,10,1),Orders!$P:$P,"<"&DATE(2023,11,1),Orders!$Q:$Q,"cancelled")
 -SUMIFS(Orders!$I:$I,Orders!$P:$P,">="&DATE(2023,10,1),Orders!$P:$P,"<"&DATE(2023,11,1),Orders!$Q:$Q,"returned")
 -SUMIFS(Orders!$I:$I,Orders!$P:$P,">="&DATE(2023,10,1),Orders!$P:$P,"<"&DATE(2023,11,1),Orders!$Q:$Q,"refunded")
B4: =B2-B3

B2 captures all October orders using date boundaries; B3 applies the same window but subtracts each problematic status in turn; B4 keeps the gap live so any edit to B2 or B3 flows through.

Next step

A useful follow-on would be breaking the NGN 18,426,410.13 gap down by order status to see whether cancellations, returns, or refunds account for the larger share, since each may have a different operational cause.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed to explain why Marketing, Operations, and Finance each reported a different customer count. The task was to replicate all three counting methods in Excel so Chidi could show an investor why the figures legitimately differ rather than flagging a data error.

What I did

Each methodology required a different counting function. COUNTA across the customer ID column produced Marketing's all-rows figure. Filtering for is_duplicate equal to FALSE with COUNTIF gave Operations the post-deduplication count. For Finance, I added a second criterion to that same COUNTIF structure, turning it into a COUNTIFS that also required total_orders greater than zero, isolating customers who have actually transacted. A labelled summary table with a Reason for Differences section translated the three formulas into plain language that would hold up in a presentation context.

Outcome

Chidi received a clearly structured, single-sheet summary that explained each count with its formula and rationale, ready to be dropped into the investor slide without further editing.

B4: =COUNTA(Customers!A2:A2332)
B5: =COUNTIF(Customers!I2:I2332,FALSE)
B6: =COUNTIFS(Customers!I2:I2332,FALSE,Customers!H2:H2332,">0")

The three formulas stack from broadest to narrowest, each adding one condition, which makes the logic of the differences immediately visible to a non-technical reader.

Next step

A useful extension would be segmenting the 2,105 active customers by acquisition month to see whether the duplicate rate or the zero-order rate is higher in specific cohorts.