Students Report
Summer 2002
Name Nasir Khan Zubair Baig Mustafa Hussain Bushra Aziz Qasim Nisar Zeshan Ali Yayha Khan Moeen Ahmer Syed Ali Asalm Baig M. Naseem Nadeem Shah Aleem Khan Zahid Ali Part F-I F-I F-II F-I F-II F-I F-II F-II F-II F-I F-I F-II F-II F-I TYPE Coaching Coaching Coaching Coaching Correspondence Coaching Coaching Correspondence Coaching Coaching Coaching Coaching Correspondence Coaching Total Lectures 80 80 70 80 0 80 70 0 70 80 80 70 0 80 Absent 20 22 34 21 0 1 4 0 8 45 3 1 0 0 Present
Summary Report
Total No. of F-I
Total No. of F-II
Allowed
Not Allowed
INSTRUCTIONS
Q1. Type the Above Sheet as given above Q2. Calculate "Present" and "Per% of Attendance" Q3. Calculate Status of Attendance using IF Logical Function: Attendance Per%>=75 "Y" otherwise "N" Q4. Calculate Average of Test1, Test2, Test3 Q5. Calculate Status of Test using IF Logical Function: If the student attended all three tests and got average points >=60, "Y" otherwise "N" Q6. Calculate Status of Presentation Using IF Logical Function: Presentation>1 "Y" otherwise "N" Q7. Calculate Final Status Using IF Logical Function: If the Type of Student =Correspondence, "Allowed" If Status1,Status2,Status3 = "Y", "Allowed" otherwise "Not Allowed" Q8. Calculate Summary Report Using Formulas Q.9 Create a Column Chart of Total No. of F-I, Total No. of F-II, Allowed and Not Allowed
s Report
PER% Status1 Test1 50 67 78 90 0 78 34 0 33 67 56 78 0 67 67 87 0 56 56 0 56 76 53 45 0 0 76 78 0 87 65 78 Test2 88 Test3 46 79 90 89 0 AVG Status2 Presentation 3 2 3 2 0 2 1 0 3 1 3 3 0 2 Status3 Final Status
ummer 2002
National Beverage (Coca Cola)
RATE TABLE
Sale Limit 1 100 200 300 400 600
Emp-Name
Bonus 0 0 0 1000 2000 5000
Region
Remarks Out Bad Poor Fair Good Exlt
Basic Monthly Sale Commission Remarks Good Good Exlt Bad Poor Fair Poor Bad Out
Amir Nadeem Kashif Aslam Noman Ali Babar Nasir Jalal
INSTRUCTIONS Q1 Q2 Q3
E W S E S N S E W
4000 3000 5000 4500 2500 3000 2000 1500 5500
567 400 2000 145 256 345 200 100 15
2000 2000 5000 0 0 1000 0 0 0
Calculate Commission and Remarks using VLOOKUP Function Calculate Total Commission on Basic Make a Pivot Table Report and show only Emp-Name, Region and Total Commission
TOTAL SALARY
6000 5000 10000 4500 2500 4000 2000 1500 5500
INSTITUTE OF COST AND MANAGEMENT ACCOUNTANTS OF P
NAME
BASIC SALARY
GRADE
SEX
LOCATION
HOUSE RENT
MEDICAL
CONV.
Zahida Hamid Benson Ali Dawar Yasmin Ali Nomana Badar Ali Chachar Bushra
3000 2800 2500 4000 4500 4500 4500 3000 2800 3000 2500 4000
17 16 15 19 20 20 20 17 16 17 14 19
F M M M M F M F M M M F
Gulshan Malir Orangi Landhi Steel Town Korangi Gulbarg F.B. Area Clifton Gulshan Nazimabad F.B. Area
300 280 200 400 450 450 450 300 280 300 200 400
Condition:
HOUSE RENT:
Grade>=15, 10% of Basic, Grade>17, 13% of Basic, Grade>19, 20% of Basic of Basic.
MEDICAL:
Grade>=15, 12% of Basic, Grade>17, 15% of Basic, Grade>19, 22% of Basic O of Basic.
CONV.
Grade>=15 and Sex=F and Location is either Korangi, Landhi, Steel Town, 30% Otherwise 20% of Basic, Grade>19, 35% of Basic. 5% of Basic Grade>=19, Manager, Grade>=17, Dep. Manager, else Clerk.
I. TAX: STATUS:
_____________________________________________________________________________ INSTRUCTIONS: Design the Sheet using the above Condition. Develop a Pie Chart between Name and Gross Salary. Paste All Formulas as Text at the very end of this Sheet.
SOLUTION
FOR H. RENT FOR MEDICAL FOR CONV. =IF(C6>19,B6*20%,IF(C6>17,B6*13%,IF(C6>=15,B6*10%,B6*8%))) =IF(C6>19,B6*22%,IF(C6>17,B6*15%,IF(C6>=15,B6*12%,B6*10%)))
=IF(C6>19,B6*35%,IF(AND(C6>=15,D6="F",OR(E6="Korangi",E6="Landhi",E6="Steel Town")
I. TAX NET STATUS
=B6*5% =J6-I6 =IF(C6>=19,"MANAGER",IF(C6>=17,"DEP. MANAGER","CLERK"))
OUNTANTS OF PAKISTAN PAYROLL SHEET
I. TAX GROSS NET STATUS
de>19, 20% of Basic Otherwise 8%
de>19, 22% of Basic Otherwise 10%
ndhi, Steel Town, 30% of Basic
_________________________
Landhi",E6="Steel Town")),B6*30%,B6*20%))
INSTITUTE OF COST AND MANAGEMENT ACCOUNTANTS OF P
NAME
BASIC SALARY
GRADE
SEX
LOCATION
HOUSE RENT
MEDICAL
CONV.
Zahida Hamid Benson Ali Dawar Yasmin Ali Nomana Badar Ali Chachar Bushra
3000 2800 2500 4000 4500 4500 4500 3000 2800 3000 2500 4000
17 16 15 19 20 20 20 17 16 17 14 19
F M M M M F M F M M M F
Gulshan Malir Orangi Landhi Steel Town Korangi Gulbarg F.B. Area Clifton Gulshan Nazimabad F.B. Area
Condition:
HOUSE RENT:
Grade>=15, 10% of Basic, Grade>17, 13% of Basic, Grade>19, 20% of Basic of Basic.
MEDICAL:
Grade>=15, 12% of Basic, Grade>17, 15% of Basic, Grade>19, 22% of Basic O of Basic.
CONV.
Grade>=15 and Sex=F and Location is either Korangi, Landhi, Steel Town, 30% Otherwise 20% of Basic, Grade>19, 35% of Basic. 5% of Basic Grade>=19, Manager, Grade>=17, Dep. Manager, else Clerk.
I. TAX: STATUS:
_____________________________________________________________________________ INSTRUCTIONS: Design the Sheet using the above Condition. Develop a Pie Chart between Name and Gross Salary. Paste All Formulas as Text at the very end of this Sheet.
SOLUTION
FOR H. RENT FOR MEDICAL FOR CONV. =IF(C6>19,B6*20%,IF(C6>17,B6*13%,IF(C6>=15,B6*10%,B6*8%))) =IF(C6>19,B6*22%,IF(C6>17,B6*15%,IF(C6>=15,B6*12%,B6*10%)))
=IF(C6>19,B6*35%,IF(AND(C6>=15,D6="F",OR(E6="Korangi",E6="Landhi",E6="Steel Town")
I. TAX NET STATUS
=B6*5% =J6-I6 =IF(C6>=19,"MANAGER",IF(C6>=17,"DEP. MANAGER","CLERK"))
OUNTANTS OF PAKISTAN PAYROLL SHEET
I. TAX GROSS NET STATUS
de>19, 20% of Basic Otherwise 8%
de>19, 22% of Basic Otherwise 10%
ndhi, Steel Town, 30% of Basic
_________________________
Landhi",E6="Steel Town")),B6*30%,B6*20%))
