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 have difficulty comparing 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 certain "unscheduled" 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 "unscheduled" 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.
Another important aspect of GoldSim that must be kept in mind is that dimensions are inherent to expressions and outputs so rates and quantities can be derived and integrated by GoldSim during the simulation. For example, a flow rate in GoldSim might be defined with dimensions of m^3/day so that an integration of this value over time must output a value with dimensions of volume (i.e. m^3). All calculations in GoldSim are executed in SI units and then converted for display purposes based on display units defined by the user.
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 the next time step, over the previous times step, or are they instantaneous values?).
Dimensions are also not inherent to spreadsheet values so additional care must be taken to ensure consistency when integrating and deriving rates. Most often, spreadsheet models are built with the time component of rates being implied by the time difference in each row of the spreadsheet rather than explicitly built into the values themselves. That is, "flow rate" values in a particular row in a spreadsheet actually represent a discrete quantity (not a rate) that flowed over a specified time (the time difference between rows).
On the other hand, as will be seen below, GoldSim has some very specific rules as to how 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: Confusing Instantaneous Rates with Discrete Quantities
This is the most common problem encountered when people try to compare spreadsheet results to GoldSim results of a model that contains rates of change and integrated quantities. The problem arises when flows are not represented the same way between the two programs. When dealing with rates (e.g., flow rate of water), GoldSim computes instantaneous rates at each time change in the simulation. Since spreadsheets do not inherently deal in rates nor dimensions, rates are typically computed as discrete quantities, which are associated with a time interval (i.e., each row of the spreadsheet). This loose association with time implies a rate of change. In particular, a discrete quantity associated with a time interval can be used calculate the average rate over the interval. Comparing an instantaneous rate reported by GoldSim at time = 2 days to an average rate reported by a spreadsheet on time = 2 days may yield different results.
The best way to demonstrate this inconsistency is through examples. Two examples will be used, starting with a simple one. We recommend that you follow along with these examples, building a GoldSim model and spreadsheet model to compare.
Example 1: Constant Inflow without Discharge Requests
Assume there is a reservoir with a constant inflow and no outlet except for the open top. The reservoir starts out empty and begins to overflow when the volume exceeds the capacity. For simplicity, it is assumed that overflow is instantaneous and is not affected by the physical limitations of an overflow facility, such as a spillway. The 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 withdrawals or losses from this reservoir (the overflow when the reservoir is full is not considered to be a withdrawal). A simple schematic illustration of this system is shown in Figure 1.
Figure 1 – Schematic Illustration of a Reservoir with Constant Inflow and Overflow
We will simulate the reservoir for a period of 5 days, using a daily time step. In a spreadsheet, the inflow rate (4 m3/day) must be treated as a discrete volume that is added during each time step, where the time step is represented by each row of the spreadsheet. That is, rather than explicitly defining the inflow as a rate, it is treated as a discrete volume applied in a lump sum at each time step. At each time step (i), which corresponds to a row in the spreadsheet, the volume (V) and overflow rate (S) are calculated as follows:
Vi = min(Vi-1 + Ii-1 - Oi-1, Vmax)
Si = max((Ii - Oi) - (Vmax - Vi), 0 m3)
where: Vi = volume reported at current time step (m3)
Si = Daily overflow volume over next time step (m3)
Ii = Daily inflow over next time step (m3)
Oi = Daily outflow over next time step (m3)
Vmax = Maximum allowable volume (m3)
A screen capture from the spreadsheet model is shown in Table 1. Each row in the first column represents the elapsed time (ETime). The daily inflow and outflow columns represent discrete amounts that accumulate between the time shown in the current row and the time shown in the next row. The volume column represents the volume reported at the beginning of the time shown for the current row (e.g., the volume at 2.0 elapsed days is 8 m3). The overflow represents the volume of water that overflowed between the time shown in the current row and the time shown in the next row. For example, between Elapsed Time = 2.0 days and Elapsed Time = 3.0 days (i.e., during the third day of the simulation), the overflow volume that accrued over the course of the day was 2 m3. Note that the overflow values shown in the table do not represent instantaneous flow rates; rather, they represent the discrete quantity thgat overflowed. Of course, what actually would occur in the real system in this case is that there would be no overflow from 2 days to 2.5 days, and then there would be an overflow rate of 4 m3/day from 2.5 days until 3 days. The actual quantity that overflowed over the day would be 2 m3, and hence the average overflow rate over the entire day would therefore be 2 m3/day (since the timestep represented by the rows in the spreadsheet was 1 day).
Table 1 – Example 1: Spreadsheet Results
For comparison, the same reservoir model was implemented in GoldSim, using a daily time step with an elapsed time of 5 days. Since GoldSim is a dynamic simulator that incorporates time and dimensions into the calculations, the overflow is truly represented as a flow rate and not a discrete volume. Table 2 is a summary of results that are reported by default at the scheduled daily time steps of the GoldSim model. The difference between Tables 1 and 2 is the overflow value shown at the elapsed time of 2 days. All values shown in this table represent the same thing: the instantaneous rate (or volume) reported at the point in elapsed simulation time. Hence, the instantaneous overflow rate at Elapsed Time = 3.0 days (i.e., the end of third elapsed day or the beginning of the fourth day) is equal to 4 m3/day. The volume at that point in time is 10 m3. Although the overflow rate shown at Elapsed Time = 2.0 days is 0 m3/day, this does not mean the rate was 0 m3/day for the entire day. Rather, it only means that exactly at Elapsed Time = 2.0 days, the overflow rate was 0 m3/day. The key point here is that in the GoldSim results (Table 2), the overflow rate represents an instantaneous flow rate.
Table 2 - Example 2: GoldSim Results (default result display)
If we integrate the overflow rate in the GoldSim model over each scheduled time step (e.g., using an Integrator element or another Reservoir), we can see the effect of an overflow occurring for a partial day. The added output called "Cumulative Overflow" is shown in Table 3.
Table 3 - Example 2: GoldSim Model with Integrated Overflow
The cumulative overflow represents the accumulated overflow that accrued up to that instantaneous point in time. Since the reservoir overflowed at a rate of 4 m3/d from 2.5 days to 3.0 days, then the resulting overflow volume at 3 days is 2 m3. It is very important to understand that GoldSim (silently) inserted an "unscheduled" time step at elapsed time = 2.5 days when the volume in the reservoir reached its upper bound in order to accurately simulate this situation. Note, however, that even though an unscheduled time step was inserted, GoldSim by default still only reports values at the scheduled time steps.
We can examine the GoldSim model further by specifically viewing the results at unscheduled time steps. This can be done in GoldSim by checking an option found under the Advanced Settings button in the Simulation Settings dialog (Figure 2). Within the Advanced Settings dialog, checking a box titled, "Include unscheduled updates (high resolution results) in scalar time histories" saves results at unscheduled time steps.
Figure 2 – Advanced Simulation Time Settings for Displaying High Resolution Outputs in Time History Plots
Checking this setting makes it possible to view all the changes that occur in the model. Note that this setting does not change the way calculations are executed, but just changes the way Time History Results report information. These "high resolution" results can be viewed in Time History Result elements that were created before running the simulation. Running the GoldSim model with this setting enabled, theTime History Result element will display results as shown in Table 4.
Table 4 - Example 2: GoldSim Model with High Resolution Results
Since an unscheduled time step was inserted at the elapsed time of 2.5 days, GoldSim inserts a row in the results table with the instantaneous flow values at that time. GoldSim will only show rows in which values are changing and will skip rows where all results remain constant. This is why the rows for days 3 and 4 are not shown in Table 4.
Starting in GoldSim 11.1, it is possible to change an advanced setting of the model's simulation settings, which prevents GoldSim from inserting unscheduled time steps during a simulation (see Figure 3). This advanced setting will change the way some calculations are executed by causing GoldSim to average overflow rates over the scheduled time step (such that the overflow rate represents the average over the next time step).
Figure 3 – Advanced Simulation Time Settings to Prevent GoldSim from Inserting Unscheduled Time Steps
Iif we choose to prevent GoldSim from inserting unscheduled time steps during the simulation, then the results of this model example will more closely mimic that of the spreadsheet model discussed earlier. The results shown in Table 5 reflect this advanced setting change. Note that the overflow rate on day 2 now is equivalent to the average flow rate spread out over the 1-day time step. In this case, no "unscheduled" time steps were allowed to be added during the simulation.
Table 5 - Reservoir Model: GoldSim Results without allowing unscheduled time steps
While it may be helpful to disable unscheduled timesteps when comparing GoldSim results to spreadsheet results, as a general rule this is not encouraged as adding unscheduled events allows GoldSim to more accurately represent the dynamics of the system.
Figure 4 shows four different figures in order to compare the differences between spreadsheets and GoldSim with GoldSim's various settings. The figure on the top left is how the spreadsheet reports its values (with the flow rates representing average values over a step). The figure on the upper right represents the default manner in which GoldSim displays results at each scheduled time step. Note that flow rates represent instantaneous values. The figure on the lower left is how GoldSim displays the results if outputs at unscheduled time steps are being shown (again, flow rates represent instantaneous values). Finally, the chart on the lower right is how GoldSim displays outputs if you choose not to allow GoldSim to insert unscheduled time steps at all. In this final case, the flow rates do not change over a timestep, and hence can be thought of as average values (as is the case for the spreadsheet).
Figure 4 - Simple Representation of Reservoir Overflow in GoldSim and a Spreadsheet
Example 2: Dynamic Inflow with Discharge Requests
To explore this further, a second, more complicated example is presented. 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. Overflows occur to prevent the maximum volume limit from being exceeded. The reservoir model runs for 5 days with a daily time step. Table 6 shows the inputs and outputs as they would appear in a typical spreadsheet. Even though the daily inflow, outflow, and overflow values are shown as discrete volume amounts, it is implied that these values represent average flows over the period of the time shown in the “ETime” column. For example, the inflow is a constant 7 m3/day starting at the beginning of the second day (i.e., at ETime =1.0 day) and ending just before the beginning of the third day (i.e., at ETime = 2.0 days). Similar to the first example , inflows, outflows, and overflows represent the value over the next time step while the volume is the amount reported at the elapsed time reported in column 1.
Table 6 - Reservoir Storage Calculations in a Spreadsheet
A plot of these spreadsheet results is shown in Figure 5.
Figure 5 - Example 2: Plot of Spreadsheet Model Results
To build this same model in GoldSim, we would use a Time Series element to represent the inflow and call it "Inflow". The data in the Time Series element would be explicitly defined to represent constant values over the next time interval (See Figure 6). This ability to specify what the Time Series represents in GoldSim will be discussed further in Problem #3 below.
Figure 6 - Definition of Inflow Data Timeseries
Table 7 is a screen capture of data that is entered into the Time Series element.
Table 7 - Inflow Data for GoldSim Model
To represent this model in GoldSim, we would add a Data element called "Outflow_Request" and assign it a value of 2.0 m3/d. We would then add a Reservoir element (call it "Reservoir") with the following definitions:
- Initial Value: 0 m3
- Addition Rate: Inflow
- Withdrawal Rate: Outflow_Request
- Lower Bound: 0 m3
- Upper Bound: 12.0 m3
Finally, we would add a Result element that references the Inflow, Reservoir.Withdrawal_Rate, Reservoir.Overflow_Rate, and Reservoir and run the model. The default GoldSim model results displayed in the Time History Result element are shown in Figure 7. According to this plot, the maximum overflow rate appears to have occurred at Elapsed Time = 3 days with a value of 3 m3/day. This is not the case! The results shown in this graph must be interpreted carefully.The key point to understand is that the flows reported represent an instantaneous value captured at specific points in time, NOT the average value over the time step. In this particular case, an "unscheduled" time step was inserted sometime between Elapsed Time = 2 days and Elapsed Time = 3 days (when the upper bound of the reservoir was reached). The instantaneous overflow rate was only plotted once per day (at the scheduled time steps) and therefore the outputs at unscheduled time steps do not appear in the plot.
Figure 7 - Example 2: GoldSim Model Results with Default Settings
The changes in the overflow rate between Time = 2 days and Time = 3 days cannot not be determined from this default plot, but can be viewed by enabling high resolution outputs in order to view the unscheduled time steps, as shown in Figure 8.
Figure 8 - Example 2: GoldSim Model Results with Unscheduled Time Steps
At the Elapsed Time of 2.21 days, the reservoir volume reached the capacity and overflow began. With high resolution results enabled, you can see all the changes that occur during the simulation instead of just the results at scheduled 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, all reported flows (inflow and outflow, as well as overflows) represent instantaneous, volumetric flow rates in GoldSim. 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 an unscheduled 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.
Table 8 is a tabular view of the all the GoldSim model results including the values at the unscheduled time step just after Elapsed Time = 2 days.
Table 8 - Summary of the GoldSim Model Results (including unscheduled time steps) 
Alternatively, you can prevent GoldSim from inserting unscheduled time steps as previously done in Example 1. With this setting, the results match those of the spreadsheet model (see Figure 9).
Figure 9 - Example 2: GoldSim Model Results Without Unscheduled Time Steps
The results shown in Figure 9 match the outputs of the spreadsheet model because the overflow rate is calculated as the volume of overflow that was released during the elapsed time between 2 and 3 days.
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
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 Table 9, 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?
Table 9 - 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 10 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 10 - 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 explicitly defined in GoldSim and loosely defined in a spreadsheet.
- Flow rates in GoldSim are reported as instantaneous values, whereas spreadsheet results typically represent average values (quantities over a fixed time interval).
- By default, GoldSim inserts unscheduled time steps as required to accurately model certain events (e.g., hitting an upper bound). These are are not plotted in a Time History chart. You can optionally choose to plot these.
- You can control whether GoldSim inserts unscheduled time steps at all from the Advanced Settings dialog of the model's Simulation Settings.
- 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.
Download the Model Files:
Comments
0 comments
Please sign in to leave a comment.