233
9.1 Modelling
9 Modelling
9.1 Modelling
Modelling means many things to many people. Many students confuse
computer modelling with physical modelling, such as creating prototypes.
A prototype can be a physical model which has been built to a smaller scale
for use in trials and testing. Prototypes can be very costly since they can be
damaged during testing and may have to be rebuilt, which means that extra
raw materials have to be bought. In more recent times it has become possible to
use a computer model to test new cars, for instance, at the design stage without
having to build and then damage actual cars.
For our purposes, modelling means the use of computers to represent real-
life situations. These models are usually financial, scientific or mathematical.
It is the creation of a computer-based simulation to see what would happen
in certain circumstances. It is not the creation of a working model, like the
prototype of a new car or a model of a new building, which are also known as
3-D (three-dimensional) models.
Computer models can be complex and often require expensive purpose-built
software and a mainframe computer to operate. Spreadsheet software can
also be used to create a computer model, but often businesses require a more
sophisticated method or solution.
Before starting this chapter you should:
+ have studied Spreadsheets (Chapter 8).
For this chapter you will need these source files:
Q TuckShop.csv Q Widget.csv
In this chapter you will learn:
+ about what-if analysis
+ about the characteristics of modelling software
+ about the need for computer models
+ about the effectiveness of spreadsheet models
+ about the use of a model to create and run
simulations
+ about a number of situations where
modelling is used
+ how to use what-if analysis
+ how to test a spreadsheet model.
234
9 MODELLING
9
9.1.1 What-if analysis
Later in this chapter you will see how to use what-if analysis, but before you do
it is worth considering what it is. It is basically asking a question such as what
will happen if I perform a certain action. For example, if you have $5, what will
happen if you spend $4? The answer is quite easy to predict; you will have $1
left. Unfortunately, not all situations are so straightforward.
Consider a company which is making steel ingots. It employs a number of
workers in its foundry to produce the ingots, and a number of office staff to deal
with company orders and organise the payroll. It also has an office manager and
a foundry manager.
Look at this spreadsheet which displays data for one week. It shows the
employees and how much they earn. It also shows how many ingots are
produced and how much money they are sold for. In order to work out the
company’s weekly profit, the running costs have to be taken into account.
Fortunately, in our model, this figure does not change from week to week.
What-if analysis can be used on this spreadsheet. The foundry workers have been
complaining for some time that they are not paid enough. The owner of the
company, Morag, could use the spreadsheet to perform some what-if analysis.
She could ask the question ‘What would happen to my profits if I increased
their pay from $13 to $14?’ This would be easy to do just by changing the value
in cell B2 to 14. The value in cell E23 would change, revealing the new profit.
In this case profits would reduce from $1580 to $1412. She could
then ask the question ‘If I now increase the price of the ingots to $12.50,
235
9.1 Modelling
9
would that make up the difference?’ The answer is that it would make more
money. She could experiment with this model to see the effect on her profits of
increasing the wages of any, or all, of the workers. She could also investigate the
effect on her profits of increasing the price she sells the ingots for, or indeed of
the company producing more ingots.
Another use of this spreadsheet could be to use the
Goal Seek feature. You will
learn how to use this later in this chapter. For now, we will concentrate on what
it can let you do. It enables you to be more precise when you are using what-if
analysis. Suppose you know the value you want a formula to produce; however,
you want to know what values to enter into the formula. This is where Goal
Seek helps. The Goal Seek feature, available in most spreadsheet software, is a
what-if analysis tool that enables you to find the input values needed to achieve
a goal or objective. To use Goal Seek, you select the cell containing the formula
that will return the result you are seeking. You then input the value you want
the formula to return and enter the cell reference of the input value that the
spreadsheet can change to reach the target. In our example spreadsheet above,
Morag could change the value in B2 to 14, as before, but then use the Goal
Seek feature. She could enter the required profit as $1580. She could then ask
it to tell us how many ingots (cell F2) the company would have to produce to
keep the profit at $1580. Alternatively, she could ask it to tell us what price she
wouldneed to charge for each ingot (cell F3), if the number of ingots produced
was left at 919. Goal Seek will be studied in more detail later in the chapter.
9.1.2 The characteristics of modelling software
As in most systems, modelling software has inputs often using the keyboard
or prepared data files, a set of embedded rules used to manipulate the data,
and outputs. There is a significant difference between a computer model and a
calculation. Sometimes both models and calculations process large quantities of
numeric data. However, the rules governing the model can be changed, whereas
they cannot be changed in a calculation.
Spreadsheet software is often used to create computer models. The
characteristics of spreadsheets which make them so suitable for this purpose
include the ability to replicate formulae throughout a model but also influence
this replication through the use of absolute and relative cell referencing. This
makes sure you only increment the parts of a formula you need to by using
relative cell referencing. The use of absolute cell referencing ensures that those
parts of the formula you wish to remain constant do so.
Another characteristic is the use of cell protection. This ensures that the cells
which are crucial to your model are not changed accidentally by someone who is
using the spreadsheet.
There are also user interface forms. These make it easier to input values into the
model by showing clearly where data is to be entered.
With spreadsheets you can create macros. Macros are a way of storing the
keystrokes you make on your keyboard. They make it easier to create more
complex formulae or functions.
One spreadsheet characteristic that makes it easy to use models is automatic
recalculation. This means that when you alter data in a spreadsheet any formula
which makes use of that data is automatically recalculated. It is not necessary
to evaluate a formula every time you change data. Conditional formatting in
spreadsheets allows you to highlight certain values that match specific criteria.
236
9 MODELLING
9
This means that you can see when positive values produced by a formula change
to negative if a formula is recalculated. You can see the change instantly.
Spreadsheets also make use of graphs or charts to reveal trends over a given
period of time.
9.1.3 The need for computer models
There are a number of reasons why computer models are useful and necessary.
Cost
As has already been implied, one of the major reasons we need models is because
of cost. We always have to pay for our mistakes. If engineers are designing
structures such as bridges and high-rise buildings, or designing cars and planes,
it is very expensive to correct mistakes. If a computer model is used, then the
costs of any mistakes practically disappear.
Testing
Another reason is the ability to test the design. It would be impossible to build
several different types of building just to see which is most likely to survive
heavy flooding. However, it can be done relatively easily with a computer model.
Prediction
It is impossible to travel through time, so in order to predict events over a large
time span, such as climate change or population growth, computer models are
used as they are the best way of making predictions. Another use of computer
models is to accurately predict when storms will occur and the severity of them,
thus enabling early warnings to the public about how to protect themselves and
their property.
Time-efficiency
Modelling also saves time as well as money. Car manufacturers can perform
more tests in a shorter space of time with a computer model than by arranging
tests of prototypes with crash-test dummies inside them over and over again.
Computer models make it possible to run many more tests.
Planning
Simulations are a specific type of computer model and are very useful in
planning for rare or unexpected events. For example, they can be used to
help predict and plan for the after-effects of natural disasters, such as volcanic
eruptions, earthquakes, and tsunamis.
Safety
Some situations are dangerous, such as experimenting with a nuclear reactor.
Acomputer model is a much safer way to do this.
9.1.4 The effectiveness of spreadsheet models
You learnt in Chapter 8 how to create, edit, and use a spreadsheet. Here we are
going to learn about their benefits and drawbacks.
Benefits of spreadsheets
One benefit you will already have noticed is that calculations can be performed a
lot more quickly than if you were writing them down or even using a calculator.
237
9.1 Modelling
9
One of the reasons for this is that
formulae are recalculatedautomatically.
Imagine you have a cell, E2, with a
formula in itsuch as B2*C2*D2, like this.
Every time you type a new value in
cell B2, the formula will automatically
recalculate and produce a new value in
E2. Here you could type 7into cell B2
and the answer 168 would automatically
appear in E2. If you were doing it
manually, even on a calculator, it would be slower as you would have to type in
7*4*6. Obviously, the more complex the formula, the more time you save.
It is also possible to automatically import data into a spreadsheet from a
database. Suppose you want to create a spreadsheet and you already have a
database which contains the data you are going to use. Most spreadsheet
software allows you to select the database as your source and then load the data
directly from that database. This saves you the trouble of having to manually
input data into your spreadsheet.
Spreadsheet models are very useful for observing patterns which take place
over a long period of time. They are particularly useful when studying animal
population growth. To observe population growth in real time would take many
years. If an accurate computer model is created, the results will be known quite
quickly. Also, if certain variables changed within the animal’s environment, the
effect would be noticed much sooner. Otherwise, waiting for the results could
take years to happen in real life.
Another useful property of spreadsheet models is the automatic updating of
graphs. In a business spreadsheet, for example, graphs might be produced
which show the profits, output, costs, and income, among other variables. It is
usual to show the trends over a period of time. These are useful when making
predictions about the future prospects of a company. Often, what-if scenarios
are carried out, with some values within the model changing in order to show
how this would affect future trends. Any graphs which show these trends are
automatically updated as values are changed within the model. New graphs are
produced automatically.
Another feature of spreadsheet models is that they can remove human fallibility.
This means that the likelihood of decisions being made based on personal
feelings is no longer a problem. It is still the case that, for example, individual
representatives of banks or credit unions are faced with making a decision
regarding offering a mortgage or loan to someone so the person can buy a
house. Sometimes the decision is a personal one and could even depend on how
the representative was feeling that day! More and more, spreadsheet models
are being used to make these decisions. The obvious benefit of this is that the
decisions made by spreadsheet models are consistent for the same inputs, which
is not always the case with different humans.
Another benefit of spreadsheet models is that templates exist for regularly used
spreadsheets. Users can save time setting up the spreadsheet by using a template
which might just need to be slightly amended to suit the particular situation.
Users do not always have to be experts because the templates already exist which
means they do not need to have great computing expertise.
Validation rules can also be built into spreadsheet models. This means that
data input errors are reduced as the validation will ensure only data which is
238
9 MODELLING
9
reasonable is allowed to be entered. This would have saved the ticket organisers
of the London 2012 Olympics™ a lot of embarrassment. There were often issues
with people not getting the tickets they asked for. Some of this was due to the
high demand for tickets. However, the Olympic Committee sold more tickets
than the number available for some swimming events. It was reported that the
error occurred when a member of staff typed in ‘2’ instead of ‘1’ and entered
‘20000’ into a spreadsheet rather than the correct figure of 10 000 remaining
tickets. The error appeared when organisers compared the number of tickets
sold with the actual seating arrangements at the venues. They had to get in
touch with the ticket holders and try to persuade them to accept seats at other
venues. Of course, if proper validation procedures had been in place, this would
never have happened.
Drawbacks of spreadsheets
However, there are some properties of spreadsheet models which reduce their
effectiveness. One such problem occurs because it is very difficult to include
every possible variable in a spreadsheet model. In population growth models,
it is very difficult to accurately predict patterns. There are numerous variables
that affect the study of a particular species; factors such as the number of
predators, birth rate, illness and so on, all need to be considered. In addition,
the variables involved tend to be considered only at the present time. It may
be difficult to then predict what the variables may become over the length
of time the model is covering. It becomes easy to leave out some of these
variables.
In financial spreadsheet models, there may be a number of variables which are
fairly straightforward to identify. However, there may be problems in predicting
future patterns of spending, borrowing and investing, as it is difficult to
accurately model human behaviour; it is just not possible to quantify or turn it
into a number.
Another aspect of modelling which makes it difficult to predict trends is the
effect that sudden emergencies will have on real-life behaviour. The banking
crisis of 2008 forced governments to make decisions. Some governments reacted
by putting more money in to their economy. Other governments cut spending.
There was no way of predicting how individual governments would react. As a
result, economic spreadsheet modelling became problematic.
Some situations will need software to be specially designed and/or computer
experts to create a model, and neither of these is inexpensive to purchase
or invest in. Spreadsheets are often used for implementing a new financial
recording and reporting system. Aspects such as invoicing can be complex if
different types of invoices and methods of producing these are required for
different companies. In this case, it is often easier to bring in expertise from
outside. Another aspect that spreadsheet users sometimes fail to take into
account is the fact that it may take a very long time to produce a complex
model. Various phases are used when producing a model. The problem has to be
identified and broken down into tasks which make it easier to solve. The inputs
and outputs of the model have to be identified. The user requirements have to
be taken into consideration. The spreadsheet model has to be created and then it
needs to be tested. Documentation for the user has to be produced. Finally, the
model has to be improved and updated as necessary. All this contributes towards
the effectiveness of the model, but it can take time.
When an expert has been through all these stages, the staff at the organisation
which is going to use the spreadsheet need to be trained to use this model. This,
239
9.1 Modelling
9
of course, creates an extra expense to the organisation on top of paying the
creator(s) of the spreadsheet model.
One other factor which affects the effectiveness of a spreadsheet model is
human errors introduced to the model by users. This can cause even the most
sophisticated model to give out inaccurate results when incorrect data is input.
Remember the mistakes made at the 2012 Olympics when a member of staff
typed in ‘2’ instead of ‘1’ and entered ‘20000’ into a spreadsheet rather than
the correct figure of 10 000 remaining tickets? Another problem can occur due
to the complexity of some spreadsheets. We have seen one of the key phases of
producing a model is the testing of it to make sure it does what we want it to.
Having data spread over different workbooks and worksheets can make them
very difficult to test thoroughly.
9.1.5 Uses of computer models
There are many types of computer model. Here, we will look at some examples
of the uses of modelling. Remember, you will be required to apply and adapt all
the knowledge you have acquired in the preceding sections: what-if analysis, the
characteristics of modelling software, the need for computer models, and the
effectiveness of spreadsheet models for each of the following uses.
Financial forecasting
Earlier in this chapter we looked at Morag’s company and we saw a simple
spreadsheet model which could be used for financial forecasting. Companies
need to be able to make a profit. It is important to them that they can model
possible future events. Our model was fairly simplistic but demonstrated how a
model can be used to predict profits. It involved calculating the costs involved in
producing ingots and subtracting these from the income obtained when selling
the ingots. This type of model tends to make use of what we call off-the-shelf
spreadsheet software. This is software which has been produced for general use
and does not take into account the needs of specific users.
Details of income and outgoing costs were entered into the spreadsheet and a
formula linking the two was used to calculate the profit. We can see, using the
model, the effect on the amount of profit a company makes of changing the
variables. This is making use of the what-if feature of spreadsheets. It is also
making use of the automatic recalculation feature of spreadsheets which means
you do not have to ask the computer to run the calculation every time you
change individual prices. The fact that we can change the variables is the most
important property of spreadsheets when using them as models. User interface
forms make it easier to input quantities/costs into the model.
Conditional formatting is another spreadsheet feature that is beneficial in
this context. It allows you to highlight particular values that match particular
criteria. For example, you can set cells which are showing a profit to a particular
colour. This enables you to see immediately which items are making a profit.
However, most companies require more intricate models than this. Companies
need to be able to predict the effects of changing the rate of selling a product. Sales
of certain products vary depending on the time of year. There is no point spending
money on producing a certain number of a type of product if it is not going to sell
anywhere near that amount. The company would end up making a loss.
In our model, we assumed that the company’s overheads were fixed. This is
not always the case. We might want to observe the effects of changing rates of
taxation, energy prices, rentals or mortgage payments on a company’s property.
240
9 MODELLING
9
This would obviously require a more complex model. Complex models take into
account the current economic climate and its effect on customers’ willingness
to spend money. They also take into account the effect of purchase taxes when
predicting the number of products which will be sold over time. This is often
represented in the form of a graph showing selling trends.
Graphs are also used to illustrate forecasts, such as which goods are likely to
make profits over a period of time, and allow you to compare the profit levels and
sales of different goods; you can see the profit made by each product very clearly.
The model can then be used to predict profits based on the rate of selling.
As we have seen, one of the most useful features of spreadsheets is the use of the
Goal Seek facility. This can be used to produce results showing the number of
products that must be sold in a given period for the company to make a specific
profit. Alternatively, you can use it to see what price you need to charge for
certain goods in order to make that profit.
Another feature of a spreadsheet is the use of absolute and relative cell
referencing. This ensures you only increment the parts of a formula you need to,
such as the prices or costs of individual items to see their effect.
Complex models used by financial organisations can be used by several
employees. Some of these employees may be inexperienced in the use of
spreadsheets. One of the most useful features of spreadsheets is the use of cell
protection. This prevents these inexperienced users changing formulae or data
by accident. This is particularly useful when protecting cells containing fixed
costs such as overheads, making sure that only senior staff can amend these as
changes occur.
Macros are another useful feature of spreadsheets; if a company wanted to
compare the effect of different costs at the same time, a macro can be used
which makes it simpler to create more complex formulae or functions.
More sophisticated financial models are often used by governments to predict
the future of a nation’s economy. Most governments collect money in the form
of taxes as well as import and export duties. They use this money to spend on
services such as the police, army, education, roads and public transport. Most
countries have some type of purchase tax, so that when goods are bought in
shops some of the price paid is sent by the store keeper to the government.
Most countries also have some form of income tax. People who work have to
give some of their income to the government. Governments can use computer
models to see how much extra money would be raised if they increased taxes.
They can do this for a variety of scenarios. Alternatively, they could change the
amount they spend and see what effect this would have on the need to raise or
decrease the tax rate. There are other variables, such as interest rates, that could
be changed to observe their effect on the model.
Population growth
Population growth is the study of how populations of any type of organism, such
as animals, bacteria, fungi or plants, increase in size over time. Population growth
tends to fall in to one of three classifications. The first is termed ‘exponential
growth’. This means that the number of animals increases in an unlimited way. An
example of this is the explosion of the rabbit population after their introduction
to Australia. Although prior to 1859 there had been some rabbits introduced to
Australia with no noticeable consequence, it was in this year that a farmer called
Thomas Austin imported 24 wild English rabbits and set them free on his land. It
is claimed that the population had increased within six years to 22 million. This
241
9.1 Modelling
9
may be an exaggeration, but it is, however, documented that rabbits had become so
numerous that within ten years of their introduction in 1859, two million could be
shot or trapped annually without having any noticeable effect on the population.
By 1950, the population was estimated to be 600 million. It shows that given
enough food, water and other resources required for life, populations can increase
exponentially without limit. The exponential growth rises rapidly when animals
such as rabbits have several young instead of just two. As long as exponential
growth is in effect, the areas that experience it become more densely populated
regardless of the number already included in the population.
However, it is unusual for populations to grow in an unlimited way because
there are natural limiting factors which stop the population increase. Limiting
factors include the lack of food, water or even shelter. If organisms cannot
find enough food and water, they will have fewer or no young and the rate of
population growth goes down. If there are predators or disease, population
growth is also reduced. Limiting factors have the greatest effect on large
populations that have grown quickly. This was the case in Australia where
the rabbit population reduced to 100 million. Although there were no real
predators, the deliberate introduction of myxomatosis into Australia in the
1950s, a killer disease for rabbits, caused a reduction in the rabbit population.
Exponential growth with limiting factors, such as disease or lack of food, is
often referred to as a ‘logistic model’.
Another type of model is the ‘chaotic model’, whereby rabbits breed exponentially
but eventually the amount of grass and water is insufficient to sustain the population
and so the rabbits die out. Some will survive and the grass will grow back, so
the cycle repeats itself. Because the numbers might be low and the time taken to
reestablish the population varies, the growth is often referred to as being chaotic.
A population-growth computer model tries to predict the population of an
organism. It assumes that the organism reproduces according to a set of rules.
There are a number of variables, such as how often the organism reproduces,
how many new organisms it produces each time, limiting factors (such as limited
resources like food and water), natural death rates and types of predators.
Models provide a way of understanding how populations of organisms change
over time or when compared to each other. Many patterns can be identified
by using computer modelling. Other studies can be made such as the age
distribution within a population and how this changes. Computer models
are used to calculate the maximum harvest for farmers and also to help with
environmental conservation. Computer models are also used to understand the
spread of parasites, viruses, and disease. They are also useful when modelling
the future of particular endangered species.
Weather systems
Weather forecasting used to involve several weather forecasters and days of
calculations. The arrival of satellites and computer models has really helped
forecasters to make more reliable predictions. Weather presenters on television
will sometimes refer to the uncertainty of their predictions because the
computer models are not in agreement. The technical term given to producing
weather forecasts using a computer is ‘numerical weather prediction’ (NWP).
The computer needs to know the current state of the atmosphere, which is
provided to it through observations from weather stations and weather balloons
as well as satellites. These readings are fed into models that analyse the data and
combine it with the most recent forecasts to produce a 3-D model of the Earth’s
atmosphere. It uses mathematical equations that represent horizontal and
242
9 MODELLING
9
vertical air motions, temperature changes and moisture processes among other
variables to calculate what the atmosphere might look like. Computer models
have to include hundreds of mathematical equations to produce a weather
forecast. A lot of computing power is needed to run a computer model. It is
usual for supercomputers to be used in such activities. As we have seen, these
supercomputers are able to perform quadrillions of calculations every second!
The UK Met Office uses the Unified Model (UM) approach. This is a numerical
model of the atmosphere used for both weather and climate applications. It is a
model which is being continuously developed by the Met Office and its partners.
The Met Office uses a seamless modelling approach, whereby a single group
of related models can be used to make predictions over a number of timescales
involving short-term weather forecasts as well as long-range climate forecasts.
The models used take variables like atmospheric pressure, humidity, rainfall,
temperature, wind speed and wind direction, which are recorded all over the Earth’s
surface, into account. The model is suitable for NWP, seasonal forecasting and
climate modelling, with forecast times ranging from a few days to hundreds of years.
Furthermore, the UM approach can be used both as a global and a regional model.
For the weather forecasting aspect, atmospheric models can be used to compare
the current conditions with previous days at similar times of the year with
the same or similar conditions. This enables a reasonably accurate forecast to
be made. Maps are produced showing isobars, temperatures and wind speed.
Predictions can be made because similar weather conditions will have been
observed over very long periods of time.
Climate change
Climate can be measured using a number of variables (quantities that change)
such as rainfall, hours of sunlight, wind speed and temperature, among others.
Temperatures in most regions of our planet vary according to the time of year.
Summer in the southern hemisphere tends to be December to February, while
in the northern hemisphere it is June to August. The temperature differences
between different regions of the world cause differences in atmospheric pressure
which, in turn, lead to winds, storms and, in some cases, hurricanes. Many
scientists believe that the increasing levels of carbon dioxide in the atmosphere,
which is leading to hotter temperatures for our planet, will give rise to major
changes in our climate. Climate change is already beginning to happen in the
Arctic and Antarctic. This does not necessarily mean that all regions on the
planet will become hotter, but some will. The result will be more changeable
weather conditions, such as extremely high levels of temperature, rain- and
snowfall becoming far more frequent than they are now. Forecasting the
possible changes to the Earth’s climate, in other words the long-term patterns
of weather for the entire planet, is obviously going to be more complex than
relatively short-term weather forecasting for specific regions of the planet.
For a long time now, the Earth’s climate has been monitored and records
have been kept of the actual values of the variables involved over a number of
years. This data can be input into a computer model, which is a large and very
complex program that runs on a supercomputer. It is made up of a collection
of equations representing different parts of the climate. Each equation contains
variables like temperature, rainfall, the amount of carbon dioxide in the
atmosphere, and sea level. It shows how each of these affects the other variables.
The variables can be changed to see what effect they will have on the climate.
These equations together give an approximation of the Earth’s climate. The
model can be used to predict trends and examine the effect of increasing and
243
9.1 Modelling
9
decreasing each variable. When the model is created, it is tested. The weather for
any particular year in the past is known. The data for several years prior to that
year are entered into the model. The model’s predictions can be compared with
the real data for that year and so its accuracy can be judged. The model is then
refined until the results accurately match the known climate. If it makes accurate
predictions, it can be used to see what will happen in the future. The accuracy,
however, is limited by the number of years into the future the model extends.
In order to create a computer model, climatologists have to make several
assumptions about how the climate works. Problems arise because climate
is affected by a large number of variables and relating all these variables to
each other is a very difficult task. When you consider that even the largest
supercomputer has its limitations, there are a number of people who are very
sceptical about the accuracy of computer models in this scenario. The sceptics
think that these models are too approximate and may not represent how climate
functions in real life. Many experts are not convinced about the accuracy of
global models. Models take into account the effect of greenhouse gas emissions
such as water vapour, carbon dioxide, methane, nitrous oxide and ozone,
including car exhaust gases. Since it is not possible to predict future government
policies, developments in technology or economic forces relating to these factors,
it is difficult to predict with any accuracy what their effect on the climate will be
in the future. Some countries are discouraging the use of internal-combustion-
engine-powered vehicles and positively advocating the use of electric cars. How
fast this change will take place, and what its effect will be, is difficult to calculate.
However, looking into the future, as climate scientists have more and more data
to work on and more powerful computers are developed, the models will become
better able to predict climate change.
Queue management
Queue management, sometimes referred to as ‘waiting line management’, is crucial
to many aspects of the service industry. The aim is to reduce the time customers
wait to be served and therefore improve the quality of the service provided. Queue
management deals with cases where customer arrival
is unpredictable and varies a great deal for different
times of the day. A service business can reduce its costs
leading to increased profitability by managing queues
effectively. The length of time customers have to queue
incurs a cost to the business, because there is a cost
associated with putting extra employees at checkouts
to reduce service time. Queue management is used
to weigh up one cost against the other and provide
solutions to these problems for the management of
the business. If customers are walking away unhappy
because of the lack of supermarket checkout operators,
for example, the business should weigh up the cost of
hiring more staff against the value of increased income
and maintaining customer loyalty. This is illustrated by
the graph in Figure 9.1, which shows the relationship
between service capacity (for example the number of operating checkouts) and
queuing cost (the cost the supermarket must bear when there are queues).
To begin with, the cost of waiting in line is at a maximum when the business is
at its lowest service capacity. As service capacity increases, there is a reduction
in the number of customers in the queue and in their waiting times, which
in turn decreases the cost of queuing. The ideal total cost is found where the
Cost $
Optimal capacity
Cost of service capacity
Waiting line cost
Aggregate cost
Minimum
V Figure 9.1 Service capacity versus cost
244
9 MODELLING
9
service capacity and waiting line curves meet. This is fine for the queuing at
supermarkets but what about other queuing systems?
In order to be able to solve the problem of queue management, it is important
to understand the characteristics of a queue. Queuing, or waiting in line, is
a normal feature of daily life, for example customers queue in bank branches
in order to be served by a bank teller, cars queue up for petrol or diesel, and
people queue for service at a checkout in a supermarket. It follows that business
managers will need to employ people who will work on mathematical formulae
which will reduce the waiting time and satisfy their customers without incurring
additional costs. There are many other forms of queuing, such as data packets
travelling across the internet or even programs running on your computer.
There are a number of factors that need to be taken into account when studying
queue management. Customers can arrive one at a time, as a group or even en
masse. They can arrive evenly over a period of time. The number of customers can
be finite, such as the boarding of an airplane when it is known exactly how many
passengers will be queuing to board the flight, or it can be what is referred to as
infinite, which is a queue management term for when the number is unpredictable
such as the number of customers in a supermarket during the course of a day.
Queue management theory also has to take into account the type of queue
set-up in particular situations. Some set-ups involve just one queue and one
outcome, such as queuing at a drive-through car wash. You have to wait for the
car in front of you to be washed before you can drive your car through. Some
have one queue but several outcomes, such as at a fast-food drive-through where
there may be one queue of cars but you could be required to first place your
order at one window, drive on and pay for your food at a second window, then
finally collect your food at a third window. Some have more than one queue but
only one outcome, for example at an airport when boarding a flight there may
be two queues – for business and economy passengers respectively – but only
one outcome, passing through the same gate to board the plane. The business
passenger queue boards first, then afterwards the economy passengers board.
The final type is several queues and several outcomes. This is best illustrated by
the example of a launderette where there may be a number of queues for each
washing machine, then once customers have used the washing machine, they
proceed to form queues for each dryer.
The equations needed to cover all possible eventualities in queue management
are extremely complex and can involve the use of an advanced form of
mathematical formulae called differential equations, as well as Petri nets, a
sophisticated mathematical modelling technique.
Let us consider a supermarket which has an automatic queue measurement
system using computers. A number of the large supermarket chains use such
systems. Automatic queue measurement systems can use people-counting sensors
at entrances to the supermarket and above checkouts. They accurately detect the
number and behaviour of people in the queue. Built-in predictive algorithms can
provide information on how many checkouts will be needed to move customers
through quickly. This is done as the queues are forming and the information can
be displayed on computer monitors or tablet devices. A range of information is
provided, such as the length of the queue at any given time and how long the
waiting time for customers is. Management teams can be automatically alerted
before the waiting times get too long. Outputs produced by such systems are:
»
the number of people entering the supermarket
» queue length
» average wait time
245
9.1 Modelling
9
» amount of time each checkout operator is idle
» total wait time.
Other software is available for more complex queuing systems.
Traffic flow
Traffic flow is the study of the flow of vehicles on the road, and their
interactions with other vehicles, pedestrians and methods of traffic control
present on the road. Free movement of traffic is affected by many factors like
speed, the number of heavy vehicles, the number of lanes and intersections
present along the road. Mathematicians and civil engineers, particularly, carry
out research into this subject. Their aim is to attempt to understand and develop
a road network which causes traffic to flow smoothly with few hold-ups.
Traffic flow on a road is calculated as being the number of vehicles using a
particular road in one hour. This is done by counting the traffic at a specific
point on a road for a given length of time. What time of day it is will affect
the traffic count. Particular attention is paid to the traffic flow during peak
hours. Traffic-flow measurement is used on the road to identify places where
congestion occurs. This often leads to decisions being made as to whether traffic
lights or roundabouts will be needed or, indeed, the need for variable speed
control. Variable speed control is the use of speed limits to slow down traffic on
busy motorways or highways; the normal speed limit for that road is temporarily
suspended. Traffic flow depends on two variables: the speed of the traffic and
the density of vehicles on the road (the number of vehicles per given length of
road). Traffic flow can be measured using sensors, (induction) loop detectors,
video cameras, and by manually counting the number of vehicles.
Since as long ago as the 1920s, attempts have been made to produce a
mathematical theory of traffic flow. However, even with the use of sophisticated
computer processing power, it has still proved difficult to produce a general
theory that can be applied consistently to modern traffic flow as it exists in
reality. Current traffic models are based on observed conditions as well as
theoretical techniques. These models are then developed so they can forecast
the amount of traffic, taking into account factors such as increased vehicle use as
well as changes in the methods of transport people use, such as changing from
taking the bus to taking the train or car.
Traffic-flow computer models use a series of complex algorithms. Some
models treat each mode of transport (such as vehicle, bus, train, tram, cyclist
or pedestrian) as a unique object, since each mode has its own behaviour and
targets. Other models concentrate on traffic-flow characteristics such as density,
flow, and average speed of the traffic.
Construction
Models are frequently used in the construction industry. The most common
type of software used is computer-aided design (CAD). It is used by architects
and engineers to create precise drawings or technical illustrations. CAD
software can be used to create two-dimensional (2-D) drawings or 3-D models.
CAD software is used to design structures such as bridges and buildings, and
can also be used to generate animations and other presentational material.
The software often allows for the inclusion of additional information, such as
dimensions and descriptions of components.
CAD packages also incorporate libraries. A library, in this sense of the word, is
a collection of pre-drawn parts and features that you can insert into drawings.
246
9 MODELLING
9
If a building is being designed, a library could be used which would have parts
such as doors, windows, staircases and roofs available. All that is needed is
to select the required parts, changing the dimensions to suit. CAD packages
produce 3-D views and different viewing angles. Most packages can create
3-D walkthroughs so that the viewer can actually see inside buildings or other
structures. Printers are used to produce hard copies of designs; however, it is
more usual for plotters to be used to produce blueprints. More recently 3-D
models can be produced using 3-D printers.
Advantages and disadvantages of CAD
CAD can allow a user to design a part much faster than traditional drawing.
There is a library of shapes and designs so only minor modifications need to be
made. However, work can be lost because of the sudden breakdown of computers.
Using computer-aided design software, it will be much easier to make any
changes because you can fix the errors and modify the drawings more easily.
It is also more accurate. The number of errors that used to occur with manual
designs is significantly reduced. However, it takes time for users to learn how
to use the software and with every new release of the CAD software the user
has to update their skills which can take time. It is expensive to regularly update
CAD software or operating systems.
The different parts of the design can be reused over and over again and CAD
tools make it easier to save files of the drawings and store them in a way that
they can be used time and again.
Open source CAD software is available for free, though it tends to be limited
in scope, but the better forms of CAD software can be expensive to buy, and it
costs a lot of money to train workers to use the software.
Building information modelling
Building information modelling (BIM) is an extension of CAD. It utilises many
of the aspects of CAD but has additional benefits. CAD refers to the use of
computer systems to aid in creating designs using high quality drawings. BIM
uses software that applies CAD concepts to designing buildings. It is used to
create models that include not just the physical aspects, such as walls, roofs and
windows, but also heating, ventilation, and air conditioning (HVAC) and the
electrical aspects of a building.
The US National BIM Standard Project Committee uses this definition:
Building information modelling (BIM) is a digital representation of physical and
functional characteristics of a facility. A BIM is a shared knowledge resource
for information about a facility forming a reliable basis for decisions during its
life-cycle; defined as existing from earliest conception to demolition.
Figure 9.2 Laptop showing
a 3-D drawing using CAD
247
9.2 Simulations
9
BIM is a new process and methodology used by teams of architects, engineers
and contractors who work together to design and build a commercial building
using the same database and computer model. This allows the team to analyse
and visualise design decisions long before a project starts. BIM allows a team to
bring all its designs, including CAD models, into a single database. Using the
Cloud, BIM provides access to this database to all project members. The use of
BIM extends beyond the planning and design phase of a project and covers the
whole of the building life-cycle.
9.2 Simulations
A computer simulation is when a computer is used to imitate an event as it
happens in the real world. A simulation is created usually from a mathematical
model which represents the real system. It consists of computer programs, which
are written to include the main features of this real-world system. The model
is the representation of the system, whereas the simulation is how the system
performs over time.
Computer simulations are often used to represent environments that may be
difficult or even dangerous to replicate in real life. Natural-disaster planning and
nuclear-science research come under these categories.
To begin with we will look at the use of computer simulators in training pilots
and in learning to drive a car.
Advantages and disadvantages of simulations
With a simulator you do not have to worry about the cost of repairing and
replacing damaged cars or airplanes, but buying a simulator with a fast processor
and large amounts of memory can be very expensive, and the technical support
for such systems can prove to be more expensive than regular servicing of
airplanes and vehicles.
You may need fewer driving or flying instructors resulting in lower costs but,
on the other hand, a driving school using a simulator from scratch will need to
retrain driving instructors, costing money and taking time.
Simulators can provide results that are generally difficult to measure such as
reaction times, but the unpredictability of human behaviour means that it may
be impossible to create simulations that cover all eventualities.
With a simulator you can see how a system might work before you design or
modify it, resulting in fewer mistakes in the system, but inaccurate output
from the simulation can still occur as the formulae and functions used may not
accurately represent all possible scenarios.
A simulator prevents injury or even death in the event of a car or plane crash, but it
may make drivers or pilots overconfident and too casual regarding their abilities.
Some events, such as extreme weather conditions, can be recreated fairly
easily without having to wait for them to happen in real life. However, there is
sometimes not sufficient data to produce a mathematical model.
9.2.1 Pilot training
As the number of people who travel by air increases, the number of pilots will
need to increase too. Similarly, as new aircraft are developed, the need to train
existing pilots to fly these new aircraft increases. This, obviously, has to be
done as cheaply and safely as possible. It is for this reason that airlines use flight
simulators. A simulator is designed to react to a pilot’s instructions just like
248
9 MODELLING
9
an actual aircraft would. Many students think that the flight simulator games
available for a PC are authentic simulators. In actual fact, a flight simulator used
by airlines is a purpose-built cabin as shown in Figure 9.3. Inside the simulator
it looks just like a real cabin in an aircraft with all the controls of a real aircraft,
as you can see in Figure 9.4.
V Figure 9.3 A flight simulator pod V Figure 9.4 A flight simulator flight deck
The cabin contains an aircraft cockpit just like the real thing. It includes screens,
flight controls and instrumentation, an adjustable pilot seat, as well as a sound
system and computer hardware. In a flight simulator, the control panel in the
cockpit is identical to one in a real plane. Displays are generated by computers
on the screens in the cockpit. In order to create realism, as the pilot ‘takes off’
in the simulator, he or she sees the airport as well as its surroundings. The
simulation might show a baggage cart to the side of the runway and countryside
in the distance. The sound system recreates the rush of air around the wings
even though they are not there. In order to recreate the feeling of movement,
the simulator is tilted and shaken by a number of hydraulic systems. These are
designed to make the pilot feel that he or she is actually controlling a real plane.
Modern military and civilian aircraft are very expensive to build and because
of the high production cost, airlines are reluctant to train beginners using real
aircraft. Although a flight simulator is also expensive to buy, it is still regarded
as the most cost-effective method for training pilots. The reason is that it can be
operated without damaging any real aircraft. Trainee pilots just beginning their
training obviously do not have much experience in flying aircraft, which means
it is likely that they would crash and damage a real aircraft, putting their lives
at risk and also creating a danger to the general public. For these reasons flight
simulators are much more effective in training pilots to fly.
For a flight simulator to be realistic, it should make the pilot think that he or she is
actually inside the cockpit of a real plane. Every instrument must function identically
to a real airplane. For example, fuel gauges must show the rate at which the engine
consumes fuel, taking into account the simulated thrust being applied as well as
engine temperature. Flight simulators use powerful hydraulic rams to reproduce the
effects of motion that pilots need to experience. The cockpits are constructed with
an array of instruments such as joysticks, levers, switches, buttons and sliders.
In order to recreate the sensation of a pilot manually landing the plane, it is
important that what the pilot sees through the cockpit windows is life-like. To
this end, sophisticated computer graphics are used in addition to the realistic
cockpit instrumentation. The hardware involved is often a group of projectors, a
back-projection screen and a panoramic spherical mirror. It is important for the
display to have very high resolution.
249
9.2 Simulations
9
In a flight simulator, the created environments are based on actual international
airports, constructed from plans, maps, photographs and site visits. A database is
needed which also contains information on the surrounding area of each airport
for several miles. Building such a database requires special software tools.
9.2.2 Learning to drive a car
Simulators, for similar reasons to those for pilot training, are also used to teach
people to learn to drive. It is safer to allow drivers to learn how to drive without
causing damage to the car or themselves or, indeed, other drivers and the general
public. The same type of graphics and animation software used for pilot training
can be used to reproduce the situations that car drivers experience. Driving
simulators can take several forms. One used by a driving school would probably
be a full cab model, which looks like the driver’s side of a car and includes real
car parts, such as clutch, accelerator and brake pedals, signals, windshield wipers
and an ignition key.
Driving simulators replicate actual driving experiences. They allow you to
experience driving at different times of day or night and driving in different
weather conditions such as fog, heavy rain, snow, driving into a setting sun
and so on. Driving simulators can also be used to monitor driver behaviour,
performance, and attention span.
9.2.3 Natural-disaster planning
A number of natural disasters have occurred in the past decade. In 2018 there
was a tsunami in Indonesia; also that year there were the Bangladesh floods and
landslides as well as an earthquake in Papua New Guinea. Most natural disasters
have a human cost, whether this is in terms of displaced persons, the spread
of disease, disruption to transport, or damaged and destroyed homes. Natural
disasters occur unexpectedly and usually result in huge loss of life and property.
How to make effective contingency plans is an intriguing question constantly
faced by governments and experts.
In order to plan for natural disasters, such as tsunamis, simulations are
created to represent the flow of floodwater and the debris it carries. Computer
modelling allows us to understand and analyse natural disasters. In recent years,
the huge increase in computer power and speed, together with advances in
the development of computer-based algorithms, has made it easier to produce
computer models. This has made it possible to model the movement of billions
of water particles to allow more accurate predictions of the effects of natural
disasters such as tsunamis, floods and mudslides. This can help town planners
to build structures which can withstand such disasters, as well as allowing the
emergency services to co-ordinate an efficient response.
The major task for the creators of natural-disaster models is to provide
information about disasters before they occur. The purpose of such modelling
is to anticipate the likelihood and severity of natural disasters, from earthquakes
and hurricanes to major crop failure. It can also be used to model what might
happen in the event of a terrorist attack. The very first task is to create a
catalogue of events, which is then used in the simulation. This is more difficult
than it seems. It should be simple to create a catalogue from historical records.
This does not work well, however, as only the most catastrophic events are
likely to have been recorded; the smaller events might not have been deemed
worthy of record or might not even have been witnessed by anyone at all.
This leads to a lack of reported small events, which leads to such events being
underestimated. Equally troubling is that this can lead to the assumption that
250
9 MODELLING
9
because a particular event has never been recorded as happening in the past, it
is unlikely to happen in the future. In recent times, however, there has been an
improvement in the collection of meaningful data and also in the methods of
describing the collective behaviours of people in disaster situations.
In addition, there are mathematical models available. Mathematics is often
used to describe natural phenomena. A tsunami is a high-speed sea wave
usually caused by an underwater earthquake, landslide, or volcanic eruption.
A tsunami when it reaches land is considered to be a shallow water wave.
That means the height of the wave is smaller than the length of the wave.
Mathematicians have produced equations relating the depth of water to
the velocity of the wave, the length of the wave, the density of the sea
water, the acceleration due to gravity and the energy of the wave. Similarly,
mathematicians have produced equations and functions which represent the
behaviour of volcanoes, earthquakes and tornadoes. These equations and
functions can then be converted into computer models.
9.2.4 Nuclear science research
V Figure 9.5 A diagram of a nuclear reactor
Systems like nuclear reactors are very complex and very costly to construct and
operate. If these systems are operated incorrectly, they can cause dangerous
and even disastrous consequences. There is a great need to train the operators
of nuclear research facilities. This is being done, increasingly, through the use
of simulators. Nowadays, most reactors have a simulator. These simulators
imitate all the devices in the reactor control room such as meters and sensors.
Operators’ skills are refined through the use of a simulated control-room
environment. Computer simulations use mathematical algorithms to predict
how different reactors will function.
Because scientists cannot forecast exactly how a nuclear reactor will respond
to variations in its set-up or the conditions under which it is operated, every
facility has to be designed and operated within large safety margins. This
means that the efficiency of the forecast is reduced. The core of a fast-nuclear
reactor may consist of an arrangement of stainless-steel pins which contain rods
made of uranium or plutonium metallic alloys or oxides. Once the reactor is
operating, these pins become extremely hot. In order to convert this heat to
electricity and to prevent melting the stainless steel, thousands of gallons of
liquid metal coolant such as liquid sodium are used. Rearranging these rods
affects the neutron chain reaction and consequently the flow of coolant and the
251
9.3 Using what-if analysis
9
transfer of heat. In a real-life reactor, this can create extra expense and increase
safety concerns. For these reasons, research scientists tend to use computer
simulations. Reactors convert the uranium and plutonium inside each pin into
energy through nuclear fission. The hope of nuclear scientists is to have more
powerful computers in order to recreate experiments at a microscopic or even
atomic level. High-speed supercomputers are increasingly being used to solve
extremely difficult problems.
9.3 Using what-if analysis
Computer models all base their model upon a what-if analysis. This involves
changing a model to find out what the results would be, if different input
values are chosen. You need to know how to build, test and change data within
models, or change the model itself to produce different sets of results.
Simple computer models can be created, tested and used in Excel. Some models
only require a change in data.
Task 9a
Open the file Widget.csv. Enter appropriate formulae in cells B6, B7, B10, B12
and B17. Workers only work for eight hours a day. Calculate the cost to make
each widget. Apply appropriate formatting to all cells (all currency values are to
be shown in $ to two decimal places). Test your spreadsheet model.
The cost of raw materials is $2, and the time taken to make each widget is 10
minutes. At the moment the company has two workers.
Use the spreadsheet model to calculate:
» how many workers are required to complete an order for 500 widgets placed
on 7 January and required by 10 January
» the sales price required per widget to make a $50 profit.
Save the spreadsheet as Task_9a.
Open the file and enter into cell B6 the formula =60/MINUTE(B5) to
calculate the number of widgets a worker makes in one hour. Enter into cell
B7 the formula
=B3*B6 to calculate the number of widgets that are made
per hour by all the workers. Enter in cell B9 the formula =B4+B8/B6 to
calculate how much it costs to make each widget. Enter in cell B12 the formula
=ROUNDUP(B11/(B7*8),0) to calculate the time taken to manufacture,
which is the number of widgets required by the customer divided by the
number that can be made in the factory in one working day by all employees,
rounded up to the nearest whole working day. Enter in cell B17 the formula
=B11*(B16-B9) to calculate the number of widgets multiplied by the sales price
of each widget minus the cost to make each widget.
9.3.1 Test the spreadsheet model
Before modelling the data, we must test the data model using a test plan and
data that gives us (easy-to-calculate) known results. Check that the formulae
you have entered give the correct results using data like this: one worker, $1
cost of raw materials, ½ hour to make one widget, and $10 per hour as the
rate of pay. Each widget would cost $6 to make ($5 for ½ hour labour + $1 for
raw materials). If 20 widgets were required it would take 1¼ working days (if
each widget takes ½ hour to make, 16 can be made in one day), so the formula
should round this up to two full working days. To test the profit/loss formula,
252
9 MODELLING
9
values that make the calculations easy have been used (for example if a widget
costs $4.50 to make, then to sell it for $5.50 makes the value 1, which is easy
to multiply by). Cell B9 is tested in Table 9.4 so will not need testing again, but
the results from B9 are used for this test. The test tables for this data and two
other examples are shown in Tables 9.1 to 9.6.
Test type Formula Cell B6
Data entry in cell: Expected results Actual result Remedial action
B3 B4 B5 B11
1 1 00:30:00 20 2 2 No
1 1 01:00:00 10 1 Division by
zero error
Yes - error
2 2 00:15:00 200 4 4 No
V Table 9.1 Completed test plan for Task_9a: cell B6 – Version 1
Identify where the error has occurred. Correct the function in cell B6 so it
becomes
=60/(60*HOUR(B5)+MINUTE(B5)).
Attempt all the tests for cell B6 again. The resulting test plan for the retest
should look like Table 9.2.
Test type Formula Cell B6
Data entry in cell: Expected results Actual result Remedial action
B3 B4 B5 B11
1 1 00:30:00 20 2 2 No
1101:00:0010 1 1 No
2 2 00:15:00 200 4 4 No
V Table 9.2 Completed test plan for Task_9a: cell B6 – Version 2
Continue with the testing for the other formulae.
Test type Formula Cell B7
Data entry in cell: Expected results Actual result Remedial action
B3 B4 B5 B11
1 1 00:30:00 20 2 2 No
1101:00:0010 1 1 No
2 2 00:15:00 200 8 8 No
V Table 9.3 Completed test plan for Task_9a: cell B7 – Version 1
Test type Formula Cell B9
Data entry in cell: Expected results Actual result Remedial
action
B3 B4 B5 B11
1 1 00:30:00 20 $6.00 $6.00 No
1 1 0 1 :00 : 0 0 1 0 $11. 00 $11 .0 0 N o
2 2 00:15:00 200 $4.50 $4.50 No
V Table 9.4 Completed test plan for Task_9a: cell B9 – Version 1
253
9.3 Using what-if analysis
9
Test type Formula Cell B12
Data entry in cell: Expected results Actual result Remedial action
B3 B4 B5 B11
1 1 00:30:00 20 2 2 No
1101:00:0010 2 2 No
2 2 00:15:00 200 4 4 No
V Table 9.5 Completed test plan for Task_9a: cell B12 – Version 1
Test type Formula Cell B17
Data entry in cell:
Expected
results
Actual
result
Remedial
action
B11 B16 B4 B8 B6 B9
10 $5.50 2 10 4 $4.50 10 10 No
10 $6.50 2 10 4 $4.50 20 20 No
20 $6.50 2 10 4 $4.50 40 40 No
V Table 9.6 Completed test plan for Task_9a: cell B17 – Version 1
After testing, enter the data for two workers, with the cost of raw materials as
$2 and the time taken to manufacture each widget as 10 minutes.
Now the model has been created, we will use it to calculate how many workers
are required to complete an order for 500 widgets, placed on 7 January and
required by 10 January.
Enter 500 in cell B11. Enter in cells B13 and B14 the dates 7 January and
10January, and enter in cell B15 the formula
=B14-B13 and format this cell as a
general number. To model the number of workers required you could just guess
what number to place in cell B3 and keep trying until you get cell B12 to say 3,
but this is not efficient.
Goal Seek
Instead of guessing the values (which may be reasonably easy in this simple
model) we can use the Goal Seek feature. Place the cursor in cell B12. Use the
Data tab and locate the Forecast group. From the What-If Analysis icon, use
the drop-down menu to select Goal Seek....
In the
Goal Seek window, enter in the To value: box the value 3
(the number of days for the order taken from cell B15). Enter in the
Bychanging cell: box the cell reference B3, then click
.
Goal Seek will run, and after testing all possibilities will return a
solution in the
Goal Seek Status window.
The solution
offered by Excel ’s
Goal Seek is to
employ 4.08
workers.
This also
suggests that
these workers
would make
24.48 widgets
per hour.
254
9 MODELLING
9
To produce a realistic working solution, the number of workers will
need changing to 5 which will change the contents of cell B7 to
30. To calculate the sales price required per widget to make a $50
profit, place the cursor into cell B17, then from the
Data tab select
the
What-If Analysis icon, then Goal Seek.... Edit the Goal Seek
window to look like this.
Click
to run the Goal Seek. Goal Seek will run, and after
testing all possibilities will return a solution in the
Goal Seek
Statuswindow.
This returns in cell B16 a value of 3.76666666666667, which
needs to be rounded up to $3.77 (it may appear to hold this
already if you have formatted the cell to two decimal places).
Change the value in cell B16 to $3.77 which will return a profit
of $51.67.
Examination-style questions
1 A company that sells cars uses a driving simulator so that customers and
their teenage children can test drive the latest models of cars without going
on the road.
Describe the effects using this driving simulator could have on customers
and the car sales company.
[6]
2
An engineering company requires a new computer system to control a
production process. Staff will need training before they can use the new system.
Describe the benefits of using a computer simulation for this training. [3]
3
Climate change is another way of describing global warming. Many experts are
using computer models to predict the changes which are occurring.
Describe the drawbacks of using computer models to predict climate change. [6]
Activity 9a
Open the file TuckShop.csv and
format it to look like this.
Enter appropriate formulae in rows
10 and 12. Enter formulae into row
13 to calculate the percentage profit/
loss, which is the profit/loss divided
by the wholesale cost per pack. Test
your model.
Use the spreadsheet model to
calculate the unit sales price for
each product to return a percentage
profit of (at least):
» 50%
» 20%.
Save the spreadsheet as
Activity_9a.
Advice
Remember, formatting to a number of decimal places does not
change the original value.