NMOD Primer Ch 21

As mentioned during introduction, how well one understands a chapter in this primer depends on whether one has read and understood preceding chapters. Without a good understanding of NMOD from previous chapters of this primer, the discussion here may at times be difficult to follow. However, since spreadsheet is a well-known environment and most people are aware of its advantages and limitations, it may be possible to jump over some of the hurdles here to gain a pretty good understanding of what is explained here.

The impact of electronic spreadsheets on businesses as well as individuals has been immense and incredible. They are wonderful tools that freed millions from the chains in IT department queues, while relieving some of the pressures on IT departments and allowing them to focus on large, wide scope issues. Ironically, in certain circumstances the freedom created by spreadsheets can also produce confining situations for many organizations. Fortunately, there may also be a way out of such a bind. The path to freedom may be through a hierarchy of logical networks.

The spreadsheet counterpart of arc is not so obvious. Arc counterparts are imbedded in spreadsheet cells, hidden from view, because the options in a spreadsheet are usually set to display the results of a cell rather than information about its components creating the results.

For example, the result in cell b46 in the spreadsheet image below is easily seen as 1.61, but the factors and terms in expression that created that value in b46 is not visible unless the cell highlighter is on b46 and one looks for the cell's expression in a window at the top of the spreadsheet.

The actual expression in b46 is b45 - b44. Obviously, it means that b46 is a function of b45 and b44. If we were to imagine this "function of" or "is influenced by" statement about b46 in network form, we will envision arcs from node b45 to node b46 and from node b44 to node b46.

One other matter we would want to express about those arcs in the corresponding network are that they are both AND arcs, since both b45 and b44 are necessary to calculate b46. Arcs indicating such relationships between cells are invisible in spreadsheets, but they are there logically, implied and imbedded in expressions.

In terms of expressing model logic and controlling model execution, a spreadsheet is constrained in several ways. For example, by their very nature, all [implied] arcs in spreadsheets are, in essence, AND arcs. This is the case even when a cell contains if-then-else or similar expressions. To take care of OR arc situations, one has to define more cells and introduce logic that will make a spreadsheet even less transparent and less tractable. Further, there can be no calculations/logic on arcs themselves, because there really are no explicit arcs in spreadsheets to express arc logic.

The model in the image of the spreadsheet above is the same as the version of our example model in Chapter 20, except for the Monte Carlo part, time delays, etc. that we discussed in that chapter.

Suppose we had built that model in a spreadsheet as above instead. We have used same inputs here as the first production batch in the Monte Carlo run we discussed in Chapter 20. You can, of course, verify that the results here are the same.

You are already familiar with {Table.D}LaborCosts used in the NMOD model (see top left-hand corner of Figure 23). If we were moving the model from this spreadsheet to NMOD, the setup we would prefer is exactly the way it was in previous chapters. The table would stay in the spreadsheet while we move model logic to NMOD.

We would most likely want to create a separate network for determining total widgets requirements in a batch by tallying up the requirements for Products A and B (PiecesRequired network). We would have in mind the possibility of going down to deeper levels from that network some time in the future to include calculation details for A and B by turning those nodes into Network types.

To get a model up quickly for initial testing, we would probably start with the top level (ProductionCost) network and not bother with the PiecesRequired network at first.

Detailed discussion of spreadsheet to NMOD conversion process can be found in Chapter 6 (Converting spreadsheet model to NMOD model) of Hands-on Tutorial section. The objective of this chapter is just to give you a rough idea of how a spreadsheet can be expressed as a model containing a hierarchy of logical networks.

The best place to start the conversion process is with cells that hold final results of the model. In this case, the best place to start is cell b46.

Note: Alternatively, one can start with those parts of a model that are most familiar/understandable and easiest to handle. The important thing is to start at a point that will easily hook you into the process, and build from there with frequent testing of the model as the transformation process progresses. No matter how much thought you put into it, odds are that you will decide to remodel the problem midstream during the conversion. This is not usually a problem, because one of the primary purposes of NMOD is to facilitate restructuring things as a model evolves and grows.

As mentioned in Chapter 2 (NMOD atom and ORMSware netoworks), some packages which facilitate modeling through network diagrams restrict the modeling atom to be a node (i.e. the equivalent of a spreadsheet cell) representing a scalar or array variable, with the variable being defined in terms of an expression. An expression can, of course, be a constant, scalar or array variable, a function, or a combination thereof. Each variable used in the expression must be defined in another cell. A cell cannot contain definition of more than one variable.

NMOD atom is flexible. It can be anything the analyst wishes it to be, and it can be different in different places in the same model. A NMOD atom can contain just one constant or variable, or a collection of constants, variables and functions. Because of this flexibility, models can be expressed compactly using NMOD diagrams.

You can verify from Figure 23 that there are 15 cells involved in producing the result in b46 (not counting the cells that would form the PiecesRequired network and not counting the LaborCost table). Click Figure 23 to open a separate window showing a one-to-one, cell-to-node conversion of our spreadsheet model into a logical network. The arcs show the dependencies and influences of each cell (node). As you can see, though the model is now a visual diagram, it does little to create better understanding of what the model is about.

Note: We have not bothered to enter content logic in any of the objects in this network, since there is a systematic and simpler approach for converting spreadsheet models to NMOD models in Chapter 6 (Converting spreadsheet model to NMOD model) of the Hands-on Tutorial. Node colors in Figure 23 window do not correspond to the colors used in ORMSware interface to indicate various node Types. The purpose of color in this diagram is explained further down.

You may recall from Chapter 20 that there were only 6 nodes in the ProductionCost network of PrimerSubNet model, but that they adequately communicated what the model was about.

No visual model can be so transparent that every detail of its contents, and inter-relationships of those contents, are immediately apparent. Some of the information will be hidden from any given view due to practical reasons. The common approach for remedying this problem as much as possible is to reorganize cluttered information into clear hierarchies.

The method some packages provide for organizing information that way is to make it easy to group nodes (whether or not they have any degree of affinity with each other) into subnets. In other words, the focus is on using (say) Windows technology to facilitate grouping nodes by netting them or dragging and dropping them into a collection object. The issue of clear presentation of the relationships just gets lost in the process.

In Figure 23 window we have color-coded nodes based on affinity. Notice, however, that if we simply group them according to color to create subnets, we will have fewer nodes in the higher level network, but there will be more than one entry into and more than one exit out of some of those subnets. Not that there is anything necessarily wrong with a logical network having multiple entries and/or exits. But, we think single-entry-single-exit rule makes it easier to formulate, debug, test, implement and maintain models, just as it is in the case of traditional programs.

You may recall from Chapter 20 that we defined the counterparts of the last three variables in this spreadsheet (b44, b45 and b46) in ProductionCost.[5] (if you need to review, click here, page down to {Object.5}BatchProduction, and check the first three lines of its SignalTarget property and then close the window). To make the conversion process easy and systematic, it is a good idea to manage the process in small chunks, consolidating nodes whenever it makes sense to do so, and testing frequently the results of integrating each chunk into the NMOD model.

Consolidation of nodes in NMOD does not automatically mean creating subnets. Nodes can be consolidated using model logic in terms of Global, Customer and Surrogate properties and user-defined variables and structures.

To illustrate this, let us look at the three nodes at left in Figure 24. They represent cells b44, b45 and b46. Click on Figure 24 and switch between Figure 23 (PrimerSStoNMODvsd) and Figure 24 (PrimerSStoNMOD1vsd) windows (or cascade them so that right sides of both are visible simultaneously) to see how we have consolidated (for demonstration purposes) those three nodes into one node (i.e. [1]) and reconnected all relevant arcs to [1]. (For the record, we have also consolidated [33]B27 and [11]B24,B25 into [11]B24,B25,B27.)

The next obvious step if we were to continue this kind of conversion process would be to eliminate a[6] or a[12], because they are redundant. We can be confident it will be safe to eliminate either arc, because [implied] arcs in spreadsheets do not hold any logic other than provide precedence information.

Figure 24

At any rate, notice that we did not turn the three nodes and related arcs into subnets. We simply moved the computations of b37 and b38 into [1]. Since the data we need to calculate b37 and b38 are in customer properties, it does not matter at which node the calculations are performed as long as they are performed in proper sequence. The key thing to remember is to keep calculations in each network object as simple and fundamental as possible and to use network anatomy/structure to model and communicate variable relationships and to control execution.

We wanted to show you in this chapter that there is natural correspondence between cells in a spreadsheet and nodes in a logical network; that while this is the case, mindlessly translating cells to nodes and grouping the nodes to avoid clutter does not necessarily contribute to creating clarity and understanding; that though it is possible to continue along the lines of cell-to-node conversion and even grouping nodes based on affinity of some kind as we did above, there is better way to transform a spreadsheet model into a NMOD model.

Close all other open windows. If you would actually like to go through the exercise of converting this spreadsheet into a NMOD model, please go to Chapter 6 (Converting spreadsheet model to NMOD model) of the Hands-on Tutorial.

Expressing spreadsheets in terms of logical networks