Question

How to solve this question on Microsoft Excel

The annual commissions earned by sales representatives of Machine Products Inc., a manufacturer of light machinery, follow the normal distribution. The mean yearly amount earned is $40,000 and the standard deviation is $5000.

a. What percentage of sales representatives earn between $32,000 and $42,000 per year?

b. What percentage of sales representatives earn more than $42,000 per year?

c. The sales manager wants to award the sales representatives who earn the largest commissions a bonus of $1000. He can award a bonus to 20% of the representatives. What is the cutoff point between those who earn a bonus and those who do not?

Answer 1

Mean = 40000, sd = 5000

a) P(32000 < x < 42000) = P(x < 42000) - P(x < 32000)

To do this we need to use formula: 'norm.dist(x, mean, standard deviation, true)' formula

Excel work:

Hence, 60.06% of sales person earn between $32000, and $42000.

b) P(x > 42000)

Norm.dist provides area of the left of a particular value, here we need area of right so we will use 1-norm.dist.

Excel work:

Hence, 34.46% of sales person earn more than $42000 per year.

c) For top 20% we need to find z-score by using normsinv function.

0.8 because 80% of values below of this.

Hence, cutoff for bonus is $44208.

I hope these will help. thank you.