Blackhawk Technical College Advanced Excel Worksheet

User Generated

HFRE_ERZBIRQ_YRTNY_1198

Business Finance

Blackhawk Technical College

Description

Unformatted Attachment Preview

Advanced Excel Problem #2 Background The Jackson Travel Agency was founded 20 years ago in Madison, Wisconsin and has expanded to have offices in 10 other cities. Jackson Travel was founded on the principle on providing travel advice and outstanding service for the best travel for their customers. Jackson Travel’s major source of revenue is the commissions they received from the airlines for booking tickets on their airlines. Jackson Travel has implemented a new incentive plan for their travel agents. Starting in October, 2015 each agent will receive a bonus based on the commissions Jackson Travel receives from the airlines for booking tickets and their years of service. At the end of the month the managers of each office must provide the main office in Madison with the amount of bonus due each employee. Jane Gibbs, the manager of the local office would like an Excel spreadsheet that would allow her to track how much each airline owes them and also computes each employee’s bonus. She plans to use this workbook in future years. She can easily download ticket sales information from their current sales tracking program but needs an easy way to analyze the numbers. Procedures 1. Download and save the Ticket_Sales_Data file. This file contains the information regarding ticket sales in October, November, and December. 2. Create a workbook that meets the required business purpose. You will need to use at least 3 worksheets. The minimum worksheets would include: User Information Sheet, Data/Analysis Sheet, and Output Sheet. 3. Use Range names and/or structured table references in your formulas to make the formulas easier to read/understand. 4. All raw data should be organized professionally on one sheet. 5. An employee’s years worked should be calculated using the first day of the month for the month for which the commissions are being calculated. 6. The Output sheet should look similar (not exactly) to: Enter Date for Monthy Calculations: 10/1/2015 Monthly Employee Bonus Calculation Employee Code Employee Name 1 Gibbs Airline Commission Earned $ Employee Years Worked 113.81 11 Employee Bonus $ 17.07 Monthly Commission Due by Airline Airline Name Airline Code United 3 Commission Due $ 55.33 7. On the Output Sheet, the manager should be able to input a date for the month for which the commissions and bonuses are to be calculated and then select an employee code and the name of the airline using dropdown lists. 8. All calculations are for the month selected only. (Hint: use a function to extract the month) Monthly Employee Bonus Calculations:  Use the CHOOSE function to display the Employee Name.  Use the SUMPRODUCT function to calculate the Airline Commission Earned by the selected employee. Note: Do not use the INSERT FUNCTION shortcut in Excel. For SUMPRODUCT to work, you must type the formula in.  Use the VLOOKUP function to display the Employee Years Worked.  Use a nested IF to calculate the Employee Bonus. Monthly Commission due by Airline Calculations:  Use the Index/Match functions to display the code of the airline selected.  Use a SUMIFS function to calculate the Commission Due from the airline. 9. Style your worksheets by adjusting column widths and row heights, aligning row and column headings, formatting values. Check that the worksheet will print correctly. 10.Test the formulas with the information provided. 11.Create a User Information worksheet within your workbook. 12.Follow the guidelines in the Spreadsheet Checklist. To: Accounting Department From: Contract Department Date: September 1, 2015 Re: Airline Commission Rates Below are contracted commission rates for the 4th quarter. If you have any questions, please contact Jim at extension 8965. Airline Commission Rates Code Airline Rate 1 Delta 0.01 2 USAirways 0.015 3 United 0.011 4 Southwest 0.007 5 Northwest 0.012 6 American 0.011 7 Continental 0.013 To: Accounting Department From: Payroll Department Date: September 1, 2015 Re: New Commission Policy Information Per the new incentive policy, each employee will receive a portion of the commission we receive from each airline based upon their years of employment. The following is the new employee commission rate schedule: Years of Service Percentage 10 years or more 15% 3 years to 10 years 10% Less than 3 years 5% The following is a list of employees from your location and their date of hire. Years of service will be calculated as of the first day of the month. Employee Number Employee Name Hire Date 1 Gibbs 3/28/2004 2 Smith 11/15/2007 3 White 4/2/2013 Customer Agner Andrews Barrick Baucom Baumann Burkhead Clegg Dietz Dollar Donaldson Dunnavant Dunston Furr Galvin Guidry Hansen Hillman Hulslander Johnston Jones Kastelberg Lee Lein McGuire McIntosh Moose Mowrey Pearson Roth Roush Stein Wallis Weis Weisner Carlton Carlton Carlton Wagner Wagner Carlton Carlton Mandala Staff Carlton McIntrye Thomas Sale Date Airline Code Sales Person Ticket Sales Price 10/18/2015 1 3 1827 10/15/2015 7 2 1697 10/15/2015 6 3 1806 10/23/2015 4 1 409 10/26/2015 6 1 356 10/20/2015 7 2 798 10/21/2015 3 3 1403 10/26/2015 1 1 1718 10/21/2015 6 2 1071 10/26/2015 7 2 451 10/27/2015 6 3 1751 10/23/2015 1 3 866 10/23/2015 1 1 1337 10/19/2015 6 1 1277 10/26/2015 1 2 1279 10/21/2015 3 3 854 10/26/2015 5 3 731 10/16/2015 5 2 823 10/27/2015 2 1 1417 10/18/2015 2 2 405 10/17/2015 1 1 315 10/19/2015 4 3 1284 10/27/2015 7 2 1158 10/19/2015 3 3 1029 10/27/2015 3 1 747 10/15/2015 6 3 1044 10/24/2015 3 2 536 10/25/2015 6 1 1290 10/19/2015 7 2 1777 10/27/2015 4 2 661 10/24/2015 1 3 501 10/25/2015 7 1 1202 10/20/2015 3 2 461 10/18/2015 4 2 1860 11/4/2015 6 1 1035 11/15/2015 5 2 997 11/30/2015 1 3 487 11/23/2015 5 1 1756 11/26/2015 6 2 1636 11/20/2015 5 3 1324 11/21/2015 2 2 933 11/26/2015 4 3 989 11/21/2015 5 1 946 11/26/2015 2 1 1066 11/27/2015 5 2 660 11/23/2015 6 3 1530 Garcia Reid Anderson Anderson Thomas Reid Reid McIntrye Thomas Garcia Garcia Thomas Thomas McIntrye Carlton Carlton Carlton Thomas Staff Carlton Anderson Carlton Garcia Thomas Staff Garcia McIntrye Reid McIntrye Matthews Matthews Staff Staff Anderson Staff Philips Staff Staff Carlton Staff Reid Staff Mandala Thomas Carlton Reid McIntrye 11/23/2015 11/19/2015 11/26/2015 11/21/2015 11/26/2015 11/16/2015 11/27/2015 11/18/2015 11/17/2015 11/19/2015 11/27/2015 11/19/2015 11/27/2015 11/15/2015 11/24/2015 11/25/2015 11/19/2015 11/27/2015 11/24/2015 11/25/2015 11/20/2015 11/18/2015 11/13/2015 11/10/2015 11/10/2015 11/18/2015 11/21/2015 11/15/2015 11/16/2015 11/21/2015 11/16/2015 11/21/2015 11/22/2015 11/18/2015 11/18/2015 11/14/2015 11/21/2015 11/16/2015 11/21/2015 11/11/2015 11/22/2015 11/13/2015 11/12/2015 11/14/2015 11/22/2015 11/14/2015 11/22/2015 2 2 3 2 7 2 4 1 4 6 2 1 5 5 4 7 4 7 6 2 5 1 3 4 5 3 6 7 3 5 4 6 4 2 2 7 4 2 4 2 2 1 1 6 2 5 5 3 3 2 3 2 2 3 1 3 2 2 2 1 1 2 1 3 2 2 2 2 3 3 3 1 3 2 3 1 3 1 1 1 1 1 2 3 2 1 2 3 3 1 1 3 1 2 451 501 1703 1912 1285 399 960 1449 1195 1558 1022 565 1973 907 517 796 772 390 2000 480 1581 1746 860 652 811 1465 513 491 1493 366 613 1540 1563 943 1846 1775 968 1294 1665 1092 1220 1564 1529 699 1979 381 788 Thomas McIntrye Patel Anderson Staff Patel Philips Staff Staff Thomas Staff Staff Desprez Anderson Staff Staff Thomas Staff Staff Randolph Randolph Staff Staff Anderson McIntrye Staff Staff Thomas Reid Garcia Staff Thomas Reid Staff Matthews Staff Mandala Philips Reid Staff Carlton Staff Randolph Randolph Thomas Thomas Reid 11/10/2015 11/19/2015 12/3/2015 11/30/2015 11/30/2015 12/8/2015 12/11/2015 12/5/2015 12/6/2015 12/11/2015 12/6/2015 12/11/2015 12/12/2015 12/8/2015 12/8/2015 12/4/2015 12/11/2015 12/6/2015 12/11/2015 12/1/2015 12/12/2015 12/3/2015 12/2/2015 12/4/2015 12/12/2015 12/4/2015 12/12/2015 11/30/2015 12/9/2015 12/10/2015 12/4/2015 12/12/2015 12/9/2015 12/10/2015 12/15/2015 12/12/2015 12/12/2015 12/20/2015 12/23/2015 12/17/2015 12/18/2015 12/23/2015 12/18/2015 12/23/2015 12/24/2015 12/20/2015 12/20/2015 2 5 4 4 6 4 4 2 1 6 6 7 6 3 4 3 3 4 2 1 4 2 7 6 7 3 4 1 1 3 7 3 4 7 5 2 6 3 6 4 4 3 3 3 6 4 7 3 2 1 1 3 3 1 1 2 3 2 3 3 1 2 3 3 3 2 1 3 1 3 3 1 2 1 2 3 3 1 1 2 3 3 1 3 1 1 1 2 2 1 1 3 1 3 1976 1790 1554 1827 1146 806 1361 1438 553 961 1525 1199 1489 1749 1372 1765 1968 402 1171 1809 1741 1574 524 1492 632 1226 991 1003 1467 1507 706 400 1684 980 361 374 1673 822 1506 1450 1186 1259 874 1259 1988 1296 531 Staff Staff Reid Reid Patel Matthews Matthews McIntrye Anderson Thomas Reid Desprez Desprez Thomas 12/16/2015 12/23/2015 12/18/2015 12/23/2015 12/13/2015 12/24/2015 12/15/2015 12/14/2015 12/16/2015 12/24/2015 12/16/2015 12/24/2015 12/12/2015 12/21/2015 6 3 5 2 7 3 6 1 1 1 5 2 4 2 1 3 1 2 3 2 3 3 1 3 2 3 2 1 1838 1434 1668 722 943 1030 1902 1315 1390 745 1395 1950 1898 340
Purchase answer to see full attachment
Explanation & Answer:
Worksheet
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Jackson Travel Ltd
User Information
1. Different commission structures from different department for the
airline and the staff
Prescribe airline commission rates as per the contract department for
Airline Commission Rates
Code
Airline
Rate
1 Delta
0.01
2 USAirways
0.015
3 United
0.011
4 Southwest
0.007
5 Northwest
0.012
6 American
0.011
7 Continental
0.013

Commission incentive computation as per the payroll department
Years of Service
Percentage
10 years or more
15%
3 years to 10 years
10%
Less than 3 years
5%
Date of hiring for the staff as per the payroll department
Employee Number
Employee Name Hire Date
1 Gibbs
2 Smith
3 White

3/28/2004
11/15/2007
4/2/2013

2. Data analysis for the 3 months as retrived from the system.
3. Under the output worksheet only amend the yellow that is for the period under analysis.
4. When computing for any month enter the first day of the month.

Customer
Agner
Andrews
Barrick
Baucom
Baumann
Burkhead
Clegg
Dietz
Dollar
Donaldson
Dunnavant
Dunston
Furr
Galvin
Guidry
Hansen
Hillman
Hulslander
Johnston
Jones
Kastelberg
Lee
Lein
McGuire
McIntosh
Moose
Mowrey
Pearson
Roth
Roush
Stein
Wallis
Weis
Weisner
Carlton
Carlton
Carlton
Wag...


Anonymous
Just the thing I needed, saved me a lot of time.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags