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)
Employee Information
![]()
![]()
Answer to Question 2:
|
Employee Name
|
Hours worked
|
Regular Hours
|
Overtime Hours
|
Hourly Pay
|
Base Amount
|
Over-Time
|
Total Pay
|
|
|
|
|
|
|
|
|
|
|
Jennifer Leepoz
|
36
|
36
|
0
|
80
|
2880
|
0
|
2880
|
|
Kath Hudson
|
52
|
37
|
15
|
80
|
2960
|
1350
|
4310
|
|
Lara Bangle
|
40
|
37
|
3
|
50
|
1850
|
270
|
2120
|
|
Novak Djoker
|
37
|
37
|
0
|
30
|
1110
|
0
|
1110
|
|
Paris Holton
|
45
|
37
|
8
|
70
|
2590
|
720
|
3310
|
|
Rafael Nooderly
|
44
|
37
|
7
|
60
|
2220
|
630
|
2850
|
|
Ricky Mortini
|
52
|
37
|
15
|
60
|
2220
|
1350
|
3570
|
|
Russell Creak
|
34
|
34
|
0
|
80
|
2720
|
0
|
2720
|
|
Selina Geemak
|
30
|
30
|
0
|
40
|
1200
|
0
|
1200
|
|
Willard Smith
|
39
|
37
|
2
|
30
|
1110
|
180
|
1290
|
|
|
|
|
|
|
|
|
|
|
Total
|
409
|
359
|
50
|
580
|
20860
|
4500
|
25360
|
Answer to Question 3.a:
|
Row Labels
|
Sum of Over-Time
|
|
Kath Hudson
|
1980
|
|
Ricky Mortini
|
1980
|
|
Grand Total
|
3960
|
Answer to Question 3.b:
In the present context the use of pivot table is seen to be having a significant role in taking various decisions along with the functions which are stated in the previous section.
The evaluation of the charge to “ABL Construction by Simple Contracts Ltd.” is seen to be based on applying pivot tables and the value is calculated as $20330.
|
Employees at Level 4 and Above:
|
| |
|
|
| |
Values
|
|
|
Row Labels
|
Sum of Hourly Pay
|
Sum of Total Pay
|
|
Jennifer Leepoz
|
80
|
2940
|
|
Kath Hudson
|
80
|
4380
|
|
Paris Holton
|
70
|
3450
|
|
Rafael Nooderly
|
60
|
3060
|
|
Ricky Mortini
|
60
|
3780
|
|
Russell Creak
|
80
|
2720
|
|
Grand Total
|
430
|
20330
|
Answer to Question 3.c: Answer to Question 2:
Part 1
The use of business intelligence from the past decade has been seen to having a more significant role too many companies. It has allowed the companies to stay connected and assist in making second by second decisive choices on the hard facts rather than just relying on gut feeling or conjecture. The use of business intelligence first began when the people that have been to raise the reports on what they were relying on. This ranged from sales performance figures to the number of customer complaints as they have received and how their actions had an impact on leading to a change (The Past and Barton 2018).
The use of Internet has allowed the companies to check data and confirm that it is up to date to the point at which the report was pulled. Henceforth, in case a trend and be identified early with the recent data, the corporates will be able to act quickly with the use of business intelligence. In addition to this, with the advent of powerful systems the reports can be updated in real-time thereby allowing for completely accurate monitoring. It needs to be understood that business intelligence has therefore come a long way with increased volume of data which are being gathered and put to use for future predictions (Qureshi, Khan and Saif 2017).
The use of data gathering is seen to be having a profound effect for majority of the companies and has the likelihood of huge significance to the business intelligence functions. Reports to be pulled with a higher degree of accuracy and larger number of data has more chances of finding previously undiscovered trends. In addition to this, the increase in the overall volume of the data and the importance of having clean and easily usable data is seen to be more significant in the recent data. The clean data has therefore meant that the findings from business intelligence is more accurate and therefore conducive for the company as a whole. Therefore, the emergence of clean data and accurate models has given business intelligence a better possible opportunity for making a vital impact in the future days (Bryman and Bell 2015).
Total Payroll Calculation
Part 2
The two-important characteristics for the business intelligence is considered with the following aspects:
Executive Dashboards- In general the business dashboards are discerned to act as a powerful tool which are executed as per making summary of intricate data and present it in an appropriate way. The executive dashboard has been considered with the main purpose of lending assistance to the significant performance indicators which should be applied in an appropriate manner. In addition to this, the application of personalized dashboards is seen to be conducive in delivering and understanding real-time data for the “business leaders and decision makers”, thereby allowing for swift response in several aspects of Internet and external events (Hartley and Seymour 2015).
Moreover, the executors need to take into account an easy to understand key performance indicators and summary of information on scheduled daily basis. Therefore, an executive dashboard is regarded as a computer interface which depicts the KPI along with corporate officers needing to operate effectively for an enterprise. Furthermore, the exception in factors of reporting should be able to avert the unexpected events and situations which needs immediate action. The personalized data allows the decision-maker for quickly bringing into account guess work, without the need to create any irrelevant information (Yeoh and Popovi? 2016).
What if analysis- A well-known feature commonly applied in the business intelligence to is considered with the application of what if analysis which allows the businesses to evaluate the several types of potential impacts which are critical to business decision before they occur. The existing data can be used to formulate the various types of strategies for achieving the relevant targets set for business and avoid any instance of “hit and miss” approach. This particular consideration allows the management in understanding of appropriate strategy (Caserio and Trucco 2018).
A common use for the what-if analysis is depicted in various types of financial perspectives via the management needs to understand the implications of having a project to get approved in a particular situation. In addition to this, the what-if analysis performance is applied at several types of tools such as “1000minds, Ahoona, AltovaMetaTeam, Analytica, Criterium Decision Plus, D-Sight, DecideIT, Decision Lens, Expert Choice, Hiview3, Intelligent Decision System and Logical Decisions” (Fairhurst 2015).
References
Bryman, A., and Bell, E., 2015. Business research methods. Oxford University Press, USA.
Caserio, C. and Trucco, S., 2018. Business Intelligence Systems. In Enterprise Resource Planning and Business Intelligence Systems for Information Quality (pp. 43-73). Springer, Cham.
Fairhurst, D. S. (2015). Using Excel for business analysis: a guide to financial modelling fundamentals. John Wiley & Sons
Qureshi, N.A., Khan, B.A. and Saif, J.A., 2017. Business Intelligence Systems in the Holistic Infrastructure Development Supporting Decision-Making in Organisations.
The Past, P. and Barton, D. (2018). The Past, Present & Future Of Business Intelligence | Articles | Analytics.
Yeoh, W. and Popovi?, A., 2016. Extending the understanding of critical success factors for implementing business intelligence systems. Journal of the Association for Information Science and Technology, 67(1), pp.134-147.
