Abasiodiong Ibok

Junior Analyst

Junior data analyst building customer and retail insights in Excel: counts, trends, channel performance, and data quality audits.

Scores reflect independent, automated grading of simulated business tasks completed on DataSimHQ.

Graded projects
5
Average best score
91/100
Career level
Junior Analyst
Core tool stack
Excel (5)

About

Working through retail scenarios for a fictional company called Meridian Retail, I completed five graded tasks covering customer validation, acquisition trend analysis, channel performance summaries, duplicate detection, and structured data audits. Each submission was independently reviewed and scored, pushing me to produce outputs that a non-technical stakeholder could read and act on without extra explanation.

These exercises sharpened my use of Excel formula logic, including COUNTA, COUNTIF, COUNTIFS, and AVERAGEIFS, alongside PivotTables, combination charts, and conditional formatting. The recurring challenge was reconciling numbers across different business definitions, something I addressed by building transparent, formula-driven summary tables rather than hard-coded figures.

Skills

Excel Formulas

  • COUNTA for all-row counts
  • COUNTIF for single-condition counts
  • COUNTIFS for multi-condition counts
  • AVERAGEIFS for conditional averages
  • Expanding-range SUM for running totals
  • Reconciliation bridge formulas linking multiple count methodologies

Data Quality and Reporting

  • Conditional formatting with formula-based rules
  • Structured summary table layout for non-technical audiences
  • Data quality issue documentation with scope and remediation fields

Excel PivotTables and Charts

  • PivotTable construction and grouping by date
  • Combination chart building (bar and line, dual axis)
  • Sorting and filtering PivotTable output by a business metric

Featured work

Retail · Excel

Duplicate customer validation and coincidental name-match quantification

Independent grading: 88/100(verify, opens in a new tab)

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed to validate whether the system's duplicate flag was reliable, and to quantify how many name matches in the data were genuine duplicates versus different people who happened to share a name.

What I did

On the Customers sheet, I added a Name Match Count column using COUNTIF on the Full Name column, then applied conditional formatting to highlight any row where that count exceeded one. A Verified Duplicate column combines the name-match condition with the is_duplicate flag using a logical AND, marking only records that both methods agree on. The Duplicate Summary sheet presents five metrics built from COUNTIF and COUNTIFS formulas, and each metric row includes the actual formula expression in a methodology column so Chidi's team can audit the logic directly. A KPI card row at the top of the sheet lets an investor scan the five headline numbers without reading the table detail.

Finding

All 200 system-flagged duplicates are confirmed by the name-pattern method, a 100% detection rate, while 240 additional name matches are coincidental and do not represent true duplicates.

Outcome

Chidi received an investor-grade due diligence sheet that validated the duplicate detection system and clearly separated genuine duplicates from coincidental name overlaps, both with transparent formula evidence.

Next step

Reviewing the 240 coincidental name matches for shared addresses or email domains would determine whether any should be reclassified as genuine duplicates the system missed.

  • COUNTIF for single-condition counts
  • COUNTIFS for multi-condition counts
  • Conditional formatting with formula-based rules
  • Structured summary table layout for non-technical audiences
  • Reconciliation bridge formulas linking multiple count methodologies

Retail · Excel

Reconciling three customer count methodologies for investor reporting

Independent grading: 98/100(verify, opens in a new tab)

Problem

Chidi Okafor, a stakeholder at Meridian Retail (simulated retailer), flagged that marketing, operations, and finance each reported a different total customer number. He needed a single sheet that replicated all three counting methods, explained the arithmetic gaps between them, and was clear enough to show an investor.

What I did

To replicate the three methodologies, I used COUNTA for the raw row count, COUNTIF filtering on is_duplicate = FALSE for the deduplicated operations figure, and COUNTIFS combining is_duplicate = FALSE with total_orders greater than zero for the finance active-customer figure. Beyond the three headline numbers, I built a reconciliation bridge in rows 11 through 15 using live formulas that arithmetically step from the marketing count down to the finance count, isolating the duplicate adjustment and the non-transacting gap as separate line items. Each figure carries a plain-English label in a Business Rationale column so the numbers tell a coherent story without requiring the reader to understand the underlying logic.

Finding

The marketing count of 2,331, the operations count of 2,131, and the finance count of 2,105 each measure something legitimately different: the 200-record gap reflects duplicate records, and the remaining 26-record gap reflects customers who signed up but never transacted.

Outcome

Chidi received a single, formula-driven summary table that explained the source of every discrepancy, removing ambiguity before the investor meeting.

Next step

A useful next step would be to break the 26 non-transacting customers down by acquisition channel to see whether any single channel is consistently producing sign-ups that never convert.

  • COUNTA for all-row counts
  • COUNTIF for single-condition counts
  • COUNTIFS for multi-condition counts
  • Reconciliation bridge formulas linking multiple count methodologies
  • Structured summary table layout for non-technical audiences

Retail · Excel

Monthly customer acquisition trend chart for investor presentation

Independent grading: 92/100(verify, opens in a new tab)

Problem

Chidi Okafor at Meridian Retail (simulated retailer) needed a visual centrepiece for the customer slide of an investor deck, specifically a chart showing how many new customers Meridian acquired each month and how the base grew cumulatively over time.

What I did

The foundation is a PivotTable on a dedicated Customer Growth sheet, grouped by the created_at field at Year-Month granularity and filtered to non-duplicate customers only, which excludes the 200 is_duplicate = TRUE records that would otherwise inflate monthly counts. Alongside the PivotTable, I added a Cumulative Total column using an expanding-range SUM formula dragged down row by row, so each cell accumulates all months above it without hard-coding any values. From that data, I built a combination chart: orange bars for monthly new customers and a navy line on a secondary axis for the cumulative total, matching the Meridian brand colours Chidi specified.

Finding

Monthly acquisition ran consistently in the 38 to 52 new customer range across the tracked period, with the cumulative non-duplicate base reaching 1,775 by April 2024.

Outcome

Chidi received a presentation-ready chart titled Customer Acquisition and Cumulative Growth that he could embed directly into the investor slide without reformatting.

Next step

It would be worth breaking the monthly counts by acquisition channel to see whether any channel shows a distinct growth or decline pattern within the same overall trend.

  • PivotTable construction and grouping by date
  • Expanding-range SUM for running totals
  • Combination chart building (bar and line, dual axis)
  • Sorting and filtering PivotTable output by a business metric

Retail · Excel

Acquisition channel performance summary ranked by customer count

Independent grading: 88/100(verify, opens in a new tab)

Problem

For his investor deck, Chidi Okafor at Meridian Retail (simulated retailer) needed a table comparing all acquisition channels across three metrics: how many customers each channel brought in, how often those customers ordered on average, and what average lifetime value each channel produced.

What I did

Each row in the Channel Performance sheet corresponds to one acquisition channel, with figures drawn from COUNTIFS, AVERAGEIFS, and a combination of SUMIFS and COUNTIFS to derive average lifetime value. Channels are sorted by Customer Count descending, from Instagram at 256 down to Referral at 194, as Chidi requested. A Total row at the bottom aggregates all channels and serves as a consistency check against the known full dataset figures. The bonus sales channel breakdown was added alongside to give Chidi an additional angle without cluttering the primary table.

Finding

The referral channel produced the highest average lifetime value at NGN 1,051,819.12 and the highest average order count at 10.36, making it the most valuable acquisition source despite not being the highest-volume channel.

Outcome

Chidi gained a ready-to-use investor table that ranked every channel by volume and surfaced which channel warranted the most investment based on customer quality.

Next step

Comparing lifetime value by channel against any known cost-per-acquisition figure would show which channel has the best return on spend, not just the best raw customer quality.

  • COUNTIFS for multi-condition counts
  • AVERAGEIFS for conditional averages
  • Structured summary table layout for non-technical audiences
  • Sorting and filtering PivotTable output by a business metric

Retail · Excel

Structured data audit flagging six quality issues across the Meridian dataset

Independent grading: 88/100(verify, opens in a new tab)

Problem

Before any analysis could begin on Meridian Retail (simulated retailer), the stakeholder Nura Ibrahim needed a systematic record of what the data file contained and what problems were present. The task was to document the sheet structure, the Orders column inventory, the distinct order statuses, and at least three data quality issues.

What I did

The Data Audit sheet records all seven distinct order statuses with their row counts and percentage shares, verified to sum to the 1,849-row total. For the Orders sheet, I documented all sixteen columns from A through P with business-meaningful descriptions of each field, including notes on the purpose of fields like is_first_order and legacy_order_ref. Six quality issues were identified and logged with an Issue ID, the affected sheet and field, a plain description, the observed scope, and a suggested remediation step. Spotting the category slug inconsistency between formats like bags-accessories and title-case equivalents required close reading of the data rather than any automated check.

Finding

Six genuine data quality issues are present in the file, including 66 records with negative stock quantities, sparse legacy reference values, return quantity misalignments, and inconsistent category formatting that would break any automated grouping by product category.

Outcome

Nura's team received an issue log structured in the format a data team would use to prioritise remediation, with each problem scoped and explained before any downstream analysis began.

Next step

Quantifying how many order rows are affected by each category slug variant would help prioritise whether a simple text-cleaning step is sufficient or whether a reference table mapping slugs to canonical names is needed.

  • Data quality issue documentation with scope and remediation fields
  • Structured summary table layout for non-technical audiences
  • COUNTA for all-row counts