Lab Note: The tools presented in this lab periodically change. Updated instructions, if applicable, can be found in the eBook and lab walkthrough videos in Connect.

Case Summary: After running diagnostic analysis on Dillard$$s transactions, you have found that there is a statistically significant difference between the amount of money customers spend in online transactions versus in$-$person transactions. You decide to take this a step further and design a predictive model to help determine how much a customer will spend based on the transaction type $($online or in$-$person$).$ You will run a simple regression to create a predictive model using one explanatory variable in Part $1,$ and in Part $2$ you will extend your analysis to add in an additional explanatory variable$$tender type $($the method of payment the customer chooses$).$

Data: Dillard$$s sales data are available only on the University of Arkansas Remote Desktop $($waltonlab$.$uark.edu$).$ See your instructor for login credentials.

By the end of this lab, you will create a regression analysis. While your results will include different data values, your work should look similar to this:

The screenshot shows the header on the top reads Summary output. Two of the tables below it are labeled Regression statistics and ANOVA. Regression Statistics lists Multiple R$,$ R Square, Adjusted R Square, Standard Error, and Observation; ANOVA lists Regression, Residual, and total; and the last table lists Intercept and X Variable $1.$

$-2$:$52$

$1$x

Click here for full size Example Output.

Before you begin the lab, you should create a new blank Word document where you will record your screenshots and save it as Lab $3-6[$Your name$][$Your email address$].$docx.

Dillard$$s is trying to figure out when its customers spend more on individual transactions. We ask questions regarding how Dillard$$s sells its products.

Microsoft $|$ Excel $+$ Power Query

From Microsoft Excel, click the Data tab on the ribbon.

Note: If you completed Lab $3-5,$ you can edit the query from your Lab $3-5$ spreadsheet instead of starting from scratch. Skip Step $2$ below, and open your Lab $3-5$ spreadsheet instead. Click into your data, and a tab for Query will appear in the ribbon. From the Query tab, select Edit to open the Power Query editor. In Query Settings $($the pane to the right$),$ delete the Pivot step. Now you can proceed to step $3$ below.

Click Get Data $>$ From Database $>$ From SQL Server Database.

Server: essql$1.$walton.uark.edu

Database: WCOB$\_$Dillards

Expand Advanced Options and input the following query:

SELECT $*$

FROM TRANSACT

WHERE TRAN$\_$DATE BETWEEN $\text{'}20160901\text{'}$ AND $\text{'}20160905\text{'}$ AND TRAN$\_$AMT $>0$

Click OK$.$

Click Edit or Transform Data.

Now that you have created a data connection, you can create new columns to isolate the online sales from the in$-$person sales. The online sales store ID is $698.$ Unlike the way you performed this change to run a t$-$test, this time you only need to create the Conditional Column $($no need to Pivot the columns$),$ but you will name the variables $1$ and $0$ instead of online and in$-$person. This is because regression analysis requires all variables to be numeric.

From the Power Query ribbon, select Add Column, then Conditional Column.

Input the following in the Add Conditional Column window:

New column name: Online$-$Dummy

Column Name: STORE

Operator: equals

Value: $698$

Output: $1$

Otherwise: $0$

Click OK$.$

From the Power Query ribbon, select the Home tab and Close & Load.

Take a screenshot $($label it $3-6$MA$).$

Perform a regression analysis by performing the following steps:

Click on the Data Analysis button in the Data tab in the ribbon. If you do not have the Data Analysis ToolPak added in$,$ see Appendix C to learn how to add it to Excel.

Click Regression, and then click OK$.$

Reference the cells that contain the TRAN$\_$AMT in the Input Y Range and Online$-$Dummy in the Input X Range and then click OK$.$ Note: Regression Analysis does not accept null values, so it will not work to select the entire columns for your Y and X ranges. Be sure to select only the column label and the values; the values should extend to row $570,062.$

Take a screenshot $($label it $3-6$MB$)$ of your results.

Save your file as Lab $3-6$ Dillard$$s Regression.xlsx$.$