Why do you think that is so?
Place your order now for a similar assignment and have exceptional work written by our team of experts, At affordable rates
For This or a Similar Paper Click To Order Now
Class – Textbook – https://drive.google.com/file/d/1XYPJeEoLdNV65CU2j…
In class Assignment
Lab 3 Comprehensive Case: Data Abstract (SQL) and Regression
Data
This dataset contains sales data for Dillard’s Department Stores in the following tables.The ER Diagram and table descriptions are at the end of this document.
The data for this lab and all other Dillard’s labs must be accessed through the University of Arkansas Remote Desktop. Directions for accessing the Remote Desktop can be found on Canvas in an assignment named U Arkansas Login.Your log on credential is in the comments of the assignment.
Software needed
Excel 2016 (available on the Remote Desktop at the University of Arkansas)
In this lab, you will:
Conduct analysis on three important questions that help us understand when customers spend more on individual transactions.
Before we get started.
Login to University of Arkansas remote desktop.
https://waltonlab.uark.edu/
Enter your username and password (from Canvas in assignments)
Part 1: Identify the Questions
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.
Q1.Customers in which states had the highest transaction balances over the entire sample period? over the week September 1, 2016, to September 9, 2016?
Q2.Do customers in the state with the highest transaction balances have a significantly higher transaction balance from September 1, 2016, to September 9, 2016, than all other states?
Q3.Are online transaction amounts statistically greater than or lesser than non-online transactions during the time period September 1, 2016, to September 9, 2016?
Part 2: Master the Data
For this lab, you should access the TRANSACT and the STORE tables from the Dillard’s WCOB_DILLARDS dataset from the University of Arkansas. You can also learn how to do so in Appendix F.
Let’s address our firs questions: “Customers in which states had the highest transaction balances over the entire sample period? over the week September 1, 2016, to September 9, 2016?
To address the first question, in Microsoft Excel, run a query that retrieves the Sales (TRAN_AMT) and calculates the average Sales by State (STATE) for the DATE BETWEEN ‘20160901’ AND ‘20160909’. Order the list in descending order based on the average transaction amount.
Refer to last page of the textbook to view an ER Diagram for the Dillard’s data.
Server: essql1.walton.uark.edu
File/Database: WCOB_DILLARDS
Click Advanced and enter the SQL code below, then click OK:
List the sales for the top 3 states for Question 1
Part 3: Perform an Analysis of the Data
Noting that Arkansas (State =‘AR’) has the highest transaction balance, let’s address our second question: “Do customers in the state with the highest transaction balances have a significantly higher transaction balance from September 1, 2016, to September 9, 2016, than all other states?”
To address Q2, you will need to connect to SQL Server data through Excel using Data > Get & Transform and the Analysis ToolPak. If you need a refresher on how to use that tool, refer to the textbook’s Appendix B.
Once the query runs, there is no need to transform the data in Power Query for this lab. Immediately load the data into Excel. click on “Load” (may take a minute or two)
Once the data are in Excel, you’ll need to transform the State data to perform regression analysis on the state of Arkansas to address Q2. To do so, make a new column just right of the existing dataset and label it Arkansas-dummy in column Q. Type the formula =IF([@STATE]=“AR”,1,0) in each row. It will assign a value of 1 to transactions at stores in Arkansas and a value of 0 for transactions at stores outside of the Arkansas. Copy this formula all the way down to cover each row.
Perform a regression analysis by performing the following steps.Click on Data Analysis button in the Data tab in the ribbon. If you do not have the Data Analysis ToolPak added in, see the Appendix B, to learn how to add it to Excel.(Click on File/Options/Add-ins/Analysis ToolPak/Go
Click Regression (as shown below), and then click OK.
Once you connect to the SQL Server dataset in Excel and expand the options to input a query, input the following SQL query to extract the data needed for our analysis.
Go to Data/Get Data/From Database/From SQL Server Database
Server: essql1.walton.uark.edu
File/Database: WCOB_DILLARDS
Click Advanced and enter the SQL code below, then click OK:
Then click on “Load” (may take a minute or two)
SELECT TRANSACT.*, STORE.STATE
FROM TRANSACT
INNER JOIN STORE
ON TRANSACT.STORE = STORE.STORE
WHERE TRAN_DATE BETWEEN ‘20160901’ AND ‘20160909’
ORDER BY TRAN_DATE
Source: Microsoft Excel 2016.
Reference the cells that contain the Tran_AMT in the Input Y Range and Arkansas-dummy in the Input X Range and then click OK
Your output should look like the screenshot below. The t Stat greater than 2.0 suggests that the transaction amount (Tran_Amt) is statistically greater in Arkansas than in all other states.
Put screenshot of your results on the answer sheet for Q2.
To address this question, we need to transform the Store variable into an onlinedummy variable. Dillard’s handles all of their online sales through a fulfillment center in Maumelle, Arkansas. This location has the store number 698. We will use an IF statement to transform all transactions in store number 698 into a 1 to indicate they are online sales, and all other transactions will be indicated as a 0. The IF statement is the following (we have hidden a few columns in the middle of the table to make the screenshot easier to read):
Put a screenshot of your regression on the answer sheet for Q3
We are now ready to address our third question: Are online transaction amounts statistically greater than or lesser than non-online transactions during the time period September 1, 2016, to September 9, 2016? Because we found that transactions in Arkansas are statistically higher than all other states, we will include that finding in our analysis as well, making this a multivariate regression.
Source: Microsoft Excel 2016.
Once this is complete, copy the calculation for all cells in the column.
We’re now ready for regression analysis. Reference the cells that contain the Tran_AMT in the Input Y Range and reference Arkansas-dummy and Online-dummy in the Input X Range and click OK.
The results of the regression analysis suggesting that both Transactions in Arkansas and Transactions done online are associated with greater transaction amounts are below.
Source: Microsoft Excel 2016.
Part 4: Address and Refine Results
Q4.How would you interpret the results of your analysis of Q1 in plain English? Why do you think the state of Arkansas had the highest transaction volume?
Q5.The analysis of Q2 addressed whether Arkansas had a statistically higher transaction volume than other states. How did the regression tests show or not show a statistical difference? Does this have any implications for the marketing for Dillard’s? Do you think it is because Arkansas is the home base for Dillard’s?
Q6.The regression analysis suggests that online sales are associated with greater transactions amounts. Why do you think that is so?
Dillard’s Tables
Dillard’s ER Diagram
