accounnnnting

1

Excel Assignment #3

Due: November 7, 2017 by 9:00 am

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.

2

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.

3

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.