edium profile

The bio fits Medium’s 160-character limit. The About text goes on your profile’s About tab. Each article below is ready to paste into a new story: the first line is the title, the italic line is the subtitle, and the tags are listed at the end.

Short bio (152 characters)

Data analyst working in Excel and SQL. I turn messy business data into clear answers on profit, pricing and performance. Writing up my projects as I go.

About

I’m a data analyst who likes the question behind the numbers: not just what sold, but what actually made money, and why.

My work so far covers three areas:

• Excel dashboards. An interactive retail sales dashboard built from 9,994 order lines, with region and year filters, live KPI cards and commentary that writes itself from formulas.
• SQL business analysis. A profitability and discount study of 51,290 Global Superstore transactions, written for a Sales Director.
• Sports analytics. A 40-query SQL recruitment study of 18,000+ FIFA 23 players and 678 clubs, written for a Sporting Director.

Every project starts with a named decision-maker and a business question, and ends with recommendations they could act on. Here I write up what I built, what the data showed, and what I learned along the way.

Tools: Excel (SUMIFS, INDEX/MATCH, dynamic charts, data validation) · SQL (CTEs, window functions, joins, CASE segmentation) · MySQL and SQLite

Suggested profile topics to follow: Data Analysis, SQL, Excel, Data Visualization, Sports Analytics

Article 1: Excel retail dashboard

I Built a One-Screen Sales Dashboard in Excel. It Found $135K in Losses Hiding Behind 51% Growth.

How 9,994 order lines, a few SUMIFS formulas and two dropdowns turned into a dashboard a manager can read in under a minute.

On paper, the business looked great. Sales grew 51% between 2014 and 2017. Profit grew 89%. Orders were up 74%.

But when I put four years of retail data onto a single Excel screen, a different story came through. One product category was earning almost nothing. One region was giving away nearly a quarter of its price in discounts. And every deep discount was losing money.

Here is how I built the dashboard, and what it showed.

The problem

The data was the Sample Superstore dataset: 9,994 order lines from a US office-products retailer, covering January 2014 to December 2017. It spans 793 customers, 49 states, 4 regions, 3 categories, 17 sub-categories and 1,850 products.

In raw form, that can’t answer simple questions a manager asks every week. Which region is growing fastest? Which products drive revenue? Where are we losing money?

My goal was one interactive page that answers all three. No PivotTables, no add-ins, nothing that breaks in an older version of Excel.

How I built it

I split the workbook into three layers so each part is easy to check:

1. Raw_Data. The 9,994 original rows plus four helper columns: OrderYear, OrderMonthNum, OrderMonthName and IsFirstOrderLine.
2. Pivot_Tables. Every summary the dashboard needs, all built with formulas.
3. Dashboard. A filter sidebar, four KPI cards, four charts and two commentary panels.

The IsFirstOrderLine column solved a small but real problem. Each row is a product line, not an order, so counting rows overstates orders. The column is 1 on the first line of each Order ID and 0 otherwise. Summing it gives the true order count: 5,009, not 9,994.

Formulas instead of PivotTables. Every summary reads two named cells on the dashboard, SelRegion and SelYear. Each formula checks whether a filter is set to “All” and only adds that condition when it needs to:

=IF(SelRegion="All",
    IF(SelYear="All", SUM(Sales), SUMIFS(Sales, Year, SelYear)),
    IF(SelYear="All", SUMIFS(Sales, Region, SelRegion),
       SUMIFS(Sales, Region, SelRegion, Year, SelYear)))

This updates instantly, works in every Excel version and in Google Sheets, and lets the written commentary use the same numbers as the charts.

A Top 10 that never ties. A hidden helper table lists all 1,850 products with their filtered sales. Two products with identical sales would break a LARGE() plus INDEX/MATCH ranking, so I added a tiny tiebreaker (row number × 0.0000001). LARGE() then finds the nth value and INDEX/MATCH returns the product name.

Commentary that writes itself. The CEO Insights and Recommendations panels are text formulas. They join calculated values such as the best month, the top and weakest region, and the overall margin into sentences. Change a filter and the commentary changes with the charts.

What the dashboard showed

Growth was real. Total sales were $2.30M and profit $286,397, a 12.5% margin. After a 2.8% dip in 2015, sales rose 29.5% in 2016 and 20.4% in 2017. Margin climbed from 10.2% in 2014 to about 13%.

Get miracle fordjour’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Demand is seasonal. September, November and December delivered 43% of all sales. February was the weakest month at $60K.

Central is the problem region, not South. South had the lowest sales, but Central had the lowest margin:

Region

Sales

Margin

Avg discount

West

$725,458

14.9%

10.9%

East

$678,781

13.5%

14.5%

Central

$501,240

7.9%

24.0%

South

$391,722

11.9%

14.7%

Central’s average discount was more than double the West’s. Two Central states, Texas (−$25.7K) and Illinois (−$12.6K), were among the biggest loss-makers in the country.

Furniture sells but doesn’t earn. The three categories each brought in about a third of sales. But Technology and Office Supplies earned around 17% margin, while Furniture earned 2.5%. Tables (−$17.7K) and Bookcases (−$3.5K) lost money outright.

Deep discounts lose money on every sale. Full-price lines earned a 29.5% margin. Discounts up to 20% still left 11.9%. Above 20%, the 1,393 discounted lines lost $135,376 combined. Above 40%, the business lost 77 cents for every dollar of revenue.

What I’d recommend

1. Cap discounts at 20%, with manager approval for anything higher.
2. Reprice or bundle Tables and Bookcases.
3. Audit discounting in Texas and Illinois and align it with the West.
4. Put stock and marketing behind Technology and Office Supplies.
5. Build inventory and staffing ahead of September and November–December.

What I learned

• Growth can hide leaks. The headline numbers were all good. The problems only showed once sales and profit sat side by side by region, category and discount level.
• Know your row grain. Counting order lines as orders would have nearly doubled the order count.
• Plain formulas go a long way. SUMIFS, INDEX/MATCH and two dropdowns gave me a fully interactive dashboard with no PivotTables or add-ins.

Limitations. This is a public sample ending in 2017, so it shows the method rather than current market conditions. Profit is taken as given, without cost of goods or returns broken out. Next, I’d add category and segment filters, year-over-year variance cards and a FORECAST.ETS sales forecast.

Tags: Excel, Data Analysis, Dashboard, Data Visualization, Business Intelligence