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%...
-
Dun & Bradstreet reports, among other things, information about new business incorporations and number of business failures over several years. Shown here are data on business failures and current...
-
The standard enthalpy of combustion of solid urea (CO (NH2)2) is -632 kl mol-1 at 298 K and its standard molar entropy is 104.60 J K-1 mol-1, Calculate the standard Gibbs energy of formation of urea...
-
Which core job characteristic promotes job satisfaction for nurses? A. Rigid rules. B. Autonomy in decision making. C. Hierarchical decision making. D. Lack of communication with physicians.
-
Mary Kelly is a scholarship soccer player at State University. During the summer, she works at a youth all-sports camp that several of the university's coaches operate. The sports camp runs for 8...
-
Case Study - Auditing ***** Please can somebody help me with this properly??? I need a full answer. the flow charts, report and everything. The Accounting firm The firm of Bracken Ridge Accounting...
-
You are discussing your 401(k) with Dan Ervin when he mentions that Sarah Brown, a representative from Bledsoe Financial Services, is visiting East Coast Yachts today. You decide that you should meet...
-
Joe believes his employees at the pizza shop would be more motivated if they handled orders from start to finish. They will prepare and bake the pizza themselves rather than doing specialized tasks....
-
Discuss, using the concept of a load line, how a simple common-source circuit can amplify a time-varying signal.
-
What information is likely to be included on a job authorization form? Why prepare such a form?
-
Discuss the physical meaning of the small-signal circuit parameter \(r_{o}\).
-
Describe the basic operation of a junction FET.
-
Compare the respective responsibilities of the audit director, team leader, and audit staff for each of the nine steps in the audit process.
-
(b) You are an options trader for a hedge fund. Your assistant prepared the following sheet containing the prices of various options. Expiration Months Stock Stock Option Price Exercise March June...
-
Complete the following acid-base reactions: (a) HCCH + NaH
-
Describe how monopoly regulation influences output, price, economic profit, and efficiency.
-
Describe and explain the inefficiencies of monopoly.
-
Why is pure monopoly rare?
Study smarter with the SolutionInn App