2015 Round One: Options to Call

Description
This workbook is taken from the 1st round of the 2015 Modeloff financial modeling world chamionships.

Follow the instructions in order to complete the model:

You have just moved to a new country and need to sort out a mobile phone contract. You have identified six carriers in the local market, each offering a single mobile phone plan. Each plan has an included number of minutes to make phone calls, an included number of text messages and an included allocation of data. Each plan also has rates that apply once the included allocations have been fully used. You will need to model each of these mobile plans over a 36 month period to help determine which will be the best mobile plan for your needs. The evaluation period will be from 1 January 2016 until 31 December 2018.

The key details for each of the plans are summarised in a table within the attached PDF file.

All costs and values are monthly unless stated otherwise. All contracts have no minimum length and no break costs for cancellation. Any unused minutes carry forward and can be used in future months. Carried forward minutes do not expire. Where noted, unused text messages also carry forward to be used in future months. Any carried forward text messages also do not expire. Data allowances do not carry forward. Additional data is charged in packs, with a fixed cost for every additional pack of data required. You cannot purchase a partial data pack.

Each of the plans has an incentive to encourage new customers to sign up. The details of these incentives can be found within the attached PDF document.

YOUR USAGE

You expect:

 your standard monthly usage to be 300 minutes, 1,000 text messages and 2,000MB of data.

 to use 1.5x your standard amount of minutes, text messages and data every June and July and 2x your standard monthly usage of minutes, text messages and data every December.

 your standard monthly usage to increase by 5% per annum (effective from 1 January each year). For the purpose of this analysis, partial minutes, text messages and MB of data are allowed (i.e., do not round this annual increase). Assume that partial additional minutes and partial additional text messages can be purchased at a pro-rata price.

Allotted time: 30 minutes

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