Tutorial 02_1_2018_solutions.pdf

# There are 15 salespeople each with their own area

• Homework Help
• 10

This preview shows pages 8–10. Sign up to view the full content.

There are 15 salespeople, each with their own area. Based on the size and population of the area, each salesperson has a sales target for the year. The measure of performance used for awarding bonuses is based on the percentage achieved above the sales target. Based on this measure, a salesperson is placed into one of five bonus bands and awarded bonus points as shown below in Table 1. Each staff is then awarded a percentage of the bonus pool, based on the percentage of the total points awarded. As shown in cell E11 of the spreadsheet, the bonus pool is \$250,000 for this year. The bonus bands shown in the instructions above are in cells A7:C11 . In this table, column A gives the lower limit of the bonus band, column B the upper limit, and column C the bonus points awarded to anyone in that bonus band. For example, salespeople who achieve sales 56 percent above their sales target would be awarded 15 bonus points. The VLOOKUP function in this case allows us to extract a subset of data from a larger table of data based on some criterion. The general form of the VLOOKUP function is: =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) where

This preview has intentionally blurred sections. Sign up to view the full version.

ETF1100 Business Statistics SOLUTIONS Tutorial 2 9 lookup_value = the value to search for in the first column of the table table_array = the cell range containing the table col_index_num = the column in the table containing the value to be returned [range_lookup] = TRUE if looking for the first approximate match of value and FALSE if looking for an exact match of value. Note: VLOOKUP assumes that the first column of the table is sorted in ascending order. There are two tables provided: Table 1 is the bonus band. Table 2 refers to the information regarding the name of sales persons and percentage above their target sales. YOUR TASK: Use the VLOOKUP function to assign the number of bonus points to each salesperson. The VLOOKUP function is used in column C to look in the bonus band table and automatically assign bonus points. Once you have completed this task, move to column D and convert your answers in C to a percentage of the total bonus points. Now in column E, calculate the bonus amount. Solutions: refer to Bonus Awards Solutions.xls in the Tutorial Material folder under the Week 2 section on Moodle. Task 4: Drawing a histogram Refer to Question 2.4. The data set Utility.xlsx is given in the After Interactive Lectures: Tutorial Materia l section, Week 02 Tutorial Material folder on Moodle. See if you can produce a histogram using the Pivot Chart option. Remember to edit the chart to optimise presentation.
ETF1100 Business Statistics SOLUTIONS Tutorial 2 10 0 2 4 6 8 10 12 14 82-101 102-121 122-141 142-161 162-181 182-201 202-221 Frequency Utility charges (\$) Histogram of Utility Charges Total
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern