Modeling in Excel
The model building process
is straightforward. We simply convert each system link to an
equation on a spreadsheet and let the spreadsheet trace the
evolution of the behavior.
If you wish to master the
mechanics of simulation, you might consider following along with
this demonstration with your own spreadsheet program.
We can Convert the
to the system equations
First, we set up the spreadsheet.
We are starting with the cup empty at
zero cups and our target is, say, one cup. The time constant
is two seconds. That means if the level remaining (target
minus level) is 1/4 cup, then the flow rate is 1/8 cup per second.
The solution interval is a small "moment in time" that we use to move
the simulation forward, step by step. The smaller the
moment, the more accurate the simulation - and the more iterations
the simulation takes to complete.
Next, we make some room for the
Here we show time running from zero to
one second, in time intervals of 1/10 second. In this way, we can
carry the simulation forward, step by step, interval by interval, 1/10 of a second
at a time.
Next, we start the simulation by filling
in the first column.
Filling in the First Column
We start out with the milk level at zero.
The level remaining = the target
level minus the milk level = 1 cup - 0 cups = 1
The milk flow rate is equal to the level
remaining (target - level) over the time constant = 1 cup / 2 seconds
= 0.5 cup per second.
At the end of this time
increment the level increases by 1/20 cup. Since
the level starts at zero, the new milk level = 0
cups + 1/20 cup = .05 cups (showing in the
yellow rectangle above).
Next, we carry the new milk level over to
be the starting level for the next time interval and fill in the
Filling in the Second Column
First, we carry the new milk level from
the Time=0 column over to be the beginning milk level for the
Time = .1 column.
Now we fill in the milk level
remaining. Since the level is now .05 cups,
we have .95 cups remaining to go.
According to our policy, we keep the
flow rate proportional to the level remaining.
Therefore, the new milk flow rate = .95 cups / 2 seconds =
.475 cups per second.
We add the milk flow rate to our starting
level and multiply it by the solution interval to get the new milk level: .05 cups + .0475
cups * .1 secs = .0975 cups.
We see that as the cup fills, the rate of
filling decreases. This is a characteristic of feedback
control systems. As we zero-in on the target, we do so in
smaller and smaller increments.
At this point, we go ahead and extend the
spreadsheet. We can continue to do this by hand - or we can
enter the equations in the spreadsheet and extend the spreadsheet by
dragging the simulation to the right.
Extending the Simulation
Here we see the simulation extending out
through the end of the sixth time interval, so we are complete past
the first 6/10 second. In this spreadsheet, I am showing only
the first three decimal places, since the decimals get longer and
longer and can make the spreadsheet hard to read.
Next we continue on and fill in the
columns as far out as we please. This would make the
spreadsheet too wide to show on this page, so we might re-arrange
the simulation in column form.
Simulation in Column Form
Here we see the evolution of the model
past the first 2-1/2 seconds. To make the results easier to
see, we can make a graph.
Graph of Simulation
Here we can see how the simulation
evolves through time. This is the same data as in the
spreadsheet. Note that the level-to-go is proportional to the
flow rate, so the flow rate (not showing on the graph) has the same
shape as the level to go.
You might notice some similarities
between the shapes of these curves and the shapes of the curves you
generate with the interactive milk model in the first
At this point, you can experiment with
your model, trying different time constants and different solution
intervals to see what happens.
The model building process
provides an excellent way to define and refine assumptions and theories
about how a system operates.
In the next section, we
present some experiences from people who are carrying
out this exercise. If you wish to report your
experiences, send them to FAQ.
If you wish to download the
excel worksheet, you may do so
In the next section, we build
the model in a modeling language, iThink.