Case Study
Assignment 1
You manage Human Relations for your company. One of your sales managers has retired, leaving an opening. You are considering two different employees for the position. Both are highly qualified so you have decided to evaluate their sales performance for the past year.
Use the Assignment 1 Data Set to create and calculate the following in Excel®:
Determine the range of values in which you would expect to find the average weekly sales for the entire sales force in your company 90% of the time. (Hint: You will find I created a video how to do this in Excelhttps://www.youtube.com/watch?v=xJtrjPrZ234
Calculate the impact of increasing the confidence level to 95%?
Calculate the impact of increasing the sample size to 150, assuming the same mean and standard deviation, but allowing the confidence level to remain at 90%?
Based on the calculated confidence interval for weekly sales on the sample of 50 reps at a 90% confidence level:
Calculate both Reps’ average weekly performance and highlight if it is greater than the population mean.
You want to determine whether there is a statistically different average weekly sales between Sales Rep A and Sales Rep B. (Hint: You will find I created a video how to do this in Excel https://www.youtube.com/watch?v=O3Jzmvftbq0
Create Null and Alternative Hypothesis statements that would allow you to determine whether their sales performance is statistically different or not.
Use a significance level of .05 to conduct a t-test of independent samples to compare the average weekly sales of the two candidates.
Calculate the p-value?
Considering that individual you did not promote:
Determine whether this person’s average weekly sales are greater than the average weekly sales for the 50 sales reps whose data you used to develop confidence intervals.
Create Null and Alternative Hypothesis statements that would allow you to determine whether the new Sales Manager’s weekly average sales are greater than the sample of Sales Reps.
Use a significance level of .05 to conduct a z-test of independent samples to compare the average weekly sales of both.
Calculate the p-value?
Assignment 2
You are the manager of a retail store and want to investigate how various metrics can improve the way you manage your business.
Use the Assignment 2 Data Set to create and calculate the following in Microsoft® Excel®
1. Conduct a goodness of fit analysis which assesses orders of a specific item by size and items you received by size.
Conduct a hypothesis test with the objective of determining if there is a difference between what you ordered and what you received at the .05 level of significance.
Identify the null and alternative hypotheses.
HINT for Goodness-of-Fit Test – You want to test if there is a difference between the # Ordered (expected) and the # Received (observed)by a specific size (first table)So you will be using the formula (O-E)^2 / E
This video walks you through how to find the test statistic, p-value and critical value
2.Generate a scatter plot, the correlation coefficient, and the linear equation that evaluates whether a relationship exists between the number of times a customer visited the store in the past 6 months and the total amount of money the customer spent.
Set up a hypothesis test to evaluate the strength of the relationship between the two variables.
Use a level of significance of .05.
Hint:See the regression video below that will find your correlation coefficient and linear equation in part 3.
3. Use the regression line formula to forecast how much a customer might spend on merchandise if that customer visited the store 13 times in a 6-month period.
HINTS for #2 & #3 Regression – There is actually a couple of ways to set find the regression equation as well as the correlation coefficient and I show both ways in this video .Also, you can actually find these values very quickly with Excel’s formula =SLOPE(y data, x data) and =INTERCEPT(y data, x data) where your y data is the $ Purchases and the x data is the # Visits. Remember that you are coming up with the equation y = mx + b where m is the slope and b in the y-intercept. This equation can be written with different variable names but the form is always the same as the y = mx + b because it is simply the equation of a line.
When it tells you to use the regression line, you use what you found in part 2; so in other words you take your equation from part 2 and plug in 13 for x to find y.
4. Consider the average monthly sales of 2014, $1310, as your base to:
Calculate indices for each month for the next two years.
Graph a time series plot.
Create a third column and name it index.For the base value you are using the 1310.So your January index is found by (1375/1310)*100 = rounded to 105 (round all these to the nearest integer); the next for February would be (1319/1310) * 100; so notice you take each value for that month in $ Sales and divide by the base and then multiply by 100.Then the graph is select your Month and $ Sales and Insert / Scatter with straight lines.
5. In the Data Analysis Toolpak, use Excel’s Exponential Smoothing option.
HINT for Exponential Smoothing:See my example video https://www.youtube.com/watch?v=lVChDZADTFQ
Apply a damping factor of .5, to your monthly sales data.
Create a new time series graph that compares the original and the revised monthly sales data.
