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 Linkages

to the system equations

First, we set up the spreadsheet.

Basic Setup

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 simulation.

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 cup. 

 

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 next column.

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 exercise.

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 here.

In the next section, we build the model in a modeling language, iThink.

iThink Milk Model