2015 Round 2: The Price Is Right

Description
This workbook is taken from the 2nd round of the 2015 Modeloff financial modeling world chamionships.

Follow the instructions in order to complete the model:

You have been hired by a consortium to assist with their financial modelling for an upcoming tender. The tender is a government contract to operate and maintain for 15 years an existing asset, a major water treatment facility. If successful the consortium plans to set up a new company called “OpCo” (short for Operating Company) which will carry out the contract. As part of the tender submission the consortium is required to bid the amount of revenue it would like OpCo to receive in order to operate and maintain the asset.

Your task is to:

 Model the year 1 expenses in accordance with the data below.

 Model the growth profile of the expenses over the contract life.

 Model the additional Lifecycle Costs (non-recurring one-off expenditure relating to major maintenance work).

 Back solve the necessary bid values for the revenue stream, in order to meet the consortium’s internal revenue sizing conditions.

You will probably need to build a simple Cash Flow statement of OpCo in order to complete this task. Information is provided in the attached PDF regarding the assumed expenses of OpCo, the escalation assumptions for both revenues and expenses, as well as general model assumptions. Use your model to answer the case study questions.

Allotted time: 40 minutes

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