Spreadsheet Concepts

Question 1

What are the two main types of modelling software?

Answer 1

Modelling of objects and mathematical modelling.

Question 2

What is the modelling of objects?

Answer 2

Computer models allows you to create a virtual representation of the items within a computer. You can model large items such as buildings and look at the effect on them from different external influences. (Fire, earthquakes). You can zoom in to components (such as a bolt on a car) and see how they will react to different things.

Question 3

Why would you model an object?

Answer 3

To change components and see how they react. Move items and try different designs. The effect can be gauged at the touch of a button without having to take the risk of building the real thing.

Question 4

What is mathematical modelling?

Answer 4

Finance is commonly modelled with spreadsheets. They are based on a layout of rows and columns. This allows items to be laid out in a logical and easy to follow format.

Question 5

What is replication in mathematical modelling?

Answer 5

This is the copying of a cell horizontally or vertically. The value of the cell can be extended outwards. If the value is an item in a list, the next item in the list can be given in the next cell, such as day of the week. Formulae can also be copied.

Question 6

What are ‘What if…?’ questions?

Answer 6

They allow a user to change values and see what the effect would be on end results. An alternative method of asking questions is to start with the result and to see what would need to happen for that result to occur. This is known as goal seeking. It requires a value to be changed so other values are recalculated.

Question 7

V_______ and C________?

Answer 7

Variables and constants.

Question 8

What are variables?

Answer 8

A variable is a changeable value that is entered into a cell that is then used in a formula. It can be changed by a user when required which will lead to a recalculation of the figures based on that variable.

Question 9

What are constants?

Answer 9

Constants are values which are used in formulae but can’t be changed by the user. VAT rate (17.5%) is an example of a constant.

Question 10

NOTE:

Answer 10

You can rename cells. So C10 could become VAT_RATE if needed, which may make it easier for you to understand later.

Question 11

Why is computer modelling used?

Answer 11

It is safer, cheaper and less risky to test a model of a design instead of creating it in reality. You only need to create one model as it can be altered and changed, instead of creating a new one in reality for each alteration. A computer model is also stored electronically which has its own advantages.

Question 12

What are the four main characteristics of a model?

Answer 12

Variables, formulae, rules and functions.

Question 13

Describe variables.

Answer 13

A variable is an identifier associated with a particular cell. Within the cell there will be a value. The variable could be a cell reference, like D4. When the variable is used in a spreadsheet, it is the value contained within the variable that is used.

Question 14

Describe formulae.

Answer 14

A formula is the way that a calculation is represented in a spreadsheet. Formulae use numbers, addresses or cells and mathematical operators ( + / * - ). An example would be A12+(A12*VAT_RATE).

Question 15

Describe rules.

Answer 15

Rules are a set of procedures that must be followed, if a calculation requires two values, these values must be supplied. A validation rule can be applied to make sure that the value is given.

Question 16

Describe functions.

Answer 16

A function is used to represent a formula that is too complex or too long to expect an ordinary user to enter. A function uses reserved words that are built into the spreadsheet such as SUM, MAX or LOOKUP.

Question 17

What if…? NOTE:

Answer 17

The only cost is time. Once the model has been created it will answer as many ‘What if…?’ questions as you want. But it is still likely to be quicker than creating physical models. And if there is an error, it is also quicker to edit on a computer.

Question 18

Advantages of using a spreadsheet to create and run simulations:

Answer 18

‘What if?’ questions can be asked without the need to rebuild a model each time. Automatic recalculation if one change is made to a variable. Graphs can be produced. The model can be saved and backed up. Electronic. No additional software is required, spreadsheets are standard applications. Only one model needs to be built. It is safer to do it on a computer (testing a ship in a storm).

Question 19

Disadvantages of using a spreadsheet to create and run simulations:

Answer 19

The model may not be a 100% accurate representation due to unforeseen complications. Many variables need to be considered and it is easy to miss things out, thus misleading results. Time consuming and you need the knowledge.

Question 20

Describe a worksheet in spreadsheet software.

Answer 20

A worksheet is a large grid of cells on a single sheet. A worksheet can be used to hold data on a single area of the business. For example, it can hold the sales data, the expenditure or the stock. They can be given names.

Question 21

Describe a workbook in spreadsheet software.

Answer 21

A workbook is a collection of more than one worksheet in the same spreadsheet. Separate worksheets could contain financial figures for different areas of a business. Together they comprise a workbook and contain the figures for the whole business.

Question 22

What is the main advantage of using a workbook in spreadsheet software?

Answer 22

If the data is changed on one worksheet, it will be reflected across the whole workbook. As it is saved as a single entity, it is easy to back up, copy and send.

Question 23

Describe rows and columns inside spreadsheet software.

Answer 23

Rows are given numbers and columns are given letters. They’re used to organised data and can hold headings to show where the data is stored. Their size can be adjusted and they can also be hidden.

Question 24

Describe a cell inside a spreadsheet software.

Answer 24

A cell is an individual data store identified by a column and row indicator. They can be formatted. And can also be protected so that they can’t be edited without a password.

Question 25

Describe range within a spreadsheet software.

Answer 25

A range is a group of cells. The group can be given a name or just known by it’s cell references. A range is given top left to bottom right, separated by a colon so… A4:B6.

Question 26

What is relative cell referencing?

Answer 26

This is when the cell referenced in a spreadsheet formula changes when the formula is copied to other cells. This means that when a formula or function is copied, the cell reference within the formula will move.

Question 27

An example of relative cell referencing:

Answer 27

If a formula in cell A3 has been copied to the right into cells B3, C3 and D3, each time relative addressing ensures that the columns referenced in the formula are also changed by the same amount.

Question 28

What is absolute referencing?

Answer 28

Used when a referenced cell in a spreadsheet formula needs to remain exactly the same when copied elsewhere. A cell may contain a constant value such as the VAT rate, so the cell will always be referred to in calculations that use it.

Question 29

An example of absolute referencing:

Answer 29

By putting a $ sign before the column and row parts of the cell references (=$A$1+A2).

Question 30

Where is absolute referencing used?

Answer 30

When a value is used in the same formula or function many times.