Comparing Time Varying Data in GoldSim and Spreadsheets

Introduction

The purpose of this article is to help GoldSim users who want to compare GoldSim model results to results of similar calculations done in a spreadsheet. Because of fundamental differences between spreadsheets and dynamic simulators like GoldSim, people sometimes find it challenging to compare time-dependent results in the two programs. 

This article first describes the differences between dynamic simulation tools (i.e., GoldSim) and spreadsheets. Following this introduction, it describes some problems people commonly encounter when comparing GoldSim results to spreadsheet results. This article is intended to help the modeler appreciate the differences between dynamic simulation and spreadsheet modeling and provide instruction on how to avoid the problems that might arise due to the misunderstandings described in this article.

Dynamic Simulation

GoldSim is a dynamic simulation tool. Dynamic simulation is the use of a computer program to model time-varying behavior of a system. In GoldSim, time is a built-in variable, explicitly associated with all model variables and the system you are modeling. The state of the system is updated and reported at specific reporting periods (i.e., time steps) during the simulation. The results for all reporting periods are stored in the computer’s memory. In GoldSim, there is an internal clock that keeps track of time and reports this in various ways (i.e., the simulated date, the simulated month, the elapsed time, etc.). The model variables are updated automatically each time the internal clock is updated. The clock is updated at scheduled time steps defined by the user and also at other points in time (when specified events occur during the simulation). That is, it is important to note that in GoldSim, clock updates don’t necessarily occur only at scheduled time steps; they can also occur at times in between the scheduled time steps.

Typically, the modeler is interested in seeing how model variables behave over time. In GoldSim, the model progresses forward in time from a defined starting point and continues running until a defined end point. During the simulation, all the model variables are updated and saved so that these values can be viewed by the modeler. 

Spreadsheet Modeling

A spreadsheet is very different from a dynamic simulation tool. A spreadsheet is a computer program that simulates a paper-accounting worksheet using a grid of cells organized in rows and columns that contains text, numeric values, or formulas. One of the widely known benefits of a spreadsheet is the feature allowing the user to copy and replicate formulas down rows or across columns, thus simplifying the process of repetitive calculations (such as repeating calculations for each time step).

One of the most important differences between a dynamic simulation tool (like GoldSim) and a spreadsheet is how the tool deals with the concept of time. Although spreadsheets do recognize dates, time is not explicitly defined inside the internal workings of the spreadsheet program. Typically, the modeler will use rows (or columns) in the spreadsheet to imply time steps. The date label in each row/column is used to reference a time point that corresponds to values and formulas within that same row/column. Note that as a result of this representation, in a spreadsheet, it is ambiguous as to what the values represent (e.g., do the values represent constant values over a time step, or are they instantaneous values?).    On the other hand, as will be seen below, GoldSim has some very specific rules as to how time-varying data is entered and represented, so that there is no ambiguity at all as to what the values represent.    These differences in time accounting methods can cause confusion when comparing GoldSim results to those in a spreadsheet. To help avoid misinterpretation of results when comparing GoldSim and spreadsheets, we have summarized three different types of issues that people tend to encounter when comparing GoldSim and spreadsheet results. These are described in the following sections.

Problem #1: GoldSim Results Represent Instantaneous Rates While Spreadsheet Results Represent Changes in a Quantity Over an Interval

This is the most common problem encountered when people try to compare spreadsheet results to GoldSim results. The problem arises because the results computed by the two approaches are fundamentally different. GoldSim computes instantaneous rates at each time step (e.g., an instantaneous flow rate). Spreadsheets do not actually deal in rates; they compute changes in a quantity (e.g., a volume) over an interval (i.e., the time step). A change in a volume divided by a time interval does not represent an instantaneous rate; it represents an average rate over the interval. Comparing an instantaneous rate at time T2 (computed by GoldSim) to an average rate over the time period between T1 and T2 (computed by a spreadsheet) is likely to yield different results.

The best way to demonstrate this difference is through examples. Two examples will be used, starting with a simple example involving an overflow from a reservoir with a constant inflow and no outflow. For simplicity, it is assumed that overflow is instantaneous at the point that the capacity of the reservoir is exceeded and is not affected by the physical limitations of an overflow facility, such as a spillway. In this simple example, a reservoir fills up at a constant rate of 4 m3/day, and then begins to overflow after it reaches its capacity of 10 m3. There are no outflows (withdrawals) from this reservoir (the overflow when the reservoir is full is not considered to be an outflow). We will also assume that we are carrying out the calculation for a period of 4 days, using a daily time step. In a spreadsheet, the inflow rate (4 m3/day) must be treated as a volume that is applied over a period of time, represented by each row of the spreadsheet. That is, rather than explicitly defining the inflow as a rate, it is treated as a volume applied uniformly over the time step (which is assumed to be 1 day). At any time step i (corresponding to a row in the spreadsheet), the volume (V) and overflow rate (S) are calculated as follows:

V_i = min(V_i-1 + I_i - O_i, V_max)
S_i = max(I_i - O_i - V_i + V_i-1, 0 m³)

where: V_i = volume at time step i (m³)
S_i = volume that overflows over time step i (m³)
I_i = inflow over time step i (m³); 4 m³ in this example
O_i = outflow over time step i (m³); 0 m³ in this example
V_max = maximum volume in reservoir (m³); 10 m³ in this example

Table 1 is a summary of the model results for this simple example. Each row in the spreadsheet represents a time step.

Table 1 – Reservoir Model: Spreadsheet Results


The inflow and outflow represent volumes applied from the time shown for the current row until the time for the next row down. The volume represents the instantaneous amount reported at the beginning of the time shown for the current row (e.g., the volume at 1 day is 8 m3). The overflow represents the volume of water that overflowed between the time shown in the previous row and the time shown in the current row. For example, between Time = 1 day and Time = 2 day (i.e., during the second day of the simulation), the overflow over the course of the day was 2 m3. Note that the overflow values shown in the table do not represent flow rates; rather, they represent flow volumes over the time period. In reality, the reservoir overflowed at a rate of 0 m3/day for the first half the second day and 4 m³/day for the second half of the second day. The average flow rate over the entire day was therefore 2 m³/day. For comparison, the same reservoir model was implemented in GoldSim, using a daily time step. Since GoldSim is a dynamic simulator that incorporates time into the calculations, the overflow is truly represented as a flow rate and not a volume as it is in the spreadsheet. Table 2 is a summary of results that are reported at the scheduled daily time steps of the GoldSim model. 

Table 2 - Reservoir Model: GoldSim Results

All values shown in this table represent the same thing: the instantaneous rate (or volume) at that point in time. Hence, the instantaneous overflow rate at Time = 2 days (i.e., the end of second day or the beginning of the third day) is equal to 4 m3/day. The volume at that point in time is 10 m³. Note that although the overflow rate shown for at Time = 1 day is 0 m³/day, this does not mean the rate was 0 m3/day for either the first day (Time = 0 to 1 days) or the second day (Time = 1 to 2 days). Rather, it only means that at the end of the first day (i.e., exactly at Time = 1 day), the overflow rate was 0 m3/day.

Figure 1 shows three different figures as another way to compare the differences between spreadsheets and GoldSim models. The figure on the top is how the system really works. The reservoir reaches capacity at 1.5 days, at which point the reservoir begins to overflow. The figure on the lower left is how GoldSim displays results at each scheduled time step. The figure on the lower right is how a spreadsheet would display results. The dots on the lower graphs represent the values that are reported at the scheduled time steps.

Figure 1 - Simple Representation of Reservoir Overflow in GoldSim and a Spreadsheet 

The key point here is that in the GoldSim result plot, the overflow rate represents an instantaneous flow rate. In the spreadsheet plot, it actually represents a quantity over a time step (i.e., an average flow rate). Note that if we were to integrate the overflow rate in the GoldSim model at each scheduled time step (e.g., using an Integrator element or another Reservoir), the results would look like this:

Table 3 - Reservoir Model: GoldSim Model with Integrated Overflow


As can be seen, GoldSim correctly computes the cumulative amount of overflow at each point in time. How does GoldSim do this? As noted earlier, the GoldSim internal clock is interrupted on scheduled time steps but is also interrupted by specific events. One such event that interrupts the internal clock is the overflowing of a Reservoir element in GoldSim. That is, at the point where a Reservoir starts to overflow, GoldSim inserts a new (unscheduled) time step, and this allows GoldSim to accurately capture the actual overflow behavior. However, the results of model variables at these inserted (non-scheduled) time steps are not reported in GoldSim Time History charts (only results at scheduled time steps are displayed). 

To explore this further, a second more complicated example is presented below. In this example, we will assume that the inflow is changing and in addition to a possible overflow, there is a constant outflow (withdrawal). The reservoir has an upper bound of 12 m3. Once the maximum volume limit is exceeded, it will overflow. The reservoir model runs for 4 days with a daily time step. Table 4 shows the inputs and outputs as they would be appear in a typical spreadsheet. It is implied that the inflow and outflow values shown on each row represent constant values over the period of the time shown in the “Day” column. For example, the inflow is a constant 9 m3/day starting at the beginning of the second day (i.e., at Time =1 Day) and ending just before the beginning of the third day (i.e., at Time = 2 day). The calculated volume and the overflow rates are shown just below the row of the inputs to indicate that the values are updated just before the next time step occurs (i.e., at the beginning of the next time step). As noted in the previous example, flows in a spreadsheet actually represent volumes that accrue over each time step.

Table 4 - Reservoir Storage Calculations in a Spreadsheet


A plot of these spreadsheet results is shown in Figure 2.

Figure 2 - Plot of Spreadsheet Model Results

To build this same model in GoldSim, we would use a Time Series element to represent the inflow. The data in the Time Series element would be explicitly defined to represent constant values over the next time interval (See Figure 3). This ability to specify what the Time Series represents in GoldSim will be discussed further below (see Problem #3). Figure 3 - Definition of Inflow Data Timeseries

Figure 4 is a screen capture of data that would be entered into the Time Series element. Figure 4 - Inflow Data for GoldSim Model

The GoldSim model results are shown below in Figure 5. Figure 5 - GoldSim Model Results

As can be seen, the maximum overflow rate appears to have occurred Time = 3 days with a value of 3 m3/day. However, the results shown in this graph must be interpreted carefully. The key point to understand is that the overflow reported is an instantaneous value captured at a specific point in time, NOT the average value over the time step. In this particular case, a time step was inserted sometime between Time = 2 days and Time = 3 days (when the upper bound of the reservoir was reached). The (instantaneous) overflow rate was only plotted, however, once per day (at the scheduled time steps). The values of the overflow rate between Time = 2 days and Time = 3 days cannot not be determined from this plot since only the values on the beginning and end of the third day (i.e., at Time = 2 day and Time = 3 day) were saved.

If you were to integrate the overflow rate using an Integrator element, you would see that GoldSim correctly maintains an accounting of the overflowing water. Figure 6 shows the overflow rate and the integral of this rate (cumulative overflow volume) over time, which properly accounts for 26 m³ of overflow water.

Figure 6 - Graph Plot of Model Results Showing Overflow Rates and Volume (5 time steps)

At Time = 3 days, the cumulative overflow has grown to 23 m³ even though the (instantaneous) overflow rate at that point in time is reported to be 3 m3/day. Clearly, this implies that sometime between Time = 2 days and Time = 3 days, the overflow rate must have been significantly greater than 3 m3/day.

This becomes easier to understand if we run the same model with shorter time steps and view the graph again. Figure 7 shows results from the same model with 50 time steps instead of 5 time steps (i.e, the values in the model are updated every 0.1 days instead of every 1 day).

Figure 7 - Graph Plot of Model Results Showing Overflow Rates and Volume (50 time steps)

This figure makes it clear that the overflow rate jumped up to 28 m3/day just after Time = 2 days, and then dropped to 3 m3/day at Time = 3 days. Note that unlike a spreadsheet, in GoldSim, all reported flows (inflow and outflow, as well as overflows) represent instantaneous, volumetric fluxes. In a spreadsheet, the flows are treated as discrete, daily volume changes. Moreover, the exact time that the reservoir begins to overflow is left ambiguous in a spreadsheet. In GoldSim, such a change is automatically captured. GoldSim inserts a time step when the overflow begins. This happens regardless of the number of scheduled time steps. As a result, the GoldSim model results (in terms of the volume in the reservoir and the cumulative volume that overflowed) using 5 time steps and 50 time steps are identical; the 50 time step model simply allows for more refined plotting on the time history chart.

We can examine this further by viewing the results at the unscheduled time steps. This can be done in GoldSim by setting up a Time Series element to record time histories. (To learn how, open the GoldSim Help file, select the Index tab, and enter “Time Series elements, using to record”.) This will make it possible to view all the changes that occurred in the model without decreasing the simulation time step. Figure 8 shows the results for the overflow rate:

Figure 8 – Time Series Recording of the Overflow Rate (including the value at the inserted time step)

As can be seen, the Reservoir started to overflow at about 2.18 days (at which point GoldSim inserted a time step). The overflow rate at this point in time was 28 m3/day. Table 5 is a summary of the all the model inputs and outputs specifically including the values at the inserted event just after Time = 2 days. The row containing the inserted time step is highlighted. (These values were pulled directly out of Time Series elements that were set to record.)

Table 5 - Summary of the GoldSim Model Results (including values recorded at the inserted time step)

A table showing results at the scheduled time steps used for display in a Time History Result element are shown in Figure 9.

Figure 9 - Time History Table in GoldSim Model (not including the values at the inserted time step)

Note that these results do not indicate that the overflow rate peaked at 28 m3/d because that value did not fall exactly on the scheduled time step. Hence, if you copy and paste these values with the intent to compare them to parallel calculations done in a spreadsheet, you will discover a discrepancy. The problem is that it is incorrect to assume that the values displayed here are constant over the time step. Instead, they represent the instantaneous values that are recorded at each scheduled time step.

Problem #2: Misuse of Month and Year Time Units

Sometimes people are surprised to find that results of functions involving time units (like months or years) do not match results in a spreadsheet. This usually happens because of differences in the way years and months are defined.

It is important to understand that all units in GoldSim must have fixed definitions (e.g., 1 km = 1000 m). These definitions are constant in time. The same rule must apply to time units. Hence, 1 day = 24 hr. Confusion occurs when people use the time unit month (mon) or year (yr). How are these units defined?

GoldSim defines the month time unit (mon) to be the length of an average month, accounting for leap year. In particular, 1 mon = 30.4375 days. The year time unit (yr) is defined in GoldSim as the average of leap years and non-leap years on a 4 year cycle so that the unit of a year always equals 365.25 days.

It is important to keep in mind that these units have fixed values, independent of the current clock time of the simulation. For example, if you define a variable in GoldSim as “1 mon”, and display the result in units of days, the result will be a constant 30.4375 days even if the model is run for 12 months. For example, the expression “1 mon” will evaluate to 30.4375 days during February and March. Do not confuse the unit “mon” with the model variable “Month”, which is a unitless counter (i.e., an integer) that indicates the current month (cycling between 1 and 12).

Problem #3: Ambiguity in the Definition of Flow Rates

Another mistake that is often made when comparing GoldSim results to those in a spreadsheet arises due to ambiguous flow rate definitions. GoldSim forces you to explicitly define what the data in a Time Series represents. In a GoldSim Time Series element, there are 6 different ways to represent time varying data:

That is, GoldSim forces you to be very explicit regarding what the time series data you are entering represents.

In the spreadsheet shown below in Figure 10, the flow rates are defined as daily values. This time definition is implied due to the date in the first column that appears to consistently change on a daily basis. However, this table is ambiguous. First, what exactly does each value of “Flow Rate” represent? Is it an average value across the entire 24-hr period or is it an instantaneous value taken at a specific time (e.g., noon or midnight)? Another clarification that needs to be made is whether or not the dates increment by exactly one day for the entire dataset. Are some dates missing and if so, do we assume the flow rate is constant over those missing days?

Figure 10 - Example Data in a Spreadsheet

Even though these questions might be taken care of through good documentation, the representation of time is often ambiguous in a spreadsheet. Hence, care needs to be taken when comparing this data to results from a dynamic simulator. Figure 11 is an example plot showing how time varying data can be represented in different ways using the same data. The figure shows plots of the same data defined in three different ways: as instantaneous values, as constant values over the next time interval, and as constant values over the previous time interval. Depending on the definition, the model would have very different results. Since the definition of such data in a spreadsheet is ambiguous, care must be used when validating results against that of a GoldSim model.


Figure 11 - Plot of Time-Varying Data with Different Definitions

Conclusions

As seen in the previous sections, there are some significant differences in the way flow rates and flow volumes are defined in spreadsheets and dynamic simulators. Becoming more aware of these differences will help to ensure that appropriate comparisons of results are made between the tools. The main points to consider when comparing GoldSim models to spreadsheet models are as follows:

• Time series data are explicitlydefined in GoldSim and loosely defined in a spreadsheet.
• Flow rates in GoldSim are reportedas instantaneous values, whereas spreadsheet results typically representaverage values (quantities over a fixed time interval).
• GoldSim might insert unscheduled time steps thatare not reported in a Time History chart
• The time units (such as mon, d,yr, hr) are different than model variables of time (such as Month, Day, Year,Hour) and should be used cautiously.
  

For answers to questions about comparing GoldSim results to spreadsheet data, please contact us at support@goldsim.com.

See Also

http://www.goldsim.com/Wiki/TimeSeries.ashx

http://www.goldsim.com/Web/Products/GoldSimPro/Spreadsheets/