Business Analytics Lab Worksheet 10

CME Group Foundation Business Analytics Lab

About

In this section, we will be using Tableau to learn concepts on data outliers, seasonality effect, and the relationships and impacts. There is no R coding in this lab session.

From our view, we cannot conclude accurately if sales are increasing or decreasing. Nevertheless, there are remarkable inconstancies present, still a drastic drop in sales on 23, 24, 25th week. Moreover, there is a decrease in sales of around $27,000.

Setup

This worksheet will be used to capture your images from Tableau and to share your observations. Example of capturing and including an image is included at the end of this sheet for your reference. You will need to log onto Tableau and Connect/Import the file EuroStore.xls found in the ‘bsad_lab10’ folder.

Remember to always set your working directory to the source file location. Go to ‘Session’, scroll down to ‘Set Working Directory’, and click ‘To Source File Location’. Read carefully the below and follow the instructions to complete the tasks and answer any questions. Submit your work to RPubs as detailed in previous notes.

Note

For your assignment you may be using different data sets than what is included here. Always read carefully the instructions on Sakai. Tasks/questions to be completed/answered are highlighted in larger bolded fonts and numbered according to their particular placement in the task section.

---

Task 1: Data Outliers and Seasonality Effect

First get familiar with the data and what each columns represent. A description of the data is provided in a seperate sheet called ‘Desc’ in the same Excel file. Refer to Lab05 for early exercise using Tableau.

In a new Tableau sheet

1A) Plot Sales (Rows) versus Week (Columns). Include a snapshot here. Analyse the data source and explain in clear words the behavior you observe.

From our view, we cannot conclude accurately if sales are increasing or decreasing. Nevertheless, there are remarkable inconstancies present, still a drastic drop in sales on 23, 24, 25th week. Moreover, there is a decrease in sales of around $27k.

1B) Switch from SUM(Sales) to Average AVG(Sales). Change the Sales scale to be more reflective of the data. Include a snapshot here. Explain the new behavior relative to 1A).

The data is more significant to describe by handling average sales due to the raw data is longer than one year of sales data held. The average sales per week range within about 20K-27K.The drop in sales among Week 22-25 is no longer meaningful. Nevertheless, there is a downward trend in Week 30-36. ##### 1C) Add Temp to the Color scale found in Marks. Change SUM(Temp) to AVG(Temp). Edit the color legend to be more reflective of hot and cold temperatures. Include a snapshot here. Explain the combined behavior of sales and temperature.

Weekly sales go up while temperature increases. Nevertheless, Sales goes down meanwhile it gets colder outside.

Task 2: Relationships and Impacts

In a seperate Tableau sheet

2A) Plot Sales (Rows) versus TV (Columns). Switch both measures from SUM() to Dimension. The plot should look more like a scatter plot. Include a snapshot here. Explain the behavior of Sales versus TV. How much you think is the upper limit amount that should be invested in TV ads?

The scatter plot correlates the sales and the TV advertising spending. The graph reveals that if there is no spending on TV advertising, there will nevertheless end in sales. Based at the scatter plot, there is quite scarce, or no correlation among sales and TV advertising. The upper limit should be around 90K.

2B) Overlay Radio to the previous plot using the Size. scale found in Marks. Include a snapshot here. Explain how the additional Radio ads to Tv ads is impacting Sales.

In a separate Tableau sheet

Supplementing the extra radio ads to TV ads in the scatter plot displays that there is a related relationship among sales vs. TV ads and sales vs. radio ads. Not TV ads or radio ads influence sales or has a precise correlation connection. Nevertheless, by relating, it appears that radio ads hold a considerable influence in sales then the TV ads. It implies more radio ads, more potential sales. ##### 2C) Plot Sales versus Fuel Volume. Explain behavior.

The sales raise as the fuel volume raise. Sales and fuel volume hold a positive relationship. ##### 2D) Overlay Temperature using the Color scale. Follow 1C) for temperature settings. Explain the new combined behavior and the impact of temperature.

With higher temperature, there is tendency for an increment on fuel volume and sales. In high temperature, people drive longer and demand more gas so buy extra at the market, which raises sales. ##### 2E) Overlay Holiday using the Label scale. Include a snapshot here. Explain the new combined behavior and the impact of Holiday.

Holidays concentrate in the summer season. Throughout the holiday season , the fuel volume raises and sales raise too.

In a separate sheet

2F) Use a Tree Map to best show the combined effect of Sales, Fuel Volume, Temp, and Holiday. A sample view is shown below. Consider using the Quick Filter on Holiday and Temp to isolate and better view the impact of each. You can have more than one filter at a time. Include a snapshot here.

2G) Write a small paragraph summarizing your final conclusions on what you think most affect Sales and under what conditions.

In my view, sales are mostly determined by temperature, holiday, and fuel volume. Nevertheless, it also changed by radio ads and TV ads spending lightly. Sales raise throughout the summer season, which longer holidays, and there is a high positive correlation among sales and fuel volume.