View the step-by-step solution to:

MIS INC - Sales Data for Widgets Salesperson District Units Smith 1 Jones 2 Peterson 3 Peterson 2 Smith 1 Smith 3 Jones 2 Peterson 1 Albert 1 Albert...

Would like some help solving the questions in the raw data worksheet

MIS INC - Sales Data for Widgets Salesperson District Units Gross Sales Expenses Smith 1 50 4000 4200 Jones 2 34 2400 2000 Peterson 3 55 5000 4000 Peterson 2 32 2500 2300 Smith 1 12 1000 1000 Smith 3 34 2750 2500 Jones 2 56 4500 4600 Peterson 1 78 6000 4000 Albert 1 65 5300 5200 Albert 2 45 3800 4000 Smith 2 45 4000 3800 Peterson 3 54 4800 4600 Albert 3 83 6600 6000 Jones 3 38 3200 3000 Peterson 1 55 5000 4900 You have been hired to evaluate the sales people in MIS INC. Above is the sales data for the previous month. There are 4 sales people who can sell their products over 3 sales regions. The company adver±ses the sales price of a widget at $80, but your salespeople have the op±on to sell their widgets at whatever price they feel comfortable with. You should prepare spreedsheets to answer the following ques±ons: 1. What is the average sales price for a widget? 2. Which salesperson has created the most proFt for MIS INC? Use one of the Excel lookup func±ons to Fnd this answer. 3. If a salesperson earns 33% commision on the proFt from each sale, how much commission did each salesperson make? If proFt is nega±ve make Commission zero. Sort the list on salesperson's last name. 4. Does the answer in 3 di²er if the sales for a salesman 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 each of the three districts by each of the salespeople. 6. Management is considering o²ering a bonus for sales made into Sales District 3. How much would it cost the company if they o²ered an addi±onal 17% commission on the sales proFt made in District 3? 7. Create a chart to show management what percentage of the total Gross Sales was Expenses, what part Commission and what part proFt for MIS INC. 8. Use Condi±onal ³orma´ng to highlight the top 5 Gross Sales in Green 9. Use the appropriate Excel func±onality to allow ONLY the names of the four sales people to be used in Cells A3-A17 10. In your opinion, which of the four salepeople has done the best job? Why? Please use the sheets provided to create you answers as needed. General Guidelines: Please use the 10 sheets provided to create your answers as needed, these are your workseets to derive your answers. I will review your formulas on these sheets if I have a ques±on about your answers. You may add columns and data as necessary. Please list any assump±ons you make in answering the ques±ons. It will help with par±al credit. When you have completed all 10 ques±ons copy and paste your answers from the 10 sheets to the sheet marked All Answers using the space provided in each box.
Background image of page 01
Q1 Ans Q5 Ans List Pivot Ta Q3 Ans Salesperson Commission Q2 Ans Q6 Ans Q4 Ans Salesperson Total Profit Commission Difference Q8 Ans
Background image of page 02
Show entire document
Sign up to view the entire interaction

Top Answer

Please find enclosed... View the full answer

9310422.xls

MIS INC - Sales Data for Widgets
Salesperson
District Units
Smith
1
Jones
2
Peterson
3
Peterson
2
Smith
1
Smith
3
Jones
2
Peterson
1
Albert
1
Albert
2
Smith
2
Peterson
3
Albert
3
Jones
3
Peterson
1...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online