Get Instant Help From 5000+ Experts For
question

Writing Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!

Pivot Table Calculation For Employee Payroll: The Essay On Business Intelligence.

5 Pages / 1,035 Words Published On: 20-11-2020

Employee Information

The following employees work for Simple 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 35 hours,
they will be paid the overtime rate for the additional hours.
The following table summarises the classifications and level of pay per classification:

TABLE A:
The overtime hourly rate is $ 90 per hour for ALL employees, regardless of their classification
level.
Simple Contracts Ltd has the following employees:
TABLE B:
Employee name Classification Level
Paris Holton 5
Ricky Mortini 4
Jennifer Leepoz 6
Selina Geemak 2
Willard Smith 1
Russell Creak 6
Rafael Nooderly 4
Novak Djoker 1
Lara Bangle 3
Kath Hudson 6
Classification Levels
Level Hourly pay
1 30.00
2 40.00
3 50.00
4 60.00
5 70.00
6 80.00 


_______________________________________________________________________
CISM 4000 T1 2018 Excel and Case Study Page 3
The hours worked for the week ended 30th June, 2017 are as follows:
TABLE C:
Employee name Hours worked
Novak Djoker 37
Willard Smith 39
Lara Bangle 40
Kath Hudson 52
Selina Geemak 30
Paris Holton 45
Russell Creak 34
Jennifer Leepoz 36
Ricky Mortini 52
Rafael Nooderly 44

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.
? Add a third column titled ‘hourly pay’. Use the VLOOKUP function to pick up the
correct hourly pay rate for each employee from the ‘classification level’ range set
up above.
? Create a ‘range’ for the data in these 3 columns. Name the range ‘pay rate table’.
This range will be used in a question below.
 (10 marks)


2 Using Table C data and keeping employee order unchanged, set up a payroll table to calculate
e total payroll for each employee. Your main payroll table should have the following headings:
Employee
Name
Hours
worked
Regular
Hours
Overtime
Hours
Hourly
Pay
Base
Amount
Overtime
Total
Pay
a) Use the IF function and absolute referencing to calculate the Regular Hours column.
b) Use a formula to calculate the Overtime hours.
c) Use the VLOOKUP function with reference to the range created earlier to determine
the Hourly Pay for each employee.
d) Use suitable formulae to calculate the Base and Overtime amount and Total pay for
each employee.
e) Sort the table in order of employee names. All columns must be totalled as required.
f) Ensure that your work is professionally presented, with suitable borders and labels
 (30 marks)


_______________________________________________________________________
CISM 4000 T1 2018 Excel and Case Study Page 4
3 a) Using another formula, identify the employee(s) that earned maximum overtime.
b) Using Drop down function and Vlookup, prepare a separate summary table to
display, one employee at a time, as follows
Employee Name Overtime Earned

 (10 marks)
4 Discuss, justify and illustrate two more functions that can be included, in a case like this,
to help with decision making.
 (10 marks)
Q 2 (40 marks)


Research 2 commonly used Business Intelligence Tools that help extract intelligence from
the business data to support decision-making through modelling and analysis.
1 Describe the growth of Business Intelligence in the past decade. You can use graphs, tables,
success stories, as you deem fit, to present your cases.
 (20 marks)
2 List and briefly illustrate at least 2 features of commonly used Business Intelligence Tools that
can support decision-making.
 (20 marks)


Your report need be:-
? Professionally presented, in your own words. Your research should include at least 3
references, such as the professional body articles as well as text books and peer
reviewed academic journals. Vendor websites are not appropriate sources of
information for this task. Use appropriate referencing in your report.
? Word limit : 1000 ( excluding references, diagrams)

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Pivot Table Calculation For Employee Payroll: The Essay On Business Intelligence.. Retrieved from https://myassignmenthelp.com/free-samples/cism4000-accounting-information-systems/sales-performance.html.

"Pivot Table Calculation For Employee Payroll: The Essay On Business Intelligence.." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/cism4000-accounting-information-systems/sales-performance.html.

My Assignment Help (2020) Pivot Table Calculation For Employee Payroll: The Essay On Business Intelligence. [Online]. Available from: https://myassignmenthelp.com/free-samples/cism4000-accounting-information-systems/sales-performance.html
[Accessed 23 September 2023].

My Assignment Help. 'Pivot Table Calculation For Employee Payroll: The Essay On Business Intelligence.' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/cism4000-accounting-information-systems/sales-performance.html> accessed 23 September 2023.

My Assignment Help. Pivot Table Calculation For Employee Payroll: The Essay On Business Intelligence. [Internet]. My Assignment Help. 2020 [cited 23 September 2023]. Available from: https://myassignmenthelp.com/free-samples/cism4000-accounting-information-systems/sales-performance.html.


Stuck on Any Question

Our best expert will help you with the answer of your question with best explanation.

question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

question
We will use e-mail only for:

arrow Communication regarding your orders

arrow To send you invoices, and other billing info

arrow To provide you with information of offers and other benefits

Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

loader
250 words
Error goes here

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Other Samples
icon
5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?
close
Subtraction Payment required!

Only one step away from your solution of order no.