Worksheet 1 (Bonus worksheet) - Compute bonuses paid to sales agents - You have been hired to construct a spreadsheet that calculates bonuses paid to sales agents. On the Bonus worksheet is the following data: • Agents Name • Hours worked for the year • Years of service • Job title – there are only 4 titles: level-1, level-2, manager, executive • Salary • Last year’s bonus Bonuses are given to employees for the following: a) Job title bonus – this is based on the job title of the employee as described below. Job Title Bonus Level-1 250 Level-2 500 Manager 900 Executive 1,200 b) Years of service –2.5% of salary if the number of years of service is greater than 4 years otherwise it is 1.5% of salary. c) If an employee’s salary is less than $50,000 and if the employee has more than 7 years of service, a $500 bonus is given otherwise the employee does not get this type of bonus. Calculate and display for each employee the following: 1) The bonus for Job title 2) he bonus for Years of service 3) The bonus for salary and years of service 4) The total of all 3 bonuses 5) The percentage of an employee’s total compensation the bonus accounts for where total compensation is salary plus bonus 6) The percent change of this year’s bonus to last year’s bonus Your spreadsheet should consist of formulas that work if any of the data (e.g. any of the data given in the template) or assumptions (e.g. any of the values that describe the bonus calculations) are changed. That is, do write formulas that only work for the data given. Make sure your spreadsheet is laid out in an organized, logical manner and has the following: • Each column should have a heading with a description that is text wrapped. • All bonus values should be displayed as a number (no $) with 0 decimal places. • All percentages should be displayed as a percentage to 1 decimal place. • Be sure to have an assumptions section. • Use absolute addressing where appropriate. Worksheet 2 (Income Statement) – on this worksheet (2nd tab in Excel template) you are given an Adjusted Trial Balance. Starting in row 33 prepare an Income Statement in good form. Be sure that the cells used in your Income Statement reference the cells in the Adjusted Trial Balance.