Tejumola Hubaidah Oyindamola

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed the October 2023 revenue figure calculated in Excel to verify against the Finance system. The specific question was how much gross revenue all orders produced, and how much remained once cancelled, returned, and refunded orders were excluded, and what the gap between those two figures was.

What I did

To isolate October orders, I used SUMIFS against the order_date column with paired greater-than and less-than date boundary criteria, which avoids any ambiguity about month boundaries. For net revenue, I added three separate status exclusion criteria to the same formula, one each for cancelled, returned, and refunded rows. A simple subtraction formula then computed the gap. All three output cells were formatted with the custom Nigerian Naira code so the figures displayed correctly with thousand separators.

Outcome

Nura received a single structured sheet with labelled figures she could compare directly against the Finance system output, with the revenue gap made explicit.

=SUMIFS(Orders!I2:I1850,Orders!P2:P1850,">=2023-10-01",Orders!P2:P1850,"<2023-11-01")
=SUMIFS(Orders!I2:I1850,Orders!P2:P1850,">=2023-10-01",Orders!P2:P1850,"<2023-11-01",Orders!status_col,"<>cancelled",Orders!status_col,"<>returned",Orders!status_col,"<>refunded")
=A2-B2

The first formula isolates October orders by date; the second adds three status exclusions to produce net revenue; the third computes the gap between them.

Next step

A natural follow-on would be to break the NGN 18,426,410.13 gap down by status category to see whether cancellations, returns, or refunds are individually the largest driver of lost revenue.

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 monthly new customer counts alongside a running cumulative total, using Meridian brand colours.

What I did

The PivotTable was built from the Customers sheet with is_duplicate set as a filter field and restricted to FALSE, so only the 2,131 non-duplicate customers fed into the monthly counts. To keep the summary table dynamic, I used GETPIVOTDATA formulas in column F to pull each month's count by name rather than by cell position, which means the table stays correct even if the PivotTable is re-sorted or filtered. The cumulative column in G used an expanding SUM with an anchored start row, so each row accumulated from the first month downward. The combination chart then mapped the monthly counts to orange bars and the running total to a navy line, matching Meridian's brand requirements.

Outcome

Chidi received a combination chart and supporting table on a dedicated sheet, ready to be embedded in the investor presentation with brand-consistent colours and a clear visual narrative of cumulative growth.

=GETPIVOTDATA("customer_id",$A$3,"created_date",1)
=SUM($F$4:F4)
=SUM($F$4:F5)

GETPIVOTDATA pulls each month's count by item name from the PivotTable; the expanding SUM pattern in column G accumulates the running total row by row.

Next step

Breaking the monthly acquisition counts down by customer region or acquisition channel would show whether growth is concentrated in one segment, which is likely the next question an investor would ask after seeing the overall trend.

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 methods in Excel and present the results in a table Chidi could show directly to an investor.

What I did

Three separate formulas each modelled a different counting rule. COUNTA on the customer ID column captured every row, matching marketing's all-signups figure. COUNTIF filtered to rows where is_duplicate equals FALSE, replicating operations' deduplicated total. COUNTIFS combined the deduplication filter with a check that total_orders was greater than zero, matching finance's definition of an active unique customer. Rather than just listing the numbers, I added a REASON column that explained in plain terms why each method produces a different result, which was the narrative Chidi needed for the investor conversation.

Outcome

Chidi received a four-column summary table with team, metric, result, and explanatory reason for each count, structured so it could be dropped into an investor presentation without additional editing.

Next step

Comparing the 26 customers who are non-duplicates but have zero orders by acquisition month could show whether a particular signup cohort has unusually low activation, which would be useful context for the investor conversation.

Problem

Before any analysis could begin on the Meridian Retail (simulated retailer) dataset, Nura Ibrahim needed a clear picture of what was in the file and what could not be trusted. The task was to browse the dataset, document its structure, and flag at least three data quality issues that would affect downstream reporting.

What I did

Working through each sheet systematically, I recorded issues in a dedicated Data Audit sheet using a five-column table: serial number, sheet name, column name, issue found, and reason. Building a quick pivot on the Orders sheet helped confirm that the dataset covered Q4 2023 rather than just October, which was not immediately obvious from the file name. Column statistics on Order_Items revealed 24 distinct category values where 12 were expected, pointing to a mixed formatting problem between human-readable labels and slug-style strings. The is_duplicate column statistics confirmed 200 flagged duplicates, and the Inventory minimum stock value of -25 confirmed negative stock records were present.

Outcome

Nura received a structured audit table she could act on before commissioning further analysis, with the business consequence of each issue explained in the reason column.

Next step

Quantifying how much revenue is affected by the inconsistent category formatting specifically, by comparing summed revenue under the slug-style labels against the human-readable equivalents, would make it easier to prioritise a fix.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) wanted to see whether the October cancellation volume was a one-off or part of a worsening trend. She asked for a full Q4 2023 breakdown of revenue by order status across October, November, and December, with a chart showing the monthly split visually.

What I did

A PivotTable on a dedicated Status_Breakdown sheet gave the quickest route to a correctly grouped cross-tab: status on rows, order_date grouped by month on columns, and sum of total_naira as the value field. Applying the NGN custom format to every value cell in the table kept the display consistent with the earlier October analysis. A stacked bar chart built from the PivotTable data then made the monthly composition of each status visible at a glance, with a legend and labelled axes so Nura could read it without further explanation.

Outcome

Nura had a single sheet containing both the numeric breakdown and the visual trend chart, which she could use to assess whether cancellation volumes were isolated to one month or recurring across Q4.

Next step

Calculating each status as a percentage of its monthly grand total would make it easier to compare the cancellation rate across months rather than relying on absolute naira figures that vary with overall volume.