ALL SOFTWARE EXERCISES
- EXERCISES HOME PAGE (776)
- Access 2010 (66)
- Access VBA Macros (17)
- Advanced VBA (29)
- ASP.NET MVC (0)
- ASP.NET webforms - C# (25)
- ASP.NET webforms - VB (27)
- Excel 2010 (83)
- Excel 2013 (10)
- Excel VBA Macros (37)
- Power BI - Excel 2013 (25)
- Power BI Desktop (26)
- PowerPivot 2010 (26)
- Report Builder 3.0 (42)
- SQL (115)
- SSAS - multidimensional (21)
- SSAS - tabular (29)
- SSIS Integration Services (18)
- SSRS Reporting Services (53)
- Visual Basic 2010 (42)
- Visual C Sharp 2010 (65)
- WPF - Visual C# (20)
EXCEL 2010 EXERCISES
- Formatting your work (7)
- Basic formulae (5)
- Conditional formatting (5)
- Number formatting (1)
- Printing (5)
- Validation and protection (5)
- Range names and absolute references (9)
- IF and LOOKUP functions (15)
- Advanced lookup functions (6)
- Basic tables (4)
- Pivot tables (2)
- Charts (7)
- Building models (2)
- Cashflow calculations (1)
- Investment appraisal (1)
- Array formulae (2)
- Scenarios (1)
- Data tables (2)
- Masking (2)
- Dates and times (1)
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.
The answer to the exercise will be included and explained if you attend the Wise Owl 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!