Your Perfect Essay is Just a Click Away

Starting at $8.00 per Page

100% Original, Plagiarism Free, Customized to Your instructions!

Order Now My Account

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

Do you need any assistance with this question?
Send us your paper details now
We’ll find the best professional writer for you!

"We offer the best custom paper writing services. We have done this question before we can also do it for you."

  • 100% non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee

Our Service Charter


1. Professional & Expert Writers: Experts Essays only hires the best. Our writers are specially selected and recruited, after which they undergo further training to perfect their skills for specialization purposes. Moreover, our writers are holders of masters and Ph.D. degrees. They have impressive academic records, besides being native English speakers.

2. Top Quality Papers: Our customers are always guaranteed of papers that exceed their expectations. All our writers have +5 years of experience. This implies that all papers are written by individuals who are experts in their fields. In addition, the quality team reviews all the papers before sending them to the customers.

3. Plagiarism-Free Papers: All papers provided by Experts Essays are written from scratch. Appropriate referencing and citation of key information are followed. Plagiarism checkers are used by the Quality assurance team and our editors just to double-check that there are no instances of plagiarism.

4. Timely Delivery: Time wasted is equivalent to a failed dedication and commitment. Experts Essays are known for the timely delivery of any pending customer orders. Customers are well informed of the progress of their papers to ensure they keep track of what the writer is providing before the final draft is sent for grading.

5. Affordable Prices: Our prices are fairly structured to fit in all groups. Any customer willing to place their assignments with us can do so at very affordable prices. In addition, our customers enjoy regular discounts and bonuses.

6. 24/7 Customer Support: At Experts Essays, we have put in place a team of experts who answer all customer inquiries promptly. The best part is the ever-availability of the team. Customers can make inquiries anytime.