Mozeed Arogundade

Problem

Nura Ibrahim, a finance stakeholder at Meridian Retail (simulated retailer), needed October 2023 revenue calculated in Excel to cross-check against the internal finance system. The specific requirement was to show both gross revenue across all orders and net revenue after removing cancelled, returned, and refunded orders, then surface the gap between them.

What I did

The gross SUMIFS formula filters the total_naira column to rows where order_date falls within October 2023 using a DATE boundary pair, which avoids any ambiguity around partial months. Extending that formula with three additional criteria pairs, each checking that status does not equal one of the three excluded values, produces the net figure using SUMIFS AND logic so a row must pass every condition to be counted. The gap is a straightforward subtraction in B5, keeping the dependency chain visible. All three cells are formatted with the Nigerian Naira currency symbol and thousand separators as Nura specified.

Outcome

Nura received a labelled sheet with three reconcilable figures she could present directly alongside the finance system output, with the source of the gap made explicit.

Next step

A natural follow-on would be to run the same gross-to-net comparison across every month in the dataset to see whether October's cancellation and return rate is typical or elevated.

Problem

Funmi Alade, Head of Customer Experience at FlowRoute Logistics (simulated logistics company), needed a summary of the full complaints record built in Excel so her team could keep it updated. The deliverable required a total complaint count, a live percentage breakdown by category sorted from most to least common, and a single cell identifying the category to lead with at the board.

What I did

A PivotTable on the Summary sheet delivers both the count and percentage columns for each category, sorted descending from most to least common, satisfying the requirement for live rather than typed-in numbers. The total of 64,737 complaints is presented as a single labelled figure in rows 13 and 14. A board headline cell in F4 states the leading category explicitly, naming lost with its count of 16,551. Using a PivotTable rather than manual COUNTIFS formulas means Funmi's team can refresh the breakdown as new complaint records are added.

Outcome

Funmi received a refreshable summary sheet with all three required elements ready to present, and the board headline cell removed any ambiguity about which category to address first.

Next step

Breaking down the lost-shipment complaints by destination hub would show whether the problem is concentrated in specific locations, which would make the board recommendation more actionable.

Problem

Kemi Oyedepo, Financial Controller at FlowRoute Logistics (simulated logistics company), extracted every shipment carrying a cash-on-delivery amount and asked me to find the ones that should not have one. The specific questions were: how many phantom rows exist, what is their naira total, and is the problem still active at the end of the data window?

What I did

A helper column in column G of the COD_Shipments sheet holds a live flag for every row where the payment method is not cod, giving every downstream formula a single reliable reference range. All counts and totals on the Audit sheet pull from that flag column via COUNTIFS and SUMIFS, so updating the source data automatically refreshes the audit. The kobo-to-naira conversion is applied directly inside the SUMIFS formula by dividing the raw sum by 100, keeping the conversion logic in one place. To confirm the numbers were internally consistent, I placed self-audit cells in E9 and F9 using equality checks that return TRUE when the breakdown totals match the headline figures.

Outcome

Kemi received a committee-ready workbook with a 13-month trend table, a breakdown by payment method showing the prepaid, credit, and wallet split, and a written findings section answering all three business questions.

Next step

A useful next step would be to compare the phantom rate by destination hub to see whether the data entry problem is concentrated in specific locations rather than spread evenly across the operation.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a ranked breakdown of customer counts by acquisition source ahead of an investor conversation about acquisition strategy. The requirement was to exclude duplicate customer records and sort channels by volume.

What I did

Filtering with WHERE is_duplicate = FALSE removes flagged duplicates before any aggregation happens, which is the correct place to apply that condition. Rather than a plain COUNT(*), I used COUNT(DISTINCT customer_id) as a second layer of protection against any accidental row duplication in the result set. GROUP BY acquisition_source combined with ORDER BY customer_count DESC produces the ranked table Chidi needed in a single pass. The query returns 10 channels across 2,131 non-duplicate customers.

Outcome

Chidi received a clean, sorted channel table ready to reference in the investor conversation, with duplicate records excluded and counts verified as distinct.

SELECT
 acquisition_source,
 COUNT(DISTINCT customer_id) AS customer_count
FROM Customers
WHERE is_duplicate = FALSE
GROUP BY acquisition_source
ORDER BY customer_count DESC;

The WHERE clause removes duplicate rows before aggregation, COUNT(DISTINCT customer_id) guards against any remaining row duplication, and ORDER BY DESC delivers the ranked output the stakeholder needed.

Next step

It would be worth breaking down each channel by customer activity, specifically comparing average total orders per channel, to see whether high-volume acquisition sources also produce engaged customers.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed to explain why marketing, operations, and finance each reported a different customer count from the same dataset. The task was to replicate all three counting methodologies in Excel using live formulas and present the results in a table Chidi could show an investor.

What I did

COUNTA on the customer_id column captures every row with any value, reproducing marketing's all-signups figure. COUNTIF on the is_duplicate column filtered to FALSE removes the duplicate records, giving operations' deduplicated count. Adding a second condition to COUNTIFS requiring total_orders greater than zero filters further to customers who have actually transacted, which is how finance defines an active customer. Explanatory notes in columns E through G spell out why each number differs, so the investor table is self-contained.

Outcome

Chidi received a labelled validation table with all three figures, their formulas, and written explanations of the methodology differences, ready to walk an investor through the reconciliation.

Next step

It would be worth investigating the 26 non-duplicate customers who registered but never placed an order to understand whether they represent a specific acquisition channel or sign-up period.