*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.

- 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.
- 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.
- 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.
- Find the average sales per employee and cost of sales per employee.
- Find the total sales for SmartHome Co. and total cost of sales. Calculate the gross margin.
- Find the average and total sales for the Japanese branch (JA).
- Find the variance and the standard deviation of the Employee Satisfaction Score.
- 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.
- Plot a scatterplot between employee satisfaction score and sales. Use Employee Satisfaction Score as the Y-axis and sales as the X-axis.
- 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**

## 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