Appendix D Student handout: Spreadsheet assignment 2 The second spreadsheet adds some complexity in that it includes data as well as variables. This spreadsheet requires you to create a workbook that will calculate total compensation for individual salespersons. The compensation computation includes: salary, commission on low-margin items, commission on high-margin items, and commission for new customers. Remember to start with the output (Compensation Report) when designing. In addition, you will need an extra sheet for the sales data, so this Excel workbook, when complete, will consist of five or six separate worksheets (1) A identification/description sheet (there could be separate identification and description sheets): (2) a sales data sheet:(3) a sales commissions sheet: (4) a calculation sheet; and (5) the report on the total amount owed in compensation to each salesperson in final printable format. Swifty's T-Shirts Salesperson Compensation Report Swifty's T-Shirts supplies personalized shirts to businesses. The sales staff are paid via salary and commission. The com- mission is based on three rates: a percentage commission on lower margin items (known as "standard commission"), a per- centage commission on higher margin items (known as "priority commission"), and a bonus for each new customer. The commission rates are set by the Vice President of Marketing, Mr. Sam Sneed. Mr. Sneed likes to adjust the commission rates to provide appropriate incentives for sales. When there are many higher margin items that are overstocked, Sam increases the "high-margin commission temporarily to try to reduce the excess inventory. Similarly, if he feels that the sales staff has not found enough new customers, he may raise the bonus for new customers. When adjustments to the commission structure are made, they are effective at the beginning of the next calendar month. Swifty's T-Shirts uses an accounting software package to handle its order processing and billing. That software can be used to produce a monthly summary of sales of low-margin items and high-margin items for each salesperson. Each sales- person submits a list of new customers they have attracted that month, which is verified from the accounting data to deter- mine the count of new customers. nanarinn med in each Mr. Sneed has requested that you create a workbook that will calculate the total amount of compensation owed to each salesperson for the month. He asks that the spreadsheet be designed to allow for adjustments to the commission rates. He would like to be able to allow his secretary to do the actual data entry each month. The secretary is a novice spreadsheet user. The monthly data has been downloaded into the table below from the accounting system. The data will be delivered in this format every month. The workbook should be designed so that the new data can update the old data and calculate com- missions for the month based on the new data and any changes in the commission rates. Current values for the commission rates and bonus rates are also shown. Your spreadsheet should treat the commission and bonus rate information as param- eters (assumptions/variables). That is, all uses of a given rate should reference a single cell that can be easily identified and modified by the user. Your calculations should display detailed information about commissions earned by component and by salesperson, as well as summary information for each salesperson and the total compensation to be paid. Commission Rates: High-Margin Items Commission 4% Low-Margin Items Commission 3% New Customer Commission $150/customer New customers 10 2 9 5 Salesperson Zicarelli, Joan Harrington, Diane Lockamy, Bill Belski, Archie Lohrke, Robin Frownfelter, Sam Long. Sebastian Affenbach, Yolonda Scalise, Matise Rahn, Stella York, Robin Garrett, Susan Powell, Bo Rogers, Fred McMullen, Harry Marshall, Marshall Hendon, Sarai Harlow, Gene Salary 475 320 400 345 455 490 420 540 520 345 585 420 330 455 255 415 555 255 Sales of high-margin items 48,701 41,951 30.748 57,465 42,629 40,728 35,410 44,839 47,661 38,129 55,466 41,948 58,603 58,021 30,497 57,338 45,917 56,603 Sales of low-margin items 29,575 31.942 33,152 32,701 30,135 36,996 30,960 33,654 21,214 21.252 32,526 26.842 31.778 26,908 23,396 33,421 28,587 2471 * Appendix D Student handout: Spreadsheet assignment 2 The second spreadsheet adds some complexity in that it includes data as well as variables. This spreadsheet requires you to create a workbook that will calculate total compensation for individual salespersons. The compensation computation includes: salary, commission on low-margin items, commission on high-margin items, and commission for new customers. Remember to start with the output (Compensation Report) when designing. In addition, you will need an extra sheet for the sales data, so this Excel workbook, when complete, will consist of five or six separate worksheets (1) A identification/description sheet (there could be separate identification and description sheets): (2) a sales data sheet:(3) a sales commissions sheet: (4) a calculation sheet; and (5) the report on the total amount owed in compensation to each salesperson in final printable format. Swifty's T-Shirts Salesperson Compensation Report Swifty's T-Shirts supplies personalized shirts to businesses. The sales staff are paid via salary and commission. The com- mission is based on three rates: a percentage commission on lower margin items (known as "standard commission"), a per- centage commission on higher margin items (known as "priority commission"), and a bonus for each new customer. The commission rates are set by the Vice President of Marketing, Mr. Sam Sneed. Mr. Sneed likes to adjust the commission rates to provide appropriate incentives for sales. When there are many higher margin items that are overstocked, Sam increases the "high-margin commission temporarily to try to reduce the excess inventory. Similarly, if he feels that the sales staff has not found enough new customers, he may raise the bonus for new customers. When adjustments to the commission structure are made, they are effective at the beginning of the next calendar month. Swifty's T-Shirts uses an accounting software package to handle its order processing and billing. That software can be used to produce a monthly summary of sales of low-margin items and high-margin items for each salesperson. Each sales- person submits a list of new customers they have attracted that month, which is verified from the accounting data to deter- mine the count of new customers. nanarinn med in each Mr. Sneed has requested that you create a workbook that will calculate the total amount of compensation owed to each salesperson for the month. He asks that the spreadsheet be designed to allow for adjustments to the commission rates. He would like to be able to allow his secretary to do the actual data entry each month. The secretary is a novice spreadsheet user. The monthly data has been downloaded into the table below from the accounting system. The data will be delivered in this format every month. The workbook should be designed so that the new data can update the old data and calculate com- missions for the month based on the new data and any changes in the commission rates. Current values for the commission rates and bonus rates are also shown. Your spreadsheet should treat the commission and bonus rate information as param- eters (assumptions/variables). That is, all uses of a given rate should reference a single cell that can be easily identified and modified by the user. Your calculations should display detailed information about commissions earned by component and by salesperson, as well as summary information for each salesperson and the total compensation to be paid. Commission Rates: High-Margin Items Commission 4% Low-Margin Items Commission 3% New Customer Commission $150/customer New customers 10 2 9 5 Salesperson Zicarelli, Joan Harrington, Diane Lockamy, Bill Belski, Archie Lohrke, Robin Frownfelter, Sam Long. Sebastian Affenbach, Yolonda Scalise, Matise Rahn, Stella York, Robin Garrett, Susan Powell, Bo Rogers, Fred McMullen, Harry Marshall, Marshall Hendon, Sarai Harlow, Gene Salary 475 320 400 345 455 490 420 540 520 345 585 420 330 455 255 415 555 255 Sales of high-margin items 48,701 41,951 30.748 57,465 42,629 40,728 35,410 44,839 47,661 38,129 55,466 41,948 58,603 58,021 30,497 57,338 45,917 56,603 Sales of low-margin items 29,575 31.942 33,152 32,701 30,135 36,996 30,960 33,654 21,214 21.252 32,526 26.842 31.778 26,908 23,396 33,421 28,587 2471 *