Oseaga Oseyomon

Problem

Nura Ibrahim, a stakeholder at Meridian Retail (simulated retailer), needed to understand whether October's cancellation picture was a one-off or part of a worsening trend. The question was whether Q4 2023 revenue, split by all seven order statuses across October, November, and December, could be shown in a single, accurate view.

What I did

To answer this, I built a PivotTable on a new sheet named Status Breakdown, placing order status on rows, month on columns using Excel's native date grouping, and sum of total_naira as the value field. Naira formatting was applied with a custom format string that also highlights negative values in red. Beneath the PivotTable, I added a stacked bar chart titled 'Revenue for Q4 Months, 2023', with each of the seven statuses as a separate series so Nura could see the composition of each month at a glance.

Outcome

Nura received a single sheet combining the full numeric breakdown and a visual trend, giving her everything needed to assess whether cancellations shifted across the quarter.

Next step

A useful next step would be to calculate the cancellation rate as a percentage of monthly gross revenue, so the trend comparison is normalised rather than affected by overall volume changes.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a visual centrepiece for the customer slide of an investor deck. The requirement was a chart showing both monthly new customer acquisition and a running cumulative total across 2024, limited to non-duplicate customers.

What I did

The PivotTable on the Customer Growth sheet sources from the Customers table, with Created Date grouped by month on rows and Is Duplicate? set to FALSE on the filter pane, so only genuine new customers are counted. Running cumulative totals sit in column G using an expanding-range SUM formula dragged across all twelve months, with no hardcoded values at any step. The combination chart pairs orange bars for monthly counts against a navy line for the cumulative total, with a secondary axis scaling the line series independently. The chart title and legend labels were written to be investor-readable without any accompanying explanation.

Outcome

Chidi received a self-contained chart slide with correct brand colours and an accurate running total, ready to drop into the investor presentation.

=SUM($C$7:C7)

This expanding-range pattern, dragged down across all twelve rows, produces a live cumulative total that updates automatically if the PivotTable source data changes.

Next step

Breaking monthly acquisition down by channel or region would show Chidi which acquisition sources are driving growth, which is the natural follow-up question an investor is likely to ask.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) faced a credibility problem: marketing, operations, and finance were each reporting a different customer count, and an investor presentation was imminent. The specific ask was to replicate all three counting methodologies in Excel and explain, in plain language, why the numbers differ.

What I did

Each count maps to a different business definition, so I built three live formulas rather than hardcoded values. COUNTA across the customer ID column captures every row regardless of duplicate status, matching marketing's methodology. COUNTIF then filters to rows where is_duplicate equals FALSE, replicating the operations team's deduplication step. Finally, COUNTIFS combines the duplicate filter with a condition that total_orders is greater than zero, matching finance's definition of an active customer. Each formula is paired with a label and a short narrative so Chidi can read the sheet without analytical support.

Outcome

Chidi received a single validation sheet that documents all three counts with live formulas and explanations, making it straightforward to answer an investor who questions which number is correct.

Next step

It would be worth isolating which of the 26 customers counted by operations but excluded by finance have zero orders recorded, to check whether that gap reflects genuinely inactive accounts or a data entry gap.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed to cross-check the October 2023 revenue figure against the finance system, and specifically wanted the gap between gross revenue and net revenue made visible. The task required separating orders with all statuses from those excluding cancelled, returned, and refunded entries.

What I did

For gross revenue, I used SUMIFS with date boundaries set to 1 October 2023 and 31 October 2023 against the order date column, capturing every order regardless of status. Net revenue required a different approach: rather than writing one formula per included status, I used SUM wrapped around SUMIFS with an array of the four included statuses, which keeps the formula compact and avoids double-counting. The same array pattern was applied to the excluded statuses to calculate the revenue gap directly. All three outputs are formatted with the NGN symbol and thousand separators on the Oct Revenue Analysis sheet.

Outcome

Nura received a single sheet with all three figures and their formulas visible, giving her a clear audit trail to use when querying the finance system.

Next step

Running the same gross-vs-net split for November and December would show whether the revenue gap from adjustments is stable or growing across Q4.

Problem

Before any analysis could begin at Meridian Retail (simulated retailer), Nura Ibrahim needed a structured audit of the Meridian_Data.xlsx file. The ask was to document the sheet structure, column inventory, distinct order statuses, and at least three genuine data quality issues.

What I did

Working through each sheet systematically, I documented all 16 columns in the Orders sheet and recorded all seven distinct order statuses. Four concrete quality issues were flagged: the legacy_order_ref column contains no data, category labels in Order_Items use inconsistent formatting across rows, the Brand column in Products carries only one value making it redundant, and the Inventory sheet contains negative stock quantities. On a separate Investigation Tables sheet, I built two PivotTables to reconcile gross and net October revenue, which also provided an early check that the order status values were correctly identified.

Outcome

Nura received a documented baseline that flags which columns and values are unreliable, giving any downstream analyst a clear starting point before building on the data.

Next step

Quantifying how many rows are affected by the inconsistent category formatting in Order_Items would clarify whether this is a minor edge case or a systemic issue that skews any category-level sales analysis.