{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Worksheet","SYSTEM FORMULAE 2","SYSTEM FORMULAE"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":2,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Worksheet","index":0,"n_rows":37,"n_cols":10,"rows_kept":37,"cut":false,"hidden":false,"merged":[[0,3,0,8],[5,0,5,1],[1,3,1,8],[13,3,13,8],[25,3,25,8]]}
[null,null,null,"Complete these monthly payslips and calculate the net pay"]
[null,null,null,"This is Mohammed Thomson's monthly wage slip.                                                                                                                                                                                                                                                                     •  Mohammed is paid a basic hourly wage of £18.92, has monthly tax allowances of £844, and pays tax at the standard rate of 20%.                                                                                                                                                                                                            •  Mohammed pays 11.9% of his gross monthly pay over £611 towards his National Insurance.                                                                                                                                                                                      •  6.1% of his gross monthly pay also goes into his pension fund.                                                                                                                                                                                      Calculate Mohammed's net monthly pay."]
[null,null,null,"Name","Employee number","Month","Date","Tax Code","NI Number"]
[null,null,null,"Mohammed Thomson",170927,3,"2024-05-30","S844L","MH 89 41 64 A"]
[]
["Variables",null,null,"MONTHLY PAY",null,null,null,"               MONTHLY DEDUCTIONS",null,""]
["SHOW ANSWERS",false,null,null,"Hourly wage","Hours worked","TOTAL",null,"TOTAL",8]
["Calculate tax?",true,null,"Basic hours",18.920000000000002,108,2043.3600000000001,"Tax",395.9620000000001,9]
["Calculate National Insurance?",true,null,"Overtime (time-and-a-quarter)",23.650000000000002,33,780.45,"National Insurance",254.8,10]
["Calculate pension?",true,null,"","","","","Pension",172.25241000000003]
["Calculate commission?",false,null,null,null,"GROSS MONTHLY PAY",2823.8100000000004,"TOTAL DEDUCTIONS",823.01441000000023]
[]
["Calculate overtime?",true,null,null,null,null,null,"NET PAY ",2000.7955900000002]
[null,null,null,"This is Bradley Wood's monthly wage slip.                                                                                                                                                                                                                                                                     •  Bradley is paid a basic hourly wage of £10.80, has monthly tax allowances of £1012, and pays tax at the standard rate of 20%.                                                                                                                                                                                                            •  Bradley pays 10.8% of his gross monthly pay over £683 towards his National Insurance.                                                                                                                                                                                      •  5.8% of his gross monthly pay also goes into his pension fund.                                                                                                                                                                                      Calculate Bradley's net monthly pay."]
[null,null,null,"Name","Employee number","Month","Date","Tax Code","NI Number"]
[null,null,null,"Bradley Wood",8787,5,"2024-07-05","S1012L","SO 86 81 91 D"]
[]
[null,null,null,"MONTHLY PAY",null,null,null,"               MONTHLY DEDUCTIONS",null,21]
[null,null,null,null,"Hourly wage","Hours worked","TOTAL",null,"TOTAL",22]
[null,null,null,"Basic hours",10.8,89,961.2,"Tax",132.4,20]
[null,null,null,"Overtime (time-and-a-half)",16.200000000000003,14,226.80000000000004,"National Insurance",150.6,21]
[null,null,null,"Overtime (time-and-a-quarter)",13.5,36,486,"Pension",97.091999999999985]
[null,null,null,null,null,"GROSS MONTHLY PAY",1674,"TOTAL DEDUCTIONS",380.09199999999998]
[]
[null,null,null,null,null,null,null,"NET PAY ",1293.9079999999999]
[null,null,null,"This is Danielle Jones's monthly wage slip.                                                                                                                                                                                                                                                                     •  Danielle is paid a basic hourly wage of £16.52, has monthly tax allowances of £1165, and pays tax at the standard rate of 20%.                                                                                                                                                                                                            •  Danielle pays 10.9% of her gross monthly pay over £531 towards her National Insurance.                                                                                                                                                                                      •  6.8% of her gross monthly pay also goes into her pension fund.                                                                                                                                                                                      Calculate Danielle's net monthly pay."]
[null,null,null,"Name","Employee number","Month","Date","Tax Code","NI Number"]
[null,null,null,"Danielle Jones",403179,3,"2024-05-27","S1165L","ZY 58 75 76 B"]
[]
[null,null,null,"MONTHLY PAY",null,null,null,"               MONTHLY DEDUCTIONS",null,""]
[null,null,null,null,"Hourly wage","Hours worked","TOTAL",null,"TOTAL",33]
[null,null,null,"Basic hours",16.52,89,1470.28,"Tax",371.63200000000001,32]
[null,null,null,"Overtime (double time)",33.04,47,1552.8799999999999,"National Insurance",272.10000000000002,33]
[null,null,null,"","","","","Pension",205.57487999999998]
[null,null,null,null,null,"GROSS MONTHLY PAY",3023.16,"TOTAL DEDUCTIONS",849.30687999999998]
[]
[null,null,null,null,null,null,null,"NET PAY ",2173.8531199999998]

{"sheet":"SYSTEM FORMULAE 2","index":1,"n_rows":37,"n_cols":10,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,{"$f":"CONCATENATE(\"This is \",A4,\" \",A5,\"'s monthly wage slip.                                                                                                                                                                                                                                          \",\"                           •  \",A4,\" is paid a basic hourly wage of £\",TEXT(E8,\"#.00\"),IF(Worksheet!$B$8,CONCATENATE(\", has monthly tax allowances of £\",VALUE(LEFT(RIGHT(H4,LEN(H4)-1),LEN(RIGHT(H4,LEN(H4)-1))-1)),\", and pays tax at the standard rate of 20%.                                                                                                                                                                                                            \"),\".                                                                                                                                                                                                       \"),CONCATENATE(IF(Worksheet!$B$11,CONCATENATE(\"•  \",A4,\" is also paid \",A6,\"% commission on \",IF(B4=\"M\",\"his\",IF(B4=\"F\",\"her\",\"their\")),\" sales.                                                                                                                                                                                                                      \"),\"\")),CONCATENATE(IF(Worksheet!$B$9,CONCATENATE(\"•  \",A4,\" pays \",B11,\"% of \",IF(B4=\"M\",\"his\",IF(B4=\"F\",\"her\",\"their\")),\" gross monthly pay over £\",B10,\" towards \",IF(B4=\"M\",\"his\",IF(B4=\"F\",\"her\",\"their\")),\" National Insurance.                                                                                                                                                                                      \"),\"\")),CONCATENATE(IF(Worksheet!$B$10,CONCATENATE(\"•  \",A11,\"% of \",IF(B4=\"M\",\"his\",IF(B4=\"F\",\"her\",\"their\")),\" gross monthly pay also goes into \",IF(B4=\"M\",\"his\",IF(B4=\"F\",\"her\",\"their\")),\" pension fund.                                                                                                                                                                                      \"),\"\")),CONCATENATE(\"Calculate \",IF(Worksheet!$B$11,\"the\",CONCATENATE(A4,\"'s\")),\" net monthly pay\",IF(Worksheet!$B$11,CONCATENATE(\" in a month where \",A4,\"'s sales are £\",A7,\".\"),\".\")))","v":"This is Henry Stewart's monthly wage slip.                                                                                                                                                                                                                                                                     •  Henry is paid a basic hourly wage of £10.86, has monthly tax allowances of £1391, and pays tax at the standard rate of 20%.                                                                                                                                                                                                            •  Henry pays 6.1% of his gross monthly pay over £528 towards his National Insurance.                                                                                                                                                                                      •  5% of his gross monthly pay also goes into his pension fund.                                                                                                                                                                                      Calculate Henry's net monthly pay."}]
[{"$f":"RANDBETWEEN(1,200)","v":56},null,null,"Name","Employee number","Month","Date","Tax Code","NI Number"]
[{"$f":"INDEX('SYSTEM FORMULAE'!A$2:A$201,A3)","v":"Henry"},{"$f":"INDEX('SYSTEM FORMULAE'!B$2:B$201,A3)","v":"M"},null,{"$f":"CONCATENATE(A4,\" \",A5)","v":"Henry Stewart"},{"$f":"IF(RAND()<0.3,RANDBETWEEN(1,99),IF(RAND()<0.35,RANDBETWEEN(100,9999),RANDBETWEEN(10000,999999)))","v":267479},{"$f":"MOD(MONTH(G4)+9,12)+1","v":2},{"$f":"TODAY()+RANDBETWEEN(-50,50)","v":"2024-04-29"},{"$f":"CONCATENATE(\"S\",RANDBETWEEN(800,1400),\"L\")","v":"S1391L"},{"$f":"CONCATENATE(CHOOSE(RANDBETWEEN(1,20),\"A\",\"B\",\"C\",\"E\",\"G\",\"H\",\"J\",\"K\",\"L\",\"M\",\"N\",\"O\",\"P\",\"R\",\"S\",\"T\",\"W\",\"X\",\"Y\",\"Z\"),CHOOSE(RANDBETWEEN(1,20),\"A\",\"B\",\"C\",\"E\",\"G\",\"H\",\"J\",\"K\",\"L\",\"M\",\"N\",\"O\",\"P\",\"R\",\"S\",\"T\",\"W\",\"X\",\"Y\",\"Z\"),\" \",RANDBETWEEN(1,9),RANDBETWEEN(1,9),\" \",RANDBETWEEN(1,9),RANDBETWEEN(1,9),\" \",RANDBETWEEN(1,9),RANDBETWEEN(1,9),\" \",CHOOSE(RANDBETWEEN(1,4),\"A\",\"B\",\"C\",\"D\"))","v":"JA 93 22 25 B"}]
[{"$f":"INDEX('SYSTEM FORMULAE'!C$2:C$51,RANDBETWEEN(1,50))","v":"Stewart"}]
[{"$f":"RANDBETWEEN(7,15)/2","v":6.5},null,null,"MONTHLY PAY",null,null,null,"               MONTHLY DEDUCTIONS",null,{"$f":"IF(D10=\"\",\"\",3*ROW(G8)+3-J7-J8)","v":""}]
[{"$f":"(RANDBETWEEN(12,100)*10000+RANDBETWEEN(1,9)*1000)/10","v":65400},null,null,null,"Hourly wage","Hours worked","TOTAL",null,"TOTAL",{"$f":"IF(D10=\"\",RANDBETWEEN(ROW(G8),ROW(G8)+1),RANDBETWEEN(ROW(G8),ROW(G8)+2))","v":8}]
[null,null,null,"Basic hours",{"$f":"RANDBETWEEN(430,1200)/50","v":10.86},{"$f":"RANDBETWEEN(40,140)","v":112},{"$f":"E8*F8","v":1216.32},"Tax",{"$f":"(G11-VALUE(LEFT(RIGHT(H4,LEN(H4)-1),LEN(RIGHT(H4,LEN(H4)-1))-1)))*0.2","v":118.18999999999997},{"$f":"IF(D10=\"\",IF(J7=ROW(G8),ROW(G8)+1,ROW(G8)),IF(J7=ROW(G8),RANDBETWEEN(ROW(G8)+1,ROW(G8)+2),IF(J7=ROW(G8)+2,RANDBETWEEN(ROW(G8),ROW(G8)+1),CHOOSE(RANDBETWEEN(1,2),ROW(G8),ROW(G8)+2))))","v":9}]
[{"$f":"RANDBETWEEN(1,IF(RAND()<0.3,3,2))","v":2},null,null,{"$f":"CHOOSE(A9,\"Overtime (double time)\",\"Overtime (time-and-a-half)\",\"Overtime (time-and-a-quarter)\")","v":"Overtime (time-and-a-half)"},{"$f":"CHOOSE(A9,2,1.5,1.25)*E8","v":16.29},{"$f":"RANDBETWEEN(4,50)","v":47},{"$f":"E9*F9","v":765.63},"National Insurance",{"$f":"INT(0.09*G11)+0.1*RANDBETWEEN(0,9)","v":178.7},{"$f":"RANDBETWEEN(ROW(G8),ROW(G8)+2)","v":9}]
[{"$f":"IF(A9=1,RANDBETWEEN(2,3),IF(A9=3,RANDBETWEEN(1,2),CHOOSE(RANDBETWEEN(1,2),1,3)))","v":3},{"$f":"RANDBETWEEN(500,VALUE(LEFT(RIGHT(H4,LEN(H4)-1),LEN(RIGHT(H4,LEN(H4)-1))-1)))","v":528},null,{"$f":"IF(Worksheet!$B$11,\"Commission\",IF(AND(RAND()<0.5,Worksheet!$B$13),CHOOSE(A10,\"Overtime (double time)\",\"Overtime (time-and-a-half)\",\"Overtime (time-and-a-quarter)\"),\"\"))","v":""},{"$f":"IF(LEFT(D10,4)=\"Comm\",\"-\",IF(D10=\"\",\"\",CHOOSE(A10,2,1.5,1.25)*E8))","v":""},{"$f":"IF(LEFT(D10,4)=\"Comm\",\"-\",IF(D10=\"\",\"\",RANDBETWEEN(4,50)))","v":""},{"$f":"IF(D10=\"\",\"\",IF(LEFT(D10,4)=\"Comm\",A6/100*A7,E10*F10))","v":""},"Pension",{"$f":"G11*A11/100","v":99.097499999999997}]
[{"$f":"RANDBETWEEN(30,70)/10","v":5},{"$f":"RANDBETWEEN(60,120)/10","v":6.1},null,null,null,"GROSS MONTHLY PAY",{"$f":"SUM(G8:G10)","v":1981.9499999999998},"TOTAL DEDUCTIONS",{"$f":"SUM(I8:I10)","v":395.98749999999995}]
[]
[null,null,null,null,null,null,null,"NET PAY ",{"$f":"G11-I11","v":1585.9624999999999}]
[null,null,null,{"$f":"CONCATENATE(\"This is \",A16,\" \",A17,\"'s monthly wage slip.                                                                                                                                                                                                                                          \",\"                           •  \",A16,\" is paid a basic hourly wage of £\",TEXT(E20,\"#.00\"),IF(Worksheet!$B$8,CONCATENATE(\", has monthly tax allowances of £\",VALUE(LEFT(RIGHT(H16,LEN(H16)-1),LEN(RIGHT(H16,LEN(H16)-1))-1)),\", and pays tax at the standard rate of 20%.                                                                                                                                                                                                            \"),\".                                                                                                                                                                                                       \"),CONCATENATE(IF(Worksheet!$B$11,CONCATENATE(\"•  \",A16,\" is also paid \",A18,\"% commission on \",IF(B16=\"M\",\"his\",IF(B16=\"F\",\"her\",\"their\")),\" sales.                                                                                                                                                                                                                      \"),\"\")),CONCATENATE(IF(Worksheet!$B$9,CONCATENATE(\"•  \",A16,\" pays \",B23,\"% of \",IF(B16=\"M\",\"his\",IF(B16=\"F\",\"her\",\"their\")),\" gross monthly pay over £\",B22,\" towards \",IF(B16=\"M\",\"his\",IF(B16=\"F\",\"her\",\"their\")),\" National Insurance.                                                                                                                                                                                      \"),\"\")),CONCATENATE(IF(Worksheet!$B$10,CONCATENATE(\"•  \",A23,\"% of \",IF(B16=\"M\",\"his\",IF(B16=\"F\",\"her\",\"their\")),\" gross monthly pay also goes into \",IF(B16=\"M\",\"his\",IF(B16=\"F\",\"her\",\"their\")),\" pension fund.                                                                                                                                                                                      \"),\"\")),CONCATENATE(\"Calculate \",IF(Worksheet!$B$11,\"the\",CONCATENATE(A16,\"'s\")),\" net monthly pay\",IF(Worksheet!$B$11,CONCATENATE(\" in a month where \",A16,\"'s sales are £\",A19,\".\"),\".\")))","v":"This is Mia Clark's monthly wage slip.                                                                                                                                                                                                                                                                     •  Mia is paid a basic hourly wage of £16.54, has monthly tax allowances of £1077, and pays tax at the standard rate of 20%.                                                                                                                                                                                                            •  Mia pays 11.7% of her gross monthly pay over £1012 towards her National Insurance.                                                                                                                                                                                      •  6.5% of her gross monthly pay also goes into her pension fund.                                                                                                                                                                                      Calculate Mia's net monthly pay."}]
[{"$f":"RANDBETWEEN(1,200)","v":125},null,null,"Name","Employee number","Month","Date","Tax Code","NI Number"]
[{"$f":"INDEX('SYSTEM FORMULAE'!A$2:A$201,A15)","v":"Mia"},{"$f":"INDEX('SYSTEM FORMULAE'!B$2:B$201,A15)","v":"F"},null,{"$f":"CONCATENATE(A16,\" \",A17)","v":"Mia Clark"},{"$f":"IF(RAND()<0.3,RANDBETWEEN(1,99),IF(RAND()<0.35,RANDBETWEEN(100,9999),RANDBETWEEN(10000,999999)))","v":9229},{"$f":"MOD(MONTH(G16)+9,12)+1","v":4},{"$f":"TODAY()+RANDBETWEEN(-50,50)","v":"2024-06-22"},{"$f":"CONCATENATE(\"S\",RANDBETWEEN(800,1400),\"L\")","v":"S1077L"},{"$f":"CONCATENATE(CHOOSE(RANDBETWEEN(1,20),\"A\",\"B\",\"C\",\"E\",\"G\",\"H\",\"J\",\"K\",\"L\",\"M\",\"N\",\"O\",\"P\",\"R\",\"S\",\"T\",\"W\",\"X\",\"Y\",\"Z\"),CHOOSE(RANDBETWEEN(1,20),\"A\",\"B\",\"C\",\"E\",\"G\",\"H\",\"J\",\"K\",\"L\",\"M\",\"N\",\"O\",\"P\",\"R\",\"S\",\"T\",\"W\",\"X\",\"Y\",\"Z\"),\" \",RANDBETWEEN(1,9),RANDBETWEEN(1,9),\" \",RANDBETWEEN(1,9),RANDBETWEEN(1,9),\" \",RANDBETWEEN(1,9),RANDBETWEEN(1,9),\" \",CHOOSE(RANDBETWEEN(1,4),\"A\",\"B\",\"C\",\"D\"))","v":"RW 82 92 51 B"}]
[{"$f":"INDEX('SYSTEM FORMULAE'!C$2:C$51,RANDBETWEEN(1,50))","v":"Clark"}]
[{"$f":"RANDBETWEEN(7,15)/2","v":4},null,null,"MONTHLY PAY",null,null,null,"               MONTHLY DEDUCTIONS",null,{"$f":"IF(D22=\"\",\"\",3*ROW(G20)+3-J19-J20)","v":21}]
[{"$f":"(RANDBETWEEN(12,100)*10000+RANDBETWEEN(1,9)*1000)/10","v":61800},null,null,null,"Hourly wage","Hours worked","TOTAL",null,"TOTAL",{"$f":"IF(D22=\"\",RANDBETWEEN(ROW(G20),ROW(G20)+1),RANDBETWEEN(ROW(G20),ROW(G20)+2))","v":20}]
[null,null,null,"Basic hours",{"$f":"RANDBETWEEN(430,1200)/50","v":16.54},{"$f":"RANDBETWEEN(40,140)","v":41},{"$f":"E20*F20","v":678.14},"Tax",{"$f":"(G23-VALUE(LEFT(RIGHT(H16,LEN(H16)-1),LEN(RIGHT(H16,LEN(H16)-1))-1)))*0.2","v":65.780000000000015},{"$f":"IF(D22=\"\",IF(J19=ROW(G20),ROW(G20)+1,ROW(G20)),IF(J19=ROW(G20),RANDBETWEEN(ROW(G20)+1,ROW(G20)+2),IF(J19=ROW(G20)+2,RANDBETWEEN(ROW(G20),ROW(G20)+1),CHOOSE(RANDBETWEEN(1,2),ROW(G20),ROW(G20)+2))))","v":22}]
[{"$f":"RANDBETWEEN(1,IF(RAND()<0.3,3,2))","v":2},null,null,{"$f":"CHOOSE(A21,\"Overtime (double time)\",\"Overtime (time-and-a-half)\",\"Overtime (time-and-a-quarter)\")","v":"Overtime (time-and-a-half)"},{"$f":"CHOOSE(A21,2,1.5,1.25)*E20","v":24.81},{"$f":"RANDBETWEEN(4,50)","v":21},{"$f":"E21*F21","v":521.01},"National Insurance",{"$f":"INT(0.09*G23)+0.1*RANDBETWEEN(0,9)","v":126.6},{"$f":"RANDBETWEEN(ROW(G20),ROW(G20)+2)","v":20}]
[{"$f":"IF(A21=1,RANDBETWEEN(2,3),IF(A21=3,RANDBETWEEN(1,2),CHOOSE(RANDBETWEEN(1,2),1,3)))","v":3},{"$f":"RANDBETWEEN(500,VALUE(LEFT(RIGHT(H16,LEN(H16)-1),LEN(RIGHT(H16,LEN(H16)-1))-1)))","v":1012},null,{"$f":"IF(Worksheet!$B$11,\"Commission\",IF(AND(RAND()<0.5,Worksheet!$B$13),CHOOSE(A22,\"Overtime (double time)\",\"Overtime (time-and-a-half)\",\"Overtime (time-and-a-quarter)\"),\"\"))","v":"Overtime (time-and-a-quarter)"},{"$f":"IF(LEFT(D22,4)=\"Comm\",\"-\",IF(D22=\"\",\"\",CHOOSE(A22,2,1.5,1.25)*E20))","v":20.674999999999997},{"$f":"IF(LEFT(D22,4)=\"Comm\",\"-\",IF(D22=\"\",\"\",RANDBETWEEN(4,50)))","v":10},{"$f":"IF(D22=\"\",\"\",IF(LEFT(D22,4)=\"Comm\",A18/100*A19,E22*F22))","v":206.74999999999997},"Pension",{"$f":"G23*A23/100","v":91.383499999999998}]
[{"$f":"RANDBETWEEN(30,70)/10","v":6.5},{"$f":"RANDBETWEEN(60,120)/10","v":11.7},null,null,null,"GROSS MONTHLY PAY",{"$f":"SUM(G20:G22)","v":1405.9},"TOTAL DEDUCTIONS",{"$f":"SUM(I20:I22)","v":283.76350000000002}]
[]
[null,null,null,null,null,null,null,"NET PAY ",{"$f":"G23-I23","v":1122.1365000000001}]
[null,null,null,{"$f":"CONCATENATE(\"This is \",A28,\" \",A29,\"'s monthly wage slip.                                                                                                                                                                                                                                          \",\"                           •  \",A28,\" is paid a basic hourly wage of £\",TEXT(E32,\"#.00\"),IF(Worksheet!$B$8,CONCATENATE(\", has monthly tax allowances of £\",VALUE(LEFT(RIGHT(H28,LEN(H28)-1),LEN(RIGHT(H28,LEN(H28)-1))-1)),\", and pays tax at the standard rate of 20%.                                                                                                                                                                                                            \"),\".                                                                                                                                                                                                       \"),CONCATENATE(IF(Worksheet!$B$11,CONCATENATE(\"•  \",A28,\" is also paid \",A30,\"% commission on \",IF(B28=\"M\",\"his\",IF(B28=\"F\",\"her\",\"their\")),\" sales.                                                                                                                                                                                                                      \"),\"\")),CONCATENATE(IF(Worksheet!$B$9,CONCATENATE(\"•  \",A28,\" pays \",B35,\"% of \",IF(B28=\"M\",\"his\",IF(B28=\"F\",\"her\",\"their\")),\" gross monthly pay over £\",B34,\" towards \",IF(B28=\"M\",\"his\",IF(B28=\"F\",\"her\",\"their\")),\" National Insurance.                                                                                                                                                                                      \"),\"\")),CONCATENATE(IF(Worksheet!$B$10,CONCATENATE(\"•  \",A35,\"% of \",IF(B28=\"M\",\"his\",IF(B28=\"F\",\"her\",\"their\")),\" gross monthly pay also goes into \",IF(B28=\"M\",\"his\",IF(B28=\"F\",\"her\",\"their\")),\" pension fund.                                                                                                                                                                                      \"),\"\")),CONCATENATE(\"Calculate \",IF(Worksheet!$B$11,\"the\",CONCATENATE(A28,\"'s\")),\" net monthly pay\",IF(Worksheet!$B$11,CONCATENATE(\" in a month where \",A28,\"'s sales are £\",A31,\".\"),\".\")))","v":"This is Charlie Hughes's monthly wage slip.                                                                                                                                                                                                                                                                     •  Charlie is paid a basic hourly wage of £17.78, has monthly tax allowances of £920, and pays tax at the standard rate of 20%.                                                                                                                                                                                                            •  Charlie pays 11.5% of his gross monthly pay over £683 towards his National Insurance.                                                                                                                                                                                      •  6.3% of his gross monthly pay also goes into his pension fund.                                                                                                                                                                                      Calculate Charlie's net monthly pay."}]
[{"$f":"RANDBETWEEN(1,200)","v":35},null,null,"Name","Employee number","Month","Date","Tax Code","NI Number"]
[{"$f":"INDEX('SYSTEM FORMULAE'!A$2:A$201,A27)","v":"Charlie"},{"$f":"INDEX('SYSTEM FORMULAE'!B$2:B$201,A27)","v":"M"},null,{"$f":"CONCATENATE(A28,\" \",A29)","v":"Charlie Hughes"},{"$f":"IF(RAND()<0.3,RANDBETWEEN(1,99),IF(RAND()<0.35,RANDBETWEEN(100,9999),RANDBETWEEN(10000,999999)))","v":848870},{"$f":"MOD(MONTH(G28)+9,12)+1","v":4},{"$f":"TODAY()+RANDBETWEEN(-50,50)","v":"2024-06-11"},{"$f":"CONCATENATE(\"S\",RANDBETWEEN(800,1400),\"L\")","v":"S920L"},{"$f":"CONCATENATE(CHOOSE(RANDBETWEEN(1,20),\"A\",\"B\",\"C\",\"E\",\"G\",\"H\",\"J\",\"K\",\"L\",\"M\",\"N\",\"O\",\"P\",\"R\",\"S\",\"T\",\"W\",\"X\",\"Y\",\"Z\"),CHOOSE(RANDBETWEEN(1,20),\"A\",\"B\",\"C\",\"E\",\"G\",\"H\",\"J\",\"K\",\"L\",\"M\",\"N\",\"O\",\"P\",\"R\",\"S\",\"T\",\"W\",\"X\",\"Y\",\"Z\"),\" \",RANDBETWEEN(1,9),RANDBETWEEN(1,9),\" \",RANDBETWEEN(1,9),RANDBETWEEN(1,9),\" \",RANDBETWEEN(1,9),RANDBETWEEN(1,9),\" \",CHOOSE(RANDBETWEEN(1,4),\"A\",\"B\",\"C\",\"D\"))","v":"LL 25 61 42 B"}]
[{"$f":"INDEX('SYSTEM FORMULAE'!C$2:C$51,RANDBETWEEN(1,50))","v":"Hughes"}]
[{"$f":"RANDBETWEEN(7,15)/2","v":7},null,null,"MONTHLY PAY",null,null,null,"               MONTHLY DEDUCTIONS",null,{"$f":"IF(D34=\"\",\"\",3*ROW(G32)+3-J31-J32)","v":""}]
[{"$f":"(RANDBETWEEN(12,100)*10000+RANDBETWEEN(1,9)*1000)/10","v":97900},null,null,null,"Hourly wage","Hours worked","TOTAL",null,"TOTAL",{"$f":"IF(D34=\"\",RANDBETWEEN(ROW(G32),ROW(G32)+1),RANDBETWEEN(ROW(G32),ROW(G32)+2))","v":32}]
[null,null,null,"Basic hours",{"$f":"RANDBETWEEN(430,1200)/50","v":17.78},{"$f":"RANDBETWEEN(40,140)","v":76},{"$f":"E32*F32","v":1351.2800000000002},"Tax",{"$f":"(G35-VALUE(LEFT(RIGHT(H28,LEN(H28)-1),LEN(RIGHT(H28,LEN(H28)-1))-1)))*0.2","v":342.28800000000012},{"$f":"IF(D34=\"\",IF(J31=ROW(G32),ROW(G32)+1,ROW(G32)),IF(J31=ROW(G32),RANDBETWEEN(ROW(G32)+1,ROW(G32)+2),IF(J31=ROW(G32)+2,RANDBETWEEN(ROW(G32),ROW(G32)+1),CHOOSE(RANDBETWEEN(1,2),ROW(G32),ROW(G32)+2))))","v":33}]
[{"$f":"RANDBETWEEN(1,IF(RAND()<0.3,3,2))","v":1},null,null,{"$f":"CHOOSE(A33,\"Overtime (double time)\",\"Overtime (time-and-a-half)\",\"Overtime (time-and-a-quarter)\")","v":"Overtime (double time)"},{"$f":"CHOOSE(A33,2,1.5,1.25)*E32","v":35.56},{"$f":"RANDBETWEEN(4,50)","v":36},{"$f":"E33*F33","v":1280.1600000000001},"National Insurance",{"$f":"INT(0.09*G35)+0.1*RANDBETWEEN(0,9)","v":236.3},{"$f":"RANDBETWEEN(ROW(G32),ROW(G32)+2)","v":32}]
[{"$f":"IF(A33=1,RANDBETWEEN(2,3),IF(A33=3,RANDBETWEEN(1,2),CHOOSE(RANDBETWEEN(1,2),1,3)))","v":2},{"$f":"RANDBETWEEN(500,VALUE(LEFT(RIGHT(H28,LEN(H28)-1),LEN(RIGHT(H28,LEN(H28)-1))-1)))","v":683},null,{"$f":"IF(Worksheet!$B$11,\"Commission\",IF(AND(RAND()<0.5,Worksheet!$B$13),CHOOSE(A34,\"Overtime (double time)\",\"Overtime (time-and-a-half)\",\"Overtime (time-and-a-quarter)\"),\"\"))","v":""},{"$f":"IF(LEFT(D34,4)=\"Comm\",\"-\",IF(D34=\"\",\"\",CHOOSE(A34,2,1.5,1.25)*E32))","v":""},{"$f":"IF(LEFT(D34,4)=\"Comm\",\"-\",IF(D34=\"\",\"\",RANDBETWEEN(4,50)))","v":""},{"$f":"IF(D34=\"\",\"\",IF(LEFT(D34,4)=\"Comm\",A30/100*A31,E34*F34))","v":""},"Pension",{"$f":"G35*A35/100","v":165.78072000000003}]
[{"$f":"RANDBETWEEN(30,70)/10","v":6.3},{"$f":"RANDBETWEEN(60,120)/10","v":11.5},null,null,null,"GROSS MONTHLY PAY",{"$f":"SUM(G32:G34)","v":2631.4400000000005},"TOTAL DEDUCTIONS",{"$f":"SUM(I32:I34)","v":744.36872000000017}]
[]
[null,null,null,null,null,null,null,"NET PAY ",{"$f":"G35-I35","v":1887.0712800000003}]

{"sheet":"SYSTEM FORMULAE","index":2,"n_rows":201,"n_cols":3,"rows_kept":201,"cut":false,"hidden":false,"merged":[]}
["Names","1998 baby names - http://www.statistics.gov.uk/statbase/Product.asp?vlnk=15282"]
["Jack",{"$f":"IF(RAND()<0.97,\"M\",\"N\")","v":"M"},"Smith"]
["Thomas",{"$f":"IF(RAND()<0.97,\"M\",\"N\")","v":"M"},"Wilson"]
["James","M","Jones"]
["Daniel","M","Campbell"]
["Josh","M","Williams"]
["Matthew","M","Brown"]
["Samuel","M","Taylor"]
["Callum","M","Kelly"]
["Joseph","M","Davies"]
["Jordan","M","Johnston"]
["Connor","M","Evans"]
["Ryan","M","Robertson"]
["Luke","M","Moore"]
["William","M","Thomas"]
["Harry","M","Thomson"]
["Ben","M","Roberts"]
["George","M","Johnson"]
["Lewis","M","Smyth"]
["Alexander","M","Lewis"]
["Oliver","M","Stewart"]
["Adam","M","Anderson"]
["Jake","M","Hughes"]
["Michael","M","Scott"]
["Liam","M","Wright"]
["Nathan","M","Docherty"]
["Kieran","M","Murray"]
["Jacob","M","MacDonald"]
["Cameron","M","Reid"]
["Ben","M","Taylor"]
["Mohammed","M","Clark"]
["Aaron","M","Ross"]
["Christopher","M","Young"]
["Bradley","M","Mitchell"]
["Jamie","M","Watson"]
["Charlie","M","Paterson"]
["Brandon","M","Morrison"]
["Robert","M","Jackson"]
["Kyle","M","Wood"]
["David","M","Hall"]
["Owen","M","Green"]
["Charles","M","White"]
["Andrew","M","Walker"]
["Reece","M","Carlyle"]
["Edward","N","Forbes"]
["Dylan","M","Glen"]
["Alex","M","Davidson"]
["Ethan","M","McDonald"]
["Jonathan","M","McGrath"]
["Sam","M","MacInnes"]
["Louis","M","Roberts"]
["Max","M","Cook"]
["Dominic","M"]
["John","M"]
["Ashley","M"]
["Rhys","M"]
["Henry","M"]
["Conor","M"]
["Joe","M"]
["Tyler","M"]
["Scott","M"]
["Sean","M"]
["Nick","M"]
["Ross","M"]
["Morgan","M"]
["Elliot","M"]
["Patrick",{"$f":"IF(RAND()<0.97,\"M\",\"N\")","v":"M"}]
["Peter","M"]
["Jo","N"]
["Mark","M"]
["Stephen","M"]
["Alfie","M"]
["Harrison","M"]
["Lee","M"]
["Anthony","M"]
["Mitchell","M"]
["Joel","M"]
["Toby","M"]
["Paul","M"]
["Jay","M"]
["Jason","M"]
["Aidan","M"]
["Richard","M"]
["Mohammad","M"]
["Isaac","M"]
["Declan","M"]
["Josh","M"]
["Steven","M"]
["Marcus","M"]
["Muhammad","M"]
["Elliott","M"]
["Harvey","M"]
["Tom","M"]
["Christian","M"]
["Kane","M"]
["Shane","M"]
["Leon","M"]
["Shaun","M"]
["Corey","N"]
["Ellis","M"]
["Kai","N"]
["Natalie",{"$f":"IF(RAND()<0.97,\"F\",\"N\")","v":"F"}]
["Abbie",{"$f":"IF(RAND()<0.97,\"F\",\"N\")","v":"F"}]
["Francesca","F"]
["Melissa","F"]
["Katherine","F"]
["Harriet","F"]
["Imogen","F"]
["Jodie","F"]
["Gemma","N"]
["Kayleigh","F"]
["Lily","F"]
["Jennifer","F"]
["Kate","F"]
["Stephanie","F"]
["Daisy","F"]
["Niamh","F"]
["Millie","F"]
["Aimee","F"]
["Kirsty","F"]
["Hollie","F"]
["Rosie","F"]
["Jordan","F"]
["Mollie","F"]
["Alisha","F"]
["Mia","F"]
["Isobel","F"]
["Tia","F"]
["Ellen","F"]
["Erin","F"]
["Bethan","F"]
["Sian","F"]
["Alicia","F"]
["Rhiannon","F"]
["Ashleigh","F"]
["Catherine","F"]
["Madeleine","F"]
["Isabelle","F"]
["Kelsey","F"]
["Sophia","F"]
["Isabella","F"]
["Naomi","F"]
["Yasmin","F"]
["Freya","F"]
["Harper","N"]
["Robyn","F"]
["Isabel","F"]
["Maisie","F"]
["Poppy","F"]
["Rachael","F"]
["Chantelle","F"]
["Chloe","F"]
["Emily","F"]
["Megan","F"]
["Jessica","F"]
["Sophie","F"]
["Charlotte","F"]
["Hannah","F"]
["Lauren","F"]
["Rebecca","F"]
["Lucy","F"]
["Amy","F"]
["Georgia","F"]
["Katie","F"]
["Bethany","F"]
["Emma","F"]
["Olivia",{"$f":"IF(RAND()<0.97,\"F\",\"N\")","v":"F"}]
["Courtney","F"]
["Shannon","F"]
["Eleanor","F"]
["Jade","F"]
["Ellie","F"]
["Abigail","F"]
["Holly","F"]
["Molly","F"]
["Laura","F"]
["Alice","F"]
["Sarah","F"]
["Caitlin","F"]
["Rachel","F"]
["Elizabeth","F"]
["Amber","F"]
["Paige","F"]
["Georgina","F"]
["Danielle","F"]
["Grace","F"]
["Nicole","F"]
["Natasha","F"]
["Priya","F"]
["Chelsea","F"]
["Leah","F"]
["Victoria","F"]
["Anna","F"]
["Phoebe","F"]
["Samantha","F"]
["Zoe","F"]
["Alexandra","F"]
["Jasmine","F"]
["Amelia","F"]
["Louise","F"]
["Keisha","F"]