The following employees work for Hi-Fi Contracts Ltd, a public company specialising in contract labour force for different projects in construction industry. The employees are paid an hourly rate, based on their Job Classification level. If an employee works more than 36 hours, they will be paid the overtime rate for the additional hours.
The following table summarises the classifications and level of pay per classification:
The overtime hourly rate is $ 85 per hour for ALL employees, regardless of their classification level.
Hi-Fi Contracts Ltd has the following employees:
|Employee name||Classification Level|
The hours worked for the week ended 30th June, 2017 are as follows:
|Employee name||Hours worked|
REQUIRED: One worksheet must be used to provide answers to this question.
- No marks will be awarded if the correct function or formula is not used or if data has been manually entered.
- Take care regarding professional presentation of your work at every step. All dollar amounts must be provided in currency format and 2 decimal places.
|1||a) Copy Table A into your worksheet. Set up a ‘range’ for the data. Name the range `classification level’b) Copy Table B to the same worksheet.
|2||Using Table C data and keeping employee order unchanged, set up a payroll table to calculate total payroll for each employee. Your main payroll table should have the following headings:|
|a) Use the IF function and absolute referencing to calculate the Regular Hours column. Use a formula to calculate the Overtime hours.
b) Use the VLOOKUP function with reference to the range created earlier to determine the Hourly Pay for each employee.c) Use suitable formulae to calculate the Base and Overtime amount and Total pay for each employee.d) Sort the table in order of employee names. All columns must be totalled as required.
e) Using another formula, identify the employee(s) that earned maximum overtime.
f) Ensure that your work is professionally presented, with suitable formatting, borders and lables.
|3||a) Discuss, justify and illustrate two more function that can be included, in a case like this, to help with decision making.|
Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).
After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.
If your question is slightly different from the above question, please contact us at firstname.lastname@example.org with your version of question.