Oluwafemi Akinlemibola

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) needed the October 2023 revenue figure calculated in Excel to cross-check against the Finance system. Specifically, she wanted to see gross revenue across all order statuses alongside net revenue with cancelled, returned, and refunded orders excluded, and the gap between the two made visible.

What I did

To make the workbook reusable beyond October, I isolated the period start date in a single input cell and used EDATE to derive the upper date boundary, meaning the formulas stay correct for any future month without touching the SUMIFS logic. Each excluded status was stored in its own named input cell and fed into the SUMIFS criteria chain using '<>' comparisons, keeping the logic auditable. The gap cell uses a live subtraction formula rather than a hardcoded value. Methodology notes in rows 18 and 19 document how the exclusions are applied.

Outcome

Nura received a single-sheet deliverable she could present directly to Finance, with every figure formula-driven and a percentage gap calculated for immediate reference.

Next step

A useful follow-on would be breaking net revenue down by acquisition channel to show whether the cancellation and return rate differs meaningfully between online and in-store orders.

Problem

Before any analysis could begin on Meridian Retail (simulated retailer), Nura Ibrahim asked for a structured exploration of the Meridian_Data.xlsx file to understand its contents and flag data quality problems. The brief required at least three documented issues, with the findings recorded in a dedicated Data Audit sheet.

What I did

The audit sheet is organised into four numbered sections covering workbook structure, column inventory, order status breakdown, and quality issues. Row counts are driven by COUNTA formulas referencing each source sheet, and the status breakdown pairs COUNTIF with SUMIF for every status so totals are always live. Rather than stopping at three issues, I traced patterns across sheets and documented twelve distinct findings, each supported by counts or formula-derived evidence. Cross-sheet comparisons, such as matching is_first_order flags against actual order history and checking discount amounts against line-item calculations, produced several of the additional findings.

Outcome

The Data Audit sheet gave Nura a referenced, navigable record of every quality issue before any downstream analysis started, preventing those issues from silently distorting later results.

Next step

The category label inconsistency needs a mapping table that standardises all 24 labels to their 12 canonical forms before any product-category analysis is run, and it would be worth confirming whether the slug-style labels appear only in one data feed or across multiple source systems.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed to explain to an investor audience why three internal teams reported different customer counts. Marketing, operations, and finance each applied different inclusion rules, and Chidi needed a single sheet that replicated all three methodologies and made the differences traceable.

What I did

Three formulas produce the three counts: COUNTA for the raw marketing total, COUNTIF filtering on is_duplicate = FALSE for the operations figure, and COUNTIFS combining the duplicate flag with a total_orders > 0 condition for the finance view. Using the boolean FALSE() function rather than the text string 'FALSE' matched the column's actual stored values precisely. Beyond the three headline numbers, a bridge reconciliation table walks from 2,331 down to 2,131 and then to 2,105 using live formula references at each step, so every deduction is labelled and self-updating. An active-customer share percentage in C18 rounds out the summary.

Outcome

Chidi received a single sheet he could show the investor directly, with the methodology for each count labelled and every number traceable back to a live formula.

C5: =COUNTA(Customers!$A$2:$A$2332)
C6: =COUNTIF(Customers!$I$2:$I$2332,FALSE())
C7: =COUNTIFS(Customers!$I$2:$I$2332,FALSE(),Customers!$H$2:$H$2332,">0")
C12: =-(C5-C6)

C6 uses the boolean FALSE() rather than the text 'FALSE' to match the column's stored formula values exactly; C12 is part of the bridge table and derives the deduction at each step as a live formula so the reconciliation updates automatically if the source data changes.

Next step

It would be worth segmenting the 26 non-duplicate customers with zero orders by acquisition channel to see whether one channel consistently produces inactive signups.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a channel performance table for an investor deck that showed which acquisition channels produce the most valuable customers. The table required customer count, average orders per customer, and average lifetime value for each channel, sorted by count descending.

What I did

Each COUNTIFS formula filters on both the channel value and is_duplicate = FALSE, so the table reflects only the deduplicated customer base. For average lifetime value, I used SUMIFS divided by COUNTIFS rather than AVERAGEIFS directly, giving more explicit control over which records are included in both the numerator and denominator. Channels are ordered online, in-store, marketplace, matching the requested descending sort. A totals row and a self-check formula verify that the channel counts sum to the known non-duplicate total, confirming no records are missed or double-counted.

Outcome

Chidi received a ready-to-use investor table with correct counts, formatted averages, and a built-in self-check that confirms the numbers are internally consistent.

Next step

Adding average order revenue per channel alongside average order count would show whether marketplace customers, despite being fewest, place higher-value individual orders.

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed to validate whether the system's duplicate detection aligned with name-pattern evidence in the data, and to separate confirmed duplicates from coincidental name matches that share no system flag. The output needed to quantify both groups clearly for an investor audience.

What I did

A Name Match Count column using COUNTIF on the full name column identifies every customer whose name appears more than once across the 2,331-row dataset. A Verified Duplicate column then combines that count with the system is_duplicate flag using an AND() formula, so only records confirmed by both signals return TRUE. The Duplicate Summary sheet presents all five required metrics as COUNTIF and COUNTIFS formulas, none hardcoded. An integrity check formula in B11 confirms that verified duplicates plus unflagged name matches equals the total name-match count, flagging any inconsistency with an ERROR result rather than silently passing.

Outcome

Chidi received a structured summary demonstrating that the system catches every seeded duplicate by name pattern, while also quantifying the 240 coincidental matches that fall outside automated detection.

K2: =COUNTIF($B$2:$B$2332,B2)
L2: =AND(K2>1,I2=TRUE())
B11: =IF(B8+B9=B7,"OK","ERROR")

K2 counts name occurrences across the full customer list so the value updates if rows are added; L2 combines both signals with AND() so a record must satisfy both the name pattern and the system flag to be marked verified; B11 acts as a live integrity check confirming the two sub-groups account for every name match.

Next step

The 240 unflagged name matches could be prioritised for manual review by sorting on registration date proximity and shared email domain, which would narrow the list of likely genuine duplicates before any manual effort begins.