Simplify changes to all areas of your model by creating a ProcessModel custom interface. This article walks you through the process and provides a template to make your work easy.
Why create a ProcessModel custom interface?
All of the reasons that follow are really about providing a simple way of making changes to many parts of a complex model. The changes can all be made in one place and put into a format the user will understand. Below are the reasons we have used a ProcessModel custom interface:
- Use a model as a sales support tool, even when the operator lacks training in ProcessModel.
- Do repeated analysis to the same model, where the changes are complex.
- Allow managers to experiment with a “Sand Box” model without any ProcessModel training.
- Isolate the guts of a model so the end user can’t destroy detailed logic.
- Make changes to multiple levels of a hierarchical model.
- Change complex formulas or distributions by making the entry form understandable.
How difficult is it to Build a ProcessModel Custom Interface?
You don’t have to know VBA and you don’t even have to be good at Excel to create an interface. A simple ProcessModel custom interface can be developed in about three minutes. That means you can take several factors needing adjustment, link them to ProcessModel and show the interface working in under three minutes! Does this sound like an exaggeration? It’s not. We timed it. We did all of the hard work by creating Excel template used to control the process. You just have to use our template (which is free to registered users). See how it’s done and get all the tools you need in the remainder of this article.
Why is a ProcessModel Custom interface Important?
Make a distribution easily changeable by anyone. For example, if a percentage changed for 5 known entries, you could develop a simple table that would only allow the percentages to change while showing the total of all percentages entered. The values could be turned into the D5 distribution automatically. ProcessModel would see the distribution but the user would only see the table. The example below shows the percentage samples requiring a known amount of time to test. The percentages change for each customer but the times are fixed.
ProcessModel D5 distribution to be imported.
The distribution shown above is not difficult to create, but could be a possible source of errors, frustration and delay if the changes were to be made many times. The table is very simple to understand and can easily be error proofed. For example in Excel, the percentages can be checked continuously and turn red if the sum is not 100. The export can even be stopped if the user ignores the the warning.
Implement Error checking. Mass imports will be pushed to ProcessModel irrespective of validity. If the entries are incorrectly formatted then you could introduce errors into many parts of your model in a fraction of a second. Error checking allows every entry to be verified before moving to the import stage. This also provides an opportunity to make sure entries are within reasonable guideline. Excel has all the tools necessary to error check entries. If you get stuck, give us a call we have loads of experience in error proofing entries.
Add menus and dependent entries. All features available in Excel can be used to create menus that only appear when certain selections are made. These Excel features can add “intelligence” to the ProcessModel custom interface.
Steps to Create a ProcessModel Custom Interface
There are a lot of steps and pictures included below. Many of the steps are done on any model. They have been included here so that you have a walk through from start to finish.
Create a validated and verified model.
Save this model to a “fresh” Directory. It is important to create a home for your model, export file and interface so as to not confuse the linking process with other files. The easiest way to do this is to create a new directory and place all the files you will need into this new directory.
Place the ProcessModel Custom Interface Template in the same directory as the model.
This file is supplied free of charge in the model object library and is ready to use without requiring any VBA changes. If you don’t have access to update the model objects, email email@example.com and ask for the ProcessModel Custom Interface Template.
Insert scenario parameters and replace the field values of all entries needing changes.
Scenario parameters are used because they are simply “text replacements” in the model. Scenario parameters can be used to change almost anything, from run length to activity time or from action logic to quantity of resources. You can set attributes and variables and much more. This use of scenario parameters means we can simplify the transfer process for you. If you are not familiar with adding a scenario parameter, the steps are included below:
Create a scenario parameter.
Replace a field value with the scenario parameter name.
Export the model to Excel and Copy the scenario names and values.
From the Tools menu select Export Data. Save time and avoid overwriting data by deselecting all areas of export. Scenarios will export by default.
Select OK and the information will export and open Excel. From Excel, select the scenarios tab.
Select all the cells containing scenario names and values and copy (Control + C).
Open the ProcessModel Customer Interface Template and paste the columns in the temp1 tab
First clear out the default information in the in the temp1 tab.
Select cell A1 and paste the contents copied from the scenarios tab of the exported Excel file. Change the fill color to “No Fill.”
Create the interface in the ScenarioInput tab
This is your chance to be creative and logically walk the user through the changes the model will require. Again, remove default interface entries before starting you own interface. It is important to be consistent with color. For example white cells are generally used for entry points, while shaded or colored areas are used for headings and explanations.
Link the fields on the ScenarioInput tab to the temp1 tab
During the import process, the entries on the input sheet are formatted input into the temp tab. The temp tab we replace the scenarios in your model. The seps below show you how to move entries (any possibly combine) to the temp tab.
Select the Tempt1 tab.
Select a value associated with the desired scenario parameter, that will be changed from the interface you created.
Delete the contents of the field and enter the “=” symbol.
Select the ScenarioInput tab, select the appropriate cell and press Enter. You should now see the value from the cell from the ScenarioInput tab in the Temp1 tab.
Repeat the process above for each cell that linked to the interface.
If cells in the Temp1 tab are made up of more than 1 cell on the interface, then use the CheatSheet tab as a template for proper formatting. An example of 2 or more cells on the interface being used to create a single entry in a Temp1 cell would be for the creation of a distribution. The cheat sheet has lots of examples.
It is not required that all scenario parameters are linked. Some may have other purposes in the model.
Select the automation needed and press the Update button
Depending your needs you may choose to have the ProcessModel Custom Interface perform different actions.
Adjust the speed of the automation to match your computer and model complexity
Under some instances the automation will run too fast and will not accomplish all of the steps required. If this happens, adjust the speed of the automation by adjusting the Time Multiplier in the interface. For example, hanging the multiplier to 2, will double the time of each automation step. This “speed change” may be needed based on the speed of your computer, available memory or size of the model.
Link the interface to the model
If model is going to be archived, moved to another computer or another directory is important to link the correct interface with the appropriate model. Do the following to link the interface with the model:
Place a new graphic on the layout Change the type of graphic to a Link. Any ProcessModel graphic can be used. Alternatively graphics can be imported if they are created as WMF or EMF graphics.
Attach the Excel interface to the graphic — right mouse click on the graphic and select Link Document.
Using the Interface
Open the model associated with the interface and then open the interface. Make the changes needed and press the Update button. The first time the interface is used it will request the name of the exported Excel file (your_model_name with the word data appended). The interface will transfer your changes to the model and in a few seconds you will have the results for a new configuration. It’s just that easy.
I sincerely hope you enjoy this free addition to the ProcessModel toolkit. The ProcessModel Custom Interface is a fast and easy method of controlling changes, error proofing and simplifying the interface for any ProcessModel simulation — it’s as easy as pie.