Fatimat Zuberu

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed to explain to investors why marketing, operations, and finance each reported a different customer total. My job was to replicate all three counting methodologies in Excel so the discrepancy could be shown clearly rather than simply asserted.

What I did

For each methodology, I chose the most precise counting function available: COUNTA for the raw row count, COUNTIF for a single-condition deduplication check, and COUNTIFS for the two-condition active-customer filter. Throughout, I used Excel Table structured references such as Customers[is_duplicate] and Customers[total_orders] so the formulas would stay correct if rows were added later. Gap metrics in rows 17 to 20 are live subtraction formulas rather than typed values, so the differences update automatically if the underlying data changes. Labels in column A and a dedicated narrative section give Chidi the plain-language context to walk investors through the numbers.

Outcome

Chidi received a single summary table that explains every figure and every gap, making the three-way discrepancy presentable to investors without additional preparation.

Next step

A useful next step would be to break the 26 zero-order non-duplicate customers down by acquisition channel to see whether any single source is consistently producing inactive signups.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed October 2023 revenue figures to cross-check against the finance system. She specifically wanted to see both the gross total across all order statuses and a net figure that excluded cancelled, returned, and refunded orders, along with the gap between them.

What I did

Both revenue figures use SUMIFS against the Orders table, isolating October with a date range of greater than or equal to 1 October 2023 and less than 1 November 2023, which avoids hardcoding a month-end date. The net formula chains three not-equal criteria for cancelled, returned, and refunded statuses inside the same SUMIFS call. During testing I noticed that status values are stored in lowercase in the raw data, so I used lowercase strings in my criteria to match the actual values rather than the display labels. The revenue gap in B7 is a live formula referencing B5 and B6 so it updates if either input changes.

Outcome

Nura received a structured analysis sheet with all three figures ready to paste into the finance cross-check, along with a documented note on the lowercase status data quality issue.

=SUMIFS(Orders[total_naira],Orders[order_date],">="&DATE(2023,10,1),Orders[order_date],"<"&DATE(2023,11,1))
=SUMIFS(Orders[total_naira],Orders[order_date],">="&DATE(2023,10,1),Orders[order_date],"<"&DATE(2023,11,1),Orders[status],"<>cancelled",Orders[status],"<>returned",Orders[status],"<>refunded")
=B5-B6

The gross formula uses two date boundaries to capture the full month without a hardcoded end date; the net formula adds three exclusion criteria in the same call, and the gap formula keeps all three figures linked.

Next step

Repeating this gross-versus-net split for November and December would show whether the NGN 18 million gap in October is typical or an outlier for the quarter.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) wanted to see whether cancellations were worsening across the full fourth quarter or whether October had been unusual. The deliverable was a PivotTable showing revenue by order status for each month in Q4 2023, supported by a stacked bar chart for visual comparison.

What I did

Rather than using Excel's native date-grouping feature in the PivotTable, I added a helper column to the Orders table using the formula TEXT(Orders[[#This Row],[order_date]],"mmmm"), which produces an explicit month label that is visible, auditable, and free from PivotTable grouping quirks. The PivotTable was then built with status on rows, the helper month column on columns, and the sum of total_naira as the value field. All seven order statuses appear across all three months with a Grand Total row. The stacked bar chart is titled "Q4 2023 Revenue by Order Status" and maps each status to a colour-coded band across October, November, and December.

Outcome

Nura received a single sheet combining the full status-by-month breakdown and a chart she can use to assess whether any problematic status, such as cancelled or returned, is growing as a share of monthly revenue.

=TEXT(Orders[[#This Row],[order_date]],"mmmm")

This helper column converts each order date into a month name, providing an explicit, auditable grouping field for the PivotTable instead of relying on Excel's date-grouping behaviour.

Next step

Calculating each status as a percentage of monthly gross revenue, rather than an absolute naira amount, would make it easier to judge whether cancellations are growing proportionally or just tracking overall volume.

Problem

Before any analysis could begin on the Meridian Retail (simulated retailer) data file, Nura Ibrahim needed a structured audit of what the file contained and what quality problems existed. The brief required at least three issues to be flagged in a documented Data Audit sheet.

What I did

The audit table was structured with columns for Audit ID, Data Source, Audit Check, Formula or Metric, and Status, mirroring the layout used in real-world audit documentation so the output could be referenced without additional explanation. Each issue was traced to a specific sheet and column rather than described generically. An XLOOKUP formula in the Order_Items sheet was used during exploration to check date relationships across sheets, confirming the data connected correctly before flagging the legacy_order_ref column in Orders as entirely blank. Four issues were confirmed: 200 duplicate customer records, 66 products with negative stock quantities, inconsistent category formatting across 24 distinct values that should be 12, and a legacy_order_ref column with zero populated rows.

Outcome

The Data Audit sheet gave Nura a single reference document listing each issue by source table, allowing her to prioritise cleaning work before downstream analysis.

Next step

Quantifying the revenue impact of orders linked to the 200 duplicate customer records would show whether the duplication affects reported sales figures as well as the customer count.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a customer growth chart as the centrepiece of an investor presentation slide. The chart had to show both monthly new customer counts and a running cumulative total, using Meridian brand colours.

What I did

The PivotTable sources the Customers table directly, with created_month on rows and count of customer_id as the value, covering all 12 months without manual filtering. The cumulative total column in C4 to C15 uses a running-sum pattern: C4 equals B4, and each subsequent cell adds the previous cumulative to the current month's count, so the column stays live rather than being typed values. The combination chart assigns monthly new customers to clustered bars and the cumulative total to a line series, which is the standard investor chart format for this type of growth story. Orange bars and a navy line match the Meridian brand palette specified in the brief. A clean summary table alongside the chart gives the slide a self-contained layout that does not rely on the PivotTable being visible.

Outcome

Chidi received a formatted, investor-ready chart sheet with live formulas throughout, meaning the chart and cumulative total will update automatically if the underlying customer data changes.

Next step

Filtering the PivotTable to count only non-duplicate customers each month, rather than all rows, would confirm that the 2,331 total excludes the 200 flagged duplicates identified in the earlier audit.