BUSN2037 Fundamentals of Accounting Data Analytics
Semester 1 2025
Assignment - Estimating Dillard’s Sales Returns
There are two parts to this assignment: (1) Written Submission, and (2) Presentation.
WRITTEN & DATA FILES SUBMISSION (20% of your overall course grade)
Prepare a report to be submitted to the management of Dillard’s. The report should comprise the following three sections: (1) Descriptive and Exploratory Analytics (15 marks), (2) Diagnostic Analytics (25 marks), and (3) Predictive Analytics (30 marks) (these mirror the three sections in the Instructions). In the report, you will need to address all the requirements highlighted in red in the Instructions. To enrich your report, you may wish to include an introduction section and a conclusion/recommendation section. The quality of writing, visualisations and formatting of the report will be graded (15 marks). Completeness of the Excel and Tableau files will be assessed (15 marks). If the Excel and Tableau files are not submitted, the written submission will NOT be graded.
• PAGE LIMIT: 12 pages (this includes all tables and visualisations but excludes the assignment cover page and references)
• FORMATTING: Single spacing, 12 size Times New Roman or Arial font and minimum 2 cm margins. Insert page numbers. Ensure visualisations are clear by inserting at a high resolution.
• WRITTEN SUBMISSION DEADLINE: Monday 12 May 2025 at 5pm
• SUBMISSION LINKS: Submit your report and data files via the links on Wattle.
PRESENTATION (10% of your overall course grade)
You will be required to make a 10-minute Zoom presentation in Week 11. A sign-up sheet for presentation time slots will be made available on the Wattle platform in Week 7.
Assuming that the presentation is to Dillard’s management, you will be given 5 minutes to discuss your findings and 5 minutes to answer some questions from the management. For the first 5 minutes, you should focus on the analysis from Section 3 of the assignment. You will need to discuss the questions asked, the analysis conducted and the findings. You will need to prepare some slides (aim for no more than 5 slides) to assist with your presentation.
In the 5-minute of Q&A, you may be asked clarification questions on the presentation, and/or questions about the analysis performed and the findings.
INSTRUCTIONS
In this assignment, you will describe (Section 1), diagnose (Section 2), and predict sales returns for Dillard’s (Section 3). Using these various analyses, you will develop a potential model useful in predicting Dillard’s sales returns and test it for reasonableness.
Data needed: Dillard’s sales data are available only on the University of Arkansas Remote Desktop (waltonlab.uark.edu).
There are three sections to this assignment:
• Section 1 focuses on exploring the sum of returns and the percentage of sales returns using Excel and Tableau data visualisations.
• Section 2 continues the analysis with hypothesis tests to see if the percentage of sales returned is significantly higher during the holiday season (January) than any other time of the year.
• Section 3 focuses on exploring how historical data can help predict the future percentage of sales returns through PivotTables, PivotCharts, and regression testing in Excel.
Each section has instructions to guide you through mastering the data, performing the analysis, and communicating your results. You will be using both Excel and Tableau to conduct the analysis and to prepare the visualisations.
SECTION 1: DESCRIPTIVE AND EXPLORATORY ANALYTICS
Compare the percentage of sales returns across months, and online versus in-person transactions
In this section, you will prepare the data for analysis in Microsoft Excel Power Query and Tableau Prep, and explore the data by calculating descriptive statistics and determining variables worth evaluating in your diagnostic analysis. In particular, you will analyse percentage of sales returns over time (i.e., 1 Jan 2014 to 17 October 2016) and the differences in percentages of sales returns for online purchases versus in-person purchases.
Required: Identify and discuss three questions that you think Dillard’s management, auditors, or financial analysts would want to know about sales returns that could be answered through descriptive analytics. (Hint: To help you get started, the first question should be on the variation in the average percentage of sales returns across months. The second question should be on the similarities between online and in-person transactions. For the third, you need to decide on a question that might provide good insights for Dillard’s management). In your discussion, for each question identified, provide and justify your expectations (i.e., what you believe that you would observe from examining the data).
Microsoft | Excel + Power Query
1. After logging in to the UARK’s Remote Desktop, open Microsoft Excel and click the Data tab on the ribbon.
2. Click Get Data > From Database > From SQL Server Database.
a. Server: essql1.walton.uark.edu
b. Database: WCOB_Dillards
c. Expand Advanced Options and input the following query:
SELECT MONTH(TRAN_DATE) AS MONTH, TRAN_DATE, STATE, TRANSACT.STORE, TRAN_TYPE, SUM(SALE_PRICE) AS AMOUNT
FROM TRANSACT
INNER JOIN STORE
ON TRANSACT.STORE = STORE.STORE
GROUP BY MONTH(TRAN_DATE), TRAN_DATE, STATE, TRANSACT.STORE, TRAN_TYPE
d. Click OK (if prompted: Connect using current credentials, and click OK).
e. Click Edit or Transform Data.
3. There are several changes you need to make to the data to prepare them for analysis: pivoting the tran_type column so you have two separate columns for purchases and returns, creating a calculated field for percentage of sales returns, and creating a conditional column indicating whether transactions were performed online or in-person.
a. Pivot the tran_type column:
1. Select the TRAN_TYPE column.
2. From the Transform. tab in the ribbon, select Pivot Column.
3. Values Column: Amount
4. Click OK.
b. Create a calculated field for percentage of sales returns:
1. From the Add Column tab in the ribbon, select Conditional Column.
2. New column name: % of Returned Sales
3. Column name: P
4. Operator: equals
5. Value: 0
6. Output: 0
7. Else: = [R]/[P]
8. Click OK.
c. Create a conditional column for online versus in-person transactions:
1. From the Add Column tab in the ribbon, select Conditional Column.
2. New Column Name: Online-dummy
3. Column Name: STORE
4. Operator: equals
5. Value: 698
6. Output: Online
7. Else: In-Person
8. Click OK.
d. From the Home tab in the ribbon, select Close & Load.
e. Once your data load into Excel, name the spreadsheet Assignment Query Data.
f. Next, add the data to the data model through Power Pivot and create a date table:
1. If Power Pivot is not added, you will need to enable the Power Pivot add-in (File > Options > Add-ins > Manage: COM add-ins, select GO, then enter a check mark next to Microsoft Power Pivot for Excel, click OK).
2. From the Power Pivot tab in the ribbon, select Add to Data Model.
3. From the Power Pivot window, select the Design tab > Date Table> New.
4. Once the Date table populates, select PivotTable from the Home tab and click OK to create a PivotTable in a new worksheet.
5. Name the spreadsheet with the new PivotTable S1 PivotTable.
4. In Excel, create the following PivotTables and PivotCharts (for PivotTables, include relevant conditional formatting):
a. Average % of Returned Sales by month to indicate the months with the highest and lowest averages.
• When using fields from the Calendar table and the query table, you will need to build relationships. Once you add fields from each table, Excel will prompt you to create relationships. Let Excel do this automatically for you by clicking Auto-Detect... If Excel does not detect the relationship, you can build it manually. The matching fields are Date Table.Date and Query1.TRAN_DATE.
b. Average % of Returned Sales for online transactions versus in-person transactions.
5. Add two slicers and adjust the report connections for each so that they interact and slice each of your PivotTables and PivotCharts:
a. Month
b. Online-Dummy
6. Arrange your Slicers, PivotTables and PivotCharts so that they are all easily viewable on your Excel sheet.
7. Save your workbook as [Your Uni ID].xlsx.
Tableau | Prep and Desktop
1. Open Tableau Prep Builder.
2. In Tableau Prep, click Connect to Data.
3. Choose Microsoft SQL Server in the Connect list.
4. Enter the following and click Sign In:
a. Server: essql1.walton.uark.edu
b. Database: WCOB_DILLARDS
5. Double-click Custom SQL to input the following SQL query:
SELECT TRAN_DATE, STATE, TRANSACT.STORE, TRAN_TYPE,
SUM(SALE_PRICE) AS AMOUNT
FROM TRANSACT
INNER JOIN STORE
ON TRANSACT.STORE = STORE.STORE
GROUP BY TRAN_DATE, STATE, TRANSACT.STORE, TRAN_TYPE
6. Click Run.
7. There are several changes you need to make to the data to prepare them for analysis: pivoting the tran_type column so you have two separate columns for purchases and returns, creating a calculated field for percentage of returned sales, and creating a conditional column indicating whether transactions were performed online or in-person.
8. Pivot the tran_type column:
a. Click the plus button next to the Custom SQL Input Icon and select Pivot.
b. In the Pivoted Fields window, change the Pivot type from Columns to Rows to Rows to Columns (click the dropdown next to Columns to Rows).
c. Field that will pivot rows to columns: TRAN_TYPE
d. Field to aggregate for new columns: AMOUNT
9. Create a calculated field for percentage of returned sales:
a. Click the plus button next to the Pivot Icon and select Clean Step.
b. Click Create Calculated Field. . .
c. Field Name: % of Returned Sales
d. Calculation: if [P] = 0 then 0 else [R]/[P] end
e. Click Save.
10. Create a conditional column for online versus in-person transactions:
a. Click Create Calculated Field. . .
b. Field Name: Online-dummy
c. Calculation: if [STORE] = 698 then ‘online’ else ‘in-person’ end
d. Click Save.
11. To save the file, click the plus button next to the Clean Icon and select Output.
• Select Browse and save the file as Assignment.hyper (Take note of where the file is saved. If you saved your file to the default location, the path will be Documents > My Tableau Prep Repository > Datasources).
b. Click Run Flow.
c. Save the Tableau Prep file as Assignment.tfl.
12. Open Assignment.hyper in Tableau Desktop and navigate to Sheet 1.
13. In Tableau, create the following visualisations on separate sheets:
a. Average % of Returned Sales by month to indicate the months with the highest and lowest averages. Label this sheet as Variation by Month.
b. Average % of Returned Sales for online transactions versus in-person transactions. Label this sheet as Online vs In Person
c. Arrange each of the visualisations you created into a dashboard and set each visualisation as a filter for the dashboard. Label this dashboard as S1 Dashboard.
2. Save your workbook as [Your Uni ID].twbx.
Required: Provide a brief discussion of the analysis conducted above and any additional descriptive analysis that you have conducted to answer the three questions that you have identified. Using the reports or visualisations generated above and any additional reports of visualisation generated, discuss the findings to the three questions.
SECTION 2: DIAGNOSTIC ANALYTICS
Part 1 What might be driving the variation in the average percentage of returned sales across months?
In the previous activity for this dataset, you may have observed that the average percentage of returned sales varies across months. You suspect that this is likely due to the holiday season and decided to compare January to the rest of the year more specifically. In Excel, you will drill down into the data to determine if the difference between the January percentage of returned sales and the rest of the year is statistically significant. To do so, you will run a hypothesis test to determine if a significantly higher percentage of sales are returned during the month of January than the rest of the months. Tableau does not offer the functionality to run statistical t-tests, but you can still create a set and compare the January percentage of returned sales to the rest of the year.
Microsoft | Excel
1. Access the Power Query Editor to prepare your data for running a hypothesis test:
a. Open your Excel file saved as [Your Uni ID].xlsx (from Section 1) and select a cell of the table in the Assignment Query Data sheet to activate the Query tab in the Excel ribbon.
b. From the Query tab, select Edit to open the Power Query editor. If prompted to do so, click Edit Permissions and Run the Native Database Query in the window that pops up. Then repeat a similar process by clicking Edit Credentials, clicking Connect, allowing Encryption Support, and clicking OK. The query data should show up in the Power Query Editor now.
2. You will perform. three actions: duplicate the existing % of Returned Sales column, add a new conditional column to create a holiday dummy variable, and pivot your new conditional column on % of Returned Sales.
a. Duplicate the existing % of Returned Sales column:
1. Select the % of Returned Sales column.
2. From the Add Column tab in the ribbon, click Duplicate column.
b. Add a new conditional column for the holiday dummy variable:
1. From the Add Column tab in the ribbon, select Conditional Column.
a. New column name: Holiday-Dummy
b. Column Name: Month
c. Operator: Equals
d. Value: 1
e. Output: Holiday
f. Else: Non-Holiday
g. Click OK.
2. Pivot the Holiday-Dummy column:
a. Select the Holiday-Dummy column.
b. From the Transform. tab in the ribbon, click Pivot Column.
c. Values Column: % of Returned Sales - Copy and click OK.
3. Return to the Home tab in the ribbon and click Close & Load.
4. Once your data have loaded, you will run a t-test to see if the percentage of sales returned in January is significantly higher than the rest of the year.
a. From the Data tab in the ribbon, click Data Analysis.
• If the Data Analysis Toolpak is not available, you will need to add this in via File\Options\Add-ins.
b. Select t-Test: Two Sample Assuming Unequal Variances, click OK, then enter the following:
1. Variable 1 range: all Holiday values (including the label)
2. Variable 2 range: all Non-Holiday values (including the label)
3. Place a check mark next to Labels.
4. Output options: New Worksheet Ply
5. Click OK.
2. The output for the hypothesis test will appear on a new sheet in your Excel workbook. Name this sheet S2 t-test.
3. Save your workbook!
Tableau | Desktop
1. While you cannot run a t-test in Tableau, you can drill down further into the data to create a Holiday set and compare the % of returned sales during January versus the rest of the year. To do so, you will add Month to the visualisation, select January, create a set named Holiday, then create visualisations using the new Holiday set.
2. Create a new sheet in your [Your Uni ID].twbx Tableau file and name it Holiday Diagnostic Analysis.
a. Drag TRAN_DATE to the Rows shelf.
b. Right-click the YEAR(TRAN_DATE) pill in the Rows shelf and select Month.
c. In the visualisation, select January and select Create Set button (looks like two overlapping circles).
d. Select Create Set. . . and Name it Holiday.
e. Replace the MONTH(TRAN_DATE) pill in the Rows shelf with Holiday and drag % of Returned Sales to the Columns shelf.
f. Adjust the Measure for SUM(% of Returned Sales) to Average.
3. Save your packaged workbook [Your Uni ID].twbx.
Required: State and discuss the question identified. Using the analysis conducted and visualisations generated, discuss your findings.
Part 2 How do the percentages of returned sales for holiday/non-holiday differ for online transactions and across different states?
In Section 2 Part 1, you tested the percentage of returned sales for holiday/non-holiday differences for significance (Excel) and began creating a visualisation (Tableau). In Part 2 of this Section, you will drill down into the data to further diagnose the differences in holiday and non-holiday percentage of returned sales. For this part, you can continue with the same Tableau workbook you used in the previous section.
Tableau | Desktop
From your Tableau file, you will duplicate the Holiday Diagnostic Analysis visualisation to create two additional visualisations, and then combine all three in a dashboard. The two additional visualisations will show a breakdown of holiday/non-holiday returns for online versus in-person transactions and a breakdown of holiday/non-holiday returns across states.
1. Duplicate the Holiday Diagnostic Analysis sheet and rename the duplicate sheet Holiday Diagnostic Analysis - Online.
2. Drag Online-Dummy to the right of the IN/OUT(Holiday) pill in Rows.
3. Duplicate the Holiday Diagnostic Analysis sheet again and rename the duplicate sheet Holiday Diagnostic Analysis - States.
4. Drag State to the right of the IN/OUT(Holiday) pill in Rows.
5. Create a new dashboard and arrange all three holiday diagnostic analysis sheets so that they are easy to read. Label this dashboard as S2 Dashboard.
Required: State and discuss the questions identified. Using the visualisations generated, discuss your findings. You may wish to conduct additional analysis to help answer the questions. If you do, discuss and present any other analysis that you have conducted.
Part 3 What else can you determine about the percentage of returned sales through diagnostic analysis?
Identify another question that you think Dillard’s management, auditors, or financial analysts would want to know about returns that you could answer using diagnostic analytics. Using the data you can access, generate report/s (could include a different hypothesis test) or visualisation/s that will help you find the answer to your questions. Load them into a report or dashboard.
Required: State and provide a discussion of the question that you have identified, describe the analysis conducted to answer the question that you have identified. Using the reports or visualisations generated, discuss the findings to your question.
SECTION 3: PREDICTIVE ANALYTICS
Part 1 By looking at line charts for 2014 and 2015, does the average percentage of sales returned in 2014 seem to be predictive of returns in 2015?
After assessing how different variables impact returns, you have a better idea of how to help Dillard’s prepare for returned sales — both across time and for the holiday season. You can also get an idea of how much a previous year’s percentage of returned sales can help predict the next year’s. To answer this question, you will create a line chart to compare years 2014 and 2015, then run a regression analysis to build a predictive model.
Microsoft | Excel
1. Create a PivotTable and a PivotChart to compare 2014 and 2015 Returns.
a. Open the [Your Uni ID].xlsx Excel workbook.
b. Create a PivotTable from the dataset (To use the Date Table created previously, tick the box “Add this data to the Data Model”).
c. To view a comparison of how the average percentage of sales returned changed each year, you need to view the years as columns and see a row for each month’s average percentage of sales returned for either year. This is easiest using the date parts from the Calendar Table (located in the More fields. . .) section.
d. Drag Month to the Rows (from Date Table > More Fields. . .).
e. Drag Year to Columns (from Date Table > More Fields. . .).
f. Because 2016 does not include the full year, remove 2016 from the Pivot-Table (either by clicking Column Labels or by inserting a Slicer).
g. Drag % of Returned Sales to Values.
h. Change the aggregate for % of Sales Returned from Sum to Average.
• You can adjust the format of these numbers to make them easier to read by changing from decimal to percentage.
i. Insert a PivotChart and change the default to Line, then click OK.
Tableau | Desktop
1. Create a Line Chart to compare 2014 and 2015 Returns.
a. Create a new sheet in your Tableau workbook and name it Line Chart.
b. Columns: TRAN_DATE
c. Rows: % of Returned Sales
d. Expand the YEAR(TRAN_DATE) pill to show Quarters.
e. Right-click QUARTER(TRAN_DATE) and select Month.
f. Drag TRAN_DATE to Color on the Marks card.
g. Because 2016 does not include the full year, right-click the YEAR(TRAN_DATE) pill in the Marks card and select Filter. . ., then uncheck the box next to 2016 and click OK.
Required: Using the analysis conducted and visualisations generated, discuss (1) the objective of the analysis, (2) your findings, and (3) limitations of the analysis conducted.
Part 2 Using regression, can you predict future returns as a percentage of saIes based on historicaI transactions?
Microsoft I ExceI
1. You decided to run a regression to build a model that will help stores predict the percentage of sales that will be returned each month.
a. Create a PivotTable in your [Your Uni lD].xIsx worksheet.
1. Columns: Year (from Date TabIe > More FieIds. . .)
2. Rows: Month then Day of Week (from Date TabIe > More FieIds. . .)
3. Values: % of Returned SaIes(change the aggregate value to Average from Sum)
b. Adjust PivotTable settings:
1. From the Design tab in the ribbon, select Report Layout>Show in TabuIar Form.
2. From the PivotTable Design tab in the ribbon, select Grand TotaIs >Off for Rows and CoIumns.
2. From the Data tab in the ribbon, click Data AnaIysis.
3. Select Regression, click OK, then enter the following:
a. Input Y Range: aII 2015 vaIues (incIuding the IabeI)
b. Input X Range: aII 2014 vaIues (incIuding the IabeI)
c. Place a check mark next to LabeIs.
d. Click OK.
Required: Using the analysis conducted above, discuss (1) the objective of the analysis, (2) your findings, and (3) limitations of the analysis conducted. You may wish to generate additional visualisationsto assist with the analysis. If so, please present the visualisations when discussing your findings.
Part 3 What eIse can you determine about the percentage of returned saIes through predictive anaIysis?
Identify another question that you think Dillard’s management, auditors, or financial analysts would want to know about returns to help them predict future returned sales. Using the data you can access, generate reports (could include a different regression) or visualisations that will help you find the answers to your questions. Load them into a report or dashboard.
Required: State and discuss the question that you have identified. Using the analysis conducted and visualisations generated, discuss (1) the objective of the analysis, (2) your findings, and (3) limitations of the analysis conducted.