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.