Chapter 4 - Modelling
Types of Modelling:
- Modelling of Objects:
- Creates a virtual representation - Model large items (e.g. building) and look at them from different external factors. (e.g. surface and environment).
- View different layers - E.g. exterior and interior.
- What-If questions can be asked - Components can be changed to see how it reacts.
- Mathematical Modelling:
- Based on functions and formulae - Allows the input of numbers into spreadsheets and for any changes to be automatically recalculated.
- Use of Graphs - To spot trends in data.
- Based on rows and columns - Items are laid out in a logical format and leads to sequencing and replication.
- What-if questions can be asked - User can change variables/values and see what the effect would be on the end result.
Chapter 4 - Modelling
Why computer modelling is used:
- Less Risk, safer and cheaper to test a model than to create it in reality and test it.
- One model is created - a real model would would cost time and money (due to alterations).
- Can be backed up and shared.
- Can be sped up/slowed down.
Chapter 4 - Variables, Rules & Functions
- Identifier associated with a particular cell that contains a value (that can change)
- e.g. a cell reference, name.
- The value within the variable is used in a function/formula
- An expression that allows calculations to be represented in a spreadsheet.
- It uses numbers, cell addresses and mathematical operators.
- If cells in the formula change then the formula will recalculate in response to the change.
- Set of procedures that must be followed
- Can also be the sequence of events required for the calculation to work.
- Predefined, complex formulae that represent standard routines used to perform common tasks e.g. SUM, MAX, MIN, AVERAGE etc
Chapter 4 - 'What-If?' Questions
'What-if?' questions are an attempt to find out what will happen in the future. Calculations are done within the spreadsheet or data model to help answer them.
How a data model answer 'What-if?' questions (Advantages):
- Models can automatically recalculate data.
- Unlimited number of 'what-if' questions can be asked.
- Custom interfaces can be built to increase usability.
- Time and cost are reduced as physical models could be costly.
Chapter 4 - Components of Spreadsheets
Workbook: A set of linked worksheets in the same spreadsheet. (Use: To divide and organise data into different categories between the sheets.)
Worksheet: A grid of cells on a single sheet. (Use: To hold data on a single area of a business.)
Row: A horizontal group of cells. (Use: To organise data)
Column: A vertical group of cells. (Use: To organise data)
Cell: An individual data store uniquely identified by a column and row indicator. (Use: Can be formatted (e.g. colour) and protected (to prevent altered data)).
Range: A group of cells that can be given a name or identified by their cell references. (Use: To contain similar data).
Chapter 4 - Cell Referencing
Absoloute Cell Referencing - When the cell referenced in a formula remains the same when the formula is copied to other cells.
- Achieved by adding "$" before the column letter and row number. E.g. $A$2
Relative Cell Referencing - When the cell reference in a formula changes when the formula is copied to other cells.
Chapter 4 - Spreadsheets to run simulations
Computer Model - Comprises a set of data about something and a set of rules that control what the data does.
- Automatic recalculation - A change in the cells then all formulae and values change.
- Graphs can be produced - Automatically change as any values change.
- "What-If?" questions can be asked.
- Quicker and cheaper than real life models.
- It can be time-consuming to produce.
- May not be an accurate representatoin of the real world.
- Some variables may be hard to represent in a spreadsheet and some missed out.