1 2 Instructions 3 4 You work for Abellio providing a Rail Replacement Service to Anywhere Railways. In order to 5 complete the exercises on the following pages please carefully read and understand the questions 6 as well as the Anywhere Rate structure provided in the 'Rates' tab. 7 8 'ARR' is Abellio Rail Replacement. 9 Please spend a total of no more than 60 minutes on the task, including reading time. Leave any questions that you do not complete blank 10 11 12 13 14 Please highlight completed questions and ensure that any new columns added to the data are highlighted. Any formulas used should be left within the cells for assessment. You may add assumptions/addition tabs as necessary but only submit one excel document for review 15 16 17 18 19 2 Pre Planned: Standby Emergency Rates are paid to operators based on a journey from a 4 start/end station Rates are the same for running or standby Rates are paid to operators on a pre-planned hourly basis - The 'running rate attracts a higher hourly rate when standbys are used Operator payments attract a minimum equivalent of 3 hours rate for pre planned work 5 5 Hours Comment Target Rate/hour Revenue to ARR Hours Target Rate/hour Comments Revenue to ARR Hours Target Rate/hour in comments hours Revenue to ARR 54.33 per 3 hour minimum of E140 25 per hours hour. 162.99 minimum 3 hours hour 162.99 minimum for the first three hours then 36.94 per hour beyond the first 3 hours 24 hours running/ standby 40 per hour Minimum 160 [61.56 per hour. Min 4 hours Full sized coach/bus Full sized coach/bus Full sized coach/bus > E30 162.99 for first 3 hours plus E42.38 per hour over 3 hours (25 per hours 46.18 per >>3 hours hour 42.38 for each hour vehicle is in use 4 hours running/ standby 40 per hour hour 3 hour minimum of E75 49.11 per hour. [14733 minimum hours 120 per 3 hours Minibus hour E147.33 minimum for the first three hours then 37.100 per hour beyond the first 3 hours hours 25 E147.33 for first 3 hours plus E37.10 per hour over 3 hours 20 per >3 hours hour 37.10 for each hour the vehicle is in use 4 hours running or standby 530 per hour E14.98 per hour 11 3 hours 3 hour minimum of E180 72.26 per hour. [216.78 minimum 033 per hours hour 216.78 minimum for the first three hours then 49.13 per hour beyond the first 3 hours Minimum E81.87 per 50 per hour 1200 for 4 hour. Min 4 hours hours 12 Double Deck coach/bus Double Deck coach/bus >4 hours running or standby Double Deck coach/bus 4 hours running or standby > hours E216.78 for first three hours plus 56.37 per hour over 3 hours f42 633 per >3 hours 156.37 for each hour used hour 50 per hour 61.42 per hour 5 14 instruction 1. Emergency Bus Use the rates structure to calculate the following 1) The revenue ARR will eam from Anywhere Railways for the below emergency duties 2) Use the target rate to estimate the amount ARR should spend to cover the emergency duties listed below taking the comments on the rate card into account) 3) Calculate the difference between the estimated amount ARR should spend and the revenue ARR will earn from Anywhere Railways 4) Calculate the margin (expressed as a %) ARR would make based on the revenue and estimated cost to ARR. S) Filter the data so that it only shows the duties that ran on the 15th September 2020. Ref Date Incident Start Time Incident end time Route from 8021 13/03/2020 21:55 08:08 Kyle of Lochalsh 8021 13/09/2020 21:55 08:08 Kyle of Lochalsh 8021 13/09/2020 10:55 08:08 Crianlarich 8021 13/09/2020 10:55 08:08 Fort William 8021 13/09/2020 15:20 08:08 Inverness 8021 13/09/2020 15:20 08:08 Inverness 3021 13/09/2020 15.20 08:08 Crianlarich B021 13/09/2020 21:10 08:08 Perth 8021 13/09/2020 21:10 08:08 Perth 8022 14/09/2020 12:00 19:00 Kyle of Lochalsh 8022 14/09/2020 12:00 19:00 Kyle of Lochalsh 8022 14/09/2020 18:15 08:08 Oban 8022 14/09/2020 18:15 08:08. Canlarich 8022 14/09/2020 21:31 08:08 Fort William 8022 14/09/2020 21:31 08:08 Fort William 168812 14/09/2020 09:51 08:08 Fort William 168812 14/09/2020 09:51 08:08 Fort William 168812 14/09/2020 09:51 08:08 Fort William TORR12 1 and/0020 09:51 ET Route To Reason Inverness Other - Please add detail to notes Inverness Other - please add detail to notes Mallaig Other - Please add detail to notes Glasgow Queen StreeOther - Please add detail to notes Kyle of Lochalsh Other please add detail to notes Kyle of Lochalsh Other - Please add detail to notes Mallaig Other - Please add detail to notes Glasgow Queen StreSignal Fault Edinburgh Waverley Signal Fault Inverness Other: Please add detail to notes Inverness Other Please add detail to notes Glasgow Queen StrecOtherPlease add detail to notes Oban Other Please add detail to notes Glasgow Queen Strecother - Please add detail to notes Glasgow Queen Strecother. Please add detail to notes Dumbarton Central Landslide Dumbarton Central Landslide Dumbarton Central Landslide mal Request Reference Start Time ETA at Station Actual at station Stood Down End Time Vehicle Type actual hours ERRN14036 05:30:00 09:30:00 09:30:00 15:00:00 16:00:00 MiniCoach 10:30:00 ERRN14095 08-00:00 09:30:00 09:30:00 13:15:00 13:33:00 FullSized Coach 05:33:00 ERRI14098 12:00:00 14:24:00 14:24:00 00:10:00 00:40:00 FullSized Coach 12:40:00 ERRR 14099 13:00-00 16:00:00 16:00:00 21:30:00 22:00:00 FullSized Coach 09:00:00 ERR14103 15:30:00 17:00:00 17:00:00 00:00:00 02:00:00 MiniCoach 10:30:00 ERR14102 17:30:00 17:45:00 17:45:00 20:22:00 23:15:00 FullSized Coach OS:45:00 ERAN14104 17:30:00 19:30:00 19:30:00 00:45:00 05:15:00 FullSizedCoach 11:45:00 ERR814109 22:15:00 23:05:00 23:05:00 00:55:00 01:40:00 MiniCoach 03:25:00 ERRN14108 22:00:00 23:00:00 23:00:00 00:50:00 01:25:00 FullSized Coach 03:25:00 ERRN14101 01:30:00 05:00:00 05:00.00 16:15:00 17:45:00 MiniCoach 16:15:00 ERRI14100 02:45:00 05:00:00 05:00:00 15:23:00 15:45:00 FullSized Coach 13:00:00 ERRM14106 01:30:00 04:30:00 04:30:00 09:15:00 09:45:00 Fullsizedcoach OB:15:00 ERRA 14107 04:50:00 07:00:00 07:00:00 19:15:00 21:30:00 FullSized Coach 16:40:00 ERRN14110 04:15:00 07:00:00 07:00:00 11:30:00 12:15:00 FullSized Coach 08:00-00 ERRI14111 04:15:00 07:00:00 07:00:00 11:30:00 12:15:00 MiniCoach 08:00:00 ERRA 14117 13:20:00 17:00:00 17:00:00 19:00:00 22:15:00 MiniCoach 08:55:00) ERRI14112 11:00:00 11:40:00 11:40:00 21:50:00 22:30:00 FullSizedCoach 11:30:00 ERRN14112 11:00:00 11:40:00 11:40:00 21:50:00 22:30:00 FullSized Coach 11:30:00 1 2 Instructions 3 4 You work for Abellio providing a Rail Replacement Service to Anywhere Railways. In order to 5 complete the exercises on the following pages please carefully read and understand the questions 6 as well as the Anywhere Rate structure provided in the 'Rates' tab. 7 8 'ARR' is Abellio Rail Replacement. 9 Please spend a total of no more than 60 minutes on the task, including reading time. Leave any questions that you do not complete blank 10 11 12 13 14 Please highlight completed questions and ensure that any new columns added to the data are highlighted. Any formulas used should be left within the cells for assessment. You may add assumptions/addition tabs as necessary but only submit one excel document for review 15 16 17 18 19 2 Pre Planned: Standby Emergency Rates are paid to operators based on a journey from a 4 start/end station Rates are the same for running or standby Rates are paid to operators on a pre-planned hourly basis - The 'running rate attracts a higher hourly rate when standbys are used Operator payments attract a minimum equivalent of 3 hours rate for pre planned work 5 5 Hours Comment Target Rate/hour Revenue to ARR Hours Target Rate/hour Comments Revenue to ARR Hours Target Rate/hour in comments hours Revenue to ARR 54.33 per 3 hour minimum of E140 25 per hours hour. 162.99 minimum 3 hours hour 162.99 minimum for the first three hours then 36.94 per hour beyond the first 3 hours 24 hours running/ standby 40 per hour Minimum 160 [61.56 per hour. Min 4 hours Full sized coach/bus Full sized coach/bus Full sized coach/bus > E30 162.99 for first 3 hours plus E42.38 per hour over 3 hours (25 per hours 46.18 per >>3 hours hour 42.38 for each hour vehicle is in use 4 hours running/ standby 40 per hour hour 3 hour minimum of E75 49.11 per hour. [14733 minimum hours 120 per 3 hours Minibus hour E147.33 minimum for the first three hours then 37.100 per hour beyond the first 3 hours hours 25 E147.33 for first 3 hours plus E37.10 per hour over 3 hours 20 per >3 hours hour 37.10 for each hour the vehicle is in use 4 hours running or standby 530 per hour E14.98 per hour 11 3 hours 3 hour minimum of E180 72.26 per hour. [216.78 minimum 033 per hours hour 216.78 minimum for the first three hours then 49.13 per hour beyond the first 3 hours Minimum E81.87 per 50 per hour 1200 for 4 hour. Min 4 hours hours 12 Double Deck coach/bus Double Deck coach/bus >4 hours running or standby Double Deck coach/bus 4 hours running or standby > hours E216.78 for first three hours plus 56.37 per hour over 3 hours f42 633 per >3 hours 156.37 for each hour used hour 50 per hour 61.42 per hour 5 14 instruction 1. Emergency Bus Use the rates structure to calculate the following 1) The revenue ARR will eam from Anywhere Railways for the below emergency duties 2) Use the target rate to estimate the amount ARR should spend to cover the emergency duties listed below taking the comments on the rate card into account) 3) Calculate the difference between the estimated amount ARR should spend and the revenue ARR will earn from Anywhere Railways 4) Calculate the margin (expressed as a %) ARR would make based on the revenue and estimated cost to ARR. S) Filter the data so that it only shows the duties that ran on the 15th September 2020. Ref Date Incident Start Time Incident end time Route from 8021 13/03/2020 21:55 08:08 Kyle of Lochalsh 8021 13/09/2020 21:55 08:08 Kyle of Lochalsh 8021 13/09/2020 10:55 08:08 Crianlarich 8021 13/09/2020 10:55 08:08 Fort William 8021 13/09/2020 15:20 08:08 Inverness 8021 13/09/2020 15:20 08:08 Inverness 3021 13/09/2020 15.20 08:08 Crianlarich B021 13/09/2020 21:10 08:08 Perth 8021 13/09/2020 21:10 08:08 Perth 8022 14/09/2020 12:00 19:00 Kyle of Lochalsh 8022 14/09/2020 12:00 19:00 Kyle of Lochalsh 8022 14/09/2020 18:15 08:08 Oban 8022 14/09/2020 18:15 08:08. Canlarich 8022 14/09/2020 21:31 08:08 Fort William 8022 14/09/2020 21:31 08:08 Fort William 168812 14/09/2020 09:51 08:08 Fort William 168812 14/09/2020 09:51 08:08 Fort William 168812 14/09/2020 09:51 08:08 Fort William TORR12 1 and/0020 09:51 ET Route To Reason Inverness Other - Please add detail to notes Inverness Other - please add detail to notes Mallaig Other - Please add detail to notes Glasgow Queen StreeOther - Please add detail to notes Kyle of Lochalsh Other please add detail to notes Kyle of Lochalsh Other - Please add detail to notes Mallaig Other - Please add detail to notes Glasgow Queen StreSignal Fault Edinburgh Waverley Signal Fault Inverness Other: Please add detail to notes Inverness Other Please add detail to notes Glasgow Queen StrecOtherPlease add detail to notes Oban Other Please add detail to notes Glasgow Queen Strecother - Please add detail to notes Glasgow Queen Strecother. Please add detail to notes Dumbarton Central Landslide Dumbarton Central Landslide Dumbarton Central Landslide mal Request Reference Start Time ETA at Station Actual at station Stood Down End Time Vehicle Type actual hours ERRN14036 05:30:00 09:30:00 09:30:00 15:00:00 16:00:00 MiniCoach 10:30:00 ERRN14095 08-00:00 09:30:00 09:30:00 13:15:00 13:33:00 FullSized Coach 05:33:00 ERRI14098 12:00:00 14:24:00 14:24:00 00:10:00 00:40:00 FullSized Coach 12:40:00 ERRR 14099 13:00-00 16:00:00 16:00:00 21:30:00 22:00:00 FullSized Coach 09:00:00 ERR14103 15:30:00 17:00:00 17:00:00 00:00:00 02:00:00 MiniCoach 10:30:00 ERR14102 17:30:00 17:45:00 17:45:00 20:22:00 23:15:00 FullSized Coach OS:45:00 ERAN14104 17:30:00 19:30:00 19:30:00 00:45:00 05:15:00 FullSizedCoach 11:45:00 ERR814109 22:15:00 23:05:00 23:05:00 00:55:00 01:40:00 MiniCoach 03:25:00 ERRN14108 22:00:00 23:00:00 23:00:00 00:50:00 01:25:00 FullSized Coach 03:25:00 ERRN14101 01:30:00 05:00:00 05:00.00 16:15:00 17:45:00 MiniCoach 16:15:00 ERRI14100 02:45:00 05:00:00 05:00:00 15:23:00 15:45:00 FullSized Coach 13:00:00 ERRM14106 01:30:00 04:30:00 04:30:00 09:15:00 09:45:00 Fullsizedcoach OB:15:00 ERRA 14107 04:50:00 07:00:00 07:00:00 19:15:00 21:30:00 FullSized Coach 16:40:00 ERRN14110 04:15:00 07:00:00 07:00:00 11:30:00 12:15:00 FullSized Coach 08:00-00 ERRI14111 04:15:00 07:00:00 07:00:00 11:30:00 12:15:00 MiniCoach 08:00:00 ERRA 14117 13:20:00 17:00:00 17:00:00 19:00:00 22:15:00 MiniCoach 08:55:00) ERRI14112 11:00:00 11:40:00 11:40:00 21:50:00 22:30:00 FullSizedCoach 11:30:00 ERRN14112 11:00:00 11:40:00 11:40:00 21:50:00 22:30:00 FullSized Coach 11:30:00