Robert’s Department Store is a prestigious, upscale department store located in Chadstone Shopping Centre. The store has specialty departments for women, men, children, cosmetics, cologne, linen, furniture, and housewares. One of the reasons why Robert’s has such a fine reputation is because of its highly trained sales staff. Robert’s management believes in rewarding its sales staff for its hard work, so in addition to an hourly wage, sales representatives are paid a commission on sales above an established quota. Sales representatives are classified as either part-time or full-time. Part-time representatives are then subcategorised as sales assistants or sales partners.
Full-time representatives are subcategorised as assistant managers, sales consultants, or sales associates. Part-time representatives work 20 hours a week, while full-time representatives work 40 hours a week. Sales representatives have input into how many hours a day they work; however, they cannot work overtime. Within the company, sales representatives are ranked and paid according to their experience and tenure with the company. Sales representatives are paid a commission on all sales exceeding their established quota. As members of the sales staff may work a different number of hours on a given day, the sales quota is based on the hours worked. For instance, if a sales representative has an hourly quota of $100 in sales and he works 5 hours, then his daily sales quota is $500. For any sales above the $500 quota, the sales representative receives a commission. Figure 1 below summarises the hourly wages and established quotas.
At the end of each business day, Vasanthi prepares a Daily Productivity Report. As Figure 2 shows, the Daily Productivity Report summarises each employee’s sales activity for the day. (The data shown in Figure 2 are for illustrative purposes only. Your report format and data may vary.) This report specifies the employee’s name, rank, assigned department, daily sales, hours, base pay, commission, and gross pay. At the end of each week, Vasanthi uses the Daily Productivity Reports to prepare a Weekly Productivity Report. The Weekly Productivity Report summarises the Daily Productivity Reports. Vasanthi currently uses a word processor to prepare the reports. However, she realises that a spreadsheet application is a much better tool for the summarisation and analysis work that she needs, so she asks you to develop a Productivity workbook for her.
Vasanthi wants to use the Productivity workbook to analyse the performance of her sales staff. She specifically requests that you show her how to use the PivotTable to analyse the sales data. She wants to:
- compare the commissions and sales by department, and
- view the commissions by individuals within each department.
In addition to the information requirements specified above, Vasanthi wants answers to the following questions. Using the PivotTable, Advanced Filter and Filter tools, provide Vasanthi with answers to these questions.
- Which six sales representatives received the highest commissions last week
- Which sales representatives received commissions above average last week
- Excluding the Furniture Department and its employees, which sales representatives received commissions above average last week
- If sales staff must make their quotas at least 70 percent of the time, which sales staff did not achieve this goal
- For each department, which salesperson had the highest sales
- On average, how frequently will the sales staff make their quota
- What are the commissions and total sales for each sales position by department
- What are the base pay, commission, and gross pay categorised by sales position
- What are the total salaries and hours worked by department
- Which department’s staff met their quotas more often than other departments, and which department’s staff met their quotas least often than other departments.