CASE Madison's Department 10 Store SKILLS CHECK You should review the following areas: Advanced Filter Average Function Spreadsheet Case Difficulty Rating: SPREADSHEET SKILLS Cell Reference Chart Consolidating Worksheets DAVERAGE Function DMAX Function DMIN Function DSUM Function Excel Table Filter Grouping Worksheets IF Function MAX Function MIN Function Nesting Functions PivotTable Range Name SUM Function VLOOKUP Function CASE BACKGROUND Sylvester Tarkio understands the importance of a department store's sales force, p when it comes to the sales force of a premiere, upscale department store like Mason Customers have long enjoyed the expert knowledge, attention to detail, and service Madison's sales staff provides. Maintaining the quality sales force is a daily job se Tarkio. He carefully evaluates the performance of his sales staff and makes adjustmen when needed. Currently, Mr. Tarkio monitors the performance of his daily and weekly sales productivity reports. The problem is that these res on a word processor Mr. Tarkio has no efficient way of analyzing the data new intern at Madison's, you have impressed Mr. Tarkio with y you to prepare a Productivity workbook for him. You will prepare worksheets and a weekly productivity summary worksheet, analyze h the Pivottatile, Advanced Filter and Filter tools, and generate produ CASE SCENARIO quota metropolitan area malls. The store has specially departments for women, men, children, Aladison's Department Store is a prestigious, upscale department store located in one of the cosmetics, cologne, linen, furniture, and housewares. One of the reasons why Madison's hourly wage, sales representatives are paid a commission on sales above an established has such a fine reputation is because of its highly trained sales staff. Madison's management believes in rewarding its sales staff for its hard work, so in addition to an 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, of 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 members of the sales staff may work a different number of hours on a given day, the sales representatives are paid a commission on all sales exceeding their established quota As 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 Sales Code AM PT1 PT2 ST S2 Figure 1: Hourly Wages and Quotas Hourly Sales Title Wage $23.50 $200.00 $10.50 Assistant Manager Sales Assistant Sales Partner Sales Associate Sales Consultant $11.75 Quota $100.00 $125.00 $13.50 $150.00 $15.00 61 $175.00 Commission Rate 030 010 015 020 025 At the end of each business day. Mr. Tarkio prepares a Daily Productivity Report. As Fig 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 a data may vary) This report specifies the employee's name, rank, assigned department, daly sales, hours, base pay, commission, and gross pay. At the end of each week, Mr Tarko uses the Daily Productivity Reports to prepare a Weekly Productivity Report The uses a word processor to prepare the reports However, he realizes that a spreadshee Weekly Productivity Report summarizes the Daily Productivity Reports. Mr. Tarkib curre application is a much better tool for the summarization and analysis work that he need Tarkio asks you to develop a Productivity workbook for him. Employes Alhaugh, Joshua Brake Damey Bolyard, Pat Rank AM 13 23: 52 CASE 10: Stanton, Catrina ST Figure 2: Daily Productivity Report Madison's Department Store Daily Productivity Report (Current Date) Department Cosmetics Houbowares Women's Hours Worked Sales 8.0 $4,000 00 1456 76 $450.98 $821.36 25 3.0 7.0 Base Pay $168.00 $37 50 $45.00 62 $94 50 Commission $72.00 $0,48 50 $0.00 Gross Play $200.00 $37.00 $45.00 $94.50 Design Specifications Each day, Mr. Tarkio will enter each salesperson's sales and hours into a Daily Productividly worksheet. He then expects the worksheet to determine each salesperson's 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 compart For instance, an assistant manager is paid $23.50 per hour and receives a 3 percent commission In contrast, a sales associate is paid $13.50 per hour and receives a 2 perce 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 salesperson's 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 CASE 10: Madison's Department Store commission formula must determine if a commission to be paid, the applicable mission rate and the portion of sales on which to base the commission. As the VLOOKUP functions is required (At this point, you may wish to review your system's vesion formula involvis several lockups and decisions, you resize that resting the help feature to review theo IF and VLOOKUP functions, as well as how to host As previously mentioned, Mr. Tarkio wants the Daily Productivity Reports summarized into a rose pay commission, and gross pay totals for each salesperson. For instance, the sales Weekly Productivity Report The Weekly Productivity Report provides weekly sales, hours column will reference and sum the individual sales for Sunday through Saturday After showing Mr. Tarkio the workbook prototype, he asks if you can determine the number /ames each salesperson made his quota that week You assure him that the worksheet modified to provide this information. On each worksheet, Mr Tarkio requires grand tals, averages, minimums and maximums for the sales, base pay, commission, and gross pay columns Also on each worksheet Mr. Tarkio wants to see the total quotas met and the can percentage of quotas met Information Specifications Mr. Tarkio wants to use the Productivity workbook to analyze the performance of his sales staff He specifically requests that you show him how to use the Pivot Table to analyze the sales dista, Mr. Tarkio 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, and prepare pie charts for each department, comparing the department's sales representatives sales in addition to the information requirements specified above. Mr. Tarkio wants answers to the blowing questions. Using the PivotTable, Advanced Filter and Filter tools, provide Mr. Tako with answers to these questions 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 sales representatives received commissions above average last week? if sales staff must make their quotas at least 60 percent of the time, which sales staff did not achieve this goal? 5 For each department, which salesperson had the highest sales? On average, how frequently will the sales staff make their quota? What the commissions and total sales for each sales position by department? What are the base pay, commission, and gross pay categorized by sales postion? 63 CASE 10: Madison's Department Store 9 What are the total salaries and hours worked by department? 10 Which department's staff mot their quotas more often than other departments? Lear Implementation Concerns Although you are free to work with the design of your workbook, each worksheet should have a consistent, professional appearance You should use appropriate formatting for the cells and worksheets This case requires you to group worksheets, insert columns into worksheets, consolidate information into a summary worksheet, nest functions, use several functions, reference a lookup table, and use the retrieved value in a formula, work with an Excel table, prepare charts, and use several analytical tools to analyze the sales data In several instances, you can use the Advanced Filter tool to filter data contained in the summary worksheet. To provide Mr. Tarkio with correct answers, you should use the DAVERAGE, DMIN, and DMAX functions in the summary worksheet, as opposed to the AVERAGE MIN, and MAX functions. When the Advanced Filter is used, the DAVERAGE DMIN, and DMAX functions adjust their values based on the filtered data. The AVERAGE MIN, and MAX functions do not. The determination of each salesperson's commission requires nesting the VLOOKUP function within the IF function. The commission formula must look up the hourly sales quota, determine the daily sales quota, take the difference between the daily sales quota and the daily sales, and then multiply the applicable commission rate by the amount of sales above the quota Test Your Design After creating the Productivity workbook described in the case scenario, you should test your design. Perform the following steps. 1. Mr. Tarkio hired four new sales personnel. Enter their data into the worksheets. Leandra Shekel is classified as a PT1 and works in the Men's Clothing Department She worked 4 hours each day, 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 & hour shifts, Tuesday through Saturday. Her sales were $2,500 98, $878.23, $1,503.20 $602.98, and $1,304.17, respectively Paulina Renfro is classified as an S1 and works in the Children's Department. She worked 8-hour 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 works in the Women's Clothing Departmen She worked 8 hours on Monday and her sales were $502.88. She worked 6 hours 64 CASE 10: Madison's Department Store Tuesday, and her sales were $608.12 2 Which salespersons received more than $1,500 in commissions last week? Which salespersons did not receive a commission last week? 3 Based on past performance, what would happen if Mr. Tarkio increased the sales quota for each position by $75 per hour? What would happen if Mr. Tarkin decreased the sales quota for each position by $75 per hour? 4 What would happen if Mr. Tarkio increased each sales position's commission by a hall of a percent? 5 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 employee had the lowest sales for the week? For the Children's Department S1 employees, which employee had the maximum sales for the week? 6 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? For the week, Mr. Tarkio wants to see the total commissions paid by each department, each department's percentage of the total commissions paid for all departments, and the total sales for each department 8. Based on your observations of the sales, sales quotas, and commissions, how might the sales quota system be improved? CASE DELIVERABLES In order to satisfactorily complete this case, you should build the workbook as described in the case scenario and then prepare both written and oral presentations. Unless otherwise specified, submit the following deliverables to your professor 1. A written report discussing any assumptions you have made about the case and the key elements of the case Additionally, what features did you add to make the worksheet(s) more functional? User friendly? (Please note that these assumptions cannot violate any of the requirements specified above and must be approved by your professor) 2 A printout of each worksheet (This includes your charts and Pivot Tables) 3 A printout of each worksheet's formulas 4 An electronic, working copy of your workbook that meets the criteria mentioned in the case scenario and specifications sections 5 Results for each question posed above. (A memo to your instructor discussing these 65 CASE 10: Madison's Department Store results should also be provided.) 6. As mentioned above, you should prepare an oral presentation. (Your instructor will establish the time allocated for your presentation.) You should use a presentation package and discuss the key features of your workbook. Also, discuss how the workbook is beneficial for Mr. Tarkio. What additional information should be included in the workbook to make it more useful?