Thomas Alfa

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a channel performance table for an investor deck showing which acquisition channels bring the most valuable customers. The table had to show customer count, average total orders, and average lifetime value per channel, sorted by volume.

What I did

To produce accurate channel-level figures, I used COUNTIFS, AVERAGEIFS, and SUMIFS, each filtering on both the channel column and is_duplicate = FALSE, so the 200 duplicate customer records were excluded from every metric. Sorting channels by customer count descending placed online at the top, followed by in-store and marketplace. I applied a naira currency format to the average lifetime value column and added a total row using SUM and AVERAGE to give the investor a quick cross-channel view.

Outcome

Chidi received a clean, formula-driven table ready to drop into the investor deck, with duplicate-excluded counts that match the dataset's verified total.

Next step

A useful next cut would be to break average lifetime value down by channel and acquisition year, to see whether more recent cohorts in each channel are trending up or down.

Problem

Before any analysis of the Meridian Retail (simulated retailer) dataset could begin, the brief asked me to document what was in the file and flag data quality problems that could corrupt downstream results. The deliverable was a Data Audit sheet with at least three flagged issues, each supported by a live formula.

What I did

Rather than browsing visually, I used live COUNTIF and COUNTA formulas so every count could be reproduced and updated if the source data changed. For duplicate customers I wrote =COUNTIF('Customers'!I2:I2332,"True") directly in the audit sheet, which returned 200. Negative stock quantities in the Inventory sheet were caught with =COUNTIF(Inventory!G2:G886,"<0"), returning 66. For the legacy_order_ref column, =COUNTA(Orders!O2:O1850) returning 0 confirmed it was entirely empty, making it safe to exclude from any join.

Outcome

The audit sheet gave any subsequent analyst a documented, formula-backed checklist of issues to address before building any summary or model from the Meridian data.

Next step

The inconsistent category formatting between Orders and Order_Items was flagged but not resolved; a follow-up task would be to build a mapping table that standardises both formats before any category-level aggregation is run.

Problem

Maria Castellanos at Pitchside Soccer Co. (simulated sports retailer) needed a baseline demand forecast before the 2026 tournament, built from 2018 and 2022 historical sales data. The key question was which teams to prioritise for stock allocation, and which had shown enough volatility in past tournaments to warrant caution.

What I did

Forecasts were anchored to group-stage dates only, filtering 2018 data to June 14-28 and 2022 data to November 20 - December 2, then averaging daily units per team across those windows. For the 2026 group stage of 17 days, each team's daily average was projected forward directly. The four 2026 debutants (Cape Verde, Curacao, Jordan, Uzbekistan) received the Tier 4 historical average of 532 daily units, as instructed, and teams with only one year of history were handled with explicit rules: 2022-only teams used the 2022 run-rate, 2018-only teams received a tier-specific growth factor, and host nations received a 20 percent uplift. A Methodology sheet documents all these assumptions so the forecast is auditable.

Outcome

Maria received a ranked Top Teams sheet and a bar chart identifying the highest-demand teams for Friday's meeting, along with a volatility flag column so the buying team could see which forecasts carry the most uncertainty.

Next step

The forecast treats all group-stage days as equal, but host-city scheduling means some teams play more games in high-footfall venues; adding a venue-weight factor to the daily unit projection would make the model more precise.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed to explain to investors why three internal teams reported different customer counts. Marketing, operations, and finance each used a different definition, and the task was to replicate all three counts in Excel and document why they diverge.

What I did

Three formulas each reflect a different counting rule. COUNTA on the customer ID column captures every row regardless of status, giving the marketing total. A COUNTIF filtering on is_duplicate = FALSE gives the operations figure after deduplication. Adding a second condition to COUNTIFS, requiring total_orders greater than zero, narrows the result to finance's definition of an active customer. The summary table at B22:D24 pulls all three values via live cell references rather than hardcoded numbers, so any source-data change flows through automatically.

Outcome

Chidi received a single table showing all three counts with written explanations of the logic behind each, giving him a clear narrative for the investor presentation without needing to choose one number over another.

Next step

The 26 customers counted by operations but not finance, those with no orders, could be segmented further by acquisition channel to see whether one channel produces a disproportionate share of zero-order signups.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) wanted to verify that the system's duplicate flag matched observable name patterns in the data, and to quantify how many name matches were coincidental rather than true duplicates. The deliverable was a Duplicate Summary sheet with five specific metrics and helper columns on the Customers sheet.

What I did

A Name Match Count column was added using a structured table reference, =COUNTIF(Customers[full_name],Customers[[#This Row],[full_name]]), applied across all 2,331 rows to count how many times each name appears in the full dataset. A Verified Duplicate column then applied a logical AND: it returns TRUE only when Name Match Count is greater than 1 and is_duplicate is already TRUE. Conditional formatting flags rows where Name Match Count exceeds 1 in red. For the summary sheet, the unflagged name match count was derived through a GETPIVOTDATA subtraction rather than a hardcoded figure, keeping every metric live.

Outcome

Chidi received a five-metric summary distinguishing confirmed duplicates from coincidental name matches, giving the investor presentation a defensible basis for the 2,131 valid customer count.

=COUNTIF(Customers[full_name],Customers[[#This Row],[full_name]])

This structured table reference counts every instance of the current row's name across the entire full_name column, producing a frequency that the Verified Duplicate column then cross-checks against the system flag.

Next step

The 240 unflagged name matches were quantified but not investigated further; checking whether they share additional attributes such as postcode or phone number would determine how many might be true duplicates the system missed.