2016 Round 1, Section 2: Chip Off the Old Block

Description
This workbook is taken from the First Round of the 2016 Modeloff financial modeling world championships.

Follow the instructions in order to complete the model:

INTRODUCTION

All the inputs mentioned below are provided in the workbook for this case study. You are working for a company that is planning to bid for a 20% stake in the cashflows of a biomass plant which burns wood chip to produce electricity. You have been asked to predict the cashflows of the project from 1 January 2017 until 31 December 2026, using a quarterly model, and to use your model to recommend to your CEO the purchase price she should offer for the 20% stake. You should assume that all invoices are settled in the same quarter they are issued, there are no inventory requirements and no taxes are applicable. Where amounts are to be inflated they are given in 2017 prices and inflation should be applied on 1 January of each subsequent year. Do NOT round inflated prices to whole cents in interim calculations.

ELECTRICITY GENERATION

The plant is a 9MW plant; i.e. if it is running at full capacity it will produce 9MWh of electricity per hour. Your company’s engineers think it is reasonable to assume that the plant will usually run at 95% of its capacity, 24 hours a day. There are several planned shutdown periods when no electricity will be produced at all from the start of the first day (at midnight) until the end of the final day (at midnight). (See PDF for details)

SALE OF ELECTRICITY

Electricity is sold as follows:

• At the market price if this is between the cap price and the floor price. The predictions for the market price have been provided in the workbook accompanying this question on an April -March annual timeline, and should not be inflated.
• At the cap price if the market price is higher than the cap price. The cap price is $70 per MWh, inflated at 2% per annum.
• At the floor price if the market price is lower than the floor price. The floor price is $45 per MWh, inflated at 1% per annum.

Your CEO is concerned that the market price predictions for the sale of electricity may be overly optimistic. She has therefore asked you to run some of the analysis (see Questions 5, 11 and 13) as though electricity were always sold at the floor price. It will be beneficial if you develop your model in a way that makes it easy to switch between modelling the electricity price as described above and modelling only the floor price.

PURCHASE OF WOOD CHIP

In each quarter, the project purchases exactly enough wood chip to produce the MWh output of the plant in that quarter. The plant has two possible wood chip suppliers. The project first buys the wood chip from the supplier that gives them the lowest cost per MWh in that quarter, and, if necessary, tops this up with wood chip from the other supplier.

The first supplier sells wood chip on the following terms:

• Wood chip costs $100 per tonne, inflated at 2% per annum
• 1 tonne of wood chip will produce 3.5 MWh of electricity
• Up to 4,500 tonnes may be purchased per quarter The second supplier sells wood chip on the following terms:
• Wood chip costs $130 per tonne. This is a fixed price that should not be inflated
• 1 tonne of wood chip will produce 4 MWh of electricity
• An unlimited amount of wood chip may be purchased per quarter

FIXED COSTS

The project incurs $75,000 of fixed costs per month. This amount is indexed at 1.5%.

VALUATION

The company’s required rate of return for this project is 10%, and you should assume that there is no terminal value or disposal costs for the 20% stake at the end of the ten years. To determine the purchase price your company should offer:

i) Discount using an Excel function (or functions) that discounts on an actual/365 basis;
ii) Assume an acquisition date of 31 December 2016; and
iii) Assume that all cashflows occur at the end of the quarter in which they occur.

GENERAL COMMENTS

When used in the question wording, “Q1” refers to the period January to March, “Q2” to the period April to June, “Q3” to the period July to September and “Q4” to the period October to December. For Questions 6 to 17, perform your analysis assuming a 10% required rate of return. For Question 18, perform your analysis assuming a 4% required rate of return.

Allotted time: 36 minutes

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