Excel | Building models exercise | London Olympics - 2007 base model

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Software ==> Excel  (151 exercises)
Version ==> Excel 2010 and later
Topic ==> Building models  (2 exercises)
Level ==> Average difficulty
Subject ==> Excel training
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

You have been commissioned to produce a simple (!) model to calculate the investment return on the London Olympics 2012.  First open the workbook in the above folder, and use the Window menu to freeze the windows on the Inputs and Calculations sheets as appropriate.

Following strict modelling best practice, create the following revenue inputs:

  • Ticket sales for each of the 8 years (enter 0 for years 1-5, then 1,000,000, 2,000,000 and 3,000,000 for the last 3 years leading up to and including the Olympics themselves).
  • A single-figure input showing the price of a ticket (assumed to be constant throughout the 8-year model - enter this as £60 in a single cell).
  • Sponsorship income (starting at £100,000,000 in year 1, and rising in £50,000,000 increments to £450,000,000 in year 8)

Still following modelling best practice, create the following cost inputs:

  • Running costs - these should be variable, but initially enter £250,000,000 in each of the 8 years.
  • A single-cell cost escalator, entered as 10% initially - this is the cost overrun multiplier to apply to the basic running cost figures.

Your model should now look something like this:

Excel 2010 exercise - Building models (image 1)

Your formatting and range names may be different

Create 3 calculation lines giving total revenue, total costs and net cash for each year, and apply number formatting - your final answer should look like this:

Excel 2010 exercise - Building models (image 2)

Numbers are scaled by a million using the , symbol

Apply conditional formatting to make the cash figures stand out when they exceed £150,000,000:

Excel 2010 exercise - Building models (image 3)

One suggestion for a format to use

Save your workbook with the same name, then close it down!

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 0 threads Add post