Commission Report Assignment

MGMT 341, Information Systems

Commission Report Assignment

Submission Deadline is posted in Blackboard

______________________________________________________________________

Read through this assignment in its entirety before attempting to complete it. This assignment should

be formatted in a professional manner, using appropriate titles, column headers, professional alignment

and formatting. A title within each worksheet, at the top left of the sheet, should contain your first

name, last name, course and section number, and the title of the assignment. Submissions of the

wrong file type will receive a grade of 0.

The data provided, commision_data.csv, is a record of sales which lists; the date, salesperson name, the

transaction number of the sale, the amount of the sale - in that order from left to right in the file. The

commission earned by the salesperson will be calculated as the total of the sale excluding labor fees

multiplied by the commission rate. The rate of commission varies depending on the region of the sale.

(Throughout this assignment, check your work for data integrity problems. Pay special attention to using

the correct number of decimals.) A summary report of commissions earned will be created.

Part I

Separate the salesperson name for each transaction to two fields for first and last name using an Excel

function.

In an adjacent column, create an IF statement to calculate the fee for labor for each transaction. Fees

should be 11% on sales under $500 and 9.5% on sales of $500 or more.

In another column, calculate the total sale amount for each transaction including the labor fee.

The region of the sale is indicated by the second character of the transaction number. Store the region

of each transaction in its own column.

Each salesperson earns a commission on every sale. Commission rates vary depending upon the region

of the sale. Create a VLOOKUP table (several columns to the right of your data) for the following

commission rates:

Region 1 = 2.3%

Region 2 = 2.9%

Region 3 = 3.1%

Region 4 = 3.7%

Store the commission rate for each transaction by referencing the VLOOKUP table.

TIPS: After entering the VLOOKUP formula, if you get errors showing “#N/A”, check the following things:

1) The VLookup table must be created in ascending order.

2) The VLookup function must use absolute cell referencing to the table. For example, “$M2:$N2” as the

table reference.

3) The data types using VLookup must be compatible. Convert the data in the region column to text by

copying it, then pasting it back into the same column as text. Then convert the data to a number by

clicking on the small error message symbol and selecting “convert to number”.

In an additional column, calculate the commission for each sale.

Below is an example of what your work may look like at this point.

Part II

Copy all data to a second sheet. (The Paste Values command is necessary in this step to copy the

calculated values.) On this sheet, you will create a summary report of total commissions for each

salesperson.

Remove columns that contain unnecessary or duplicated data. (You may move some columns if you

think it creates a more aesthetically pleasing report.) Insert appropriate titles and headings including

the current date, updated dynamically.

Create totals of commissions for each salesperson.

Save your file with a summary report sheet open and the first 4 salespersons’ data collapsed which hides

it from view, showing only their subtotals. The other salespersons should have all data expanded, in

view. Below is an example of a report showing collapsed and expanded subtotals. (Yours will not look

exactly like this. This example does not include all of the proper formatting you are expected to

complete and some of the data is different.)

Part III

Review the data and your work. Create a third worksheet titled “Comments.”

a. Comment briefly on the quality of the data in the assignment. For example, is it of high quality?

How do you know?

b. Comment briefly on the integrity of the data and your file. For example, did you maintain the

integrity of the data? How? What might be some common problems with data integrity in an

exercise such as this?