2014 Round 1: Dealing with Data

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

Follow the instructions in order to complete the model:

INTRODUCTION

Just over a year ago, you had a smart electricity meter installed at your home. This particular model of smart meter reports your usage to your electricity provider every hour. Your electricity contract is due for renewal and being a top financial analyst, you decide to identify which contract will minimise your annual electricity cost.

There are 3 different types of electricity contracts available to you. Each of these different contracts are layed out within the attached PDF file.

To decide which contract would be optimal, you decide to compare the costs under each of the contracts assuming your usage remains exactly the same as last year. You have contacted your current electricity supplier to obtain your electricity usage history for the last year. Unfortunately they provided it to you in a poorly structured and unsorted format, so you need to clean it up prior to doing your analysis. In the data the hour field identifies when the hour starts, so 8am would be from 8:00AM until 8:59AM.

Supporting data:

You have placed your usage histyory and the specifics of each of the electricity contracts into the supplied workbook. Your usage history is located on the '"Usage" sheet while the specifics about each of the electricity contracts is located on the "Contracts" sheet.


Allotted time: 35 minutes

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