2015 Round 1: Tax

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

Follow the instructions in order to complete the model:

You work for a company that wants to forecast its tax obligations and payments over the coming ten years, from 1 January 2016 until 31 December 2025. They have already worked out their forecast taxable profit amount in each month from 1 January 2015 until 31 December 2025, but need you to calculate the forecast tax obligations and payments for each relevant period. The company has no trading history in this country and therefore no tax payments are due from pre-2015 operations. Tax payments will commence from the 2016 calendar year, based on the taxable profit from the previous calendar year and the company tax rates and tax rules as set out in the PDF.

The monthly taxable profit figures and all the inputs below are provided in the workbook for this case study. The government has announced that the company tax rate will change frequently over the next 10 years as follows (see PDF)

For months where more than one tax rate is applicable, you should determine an average rate to apply weighted by the number of days which the relevant tax rate = applies to that month. The tax charge in each period is calculated by multiplying the applicable tax rate to the taxable profit profile given in the inputs booklet Round 1 Section 3 - Case Study Information Pack ModelOff 2015 – Round 1 The tax payable is calculated by adjusting the tax charge for losses, which are added a tax loss pool which works as follows:

 If the tax charge in a month is positive and there are no losses available in the pool, tax payable in that month is equal to the tax charge.
 If the tax charge in a month is positive and there are losses available in the pool, the tax payable in the month is the tax charge reduced by the lesser of the amount of the charge and the balance of the tax loss pool. Any losses used in this way must be removed from the tax loss pool.
 If the tax charge in a month is negative it should be added to the tax loss pool and the tax payable in that month is zero.

Assume that the balance in the tax loss pool at the start of the modeled timeline is zero. Once you have calculated the tax payable in each month, you must annualize it to calculate the company’s tax liability for a year. The company’s tax year is the calendar year. The annual tax liability is then paid in the subsequent calendar year in the following proportions

 60% in April
 25% in August
 15% in December

The following section is only relevant to questions 39-41.

The government is considering a change to tax legislation as follows:If a tax loss is not used within two years after it was created it expires and must be removed from the tax loss pool. For example, if a loss is created in April 2015 and has not been used by the end of April 2017 it will expire at the end of April 2017. Assume that losses are used and expired in order or age (oldest first). You have been asked to determine what the impact of this would be on the company’s tax payments.


Allotted time: 30 minutes

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