Business Analytics - Assignment One

Part 1 – Excel Analysis

Question 1

Table 1: Summary Statistics – Gross Profit

Graph 1: Differentiation of Gross Profit between stores

Question 2

Table 2: Gross Profit – Stores Open/ Closed on Sundays

Graph 2: Gross Profit – Stores Open/ Closed on Sunday

Table 3: Descriptive Statistics – Sunday (Open)

Graph 3: Gross Profit – Open Sunday

Table 4: Descriptive Statistics – Sunday (Closed)

Graph 4: Gross Profit – Closed Sunday

Question 3

Table 5: Count of Online Channel Start-up

Graph 5: Online Channel – Store type comparison

Question 4a

Table 6: Wastage levels between all stores (High/ Medium/ Low)

Graph 6: Comparison of total wastage levels between stores

Question 4b

Table 7: Wastage levels between states (only high-level wastage shown)

Graph 7: High wastage between state comparison

Question 5a

Table 7: Advertising expenses compared to percentage of total sales ($m) associated

Table 8: Descriptive Statistics – Advertising expenses

Graph 8: Comparison – advertising expenses & count of total sales (%)

Question 5b

Table 9: Number of staff compared to percentage of total sales ($m)

Graph 9: Comparison – number of staff & Sales ($m)

Question 5b

Table 10: Number of car spaces to percentage of total sales ($m)

Graph 10: Comparison – number of car spaces & percentage of total sales

Question 5d

Table 11: Number of trading hours compared to count of total sales %

Graph 11: Comparison – trading hours & count of total sales (%)

Part 2 – Email

To: Grace Wong

From: Stephen Hennigsson

Subject: Analysis of Store Data

Dear Grace,

I have taken on your challenge of the specific requirements you have asked me to investigate and have found some very interesting results. The information found will not only be able to show you where to focus on improving certain areas, but also look at where we as a company are striving which will allow for goal management and obtainment. As a courtesy, I have provided my answers to your questions underneath each question.

1. Can you provide me with an overall summary of Gross Profit?

• Average across all stores = $933,452.67
• Highest earning store = $2,872,000
• Lowest earning store = $18,000
• Majority of stores earned between $250,000 & $500,000
• Total gross profit amongst all stores = $140,000,000

2. The board wants to revisit the issue of whether it should now be compulsory for our stores to be open every day of the week, including Sunday, to aggressively compete with our major competitors. Can you provide a gross profit comparison for our stores that open on Sunday and those that do not?

• Closed Sunday earned gross profit of $47,304,900
• Open Sunday earned gross profit of $92,713,000
• Average gross for closed on Sunday is $829,911
• Average gross for open on Sunday $996,914
• Difference between average gross is $167,003
• Highest gross profit for open Sunday store is $2,872,000
• Lowest gross profit for open Sunday store is $18,000
• Highest gross profit for close Sunday store is $2,620,000
• Lowest gross profit for close Sunday store is $45,900

3. As you know, our latest initiative has been the creation of our store online channel. I know many of our stores have included the online channel as part of their business operations. I have heard that Mall stores are leading the way when it comes to setting up online stores. Is there any evidence of this?

• Country Stores
  • 18 stores have not set up an online channel
  • 30 stores have set up an online channel
• Mall Stores
  • 19 stores have not set up an online channel
  • 43 stores have set up an online channel
• Strip Stores
  • 8 stores have not set up an online channel
  • 32 stores have set up an online channel

Based on the evidence above, it is obvious that the Mall stalls are leading the way when it comes to setting up online stores.

4. Wastage (unsold stock) is expensive to the organisation, especially if maintained at high levels. 

1. Can you provide me with an overall breakdown of wastage?
   • High à 46 stores
   • Medium à 81 stores
   • Low à 23 Stores
2. I would also be interested in how the States compare when it comes to high levels of wastage in their stores?
   • ACT = 1 store
   • NSW = 8 stores
   • NT = 1 store
   • QLD = 11 stores
   • SA = 11 stores
   • TAS = 3 stores
   • VIC = 9 stores
   • WA = 2 stores
   • Queensland and South Australia both have the highest ‘high’ wastage levels, 23.91%.
   • Northern Territory and ACT have lowest levels of ‘high’ wastage, 2.17%.

5. I would like to see whether factors listed below provide any explanation in the variation of Sales between stores. If so, can you also indicate which factor is the most important?

1. Advertising expenses
1. Average spending on advertising expenses amongst all stores = $147,273
2. The more amount spent on advertising does not necessarily mean that there will be an increase in sales.
3. Top store with $23,000,000 in sales, spent $348,000 on advertising
4. The store which spent $369,000 on advertising only had $19,500,000 in sales
2. Number of staff
1. Stores which had approximately 50-59 employees generated the highest sales, 34% of all sales.
2. Stores with 100-109 & 110-119 employees generated the least amount of sales, 0.67% of total sales each.
3. As the number of employees increases, this does not necessarily mean that the number of sales will increase accordingly. This progression stops around 60-69 employees, as the number of employees increase from this figure, the number of sales decreases.
3. Number of car spaces
1. 20-29 available car spaces showed the highest percentage of sales, 22%
2. 110-119 car spaces showed the lowest percentage of sales, 0.67%
3. As the number of car spaces increases this does not influence the in amount of sales generated. This could be due to Supermart being located within a Mall where there are other stores, meaning there would be a lot of car spaces available. However, for the stores with 20-29 car spaces, this could mean that the car spaces are only for Supermart, meaning whoever parks here would purchase something from the store.
4. Number of trading hours
1. 90-99 opening hours presented highest total sales percentage, 18%
2. 160-169 opening hours presented lowest total sales percentages, 0.67%
3. The correlation between these two could be that if stores had a low number of opening hours, this would mean they would only be open during peak times. However, for the stores that have over 120 trading hours, this could mean that they would be open late at night or 24/7. Overnight, the sales would decrease drastically lowering their total sales percentage.
5. Most important Factor
1. From most important to least important
1. Number of trading hours
2. Advertising expenses
3. Number of staff
4. Number of car spaces

I have attached the requested dashboard to this email, it includes various dimensions which you can explore our Sales and Gross Profit performance. Hopefully you like it and it suits your demands.

Don’t hesitate to ask any questions if you have any.

Kind Regards,

Stephen Hennigsson

Data Analyst