- Excel basics (1)
- Creating formulae (5)
- Formatting worksheets (9)
- Basic printing (8)
- Charts (10)
- Absolute references (9)
- Range names (13)
- Conditional formulae (17)
- Conditional formatting (7)
- Basic tables (6)
- Formatting numbers and dates (3)
- Working with dates (1)
- Data validation (6)
- Protection (1)
- Advanced IFs (6)
- Lookup functions (14)
- Advanced lookup functions (9)
- Text functions (1)
- Advanced charts (4)
- Multiple worksheets (1)
- Advanced tables (2)
- Pivot tables (3)
- Advanced pivot tables (3)
- Scenarios (2)
- Data tables (2)
- Array formulae (2)
- Building models (2)
- Masking (2)
- Cashflow calculations (1)
- Investment appraisal (1)
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.
You can learn how to do this exercise if you attend the course listed below!
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:
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:
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:
One suggestion for a format to use
Save your workbook with the same name, then close it down!