Why date handling is common in business in Excel - Business Case Study

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand how sales change over time and identify monthly trends.

📊 Data: You have sales data with dates and amounts for each transaction.

🎯 Deliverable: Create a summary report showing total sales per month and highlight the month with the highest sales.

Sample Data

Date	Sales Amount
2024-01-05	150
2024-01-15	200
2024-02-10	300
2024-02-20	250
2024-03-05	400
2024-03-25	350
2024-04-10	500
2024-04-15	450

1

Step 1: Extract the month and year from each date to group sales by month.

In a new column next to sales data, enter =TEXT(A2, "yyyy-mm") and copy down.

Expected Result

For 2024-01-05, the result is "2024-01"; for 2024-02-10, "2024-02", etc.

2

Step 2: Create a summary table listing each unique month-year and total sales for that month.

List unique months in one column. Next to each, use =SUMIF(C:C, E2, B:B) where C:C is month-year column, E2 is the month to sum, B:B is sales.

Expected Result

For 2024-01 total sales = 350, for 2024-02 total sales = 550, for 2024-03 total sales = 750, for 2024-04 total sales = 950.

3

Step 3: Identify the month with the highest total sales.

Use =INDEX(E2:E5, MATCH(MAX(F2:F5), F2:F5, 0)) where E2:E5 is months and F2:F5 is total sales.

Expected Result

The month with highest sales is "2024-04".

4

Step 4: Format the summary table with clear headers: Month-Year and Total Sales.

Manually add headers in the summary table cells.

Expected Result

Headers appear as 'Month-Year' and 'Total Sales' above the data.

Final Result

Month-Year | Total Sales
-------------------------
2024-01    | 350
2024-02    | 550
2024-03    | 750
2024-04    | 950

Highest Sales Month: 2024-04

✓Sales increase steadily from January to April.

✓April has the highest sales total of 950.

✓Grouping sales by month helps spot trends over time.

Bonus Challenge

Create a line chart showing sales trend over the months.

Show Hint

Select the summary table and insert a line chart to visualize monthly sales changes.