Omolade Alatishe

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 gross revenue (all orders) differed from net revenue after excluding cancelled, returned, and refunded orders.

What I did

To answer this, I used SUMIFS on the total_naira column with DATE(2023,10,1) and DATE(2023,11,1) as boundary conditions, targeting the order_date column. For net revenue, I chained three separate criteria on the status column using not-equal conditions for each excluded status in a single formula, rather than subtracting them in separate steps. The gap cell is a live formula referencing the two totals, not a hardcoded number. Beyond the core task, I built a status breakdown table covering all seven statuses and added a live cross-check cell that confirms the subtotals reconcile to the gross figure.

Outcome

Nura received a self-validating spreadsheet where the gross and net figures could be confirmed against the Finance system immediately, with the status breakdown showing exactly which order types drove the gap.

Next step

A useful next step would be to break the NGN 18,426,410.13 gap down further by product category, to see whether cancellations and returns are concentrated in specific lines.

Problem

Before any analysis could begin on the Meridian Retail (simulated retailer) dataset, Nura Ibrahim needed a structured audit of the workbook: how many sheets, what columns, what distinct order statuses, and what data quality problems were present. The brief asked for at least three flagged issues.

What I did

I documented the workbook across six sheets, recording row counts, column counts, and a purpose note for each. For order statuses, percentage formulas in the audit sheet divide each status count by the total 1,849 orders, keeping all figures live. The sharpest issue I identified was inconsistent category naming in Order_Items: 24 distinct values mapping to roughly 12 logical categories, with specific examples showing the same category appearing in both a display format and a slug format. Three further issues were flagged with severity ratings: 66 negative stock quantities in the Inventory sheet, a legacy_order_ref column confirmed as 100 percent null across all rows, and a duplicate customer flag showing 200 of 2,331 customers marked is_duplicate equals TRUE. I also caught that line_total does not deduct the discount field, cross-verified by checking charged_price multiplied by quantity against line_total for discounted rows.

Outcome

The audit sheet gave Nura a documented baseline of known issues and severity ratings before any revenue or product analysis was attempted, preventing those issues from silently corrupting downstream figures.

Next step

A logical next step would be to quantify the revenue impact of the line_total discount discrepancy specifically, by summing the difference between charged_price multiplied by quantity and the recorded line_total for all rows where a discount exists.

Problem

Funmi Alade, Head of Customer Experience at FlowRoute Logistics (simulated logistics company), needed a board-ready summary of the full complaints record: total complaints, a breakdown by category sorted from most to least common, and a single sentence identifying which category to lead with.

What I did

The total count uses COUNTA on the Complaints column minus the header, so it updates automatically as data grows. Category counts are driven by COUNTIF formulas in a working area separated from the display table, and a tie-safe RANK combined with COUNTIF handles the sort order. INDEX/MATCH then pulls rows from the working area into the display table in ranked order, meaning the sort is formula-driven rather than manual. The lead-with sentence in the summary uses TEXT and string concatenation so it rewrites itself dynamically, and a note flagging the near-equal spread across categories was included as a board insight.

Outcome

Funmi received a fully live summary sheet where all figures, rankings, and the board sentence update automatically if the underlying complaints data is refreshed.

=COUNTA(Complaints!A:A)-1

This formula counts all non-empty cells in the complaints column and subtracts one for the header, keeping the total live without hardcoding.

Next step

Because the category spread is near-equal, a more useful next cut would be to cross-tabulate category against destination hub, to check whether any hub is disproportionately driving one complaint type.

Problem

Funmi Alade at FlowRoute Logistics (simulated logistics company) needed to know which hubs represented the greatest operational risk. The question combined delivery rate data from the Hub_Performance sheet with complaint counts from the Complaints sheet, and required a normalised complaints metric so that high-volume hubs would not appear worse simply due to scale.

What I did

Complaint counts per hub are drawn using COUNTIF formulas that reference the Complaints sheet directly, so the column updates if the data grows. The per-1,000 shipments metric divides each hub's complaint count by its own total_shipments value from Hub_Performance, preventing volume from distorting the comparison. For the sort, a working table computes an ascending RANK on delivery rate, and the display table uses INDEX/MATCH against that rank column to pull rows into order without manual rearrangement. The verdict formula applies a documented threshold, more than 3 percentage points gap in delivery rate or more than 15 percent gap in complaints per 1,000 versus the network average, and switches between a warning and an all-clear dynamically, with the threshold assumption noted in a separate cell.

Outcome

Funmi received an honest, evidence-based verdict: the data did not support singling out one hub, which prevented a misattributed escalation and pointed to a network-wide issue instead.

Next step

Given the near-equal spread, a useful follow-on would be to segment complaints per 1,000 by complaint category for each hub, to check whether specific complaint types are concentrated at particular locations even if overall rates look similar.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) wanted to see Q4 2023 revenue broken down by order status across October, November, and December, to determine whether the cancellation level observed in October was a persistent trend or a one-off.

What I did

Each cell in the data table uses SUMIFS with DATE boundary conditions on order_date and a status filter, so no values are hardcoded and the table would update if source data changed. The Grand Total row uses SUM formulas across each month column and the overall Q4 total. A stacked bar chart was built from the status breakdown data with all seven statuses in the legend, giving a visual read of the monthly composition. Beyond the core requirements, I added a cancellation rate row showing the percentage of monthly revenue attributed to cancelled orders across each of the three months, using live division formulas.

Outcome

Nura could see at a glance that cancellations spiked in October and moderated afterwards, allowing her to treat October as a contextual outlier rather than a baseline for Q4 planning.

Next step

To extend this analysis, I would add a column for average order value per status per month, to check whether the declining cancellation rate in November reflects fewer cancellations or a shift toward lower-value cancelled orders.