NMOD Tutorial Chapter 4 Building ORMSware networks

In this chapter you will learn the following (though not necessarily in the order listed):

As mentioned in the Primer, we will be presenting here a systematic and simpler approach for reverse-engineering spreadsheet models and transforming them into NMOD models. We will not be able account for every possibility you may encounter in converting spreadsheets, but the exercise here should give you a good understanding of what needs to be done and how to do it, and therefore, figure out ways to solve any specific or unique situation you may encounter.

Using Visio, it is easy to create nodes, give them the same name as the active cells in a spreadsheet model, then connect them according to the formulas imbedded in the cells, and export the file frequently to make sure that the connections and calculations are true to the original spreadsheet model. However, as you have seen in the Primer, networks created that way can become unwieldy in a hurry and contribute little to communicating what the model is about.

How to deflate/collapse bloated/large spreadsheets first and then estimate the time, effort and cost for converting them to ORMSware NMOD are explained towards the bottom of this page. But read that only after reading this section.

There is no way around actually making the effort to understand a spreadsheet model if one wants to convert it to NMOD, especially if the resulting NMOD model is expected to be transparent, communicating effectively what the model is about. Though the process cannot be totally mechanical, however, NMOD's visual modeling approach can still be used to make spreadsheet models more transparent, scalable and powerful.

The first thing one has to do in converting a spreadsheet model is to label as much of the cells as possible with descriptive names. Notice that we have already done that in Column F in Deviation.XLS . While most spreadsheets are not so neatly confined to a few columns as in our example case to make it possible to label cells the way we have, it is crucial that one finds a way to identify cells this way before attempting conversion. After studying the rest of this chapter, you should be able to find creative ways to accomplish that task in the conversion process.

Note: In most cases key calculations in a spreadsheet are concentrated in the first few columns. When a spreadsheet has content in many columns to the right in various blocks, they typically hold tables/arrays used in the model, or contain copies of formulas in a column towards the left of the block. Sometimes columns to the right of a formula are there to implement a recursion. Sometimes they are there to change the dimension of calculations from 1 to some value n. Regardless, you will find that you can concentrate on one particular column (or just a few columns) to understand the key logic, because the expressions in other columns of any given row in a particular block of logic of that kind are, in all likelihood, redundant. The required redundancy, whether in terms of recursion or dimension, can be incorporated effortlessly in NMOD, without duplicating everything in every active cell of a spreadsheet.

Logic in "production-mode" spreadsheets often evolve into spaghetti structures over time and, in many cases, can become harder and riskier to modify than traditional programs. Often the consequence is that an operation becomes unable to use the model effectively. Worse, some organizations are held hostage by certain key legacy spreadsheets, unable to free themselves from these electronic gridlocks to acquire the visibility and flexibility needed to move their operations to better profitability.

If one wants to punch through a confining spreadsheet model, it is necessary to spend a little time to do the work to convert the model, from the inside, so to speak. Fortunately, this is not really a tall order. You will see below that the spreadsheet-to-NMOD transformation process is considerably easier than writing a program of a spreadsheet model from scratch, or even re-engineering the spreadsheet "properly" from scratch.

In demonstrating the transformation process, we have to start here pretending that we have not already seen the NMOD model of what is in the spreadsheet, since that will be the case in a practical situation. When you finish converting this model, it is likely that it will not look like the model with which we ended up in Chapter 20 (Reinforcing concepts through PrimerSubNet's feedback file) of the Primer. Of course, that it may not (in fact, it will not) look the same will not be a surprise to anyone with sufficient modeling experience. As a matter of fact, it is highly likely that you will like, as we do, this converted model much more than the original model in Primer Chapter 20.

Note: We created C:\ORMSware\Tutorial\Chapter6\Deviation.XLS to enable you to work with it while we go through this exercise. If you are simply reading this in a browser, you may want to display it in a separate window.

Discussing what the model is about with appropriate stakeholders, tagging items in column F in the spreadsheet as we proceed, we would start developing a good feel for how to structure its NMOD counterpart. We would know from systematic queries to the stakeholders that the final result produced by the spreadsheet model is the difference between the actual cost incurred in producing a batch of widgets and the lowest total cost that could be achieved if there were sufficient time to produce at optimal production rate to meet the just-in-time delivery requirement for that batch.

To stay organized we need to have a method for tagging cells/variables that have been formulated properly in NMOD, items that have been given temporary values for intermediate testing, and items that are data inputs. We will use X, ~, and # for these purposes, respectively. When all variables are labeled either with X or #, we will know that transformation of the model from spreadsheet to NMOD is complete. We will use the same notation in the model's NET file, and Description properties of network objects in Visio, to track our progress.

Before we can do anything, of course, we have to bring up C:\ORMSware\ProgramFiles\ORMSware.VSD and save it as (say) C:\ORMSware\Tutorial\SpreadToNmod.VSD. Double-click tab at the bottom of page and change page name to Deviation.

Create n[1]: It is simple enough to calculate total costs at actual and ideal production rate and then find the difference between them. So, we can cluster all three of those simple calculations in one node. So, let us create the first node in our conversion process and populate its properties as below:

Name: DeviationFromOpt
Description: X Difference between actual and ideal costs
Trans / control: {}...

Note: It is better to enter all logic in NET file during the transformation process, since we will be able to make changes quickly in the NET file without having to rewrite reports from Visio.

Let's go ahead and generate a report of what we have so far (yes, just one node), and Just NETrans it to create a skeleton NET file for the model (NETrans will create C:\ORMSware\Tutorial\SpreadToNmod.NET). If you do not know how to generate NMOD report from Visio and do Just NETrans from the browser, please go through Chapter 2 (Quick hands-on tour of NMOD modeling process ) and then come back here to continue.

Now let us put in the following three expressions and the echo statements in the Trans property of n[1]. We will use Customer properties in expressions until and unless we discover that we need to use Surrogate properties.

#D.TotalAtOptimal = #D.PiecesRequired * &D.BestProductionCost !X
#D.TotalAtActual = #D.PiecesRequired * #D.CostPerPiece !X
#D.DeviationFromOpt = #D.TotalAtActual - #D.TotalAtOptimal !X Actual will always be >= optimal
!-----
{?|#D.PiecesRequired}
{?|&D.BestProductionCost}
{?|#D.TotalAtOptimal}
{?|#D.CostPerPiece}
{?|#D.TotalAtActual}
{?|#D.DeviationFromOpt}

As in previous chapters, indent the code as necessary whenever you paste code into NET and ADD files this way.

Note: Since the three items in the left-hand-side (LHS) of the expressions currently in focus have been properly formulated, we should place Xs next to them in the spreadsheet (as shown in the image in Deviation.XLS window) in addition to the X's at the end of the expressions for those variables in NET file. It is helpful to periodically do reference checks for !#, !~ and !X and for = signs with blanks on both sides to make sure that nothing is falling through the cracks. For this reason, it is helpful to leave spaces on either side of = signs when putting together assignment statements in NET files.

From the expressions above we know that we need #D.PiecesRequired, #D.CostPerPiece and #D.BestProductionCost to calculate the three customer properties in [1]. To make sure that we take on just a few items at a time to convert and test, we should set them equal to values we know from the spreadsheet before calculation thread reaches expressions in [1] for calculating #D.TotalAtOptimal and #D.TotalAtActual.

Note: If you do not yet know how to use Excel spreadsheets in NMOD, you may want to review Chapter 16 (Using Microsoft Excel for inputs) in the Primer first and come back here to continue. When you are reading Chapter 16, keep in mind that what is described there is not the spreadsheet-to-NMOD transformation process. That chapter is about how to setup tables in Excel as input devices for your models. Restrictions specified there do not apply to the conversion process described here.

#D.PiecesRequired, #D.CostPerPiece and #D.BestProductionCost are what we call transitional data sources at this point in the transformation. Their values may be derived from other constants or variables, but for this iteration in the transformation process we treat them just as data. However, since we know with certainty without having to dig beyond current layer of information that #D.BestProductionCost is a constant within the context of this model, we can label it with # (meaning data) in Deviation.XLS, SpreadToNmod.NET and the intermediate spreadsheet we will discuss in a minute. The other two should be labeled with ~ to indicate that they are transitional sources during current iteration.

Rather than manually entering or copying and pasting the RHS (right-hand side) of expressions for transitional data sources in the NMOD model during intermediate transformation stages, we prefer a linking method that will tie the spreadsheet being transformed to the NMOD model. To do this we have to set up an intermediate Excel spreadsheet, because at the moment NMOD cannot handle workbooks with multiple spreadsheets, and because this approach should help in converting non-Excel spreadsheet models to NMOD models, too.

1. Set up a new (intermediate) spreadsheet (say, Transform.HTM) as shown in the image at left. Notice in the formula box for cell c3 (which is one of the 3 transitional data sources in the current NMOD model) that the source of the data in that cell is cell b15 of Deviation.XLS which contains the spreadsheet model we are converting.

2. Go to the relevant object in NET (in this case n[1])and link data sources in the model's current stage to the three variables in Transform.HTM (we will explain how shortly).

3. Save the intermediate spreadsheet to make sure that NMOD gets its latest contents for the next run.

4. Choose NETrans + Compile.Link.Run and compare NMOD results with those in Deviation.XLS.

Note: If we were converting another type of spreadsheet model (say, Lotus), we would have to copy the cell in the original spreadsheet and then paste-link it in the intermediate Excel sheet.

Since with this approach data sources in the evolving NMOD model will always be linked to the original spreadsheet through the intermediate spreadsheet (in this case data sources in SpreadToNmod will always be linked to Deviation.XLS through Transform.HTM), we will always have both the original spreadsheet model as well as a hybrid model simultaneously, until the transformation is complete. With this integrated transformation/migration approach, we will be able to test and verify the NMOD model directly with entries from the original spreadsheet, without duplication of effort, while the original continues without disruption of routine use.

Note: Be sure to save Transform.HTM (i.e. the intermediate spreadsheet) each time you change input values in the original spreadsheet. If the results from NMOD model do not match those in the original spreadsheet, save the intermediate spreadsheet, make a run and check results again before trying to track down something else.

Let us now perform the steps above for the current situation. Instead of creating an intermediate spreadsheet from scratch for this exercise, you may want copy to C:\ORMSware\Tutorial the Transform.HTM we have placed in c:\ORMSware\Tutorial\Chapter6 during installation. It comes with some basic data already in it.

Note: You will have to load Transform.HTM from Excel's main menu, or use Open with option by right-clicking on the file name in Windows Explorer and choosing Excel. When Excel loads the file, it will ask you if you want to update the links. Say Yes, navigate to c:\ORMSware\Tutorial\Chapter6 and choose Deviation.XLS.

We will explain below the process we went through while hooking BestProductionCost (already identified as a constant in the Deviation model) from Deviation.XLS to Transform.HTM. You can then follow the same process to enter the two transitional data sources in current iteration into Transform.HTM.

We placed a # in Deviation.XLS!e18, copied f18 to clipboard, switched back to Transform.HTM and pasted it in Transform.HTM!b2.

We then got BestProductionCost's value into Transform.HTM!c2 by entering a + (you can also enter = instead of +) in c2, switching to Deviation.XLS, clicking b18, and hitting Enter. To avoid precision problems, we changed the format of c2 in Transform.HTM to General.

Note: NMOD's Module for reading Excel spreadsheets extracts displayed values from a spreadsheet's HTML code (what you see is all you get). If the displayed value is 2.31 instead of 2.31273508, the value NMOD will get is 2.31000000 and this difference will propagate through the rest of the model.

Insert two more rows between the Table tags in Transform.HTM as in image above and follow the same process to hook values of PiecesRequired and CostPerPiece into Transform.HTM. Remember that they should be labeled with ~, because they are dependent on other variables, constants or parameters.

As mentioned earlier the values of #D.PiecesRequired, #D.CostPerPiece and #D.BestProductionCost must be defined in SpreadToNmod before calculation thread gets to expressions in [1] for calculating #D.TotalAtOptimal and #D.TotalAtActual. So, this is a good time for us to set up a Start node where we can initialize the three variables above, as well as other variables labeled with # or ~ that will come up as we proceed. We will move variables out of this node to appropriate nodes in a systematic fashion as we proceed.

Name: Initializations
Type: Start node
Description: # Initializations, etc.
Trans / control : {}...

Note: VarLookUp is an NMOD procedure for looking up string or REAL(dbl) values in a character array (table). In the above CALL we are looking for the character string PiecesRequired in column 1 of {Table.25}Tests for reference, and if there is a match, reading a REAL(dbl) value which is 1 column over in the same row of the Table.

We now need to show that expressions in n[1] are dependent on expressions in n[2], thereby achieving calculation flow from [2] to [1].

Create a[3]: Create an arc and use it to connect [2] to [1]. Optional: Delete Description property.

Choose NETrans + Compile.Link.Run from the Advanced menu. Check results at bottom of NMOD.TXT and compare it to cell b32 in Deviation.XLS to verify accuracy of deviation calculated by SpreadToNmod model.

Note: We hope that by now you have become proficient enough with the process to notice above that we did not ask you to do Tools-Report in Visio before NETransing. As you must have gathered by now, since ORMSware interface in Visio is not hardwired with the modeling process outside of it, we have to always do Tools-Report if we want any changes made in Visio since the last NETrans step to be picked up by a new NETrans step.

To see if the linkage to Deviation.XLS works, let us change Widgets for Product A to 25, Wear coefficient to 0.15, and optimal production cost to 2.00 in Deviation.XLS. No need to save it unless you prefer to do so.

Switch to Transform.HTM. Notice that the changes in Deviation.XLS are reflected in Transform.HTM, including dependent variables PiecesRequired and CostPerPiece (recall that they are not source data/independent variables in Deviation.XLS).

Since we have only changed values in SpreadToNmod's data sources and not any of the expressions in the model (either in Visio or in NET file), there is no need to NETrans, recompile and re-link the model. Just choose Run from the Advanced menu. Compare results. As you can see, using this kind of linkage, we can use the original spreadsheet to verify SpreadToNmod every step of the way during the conversion process.

Change the 3 values you had changed in Deviation.XLS back to their original values (3 Undos).

Since #D.BestProductionCost is labeled with #, we know that there is nothing more to explore behind it. It is an input from another model and is considered a constant in this model. In network terms it means that the node representing this variable will have no predecessor nodes, and so that is the end of the road in chasing down #D.BestProductionCost.

Since this is input data, we do have the option putting it in a common initialization node where other such variables are initialized as well, rather than putting it in a node of its own. Additionally, we have the option of putting a constant value on its RHS or leaving it the way it is now to retrieve the value from a spreadsheet using VarLookUp.

As you can see, if we leave as it is, doing the same with other variables when they are labeled with #, we will have all of the constants and parameters in a model concentrated in the intermediate spreadsheet when the transformation is complete. The intermediate spreadsheet can then serve as an input interface into the NMOD model or we can move the interface block into the original spreadsheet, while also retaining as much of the original spreadsheet as we desire.

During each NETrans + Compile.Link.Run step in our transformation process, some of the variables will be data sources feeding expressions already entered into the NMOD model. Of these, one or more data sources during a given step become(s) variable(s) in the next step, dependent on the sources of data in the new step, until all remaining cells in the spreadsheet contain just data (i.e. all expressions have been accounted for).

In the transformation step we performed earlier, #D.PiecesRequired and #D.CostPerPiece were data sources feeding expressions in n[1] for #D.TotalAtOptimal and #D.TotalAtActual, but both were labeled with ~, which means they are really dependent variables treated temporarily as data sources. In the next step, we will breakdown further at least one of those two variables influencing expressions in [1].

Note: As discussed earlier, we label transitional inputs with the symbol ~. It is a good idea to define the value of every such data source in an initialization node until we find a better, more logical and communicative network object to move it to as the NMOD model evolves.

We can now incorporate further breakdown of either #D.PiecesRequired or #D.CostPerPiece. Let us go for #D.PiecesRequired.

Create n[4]: Create a node to represent expressions for calculating #D.PiecesRequired and populate it as below:

Name: PiecesRequired
Description: ~ Pieces required in a batch, and production rate
Trans / control : {}...

Just NETrans to create code block in NET file. Switch to NET file in browser. Move the expression for #D.PiecesRequired from [2] to [4]'s Trans property. We will change the calculation process for #D.PiecesRequired momentarily, but the purpose of actually going through the move is to make sure that we do not forget to delete it in [2]. We do not want to end up with two places where #D.PiecesRequired is defined when it should be defined only in one place in this model (at least based on what we know at this stage).

Looking at the spreadsheet formula for PiecesRequired, we know that it is a function (sum) of pieces required for making Products A and B. So, let us create two nodes that converge on n[4] and two AND arcs to connect them to [4].

Create n[5], n[6], a[7], a[8]: Connect [5] to [4] with [7] and [6] to [4] with [8] (just so that your diagrams will be consistent with what we have) and populate their properties as below:

n[5]:
Name: ProdAwidgets
Description: # Pieces required for Product A
Trans / control : {}...

n[6]:
Name: ProdBwidgets
Description: # Pieces required for Product B
Trans / control : {}...

Since #D.PiecesRequired is the sum of widgets required for products A and B, we can use the MergeSum function in Merging property of [4]. Let us put the expression shown below in the Merging property text box for [4] in Visio, but first delete expression for #D.PiecesRequired in [4]'s Trans property in NET file.

Switch to Visio, double-click [4], tab to Merging and paste over. Tab back to Trans/Control and remove the ellipses (because we no longer have anything in [4]'s Trans property in NET file). Then tab back to Description property and replace ~ with X. Hit Enter or click OK.

Notes

1. If you do not know what #, @, D and the period before the variables mean, you may want to review Chapter 7 (ORMSware entities), Chapter 10 (System, Global, and entity properties), and Chapter 11 (Notations of properties) in the Primer and come back here to continue.

2. To learn more about the workings of MergeSum, review Chapter 20 (Reinforcing concepts through PrimerSubNet's feedback file) in the Primer.

Since Merging property was entered in Visio, we need to write a report from Visio before NETransing the model. Write the report from Visio and Just NETrans to create placeholders for the new nodes and arcs we created above.

We now need to define @D.PiecesRequired in n[5] and n[6]. Insert the first line of code below in [5]'s Trans property and the second one in [6]'s Trans property.

Switch now to Transform.HTM and insert a row below PiecesRequired. We are going to take out PiecesRequired and put in AWidgets and BWidgets, label corresponding cells in Deviation.XLS appropriately and hook appropriate cells in Devaition.XLS into Transform.HTM.

Switch to Deviation.XLS, label f13 and f14 in e13 and e14 with #; label PiecesRequired in e15 with X; copy f13:f14 to clipboard, switch back to Transform.HTM and paste in b3. Then hook c3 and c4 to Deviation.XLS!b13 and Deviation.XLS!b14, respectively.

We also have to make sure that [5] and [6] fire when the model runs. At the moment there is nothing to evoke them during model execution. Further, even if we evoke them and subsequent flows converge to evoke [4], there is no flow from [4] to [1] to calculate the final results we are after.

Copy the following line to clipboard, switch to Visio, double-click n[2], tab to SignalTarget property, paste over and click OK.

Note: Notice the ^ to the right of [Start Node] in [2]'s node display. This is to remind the analyst and other stakeholders that this node contains temporal connection to other network object(s). If you are not familiar with temporal connection, you may want to review Chapter 14 (SignalTargets/temporal arcs property of network objects) in the Primer and come back here to continue.

Now that we have incorporated a way to evoke [5] and [6], we can also solve the problem of creating flow from [4] to [1] by connecting [4] to [1] with a[3].

Create n[9], n[10], n[11], a[12], a[13] and a[14]: Let us break down #D.CostPerPiece now. We can see in Deviation.XLS that #D.CostPerPiece is a function of TotalCostPerHour and PiecesPerHour (i.e. planned production rate). So, let us introduce three nodes for CostPerPiece, TotalCostPerHour and PiecesPerHour and populate some of their properties as follows:

n[9]:
Name: CostPerPiece
Description: X Cost per piece at actual, feasible production rate
Trans / control : {}...

n[10]:
Name: TotalCostPerHour
Description: ~ Sum of cost components
Trans / control : {}...

n[11]:
Name: PiecesPerHour
Description: ~ Pieces per hour necessary to meet JIT requirement
Trans / control : {}...

Create 3 arcs. Populate their properties as below and connect [10] to [9] with [12], [11] to [9] with [13], and [9] to [1] with [14].

We need to evoke [10] and [11], so paste over existing SignalTarget property of [2] with the following:

Cut out code for CostPerPiece from n[2]Initializations. Paste it in Trans property of n[10]. We will modify this in a minute (TotalCostPerHour) after updating Transform.HTM.

Switch to Transform.HTM. Insert a row above CostPerPiece. Switch to Deviation.XLS.

Change label of CostPerPiece in e28 to X, and labels of TotalCostPerHour and PiecesPerHour to ~ in e27 and e23.

Copy f23 and paste it in cell b5 of Transform.HTM and copy f27 and paste it in cell b6 of Transform.HTM.

Hook c5 to Deviation.XLS!b23 and c6 to Deviation.XLS!b27. Change formats of c5:c6 in Transform.HTM to General.

Go to Trans property of Deviation.[10]TotalCostPerHour in NET. We will modify the expression now as mentioned earlier. Change CostPerPiece in quotes and in #D.CostPerPiece to TotalCostPerHour, exactly as it is (case-sensitive) in Transform.HTM.

Copy that line of code to Trans property of Deviation.[11]PiecesPerHour and change TotalCostPerHour occurrences in the line to PiecesPerHour.

In breaking down TotalCostPerHour and PiecesPerHour, it may be a good idea to put them in subnets, because (1) the current network is beginning to get crowded (to fit on 8.5x11 page), and (2) they are both likely candidates for further expansion later on.

In case you do not remember how to create referenced networks (subnets) for Network nodes, please review Chapter 4 (Building ORMSware networks - beyond basics) of this tutorial.

Turn n[10]TotalCostPerHour into a Network node and follow the process for creating its subnet. Since TotalCostPerHour is being broken down, we should change its label to X in Deviation.XLS and in the network diagram.

In Visio change ~ in Deviation.[10]'s Type property to Network and prefix its Description property with X. Change corresponding label in e27 of Deviation.XLS.

We can write over TotalCostPerHour in Transform.HTM with the next variable we will use as a transitional data source, but so as not to leave a loose end, let us change b6 in Transform.HTM to ~~~~~~~~ for now. Alternatively, we could simply delete this row now and insert it again when we need to introduce another data source. We will use the ~s here.

Create 4 nodes on TotalCostPerHour network page and populate their properties as below:

n[1]:
Name: TotalCostPerHour
Type: Return
Description: X Total cost per hour
Merging: {}...

n[2]:
Name: MachineCostPerHour
Description: ~ Machine cost per hour
Trans / control : {}...

n[3]:
Name: LaborCostPerHour
Description: X Labor cost per hour
Trans / control : {}...

n[4]:
Name: Entry
Type: Start Node
Description:
SignalTargets: {{Signal.2} ; {Signal.3}}

We put X label in n[1], because we know that we are going to express TotalCostPerHour as the sum of MachineCostPerHour and LaborCostPerHour. We put X label in n[3], because we know that we can use LaborCosts table and #D.PiecesPerHour now to fetch LaborCostPerHour values. And, we put ~ label in n[2]MachineCostPerHour, because we plan to pull that value in from Deviation.XLS for this new round in the transformation process.

Create 2 AND arcs with empty Description property content. Connect [2] to [1] with [5] and [3] to [1] with [6].

Go to Merging property of TotalCostPerHour.[1] in NET and enter the following code:

In Trans property of [3], put in the table lookup formula for LaborCostPerHour as follows:

Before we can use the LaborCosts table above, we have to instruct NMOD to load it. Let us take care of that now. Insert the following code above the CLOSE(21) statement in Deviation.[2]Initializations:

The expression further up for @D.CostPerHour indicates that LaborCostPerHour is a function of #D.PiecesPerHour. Will the value of #D.PiecesPerHour be available when calculation thread reaches LaborCostPerHour node? Such questions arise periodically when we build a model. When they do, NMOD's reference check is a good way to answer those questions.

Do a reference check on #D.PiecesPerHour. We can see from the reference list that #D.PiecesPerHour is defined in Deviation.[11]PiecesPerHour, but we have no assurance that its calculation will precede triggering of TotalCostPerHour network.

To ensure that we show the appropriate dependency, let us connect a new arc from Deviation.[11] to [10] and then go back to TotalCostPerHour network.

The next item to take care of in this page is either the Start node or the MachineCostPerHour node. Start node is easy to do, so we will take care of that next and then move on to MachineCostPerHour.

Moving on to TotalCostPerHour.[2]MachineCostPerHour in NET, enter the following code in [2]'s Trans property:

We should now enter a corresponding label in Deviation.XLS and update Transform.HTM. Enter ~ in e25 of Deviation.XLS. Copy f25 to clipboard, switch to Transform.HTM and paste over the ~~~~~~ we entered earlier in b6. Make sure column b is wide enough to display the entire string MachineCostPerHour.

Hook Deviation.XLS!b25 into Transform.HTM!c6. Change the cell's format to General to avoid precision problems.

While the process we have presented here is systematic, there are many things we plan to do over time (future) to make it airtight. In the mean time while using this process, it is likely that you will forget something during any given conversion step. However, the errors will surface and you will be able to correct them with the tools currently included with NMOD, as you will see now.

The error message is saying that NMOD could not find {Table.D}LaborCosts in file 21. We could ask NMOD to do Reference check on 21 in NET file, but we know that the only place we have opened a file as Unit 21, and loaded data from it, is Deviation.[2]Initializations.

Looking at the code we can see that we are asking NMOD to load LaborCosts table from Transform.HTM, but we never set up that table. Well, there is a LaborCosts table in Transform.HTM, but you will notice an asterisk at the end of that table's name tag. Let is remove those asterisks from that table's tags and pretend that we just created it, hooking in table contents from Deviation.XLS.

Note: If you hit f4 three times while copying the first cell of the table data from Deviation.XLS (to change the addressing to relative reference from the default of absolute reference), you will be able to simply copy and paste the formula to the rest of {Table.D}LaborCosts data cells in Transform.HTM.

Save Transform.HTM after you have created the table and NETrans + Compile.Link.Run.

This time the error is saying that it could not find TotalCostPerHour in {Table.25}Tests. But, we should not be looking for TotalCostPerHour in this table, since it has already been transformed and labeled with X. To track down why this is happening, go to the end of execution feedback file c:\ORMSware\ProgramFiles\NMOD.TXT. Select TotalCostPerHour in the error message and do a reference check. For a bigger view of the references, double-click the last line in the output window (Search results also stored in...).

As you can see we forgot to delete the VarLookUp line of code in Deviation.[10]'s Trans property in NET, though we had redefined #D.TotalCostPerHour in the TotalCostPerHour network.

Delete the line, remove the ellipses in [10]'s Trans property box in Visio, re-export.

Execution ends normally, but NMOD cautions us that there was an entity remaining in one of the convergence queues. This is not to be expected, since we are not preempting model run in anyway. So, we must find out why this is happening. A good place to start looking for clues is at the end of the NMOD.TXT, the execution feedback file.

The cleanup and wrap-up section shows us that the convergence queue with the one remaining entity is Deviation.[9]CostPerPiece. It then shows that NMOD dequeued the entity, identified it and figured out through which arc it reached [9]. That clue tells us that there was no matching surrogate arriving at [9] via a[13] (from [11]).

Recall that we introduced signals in n[2] earlier to trigger [10] and [11]. Then, later when we discovered there was no arc showing dependence of expressions in [10] on expressions in [11], we connected [11] to [10] with [15] to show the dependence. That causes [10] to get two surrogates of the same customer (one comes as a signal from [2] and the other comes normally along [15]).

To fix the problem, let us remove {Signal.10} from [2]'s SignalTarget property. Re-export and NETrans + Compile.Link.Run. Press Ctrl-End to jump to end Nmod.TXT. Compare results.

Change input values in Deviation.XLS, save Transform.HTM and Run. Compare results and verify SpreadToNmod as it stands now.

You now know enough to complete the conversion if you desire. You can see what we ended up with in c:\ORMSware\Tutorial\Final. You will notice that it is a little different from the way the model looks now.

This is the result of natural progression through the transformation process. The advantage of NMOD, of course, is that it is easy to turn and move the model in a new direction when it becomes apparent that it is better to do so. The time it took us to make the changes and complete the model was about an hour.

The transformation work we have done so far may seem long and laborious. But, the notion of Learning Curve is quite applicable here. You will find out as you gain experience with this process and working with NMOD that everything moves along quite fast.

If you have gone through the Primer, you may recall that the final version of the problem involved looking at the dynamics of this production operation in terms of arrivals and departures of several production batches with various JIT requirements. Some of the dynamics in that model would be very hard to implement in a spreadsheet, but if one were only interested in simply replicating the calculations in the current spreadsheet for several production batches, one is likely to copy over the logic in the column for one customer to as many columns as necessary to accommodate more customers, with appropriate changes to customer-related variables.

As you can surmise from the discussion above, spreadsheets containing many columns can be reduced in size (carefully) for conversion purposes. We discussed this notion at the beginning of this chapter. It will be helpful to keep the idea of problem size reduction in mind while attempting to do cost-benefit analyses of your conversion projects.

We estimate that 90% of the content of any given large spreadsheet is made up of redundant formulas. Therefore, in all likelihood a large spreadsheet to be converted could be collapsed to a spreadsheet of core formulas. This spreadsheet will in all likelihood be about one-tenth the size of the original spreadsheet.

You can estimate the conversion rate to be roughly 500 bytes per hour (after considering the deflation factor). A spreadsheet with an original size of 1 MB may take about 200 hours to convert to ORMSware NMOD. With the highly risk-mitigated, integrated transformation NMOD offers, the resulting quality assurance, and the flexibility and freedom to change NMOD models quickly to support dynamic business needs, conversion of large and complex spreadsheets to NMOD models will be well worth the time, effort and cost for most businesses.

Spreadsheet macros complicate matters a little. NMOD may obviate the need for some macros, so their conversion may essentially be cost-free. However, time and cost of converting macros in a spreadsheet can only be estimated after careful reviews of what needs to be done. Most macros are likely to be undocumented. Therefore, it may be necessary for analysts to conduct intense interviews with users of the spreadsheet to understand ahead of time, to the extent possible, the depth and breadth of the problem.