Gbenga Samuel

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 both gross revenue across all orders and net revenue with cancelled, returned, and refunded orders excluded, along with the gap between the two.

What I did

To keep the formulas date-safe, I used DATE(2023,10,1) and DATE(2023,11,1) as the lower and upper boundaries in SUMIFS rather than hardcoded text strings. The net revenue formula adds three separate criteria pairs on the status column, each using the not-equal operator to exclude one problematic status at a time, relying on SUMIFS AND logic to drop any order matching even one exclusion. A third cell holds a live subtraction formula rather than a hardcoded difference, so the gap stays accurate if the source data changes. All three cells carry the custom format 'NGN '#,##0 to display naira with thousand separators as Nura specified.

Outcome

Nura received a single dedicated sheet with three clearly labelled figures she could compare directly against the Finance system, confirming whether the data export and the system agreed.

Next step

A useful follow-on would be breaking the NGN 18,426,410 gap down by individual status to see how much of the lost revenue comes from cancellations versus returns versus refunds.

Problem

Nura Ibrahim at Meridian Retail (simulated retailer) wanted to see whether cancellation levels in October were a one-off or part of a worsening trend. The task called for a full Q4 2023 view, with monthly revenue split across all order statuses, a grand total row, naira formatting, and a chart that made the monthly pattern immediately readable.

What I did

Rather than building the PivotTable from a date-grouped column directly, I added a helper column to the Orders sheet using TEXT(P2, "MMM") to label each row with a three-letter month name. That column then drove the PivotTable's column grouping cleanly, with status on rows and months on columns. The source range Orders!A1:Q1851 covers the full dataset. A stacked bar chart was placed beneath the PivotTable to let Nura compare each status's contribution across the three months visually, and the 'NGN '#,##0 format was applied across all value cells.

Outcome

Nura received a single sheet combining the PivotTable and chart, giving her both the exact figures and a visual trend line to assess whether cancellation patterns shifted month to month across Q4.

=TEXT(P2, "MMM")

This helper column converts the order date in column P to a three-letter month label, which the PivotTable uses for column grouping across October, November, and December.

Next step

Computing each status as a percentage of its month's grand total would make it easier to compare the cancellation rate across months without raw-revenue scale distorting the picture.

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 underlying data. The task was to replicate each team's counting logic in Excel formulas and present the results in a summary table Chidi could use with stakeholders.

What I did

Three separate formulas handle three distinct definitions of a customer. COUNTA across the customer ID column captures every row regardless of status, matching marketing's signup total. COUNTIF on the is_duplicate column filtered for FALSE gives operations their deduplicated headcount. Combining both conditions in a single COUNTIFS, requiring is_duplicate to be FALSE and total_orders to be greater than zero, produces finance's active-customer figure. Column D in the sheet holds the formula strings as text, so a non-technical reader can see exactly what each number represents without needing to open the formula bar. A bar chart was added to make the three-way comparison visible at a glance.

Outcome

Chidi received a labelled summary table with live formulas, plain-language explanations, and a chart, giving a concrete and transparent account of why the three counts differ.

Next step

Checking how many of the 26 customers dropped between the operations count and the finance count have is_duplicate FALSE but total_orders of zero would clarify whether those are incomplete signups or data entry gaps.

Problem

At Meridian Retail (simulated retailer), a senior analyst needed the headline October 2023 gross revenue figure confirmed independently from the data file before trusting the sales system's reported total of approximately 56.9 million naira. The amounts in the source data were stored in a column already converted to naira, so a direct SUM across the October rows was the appropriate approach.

What I did

The October 2023 orders occupy rows 2 through 611 in the Orders sheet, covering 610 records. A SUM formula targeting I2:I611 sums the total_naira column for that exact range. The result was displayed with a Nigerian Naira currency format on the summary row. Adjacent column totals for subtotal_naira, discount_naira, shipping_naira, and tax_naira were also computed in the same row to give a fuller breakdown of how the gross figure is composed.

Outcome

The finance team had an independently derived figure they could compare against the system total, confirming the export was accurate before any further analysis was built on it.

Next step

Replacing the fixed range I2:I611 with a SUMIFS bounded by DATE functions would make the formula reusable for any month rather than being tied to a manually identified row range.

Problem

Before any analysis began at Meridian Retail (simulated retailer), Nura Ibrahim asked for a structured familiarisation with the Meridian_Data.xlsx file: how many sheets, how many columns in the Orders sheet, what the distinct order statuses are, and what data quality issues were visible from browsing. The findings needed to be documented in a dedicated Data Audit sheet.

What I did

A COUNTA formula on Orders!F:F counted all status values including the header, returning 1,850, confirming the dataset size. A PivotTable sourced from Orders!A1:P1850 with status on rows surfaced all seven distinct statuses and their record counts without needing to scan manually. The sheet documented that Orders has 16 columns and that the file contains 6 sheets. Some data quality issues were flagged in the audit, though the reviewer noted that several of the most important real issues in the file were missed.

Outcome

The Data Audit sheet gave Nura a structured starting point documenting file structure and status distribution, though the issue-flagging section was incomplete relative to what the data contained.

=COUNTA(Orders!F:F)

This formula counts all non-empty cells in the status column, giving a quick check on total record volume including the header row.

Next step

Applying COUNTIF(Customers!I:I, TRUE) to quantify duplicate customer records and COUNTIF(Inventory!G:G, "<0") to count negative stock quantities would have covered two of the real issues the audit missed.