5. SPREADSHEET MODELLING

The unit is centered around creating a financial model for a TV show. You start by looking at different types of model and then use basic spreadsheet techniques to create and format a simple financial model to calculate the expected income from viewers’ voting. The model is then extended to include sales from merchandising, with the introduction of “what if” scenarios. Finally, you create a seating plan, book seats and calculate income from seat sales. Spreadsheet features covered include SUM, MAX, IF and COUNTIF functions, cell naming for absolute referencing, conditional formatting, validation, charting and simple macros.

1. COMPUTER MODELS

Understand what is meant by the term computer model, and compare different types of model
Understand that spreadsheets can be used to build financial models
Revise spreadsheet basics: entering text, numbers and formulae
Use relative and absolute referencing
Format cells, insert a graphic

LESSON PRESENTATIONLESSON FILESLOGOS

2. Creating a financial model

Create a financial model to predict the profit on the sale of merchandise
Make the model as realistic as possible based on known sales figures and prices
Consider ways of increasing profit to meet a given target

LESSON PRESENTATIONLESSON FILESWATCH THIS LESSON

3. ‘What if’ scenarios

Use a spreadsheet to model outcomes
Use functions including Max, Min and If
Name a cell
Sort data into different sequences
Try out different ‘What if’ scenarios to achieve a goal
Display the formulae in a spreadsheet

LESSON PRESENTATIONLESSON FILES

4. Conditional formatting and validation

Create a seat booking system for a live show
Use a validation rule to ensure that only valid data can be entered
Use conditional formatting to show which seats have been booked
Use a Countif function in calculations of seat sales

LESSON PRESENTATIONLESSON FILES

5. Macros and charts

Create a macro to reset all seats to unbooked
Assign the macro to a button
Create and customise a pie chart to show the number of seats remaining

LESSON PRESENTATIONLESSON FILES

Page updated

Google Sites

Report abuse