How you will create a worksheet for each salesperson

Assignment Help Financial Accounting
Reference no: EM131712105

Assignment

The purpose of this assignment is to give you practice on designing a spreadsheet and using important Excel functions. This is the third of four Excel assignments this semester and this assignment is intended to apply what you practiced on the first two assignments. This assignment will require you to think about creating a spreadsheet so that a routine clerical process can be automated and completed easily in Excel. Similar issues come up in real life frequently.

You may complete the assignment on your own or with one partner. Other than your assignment partner (if you choose to work with one), you may not use the help of other classmates, friends, parents, siblings, relatives, etc. Any copying or obtaining help from anyone other than your partner (if applicable) will be treated as an Honor Code violation. You have a lot of leeway in how you put the spreadsheet together so my expectation is that no two submissions will look alike.

This assignment is worth 12 points and will be graded based on how well the file accomplishes the task, as well as completeness, neatness, apparent effort, and the ease of understanding your work. Please submit your completed assignment through the Assignments tab on Isidore prior to 9:00 am on November 7th.

Be sure to read and follow all instructions! Not following instructions will significantly lower your score.

For this assignment, assume that you work in the Accounting department for a small company that sells three products. The company has four salespersons who are compensated monthly based entirely on commissions and bonuses. Up to now, the monthly salesperson compensation has been calculated manually by a clerical employee. A new Controller was recently hired, and he has tasked you with creating an Excel spreadsheet to automate the monthly calculation.

The salespeople are paid a commission of 5% on the selling price of Product H, the highest- margin product the company sells. They are paid a commission of 4% and 3% on the selling price of Product M and Product L, respectively. Product M is the second highest-margin product and Product L is the lowest-margin product. In addition, each salesperson is paid a bonus of $100 for every new customer to whom they make a sale.

The data file for this assignment contains a download of sales data for the month of September from the company's sales management system. The download lists, by date, each sale made, as well as the name of the salesperson, the product, the selling price, and the order number in the specific customer's history (e.g., 7 for that customer's seventh order with the company since operations began). The information from the download will be needed to calculate compensation by salesperson according to the policy in the previous paragraph.

Your Task:

For this assignment, you are tasked with automating the process of calculating compensation for each salesperson. You should add one worksheet for each sales employee. Each worksheet should show an overall summary that calculates commissions earned by type of product and any first-time customer bonus. In addition, the worksheet for each individual employee should contain the detail of all sales they made for the month so each salesperson can audit their compensation calculation and see how amounts were calculated. Only include sales on a salesperson's worksheet if they made the sale (e.g., the sales detail on Bret's worksheet should only include Bret's sales and not sales from any other salesperson).

The goal is to create a file that can be used every month such that the download would be copied in and the file would automatically make the necessary calculations. There are many different ways to accomplish this and you are free to pursue any method that you choose. Below are additional guidelines for the creation of your file.

1. In future months, the process will start with an Excel file with a single worksheet containing a download from the sales system (i.e., the same as what you have been given for this assignment but the data will differ). To automate the process, you should use macros (one or more, depending on how you set it up) as well as formulas so that Excel does all of the work in the future.

2. Begin by planning out how you will create a worksheet for each salesperson and how you will populate each salesperson's worksheet with their individual sales data. It is best to actually do the steps before recording a macro. Also write the steps down so that when you eventually record a macro, you know exactly what steps to do in what order and you will not have unnecessary steps in the macro.

a. Simplifying assumption: you may assume that each salesperson will have the same number of sales transactions in each future month. However, the amount of sales volume will change from month to month and so will the number of previous orders for each customer. In other words, if Bret has 25 individual sales transactions in the data file for September, you may assume he will have 25 individual sales transactions in future months, but the information for each sale will differ. Depending on how you approach the task, this may help you to complete it more efficiently.

3. Then, once each salesperson's data is in their individual worksheet, create a summary at the top of the worksheet that calculates the salesperson's monthly compensation. The summary should show the total sales revenue by product, the appropriate commission percentage for each product, and the amount of commission earned by product. In addition, the bonus for sales to first-time customers should be added to arrive at the total compensation to be paid to the salesperson for the month. You may set this summary up however you desire as long as it will be clear to each salesperson. (Remember, this is their compensation and it is important that they understand what you give them!)

a. Note that it is easiest to read if the summary showing the compensation calculations is at the top and the detail of each transaction is below the summary.

4. You should use only cell reference formulas in each worksheet. You should not manually enter a number into any formula.

5. Format each salesperson's worksheet so that each one is presentable and professional looking. Each salesperson's worksheet should be formatted the same.

6. Once you have completed the steps as a "dry run", you are happy with the results, and you have documented your steps, you are now ready to record one or more macros to automate the process. I recommend opening the data file for the assignment as a new file and starting with a fresh file rather than deleting your "dry run". You may record one or more macros, depending on how you wish to execute the process.

a. Be sure to enter on the Download worksheet a definition for each macro that you record. Show the macro shortcut as well as a short description of what it does.

b. Also, be sure to save your file as a macro enabled workbook!! This is VERY important. Otherwise, your macro will not be saved in the file and you will not receive credit for the work.

7. After recording your macro(s), it is a good idea to test it out. You can delete the individual salesperson worksheets and run your macro(s) to make sure it produces the results that you expect.

8. I will be acting as your supervisor in case you have questions. I will not provide answers but I will answer questions as long as you have thought about your approach.

Attachment:- Assignment-Data.rar

Reference no: EM131712105

Questions Cloud

Marketing mix align with the four strategy categories : How do the elements of the marketing mix align with the four strategy categories in the left-hand column?
Most economists believe that the current credit crisis : Most Economists believe that the current credit crisis will not really end until housing prices stop declining
Threshold in the center of the axon : What would happen if a neuron were stimulated to threshold in the center of the axon (halfway between the axon hillock ands the synaptic end bulbs)
Discuss money and capital market : In the 1980's the Savings and Loans industry was in crisis and the crisis required government intervention
How you will create a worksheet for each salesperson : Begin by planning out how you will create a worksheet for each salesperson and how you populate each salesperson's worksheet with their individual sales data.
Summarize the center and spread in the given data : Pizza prices. The histogram shows the distribution of the prices of a small, plain pizza (in $) for 156 weeks in Dallas, Texas.
Identify the top leadership points you would want to share : Identify the top two leadership points you would want to share at the workshop, and discuss why you selected those points.
Should there be global internet privacy policy : Is that the same person who should have "keys" to employee data? Should there be global Internet privacy policy?
Defined in terms of satisfying customers : Marketing has often been defined in terms of satisfying customers’ needs and wants.

Reviews

Write a Review

Financial Accounting Questions & Answers

  Coverage high deductible health plans

Sam is 54 years old, and Carol is 62 years old. Both were enrolled in self-only coverage High Deductible Health Plans (HDHPs) through their employers for the entire year of 2014. Both were enrolled in self-only coverage High Deductible Health Plans

  Calculate the ending balance of finished goods

Calculate the ending balance of Raw Materials and calculate the ending balance of Work in Process and calculate the ending balance of Finished Goods.

  What percentage of payment goes toward interest for payment

Katie Couric's company borrowed $100,000 from the bank at 8% interest compounded quarterly in order to put in a pool for the senior management team and boost morale. If the bank wants to be paid back in monthly payments over 6 years... How much is ea..

  Gross profit between LIFO and FIFO inventory cost systems

The units of Manganese Plus available for sale during the year were as follows: Mar. 1 Inventory 21 units @ $30 $630 June 16 Purchase 31 units @ $33 1,023 Nov. 28 Purchase 43 units @ $37 1,591 95 units $3,244 There are 17 units of the product in the ..

  Question evaluate the market value of the given bond8 bonds

question evaluate the market value of the given bond8 bonds of 150000 sold 30 months after bond issue date. 15-year

  Non-controlling interest share of subsidiary net income

Tray Co. reported current earnings of $560,000 while paying $56,000 in cash dividends. Sparrish Co. earned $140,000 in net income and distributed $14,000 in dividends. What is the non-controlling interest's share of the subsidiary's net income?

  Prepare corporate income statement-earnings per share

The following information is available for Norman Corporation for the year ended December 31, 2014: sales revenue $817,700, other revenues and gains $88,100, operating expenses $105,800, cost of goods sold $464,100, other expenses and losses $33,100,..

  Question evaluate product cost and purpose an income

question evaluate product cost and purpose an income statement under absorption and variable costing.polk company

  Determine the average costnbsp from the given datasvens

determine the average costnbsp from the given data.svens cookhouse is a popular restaurant located on lake union in

  Questiontallahassee builders inc signed a contract to

questiontallahassee builders inc. signed a contract to prepare a certain project for 4000. in 2010 800 of cost was

  Roselle appliance uses a perpetual inventory system

Roselle Appliance uses a perpetual inventory system. For its flat-screen television sets, the January 1 inventory was 5 sets at $563.00 each. On January 10, Roselle purchased 7 units at $751.40 each. The company sold 3 units on January 8 and 5 units ..

  Describe what the consolidation process entails

A company is considering acquiring another small company. Communicate the crucial elements of creating consolidated financial statements including the income statement, balance sheet, and statement of cash flows in the case of possibly acquiring anot..

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd