Why external data expands analysis in Google Sheets - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants you to analyze monthly sales data combined with external weather data to understand how weather affects sales.

📊 Data: You have internal sales data by month and region, and external weather data showing average temperature and rainfall by month and region.

🎯 Deliverable: Create a combined report that shows sales alongside weather data and highlights any correlation.

Sample Data

Month	Region	Sales
Jan	North	1200
Jan	South	1500
Feb	North	1100
Feb	South	1600
Mar	North	1300
Mar	South	1700

Month	Region	Avg Temp (°C)	Rainfall (mm)
Jan	North	5	80
Jan	South	15	60
Feb	North	7	70
Feb	South	16	55
Mar	North	10	50
Mar	South	18	40

1

Step 1: Create a new sheet named 'Combined Data' to merge sales and weather data.

No formula needed, just create a new sheet.

Expected Result

A blank sheet named 'Combined Data' is ready.

2

Step 2: Copy the sales data headers and rows into 'Combined Data' starting at cell A1.

Copy Month, Region, Sales columns and data.

Expected Result

Sales data appears in 'Combined Data' columns A to C.

3

Step 3: Add new headers in 'Combined Data' for Avg Temp (°C) in column D and Rainfall (mm) in column E.

Type headers manually: D1='Avg Temp (°C)', E1='Rainfall (mm)'.

Expected Result

Headers for weather data appear in columns D and E.

4

Step 4: Use VLOOKUP to bring Avg Temp (°C) from weather data into 'Combined Data'.

=ARRAYFORMULA(IF(A2:A="","",VLOOKUP(A2:A&B2:B, {Weather!A2:A&Weather!B2:B, Weather!C2:C}, 2, FALSE)))

Expected Result

Column D fills with average temperature matching Month and Region.

5

Step 5: Use VLOOKUP to bring Rainfall (mm) from weather data into 'Combined Data'.

=ARRAYFORMULA(IF(A2:A="","",VLOOKUP(A2:A&B2:B, {Weather!A2:A&Weather!B2:B, Weather!D2:D}, 2, FALSE)))

Expected Result

Column E fills with rainfall matching Month and Region.

6

Step 6: Create a new column F named 'Sales per Degree' to calculate sales divided by average temperature.

=ARRAYFORMULA(IF(A2:A="","",C2:C/D2:D))

Expected Result

Column F shows sales divided by temperature for each row.

7

Step 7: Create a chart to visualize Sales and Avg Temp by Month and Region.

Insert > Chart, select data range A1:E7, choose Combo chart with Sales as bars and Avg Temp as line.

Expected Result

Chart shows sales and temperature trends side by side.

Final Result

Combined Data Sheet

Month | Region | Sales | Avg Temp (°C) | Rainfall (mm) | Sales per Degree
--------------------------------------------------------------------------
Jan   | North  | 1200  | 5             | 80            | 240
Jan   | South  | 1500  | 15            | 60            | 100
Feb   | North  | 1100  | 7             | 70            | 157.14
Feb   | South  | 1600  | 16            | 55            | 100
Mar   | North  | 1300  | 10            | 50            | 130
Mar   | South  | 1700  | 18            | 40            | 94.44

✓Sales tend to be higher in the South region where temperatures are warmer.

✓Sales per degree is higher in the North, suggesting temperature impacts sales differently by region.

✓Rainfall is higher in the North but does not show a clear direct effect on sales.

Bonus Challenge

Add a correlation calculation between Sales and Avg Temp for each region.

Show Hint

Use the CORREL function with FILTER to select sales and temperature by region.