148|EXCEL – Statistics

[Benjamin_Lau]

BYU Student Author: @Benjamin_Lau
Reviewers: @Carter_Lee, @Dallin_Gardner
Estimated Time to Solve: 20 Minutes

This is an intro challenge that is part of the Excel Learning Path.

We provide the solution to this challenge using:

• Excel

Need a program? Click here.

Overview
SmartHome Co. sells smart home systems of three levels (Base, Plus, and Full). SmartHome has an international salesforce and generates millions of sales each year. You are given the dataset of SmartHome employees including their information, performance, and satisfaction scores. In this challenge, you will use the statistics functions in Excel to uncover interesting facts about SmartHome and its employees.

Instructions
Go to sheet “Employee Data” to start the analysis.

1. Create a new column to check if the employee generated more than or equal to $1,000,000 of sales or not. Highlight the ones that generated more than or equal to $1M of sales using Conditional Formatting.
2. Create a new column to check if any of the JAPANESE (JA) employee generated more than or equal to $1,000,000 in sales or not. Highlight the ones that generated more than or equal to $1M of sales and from Japan.
3. Create a new column to check if any of the JAPANESE (JA) or AUSTRALIAN (AUS) employees generated more than or equal to $1,000,000 in sales or not. Highlight the ones from Japan or Australia that generated more than or equal to $1M of sales.
4. Find the average sales per employee and cost of sales per employee.
5. Find the total sales for SmartHome Co. and total cost of sales. Calculate the gross margin.
6. Find the average and total sales for the Japanese branch (JA).
7. Find the variance and the standard deviation of the Employee Satisfaction Score.
8. Excel supports the regression model. It helps you quantify the relationship between two variables. The SLOPE function finds the slope of the regression line and the INTERCEPT function finds the intercept of the regression line. RSQ function finds the R-Square which is important for the explanatory power of the regression model. Here, we examine the relationship between employee satisfaction score (Y-axis) and sales generated (X-axis). Find the slope, intercept, and R-square of the regression line between these two variables.
9. Plot a scatterplot between employee satisfaction score and sales. Use Employee Satisfaction Score as the Y-axis and sales as the X-axis.
10. Import the Analysis Toolpak. Go to File > Options > Add-ins > Go. Check the box for Analysis Toolpak. Press OK. Now go to Data > Data Analysis. Find Regression and click on it. Put the Y range as the employee satisfaction score and X range as the sales. Now selected your output range on the sheet. Click OK when finish the setup.

Data Files

• Challenge148_Data.xlsx
• Challenge148_Data_Dictionary

Suggestions and Hints

• Use IF function for Question 1 and check row 2 to see if the conditional formatting works.
• Use IF + AND functions for Question 2 and check row 100 to see if the conditional formatting works.
• Use IF + AND + OR functions for Question 3.

Solution

Challenge148_Solution.xlsx
Solution Video: Challenge 148|EXCEL – Statistics

[Carter_Lee]

Really good intro challenge for people trying to learn or refresh. It is the perfect amount for beginners like me. Here is what I got:

[Bryce_Lawrence]

Challenge148_Data.xlsx (107.4 KB)

[Abby_Bird]

Challenge148_Data.xlsx (115.1 KB)

[Noah_Cook]

Excel Challenge Noah Cook.xlsx (134.6 KB)

[Rebecca_Bourne]

I enjoyed this challenge! If you need help, here’s a quick walkthrough for one of the more complex problems For problem #3, (1) Use a formula to check if sales are greater than or equal to $1M and the nationality is Japanese or Australian. For example, =IF(AND(OR(D6=“JA”,D6=“AUS”),I6>=1000000),“Yes”,“No”). =IF(): This function allows you to specify a condition and return one value if the condition is true and another value if the condition is false. AND(): This function evaluates multiple conditions and returns TRUE only if all conditions are true; otherwise, it returns FALSE. OR(): This function evaluates multiple conditions and returns TRUE if at least one of the conditions is true; otherwise, it returns FALSE. (2) Apply conditional formatting to highlight cells where “Million Dollar Sales (JP or AU)” column is equal to “Yes”. First, select the range of cells where you want to apply this conditional formatting. Go to the “Home” tab on the Excel ribbon. Click on “Conditional Formatting” in the “Styles” group. Choose “New Rule” from the dropdown menu. Set the rule as Cell Value Contains “Yes”, and choose a highlight color. After setting up the formatting, click "OK”. I hope that helped!

[Connor_LoCascio]

Time to complete 30 min
Difficulty: Beginner
Feedback: This is a good exercise for those wanting to learn or refresh the data analysis tool kit.
LoCascio_Connor_Challenge148.xlsx (134.2 KB)

[Ellie_Black]

42 minutes
Find the average sales per employee and cost of sales per employee: $782.060, $241,330
Find the total sales for SmartHome Co. and total cost of sales. Calculate the gross margin: $466,890, $144,075, 70%
Find the average and total sales for the Japanese branch (JA): $780,000, $64000000
Find the variance and the standard deviation of the Employee Satisfaction Score: 26.44,5.14

[Riley_Dougherty]

Challenge148_Data_R1.xlsx (151.9 KB)
Fun challenge, time to complete 25 mins.

[Kade_Halupnick]

Time to complete was around 30 minutes. The training was very useful and a nice refresher on several key Excel functions.
Challenge148_Data - Completed.xlsx (134.2 KB)

[John_Gonnella]

Time: 35-40
Rating: Good refresh of excel functions

Challenge148_Data.xlsx (150.8 KB)

[Luke_Bandel]

44 Minutes, Some struggles but overall got there in the end.
Challenge148_Data.xlsx (107.4 KB)

[Michael_Femia]

I have attached my work below!
Michael_Femia_Challenge148_Data.xlsx (134.4 KB)

[Jack_Bjorklund]

Attached my work!
Challenge148_Data.xlsx (135.1 KB)

[Destiny_Waldron]

This challenge was a good refresher for different excel formulas.
Challenge148_Data.xlsx (115.5 KB)

[Evan_Arpin]

I enjoyed the challenge, took around 25 minutes
Challenge148_Data Evan Arpin.xlsx (133.2 KB)

[Paul_Angelastro]

Work is attached below:
Challenge148_Data.xlsx (133.3 KB)

[Michael_Piotrowski]

Time to complete: 30-40 minutes
Rating: Not too bad but a good refresher
TechHub 148EXCELStatistics.xlsx (134.1 KB)

[Malik_Alwaail]

Here is my excel work! Thank you so much
Challenge148, malik Alwaail_Data.xlsx (149.8 KB)

[Sona_Maharaj]

Time: 30 minutes
Rating: Moderate
Comments: I really liked the practice with the if statements!
SMChallenge148_Data.xlsx (140.9 KB)