MINGGU 5 - 11 OKTOBER 2012 (MICROSOFT EXCEL)

Assignment Excel :

Assignment 2: Microsoft Excel

This is an individual assignment. You are new Human Resource Executive in ABC Enterprise. You will need to use these tables as reference to develop new table by following below instructions.

Table 1 Employee information

Employee ID	Full Name	I/C No	Age	Contact No	Job ID	KPI
ABC1001	Mohd Shahrul Nizam	790323-02-6037	33	012-3456789	70	4
ABC1002	Muhammad Irham Shafy	870915-10-1243	25	019-4567890	501	3
ABC1003	Nur Fathaen Arynee	851023-10-7890	27	013-5678901	509	4
ABC1004	Akmal Razi	881120-05-5287	24	010-9876543	1002	5
ABC1005	Fauziah Ahmad	880428-04-9876	24	013-2345678	1007	1
ABC1006	Zulkifli Zaini	900712-10-6543	22	011-8970324	1009	2
ABC1007	Saiful Nizam	900227-05-7643	22	010-7549239	1012	0
ABC1008	Ahmad Dani	900514-07-8761	22	011-7357401	1013	0

Table 3 Job description

Job ID	Job Description	Job Level	Leave  (day)	Basic Wage (RM)
70	Managing Director	Executive	20	4000
501	Technical Manager	Executive	15	3000
509	Account Manager	Executive	11	2600
1002	Technical Assistant Manager	Non-executive	7	1600
1007	Technician	Non-executive	12	1380
1009	Technician	Non-executive	8	1360
1012	Technician	Non-executive	10	1240
1013	Technician	Non-executive	1	1250

1.       Create a individual payroll calculator table from data in table 1 and 2. Name this table 3 as Payroll Calculator. You need to calculate payroll for this month of September 2012.

a.       Add Employee ID, Employer EPF (12%), Employee EPF (9%), Employer Socso (1.5%), Employee Socso (1.2%) and Tax (10%) fields. Tax field only applicable for employee with salary more than RM2500. Calculate the percentage base on Basic Wage and fill up each of the fields.

b.      Add Net Wage field and fill up in each row. Net wage must deduct from Employee Socso, Employee EPF and Tax contribution.

c.       Create new table 4 as Payroll. You need to add field such Date, Total Amount, Average, Minimum and Maximum Amount. The date will be last date of each month. Calculate previous month of August, current month and expected of next month salary expenses. For your additional information, Saiful Nizam and Ahmad Dani just joined the company early this month and Nur Fathaen Arynee will leave this company end of this month.

d.      Use LOOKUP function to determine the performance base on the KPI. The performance can assign as below :

        0-Not Evaluate, 1-Poor, 2-Medium, 3-Good, 4-Excellent

2.       Sort of each table by following

a.       In ascending sequence by name

b.      By net wage (ascending)

3.       Use the COUNTIF function to calculate the number of employee base on their KPI. Create pie chart for the result above.

Assignment must be posted in your own blog using Google documents.