Exercise n. 4 - Travelling from London to Glasgow

The WBC (Wonderful Building Company), whose main office is located in London, needs to transfer some employees from London to Glasgow (654 km one way) and viceversa every working day (from Monday to Friday) in 2012.

There are three companies which offer transports from London to Glasgow: FBTC (Fast Bus Transport Company), VCTL (Very Cheap Train Lines) and FIOB (Flying Is Our Business). The cost changes depending on the mean of transport used and on the size of the group of travellers, here indicated by the symbol n.

The price of the FBTC bus tickets per km per person is 0.12 € when n<20, 0.11 € when 20<=n<35, 0.10 € when 35<=n<50 and 0.09 when n>=50.

The price of the VCTL train tickets per km per person is 0.11 € when n<30, 0.08 € otherwise.

The price of the FIOB plane tickets is 100 € per person when n<10, 65 € per person otherwise.

You are a novice in the WBC and you are in charge of computing the less expensive way of moving the group of employees from London to Glasgow.

In order to perform your duty, prepare an Excel worksheet which your boss can easily understand. A good idea consists in building a table which calculates the different costs for each trip depending on the size of the group of employees and on the means available. Compute also the total annual cost that the WBC will support with the best choice depending on the size of the group. Adding one or more charts would be probably appreciated by your boss.

Suppose also that your boss decides to place a fixed maximum amount of money to cover the transport expenses for each trip. Prepare another worksheet or add to your worksheet an area, where your boss can type this amount and obtain the maximum number of employees transportable and the best mean available. Try also to calculate the discount that FIOB should give to become the best company (when the maximum amount of money to cover the transport expenses for each trip has been fixed).