Information Systems Assignment
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?
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Commission-Assignment.rar