Here The Video about Microsoft Excel.. So this video is about to tell the tutorial for the Beginner. This video contain how to use the Microsoft excel... Actually, Microsoft excel is very simple to use.. And here i will provide for you all the exercise for Microsoft excel... Enjoy the video and exercise...
Exercise 1.
Syarikat Maju Jaya Sdn Bhd has a 11 employees from the low to the
highest position. The company have decided to give annual bonuses to its employees.
The bonus given is based on the following formula:
Category 1: Salary
RM1000 and below the bonus is RM2,000.00 + 75% salary
Category 2:
Salary RM1001 – RM5000 the bonus is
RM2,000.00 + 50% salary
Category 3: Salary
RM5001 and above, bonus is RM2,000.00 + 25% salary
The following are the list of employees with their
address and salary:
|
Name
|
Address
|
Salary (RM)
|
|
Encik Fuad Azman
|
No 45 Kampung Bahagia, 06010, Jitra Kedah
|
6,510.00
|
|
Puan Noraziah Harun
|
No 23 Jln Tengas 3, Taman Tengas, 06010 Changlun Kedah
|
5,510.00
|
|
Encik Zabidi Hassan
|
No 56, Jln Sesak 2, Taman Sesak, 06010 Changlun Kedah
|
880.00
|
|
Cik
Maziah Rabu
|
No 33, Jln Kaya 5, Taman Kaya, 06010 Changlun Kedah
|
1,250.00
|
|
Encik Hambali Mohamad
|
No 100, Kampung Idaman, 06010, Jitra Kedah
|
955.00
|
|
Encik Bakaruddin Ali
|
No 45, Jln Suria 8, Taman Suria, 06010,
Jitra Kedah
|
2,465.00
|
|
Encik Hassan Azhar
|
No 67, Kampung Selamat Maju, 16010, Napoh, Kedah
|
2,555.00
|
|
Encik Hisham Mamat
|
No 55, Jalan Likoh 4, Taman Likoh, 06010, Bukit Kayu Hitam Kedah
|
4,210.00
|
|
Puan Zuhanari Samat
|
Lot 333, Kampung Sentol, 06010 Changlun Kedah
|
750.00
|
|
Encik Nizar Mohd. Saad
|
Lot 878, Kampung Tolak, 06010, Changlun Kedah
|
879.20
|
|
Tuan Haji Azmin Rahman
|
No 12, Jalan Indah 2, Taman Bukit Indah, Jitra Kedah
|
9,900.00
|
You are required to:
1.
Use VLOOKUP function to calculate the yearly bonus
of all employee according to the category
2.
Create a new
column to total up the salary and bonus
3.
Find the maximum
bonus received by the employee
4.
Find the minimum
bonus received by the employee
5.
Find the average
bonus received by the employee
6.
Draw a pie chart
that shows the number and percentage of bonus received for each category
** Ensure that your worksheet have ”What If”
analysis that can re-calculate the bonus payment quickly if the company want to
change the condition from RM2000 + the percentage to RM4000 + the percentage
Save the worksheet in the fail named ”BONUS” and write
your name and matric number in the worksheet
Use the following Template:
|
Bil
|
Name
|
Address
|
Salary
|
% Bonus
|
Bonus
|
Salary+Bonus
|
|
1
|
XXX
|
XXX
|
XXX
|
VLOOKUP
|
=FORMULA
|
=Formula
|
|
2
|
XXX
|
XXX
|
XXX
|
VLOOKUP
|
=FORMULA
|
=Formula
|
|
3
|
XXX
|
XXX
|
XXX
|
VLOOKUP
|
=FORMULA
|
=Formula
|
|
4
|
XXX
|
XXX
|
XXX
|
VLOOKUP
|
=FORMULA
|
=Formula
|
|
XXX
|
XXX
|
XXX
|
XXX
|
VLOOKUP
|
=FORMULA
|
=Formula
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
# of Category
|
Main Bonus
|
2000
|
|
|
|
0
|
.75
|
COUNT
|
Maximum Bonus
|
=Max
|
|
|
|
1000
|
.5
|
COUNT
|
Minimum Bonus
|
=Min
|
|
|
|
5000
|
.25
|
COUNT
|
Average Bonus
|
=AVG
|
|
|
Required Items:
1.
The completed worksheet as above
2.
Another worksheet that shows all the
formulas and functions used
3.
Pie Chart diagram
GOOOOOOOD LUCKKK
