Divine Mbonu

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed a baseline data audit before any analysis could begin on the Meridian_Data.xlsx file. The questions were concrete: how many sheets exist, what columns does the Orders sheet contain, what are the distinct order statuses, and what quality problems are visible on inspection? At least three issues had to be flagged with evidence.

What I did

The audit sheet is divided into seven labelled sections covering the brief's structural questions, a sheet overview, column-level documentation, status counts, flagged issues, passing checks, and assumptions. Each count is backed by a live formula pointing to the source sheet rather than a typed constant, so the sheet would stay accurate if the data were refreshed. For the category format issue, I used SUMPRODUCT with EXACT and LOWER together to count slug-format rows precisely, which distinguishes case-sensitive mismatches that a plain COUNTIF would miss. Twelve passing checks are documented alongside the failures, giving the reader a clear statement of what they can rely on.

Outcome

Nura received a single, formula-driven reference sheet covering every structural question in the brief and quantifying each issue well enough to prioritise fixes before downstream analysis began.

Next step

A useful follow-on would be checking whether the 66 negative stock rows cluster in specific store locations, which would distinguish a data entry pattern from a genuine inventory problem.

Problem

Nura Ibrahim at Meridian Retail needed October 2023 revenue calculated in Excel to cross-check against the finance system. The specific requirement was to show both gross revenue across all order statuses and net revenue after excluding cancelled, returned, and refunded orders, with the gap between the two figures made explicit.

What I did

Two SUMIFS formulas drive the sheet: one summing total_naira across all statuses for October, and one applying three not-equal criteria pairs to exclude the three unwanted statuses in a single formula call. The date window uses DATE(2023,10,1) as the lower bound and DATE(2023,11,1) as the exclusive upper bound, which avoids any ambiguity from time components. Input cells in B4 and B5 hold the start and end dates as DATE formulas, making the sheet reusable for any month without touching the SUMIFS logic. A self-check formula in B19 uses ROUND to confirm that the manually derived gap matches the formula-derived figure, returning zero if the sheet is internally consistent.

Outcome

Nura could bring a single verified sheet to the finance reconciliation meeting, with the gross-to-net difference broken down by status and a built-in integrity check confirming the arithmetic was clean.

=SUMIFS(Orders!$I:$I,Orders!$P:$P,">="&$B$4,Orders!$P:$P,"<"&$B$5,Orders!$F:$F,"<>cancelled",Orders!$F:$F,"<>returned",Orders!$F:$F,"<>refunded")

This single SUMIFS applies date-range bounds and three exclusion criteria in one formula, returning net revenue without requiring a filtered copy of the data.

Next step

Extending the same SUMIFS structure to November and December would confirm whether the cancelled-order share of revenue is stable or grew through Q4, which is the question Nura raised in the next brief.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed to explain why three internal teams reported different customer counts. Marketing, operations, and finance each used a different definition of a customer, and the task was to replicate all three counts in Excel and document why the figures diverge.

What I did

Three formulas anchor the sheet: COUNTA for the raw row count matching marketing's method, COUNTIF filtering on is_duplicate equal to FALSE for operations' deduplicated count, and COUNTIFS combining the duplicate flag with a total_orders greater than zero condition for finance's active customer definition. Rather than typing the deductions between counts, a reconciliation bridge in Section 2 computes the minus 200 and minus 26 steps as live formulas, so the bridge stays accurate if the source data changes. Two integrity checks in rows 19 and 20 cross-verify that the bridge ties to the COUNTIFS result and that the duplicate count matches the marketing-to-operations gap. An investor-facing narrative sentence in Section 3 is built with TEXT functions so it updates dynamically rather than becoming stale.

Outcome

Chidi received a single sheet that Nura could take into the investor meeting to explain the discrepancy, with every number driven by a live formula and two built-in checks confirming internal consistency.

=COUNTIFS(Customers!$I$2:$I$2332,FALSE(),Customers!$H$2:$H$2332,">0")

This COUNTIFS combines the is_duplicate and total_orders conditions in one formula to produce the finance-definition active customer count without a helper column.

Next step

It would be worth checking whether the 26 deduplicated customers with zero orders share a common acquisition channel, which could indicate a sign-up flow problem rather than random data noise.

Problem

Nura Ibrahim at Meridian Retail wanted to see Q4 2023 revenue split by order status across October, November, and December to assess whether cancellations were deteriorating over time or whether October was an isolated spike. A PivotTable-style matrix and a stacked bar chart were both required.

What I did

Rather than a PivotTable, I built the matrix using SUMIFS formulas in each cell, with date-range bounds derived from EDATE so that changing a single header date in row 4 would re-drive the entire table. Each cell references Orders!$I:$I for total_naira, Orders!$F:$F for status, and Orders!$P:$P for order_date, keeping the formula structure consistent across all 21 data cells. Column headers in row 4 store date serial numbers formatted as mmmm yyyy, so the display reads as full month names while the underlying values remain usable as EDATE inputs. A verification cell cross-checks the Grand Total against SUM of the entire Orders total_naira column, confirming the matrix covers all three months without gaps or double-counting.

Outcome

Nura received a formula-driven status matrix covering Q4 2023 and a stacked bar chart showing the monthly revenue split, giving her a visual basis for assessing whether cancellation revenue was stable or shifting month to month.

=SUMIFS(Orders!$I:$I,Orders!$F:$F,$A5,Orders!$P:$P,">="&B$4,Orders!$P:$P,"<"&EDATE(B$4,1))

This SUMIFS uses EDATE to derive the month end bound from the header date, so every cell in the matrix is driven by the same formula pattern and the table recalculates if any header changes.

Next step

Adding a percentage-of-monthly-total column for cancelled orders across the three months would make the trend comparison more precise than reading absolute naira figures from the chart.

Problem

Chidi Okafor at Meridian Retail needed a customer growth chart for an investor presentation slide. The deliverable required a PivotTable counting non-duplicate customers by acquisition month, a running cumulative total column next to the PivotTable, and a combination chart showing monthly bars alongside a cumulative line.

What I did

A helper column K in the Customers sheet uses DATE with YEAR and MONTH to normalise each acquisition date to the first of its month, which allows the PivotTable to group cleanly without Excel's built-in date grouping feature. The PivotTable is filtered to is_duplicate equal to FALSE so the monthly counts reflect deduplicated customers only, consistent with the 2,131 operations-definition count established in the prior task. The cumulative total column in C4:C51 uses an expanding SUM range starting at $B$4 and extending to the current row, dragged down 48 rows to cover the full date range. Column A is formatted as mmm-yy so the chart axis displays readable month labels rather than serial numbers, and a Notes column documents the source range, filter logic, and chart colour hex codes.

Outcome

Chidi received a self-contained sheet with a filtered PivotTable, a live cumulative column, and a combination chart ready to drop into the investor presentation without manual updates.

=SUM($B$4:B4)

This expanding range formula, dragged down the cumulative column, adds one new row to the sum with each step, producing a running total that updates automatically if the PivotTable refreshes.

Next step

Breaking the monthly acquisition count by customer segment or acquisition channel, if that data exists in the Customers sheet, would give the investor slide a more actionable story than a raw growth count.