3.8 Spreadsheets, modelling and simulations


The increasing capabilities of computers have allowed individuals and organizations to develop software
that can be used to test “what-if” scenarios and create simulations and models of real-world events.
Spreadsheets, through the use of worksheets and graphs, can be used to manage, predict using a series of
“what-if” scenarios, and display financial details of businesses.
Modelling and simulations can be used to recreate or predict the conditions that may result from an
event, for example, the areas that will be affected by coastal flooding as a result of different levels of global

It is important that the ITGS student is aware of the benefits of creating accurate spreadsheets, models and
simulations as well as the social impacts that could result from simulations being unable to replicate the real
world, and the ethical issues that may arise during the development of the model.

IT concepts to address in this topic

Theoretical and practical concepts for spreadsheets

Cell types: for example, text, number, date, currency, hyperlinks

Each cell in a spreadsheet can only deal with one type of data at a time. For example, it is not possible to enter text and numbers in the same cell, and then be able to use the number in a calculation, as the entire content of the cell would be treated as text.
Spreadsheets allow you to use formulas to perform repetitive calculations. Formulas use cell names to perform calculations.
Answers to formulas will change when values in the cells it refers to change, which makes a spreadsheet dynamic and efficient.
The five basic types of data which can be stored in a spreadsheet cell are:
A text element can contain a series of letters or numbers. To distinguish text elements from number or formula elements, the text starts with a single quote.
A number element can contain a series of digits (425) but may include specific text and formatting characters to indicate negative numbers (-345), decimal separator (34.0567), thousand separators (12,342), currency ($23), dates (21-10-1998), times (10:23) or scientific notation (2.3e12).
A boolean element can contain one of two values: TRUE and FALSE. These are useful as inputs or outputs from formulas and for boolean algebra.
A formula is an instruction to perform a calculation automatically. These formulas can contain standard arithmetic elements or references to other cells. Calculations which depend on other cells are usually recalculated when the values of another cell changes.
Formulas begin with the equals (=) or at signs (@).
A cell reference is the part of a formula which refers to another cell. For example, in the formula =(A4+A1), both A4 and A1 are cell references. These references can be quite complex referring to cells in different worksheets or even in different files.
An error element describes the failure to calculate the result of a formula. A cell may display a series of hash marks (######). This indicates that the result is too wide to display in the cell given the current font setting and the current column width.
When this occurs, the value in the cell can be seen in two ways. If the cell is selected, the value will appear in the data entry area. Alternatively, the column containing the cell can be widened until the data contents become visible.


For clear examples of each data type, see:

Formulas: relative and absolute cell references

There are two ways of using cells in formulas: relative and absolute.
Relative references (to cells) change when a formula is moved to another cell. This feature saves you having to work out a different formula for an identical calculation. Absolute references to cells will always refer to exactly the same cell, even if the entire formula is moved or copied to another cell.
There are two main types of formula that you can use in a spreadsheet: logic and mathematical functions.
Logic functions help the spreadsheet decide how to act, e.g. the IF function.
Mathematical functions use data in the spreadsheet to calculate numerical answers, e.g. the SUM function.

Sorting, filtering and replicating data

Simple practical guides on sorting and filtering data in a Miscrosoft Access:

Types of charts

Charts are visual representations of worksheet data. Charts often makes it easier to understand the data in a worksheet because users can easily pick out patterns and trends illustrated in the chart that are otherwise difficult to see.

Bar Graphsbar_chart.jpg
Sometimes known as "column charts", bar graphs are most often used to show amounts or the number of times a value occurs.
The amounts are displayed using a vertical bar or rectangle. The taller the bar, the greater number of times the value occurs.
For example, for a school class you can use a bar graph to show and compare the number of students with different hair colors. The more students with a particular hair color, the taller the bar for that color will be in the graph.
Bar graphs make it easy to see the differences in the data being compared.

Line Graphs
Line graphs are often used to plot changes in data over time, such as monthly temperature changes or daily changes in stock market prices, or to plot data recorded from scientific experiments, such as how a chemical reacts to changing temperature or atmospheric pressure.
Similar to most other graphs, line graphs have a vertical axis and a horizontal axis. If you are plotting changes in data over time, time is plotted along the horizontal or x-axis and your other data, such as rainfall amounts is plotted as individual points along the vertical or y-axis.
When the individual data points are connected by lines, they clearly show changes in your data. You can use these changes to predict future results.

Scatter Plot Graphs
Scatter plot graphs are used to show trends in data. They are especially useful when you have a large number of data points. Like line graphs, they can be used to plot data recorded from scientific experiments.
Whereas line graphs connect the dots or points of data to show every change, with a scatter plot you draw a "best fit"line. The data points are scattered about the line. The closer the data points are to the line the stronger the correlation or affect one variable has on the other.
If the best fit line increases from left to right, the scatter plot shows a positive correlation in the data, and vice-versa.

Excel Pie Charts

Pie charts can only be used to show percentages.The circle of pie charts represents 100%. The circle is subdivided into slices representing data values. The size of each slice shows what part of the 100% it represents.Pie charts can be used anytime you want to show what percent a particular item represents of a data series such as:

A baseball player's batting average can be shown with a pie chart as it represents the percentage of hits when compared to his total number of at bats for a season.
A company's profits for each month can be shown with a pie chart as a percentage of the year's total profits.

Source: about.com
Create a Column Chart in Excel 2007
Create a Line Graph in Excel 2007
Create a Pie Chart in Excel 2007

Formatting and presentation: for example, text (fonts), background, paragraphs, pages

A page dedicated to improving the presentation of the worksheet. Explanation on:
Formatting the toolbar
Formatting the cell
Copying the presentation format as a template
More advanced concepts
Conditional format


Data validation

Validation helps to reduce the number of errors made when entering data into a database and choosing the correct data types, by checking whether data is sensible.
However, validation cannot stop the wrong data being entered, you can still enter 'Brown' instead of 'Green' or '78' instead of '87'.
Validation: teach-ict.com
Types of validation: bbc.co.uk:
  • Check digit
  • Format check
  • Length check
  • Lookup table
  • Presence check
  • Range check
  • Spell check

Data verification

Verification: teach-ict.com
Verification means to check the data that you have entered against the original source data.
Think about when you choose a new password, you have to type it in twice. This lets the computer check if you have typed it exactly the same both times and not made a mistake.
There are two main methods of verification:
1. Double entry - entering the data twice and comparing the two copies. This effectively doubles the workload, and as most people are paid by the hour, it costs more too.
2. Proofreading data - this method involves someone checking the data entered against the original document. This is also time consuming and costly.

Functions: maths

Excel's Math Functions can be used to perform common mathematical operations such as addition (SUM function), multiplication (product function), and rounding numbers (ROUND function).
Excel Math Functions List: excelfunctions.net
Excel Math functions and official tutorials: office.microsoft.com


Functions: text

Built-in Excel Text Functions. These functions are provided by Excel, to help you to work with text strings. They include functions to return information about a text string, to apply formatting to a text string, to convert between text and other data types, and to cut up and join together text strings.
Excel text Functions List: excelfunctions.net

Formatting and presentation: for example, text (fonts), background, paragraphs, pages
Data validation, verification and testing
Functions: maths, text, logic, date
Protection for sheets and workbooks, cell locking
Advanced functions: for example, lookup, pivot tables, macros
Worksheet modelling: “what-if” analysis (scenarios, goal seek tool)

Modelling and simulation technologies and considerations


A computer model is a computer program designed to behave like a real-life system. It allows the user to predict how certain changes would affect other parts of the system.
'Tomorrow's Technology and You', George Beekman & Ben Beekman


A simulation is the operation and application of a computer model to analyze and evaluate the performance of a system. .It allows you to make improvements and adjust any parameters within the system.


Types of simulations and models

external image climate-model-2.jpg
Weather modelling:

A computer model of a weather system can be used to predict changes in the world's wind patterns, temperatures...etc. This can alert people in advance of any extreme changes, allowing them time to make any necessary preparations.

Design modelling:

external image stacks_image_8957_1.png
e.g. Bridge designing, Car designing...

These models can be used to test the weaknesses and strengths of designs of structures to avoid having to create and then re-create expensive and time-consuming prototypes.

Business modelling:

external image stacks_image_8968_1.png
A computer model of a business can be used to predict future profits or losses for a business. The models work by answering 'what if?' questions. For example, "What if we decrease the workforce by 15% Will our profits increase or decrease?" The managers of the business can then analyze the answers to these questions and make decisions based on them.


Virtual reality, augmented reality, gaming: for example, MMORPG (massively multiplayer online roleplaying game)

external image 300px-VR-Helm.jpg

Virtual reality refers to computer-simulated environments that imitate a person's physical presence in a situation, in either in a place resembling one in the real world, or in an imaginary world. Most of these virtual reality environments are primarily visual experience. This visual experience is either displayed on a computer screen or through special stereoscopic displays. However, some may also include other sensory information, for example, sound, which would be output through speakers or headphones. MMORPGs (massively multiplayer online roleplaying games) such as 'World of Warcraft', are an example of a virtual reality.

external image 220px-Wikitude.jpg

Augmented reality refers to view of a physical, real-world environment which is enhanced by a computer-generated sensory input, such as sound or graphics. It differs from virtual reality as it augments one's current perception of reality, while virtual reality replaces it with a computer simulated version.


Graphics and animations (2D, 3D)


What's the Difference between 2D and 3D Graphics? This short video gives a basic explanation of each:

Summed up, 2D = height and width, 3D = height, width and depth.

The following link describes how modelling software is used to create a 3D computer-generated object:


Visualization of data

external image images?q=tbn:ANd9GcRarLWnxb5UuiklagR_LJQQOD_OiwIzISEgqDuSqZMKd3g870YFuA
Data visualization is the graphical representation of data, with the goal of providing the viewer with a qualitative understanding of the information presented. According to Friedman (2008) the "main goal of data visualization is to communicate information clearly and effectively through graphical means".

Feedback loop

A feedback loop is a situation when the output from a system will control future processes carried out by the system. It is a mechanism, process or signal that is looped back to control a system within itself. If the output of the system causes an increase in the input it is called positive feedback, and if it decreases the input it is called negative feedback.

external image 300px-Ideal_feedback_model.svg.png

Developing and using models and simulations

The validity of the model and verification of the results of a simulation, reproducibility of results

Relationship of model to reality

Relationship between a model and a simulation

Best content in StJulians_ITGS | Diigo - Groups