2016 Round 2, Section 4: Maximize the Benefit

Excel training/competition model from the 2017 Financial Modeling World Championships

by Full Stack Modeller
To help finance professionals transform their Excel, financial modelling, data visualisation & analytics skillsFollow 72

5 reviews462 views|Start the discussion!|Bookmark download for free

Description
This workbook is taken from the Second Round of the 2016 Modeloff financial modeling world chamionships.

Follow the instructions in order to complete the model:

You are working for a vehicle parts manufacturer called Parts 4 Less (“P4L”). P4L are investigating the construction of a new plant to produce vehicle parts. P4L operate in a country where there are four different methods possible for tax depreciation. P4L can choose to depreciate under any of these four methods. Once selected, which method is chosen can not be changed and must be used for all asset
classes.

You will need to develop a model to forecast the depreciation and net book value of P4L’s assets under each method. The model will be annual and cover 20 years from 1 January 2017 until 31 December 2036.

P4L will own assets in five different classes (A, B, C, D, E). The new plant will have capital expenditure across each of these five asset classes. There is no existing asset balance for any asset class.

The capital expenditure profile for each asset class is provided in the supporting Excel file. This capital expenditure is real as at 1 January 2017. These values should be inflated by 2% per annum on 1 January of each year from 1 January 2018 onwards.

Assume all capital expenditure occurs at the start of the year. There is no salvage value or residual value for any asset class.

ASSUMPTIONS
• The corporate tax rate applied to the depreciation calculations is 32%.
• When comparing depreciations methods, use a discount rate of 11% per annum and calculate your discount factors as 1 / (1 + r) ^ n. Assume all depreciation charges and tax flows occur at the end of the period when discounting

Allotted time: 36 minutes

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

This Best Practice includes
1 excel workbook and 1 PDF