CRAM Documentation

CRAM is a water resources model used to support decisions for short, medium and long-range planning of a system’s raw water supply. CRAM simulates all components of a system including reservoirs, water rights, wells, diversions, exchanges and more, through an intuitive drag and drop user interface. Whether your role is decision-maker, water resources manager, planner, engineer or accountant, CRAM will provide you with a more complete understanding of your water supply and demand, as well as how to optimize the use of this limited resource. CRAM is currently used by many cities, states and agencies throughout the United States for their water resources modeling and decision-making.

More information is available at www.lynker.com/cram.

Guide

What Is CRAM

CRAM is a network flow model used to simulate water resources systems. It provides system optimization and simulation in the easy-to-use, familiar environment of Excel.

CRAM uses priority-based assignments applied to the physical constraints of a system to properly allocate water according to past, present or future operations. CRAM’s interface allows users to build networks that represent real systems. The network is then forced by hydrologic time series (flow) at a consistent time step.

CRAM networks are constructed from several types of active elements, connected by nodes. There are 8 types of active elements: Nodes, Links, Inflows, Demands, Decrees, Reservoirs, Instream Flows, and Return Flows.

Water enters the network through inflows. Like a real system, all the water that enters the system plus any change in storage at reservoirs in the system must be equal to outflows (such as through demands or links that represent river reaches that leave the study area) and losses (such as evaporation or losses to groundwater.) CRAM automatically enforces mass balance.

When CRAM solves the network, it supplies water first to those elements with the highest numerical priority, subject to any constraints, such as physical availability and capacity constraints. This makes CRAM highly suitable for analyzing systems where water rights must be administered in priority, or operations involve prioritized targets.

Applications

CRAM is used by many cities, states and agencies throughout the United States for their water resources modeling and decision-making. Some typical applications include:

• Simulate municipal water supply
• Evaluate changes in water demand
• Evaluate the effects of capital improvement projects
• Water allocation by water rights (prior appropriation)
• Evaluate climate change scenarios
• Manage water use within a watershed
• Evaluate water rights
• Simulate water resources facilities and their operations

Features

• Interactive user interface
• Reservoir operations
• Trans-basin diversions
• Minimum flow requirements
• Return flows
• Groundwater
• Advanced “What-If” scenario analysis capabilities

Under the Hood

CRAM optimizes systems while preserving mass balance using what is known as a circulating network. It solves the minimum-cost flow problem using the Out-of-Kilter algorithm, where flows (or some quantity) are solved for while minimizing costs.

CRAM runs within Excel, utilizing its graphics, spreadsheets and VBA code to create a familiar user interface.

Model Basics

Network Elements

CRAM uses 8 types of objects to represent real-world systems.

• Nodes: Nodes are general-purpose elements within the model network. They are used along with links to build a model network that represents a real-world system. Mass balance nodes are a special type of node added automatically by CRAM. They are connected to inflows and demands and are used to maintain mass balance.
• Links: Links are general-purpose elements most often used to simulate rivers or canal reaches. They are also used as arbitrary connectors between two nodes.
• Inflows: Inflows represent flow into a network. They are frequently used to represent a raw water supply such as a river.
• Demands: Demands represent flow out of a network. They are frequently used to represent withdrawals from a system such as a diversion ditch or water right.
• Reservoirs: Reservoirs represent storage within a system. They are more complex than other network elements, requiring nodes and links for proper configuration.
• Decrees: Decrees are used to model water rights. They can be used to represent the limits on total annual diversion to canals, to set annual filling limits on water rights into reservoirs, or to limit supplemental water to an annual limit without having to determine in which months it might be needed.
• Return Flows: Return flows represent water that leaves the surface water system at one location but then returns at another location and possibly another time.
• Instream Flows: Instream flows represent the minimum water that must be available in a river, for example to support fish health.

Data Objects are an additional type of model object that can be added to the network. They are used to track model output and do not affect the configuration or solution of the model. That is to say, Data Objects don’t connect with a To Node and From Node within the network.

The Network Schematic

The network schematic shows the physical connections in the model network. It is the user interface for CRAM, displaying a graphical representation of the links and nodes that represent the system. This includes inflows, reservoirs, demands, return flows, and groundwater use. Click the image below for a full resolution network schematic.

The network schematic is an interactive interface for building the model and editing model parameters. It allows the user to access most locations in the model. For instance, clicking on Link 15 opens a pop-up that lists the link name, how it’s connected in the model (From Node, To Node), and the types of output it should have. This is shown in the image provided below.

The User Input Sheet

The user input sheet contains the settings for a model. This allows the user to run the model, set model parameters, and show additional model details to name a few. The complexity of this sheet is dependent on the complexity of the model.

The user sheet can set the level of detail in the model, with the “Basic User” hiding most model sheets as discussed in the CRAM Worksheet Names section. The “Advanced User” reveals the timeseries data sheets within the model and “Developer” mode unhides all of the sheets in a model workbook.

Time Steps

CRAM can be operated at any time step; however, daily, weekly, and monthly are the most common.

CRAM operates using a two time step hierarchy: the major time step and the minor time step. These two modeling concepts are used to represent real-world time periods. For example, a common model representation would be that the major time step represents years and the minor time step represents months.

The minor time step is where most of the work gets done - CRAM solves the network once or more at each minor time step. The major time step is a concept used to organize data for the minor time steps.

Operation Steps

Operations steps are used to build complex system operations that are often involved with water rights, reservoirs and exchanges. More information on the use of operation steps can be found in the Model Details documentation.

User Code

Since CRAM runs within Excel, VBA code can be used to check and changes values in the model while it is running. These functions allow the user to intercept the execution of the model at various points to allow the use of macros. This is useful for creating customized conditions based on complex system operations. More information on the user code can be found in the Model Details documentation.

CRAM Worksheet Names

Most of the CRAM model data (time series data, model parameters, network connections) are stored within Excel worksheets. The worksheets have been listed in the tables below based on their functionality within in the model. By default many of these sheets are hidden to users. This functionality can be changed via the Worksheet Dictionary.

Standard CRAM Sheets: This is the core functionality within CRAM. Do not rename or delete these sheets.

Sheet Name	User Level	Description
User Controls	Basic	Contains settings for the model, most recent run.
Network Schematic	Basic	Contains the Network diagram.
Worksheet Output Template	Basic	Contains list of elements to export to output file.
Worksheet Dictionary	Basic	Controls sheet visibility.
Model Workbook Version History	Basic	Worksheet to track changes to model workbook. Manually updated by users.
Output Sheet	Basic	Worksheet to store model results for model run. CRAM raw output.

Internal CRAM Sheets: These sheets are hidden in the default “Basic User” settings. Do not rename or delete these sheets.

Sheet Name	User Level	Description
Global Data Sheet	Developer	Contains settings for CRAM model, global variables for model execution, most recent run and dialog box settings.
Node Sheet	Developer	Contains text data used in node dialog box.
Link Sheet	Developer	Contains text data used in link dialog box.
Inflow Sheet	Developer	Contains text data used in inflow dialog box.
Demand Sheet	Developer	Contains text data used in demand dialog box.
Reservoir Sheet	Developer	Contains text data used in reservoir dialog box.

Time Series Data Sheets: These sheets are automatically created by CRAM as the user builds the model. They are sequentially numbered by CRAM.

Sheet Name	User Level	Description
Link n	Advanced	Contains time series data for user defined link sheets.
Inflow n	Advanced	Contains time series data for user defined inflow sheets.
Demand n	Advanced	Contains time series data for user defined demand sheets.
Instream flow n	Advanced	Contains time series data for user defined instream flow sheets.

Creating a Network

In its simplest form, a CRAM network consists of a single node with one inflow and one demand. The inflow specifies the amount of water entering the system and the demand specifies the amount of water leaving the system. Because CRAM enforces mass-balance, all flow entering through the Inflow must exit through the Demand. If the demand has been constrained to a capacity less than the Inflow flow found in one of the minor time steps the model will determine that an infeasible solution has occurred.

Most CRAM models will consist of a few hundred nodes connected by a larger number of links allowing water (flow) to be delivered to many different competing demands from a variety of sources. CRAM optimizes the system to determine the most efficient use of the water given the delivery constraints (both physical and administrative). After a solution has been attained, the user may want to experiment with additional stresses, constraints, or options on the simulation network. This is a core component of CRAM’s scenario analysis.

The modeling tool supports reservoirs, instream flows, decrees, and return flows which can greatly increase the complexity of models. CRAM’s operation steps allow modeling of the most complex exchanges and water rights. Please read the additional information about these features to be aware of “gotchas” that you may encounter when using the CRAM modeling tool.

Note

The blue note boxes provide tips that may be helpful in avoiding modeling errors such as creating infeasible solutions.

Adding a Node

Nodes are the basic building block of a CRAM network. Nodes are used to connect other network arc types (e.g., links, inflows, demands) and help determine the potential paths for flow in the model. There are two kinds of nodes in a model.

1. General nodes are nodes that have a single instance and physical location in the model. These are the types of nodes added to the model by the user. General nodes can be identified by a circle with a number inside of it.
2. Special case nodes are related to mass balance. In each CRAM model there is one mass-balance node, but in the network diagrams (see The Network Schematic) it is represented in multiple locations. However, they all represent the same node in the model. For instance, an inflow arc is attached from a mass balance node, while a demand arc is attached going-to a mass balance node. Mass balance nodes in the network schematic can be identified by the MB prefix before the node number. The MB node number is irrelevant, and simply used for book keeping. These are automatically added to a model network as needed by CRAM.

All nodes in the network maintain mass balance during every step of the solution.

To add a node to the network, click on the Create Node button on the toolbar (shown below) or click on the menu ExcelCRAM->Network->Add Object… menu item and select Node from the object type drop-down box.

Node Basics

The basic features necessary to add a model node are discussed in this section. Here we step through the fields on the Edit Node dialog box.

The Node Number is automatically assigned by CRAM. Nodes cannot be reused in the network.

The Node Name is user-defined and is typically left blank. If the Node Name is used, it must be an ASCII string (alphanumeric and/or special characters). A good example for a node name would be a stream gage name, location, or other geographic reference.

The Comment box allows the user to add any notes about the node that might be important to the design.

Advanced Node Setup

• The Display Group is set to “1” by default. The display group is an advanced feature that allows the user to hide network objects (nodes, links, etc.) in the network schematic. For more on display groups, see Model Details documentation.

Adding a Link

A link connects two nodes. It has four user configurable parameters which help determine the amount of flow passing from the “From Node” to the “To Node” in the model. The four parameters are:

1. High: The High parameter is the maximum amount of flow that can pass through the link in a single solution time step. Typically, the default value of “Infinite” is used. However, integer values are commonly used to represent pipeline capacities.

Note

Advanced Note: If the total amount of water at the From Node is greater than the sum of all High parameters on all arcs leaving that node, an infeasible solution will occur. Using the default value of “infinite” avoids this problem.

2. Low: The Low parameter is the minimum amount of flow that MUST pass through the link in a single solution time step. Typically, the default value of “0” is used.

Note

If the Low is set higher than the High parameter, an infeasible solution will occur. If the From Node for this link does not have as much flow into it as the sum of all the Low parameters leaving that node, an infeasible solution will occur.

3. Priority: The Priority parameter helps the network to determine the relative priority of sending water through a link. Priorities (or ranks) in the network model are additive.

Note

As a model becomes more complex, the additive values of different flow paths can become more complicated.

4. Flow: The flow parameter is the optimized result of a model solution. The user is unable to change this value - it is model output only.

To add a link to the network, click on the Create Link button on the toolbar (shown below) or click on the menu ExcelCRAM->Network->Add Object… menu item and select Link from the drop-down box.

Link Basics

The basic features necessary to build a model link are discussed in this section. Here we step through the fields on the Edit Link dialog box.

The Link Number is automatically assigned by CRAM. Link numbers cannot be reused in the network.

The Link Name is a user-defined ASCII string that provides a common name to describe the reach. It is recommended that the name be unique within the first 32 characters, but this not required. The name should normally be less than 256 characters in length.

The From Node identifies the node at the upstream end of the link.

The To Node identifies the node at the downstream end of the link. The To Node is where the flow from this link enters and mixes with all other sources (links).

Create Time Series Sheet/Go to Time Series Data button. This button has one of two labels on it. If the link being edited does not currently have any time series data associated with it, the button will read Create Time Series Sheet. Clicking on the button will create a formatted worksheet in the current scenario to hold time series data for the link. The user will need to populate the sheet with the appropriate data.

Note

Most links DO NOT have time series data associated with them. Links used to build advanced reservoirs are an exception to this.

The High field provides a space to specify a constant maximum capacity for the link. A value provided here will last for all minor time steps in a model run unless there is a Link Time Series Data sheet to override the value. A value of “Infinite” here indicates that the link does not have a capacity limit.

The Low field provides a space to specify a constant minimum flow for the link. A value provided here will last for all minor time steps in a model run unless there is a Link Time Series Data Sheet in the current scenario with the Low parameter specified there.

Note

If a negative value is used in this field, water will flow “backwards” through the link generating a negative priority for each unit of flow transferred. This should be used with caution, and it is recommended the priority value is set to zero.

The Priority field provides a space to enter the priority to be assigned to that link.

The Comment box allows the user to add any notes about the node that might be important to the design.

Advanced Link Setup

• The Display Group is set to “1” by default. The display group is an advanced feature that allows the user to hide network objects (nodes, links, etc.) in the network schematic. For more on display groups, see Model Details documentation.
• The Step Sequence allows you to enter the state of the element (Open, Closed, Frozen) for each operation step. More information can be found in Model Details documentation.

Note

The default value is “O” for open. Other values should only be used by advanced CRAM users.

• Output To Worksheet provides a list of check boxes for Link parameters that can be written to the output worksheet when the model is run.

Adding an Inflow

Inflows provide the source of water for an CRAM network. Once in the model the water (flow) is divided up among the demands based on the total priority of routing, from the inflow to the bottom of the network. Behind the scenes, the model “circulates” the water using the mass balance nodes.

• An inflow can be connected TO any node (except a mass balance node)
• An inflow will always be connected FROM the mass balance node

Inflows only have one parameter, Flow. For Inflows, the Flow defines both the High and the Low on the arc. If the Flow from an inflow is not able to find a route through the network and back to the mass balance node an infeasible solution will occur.

To add an inflow to the network, click on the Create Inflow button on the toolbar (shown below) or click on the menu ExcelCRAM->Network->Add Object… menu item and select Inflow from the dialog box that appears.

Inflow Basics

The basic features necessary to add model inflows are discussed in this section. Here we step through the fields on the Edit Inflow dialog box.

The Inflow Number is automatically assigned by CRAM. Inflow numbers cannot be reused in the network.

The Inflow Name is a user-defined ASCII string that provides a familiar name to describe the inflow. Inflows are ALWAYS named by users within the model. We recommend that the name be unique within the first 32 characters, but this not required. The name should normally be less than 256 characters in length.

The To Node identifies the node at the receiving end of the inflow. The To Node is where the flow from the inflow enters and mixes with all other sources (links or inflows).

Create Time Series Sheet/Go to Time Series Data button. This button has one of two labels on it. If the inflow being edited does not currently have any time series data associated with it the button will read Create Time Series Sheet. Clicking on the button will create a formatted worksheet in the current scenario to hold time series data for the link. The user will need to populate the sheet with the appropriate data.

Note

Inflows should always have time series data associated with them. Failure to create a Time Series Sheet will result in a zero inflow.

The Comment box allows the user to add any notes about the node that might be important to the design.

Advanced Inflow Setup

• The Step Sequence specifies the operation steps to be used for this Inflow. More information can be found in Model Details documentation.

Note

The default value is “O” for open. Other values should only be used by advanced CRAM users.

• The Display Group is set to “1” by default. The display group is an advanced feature that allows the user to hide network objects (nodes, links, etc.) in the network schematic. For more on display groups, see Model Details documentation.
• Output To Worksheet provides a list of check boxes for the Inflow parameter that can be written to the output worksheet when the model is run.

Adding a Demand

Demands are used to route water to specific users in the CRAM network (e.g., cities, farmers, ditches). The water that passes through a demand arc is not available for use anywhere else in the network during the same time step. Demands can be thought of as the final destination of water within the network.

The capacity of a demand is determined by the High parameter while the minimum flow that must pass through a demand arc is set by the Low parameter.

• A demand can be connected FROM any node (except a Mass-Balance node)
• A demand is always connected TO a mass balance node

To add a demand to the network, click on the Create Demand button on the toolbar (shown below) or click on the menu ExcelCRAM->Network->Add Object… menu item and select Demand from the dialog box that appears.

Demand Basics

The basic features necessary to add model demands are discussed in this section. Here we step through the fields on the Edit Demand dialog box.

The Demand Number is automatically assigned by CRAM. Demand numbers cannot be reused in the network.

The Demand Name is a user-defined ASCII string that provides a familiar name to describe the demand. Demands are ALWAYS named by users within the model. We recommend that the name be unique within the first 32 characters, but this not required. The name should normally be less than 256 characters in length.

The From Node identifies the node at the upstream or distributing side of the demand.

Create Time Series Sheet/Go to Time Series Data button. This button has one of two labels on it. If the demand being edited does not currently have any Time Series data associated with it the button will read Create Time Series Sheet. Clicking on the button will create a formatted worksheet in the current scenario to hold time series data for the link. The user will need to populate the sheet with the appropriate data.

Note

Demands should always have time series data associated with them. Failure to create a Time Series Sheet will result in the demand having a default value of infinite.

The High field provides a space to specify a constant maximum capacity for the demand. A value provided here will last for all minor time steps in a model run unless there is Demand Time Series Data Sheet to override the value. A value of “Infinite” here indicates that the demand does not have a capacity limit. This can be useful for creating a demand that will take all available flow in a network.

The Low field provides a space to specify a constant minimum flow for the demand. A value provided here will last for all minor time steps in a model run unless there is a Demand Time Series Data Sheet in the current scenario with the Low parameter specified there.

Note

If the user sets the Low value higher than the available water in a time step an infeasible solution will occur. Non-zero low values should be used sparingly.

The Priority field provides a space to enter the priority assigned to that demand.

Advanced Demand Setup

• The Display Group is set to “1” by default. The display group is an advanced feature that allows the user to hide network objects (demands, links, etc.) in the network schematic. For more on display groups, see Model Details documentation.
• The Step Sequence allows the user to enter the state of the element (Open, Closed, Frozen) for each operation step.

Note

The default value is “O” for open. Other values should only be used by advanced CRAM users.

• The Comment box allows the user to add any notes about the node that might be important to the design.
• Output To Worksheet provides a list of check boxes for Demand parameters that can be written to the output worksheet when the model is run.

Adding a Reservoir

A CRAM Reservoir is used to simulate the storage of water in a reservoir. This network object may be used to represent either surface or groundwater storage systems. Reservoirs can be built to include complex operations such as hydropower, water rights exchanges, flood storage and dead storage.

To add a reservoir to the network, click on the Create Reservoir button on the toolbar (shown below) or click on the menu ExcelCRAM->Network->Add Object… menu item and select Reservoir from the dialog box that appears.

Reservoir Basics

The basic features necessary to add reservoirs are discussed in this section. Here we step through the fields on the Edit Reservoir dialog box.

The Reservoir Number is automatically assigned by CRAM. Reservoir numbers cannot be reused in the network.

The Reservoir Name is a user-defined ASCII string that provides a familiar name to describe the reservoir. Reservoirs are ALWAYS named by users within the model. We recommend that the name be unique within the first 32 characters, but this not required. The name should normally be less than 256 characters in length.

The From Node identifies the node at the upstream or distributing side of the reservoir.

The To Node identifies the node at the downstream end of the reservoir. This node is where the flow stored by the reservoir in the previous time step (Minor Time Step) is released back to the network. A link from this node to the From Node will allow the reservoir to retain storage from one time step (Minor Time Step) to another.

Create Time Series Sheet/Go to Time Series Data button. This button has one of two labels on it. If the reservoir being edited does not currently have any Time Series data associated with it the button will read Create Time Series Sheet. Clicking on the button will create a formatted worksheet in the current scenario to hold time series data for the link. The user will need to populate the sheet with the appropriate data.

Note

Reservoirs usually do not have time series data associated with them. However, links that are a part of the reservoir, such as those used to represent target storage, usually do have time series data.

The Dead Storage is currently inactive.

The Active Storage is currently inactive.

The Total Capacity provides a place to record the total storage capacity of the reservoir. This can be either active storage or total storage depending on how you have decided to model the reservoir.

Note

The Total Capacity must be less than or equal to the maximum value added to the reservoir elevation-area-volume curve (data).

The Initial Contents provides a place to record the initial storage contents of the reservoir. This can be a variety of values (including zero), depending on how the reservoir is modeled.

The Volume-Area Curve contains pairs of numbers that describe the volume-area relationship for the reservoir. This table of numbers is used to calculate average surface area over a time step (minor time step) to calculate evaporation. The numbers for this field are entered in increasing order from the lowest volume to the reservoir’s total capacity with the corresponding area following the colon. (i.e. 0:0, 100:40, 200:60 would represent a reservoir that had covered 40 acres when it contained 100 acre-feet (AF) and covered 60 acres when it contained 200 AF). Values between the points are linearly interpolated to determine volume and surface area. Using the previous example, 150 AF of water would correspond to 50 acres in surface area.

The Seasonal Evaporation Rate Series stores the evaporation rates as a series of comma delimited numbers. There should be one value for each minor time step in your model.

Note

There are 2 options for evaporation data. 1. Time series of reservoir evaporation (by Minor Time Step). 2. Annual repeating evaporation values (by Minor Time Step).

Advanced Reservoir Setup

• The Display Group is set to “1” by default. The display group is an advanced feature that allows the user to hide network objects (demands, links, reservoirs, etc.) in the network schematic. For more on display groups, see Model Details documentation.
• The Comment box allows the user to add any notes about the node that might be important to the design.
• Output To Worksheet provides a list of check boxes for Reservoir parameters that can be written to the output worksheet when the model is run.

Adding an Instream Flow

A CRAM Instream Flow is a specialized object used in model to simulate water rights that are used to keep water in the stream and make them available below the instream flow for diversion without increasing the priority of the demands below the instream flow.

To add an instream flow to the network, click on the “Create an Instream Flow” button on the toolbar (shown below) or click on the menu ExcelCRAM->Network->Add Object… menu item and select Reservoir from the dialog box that appears.

Instream Flow Basics

The basic features necessary to add instream flow objects are discussed in this section. Here we step through the fields on the Edit Instream Flow dialog box.

The ISF Number is automatically assigned by CRAM. Instream flow numbers cannot be reused in the network.

The ISF Name is a user-defined ASCII string that provides a familiar name to describe the instream flow. ISFs are ALWAYS named by users within the model. We recommend that the name be unique within the first 32 characters, but this not required. The name should normally be less than 256 characters in length.

The From Node identifies the node at the upstream or distributing side of the instream flow.

The To Node identifies the node at the downstream end of the instream flow. This node is where the flow from the instream flow re-enters the network and mixes with all other sources at the node specified in this field.

Create Time Series Sheet/Go to Time Series Data button. This button has one of two labels on it. If the instream flow being edited does not currently have any Time Series data associated with it, the button will read Create Time Series Sheet. Clicking on the button will create a formatted worksheet in the current scenario to hold time series data for the link. The user will need to populate the sheet with the appropriate data.

Note

Instream flows have time series data associated with them depending on their complexity. For instance, if the instream flow varies over time, then the user should create corresponding time series data. However, if the instream flow is constant, a single value within the **High* parameter field is sufficient.*

The High field provides a space to specify a target or goal for the instream flow. A value provided here will last for all minor time steps in a model run unless there is Time Series Data to override the value. Do not leave the high value to Infinite! The High field for this network element represents the “minimum” amount of water that the instream flow should try to preserve in the river. Excess flow capabilities of the river at this location should be represented by a standard model link (with zero priority) set in parallel with the instream flow object. Thus, the sum of the two parallel arcs (1 instream flow and 1 link) will represent the total flow through the river.

Note

Do not set the High value to Infinite, or else the model will try and route all available water through the instream flow object.

The Low field provides a space to specify a constant minimum flow for the instream flow. A value provided here will last for all minor time steps in a model run unless there is a Instream Flow Time Series Data Sheet in the current scenario with the Low parameter specified there. Since the instream flow is already trying to attain the value set as a High, it is unnecessary to change the Low from zero.

Note

An instream flow Low should always be set to zero (0). An infeasible solution will likely occur otherwise.

The Priority field provides a space to enter the priority assigned to that instream flow.

Best Practices: The instream flow should be setup with a single link above it and a single link downstream of it. In the middle, there are two parallel arcs, an instream flow and a link. This helps to avoid confusion regarding the link priorities. The total flow of a river reach (sum of instream flow and its parallel link) is easily ascertained from next downstream link. See image below.

Advanced Instream Flow Setup

• The Display Group is set to “1” by default. The display group is an advanced feature that allows the user to hide network objects (demands, links, reservoirs, etc.) in the network schematic. For more on display groups, see Model Details documentation.
• The Comment box allows the user to add any notes about the node that might be important to the design.
• Output To Worksheet provides a list of check boxes for Instream Flow parameters that can be written to the output worksheet when the model is run.

Moving Objects in CRAM

When a new node is added to the model, it is positioned as close as possible to the last cell selected on the network schematic. The node can be repositioned by bringing up the Drawing Toolbar and clicking on the arrow to move the drawing of the node. Alternatively, right-click the node, then left-click to remove the pop-up menu, then place the cursor at the edge of the node to grab it and move it.

Running a Model

Loading a Model

CRAM consists of two parts: Lynker’s CRAM model engine (.XLAM) and the user-developed model network diagram, data, and scenarios (.xcwm).

CRAM Model Engine

Before the model can be run, Lynker’s CRAM model engine needs to be opened and the model file loaded. The CRAM model engine is opened by starting Microsoft Excel and loading the file XLCRAM2007.XLAM, or by simply double clicking the XLCRAM2007.XLAM file. The model engine can be found in the installation directory (the default directory is C:/Program Files/Lynker).

A pop-up dialog box may appear when this CRAM engine is loaded into Excel, warning that the workbook contains macros and that some macros may contain viruses that could be harmful to the computer. Click on the “Enable Macros” button to allow the CRAM engine to install the menu interface to the Excel ribbon.

Note

Note on Excel Macro Settings: To check your Macro settings in Excel 365, do the following. Click the “File” menu, then click “Options” in the lower left corner. From the Excel Options popup box, first click “Trust Center” in the lower left, then click the “Trust Center Settings” button. A new Trust Center popup box will appear. Navigate to the “Macro Settings” on the left panel, and click “Disable all macros with notification”.

CRAM Model File

If the user has not already developed a CRAM model, a network can be built using the tools and menus in Excel CRAM (see Creating a Network). If a model has already been developed, the model network can be loaded and the data and scenarios can be found in the *.xcwm file. Load the model file by finding the Add-in section in the Excel ribbon, then click on Excel CRAM->Workbook->Open Existing Workbook and select the appropriate .xcwm file. If the user does not see this file listed in the dialog box displayed, the user will need to change the current directory by navigating to the folder containing the model (*.xcwm).

Starting a Model Run

To run a CRAM model click the Excel CRAM menu, and then the “Start Simulation” option. Alternatively, click the triangle icon pointing to the right.

This will open the Run Simulation dialog box that controls the model run. The first two edit fields identify the starting and ending years of the model run. These fields should have numbers that fall within the range of values in the Year (Major Time Step) column of your time series data sheets.

Note

If the start year is before the first year of time series data, the model will produce the Debugging Tool indicating that an error has occurred. Similarly, if the end year is after the last year of time series data, the model will indicate an error has occurred.

When CRAM begins a simulation, users will see model updates in the lower left corner of Excel. The updates quickly change from ‘initializing network arrays’ to ‘reading time series’ and then display an update of the year-season that CRAM is executing. For instance, in the image below, CRAM is running month 6 of year 1955.

Note

When running CRAM, it is common for Excel to display (not responding), which typically indicates that Excel has frozen. This is normal behavior in newer versions of Excel running CRAM. Allow the model to finish the simulation and CRAM will run and execute properly.

When the simulation has completed, a dialog box will appear showing the simulation runtime.

Viewing Model Output

Once a model run has finished, time series data will be stored in the Output Sheet, according to inflows, demands, links, and other network elements specified by the user. For more information on controlling the model output, and the types of model output that are available, see Model Output. An example of output from CRAM is shown below.

Model Output

Extracting Model Output

The Network Schematic allows users to interact with network elements and extract model output. By clicking on an object, users open the edit object dialog box. At the bottom of each dialog box there are multiple check boxes where users can select the output to export to a worksheet. In the Edit Demand figure below, output has been selected for Flow, High, and Shortage.

For run-time efficiency, CRAM saves only those parameters that are defined as output before the model executes. It is not possible to retrieve additional data after a run that was not included on the output templates. Therefore, if additional output is needed simply select the object (link, reservoir, inflow, etc.) and check the desired output (flow, high, low, etc.).

Output Variables

There are 7 types of data that can be output from CRAM: Flow, High, Low, Priority, Contents, Evaporation, and Shortage. Some output is only available to certain network elements (links, reservoirs, etc.). The output variables are described in the table below. Information on the model parameters can be found in Creating a Network, arranged by network element.

Parameters	Description
Flow	Denotes the amount of flow that the model found to be the optimal amount to pass through the network element considering all network element priorities and constraints. The value of the Flow variable is generated by CRAM. Flow is the typical output users will want to generate from their CRAM model. The Flow parameter applies to Links, Demands, Reservoir, Inflows, Instream Flows, and Decrees. For Data Objects this parameter is set by the User Code.
High	Denotes the maximum amount of flow allowed through the link in a single time step. The High parameter applies to Links, Demands, Instream Flows, and Decrees. If the High parameter is set to Infinite (default), the High output will often be similar to the Flow output. If the High parameter is set by the user (ex., 200), the High output will be limited by the High parameter.
Low	Denotes the minimum amount of flow that must pass through an element in a single time step. The Low parameter applies to Links, Demands, Instream Flows, and Decrees. The Low parameter default value of 0 is recommended; however, the value of this parameter can be adjusted by the user.
Priority	Denotes the relative benefit or priority of routing flow through this network element. Priorities in the model are additive so two elements in series will have their priorities added together and may create a combined priority greater than another element in parallel. The Priority parameter applies to all elements except Inflows.
Contents (storage)	Denotes the end of month storage contents of the Reservoir element. The Contents variable applies only to Reservoir elements. The initial contents are set by the user, but after the first time step the reservoir contents are calculated by CRAM.
Evaporation	Denotes the total evaporation from the reservoir during the current time step. The Evaporation variable applies only to Reservoir elements. The evaporation is calculated by CRAM according to the surface area/volume curve supplied by the user.
Shortage	Denotes the difference between the High (value desired) and the Flow (value attained) during the current time step. The shortage output is calculated by CRAM, and is typically used when analyzing output from Demand objects.
Capacity	Denotes the annual volumetric flow limit that is able to pass through a link. The volume of flow that has passed through a link is calculated by CRAM, and the link will close when the limit is reached so that no more flow passes through the object. The Capacity variable applies only to Decree objects.

Worksheet Output Template

Anytime output is selected by the user (Flow, High, Shortage, etc.), a corresponding entry is automatically added to the sheet Worksheet Output Template. This sheet contains a list of all the network object that have been selected by the user to be output from CRAM. As shown below, the Worksheet Output Template lists the element type, element number (for that type), the type of output selected, the order number, the name of the element, and the output sheet where the location will be stored. Notice that some objects have multiple outputs. For instance, Demand 1 has output for High, Flow, and Shortage.

While the Worksheet Output Template stores a list of the data that will be exported from CRAM, the various output sheets actually display and store the data. Model output can be saved to any number of model output sheets as specified by the user. Output is stored in the sheet Output Sheet by default.

Output Location

The model output is stored according to the name specified in Column F of the Worksheet Output Template, which has the heading “Output Sheet” (see image above). When output is initially added, a new corresponding entry is added to the Worksheet Output Template and the value in Column F will have the value “Default”. Entries in Column F with the value “Default” will be automatically exported to a sheet specified in the Run Dialog Box (see figure below). The default name for this worksheet is Output Sheet.

Users are encouraged to export different types of output to different locations (sheets). For instance, in the Worksheet Output Template image above, some demand related output will export to a sheet named “Demand Output”, and some reservoir related output will export to a sheet named “Reservoir Output”. The output location can be customized to meet the user’s needs. To change the output location, do the following:

• Write a new sheet name in Column F for the variable of interest, such as ‘Shortages’ or ‘Watershed Output’.
• Click “Read Output Template Sheets” from the ExcelCRAM menu to save the change, as shown in the image below.

Note

Clicking “Read Output Template Sheets” ensures that changes to the Worksheet Output Template are not lost. For instance, if an object is selected from the Network Schematic before “reading the sheet”, the changes will not be saved.

Defining the Order of the Output

By default, CRAM places all output on the Output Sheet in numeric order, grouped by element type (Link, Inflow, Demand, etc.) as it is listed in the Worksheet Output Template . Adding new network elements later causes the output order to change because the new output is inserted in at its numerical place.

Users can manually adjust the order of model output by specifying a numbered order in Column D named “Output Order” of the Worksheet Output Template. To change the order of the output, do the following:

• The default value for Column D is 10,000,000. When the output from the Worksheet Output Template is displayed in the output sheets (e.g., Output Sheet), the output is put in numerical order, with lower values appearing in columns to the left and higher values in columns to the right. The order is purely relational, so there is no effect of gaps in number sequence. Sometimes it is valuable to intentionally leave gaps so additional parameters can be inserted into the order of the list at a later time. For instance, output related to an upper watershed may be numbered in the 5000’s, while output from a lower watershed may be numbered in the 8000’s.
• When adding the output order is completed, from the Add-ins section of the Excel Ribbon select “ExcelCRAM / Output / Read Output Template Sheets”. This will rearrange the output parameters in increasing order. Next, select “ExcelCRAM / Output / Read Output Template Sheets” from the Add-ins menu. This will save the changes to the Worksheet Output Template. Failing to read and then write the output sheet will result in the changes being lost the next time the Worksheet Output Template is read (e.g., when a parameter is selected on the Network Schematic sheet).
• Run the model to get the output displayed on the output sheets in the order specified.
• An example of the interaction between the numbering on the Worksheet Output Template and the order on the default Output Sheet is shown below. Link 3 is ordered first with a value of 200, followed by Link 16 with a value of 210, followed by Link 9 with a value of 300.

Model Details

This page will feature content on the following topics:

1. Operation Steps: Operations executed within each time step, which allows for complex water rights operations without adding custom code.
2. User Code: Customized user code added to the model using Excel’s Visual Basic for Applications (VBA).
3. Display Groups: Advanced display options in the Network Schematic allowing users to group and hide network elements.

This section is currently under development.

Interactive CRAM Tool

A simple interactive CRAM tool is available online to demonstrate how the model works. It can be found at Lynker’s Shiny page.

Model Version

This lists the changes to CRAM by release number.

Version 4 Releases

Model version 4 releases include updated compatibility with Excel 2013 and major enhancements to the user interface.

CRAM 4.043

Release Date: February 3, 2020

New Feature

1. New Exchange object added to the model. This can be found in the CRAM model ribbon “Add Exchange”.

CRAM 4.042

Release Date: September 13, 2019

General Update

1. New status update when user-defined (premade) scenarios are loaded. This pop-up dialog box provides a description of the scenario that has been loaded into the model.

CRAM 4.041

Release Date: December 12, 2018

General Update

1. Added code to save the ribbon handle.

CRAM 4.040

Release Date: November 16, 2018

New Feature

1. Users can now save default label colors in the network schematic. Modified ColorObject, ColorObject2, DrawNode, and DrawMBNode to use the saved settings on the GlobalDataSheet.

CRAM 4.039

Release Date: November 9, 2018

General Update

1. Changed the width of the premade scenarios to improve readability.

CRAM 4.038

Release Date: October 26, 2018

General Update

1. Changed main solver loops to not throw an error if the first instream flow (ISF) convergence test doesn’t converge. Now it continues to solve the return flows (if any), and then produces an error if the second ISF convergence test doesn’t converge.

CRAM 4.037

Release Date: May 21, 2018

Bux Fixing

1. Added code to fix adding worksheet to Worksheet Dictionary list having to many spaces between new worksheets.
2. Added code to disable error message when the Excel Ribbon disables during workbook close.

CRAM 4.036

Release Date: April 3, 2018

User Interface

1. Added a Ribbon to the application. CRAM now features a “CRAM” tab in the Excel Ribbon. This provides for a more seamless user interface.

CRAM 4.035

Release Date: April 3, 2018

User Interface

1. Added a new navigation menu and new navigation buttons (network schematic, basin map, worksheet dictionary) to the Add-ins tab, and updated to the main XLCRAM engine.

CRAM 4.034

Release Date: February 16, 2018

Bug Fixes

1. There was a problem reading Reservoir Timeseries Sheets in batch simulation mode. Versions before this would throw an error if you did a batch simulation with reservoir timeseries data sheets.

CRAM 4.033

Release Date: February 5, 2018

Bug Fixes

1. Model shutdown process was producing an error that produced a popup box asking for a password. Fixed.
2. Editing the network before a run drops the object from the network data worksheets. Fixed.

CRAM 4.032

Release Date: December 12, 2017

General Update

1. Added subroutine CheckNumberofStyles and DropUnusedStyles() and RemoveUnusedStyles() to the main engine to remove the bad formats that mess up model workbooks. These help to reduce the number of styles in a model workbook.

CRAM 4.031

Release Date: December 6, 2017

New Feature

1. Added code and worksheets for PremadeScenario settings to main XLCRAM model engine.

Bug Fixes

1. Added module modFixUnusedStyles to the codebase. This is needed for some .xcwm model files.

CRAM 4.030

Release Date: September 6, 2017

Bug Fixes

1. Changed default for ConvergenceLimit named range from FALSE (incorrect) to 0 (correct).

CRAM 4.029

Release Date: August 29, 2017

Bug Fixes

1. Fixed bug in Reading Reservoir timeseries data (where evaporation data varies year to year).

CRAM 4.028

Release Date: August 24, 2017

Bug Fixes

1. Changed code to fix when the cursor goes into hourglass mode.

CRAM 4.027

Release Date: August 22, 2017

General Update

1. Revised model engine to allow an option to relax convergence limit in base model on the Start Simulation dialog box, which is also stored on the Global Data Sheet.

CRAM 4.026

Release Date: July 6, 2017

Bug Fixes

1. Removed some #REF named ranges on the User Controls worksheet that were imported from the demo model.

CRAM 4.025

Release Date: June 16, 2017

Bug Fixes

1. Fixed Goto Timeseries Data function to make sure the worksheet is visible before activating it. Otherwise it doesn’t work for hidden worksheets.

General Update

1. Added a parameter to the DebugNetwork() function to indicate the model problem is a convergence loop and it can’t be debugged with the infeasible checker because it isn’t infeasible.

CRAM 4.024

Release Date: May 31, 2017

Bug Fixes

1. Fixed a bug that would develop because GlobalScalingFactor wasn’t loaded when the ReadReservoirDataSheet was called and the Initial Contents value was throwing an error when reading the Worksheet Output Template. GlobalScalingFactor wasn’t loaded and the Contents were then zero and the Initial Contents fell out of range.

CRAM 4.023

Release Date: April 21, 2017

Bug Fixes

1. Fixed bug in creating new model workbook. modUserModeCustomCode wasn’t being copied correctly causing new model command to fail.

CRAM 4.022

Release Date: April 19, 2017

User Interface

1. Incorporated updated buttons and look for the User Controls worksheet into the main code engine.

General Update

1. Added DropUnusedStyles() to the OverheadCode module so that it can be used in new models to fix errors where workbooks contained too many styles.

CRAM 4.021

Release Date: April 5, 2017

General Update

1. Added code to make sure worksheets are visible before activating them.

CRAM 4.020

Release Date: March 13, 2017

General Update

1. Removed Binary Output code
2. Added Worksheet Dictionary, User Controls, and ObjectiveFunctionDefinition worksheet to the default CRAM package.

General Update

1. Added code to turn off StatusBar messages as an option on a Single Scenario run.

CRAM 4.019

Release Date: February 1, 2017

Enhanced Feature

1. OWRB Release for Reservoir Timeseries capabilities.

CRAM 4.017

Release Date: December 2, 2016

General Update

1. Revised VBA code for Reservoir Time Series worksheets to allow Evaporation on the worksheet.

CRAM 4.015

Release Date: February 23, 2016

Bug Fixing

1. Fixed problem with batch simulation and GlobalScalingFactor (it doesn’t need to have that used in the subroutine).

CRAM 4.014

Release Date: August 18, 2015

Bug Fixing

1. Revised Code for Excel 2013 so that when it closes workbook, it doesn’t lose ExcelCRAM menus (problem with Save as AddIn button under Excel 2013).

Version 3 Releases

Model version 3 releases include updated compatibility with Excel 2000, 2003, and 2007.

Help

Have more questions about CRAM?

Interested in making CRAM work for you?

Contact Us:

Roger Wolvington	Bill Szafranski
CRAM Developer/Hydrologic Modeler	Water Resources Scientist/Modeler
rwolvington@lynkertech.com	bszafranski@lynkertech.com
720-446-1705	720-446-1709