2017 Round 2, Section 3: System Allocation

Description
This workbook is taken from the Second Round of the 2017 Modeloff financial modeling world chamionships.

Follow the instructions in order to complete the model:

You are working for ModelOff Vehicle Services (MVC) who specialize in servicing highly specialized vehicles. MVC have 9 depots (referred to as Depot A to Depot I) which they use to service the vehicles. You are planning for the expected vehicles due next quarter and need to allocate the vehicle intake to the 9 depots under the allocation methodology that MVC uses. MVC earns revenue of $2,000 per vehicle serviced. Profit is calculated as revenue less any transportation costs incurred less any penalty costs incurred. Where a vehicle is serviced at a depot other than the one where the customer drops it off, MVC must pay the transport costs associated with getting it to the depot it is serviced at, then back to the original depot for collection. Transport costs are $1.40 per vehicle per mile (part miles are charged pro rata). If MVC cannot service a vehicle, they must pay a $500 penalty per vehicle. They will pay this penalty in two situations:

1) Where there is insufficient capacity within a hub to service a vehicle; and
2) Where the revenue for a vehicle less the transport costs for that vehicle would be more expensive for MVC than not servicing the vehicle and paying the penalty.


There are 9 depots. The distance between each depot pairing (one way, in miles) is shown in the table.

ALLOCATION METHODOLOGY

For legal reasons, allocation must be assessed and performed for each hub in isolation (no cars from one hub may be serviced in any other hub). The methodology for allocating vehicles to depots for servicing within a hub is:

1) Each vehicle is initially delivered to a certain depot and must be collected from that depot. If MVC chooses to transport that vehicle to another depot for servicing, MVC will bear a transportation cost for that vehicle as set out above.

2) All vehicles are serviced at the depot they arrive, where capacity exists to do so. Where any depot has excess vehicles that cannot be serviced at that depot, vehicles are allocated as per step 3.

3) Calculate the vehicle that has the shortest trip (and therefore cost) to any other depot with available capacity. Allocate that vehicle to that depot for servicing

4) Repeat step 3 until either no excess vehicles remain or MVC would choose to instead pay the penalty per vehicle (as described above).

Note that MVC’s allocation methodology may not lead to the optimum allocation between depots. You need to model MVC’s allocation methodology as it is set out above, you are not required to make any changes to this methodology.

All analysis is conducted on whole vehicles only (no part vehicle calculations are performed).

SCENARIOS

There are 4 different scenarios you need to consider of your model. These scenarios are in the excel
workbook provided.

Allotted time: 30-45 minutes

Once finished, feel free to upload your model to your own dedicated author channel!