2017 Round 1: Go With the Flow

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 have been asked to model the revenues and costs for the operator of a waste processing plant.

The plant receives waste from two sources – from the authority that commissioned the plant and from third parties. The authority waste is split into a guaranteed tonnage amount and an additional tonnage amount.

If the authority tonnage received in a given period is less than the guaranteed tonnage amount, then revenue is still received for the “missing” tonnage. Tonnage which is paid for but not received in this manner is written off against additional tonnage in later quarters. There is a subcontractor which processes the waste and charges a fee proportional to the amount of waste that is incinerated. Once processed, waste is either incinerated, recycled or landfilled. The revenue received from the recycling and the cost of landfill is passed through to the operator.

The flowchart on the following page describes how the total waste processed in each quarter is split into different categories. On the flowchart, the numbers in brackets next to each waste stream indicate the cost and revenue types which apply to that waste stream. The different revenues and costs are described in the table below.

Where amounts are to be inflated, they are given in 2017 prices. Depending on the terms of the contracts with various parties, the inflation for each revenue or cost item steps annually or quarterly, and at the agreed rate for that line item.

Inflation should be applied such that a full year of inflation has been applied on or by 1 January of each subsequent year (i.e. for all inflation indices the value of the index on 1 January 2019 should reflect exactly one year of inflation). Do NOT round inflated prices to whole cents in interim calculations. Your model should be quarterly and cover the period from 1 January 2018 until 31 December 2030.

For Questions 41 to 48, select your answer from a multiple choice list.
For Questions 49 to 50, you are required to type in your answer.

Prepare your model and then use it to answer the given questions.

Allotted time: 30-45 minutes

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