A B C D E F G H J K 1 Pre planned Coordination 2 3 The below lines are shifts undertaken by Coordination for Anywhere Railways. Add the following information to the data: 4 1) ARR staff are paid 10.56 per hour. Calculate the number of hours for each shift and the pay to staff (Some shifts finish after midnight 5 2) Add 12.1% holiday pay to the total pay and then add 0.2% national insurance to this figure (show as 2 columns). 6 3) Provide a total cost to ARR including holiday pay and national insurance in Column G 7 4) Anywhere railways pays ARR 20.53 per shift with a minimum 4 hour shift length. Calculate the revenue for each shift and total revenc 8 5) Using a Pivot Table. Show the total nber of hours and total cost to Anywhere Railway for shifts worked at Dundee. 9 10 Date Station Start Finish 11 14/09/2020 Aberdeen 06:15:00 20:30:00 12 15/09/2020 Aberdeen 06:15:00 20:30:00 13 16/09/2020 Aberdeen 06:15:00 20:30:00 14 17/09/2020 Aberdeen 06:15:00 20:30:00 15 18/09/2020 Aberdeen 06:15:00 20:30:00 16 14/09/2020 Edinburgh Wa 04:45:00 18:15:00 17 15/09/2020 Edinburgh Wa 04:45:00 18:15:00 18 16/09/2020 Edinburgh Wa 04:45:00 18:15:00 19 17/09/2020 Edinburgh Wa 04:45:00 18:15:00 20 18/09/2020 Edinburgh Wa 04:45:00 18:15:00 21 14/09/2020 Inverurie 08:45:00 18:30:00 22 15/09/2020 Inverurie 08:45:00 18:30:00 23 16/09/2020 Inverurie 08:45:00 18:30:00 24 17/09/2020 Inverurie 08:45:00 18:30:00 18:30:00 18:30:00 18:45:00 18:45:00 08:45:00 08:45:00 08:00:00 08:00:00 08:00:00 08:00:00 08:00:00 09:45:00 11:30:00 08:30:00 11:45:00 08:15:00 18:45:00 18:45:00 18:45:00 20:00:00 17:45:00 17:15:00 24 17/09/2020 Inverurie 25 18/09/2020 Inverurie 26 14/09/2020 Aberdeen 27 15/09/2020 Aberdeen 28 16/09/2020 Aberdeen 29 17/09/2020 Aberdeen 30 18/09/2020 Aberdeen 31 13/09/2020 Aberdeen 32 13/09/2020 Aberdeen 33 13/09/2020 Edinburgh Wa 34 13/09/2020 Inverurie 35 14/09/2020 Edinburgh Wa 36 15/09/2020 Edinburgh Wa 37 16/09/2020 Edinburgh Wa 38 17/09/2020 Edinburgh Wa 39 18/09/2020 Edinburgh Wa 40 12/09/2020 Edinburgh Wa 41 12/09/2020 Edinburgh Wa 42 12/09/2020 Aberdeen 43 12/09/2020 Aberdeen 44 12/09/2020 Inverurie 45 12/09/2020 Glasgow Quee 46 12/09/2020 Glasgow Quer 47 12/09/2020 Glasgow Quet 48 12/09/2020 Glasgow Quee 49 12/09/2020 Glasgow Quee 50 12/09/2020 Inverurie 51 12/09/2020 Inverurie 52 12/09/2020 Inverurie 08:15:00 08:15:00 08:15:00 08:15:00 07:00:00 07:00:00 08:15:00 08:45:00 07:45:00 21:30:00 04:15:00 04:15:00 12:30:00 12:30:00 04:30:00 04:30:00 14:00:00 20:30:00 18:15:00 18:15:00 18:15:00 18:15:00 18:15:00 17:45:00 17:45:00 20:15:00 17:30:00 18:15:00 00:00:00 12:45:00 12:45:00 20:45:00 20:45:00 14:15:00 14:15:00 00:00:00 00:00:00 00:00:00 15:00:00 15:00:00 21:30:00 21:30:00 16:15:00 16:15:00 00:00:00 00:00:00 14:30:00 14:30:00 23:30:00 2 12/09/2020 Inverurie 14:00:00 3 12/09/2020 Inverurie 14:00:00 54 13/09/2020 Glasgow Quee 08:00:00 55 13/09/2020 Glasgow Quee 08:00:00 56 13/09/2020 Glasgow Quee 14:45:00 57 13/09/2020 Glasgow Quec 14:45:00 58 13/09/2020 Inverurie 08:15:00 59 13/09/2020 Inverurie 08:15:00 60 13/09/2020 Inverurie 16:00:00 61 13/09/2020 Inverurie 16:00:00 62 12/09/2020 Dumbarton C 05:30:00 63 12/09/2020 Dumbarton Co 05:30:00 64 12/09/2020 Dumbarton Ge 14:15:00 65 12/09/2020 Dumbarton C 14:15:00 66 12/09/2020 Dundee 05:15:00 67 12/09/2020 Dundee 05:15:00 68 12/09/2020 Dundee 14:00:00 69 12/09/2020 Dundee 14:00:00 70 12/09/2020 Glasgow Quee 06:15:00 71 12/09/2020 Glasgow Quee 14:30:00 72 12/09/2020 Glasgow Quee 14:30:00 73 12/09/2020 Aberdeen 04:15:00 74 12/09/2020 Aberdeen 04:15:00 75 12/09/2020 Aberdeen 13:45:00 76 12/09/2020 Aberdeen 13:45:00 77 13/09/2020 Dumbarton C 09:00:00 78 13/09/2020 Dumbarton C 09:00:00 79 13/09/2020 Dumbarton C 16:45:00 80 13/09/2020 Dumbarton C 16:45:00 Instructions Rates Page 1 23:30:00 14:15:00 14.15:00 23:15:00 23:15:00 14:45:00 23:15:00 23:15:00 14:00:00 14:00:00 23:15:00 00:00:00 17:00:00 17:00:00 00:30:00 00:30:00 Page 2 Pag 00:30:00 00:30:00 16:00:00 16:00:00 23:30:00 23:30:00 16:00:00 16:00:00 23:15:00 23:15:00 16:00:00 16:00:00 16:45:00 79 13/09/2020 Dumbarton C 16:45:00 80 13/09/2020 Dumbarton C 16:45:00 81 13/09/2020 Huntly 08:30:00 82 13/09/2020 Huntly 08:30:00 83 13/09/2020 Huntly 15:45:00 84 13/09/2020 Huntly 15:45:00 85 13/09/2020 Dundee 08:30:00 86 13/09/2020 Dundee 08:30:00 87 13/09/2020 Dundee 15:45:00 88 13/09/2020 Dundee 15:45:00 89 13/09/2020 Edinburgh Wa 08:45:00 90 13/09/2020 Dundee 08:45:00 91 13/09/2020 Dalmuir 09:00:00 92 13/09/2020 Glasgow Quee 08:45:00 93 13/09/2020 Keith 08:30:00 94 13/09/2020 Glasgow Quee 08:30:00 95 13/09/2020 Dundee 08:00:00 96 13/09/2020 Dundee 08:00:00 97 13/09/2020 Edinburgh Wa 09:15:00 98 13/09/2020 Glasgow Quee 09:15:00 99 13/09/2020 Glasgow Quee 09:30:00 100 13/09/2020 Edinburgh Wa 08:00:00 101 13/09/2020 Glasgow Quee 15:30:00 102 13/09/2020 Glasgow Quee 07:45:00 103 13/09/2020 Glasgow Quee 07:45:00 104 13/09/2020 Glasgow Quee 07:45:00 105 13/09/2020 Aberdeen 09:30:00 106 13/09/2020 Aberdeen 09:30:00 107 13/09/2020 Aberdeen 15:45:00 Instructions Rates Page 1 17:00:00 17:15:00 17:15:00 17:45:00 14:15:00 19:30:00 20:30:00 20:15:00 16:00:00 23:15:00 15:45:00 15:45:00 15:45:00 16:00:00 16:00:00 23:45:00 Page 2 15:45:00 HT 07:30:00 07:30:00 15:15:00 15:15:00 23:45:00 15:30:00 15:30:00 23:15:00 22:00:00 22:00:00 23:15:00 01:45:00 01:45:00 01:45:00 01:45:00 23:30:00 22:00:00 22:00:00 21:45:00 21:45:00 21:45:00 21:45:00 37 13/09/2020 Aberdeen 08 13/09/2020 Aberdeen 09 13/09/2020 Aberdeen 10 13/09/2020 Aberdeen 11 13/09/2020 Aberdeen 12 14/09/2020 Glasgow Quee 113 15/09/2020 Glasgow Que 114 16/09/2020 Glasgow Quec 115 17/09/2020 Glasgow Quee 116 14/09/2020 Dundee 117 14/09/2020 Dundee 118 15/09/2020 Dundee 119 15/09/2020 Dundee 120 16/09/2020 Dundee 121 16/09/2020 Dundee 122 17/09/2020 Dundee 123 17/09/2020 Dundee 124 14/09/2020 Aberdeen 125 15/09/2020 Aberdeen 126 16/09/2020 Aberdeen 127 17/09/2020 Aberdeen 128 14/09/2020 Dundee 129 15/09/2020 Dundee 130 16/09/2020 Dundee 131 17/09/2020 Dundee 132 12/09/2020 Dundee 133 12/09/2020 Dundee 134 12/09/2020 Glasgow Quee 135 12/09/2020 Glasgow Quee 23:30:00 23:30:00 23:30:00 23:30:00 23:30:00 23:30:00 21:45:00 21:45:00 21:45:00 21:45:00 21:45:00 21:45:00 21:45:00 21:45:00 22:00:00 22:00:00 22:00:00 22:00:00 23:45:00 23:45:00 06:15:00 21:30:00 23:30:00 00:45:00 00:45:00 00:45:00 00:45:00 00:45:00 00:45:00 00:45:00 00:45:00 01:00:00 01:00:00 14:45:00 00:00:00 Instructions Jork for Abellio providing a Rail Replacement Service to Anywhere Ra e the exercises on the following pages please carefully read and und as well as the Anywhere Rate structure provided in the 'Rates 'ARR' is Abellio Rail Replacement. e spend a total of no more than 60 minutes on the task, including readi questions that you do not complete blank ase highlight completed questions and ensure that any new columns adde ghlighted. Any formulas used should be left within the cells for assessmen assumptions/addition tabs as necessary but only submit one excel docume ENO Pre Planned: P Standby Emergency 4 Rates are paid to operators based on a journey from a start/end station Operator payments attract a minimum equivalent of 3 hours rate for pre planned work Rates are paid to operators on a pre-planned hourly basis Rates are the same for running or standby in - The running rate attracts a higher hourly rate when standbys are used Hours Target Rate/hour Comment 5 Revenue to ARR Hours Target Rate/hour in Comments hours Revenue to ARR 3 hour minimum of E140 hours Hours Target Comments Revenue to ARR Rate/hour 162.99 minimum for the first three 25 hours per hours then 36.94 hour per hour beyond the first 3 hours 154.33 per hour. 162.99 minimum 8 CO 640 per hour Full sized coach/bus Minimum 160 E6L. 56 per hour. Min 4 hours Full sized coach/bus > 3 hours 25 per hour 9 F42.38 for each hour vehicle is in use E40 per hour E46.18 per hour 3 hour minimum of 175 hours [49.11 per hour. 147.33 minimum Minibus 20 per hours hour E147.33 minimum for the first three hours then 37.101 per hour beyond the first 3 hours 4 hours running or standby E30 per hour Mini bus E53.00 per Minimum hour. Min 4 120 hours 10 Minibus > hours 6251 614733 for first 3 hours plus 37.10 per hour over 3 hours >3 hours E20 per hour 37.10 for each hour the vehicle is in use >4 hours running or standby 11 130 per hour 54.95 per hour 3 hours 3 hour minimum of 6180 E72.26 per hour. 216.78 minimum 133 per 3 hours hour 216.78 minimum for the first three hours then [49.13 per hour beyond the first hours 4 hours running or standby Minimum E81.87 per E50 per hour 1200 for 4 hour. Min 4 hours hours TZ Double Deck coach/bus Double Deck coach/bus Double Deck coach/bus > hours [42 216.78 for first three hours plus E56 37 per hour over 3 hours. 33 per >>3 hours hour 156.37 for each hour used 4 hours running or standby E50 per hour E6L42 per hour 13 14 15 A B C D E F G H J K 1 Pre planned Coordination 2 3 The below lines are shifts undertaken by Coordination for Anywhere Railways. Add the following information to the data: 4 1) ARR staff are paid 10.56 per hour. Calculate the number of hours for each shift and the pay to staff (Some shifts finish after midnight 5 2) Add 12.1% holiday pay to the total pay and then add 0.2% national insurance to this figure (show as 2 columns). 6 3) Provide a total cost to ARR including holiday pay and national insurance in Column G 7 4) Anywhere railways pays ARR 20.53 per shift with a minimum 4 hour shift length. Calculate the revenue for each shift and total revenc 8 5) Using a Pivot Table. Show the total nber of hours and total cost to Anywhere Railway for shifts worked at Dundee. 9 10 Date Station Start Finish 11 14/09/2020 Aberdeen 06:15:00 20:30:00 12 15/09/2020 Aberdeen 06:15:00 20:30:00 13 16/09/2020 Aberdeen 06:15:00 20:30:00 14 17/09/2020 Aberdeen 06:15:00 20:30:00 15 18/09/2020 Aberdeen 06:15:00 20:30:00 16 14/09/2020 Edinburgh Wa 04:45:00 18:15:00 17 15/09/2020 Edinburgh Wa 04:45:00 18:15:00 18 16/09/2020 Edinburgh Wa 04:45:00 18:15:00 19 17/09/2020 Edinburgh Wa 04:45:00 18:15:00 20 18/09/2020 Edinburgh Wa 04:45:00 18:15:00 21 14/09/2020 Inverurie 08:45:00 18:30:00 22 15/09/2020 Inverurie 08:45:00 18:30:00 23 16/09/2020 Inverurie 08:45:00 18:30:00 24 17/09/2020 Inverurie 08:45:00 18:30:00 18:30:00 18:30:00 18:45:00 18:45:00 08:45:00 08:45:00 08:00:00 08:00:00 08:00:00 08:00:00 08:00:00 09:45:00 11:30:00 08:30:00 11:45:00 08:15:00 18:45:00 18:45:00 18:45:00 20:00:00 17:45:00 17:15:00 24 17/09/2020 Inverurie 25 18/09/2020 Inverurie 26 14/09/2020 Aberdeen 27 15/09/2020 Aberdeen 28 16/09/2020 Aberdeen 29 17/09/2020 Aberdeen 30 18/09/2020 Aberdeen 31 13/09/2020 Aberdeen 32 13/09/2020 Aberdeen 33 13/09/2020 Edinburgh Wa 34 13/09/2020 Inverurie 35 14/09/2020 Edinburgh Wa 36 15/09/2020 Edinburgh Wa 37 16/09/2020 Edinburgh Wa 38 17/09/2020 Edinburgh Wa 39 18/09/2020 Edinburgh Wa 40 12/09/2020 Edinburgh Wa 41 12/09/2020 Edinburgh Wa 42 12/09/2020 Aberdeen 43 12/09/2020 Aberdeen 44 12/09/2020 Inverurie 45 12/09/2020 Glasgow Quee 46 12/09/2020 Glasgow Quer 47 12/09/2020 Glasgow Quet 48 12/09/2020 Glasgow Quee 49 12/09/2020 Glasgow Quee 50 12/09/2020 Inverurie 51 12/09/2020 Inverurie 52 12/09/2020 Inverurie 08:15:00 08:15:00 08:15:00 08:15:00 07:00:00 07:00:00 08:15:00 08:45:00 07:45:00 21:30:00 04:15:00 04:15:00 12:30:00 12:30:00 04:30:00 04:30:00 14:00:00 20:30:00 18:15:00 18:15:00 18:15:00 18:15:00 18:15:00 17:45:00 17:45:00 20:15:00 17:30:00 18:15:00 00:00:00 12:45:00 12:45:00 20:45:00 20:45:00 14:15:00 14:15:00 00:00:00 00:00:00 00:00:00 15:00:00 15:00:00 21:30:00 21:30:00 16:15:00 16:15:00 00:00:00 00:00:00 14:30:00 14:30:00 23:30:00 2 12/09/2020 Inverurie 14:00:00 3 12/09/2020 Inverurie 14:00:00 54 13/09/2020 Glasgow Quee 08:00:00 55 13/09/2020 Glasgow Quee 08:00:00 56 13/09/2020 Glasgow Quee 14:45:00 57 13/09/2020 Glasgow Quec 14:45:00 58 13/09/2020 Inverurie 08:15:00 59 13/09/2020 Inverurie 08:15:00 60 13/09/2020 Inverurie 16:00:00 61 13/09/2020 Inverurie 16:00:00 62 12/09/2020 Dumbarton C 05:30:00 63 12/09/2020 Dumbarton Co 05:30:00 64 12/09/2020 Dumbarton Ge 14:15:00 65 12/09/2020 Dumbarton C 14:15:00 66 12/09/2020 Dundee 05:15:00 67 12/09/2020 Dundee 05:15:00 68 12/09/2020 Dundee 14:00:00 69 12/09/2020 Dundee 14:00:00 70 12/09/2020 Glasgow Quee 06:15:00 71 12/09/2020 Glasgow Quee 14:30:00 72 12/09/2020 Glasgow Quee 14:30:00 73 12/09/2020 Aberdeen 04:15:00 74 12/09/2020 Aberdeen 04:15:00 75 12/09/2020 Aberdeen 13:45:00 76 12/09/2020 Aberdeen 13:45:00 77 13/09/2020 Dumbarton C 09:00:00 78 13/09/2020 Dumbarton C 09:00:00 79 13/09/2020 Dumbarton C 16:45:00 80 13/09/2020 Dumbarton C 16:45:00 Instructions Rates Page 1 23:30:00 14:15:00 14.15:00 23:15:00 23:15:00 14:45:00 23:15:00 23:15:00 14:00:00 14:00:00 23:15:00 00:00:00 17:00:00 17:00:00 00:30:00 00:30:00 Page 2 Pag 00:30:00 00:30:00 16:00:00 16:00:00 23:30:00 23:30:00 16:00:00 16:00:00 23:15:00 23:15:00 16:00:00 16:00:00 16:45:00 79 13/09/2020 Dumbarton C 16:45:00 80 13/09/2020 Dumbarton C 16:45:00 81 13/09/2020 Huntly 08:30:00 82 13/09/2020 Huntly 08:30:00 83 13/09/2020 Huntly 15:45:00 84 13/09/2020 Huntly 15:45:00 85 13/09/2020 Dundee 08:30:00 86 13/09/2020 Dundee 08:30:00 87 13/09/2020 Dundee 15:45:00 88 13/09/2020 Dundee 15:45:00 89 13/09/2020 Edinburgh Wa 08:45:00 90 13/09/2020 Dundee 08:45:00 91 13/09/2020 Dalmuir 09:00:00 92 13/09/2020 Glasgow Quee 08:45:00 93 13/09/2020 Keith 08:30:00 94 13/09/2020 Glasgow Quee 08:30:00 95 13/09/2020 Dundee 08:00:00 96 13/09/2020 Dundee 08:00:00 97 13/09/2020 Edinburgh Wa 09:15:00 98 13/09/2020 Glasgow Quee 09:15:00 99 13/09/2020 Glasgow Quee 09:30:00 100 13/09/2020 Edinburgh Wa 08:00:00 101 13/09/2020 Glasgow Quee 15:30:00 102 13/09/2020 Glasgow Quee 07:45:00 103 13/09/2020 Glasgow Quee 07:45:00 104 13/09/2020 Glasgow Quee 07:45:00 105 13/09/2020 Aberdeen 09:30:00 106 13/09/2020 Aberdeen 09:30:00 107 13/09/2020 Aberdeen 15:45:00 Instructions Rates Page 1 17:00:00 17:15:00 17:15:00 17:45:00 14:15:00 19:30:00 20:30:00 20:15:00 16:00:00 23:15:00 15:45:00 15:45:00 15:45:00 16:00:00 16:00:00 23:45:00 Page 2 15:45:00 HT 07:30:00 07:30:00 15:15:00 15:15:00 23:45:00 15:30:00 15:30:00 23:15:00 22:00:00 22:00:00 23:15:00 01:45:00 01:45:00 01:45:00 01:45:00 23:30:00 22:00:00 22:00:00 21:45:00 21:45:00 21:45:00 21:45:00 37 13/09/2020 Aberdeen 08 13/09/2020 Aberdeen 09 13/09/2020 Aberdeen 10 13/09/2020 Aberdeen 11 13/09/2020 Aberdeen 12 14/09/2020 Glasgow Quee 113 15/09/2020 Glasgow Que 114 16/09/2020 Glasgow Quec 115 17/09/2020 Glasgow Quee 116 14/09/2020 Dundee 117 14/09/2020 Dundee 118 15/09/2020 Dundee 119 15/09/2020 Dundee 120 16/09/2020 Dundee 121 16/09/2020 Dundee 122 17/09/2020 Dundee 123 17/09/2020 Dundee 124 14/09/2020 Aberdeen 125 15/09/2020 Aberdeen 126 16/09/2020 Aberdeen 127 17/09/2020 Aberdeen 128 14/09/2020 Dundee 129 15/09/2020 Dundee 130 16/09/2020 Dundee 131 17/09/2020 Dundee 132 12/09/2020 Dundee 133 12/09/2020 Dundee 134 12/09/2020 Glasgow Quee 135 12/09/2020 Glasgow Quee 23:30:00 23:30:00 23:30:00 23:30:00 23:30:00 23:30:00 21:45:00 21:45:00 21:45:00 21:45:00 21:45:00 21:45:00 21:45:00 21:45:00 22:00:00 22:00:00 22:00:00 22:00:00 23:45:00 23:45:00 06:15:00 21:30:00 23:30:00 00:45:00 00:45:00 00:45:00 00:45:00 00:45:00 00:45:00 00:45:00 00:45:00 01:00:00 01:00:00 14:45:00 00:00:00 Instructions Jork for Abellio providing a Rail Replacement Service to Anywhere Ra e the exercises on the following pages please carefully read and und as well as the Anywhere Rate structure provided in the 'Rates 'ARR' is Abellio Rail Replacement. e spend a total of no more than 60 minutes on the task, including readi questions that you do not complete blank ase highlight completed questions and ensure that any new columns adde ghlighted. Any formulas used should be left within the cells for assessmen assumptions/addition tabs as necessary but only submit one excel docume ENO Pre Planned: P Standby Emergency 4 Rates are paid to operators based on a journey from a start/end station Operator payments attract a minimum equivalent of 3 hours rate for pre planned work Rates are paid to operators on a pre-planned hourly basis Rates are the same for running or standby in - The running rate attracts a higher hourly rate when standbys are used Hours Target Rate/hour Comment 5 Revenue to ARR Hours Target Rate/hour in Comments hours Revenue to ARR 3 hour minimum of E140 hours Hours Target Comments Revenue to ARR Rate/hour 162.99 minimum for the first three 25 hours per hours then 36.94 hour per hour beyond the first 3 hours 154.33 per hour. 162.99 minimum 8 CO 640 per hour Full sized coach/bus Minimum 160 E6L. 56 per hour. Min 4 hours Full sized coach/bus > 3 hours 25 per hour 9 F42.38 for each hour vehicle is in use E40 per hour E46.18 per hour 3 hour minimum of 175 hours [49.11 per hour. 147.33 minimum Minibus 20 per hours hour E147.33 minimum for the first three hours then 37.101 per hour beyond the first 3 hours 4 hours running or standby E30 per hour Mini bus E53.00 per Minimum hour. Min 4 120 hours 10 Minibus > hours 6251 614733 for first 3 hours plus 37.10 per hour over 3 hours >3 hours E20 per hour 37.10 for each hour the vehicle is in use >4 hours running or standby 11 130 per hour 54.95 per hour 3 hours 3 hour minimum of 6180 E72.26 per hour. 216.78 minimum 133 per 3 hours hour 216.78 minimum for the first three hours then [49.13 per hour beyond the first hours 4 hours running or standby Minimum E81.87 per E50 per hour 1200 for 4 hour. Min 4 hours hours TZ Double Deck coach/bus Double Deck coach/bus Double Deck coach/bus > hours [42 216.78 for first three hours plus E56 37 per hour over 3 hours. 33 per >>3 hours hour 156.37 for each hour used 4 hours running or standby E50 per hour E6L42 per hour 13 14 15