The second spreadsheet adds some complexity in that it incdludes data as well as variables. This...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
The second spreadsheet adds some complexity in that it incdludes data as well as variables. This spreadsheet réquires you ta 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)Aidentification/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. 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 casily identifled 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 Cammission $150/customer Salesperson Salary Sales of high-margin items Sales of low- margin items New customers 29,575 31,942 33,152 32,701 30,135 10 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 48,701 41,951 30,748 57,465 42,629 40,728 475 320 400 345 455 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 490 8. 35,410 44,839 47,661 38,129 55,466 41,948 58,603 58,021 30,497 57,338 45,917 56,603 420 540 6. 520 345 3 585 10 420 330 455 255 6. 1 415 555 255 The second spreadsheet adds some complexity in that it incdludes data as well as variables. This spreadsheet réquires you ta 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)Aidentification/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. 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 casily identifled 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 Cammission $150/customer Salesperson Salary Sales of high-margin items Sales of low- margin items New customers 29,575 31,942 33,152 32,701 30,135 10 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 48,701 41,951 30,748 57,465 42,629 40,728 475 320 400 345 455 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 490 8. 35,410 44,839 47,661 38,129 55,466 41,948 58,603 58,021 30,497 57,338 45,917 56,603 420 540 6. 520 345 3 585 10 420 330 455 255 6. 1 415 555 255
Expert Answer:
Answer rating: 100% (QA)
zicarelli Joan salary 475 commission on sale of high margin items 4870141948 commission on sale of low margin items 295753887 bonus for new customers ... View the full answer
Posted Date:
Students also viewed these accounting questions
-
A span of five or six subordinates is a rule of thumb at best. How many people report directly to you? How many people, including you, report directly to your manager?
-
Form a different group of five or six to discuss the following: "Theoretical research and applied research should not be mixed. Hence, it is wrong to insist that the approach to an applied marketing...
-
Form teams of five or six persons, each with at least one person who owns or has worked for a small business. Based on their experiences, make a list of the inadequate-HR risks the business endured,...
-
Using a spreadsheet, compute the average return and standard deviation of each of the three asset classes. Compute the annual return of a portfolio consisting of 50% stocks, 40% real estate, and 10%...
-
Do you agree with the statement that ''debits mean increase and credits mean decrease''? If not, what do debit and credit mean?
-
A consumer is in equilibrium at point A in the accompanying figure. The price of good X is $5.a. What is the price of good Y?b. What is the consumer??s income?c. At point A, how many units of good X...
-
Meagan and Jenny are in partnership, sharing profits equally. Provision exists in the partnership agreement for charging interest on capital at the rate of 8% p.a. and interest on drawings at 10%...
-
The declaration, record, and payment dates in connection with a cash dividend of $187,500 on a corporation's common stock are July 10, August 9, and September 18. Journalize the entries required on...
-
The atomic number of element M, N, R, and T are 1, 8, 12 and 14 respectively. The element which can form ions by either losing or gaining electron(s) is M 11. A. B. N C R D. T 12. The atomic number...
-
Find the Norton equivalent at terminals a-b of the circuit in Fig. 4.119 . Vo- +. 10 20 2 V.
-
Write a bash script to take a year as input from user and check whether the input year is a LEAP YEAR OR NOT . HINT: Any year which is exactly divisible by 4 is a leap year.
-
Which statements about the following program are correct? (Choose two.) A. The code does not compile because of line m1. B. The code does not compile because of line m2. C. The code does not compile...
-
Which of the following interface methods are inherited by classes that implement the interface? (Choose two.) A. private methods B. private static methods C. default methods D. static methods E....
-
Which statement about the following code is correct? A. The code contains an invalid constant. B. The method getSize() does not compile. C. The method getLength() does not compile. D. The method...
-
Which line of code, inserted at line p1, causes the application to print 5? A. this(4); B. new Jump(4); C. this(5); D. rope = 4; E. super(4); F. super(5); package games; public class Jump { } private...
-
How many of these methods compile? A. Zero B. One C. Two D. Three E. Four F. Five public class Singing { private void sing (String key) { } public void sing_do (String key, String... harmonies) {...
-
Identify the methods acceptable to identify and take out of service any machinery, tool, material, or equipment that is not in compliance with 29 CFR 1926. Locking controls Tagging as inoperable...
-
Propose a reasonable mechanism for the following reaction. OH
-
Using a financial calculator, solve for the unknowns in each of the following situations. a. On June 1, 2024, Holly Golightly purchases lakefront property from her neighbor, George Peppard, and...
-
Stan rented an office building to Clay for \($3,000\) per month. On December 29, 2022, Stan received a deposit of \($4,000\) in addition to the first and last months rent. Occupancy began on January...
-
In 2017, Harry and Mary purchased Series EE bonds, and in 2023 redeemed the bonds, receiving \($500\) of interest and \($1,500\) of principal. Their income from other sources totaled \($30,000.\)...
Study smarter with the SolutionInn App