Text description with written audio for the video titled “Lab 7.6 Predicting Sales Revenue from Advertising Expense”
When done, close browser tab or window.
Start video.
Description 1
McGraw Hill red cube logo. Introduction to Business Analytics. Richardson, Weidenmier-Watson
Title screen: Lab 7.6 Predicting Sales Revenue from Advertising Expense—Walkthrough Video with Example Data Tableau Desktop (2022.3). © Copyright McGraw Hill.
Audio 1
Narrator: “This video is for Lab 7.6, Predicting Sales Revenue from Advertising Expense Using Tableau. In this lab, we’re going to use Tableau to create a trend line to determine the relationship between advertising expense and sales revenue. As you can see, I’ve already opened up Tableau, and we’re going to connect to our Excel file. So click on Microsoft Excel, find your file Lab 7.6 Data.”
Description 2
Microsoft Excel is selected from the To a File section of the Tableau home window. Lab 7.5 Data is selected after navigating backwards in the File Explorer.
The center of the screen is split in two: the top is where sheets are dragged to define relations, and the bottom shows the selected table’s details and a preview. The narrator collapses the Table Details within the bottom half of the page.
Audio 2
Narrator: “Now, because there’s only one tab of data, we don’t need to create any relationships. Let’s go ahead and change this. But let’s make sure that our data was imported properly. So month is a number. Date is formatted as the date. And these two variables are formatted as numbers, so that is correct. And so we are ready to begin our data analysis. So click on Sheet 1. Let’s drag Advertising Expense to the column shelf and Sales Revenue to the rows shelf.”
Description 3
The Sheet 1 button at the bottom left corner of the screen is selected. From the Data pane on the left side of the screen, Advertising Expense and Sales Revenue are dragged to the column and row shelves above the sheet.
Audio 3
Narrator: “Let’s then drag Month to Color in the Marks card.”
Description 4
The Marks card is found right of the Data pane and left of the Sheet. Month is dragged form the Data pane to the Color section of the Marks card.
Audio 4
Narrator: “To make the visual more easy to interpret, we need to not have the x-axis start at 0, because there’s nothing below 5,000. So right-click on Advertising Expense. Click Edit Axis.”
Description 5
The Edit Axis dialog box appears. Within the General tab of the dialog box is the Include Zero check box.
Audio 5
Narrator: “You need to uncheck Include Zero, and then click the X to close the box. We’re now ready to add a trend line. So let’s click Analytics and scroll down till you find Trend Line. Drag Trend Line into the Add a Trend Line box and put it over Linear.”
Description 6
The Data pane is changed to Analytics with the tab at the top of the pane. In the Model section, Trend Line is dragged to the Sheet. When hovering over the sheet, a box labeled Add a Trend Line appears with five options: Linear, Logarithmic, Exponential, Polynomial, and Power.
Audio 6
Narrator: “And a trend line is created, and when you hover over it, it gives you the equation of the line. Sales revenue or your y is equal to 146.661 times advertising expense, which is the x, plus a negative 739681. Let’s rename the sheet. So double click on Sheet1, and we’re going to call this Advertising.”
Description 7
The Sheet 1 tab is renamed by clicking its default title.
Audio 7
Narrator: “So we’re going to call this Advertising Sales Revenue, and click Enter. And let’s add some commas to our axes. So we’re going to right-click on the axis and select Format.”
Description 8
When Format is selected, the pane on the left side of the screen updates to Format. Under the Scale section, Numbers is changed to Number (Standard).
Audio 8
Narrator: “And under Number, let’s change it to Number Standard. Click out. I want to do the same thing for Advertising, so let’s right-click, click on Format, and change the numbers to Number Standard, and click out. If you want more information about your trend line, if you right-click on the trend line, you’re going to have a Describe Trend Line box, and it shows you that the p-values are very small for advertising expense.”
Description 9
The Describe Trend Line dialog box contains a table of coefficients.
Audio 9
Narrator: “So there is a significant positive relationship between advertising expense and sales revenue. You want even more information, right-click on the Trend Line and do Describe Trend Model.”
Description 10
The Describe Trend Model dialog box appears with list of various data.
Audio 10
Narrator: “This gives you the full output for the model itself. You can make this bigger if it makes it easier to read. So as you can see, your R-squared is 0.88. There is no adjusted R-squared in Tableau. And then we know that advertising expense is significant since it’s a small value. And that concludes Labs 7.6 in Tableau.”
Video has ended.
Transcript
0:00[Female Narrator:] This video is for Lab 7.6, Predicting Sales Revenue
0:03from Advertising Expense Using Tableau.
0:06In this lab, we’re going to use Tableau to create a trend line
0:09to determine the relationship between advertising expense and sales revenue.
0:13As you can see, I’ve already opened up Tableau, and we’re going to connect to our Excel file.
0:19So click on Microsoft Excel, find your file Lab 7.6 Data.
0:27Now, because there’s only one tab of data, we don’t need to create any relationships.
0:31Let’s go ahead and change this.
0:33But let’s make sure that our data was imported properly.
0:35So month is a number.
0:37Date is formatted as the date.
0:39And these two variables are formatted as numbers, so that is correct.
0:44And so we are ready to begin our data analysis.
0:46So click on Sheet1.
0:48Let’s drag Advertising Expense to the column shelf and Sales Revenue to the rows shelf.
0:54Let’s then drag Month to Color in the Marks card.
1:00To make the visual more easy to interpret, we need to not have the x-axis start at 0,
1:07because there’s nothing below 5,000.
1:12So right-click on Advertising Expense.
1:15Click Edit Axis.
1:20You need to uncheck Include Zero, and then click the X to close the box.
1:24We’re now ready to add a trend line.
1:26So let’s click Analytics and scroll down till you find Trend Line.
1:31Drag Trend Line into the Add a Trend Line box and put it over Linear.
1:39And a trend line is created, and when you hover over it, it gives you the equation of the line.
1:43Sales revenue or your y is equal to 146.661 times advertising expense,
1:52which is the x, plus a negative 739681.
1:55Let’s rename the sheet.
1:56So double click on Sheet1, and we’re going to call this Advertising.
2:01[ Silence ]
2:13So we’re going to call this Advertising Sales Revenue, and click Enter.
2:19And let’s add some commas to our axes.
2:21So we’re going to right-click on the axis and select Format.
2:27And under Number, let’s change it to Number Standard.
2:32Click out.
2:33I want to do the same thing for Advertising, so let’s right-click, click on Format,
2:38and change the numbers to Number Standard, and click out.
2:44If you want more information about your trend line, if you right-click on the trend line,
2:47you’re going to have a Describe Trend Line box,
2:50and it shows you that the p-values are very small for advertising expense.
2:54So there is a significant positive relationship between advertising expense and sales revenue.
2:59You want even more information, right-click on the Trend Line and do Describe Trend Model.
3:03This gives you the full output for the model itself.
3:11You can make this bigger if it makes it easier to read.
3:12So as you can see, your R-squared is 0.88.
3:16There is no adjusted R-squared in Tableau.
3:19And then we know that advertising expense is significant since it’s a small value.
3:26And that concludes Labs 7.6 in Tableau.
Part 1 of 3
Part 2 of 3
Question Answer
1. The regression results suggest what type of relationship between advertising expense and sales revenue?
a) a positive
b) a negative
c) a uniform
d) no relationship
2. In the data visualization, what does the X-axis represent?
a) the codependent variable
b) the dependent variable
c) the independent variable
d) the intercept
3. How many observations are included in the dataset?
a) 35
b) 36
c) 37
d) 38
4. If LeTable Incorporated spends $25,000 on advertising, what is the estimated sales revenue (rounded to the nearest dollar.)?
a) $629,380
b) $2,926,819
c) $3,200,000
d) $5,000,000
5. Look at Lab Exhibit 7.6E.3 in Lab 7.6 Excel. How do the regression results in Excel (Lab 7.6 Excel) compare to the results in this lab
(Lab 7.6 Tableau) with a trend line in Tableau? (Recall that the labs use the same dataset.)
a) Tableau results are better.
b) The results cannot be compared.
c) They are different.
d) They are essentially the same.
Part 2 of 3