Reference no: EM133176988

Mehdi's Department Store 

Case background 

Hassan Moe, the Manager at Mehdi's, understands the importance of a department store's sales force, especially when it comes to the sales force of a premiere, upscale department store like Mehdi's. Customers have long enjoyed the expert knowledge, attention to detail, and service that Mehdi's sale staff provides. Maintaining the quality sales force is a daily job for Mr. Moe. He carefully evaluates the performance of his sales staff and makes adjustments when needed. Currently, Mr. Moe monitors the performance of his sales staff by reading daily and weekly sales productivity reports. The problem is that these reports are prepared on a word processor. Mr. Moe has no efficient way of analyzing the data in detail. As a new Management Trainee at Mehdi, you have impressed Mr. Moe with your work. Mr. Moe asks you to prepare a Productivity Workbook for him. You will prepare seven daily productivity worksheets and a weekly productivity summary worksheet, analyze the sales data by using the PivotTable, Advanced Filter and Filter tools, and generate several charts. 

Scenario 

Mehdi's Department Store is a prestigious, upscale department store located in one of the metropolitan area malls. The store has specifically departments for women, men, children, cosmetics, cologne, linen, furniture, and houseware. One of the reasons why Mehdi's has such a fine reputation is because of its highly trained sales staff. Mehdi's management believes in rewarding its sale 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 subcategorized as sales assistants or sales partners. Full-time representatives are subcategorized as assistant managers, sales consultants, or sales associates. Part-time representatives work 20 hours a week, while full-time representatives work 40 hours per 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 summarizes the hourly wages and established quotas. Figure 1: Hourly wages and quotas 

Sales code	Sales title	Hourly wage ($)	Quota ($)	Commission rate
AM	Assistant Manager	23.50	200.00	0.030
PT1	Sales Assistant	10.50	100.00	0.010
PT2	Sales Partner	11.75	125.00	0.015
S1	Sales Associate	13.50	150.00	0.020
S2	Sales Consultant	15.00	175.00	0.025

At the end of each business day, Mr. Moe prepares a Daily Productivity Report. As Figure 2 shows, the Daily Productivity Report summarizes 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 employees' name, rank assigned department, daily sales, hours, base pay, commission, and gross pay. At the end of each week, Mr. Moe uses the Daily Productivity Reports to prepare a Weekly Productivity Report. The Weekly Productivity Report summarizes the Daily Productivity Reports. Mr. Moe currently uses a word processor to prepare the reports. However, he realizes that a spreadsheet application is a much better tool for the summarization and analysis work that he needs. Mr. Moe asks you to develop a Productivity workbook for him. 

Figure 2: Daily Productivity Report 

			Mehdi's Department Store  Daily Productivity Report (Current Date)
Employee	Rank	Department	Sales ($)	Hours Worked Base Pay ($) Commission ($)            Gross Pay
Allbaugh, Joshua	AM	Men's	4,000.00	8.0  -  -  -	188.00	72.00	260.00
Blake, Barney	S2	Cosmetics	456.76	2.5	37.50	0.48	37.98
Bolyard, Pat	S2	Houseware	450.98	3.0  -  -  -	45.00	0	45.00
Stanton, Catrina	S1	Women's	821.36	7.0  -  -  -  -  -	94.50	0	94.50

Design Specifications 

Each day Mr. Moe will enter each salesperson's sales and hours into a Daily Productivity worksheet. He then expects the worksheet to determine each salespersons' base pay, commission, and gross pay. While the gross pay involves adding the base pay to the commission, calculating the base pay and commission requires referencing values in a lookup table. As you want the lookup table to be easily accessible and updateable, you place the lookup table in its own worksheet. 

The base pay and commission are dependent upon the salesperson's rank in the company. For instance, an assistant manager is paid $23.50 per hour and receives a 3% commission. In contrast, a sales associate is paid $13.50 per hour and receives a 2% commission. You use the VLOOKUP function to build this logic into the base pay, and you use the IF and VLOOKUP functions to build the logic into the commission cells. The base pay formula uses the salespersons' rank to retrieve the correct hourly wage and then multiplies the hourly wage by the number of hours. The determination of the appropriate commission requires more complicated logic than the base pay. For instance, the commission formula must determine if a commission is to be paid, the applicable commission rate, and the portion of sales on which to base the commission. As the commission formula involves several lookups and decisions, you realize that nesting the IF and VLOOKUP functions is required. (At this point, you may wish to review your system's online help feature to review the IF and VLOOKUP functions, as well as how to nest functions.) 

As previously mentioned, Mr. Moe wants the Daily Productivity Reports summarized into a Weekly Productivity Report. The Weekly Productivity Report provides weekly sales, hours, base pay, commission, and gross pay totals for each salesperson. For instance, the sales column will reference and sum the individual sales for Sunday through Saturday. 

After showing Mr. Moe the workbook prototype, he asks if you can determine the number of times each salesperson made his quota that week. You assure him that the worksheet can be modified to provide this information. On each worksheet, Mr. Moe requires grand totals, averages, minimums, and maximums for the sales, base pay, commission, and gross pay columns. Also on each worksheet Mr. Moe wants to see the total quotas met and the percentages of quotas met. 

Information Specifications 

Mr. Moe wants to use Productivity workbook to analyze the performance of his sales staff. He specifically requests that you show him how to use the PivotTables to analyze the sales data. Mr. Moe wants to compare the commissions and sales by department, view the commissions by individuals within each department, prepare a pie chart showing the commissions by department, comparing the department's sales representatives' sales. 

In addition to the information requirements specified above, Mr. Moe wants answers to the following questions. Using the PivotTable, Advanced Filter and Filter tools, provide Mr. Moe with answers to the following questions; 

1. Which five sales representatives received the highest commissions last week?
2. Which sales representatives received commissions above average last week?
3. Excluding the Furniture Department and its employees, which sale representative received commissions above average last week?
4. If sales staff must make their quotas at least 60% of the time, which sales staff did not achieve this target?
5. For each department, which salesperson had the highest sales?
6. On average, how frequently will the sales staff make their quota?
7. What are the commissions and total sales for each sales position by department?
8. What are the base pay, commission, and gross pay categorized by sales position?
9. What are the total salaries and hours worked by department?
10. Which department's staff met their quotas more than other departments? Least?

Test Your Design 

After creating the Productivity workbook described in the case scenario, you should test design. Perform the following steps. 

1. Mr. Moe hired four new sales personnel. Enter their data into the worksheets. 

Leandra Shakeel is classified as a PT1 and works in the Men's Clothing Department. She worked 4 hours each, Sunday through Thursday. Her sales Sunday through Thursday were $1,400.98, $1,500.42, $750.32, $550.08, and $900.78, respectively. 

Darise Ferrer was hired as an S1 and works in the Linen Department. She worked 8-hours shifts, Tuesday through Saturday. Her sales were $2,500.98, $878.23, $1,503.28, $602.98, and $1,304.17, respectively. 

Paulina Renfro is classified as an S1 and works in the Children's Department. She worked 8hours shifts, Tuesday through Saturday. Her sales were $1,500.56, $402.22, $1,090.80, $908.43, and $350.98. 

Sethana Aynes is classified as a PT1 and worked in the Women's Clothing Department. She worked 8 hours on Monday and her sales were $502.88. She worked 6 hours on Tuesday, and her sales were $608.12. 

1. Which salespersons received more than $1,500 in commissions last week? Which salespersons did not receive a commission last week?
2. Based on past performance, what would happen if Mr. Moe increased the sales quota for each position by $75 per hour? What would happen if Mr. Moe decreased the sales quota for each position by $75 per hour?
3. What would happen if Mr. Moe increased each sales positions' commission by a half of a percent?
4. For the week, what are the average sales for the S1 employees working in the Children's Department? For the Children's Department S1 employees, which employees had the lowest sales for the week? For the Children's Department S1 employees, which employees had the maximum sales for the week?
5. For the week, what are the average sales for the PT1 employees working in the Women's

Department? For the Women's Department PT1 employees, which employee had the lowest sales for the week? For the Women's Department PT1 employees, which employee had the most sales for the week? 

1. For the week, Mr. Moe wants to see the total commission paid by each department, each department's percentage of the total commission paid for all departments, and the total sales for each department.
2. Based on your observations of the sales, sales quotas, and commission, how might the sales quota system by improved?

Checklist for case analysis report 

1. Executive summary
2. Statement of the problem
3. Causes of the problem
4. Decision criteria and alternative solutions
5. Recommended solution, implementation and justification
6. External sourcing, if any
7. Spelling and grammar and presentation of report and slides