You have been hired to evaluate the sales people in MIS INC. Above is the sales

Question

You have been hired to evaluate the sales people in MIS INC.

Above is the sales data for the previous month. The are 4 sales

people who can sell their products over 3 sales regions. The

company advertises the sales price of a widget at $80, but your

salespeople have the option to sell

their widgets at whatever price they feel comfortable with. You

should prepare spreedsheets to answer the following questions?

1. What is the average sales price for a widget?

2. Which salesperson has created the most profit for MIS

INC?

3. If a salesperson earns 33% commision on the profit from

each

sale, If so how much commission did each salesperson make?

4. Does the answer in 3 differ if the sales are totaled and then

the commission calculated instead of a commission earned on each

sale? If so, how much for each salesperson?

5. Construct a pivot table to show how many total units were

sold in the three districts by each of the salespeople.

6. Management is considering offering a bonus for sales made

into Sales Disctrict 3. How much would it cost the company if they

offered an additional 17% commission on the sales made in District

3?

7. Create a chart to show management what percentage of the

Gross Sales was Expenses, what part Commission and

what part profit for MIS INC?

Explanation / Answer

1 Average sales price 736 60850 82.67663 2 More profit Sales person Sum of Profit Albert 500 jones 500 Peterson 3500 Smith 250 Grand Total 4750 Peterson has made higher profit 3 Sales person Sum of Commision Albert 5181 jones 3333 Peterson 7689 Smith 3877.5 Grand Total 20080.5 Again Peterson had made higher commission 4 If commission is total sales then Commsion = 20080.5 Per head 20080.5/4 5020.125 Sales person Sum of Commision On total basis Difference Albert 5181 5020.125 160.875 jones 3333 5020.125 -1687.13 Peterson 7689 5020.125 2668.875 Smith 3877.5 5020.125 -1142.63 Grand Total 20080.5 20080.5 0 5 District wise sales is shown below Row Labels 1 2 3 Grand Total Albert 5300 3800 6600 15700 jones 6900 3200 10100 Peterson 11000 2500 9800 23300 Smith 5000 4000 2750 11750 Grand Total 21300 17200 22350 60850 6 Additional commison for district 3 17 % of 22350 3799.5 7 % of gross sales Sum of Gross 60850 100 Sum of Expenses 56100 92.19391947 Sum of Commision 20080.5 33 Sum of Profit 4750 7.806080526

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.