Marvellous Oluchi Amadi

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) wanted to know whether cancellation-related revenue loss in October was a one-off or a worsening trend across Q4 2023. She asked for a full breakdown of revenue by order status and by month, October through December, with a chart to make the monthly pattern visible.

What I did

I built a PivotTable on a new sheet called Status Breakdown, placing status on rows, a date-grouped month field on columns, and sum of total_naira as the value. Grouping by month in the PivotTable meant all seven statuses appeared automatically without any manual filtering, and the Grand Total row confirmed the overall Q4 figure. Beneath the PivotTable, I added a stacked bar chart with status as the category axis and the three months as separate series, which lets Nura see at a glance how each status contributed to monthly revenue.

Outcome

Nura had a single sheet showing whether cancellation-related rows were growing, shrinking, or stable month on month, with the chart making the pattern readable without additional explanation.

Next step

The next useful step would be to calculate cancellation revenue as a percentage of gross revenue for each month, to make the month-on-month trend comparable on a relative rather than absolute basis.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed to explain to an investor why marketing, operations, and finance each reported a different customer count. The task was to replicate all three counting methodologies in Excel and document why the numbers differ.

What I did

For the marketing count, COUNTA on the customer_id column captures every row including duplicates. COUNTIF on the is_duplicate column restricted to the value FALSE gave the operations figure, which removes the 200 flagged duplicate records. The finance count required COUNTIFS, combining the is_duplicate = FALSE condition with a total_orders greater than zero condition, so only customers who had actually transacted were included. Alongside the three formulas, I built a summary table in rows 4 to 7 explaining in plain language what each methodology counts and which function drives it.

Outcome

Chidi could show the investor a single sheet that both produced each figure and explained the logic behind it, turning a potential credibility problem into a demonstration of data literacy.

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

The three formulas sit in the same row so the counts are immediately comparable, and each adds exactly one condition to the previous, making the step-down logic from 2,331 to 2,131 to 2,105 self-explanatory.

Next step

It would be worth checking whether the 26 customers who are non-duplicates but have zero orders are recent sign-ups, lapsed contacts, or a data entry issue, since that segment sits between the operations and finance definitions.

Problem

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

What I did

The PivotTable on the Customer Growth sheet uses created_date grouped by month on rows, count of customer_id as the value, and is_duplicate filtered to FALSE, so the 2,131 non-duplicate customers are the only population counted. For the cumulative total, I seeded C4 with the first monthly count and used a C4+B5 expanding pattern dragged down through C15, which keeps every row linked to the live PivotTable rather than hardcoded. Excel combination charts cannot draw directly from a PivotTable when mixing series types, so I built a mirrored reference table in columns F through H using live cell references, then pointed the chart at that range. The result is orange bars for monthly acquisitions and a navy line for the cumulative total.

Outcome

Chidi received a ready-to-present chart in the correct brand colours, backed by a PivotTable that could be updated if the underlying data changed, rather than a static image.

Next step

Breaking the monthly acquisition bars down by customer region or acquisition channel would show whether growth is concentrated in one segment or broad-based, which is the kind of follow-up question an investor is likely to ask.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) shared the Meridian_Data.xlsx file and asked for a structured audit of its contents before any analysis began. The task was to document the sheet structure, flag at least three genuine data quality issues, and produce a Data Audit sheet that would inform all subsequent work on the file.

What I did

Rather than moving straight into formulas, I browsed each sheet first to understand what was there. The audit sheet documented six issues across four sheets: negative stock quantities in Inventory, 200 duplicate customer records in Customers, seven distinct order statuses in Orders with a note on which three should be excluded from net revenue, inconsistent category capitalisation in Order_Items, an entirely empty legacy_order_ref column, and a clarification that monetary values were already in naira and should not be divided by 100. The order status section was the most analytically consequential entry, since it named the exclusion rule that all subsequent revenue formulas would depend on.

Outcome

The Data Audit sheet served as a reference document for every subsequent task on the Meridian dataset, and the exclusion logic identified here was applied correctly in all later SUMIFS formulas.

Next step

The audit noted that 66 products carry negative stock quantities, but did not quantify the revenue tied to orders for those products, which would help prioritise whether the inventory issue is material to financial reporting.

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 revenue was attributable to all orders versus how much remained after removing cancelled, returned, and refunded orders, and how large the gap between the two was.

What I did

To isolate October, I used SUMIFS with DATE(2023,10,1) and DATE(2023,11,1) as the lower and upper bounds on the order_date column, which avoids any ambiguity from partial-month rows. For the net revenue formula in B2, I extended the same date criteria with three separate inequality tests on the status column, one each for cancelled, returned, and refunded, so SUMIFS applied all three exclusions with AND logic. The gap in C2 is a simple subtraction of B2 from A2. All three result cells were formatted as Nigerian Naira with thousand separators.

Outcome

Nura received a single auditable sheet with three live formulas she could verify against the Finance system, with the revenue gap made explicit rather than left implicit in a raw total.

Next step

A useful next cut would be to break the NGN 18,426,410.13 gap down by status, to see whether cancellations, returns, or refunds individually drive the largest revenue loss.