Purchase Account

Name Address Product Units Purchase Rate Total Discount Net Pay
Ram Bhadohi Lux - - Pc 200 11 ? ? ?
Shyam Bhadohi Nirma - - Pc 130 10 ? ? ?
Mohan Bhadohi [Link] Lt - - 90 110 ? ? ?
Raju Bhadohi Ref. Lt - - 40 105 ? ? ?
Kishan Bhadohi Wheat - KG - 85 13 ? ? ?
Rajesh Bhadohi Rice - KG - 55 35 ? ? ?
sumit Bhadohi Kurkure - - Pc 250 10 ? ? ?

Formula
Total =Purchase * Rate
= G4*H4

Discount =Total*6%
=I4*6%

Net Pay =Total - Discount

=I4-J4
Name Address Bike Name
Hero Bike
Average Purchase Rate TOTAL ADVANCE DISCOUNT NET PAYMENT
UJJWAL Bhadohi Splendor 60 5 49000 ? 2000 ? ?
SHUBHAM Bhadohi Passion Pro 55 4 55000 ? 4000 ? ?
ADARSH Bhadohi Hunk 40 7 67000 ? 5000 ? ?
SUJAL Bhadohi CD Dulex 65 8 45000 ? 3400 ? ?
PARI Bhadohi CD Down 70 4 43000 ? 0 ? ?
NEHA Bhadohi Splendor Pro 60 5 53800 ? 0 ? ?
KAJAL Bhadohi Passion + 55 6 50000 ? 1500 ? ?

Formula
Total =PURCHASE * RATE
=E4 * F4

Discount =Total*12/100
=G4*12/100

Net Pay =Total - Advance - Discount

=G4 - H4 - I4
 Purchase, Sales With Stock
Name Address Product Purchase P. Rate P. Total Sales S. Rate S. Total Stock Net Profit
Ram Bhadohi Lux 400 10 ? 340 12 ? ? ?
Shyam Bhadohi Nirma 300 8 ? 198 11 ? ? ?
Mohan Bhadohi [Link] 60 105 ? 45 110 ? ? ?
Ankit Bhadohi Ref. 70 101 ? 64 105 ? ? ?
Babu Bhadohi Wheat 250 12 ? 178 15 ? ? ?
Suman Bhadohi Rice 150 17 ? 125 35 ? ? ?
Lata Bhadohi Kurkure 600 9 ? 436 10 ? ? ?

Farmula
Purchase Total = Purchase * [Link]
=D4 * E4

Sales Total = Sales * [Link]

=G4 * H4

Stock =Purchase - Sales

=D4 - G4
 Pass Fail With Results
Name Mark Hindi English Math Science History Art Total % Results Gread
UJJWAL 100 76 56 45 56 68 76 ? ? ? ?
SHUBHAM 100 56 67 89 65 45 67 ? ? ? ?
ADARSH 100 88 76 56 47 34 75 ? ? ? ?
SUJAL 100 58 43 87 65 49 61 ? ? ? ?
PARI 100 45 56 67 67 75 87 ? ? ? ?
NEHA 100 34 45 56 76 37 54 ? ? ? ?
KAJAL 100 49 76 56 45 65 49 ? ? ? ?

Formula
Total 1 =C4+D4+E4+F4+G4+H4 2 =SUM(C4:H4)
3 Select the one Persan Subject Number then Press Alt + =
4 Click On Autosume Icon ∑ Sume
P% 1 =I4/6 2 =I4*100/600
3 =AVERAGE(C4:H4) 4 Click on Autosume Icon Select ∑ Avg
Results 1 =IF(J4>=33,"PASS","FAIL")
2 =IF(AND(AND(AND(AND(AND(C4>=33,D4>=33),E4>=33),F4>=33),G4>=33),H4>=33),
"PASS","FAIL")
Graed 1 =IF(J4>=60,"FIRST DIVISION",IF(J4>=45,"SECOND DIVISION",IF(J4>=33,"THIRD
DIVISION","FAIL")))
2 =IF(AND(J4>=60,K4="PASS"),"FIRST DIVISION",IF(AND(J4>=45,K4="PASS"),"SECOND
DIVISION",IF(AND(J4>=33,K4="PASS"),"THIRD DIVISON","FAIL")))
 Post, Salary, D.A. & Net Salary
Name Address Mb. / Ph. No. Post Grade Salary D.A. Net Salary
AvantikaBhadohi 8081825016 MGR ? ? ? ?
Priti Bhadohi 9918343592 EXE ? ? ? ?
Rubal Bhadohi 9307186206 Clerk ? ? ? ?
Kayra Bhadohi 9415887755 Peon ? ? ? ?
Harish Bhadohi 9452556655 Clerk ? ? ? ?
Aadi Bhadohi 9550050000 EXE ? ? ? ?
PankhudiBhadohi 7800450070 Clerk ? ? ? ?

Formula
Grade: - =IF(D4="MGR","A",IF(D4="EXE","B",IF(D4="CLERK","C","D")))

Salary: - =IF(D4="MGR",30000,IF(D4="EXE",25000,IF(D4="CLERK",20000,10000)))

D.A. : - Dearness Allowance

=IF(E4="A",2500,IF(E4="B",2000,IF(E4="C",1500,1000)))

=IF(AND(D4="MGR",E4="A"),2500,IF(AND(D4="EXE",E4="B"),2000,IF(AND
(D4="CLERK",E4="C"),1500,1000)))

Net Salary: - =F4+G4

 Sales, Commission, Remark
Name Address salary Sales Commission Remark Net Salary
Babu Lucknow 5000 35000 ? ? ?
Mohan Lucknow 3000 18000 ? ? ?
Ankit Lucknow 4000 22000 ? ? ?
Vishal Lucknow 4000 25000 ? ? ?
Raja Lucknow 3000 10000 ? ? ?
Rahul Lucknow 3000 15000 ? ? ?
sumit Lucknow 4000 20000 ? ? ?

FORMULA
Commission =IF(C4>=25000,5000,IF(C4>=20000,3500,2000))

Remark =IF(D4=5000,"[Link]",IF(D4=3500,"GOOD","OK"))

Net Salary =C4+E4

 Salary, HRA, Bonus, PF & Net Salary
Name Address Salary HRA Bonus Medical PF Net Salary
Babu Lucknow 35000 ? ? ? ? ?
Mohan Lucknow 18000 ? ? ? ? ?
Ankit Lucknow 22000 ? ? ? ? ?
Vishal Lucknow 25000 ? ? ? ? ?
Raja Lucknow 10000 ? ? ? ? ?
Rahul Lucknow 15000 ? ? ? ? ?
sumit Lucknow 20000 ? ? ? ? ?

Formula
HRA Home Rent Allowance
=IF(C4>=30000,6000,IF(C4>=22000,4000,2000))

BONUS =IF(C4>=30000,C4*18/100,IF(C4>=22000,C4*16/100,C4*8/100))

MEDICAL =IF(C4>=25000,C4*5%,C4*3%)

PF Provident Found
=IF(C4>=18000,C4*12/100,0)

Net Salary =C4+D4+E4+F4-G4

 Books Library
Name Address Class Subject Issue Date Return DateLate Return Date Fine
Ram Nai Bazar BA ENGLISH 1/1/2013 1/5/2013 1/12/2013 ?
Shyam Bhadohi MA ENGLISH 1/1/2013 1/5/2013 1/12/2013 ?
Mohan Nai Bazar MA MATH 1/3/2013 1/7/2013 2/7/2013 ?
Raju Gyanpur BA HISTORY 1/3/2013 1/7/2013 1/7/2013 ?
Kishan Rajpura BA MATH 1/8/2013 1/12/2013 1/25/2013 ?
Rajesh Bhadohi [Link] Account 1/15/2013 1/18/2013 1/20/2013 ?
sumit Nai Bazar [Link] Statistics 1/20/2013 1/25/2013 1/26/2013 ?

MM/DD/YYYY

FORMULA
Fine =IF(G4>F4,(G4-F4)*5,0)
 Admission Yes / No
Name Address Cast Mark Hindi English Math Science History Art Total P% Results ADM
Seeta SRN Bhd. OBC 100 85 85 44 59 70 90 433 72.16667 ? ?
Geeta SRN Bhd. SC 100 57 57 59 40 53 46 312 52 ? ?
Ram SRN Bhd. ST 100 36 85 85 36 58 39 339 56.5 ? ?
Shyam SRN Bhd. Genral 100 58 86 54 50 72 75 395 65.83333 ? ?
Reena SRN Bhd. OBC 100 36 79 90 62 39 51 357 59.5 ? ?
Meena SRN Bhd. Genral 100 68 57 32 72 76 65 370 61.66667 ? ?
Lucky SRN Bhd. OBC 100 63 83 41 83 67 59 396 66 ? ?
Raju SRN Bhd. SC 100 40 64 39 37 36 81 297 49.5 ? ?

Formula
Total: - =E4+F4+G4+H4+I4+J4 =SUM(E4:J4) ALT + =

P% =AVERAGE(E4:J4) =K4/6
=K4/600*100

Results: - =IF(L4>=33,"PASS","FAIL")
=IF(AND(AND(AND(AND(AND(E4>=33,F4>=33),G4>=33),H4>=33),I4>=33),J4>=33),"PASS","FAIL")

Admission =IF(AND(C4="GENRAL",L4>=65),"YES",IF(AND(C4="OBC",L4>=60),"YES",IF(AND(C4="SC",L4>=
55),"YES",IF(AND(C4="ST",L4>=50),"YES","NO"))))

=IF(AND(OR(C4="GENRAL",L4>=65),M4="PASS"),"YES",IF(AND(OR(C4="OBC",L4>=60),M4="
PASS"),"YES",IF(AND(OR(C4="SC",L4>=55),M4="PASS"),"YES",IF(AND(OR(C4="ST",L4>=50
),M4="PASS"),"YES","NO"))))
 Fee Deposit & Deduction
Name Address Class Gender Shift Time Subject Fee Deposit Deducation
Total Fee
Babu Bhadohi [Link] Male Monthly Morning Commarce ? ? ? ?
Renu Bhadohi [Link] Female Yearly Evening Science ? ? ? ?
Suman Bhadohi B.A. Female Monthly Morning English ? ? ? ?
Aftab Bhadohi [Link] Male Yearly Morning Commarce ? ? ? ?
Harsita Bhadohi [Link] Female Yearly Evening English ? ? ? ?
Aadi Bhadohi [Link] Male Monthly Evening Science ? ? ? ?
Pankhudi Bhadohi B.A. Female Monthly Morning Science ? ? ? ?

Formula
Fee =IF(AND(E4="MONTHLY",G4="ENGLISH"),300,IF(AND(E4="YEARLY",G4="ENGLISH"),3200,IF(AND(E4=
"MONTHLY",G4="COMMARCE"),400,IF(AND(E4="YEARLY",G4="COMMARCE"),4000,IF(AND(E4=
"MONTHLY",G4="SCIENCE"),450,4700)))))

Deposit =IF(G4="SCIENCE",10,IF(G4="COMMARCE",15,20))

Deducation =IF(AND(D4="MALE",F4="MORNING"),8,IF(AND(D4="MALE",F4="EVENING"),15,IF(AND(D4="FEMA
LE",F4="MORNING"),10,20)))

Total Fee =H4-I4-J4

 TDS (Tax Deducted At Source)
Name Address Mobile Salary TDS Amt. Pay Name Tal. [Link] Amt.
Pay Count
Babu Bhadohi 8081825016 4000 10% 3600 Babu 8700 870 3
Shubh Bhadohi 9307186206 3000 10% ? ? ? ? ?
Vijay Varanasi 9451887755 1500 10% ? ? ? ? ?
Soni Varanasi 9452555857 1800 10% ? ? ? ? ?
Babu Bhadohi 8081825016 2200 10% ? ? ? ? ?
Raju Varanasi 9335787897 3100 10% ? ? ? ? ?
Shubh Bhadohi 9307186206 1700 10% ? ? ? ? ?
Babu Bhadohi 8081825016 2500 10% ? ? ? ? ?
Vijay Varanasi 9451887755 3400 10% ? ? ? ? ?

Formula
Amount Pay =(D4)-D4*E4
=D4-D4*E4

Name =A4

Total Salary Amount =SUMIF(A4:A12,G4,D4:D12)

Total TDS Amount =H4*E4

Pay Count =COUNTIF(A4:A12,G4)

 Mobile Call Center
1 2 3 4 5 6 7 8 9 10 11 12
Mobile No. Name Address Issue Date Validity Tarrif Back ToneSMSVoice SMS Net Pk. Night Cht. Balance
8081825016 A Varanasi 1/2/2008 Life Time 19 DDLJ 5 Yes Yes Yes 120.00
8081825025 B Bhadohi 1/16/2011 Two Year 21 KKHH 9 Yes Yes Yes 1.20
8081825034 C Lucknow 1/30/2008 One Year 19 KKKG 5 NO No Yes 4.00
8081825043 D Agra 2/13/2008 Life Time 21 13 NO Yes NO 6.00
8081825052 E Mathura 2/27/2008 Life Time 29 HSSH 27 NO No Yes 8.00
8081825061 F Allahabad 3/12/2008 Two Year 19 Man 27 Yes No NO 34.00
8081825070 G Varanasi 3/26/2008 Two Year 21 5 Yes Yes Yes 56.00
8081825079 H Bhadohi 4/9/2008 Life Time 19 Dhadkan 9 Yes Yes Yes 78.00
8081825088 I Lucknow 4/23/2008 Life Time 21 HAKHN 5 NO NO No 21.00
8081825097 J Agra 5/7/2008 One Year 29 Dil 13 Yes Yes Yes 26.75
8081825106 K Mathura 5/21/2008 Life Time 11 EVAV 27 NO Yes No 34.00

Show Type the Formula

Formula
Balance =VLOOKUP(ANY MOBILE NUM,A4:L15,COLUMN NUM,"FALSE")
TYPE =VLOOKUP(8081825016,A4:L15,12,"FALSE")

Back Tone =VLOOKUP(ANY MOBILE NUM,A4:G15,COLUMN NUM,"FALSE")

TYPE =VLOOKUP(8081825016,A4:G15,7,"FALSE")
 Attendances Register
[Link] Name S/o Mob Fees 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Total Ap. Total Pr.
1 Rahul P
2 Niraj A
3 Khushi P
4 Yash P S S S S
5 Kishan P u H u u u
6 Vikas P n o n n n
7 Raj A d l d d d
8 Anita P a i a a a
9 Vijay A y y y y
10 Mamta
11 Ram
12 Neelam
Total Fees

Set the P & A Color

Rule 1. Home Menu → Conditional Formatting → Manage Rules → New Rule → Format only cells that contain →
Equal to → Type the P / A → Click on Format and Select the Color → ok → ok

Rule 2. Home Menu → Conditional Formatting → Highlight Cells Rules → Equal to → Type the P / A → Color Select
→ ok

Formula
Total Fee =SUM(C4:C15)

Total Percent =COUNTIF(D4:AE4,"P")

Total Accent =COUNTIF(D4:AE4,"A")

 Carpets (Convert in CM to Mt2)
Name of Address Order # Rugs . Design
order Size in CM Mt2 Yard2 Weaving Total Washing Total Finishing Total Total
Buyers of PC Length X Bright Charge Weaving Charge Washing Charge Fineshing Crp. Cast
Donish London HM 0001 H.T. Flower 4 180 X 240 17.28 150 2,592.00 25 432.00 35 604.80 3,628.80
Aafrinz London HM 0002 H.N. Round-2 5 135 X 270 18.23 750 13,668.75 25 455.63 35 637.88 14,762.25
Uronic Dubai HM 0003 H.T. Sufi 6 90 X 130 7.02 300 2,106.00 25 175.50 35 245.70 2,527.20
Josub ` HM 0004 Nepalian Jaquard 7 120 X 180 15.12 850 12,852.00 25 378.00 35 529.20 13,759.20
Nazism Tuarki HM 0005 New YarkFlower 5 60 X 120 3.60 360 1,296.00 25 90.00 35 126.00 1,512.00
Tenveer USA HM 0006 H.T. Sufi 8 240 X 320 61.44 270 16,588.80 25 1,536.00 35 2,150.40 20,275.20

Formula
Mt2 =((G5*I5)/10000)*f5
Or
Mt2 =((Left(g5,3)*Right(I5,3)/10000)*F5
 Carepts in (Fit to Mt2)
Name Rugs . Designorder Fit Total Total Weaving Total Washing Total Finishing Total Total
of Address Order # of Length Brigth Mt2 Yard2 Charge Weaving Charge Washing & PackingFineshingCrp. Cast
X
Buyers PC Fit Inch Fit Inch
Donish London HM 0001 H.T. Flower 4 43 X 69 270 - 20 - 370 - -
Aafrinz London HM 0002 H.N. Round-2 1 5 X 82 195 - 20 - 300 - -
Uronic Dubai HM 0003 H.T. Sufi 5 3 X 5 1800 - 20 - 800 - -
Josub Westernize
HM 0004 Nepalian Jaquard 9 3 X 9 200 - 20 - 195 - -
Nazism Tuarki HM 0005 New YarkFlower 5 5 x 8 450 - 20 - 350 - -
Nayra USA HM 0006 H.T. Sufi 3 3 x 2 850 - 20 - 450 - -
Invoice
 CASH / BILL MEMO

JSS COMPUTER EDUCATION

Hardware & Software Point
SRN Bhadohi Mob. : 09307186206
Bill No. ……………………. D.L. 36/20A/86
Date ………………………… 36/21A/86
jss_2@[Link]
Mr. / Mrs.
…………………………………………………………………………………………………

…………………………………………………………………………………………………
Sr. # Batch No. Particulars Quantity Rate Total
1

Amount In Words: Total

…………………………………………………………………
Signature & Stamp
………………………………………………………………………………….

GOODS ONCE SOLD WILL NOT BE TAKEN BACK