2015 Round 1: Bread and Butter

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

Follow the instructions in order to complete the model:

You are in the final stages of applying for a job as an analyst at a small private equity firm. The help assess your skills, the firm asks you to complete what it calls its “Bread and Butter” test – an example of the fundamental model building work that its analysts need to do day in and day out. You will need to build a dynamic financial model showing the forecast performance of a fictitious technology company they are looking to purchase, Macrohard.

The firm would like a “3-way integrated financial model” – that is a model which includes the 3 standard financial statements (Profit and Loss, Balance Sheet, Cash Flow Statement) and allows updates to assumption values to correctly flow through to all of the financial statements. In order to model the statements correctly you will also need to do some simple modelling of a debt facility and depreciation tranches. The final output of the model will be the distributions to equity investors and the IRR achieved by the equity investors.

Information is provided in the attached PDF regarding the assumed forecast performance of Macrohard as well as general model assumptions. Use your model to answer the case study questions.

To assist you, a workbook has been provided that includes some of the assumptions you will need, plus a template for the Balance Sheet, and Profit and Loss Statement. No template is provided for the Cash Flow Statement.

This package also includes a worked solution which will help you to check your answers and your technique.

Allotted time: 40 minutes

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