Scatter plots in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand the relationship between advertising spend and sales revenue across different months.

📊 Data: You have monthly data showing advertising spend in dollars and sales revenue in dollars for the past 12 months.

🎯 Deliverable: Create a scatter plot chart in Excel that shows advertising spend on the X-axis and sales revenue on the Y-axis to visualize their relationship.

Sample Data

Month	Advertising Spend ($)	Sales Revenue ($)
Jan	5000	20000
Feb	7000	25000
Mar	6000	22000
Apr	8000	27000
May	7500	26000
Jun	9000	30000
Jul	8500	28000
Aug	9500	32000
Sep	7000	24000
Oct	6500	23000
Nov	8000	27000
Dec	10000	35000

1

Step 1: Select the data range including the Advertising Spend and Sales Revenue columns (B1:C13).

Expected Result

Cells B1 to C13 are selected.

2

Step 2: Go to the Insert tab on the Excel ribbon.

Expected Result

Insert tab is open.

3

Step 3: Click on the Scatter chart icon in the Charts group.

Expected Result

Scatter chart options appear.

4

Step 4: Choose the first Scatter chart option (Scatter with only Markers).

Expected Result

A scatter plot appears on the worksheet showing Advertising Spend on the X-axis and Sales Revenue on the Y-axis.

5

Step 5: Add chart title by clicking on the chart title area and typing 'Advertising Spend vs Sales Revenue'.

Expected Result

Chart title is set to 'Advertising Spend vs Sales Revenue'.

6

Step 6: Label the X-axis by clicking on the horizontal axis title and typing 'Advertising Spend ($)'.

Expected Result

X-axis is labeled 'Advertising Spend ($)'.

7

Step 7: Label the Y-axis by clicking on the vertical axis title and typing 'Sales Revenue ($)'.

Expected Result

Y-axis is labeled 'Sales Revenue ($)'.

Final Result

Scatter Plot: Advertising Spend vs Sales Revenue

Y-axis (Sales Revenue $)
|
|        *        *
|      *    *   *  *
|    *              *
|  *                 *
|*                    *
+-----------------------------> X-axis (Advertising Spend $)

✓There is a positive relationship between advertising spend and sales revenue.

✓Higher advertising spend generally corresponds to higher sales revenue.

✓The scatter plot helps visualize how sales increase as advertising investment grows.

Bonus Challenge

Add a trendline to the scatter plot to show the overall trend between advertising spend and sales revenue.

Show Hint

Right-click on any data point in the scatter plot, select 'Add Trendline', and choose 'Linear'.