To enable users to build basic Cosmic Frog for Excel Applications to interact directly with Cosmic Frog from within Excel without needing to write any code, Optilogic has developed the Cosmic Frog for Excel Application Builder (also referred to as App Builder in this documentation). In this App Builder, users can build their own workflows using common actions like creating a new model, connecting to an existing model, importing & exporting data, creating & running scenarios, and reviewing outputs. Once a workflow has been established, the App can be deployed so it can be shared with other users. These other users do not need to build the workflow of the App again, they can just use the App as is. In this documentation we will take a user through the steps of a complete workflow build, including App deployment.

Get the App Builder from the Resource Library

You can download the Cosmic Frog for Excel – App Builder from the Resource Library. A video showing how the App Builder is used in a nutshell is included; this video is recommended viewing before reading further. After downloading the .zip file from the Resource Library and unzipping it on your local computer, you will find there are 2 folders included: 1) Cosmic_Frog_For_Excel_App_Builder, which contains the App Builder itself and this is what this documentation will focus on, and 2) Cosmic_Frog_For_Excel_Examples, which contains 3 examples of how the App Builder can be used. This documentation will not discuss these examples in detail; users are however encouraged to browse through them to get an idea of the types of workflows one can build with the App Builder.

The Cosmic_Frog_For_Excel_App_Builder folder contains 1 subfolder and 1 Macro-enabled Excel file (.xlsm):

1. The “working_files_do_not_change” folder. This folder and its contents do not need to be touched at all while building and deploying your Apps, and they need to be kept in the same folder as the .xlsm file.
2. A Macro-enabled Excel file named Cosmic_Frog_For_Excel_App_Builder_v1.xlsm. To ensure the App Builder will work fine, you may need to do one of the following on opening this file:
   1. Work through several Enable Content and/or Enable Macros messages & warnings.
   2. If you cannot work through the messages without the warnings about Macros/Content being disabled going away, you likely need to: close the .xlsm file, go to the folder where it is saved, right-click on it, select Properties, check the checkbox that says “Unblock” at the bottom, and click on OK. Then re-open the .xlsm file.

When ready to start building your first own basic App, open the Cosmic_Frog_For_Excel_Builder_v1.xlsm file; the next section will describe the steps a user needs to take to start building.

Building your App Workflow from Actions

When you open the Cosmic_Frog_For_Excel_App_Builder_v1.xlsm file in Excel, you will find there are 2 worksheets present in the workbook, Start and Workflow. The top of the Start worksheet looks like this:

1. App Keys are used to authenticate the user of the App Builder on the Optilogic platform. To get an App Key that you can enter into your Excel Apps, see this Help Center Article on Generating App and API Keys. During the first run of the App Builder, the App Key will be copied from the cell it is entered into to an app.key file in the same folder as the Excel .xlsm file, and it will be removed from the worksheet. User can then keep running the App without having to enter the App Key again unless the workbook or app.key file is moved elsewhere. It is important to emphasize that App Keys should not be saved into Excel Apps/the App Builder as they can easily be accidentally shared when the .xlsm files themselves are shared. Individual users need to authenticate with their own App Key.
2. The App Builder will attempt to retrieve the local path of where the App Builder .xlsm file is saved. However, if the file is saved in a Cloud Drive, this may fail, and the user will need to specify the path to the workbook in this field. User will be prompted to do so when attempting a run if this is the case.
3. Clicking on this “Sync your Optilogic Account” button will gather information from user’s Optilogic account, like for example lists of models and templates stored in the user’s account. It is recommended that you synchronize your account, so that drop-downs of model names, template names, etc. can be pre-populated with those present in the user’s account. This avoids typos and gives user an easy way to configure the actions that together make up a workflow.
4. Clicking on the “Build Workflow” button will take user to the Workflow worksheet. This is where the actual App building takes place.

Going to the Workflow worksheet and clicking on the Cosmic Frog tab in the ribbon, we can see the actions that are available to us to create our basic Cosmic Frog for Excel Applications:

1. We are in the App Builder Excel file named Cosmic_Frog_For_Excel_App_Builder_v1.xlsm.
2. Click on the Cosmic Frog tab in the ribbon to view the actions available to build a workflow for an App.
3. On the left-hand side the Workflow Actions are grouped together. More detailed descriptions and screenshots of these will follow further below, but quick descriptions of these are:
   1. Connect To Or Create Model: often the first step of a workflow is to create a new Cosmic Frog model or connect to an existing Cosmic Frog model.
   2. Import Data: this action imports data from a worksheet in the App into a table in the Cosmic Frog model that we are creating/connecting to.
   3. Export Data: this action exports data from a table in the Cosmic Frog model that we are connecting to into a worksheet in the App.
   4. Create Scenario: this action creates a new scenario in the Cosmic Frog model we are creating/connecting to.
   5. Create Item: this action creates a new scenario item in a scenario in the Cosmic Frog model we are creating/connecting to.
   6. Run Scenario: this action will run the scenario that is specified in the action using the chosen Cosmic Frog engine (e.g. Neo for network optimization, Throg for simulation, etc.).
   7. Run Utility: this action enables a user to run a Cosmic Frog Utility (a Python script), which could be a System Utility like looking up SMC3 LTL rates or a Utility specifically built for the App.
   8. Upload File: this action uploads a .csv file from the user’s computer to their Optilogic account.
   9. Download File: this action downloads a .csv or .txt file from the user’s Optilogic account into a specified worksheet of the App.
4. Next are 2 Run Control actions:
   1. Run Workflow: this action will run all the actions of the workflow that has been built.
   2. Run Actions: this action lets a user run a subset of the actions that are part of the workflow that has been built.
5. There are also 2 Editing actions:
   1. Move an Action: the order of actions can be changed by moving them to a different spot in the workflow using this action.
   2. Delete an Action: actions can be deleted using this action.
6. Lastly, there is the Deploy App Action in the Deployment section of the Cosmic Frog ribbon. Use this once the complete workflow of the App has been built and tested to share the finished App out to other users.

Building a Simple App

We will now walk through building and deploying a simple App to illustrate the different Actions and their configurations. This workflow will: connect to a Greenfield model in my Optilogic account, add records to the Customer and CustomerDemand tables, create a new scenario with 2 new scenario items in it, run this new scenario, and then export the Greenfield Facility Summary output table from the Cosmic Frog model into a worksheet of the App. As a last step we will also deploy the App.

‍

On the Workflow worksheet, we will start building the workflow by first connecting to an existing model in my Optilogic account:

1. Click on the “Connect To Or Create Model” action in the Workflow Actions section of the Cosmic Frog tab.
2. The “New Connect To Or Create Model Action” form opens up.
3. In each action there are 2 tabs, 1 named Action and 1 named Help. We are currently on the Action tab where we can configure the action. We will discuss the Help tab in the next screenshot below.
4. This is where we configure the action:
   1. Action Name: a user-defined unique name for the action, here we named the action “Connect to Greenfield Model”.
   2. If you have synchronized your Optilogic account (see above), then the Model field contains a drop-down list of all models present in the account. User can pick from this list to connect to this model. Alternatively, user can specify a new model name here in which case a new model with that name will be created in the user’s Optilogic account. In this case, we are connecting to an existing model of the name “United States Greenfield Facility Selection”.
   3. Template Name: if a new model is being created under bullet b, then a template from the Resource Library can be used for this. A copy of this template in the Resource Library is then created in the user’s Optilogic account. If a new model is created and template name is left blank, an empty new model is created. In this case this is left blank as we are connecting to an existing model and not creating a new one.
5. Once all the fields have been configured as desired, click on Create Action to finish setting up this action and it will then be added to the Workflow. User can also Cancel out if needed.

The following screenshot shows the Help tab of the “Connect To Or Create Model Action”:

1. The “New Connect To Or Create Model Action” form is open.
2. In each Action there are 2 tabs, 1 named Action and 1 named Help. We are currently on the Help tab where information on how to configure the action is provided. The Action tab was discussed above.
3. Here a description of what the action will do is given.
4. For each individual part of the action a short description of how to configure it is given too.

In the remainder of the documentation, we will not show the Help tab of each action. Users are however encouraged to use these to understand what the action does and how to configure it.

‍

After creating an action, the details of it will be added to 2 columns in the Workflow worksheet, see screenshot below. The first action of the workflow will use columns A & B, the next action C & D, etc. When adding actions, the placement on the Workflow worksheet is automatic and user does not need to do or change anything. Blue fields contain data that cannot be changed, white fields are user inputs when setting up the action and can be changed in the worksheet itself too.

1. Column A contains the names of the inputs into this action. These cannot be changed by the user.
2. The Action Type is set to ConnectToOrCreateModel based on the action that was used to create this step of the workflow. It sets up the code that will be run when this action is executed. This also cannot be changed by the user.
3. These are the user inputs into the action that were entered through the “New Connect To Or Create Model Action” form seen in the screenshot a bit further above. These can be changed by the user. Note however that model and template names that exist in a user’s Optilogic account are not automatically populated as drop-downs here, also not for accounts that have been synchronized. To prevent typos when wanting to use existing model and template names, it is preferred to set these fields through the action’s input form rather than typing text into the Workflow worksheet itself.
4. Several fields have comments associated with them that contain an explanation of the field and what type of input is expected. These pop up when hovering over the red triangle in the right top corner of the field. We will not mention these or show screenshots of these in the subsequent documentation, users are however encouraged to use these to help with configuring the actions of the workflow.

The United States Greenfield Facility Selection model we are connecting to contains about 1.3k customer locations in the US which have demand for 3 products: Rockets, Space Suits, and Consumables. As part of this workflow, we will add 10 customers located in the state of Ontario in Canada to the Customers table and add demand for each of these customers for each product to the CustomerDemand table. The next 2 screenshots show the customer and customer demand data that will be added to this existing model.

1. The New_Ontario_Customers worksheet in the App Builder’s workbook contains the customer data to be added to the Customers table in the existing Cosmic Frog model.
2. The column headings need to match those of the table in Cosmic Frog for this new data to be appended to the existing data correctly.
3. The Status of these new Customers is set to Exclude, so that existing scenarios will not change if they are being re-run after these customers have been added to the model by running the App.

1. The New_CustomerDemand worksheet in the App Builder’s workbook contains the demand data to be added to the CustomerDemand table in the existing Cosmic Frog model.
2. Again, the column headings need to match those of the table in Cosmic Frog for this new data to be appended to the existing data correctly.

First, we will use an Import Data action to append the new customers to the Customers table in the model we are connecting to:

1. Click on the Import Data action in the Workflow Actions section of the Cosmic Frog tab.
2. This opens the “New Import Data Action” form.
3. Configure the action’s inputs:
   1. Action: unique name of the action, set to “Import New Ontario Customers” here.
   2. Sheet Name: select the worksheet that contains the data to be imported from the drop-down list. Here, “New_Ontario_Customers” is selected.
   3. Cosmic Frog Table: select the Cosmic Frog table name the data should be imported into from the drop-down list. As there are many tables in the drop-down, starting to type the table name can help find it quicker.
   4. Upsert or Replace: choose Upsert if the data needs to be appended to any existing data in the table in the Cosmic Frog model. If there are records in the data that is being upserted that have the same value(s) for the table’s key column(s), the record that is being upserted will replace the whole record that is already present in the existing data. Choose Replace if all data that is already present in the table in the Cosmic Frog model should be cleared out and entirely replaced by the data that is being imported.
4. Clicking on Create Action adds this action to the Workflow worksheet, columns C & D.

Next, use the Import Data Action again to upsert the data contained in the New_CustomerDemand worksheet to the CustomerDemand table in the Cosmic Frog model, which will be added to columns E & F. After these 2 Import Data actions have been added, our workflow now looks like this:

1. The first Import Data action to import the new Ontario customers into the Customers table is placed in columns C & D and we can review / edit the configuration of it.
2. The second Import Data action to import the demand associated with the new Ontario customers into the CustomerDemand table is placed in columns E & F and we can review / edit the configuration of it.

Now that the new customers and their demand have been imported into the model, we will add several actions to create a new scenario where the new customers will be included. In this scenario, we will also remove the Max Number of New Facilities value, so the Greenfield algorithm can optimize the number of new facilities just based on the costs specified in the model. After setting up the scenario, an action will be added to run it.

‍

Use the Create Scenario action to add a new scenario to the model:

1. Action Name: set the unique name of the action, here it is set to “ON Customers Cost Optimized Scenario”.
2. Scenario Name: the name of the scenario that is being created, here set to “ON Customers Cost Optimized”. If the scenario name already exists in the model, it will be replaced by this one and its parameters. Any scenario items assigned to the existing scenario will also be assigned to this new scenario.
3. Scenario: an optional description of the scenario, left blank here.
4. Technology: the Cosmic Frog technology that should be run for this scenario. Options are:
   1. Neo – network optimization
   2. Hopper – transportation optimization
   3. Triad – Intelligent Greenfield
   4. Dendro – inventory optimization
   5. Throg – simulation

Then, use 2 Create Item Actions to 1) include the Ontario customers and 2) remove the Max Number Of New Facilities value:

1. Action Name: unique name of the action, here set to “Create Item to Include ON Customers”.
2. Item Name: the unique name of the scenario item, here set to “InclOnCustomers”. If a scenario item of the same name already exists in the model, it will be replaced by this new item. If the item name already exists in the scenario that is specified (bullet #7), then the new item will overwrite the already existing item, also if it is already assigned to 1 or multiple already existing scenarios (e.g. those items in the already existing scenarios will be updated to this new one).
3. Item Description: optional description of the scenario item.
4. Table Name: the name of the Cosmic Frog table the scenario item will change, here the Customers table.
5. Action: the change that will be made to the table. Here the value of the Status column will be changed to Include.
6. Condition: the condition (or “filter”) used – only the records in the table that match this condition will be changed, the others will remain unchanged. Here set to filter for Region = ‘Ontario’. This way, the status of all US customers remains unchanged.
7. Scenario Name: the scenario to which this item is applied. Can be selected from the drop-down which is populated from the scenarios that have been set up as part of the workflow. Here set to the scenario that was created in the previous action, “ON Customers Cost Optimized”. If the scenario name is set to an already existing scenario name in the model, the scenario item will be created and assigned to this existing scenario if the scenario item name is not the same as the name of an already existing scenario item assigned to different scenario(s).

1. Action Name: unique name of the action, here set to “Create Item to Set Max Facilities Blank”
2. Item Name: the unique name of the scenario item, here set to “MaxFacilitiesBlank”
3. Item Description: optional description of the scenario item.
4. Table Name: the name of the Cosmic Frog table the scenario item will change, here the GreenfieldSettings table.
5. Action: the change that will be made to the table. Here the value of the MaxNumberOfNewFacilities column will be changed to be blank (empty string), which is done by typing MaxNumberOfNewFacilities = ‘’ into this field.
6. Condition: the condition (or “filter”) used – only the records in the table that match this condition will be changed, the others will remain unchanged. Here no condition is used which means the change is applied to all records (which is only 1 in the GreenfieldSettings table).
7. Scenario Name: the scenario to which this item is applied. Can be selected from the drop-down which is populated from the scenarios that have been set up as part of the workflow. Here set to the scenario that was created 2 actions ago, “ON Customers Cost Optimized”.

After setting up the scenario and its 2 items, the next step of the workflow will be to run it. We add a Run Scenario action to the workflow to do so:

The configuration of this action takes following inputs:

1. Action Name: unique name of the action, here set to “Run ON customers Cost Optimized Scenario”
2. Scenario Name: the scenario to run, can be chosen from the drop-down, which contains the scenarios that have been created as part of this workflow, or user can type in the name of an already existing scenario in the model.
3. Technology: the Cosmic Frog technology that should be used when running this scenario. Options are:
   1. Neo – network optimization
   2. Hopper – transportation optimization
   3. Triad – Intelligent Greenfield
   4. Dendro – inventory optimization
   5. Throg – simulation
4. Resource Size: choose the size of the resource (in terms of number of cores and amount of memory (RAM)) on the Optilogic platform to use when running this scenario, ranging form Mini to 4XL. Read more about choosing an initial resource size and evaluating resource sizes in this Help Center article.

We now have a workflow that connects to an existing US Greenfield model, adds Ontario customers and their demand to this model, then creates and runs a new scenario with 2 items in this Cosmic Frog model. After running the scenario, we want to export the Optimization Greenfield Facility Summary output table from the Cosmic Frog model and load it into a new worksheet in the App. We do so by adding an Export Data Action to the workflow:

1. Action Name: a unique name for the action, here set to “Retrieve GF Facility Summary”
2. Cosmic Frog Table: the table in the Cosmic Frog model to export and then load into a worksheet in the App
3. Sheet Name: the name of the worksheet to load the exported table into. This can be into an existing worksheet that can be selected from the drop-down list, or a new one by typing its name into the field without using the drop-down list.

After adding the above actions to the workflow, the workflow worksheet now looks like the following 2 screenshots from column G onwards (columns A-F contain the first 3 actions as shown in a screenshot further above):

Columns G-H contain the details of the action that created the new ON Customers Cost Optimized scenario, and columns I-J & K-L contain the details of the actions that added the 2 scenario items to this scenario.

Columns M-N contain the details of the action that will run the scenario that was added and columns O-P those of the action that will export the selected output table (Optimization Greenfield Facility Summary) into the GF_Facility_Summary worksheet of the App.

‍

To run the completed Workflow, all we need to do is click on the Run Workflow action and confirm we want to run it:

After kicking off the workflow, if we switch to the Start worksheet, details of the run and its progress are shown in rows 9-11:

Looking on the Optilogic Platform, we can also check the progress of the App run and the Cosmic Frog model changes:

1. After logging into cosmicfrog.com, go to the Run Manager application in the list on the left.
2. There are 3 jobs that a run of the App creates on the Optilogic platform, the first one is running the Python script associated with the App Builder, called workflow_runner.py.
3. The ‘United (‘triad’) job runs the model associated with the workflow set up in the App Builder.
4. This run of Type = Scenario runs the specific scenario that was created in the workflow: the ON Customers Cost Optimized scenario.

Once the run is done all 3 jobs will have their State changed to Done, unless an error occurred in which case the State will say Error.

‍

Checking the United Stated Greenfield Facility Selection model itself in the Cosmic Frog application on cosmicfrog.com:

1. This is the Customers table in this United States Greenfield Facility Selection model in the Cosmic Frog app on cosmicfrog.com.
2. Notice that the 10 customers in Ontario Canada have been successfully added to the Customers table.

Once the App is finished running, we see that a worksheet named GF_Facility_Summary was added to the App Builder:

1. We are on the added worksheet named GF_Facility_Summary.
2. Filtering the scenarioname (column A) for the scenario that was run, ON Customers Cost Optimized, we can review the outputs of this scenario: how many Greenfield Facilities were selected in this scenario, how much of the demand does each location serve and what is the location (latitude & longitude) of each Greenfield facility.

Additional Available Actions

There are several other actions that users of the App Builder can incorporate into a workflow or use to facilitate workflow building. We will cover these now. Feel free to skip ahead to the “Deploying the App” section if your workflow is complete at this stage.

‍

Additional actions that can be incorporated into workflows are the Run Utility, Upload File, and Download File actions. The Run Utility action can be used to run a Cosmic Frog Utility (a Python script), which currently can be a Utility downloaded from the Resource Library or a Utility specifically built for the App.

‍

There are currently 4 Utilities available in the Resource Library:

1. On the Optilogic platform, navigate to the Resource Library. If you do not see the icon on the left-hand side of the screen, then click on the icon with the 3 dots, which will then show the icons of all applications on the left-hand side of the screen.
2. Click on the tag filter icon and scroll down and check the checkbox for the Utilities tag, you will now see 4 resources in the list.
3. The Copy Dashboard to a Model Utility is selected in this screenshot. A description of this Utility is provided in the Preview section on the right, together with a Video Introduction and Related Files section.
4. If you want to use this Utility, then download the .py file from here.
5. The video with this Utility is also listed with the other Utilities that are available in the Resource Library and is recommended viewing if you want to modify any of these Utilities or build your own entirely.

After downloading the Python file of the Utility you want to use in your workflow, you need to copy it into the working_files_do_not_change folder that is located in the same folder as where you saved the App Builder. Now you can start using it as part of the Run Utility action. In the below example, we will use the Python script from the Copy Map to a Model Resource Library Utility to copy a map and all its settings from one model (“United States Greenfield Facility Selection”, the model connected to in a previous action) to another (“European Greenfield Facility Selection”):

1. Click on the Run Utility action in the Workflow Actions section of the Cosmic Frog tab.
2. Configure the inputs of the Run Utility action:
   1. Action Name: unique name of the action, set to “Copy GF Serviceband map from US to EU model” here.
   2. Utlity Name: name of the Python file of the Utility which is placed in the working_files_do_not_change folder. Ensure the name is exactly the same as the name of the file, including the .py extension.
   3. Utility Params: specify the parameters of the utility here in a comma separated format, enclosing the value of each paramter in double quotes. For this specific utility, the parameters are:
      1. Destination Model: the name of the model to copy the map to, set to “European Greenfield Facility Selection” here.
      2. Replace or Append maps: Replace will overwrite the map in the destination model if a map with the same name already exists in that model; Append will add a new, automatically renamed map. Set to “Append” here.
      3. Copy All or a Specific Map: setting this parameter to “All” will copy all maps from the model that the workflow has connected to to the destination model; setting it to “Specific” will only copy the map specified in the next parameter. Set to “Specific” here.
      4. Specific Map to Copy: if the third parameter (previous bullet) is set to “Specific”, then this parameter needs to be set to the name of the map to be copied, in this case it is set to “(Triad) Greenfield”. If the previous bullet is set to “All” then this parameter can be left blank by just typing “” for it.

The parameters of the Copy Dashboard to a Model Utility are the same as those of the Copy Map to a Model Utility:

1. First paramter: Destination Model – name of the model to copy the dashboard(s) to
2. Second parameter: Replace or Append Dashboards – type “Replace” or “Append”)
3. Third parameter: Copy All or a Specific Dashboard – type “All” or “Specific”
4. Fourth parameter: Specific Dashboard To Copy – name of the dashboard to copy if third parameter is set to “Specific”, or leave blank by typing “” if third parameter is set to “All”

The Orders to Demand and Delete SaS Scenarios utilities do not have any parameters that need to be set, so the Utility Params part of the Run Utility action can be left blank when using these utilities.

‍

The Upload File action can be used to take a worksheet in the App Builder and upload it as a .csv file to the Optilogic platform:

1. Click on the Upload File action in the Workflow Actions section of the Cosmic Frog tab.
2. The data we want to upload needs to be in a worksheet in the App Builder, here it is located on the worksheet named “Transportation & Coursing Costs”.
3. Configure the inputs of the Upload File action:
   1. Action Name: unique name of the action, here set to “Upload Cost Data”.
   2. Sheet Name: the name of the worksheet to be uploaded, can be chosen from the drop-down list.
   3. File Name: the name of the .csv file that will be uploaded; this file will contain the data from the worksheet specified as Sheet Name (previous bullet).

Files that get uploaded to the Optilogic platform are placed in a specific working folder related to the App Builder, the name and location of which are shown in this screenshot:

1. On the Optilogic platform, if the Explorer is not open yet, open it by clicking on the > button at the top left of the screen.
2. Expand the “My Files” folder.
3. Scroll down to find the “z Working Folder for Workflow App” folder and expand it. This is the folder on the Optilogic platform where all files related to the App Builder will be stored.
4. The file that was just uploaded through the Upload File action, Trans_Sourcing_Costs.csv is therefore also located in this folder.

The Download File action can be used to download a .txt file from the Optilogic platform and load it into a worksheet in the App:

1. Click on the Download File action in the Workflow Actions section of the Cosmic Frog tab.
2. Configure the inputs of the Download File action:
   1. Action Name: unique name of the action, here set to “Download Aggregated Outputs”.
   2. File Name: name of the .txt file on the Optilogic platform to be downloaded. This file needs to be in the “z Working Folder for Workflow App” folder under your “My Files” folder, see screenshot a bit further above.
   3. Sheet Name: name of the worksheet to load the data from the .txt file into, can be chosen from the drop-down list.

Other actions that facilitate workflow building are the Move an Action, Delete an Action, and Run Actions actions, which will be discussed now. If the order of some actions needs to be changed, you do not need to remove and re-add them, you can use the Move an Action action to move them around:

1. Click on the Move an Action icon in the Editing section of the Cosmic Frog tab.
2. Action To Move: from the drop-down list, choose the Action that needs to be moved.
3. Move In Front Of: from the drop-down list, choose the Action in front of which the Action that is being moved needs to be placed.

It is also possible that an action needs to be removed from a Workflow. For this, the “Delete an Action” action can be used, rather than manually deleting it from the Workflow worksheet and trying to move other actions in its place:

1. Click on the Delete an Action icon in the Editing section of the Cosmic Frog tab.
2. From the drop-down list, choose the action that needs to be deleted.

Instead of running a complete workflow, it is also possible to only run a subset of the actions that are part of the workflow:

1. Click on Run Actions in the Run Controls section of the Cosmic Frog tab.
2. In the list, select the Action(s) that you want to run. If you want to select multiple actions, you can hold down the Shift and Ctrl keys on your computer, while clicking on the Actions you want to include in the run.

Deploying your App

Once a workflow has been completed in the Cosmic Frog for Excel App Builder, it can be deployed so other users can run the same workflow without having to build it first. This section covers the Deployment steps.

1. Click on the Deploy App option in the Deployment section of the Cosmic Frog tab.
2. App Name: enter the name you want to give to the deployed application; we have called our App “US Greenfield Add Ontaria CAN” here.
3. Click on Deploy App.

The following message will come up after the app had been deployed:

Looking in the folder mentioned in this message, we see the following contents:

1. We have navigated to the US Greenfield Add Ontaria CAN folder in our File Explorer, which is a folder inside the folder where the App Builder is saved.
2. There is a subfolder here called working_files_do_not_change which users should leave as is and as the message says copy along with the other contents of the folder when sharing with other users.
3. The deployed App, named US greenfield Add Ontaria CAN.xlsm. When sharing the App with other users, this is the file they will open and run the App from, see also next screenshot:

1. We are on the Start worksheet of the deployed App.
2. Users need to add their own App Key, see this Help Center Article on Generating App and API Keys to learn how to generate your own App Key.
3. If the Excel workbook is in the Cloud, users may need to add the path to the workbook here. If this is needed, user will be prompted to do so when trying to first run the App.
4. On the New_Ontario_Customers and New_CustomerDemand worksheets, users can update the Ontario data that needs to be upserted to the model.
5. Once ready to run the app, user can click on the Run App button, progress will be shown in rows 9-11 while the App is running (or user can have a look in the Run Manager on the Optilogic platform too).
6. Once the run is completed, the updated Optimization Greenfield Facility Summary table will be loaded into the GF_Facility_Summary worksheet.

Congratulations on building & deploying your own Cosmic Frog for Excel App!

‍

If you want to build Apps that go beyond what can be done using the App Builder, you can do so too. This may require some coding using Excel VBA, Python, and/or SQL. Detailed documentation walking through this can be found in this Getting Started with Cosmic Frog for Excel Applications article on Optilogic’s Help Center.

‍

Teams is an exciting new feature set designed to enhance collaboration within Supply Chain Design, enabling companies to foster a more connected and efficient working environment. With Teams, users can join a shared workspace where all team members have seamless access to collective models and files. This ensures that every piece of work remains synchronized, providing a single source of truth for your data. When one team member updates a file, those changes instantly reflect for all other members, eliminating inconsistencies and ensuring that everyone stays aligned.

‍

Beyond simply improving collaboration, Teams offers a structured and flexible way to organize your projects. Instead of keeping all your files and models confined to a personal account, you can now create distinct teams tailored to different projects, departments, or business functions. This means greater clarity and easier navigation between workspaces, ensuring that the right content is always at your fingertips.

‍

Consider the possibilities:

• Want a dedicated team for Network Optimization and another for Transportation Optimization or perhaps North American and EMEA based projects? Now you can keep these projects separate yet easily accessible.
• Need a centralized space for onboarding materials, shared model templates, or best practices? Create a dedicated team for it.
• Are you a partner managing multiple clients? Organize your work into client-specific teams to maintain clarity and ensure that each client’s data remains distinct yet effortlessly accessible.

Teams introduces a more intuitive and structured way to collaborate, organize, and access your work—ensuring that your team members always have the latest updates and a streamlined experience. Get started today and transform the way you work together!

‍

This documentation contains a high-level overview of the Teams feature set, details the steps to get started, gives examples of how Teams can be structured, and covers best practices. More detailed documentation for Organization Administrators and Teams Users is available in the following help center articles:

• Optilogic Teams – Administrator Guide
• Optilogic Teams – User Guide

Teams Feature Set Overview

The diagram below highlights the main building blocks of the Teams feature set:

1. The Organization Dashboard is central to the feature set – this is where organization administrators can create/edit teams and add/remove users to teams and the organization.
2. The Team Hub application is an integral part of the user’s Teams experience – here they can see and access the teams they are part of and their own personal workspace (My Account), observe the activities of team members, and switch context from one team to another.
3. Sharing of files & models has been enhanced, ensuring there is a suitable method for each purpose – share access for multi-user collaboration, transfer ownership to hand-off full control, or send a copy to provide a snapshot.
4. The breadcrumb trail of Org > Team > App (e.g. Company X > EMEA > Cosmic Frog) in the browser tabs and within the Optilogic platform itself show a user exactly where they are working at all times.
5. The activity feed of each team is an automatic log capturing who of the team members did what when (e.g. creating a new .csv file, uploading a model, running a query, viewing a model) – this can replace manual updates by email or instant messaging.
6. When a user is within the context of a particular team, everything they see and interact with is limited to that team. This includes for example folders & files in the Explorer, and the list of model runs in the Run Manager application.

How to Get Started

At a high-level, these are the steps to start using the Teams feature set:

1. Establish Your Organization​
   1. Contact Optilogic Support to specify your organization’s Admin User(s) and email domains.
   2. Optilogic creates your organization on the Optilogic platform where domain association ensures pre-populated user lists.
2. Create Teams & Add Users (Organization Admins Only, see the Optilogic Teams – Administrator Guide help center article)
   1. Org Dashboard → Teams application → ‘Create Team’ button → Add Members
3. Use Team Hub​ (see the Optilogic Teams – User Guide help center article)
   1. Switch into a team to view shared content​
   2. All actions (model runs, file creation) are now associated with that team​
4. Add Content​
   1. Upload / create directly in the team workspace
   2. Share copies from your personal workspace, see this “Model Sharing & Backups for Multi-User Collaboration” help center article for more details on model sharing​
   3. Copy from the Resource Library, see this “How to use the Resource Library” help center article for more details

Team Structure Use Cases

Here follow 5 examples of how teams can be structured, including an example for each and an explanation of why such a setup works well.

1. Organizing Projects by Function or Focus Area
   1. Example: A company with multiple supply chain initiatives can create separate teams for Network Optimization, Transportation Planning, and Inventory Management.
   2. Why it works: Each team gets its own dedicated workspace, keeping content siloed and relevant. Members focus on their specific initiatives without distractions from unrelated files or models.
2. Global or Regional Team Structures
   1. Example: Multinational companies can set up teams by region (e.g., North America, EMEA, APAC) or country to manage localized models and data.
   2. Why it works: Supports localized decision-making, ensuring teams have the autonomy to manage their own supply chain designs, while corporate admins maintain visibility and governance.
3. Client-Specific Workspaces for Consulting or Partner Organizations
   1. Example: A consulting partner managing multiple clients (e.g., Client A, Client B, Client C) can create client-specific teams to store each client’s data, models, and deliverables.
   2. Why it works: Keeps client data separate, secure, and easily accessible, reducing the risk of accidentally sharing files with the wrong client and providing a clear organizational structure.
4. Centralized Training & Onboarding Resources
   1. Example: An organization can create a Team for Onboarding & Best Practices, where new employees can access shared training materials, model templates, and company standards.
   2. Why it works: Simplifies onboarding by giving new hires a single source of truth. Reduces time spent searching for resources and ensures they’re working with the latest templates and guidelines
5. R&D and Innovation Labs
   1. Example: Create a team dedicated to Proof of Concept (PoC) projects or innovation initiatives, where experimentation can happen without cluttering production environments.
   2. Why it works: Keeps experimental content separate from operational projects, reducing confusion while still enabling collaboration among innovation teams.

Best Practices

Please keep following best practices in mind to ensure optimal use of the Teams feature set:

1. Use clear naming conventions for the team names. This will ensure everyone can quickly identify the correct team spaces & files. Like for example Partner – Client, or Department – Project.
2. Instead of written / verbal status updates, use the Activity feed in Team Hub to understand the details of who worked on what when.
3. Before sharing a model, consider which type of sharing is most suitable: use Send Copy for snapshots, Share Access for live collaboration, and Transfer Ownership for full hand-off.
4. Create a dedicated team to store training materials, e.g. an Onboarding team.
5. Return to your personal space in My Account to keep workspace content separate.
6. Perform regular housekeeping (e.g. weekly) by archiving or removing active teams & content – a small effort upfront can prevent accidental exposure later.

Once you have set up your teams and added content, you are ready to start collaborating and unlocking the full potential of Teams within Optilogic!

‍

Let us know if you need help along the way—our support team (support@optilogic.com) has your back.

Depending on the type of supply chain one is modelling in Cosmic Frog and the questions being asked of it, it may be necessary to utilize some or all the features that enable detailed production modelling. A few business case examples that will often include some level of detailed production modelling include:

• Tactical capacity planning of packaging lines at a weekly or monthly level. Think for example of a beverage bottling company determining which bottling line at what plant should produce which product(s) for the next 8-12 weeks. This can involve optimizing how to manage seasonality where the model decides whether pre-building when there is slack capacity is more beneficial than sourcing from farther away if there is only slack capacity in more distant factories during high season. Known outages due to pre-planned maintenance and repair can also be incorporated as to minimize the impact of those. For a somewhat longer horizon, say for example 1-2 years, upgrades to existing lines or adding/removing any lines can be considered in the modelling too to see the impact on the overall utilization and costs.
• If raw materials, intermediates, bulk products, packaging products and/or by-products are a significant part of the network, either in terms of cost or storage space they take up, they may need to be included in the modelling. Where these are sourced from, their storage options, associated costs, and how these are converted into each other can be specified. Examples are for example the production of cheese or beer.
• Determine the production equipment investment strategy for the mid- to long-term based on forecasted demand. When and where is it most optimal to add or remove production capability in the next 5-10 years? Think of decisions like building a new plant vs adding new lines or upgrading existing line(s) at an existing plant, is it more advantageous to invest in higher throughput, more expensive equipment than in lower throughput less expensive equipment, etc. For these types of questions often a lot of scenarios that explore sensitivity around the demand forecast and supply & production costs are run to ultimately select the most robust solution to move forward with.

In comparison, modelling a retailer who buys all its products from suppliers as finished goods, does not require any production details to be added to its Cosmic Frog model. Hybrid models are also possible, think for example of a supermarket chain which manufactures its own branded products and buys other brands from its suppliers. Depending on the modelling scope, the production of the own branded products may require using some of the detailed production features.

The following diagram shows a generalized example of production related activities at a manufacturing plant, all of which can be modelled in Cosmic Frog:

In this help article we will cover the inputs & outputs of Cosmic Frog’s production modelling features, while also giving some examples of how to model certain business questions. The model in Optilogic’s Resource Library that is used mainly for the screenshots in this article is the Multi-Year Capacity Planning. There is a 20-minute video available with this model in the Resource Library, which covers the business case that is modelled and some detail of the production setup too.

General Pointers before diving into Detailed Production

To not make this document too repetitive we will cover some general Cosmic Frog functionality here that applies to all Cosmic Frog technologies and is used extensively for production modelling in Neo too.

Using the Neo (network optimization) Technology Filter

To only show tables and fields in them that can be used by the Neo network optimization algorithm, select Optimization in the Technologies Filter from the toolbar at the top in Cosmic Frog. This will hide any tables and fields that are not used by Neo and therefore simplifies the user interface.

Information contained in Tooltips

Quite a few Neo related fields in the input and output tables will be discussed in this document. Keep in mind however that a lot of this information can also be found in the tooltips that are shown when you hover over the column name in a table, see following screenshot for an example. The column name, technology/technologies that use this field, a description of how this field is used by those algorithm(s), its default value, and whether it is part of the table’s primary key are listed in the tooltip.

UOM (unit of measure) Input Fields

There are a lot of fields with names that end in “…UOM” throughout the input tables. How they work will be explained here so that individual UOM fields across the tables do not need to be explained further in this documentation as they all work similarly. These UOM fields are unit of measure fields and often appear to the immediate right of the field that they apply to, like for example Unit Value and Unit Value UOM in the screenshot above. In these UOM fields you can type the Symbol of a unit of measure that is of the required Type from the ones specified in the Units Of Measure input table. For example, in the screenshot above, the unit of measure Type for the Unit Value UOM field is Quantity. Looking in the Units Of Measure input table, we see there are 2 of these specified: Each and Pallet, with Symbol = EA and PLT, respectively. We can use either of these in this UOM field. If we leave a UOM field blank, then the Primary UOM for that UOM Type specified in the Model Settings input table will be used. For example, for the Unit Value UOM field in the screenshot above the tooltip says Default Value = {Primary Quantity UOM}. Looking this up in the Model Settings table shows us that this is set to EA (= each) in our current model. Let’s illustrate this with the following screenshots of 1) the tooltip for the Unit Value UOM field (located on the Products input table), 2) units of measure of Type = Quantity in the Units Of Measure input table and 3) checking what the Primary Quantity UOM is set to in the Model Settings input table, respectively:

Note that only hours (Symbol = HR) is currently allowed as the Primary Time UOM in the Model Settings table. This means that if another Time UOM, like for example minutes (MIN) or days (DAY), is to be used, the individual UOM fields need to be utilized to set these. Leaving these blank would mean HR is used by default.

Status and Notes fields

With few exceptions, all tables in Cosmic Frog contain both a Status field and a Notes field. These are often used extensively to add elements to a model that are not currently part of the supply chain (commonly referred to as the “Baseline”), but are to be included in scenarios in case they will definitely become part of the future supply chain or to see whether there are benefits to optionally include these going forward. In these cases, the Status in the input table is set to Exclude and the Notes field often contains a description along the lines of ‘New Market’, ‘New Line 2026’, ‘Alternative Recipe Scenario 3, ‘Faster Bottling Plant5 China’, ‘Include S6’, etc. When creating scenario items for setting up scenarios, the table can then be filtered for Notes = ‘New Market’ while setting Status = ‘Include’ for those filtered records. We will not call out these Status and Notes fields in each individual input table in the remainder of this document, but we do encourage users to use these extensively as they make creating scenarios very easy. When exploring any Cosmic Frog models in the Resource Library, you will notice the extensive use of these fields too. The following 2 screenshots illustrate the use of the Status and Notes fields for scenario creation: 1) shows several customers on the Customers table where CZ_Secondary_1 and CZ_Secondary_2 are not currently customers that are being served but we want to explore what it takes to serve them in future. Their Status is set to Exclude and the Notes field contains ‘New Market’; 2) a scenario item called ‘Include New Market’ shows that the Status of Customers where Notes = ‘New Market’ is changed to ‘Include’.

The Status and Notes fields are also often used for the opposite where existing elements of the current supply chain are excluded in scenarios in cases where for example manufacturing locations, products or lines are going to go offline in the future. To learn more about scenario creation, please see this short Scenarios Overview video, this Scenario Creation and Maps and Analytics training session video, this Creating Scenarios in Cosmic Frog help article, and this Writing Scenario Syntax help article.

Summary of Multi-Year Capacity Planning model

The model that is mostly used for screenshots throughout this help article is as mentioned above the Multi-Year Capacity Planning model that can be found here in the Resource Library. This model represents a European cheese supply chain which is used to make investment decisions around the growth of a non-mature market in Eastern Europe over a 5-year modelling horizon. New candidate DCs are considered to serve the growing demand in Eastern Europe, the model optimizes which are optimal to open and during which of the 5 years of the modelling horizon. The production setup in the model uses quite a few of the detailed modelling features which will be discussed in detail in this document:

• Raw and bulk materials are included in the modelling. As part of the cheese production process, the model uses recipes (bills of materials) to convert raw materials into bulk materials and make the finished goods from the bulk materials.
• The cheese making equipment, i.e. the production lines, at 2 plants is also part of the model. A new additional production line is set up at each of the 2 cheese making plants to see where and when a new line needs to be added to keep meeting demand.
• Detailed costs and capacities of the production processes are modelled.

Note that in the screenshots of this model, the columns have been re-ordered sometimes, so you may see a different order in your Cosmic Frog UI when opening the same tables of this model.

The 2 screenshots below show the Products and Facilities input tables of this model in Cosmic Frog:

1. We are on the Products input table.
2. Three raw materials (RAW_) that are ingredients of the cheese making process are being modelled: rennet, additive, and milk.
3. From the 3 raw materials, 5 different cheeses are made, in bulk form (BULK_…).
4. From the bulk materials, the 5 finished goods (FG_) cheeses (MOZ – mozzarella, CHE – cheddar, SWI – Swiss, FET – feta, and BLU – blue) are made by packaging the bulk materials.
5. All products have a value associated with them to calculate inventory holding costs.
6. Only the finished goods that will be sold have a price associated with them in order to calculate revenues.

Note that the naming convention of the products lends itself to easy filtering of the table for the raw materials, bulk materials, and finished goods due to the RAW_, BULK_, and FG_ prefixes. This makes the creation of groups and setting up of scenarios quick and easy.

1. We are on the Facilities input table.
2. There are 3 suppliers (SUP_) in this model, these supply the raw materials to the DCs.
3. There are 2 plants (PLT_) in this model, these turn the raw materials into the bulk products and package them into the finished goods.
4. There are 3 DCs (DC_) in this model, these receive the finished goods from the Plants and serve the 23 Europe based customers (specified in the Customers input table, not shown here).
5. There is an Overflow facility specified, which as a last resort can produce any of the finished goods at a very high cost and serve these to all the customers. This is really a modelling trick to not have the model return infeasible if there is not enough capacity to fulfill all demand. The results of when and where the Overflow location is used will give the user valuable information about capacity insufficiencies.

Note that similar to the naming convention of the products, the facilities are also named with prefixes that facilitate filtering of the facilities so groups and scenarios can quickly be created.

‍

Here is a visual representation of the model with all facilities and customers on the map:

Production Modelling in Cosmic Frog

The specific features in Cosmic Frog that allow users to model and optimize production processes of varying levels of complexity while using the network optimization engine (Neo) include the following input tables:

1. We are in the Input Tables part of the Data section of the Multi-Year Capacity Planning Cosmic Frog model.
2. Click on the square grid to go to input tables if not yet there. Clicking on the round grid icon will open the list of output tables, while clicking on the square grid icon with solid bar at the top will open the list of custom tables.
3. The 4 single-period production focussed input tables are:
   1. Production Policies
   2. Processes
   3. Bills Of Materials
   4. Work Centers
4. There are 3 multi-time period tables that are specific for production:
   1. Production Policies Multi-Time Period
   2. Processes Multi-Time Period
   3. Work Centers Multi-Time Period
5. Of the Constraints tables, the following 3 are production related:
   1. Production Constraints
   2. Production Count Constraints
   3. Work Center Count Constraints

We will cover all these production related input tables to some extent in this article, starting with a short description of each of the basic single-period input tables:

• Production Policies: any product that is in-scope for the modelling and originates within the supply chain needs to have at least 1 production policy set up on the Production Policies table. Products that originate from outside the supply chain can be added by using the Supplier Capabilities and Procurement Policies input tables. The production policies contain the details of which products can be made where. In addition, costs and production rates can optionally be associated through the production policies table. If the manufacturing process of the product requires more detailed modelling, then bills of materials, processes, and/or work centers can also optionally be associated through production policies.
• Bills Of Materials: if raw materials are for example in-scope for modelling, then the Bills of Materials (BOMs) table can be used to specify the “recipes” of how much of each raw material goes into a finished good. Any stage of a product from raw material, component to intermediate, bulk, by-product to finished good can be modelled if deemed in-scope. Examples:
  • Bottling or other packaging plants – BOMs for the ratio of packaging and bulk food/drink product
  • Recipes to manufacture products like cheese from rennet, milk, cultures, and salt or to produce beer from malt, hops, yeast, and water. If multiple different recipes exist for the same product, Cosmic Frog can optimize which recipe is best to use when and where.
• Processes: if details such as costs, production rates, and/or changeover times/costs of a manufacturing process need to be included in the model, this can be achieved by utilizing the Processes table. Each step of a process can also have a Work Center associated with it.
• Work Centers: if detailed costs and capacities of resources like packaging lines, machines, tools, and/or robots need to be taken into account, this can be done by specifying those details in the Work Centers table.

These 4 tables feed into each other as follows:

A couple of notes on how these tables work together:

• If Bills of Materials, Processes and/or Work Centers are specified in the model, they will only be used if they are associated with a Production Policy.
• If both a Process and a Work Center are specified on the same Production Policy, the Work Center on that Production Policy will be ignored. This is regardless of the Process itself having a Work Center associated with it or not.

Basic Production Input Tables

Production Policies

For all products that are explicitly modelled in a Cosmic Frog model, there needs to be at least 1 policy specified on the Production Policies table or the Supplier Capabilities table so there is at least 1 origin location for each. This applies to for example raw materials, intermediates, bulk materials, and finished goods. The only exception is if by-products are being modelled, these can have Production Policies associated with them, but do not necessarily need to (more on this when discussing Bills of Materials further below). From the 2 screenshots below of the Production Policies table, it becomes clear that depending on the type of product and the level of detail that is needed for the production elements of the supply chain, production policies can be set up quite differently: some use only a few of the fields, while others use more/different fields.

1. We are on the Production Policies input table.
2. These 2 records are for 2 of the 3 raw materials: RAW_RENNET and RAW_MILK are specified as the Product Names. A group named ALL_SUP_GROUP, which consists of all 3 suppliers, is specified as the Facility Name on both records. This means that these 2 raw materials can be made at each of the 3 suppliers. The Unit Cost field indicates that the cost of these is €1 each for both raw materials at all 3 suppliers. Had the costs been different at the different suppliers, separate records would have needed to be set up to capture that detail (or a Lookup would have needed to be used to read in costs data from an external source like for example an Excel worksheet).
3. These 3 records are all for the third raw material: RAW_ADDITIVE, as specified as the Product Name. Here the Facility Name field contains the individual names of the suppliers, SUP_1, SUP_2, and SUP_3. These are set up as separate records so the different Unit Costs (€10, €15, and €18 respectively) can be captured correctly.
4. None of these records for the raw materials at the suppliers require more detailed production modelling through BOMs, Processes or Work Centers, and therefore the BOM Name, Process Name, and Work Center Name fields are blank on these records.
5. This record tells us that the OVERFLOW location (Facility Name), can make all products that are part of the ALL_FG_GROUP (Product Name), which contains the 5 finished good cheeses, using a process named PROC-OVERFLOW (Process Name), but at a high cost of €1000 (Unit Cost) for each unit of cheese (Unit Cost UOM = EA).

A couple of notes as follows:

• In this specific model the suppliers have been added to the Facilities table, and therefore the policies for raw materials are specified on the production policies table. Had the suppliers been added to the Suppliers table instead, then the Suppliers Capabilities table would have been used to specify that the raw materials are coming from the supplier locations and at what cost. This is a modelling decision where the main difference between facilities and suppliers is that inventory and certain costs (like fixed operating) can be modelled at facilities, but not at suppliers.
• If a Process is used as part of a production policy (i.e. the Process Name field is used), then the Unit Cost set on this production policy will be ignored, since the record for the specified Process Name in the Processes table overrides the details of the production policy.

Next, we will look at a few other records on the Production Policies input table:

1. We are on the Production Policies input table still, now filtered for the mozzarella bulk material (BULK_MOZ) and finished good (FG_MOZ).
2. This record tells us that the bulk material BULK_MOZ (Product Name) can be made at PLT_1 (Facility Name) using a bill of materials named BOM_BULK_MOZ (BOM Name).
3. These 2 records tell us that the finished good FG_MOZ (Product Name) can be made at PLT_1 (Facility Name) using a bill of materials named BOM_FG_MOZ (BOM Name) and using either a process named PROC-PLT_1_Line-FG_MOZ or a process named PROC-PLT_1_NewLine-FG_MOZ. As the names of the Processes suggest:
   1. The first record uses a process on a currently existing production line that is located at PLT_1 and which is specific for making mozzarella cheese.
   2. The second record uses a process on a production line that currently does not exist but is considered to be added at PLT_1, and will also be specific for mozzarella cheese.

We will take a closer look at the BOMs and Processes specified on these records when discussing the Bills of Materials and Processes tables further below.

Note that the above screenshot was just for PLT_1 and mozzarella, there are similar records in this model for the other 4 cheeses which can also be made at PLT_1, plus similar records for all 5 cheeses at PLT_2, which includes a new potential production line for future expansion too.

Other fields on the Production Policies table that are not shown in the above 2 screenshots are:

• Production Rate with its 2 UOM fields, Rate Quantity UOM & Rate Time UOM: use these fields to specify how long production of this product at this facility takes. For example, setting Production Rate = 5, Rate Quantity UOM = EA (eaches), and Rate Time UOM = HR (hours) means that 5 units are produced per hour, which equates to 12 minutes for 1 unit.
• CO2 Emission Rate with its CO2 Emission Rate UOM field: use these fields to specify how much CO2 is emitted when producing this product at this facility. For example, setting CO2 Emission Rate = 0.2 and CO2 Emission Rate UOM = KG means that per unit produced 0.2 kg CO2 is emitted.

Bills of Materials

The recipes of how materials/products of different stages convert into each other are specified on the Bills of Materials (BOMs) table. Here the BOMs for the blue cheese (_BLU) products are shown:

1. We are on the Bills of Materials input table.
2. These 3 records make up 1 bill of materials as they all have the same BOM Name “BOM_BULK_BLU”. The Product Name and Product Type fields tells us that there are 3 different raw materials (RAW_ADDITIVE, RAW_MILK, and RAW_RENNET) that are going into this recipe as Components. The Quantity field indicates how much of each raw material is used: 0.1 units of RAW_ADDITIVE, 5 units of RAW_MILK, and 0.01 units of RAW_RENNET.
3. This record has BOM Name set to “BOM_FG_BLU” which suggests it is the BOM to make the finished good blue cheese. There is only 1 Component (Product Type) that is the ingredient into this BOM, which is BULK_BLU (Product Name). 1 unit (Quantity) of it is used for the BOM.

Note that the above specified BOMs are both location and end-product agnostic. Their names suggest that they are specific for making the BULK_BLU and FG_BLU products, but only associating these BOMs on a Production Policy which has Product Name set to these makes this connection. We can use these BOMs at any location that they apply. Filtering the Production Policies table for the BULK_BLU and FG_BLU products we can see that 1) BOM_BULK_BLU is indeed used to make BULK_BLU and BOM_FG_BLU to make FG_BLU, and 2) the same BOMs are used at PLT_1 and PLT_2:

It is of course possible that the same product uses a different BOM at a different location. In this case, users can set up multiple BOMs for this product on the BOMs table and associate the correct one at the correct location in the Production Policies table. Choosing a naming convention for the BOM Names that includes the location name (or a code to indicate it) is recommended.

The screenshot above of the Bills of Materials table only shows records with Product Type = Component. Components are input into a BOM and are consumed by it when producing the end-product. Besides Component, Product Type can also be set to End Product or Byproduct. We will explain these 2 product types through the examples in this following screenshot:

1. BOM_BULK_BLU (BOM Name) now has 1 additional record where BYP_WHEY (Product Name) is specified as a Byproduct (Product Type) of the production of BULK_BLU. 2 units (Quantity) of BYP_WHEY are produced as part of the production process of BULK_BLU. To include by-products in a Cosmic Frog model, they not only need to be specified as part of the appropriate BOM(s), but also:
   1. Need to be specified on the Products table.
   2. Need to either have an Inventory Policy with Stocking Site = True at the Facility where the by-product is being produced or need to have Transportation and/or Sourcing Policies (depending on the Lane Creation Rule setting in the Neo run parameters) set up from the location where it is produced to end up at a location where there is an Inventory Policy with Stocking Site = True for it.
2. BOM_FG_BLU (BOM Name) also has 1 additional record where it is specified that FG_BLU (Product Name) is the End Product (Product Type). The Quantity field indicates that 0.1 units of FG_BLU are produced as a result of this BOM. If no End Product is specified in a BOM, by default 1 unit of product is produced when the BOM is used on a Production Policy for that product. Sometimes BOMs are specified in different units though and it may be easier to enter the BOM as is with adding the Quantity for the End Product as in the example here, rather than converting the entire BOM so the quantity of the End Product is 1.

Notes:

• It is possible to set up multiple BOMs with (different quantities of) different components and/or by-products for the same end-product at the same location. The model will then optimize which one(s) to use when. To do so: 1) add all relevant BOMs to the Bills of Materials table, and 2) create multiple Production Policies for the same Facility Name – Product Name combination and use the different BOM Names on them.
• It is possible that products in a model do not only function as components or by-products. For example, there can be situations where they are also used as end products for which there is customer demand in the model, or a by-product is also used as a component into a different BOM.
  • If components are also end-products with customer demand, the model set up for this is straightforward: add the demand to the customer demand table, and add the correct inventory, sourcing and/or transportation policies so the product can reach the customer(s) that have demand for the component.
  • If by-products are also end-products with customer demand, the model set up is somewhat more involved and an example will be discussed in the addendum at the end of this help article.
  • If a product is both used as a component and a by-product on different BOMs, this is no problem, and it can just be set to either Product Type as needed.

Processes

On the Processes table production processes of varying levels of complexity can be set up, from simple 1 step processes without using any work centers, to multi-step ones that specify costs, processing rates, and use different work centers for each step. The processes specified in the Multi-Year Capacity Planning model are relatively straightforward:

1. We are on the Processes input table.
2. Process Name: specify a unique name for the process here. If a process consists of multiple steps, then multiple records (one for each step) will be set up, which all use the same Process Name. A systematic naming convention for processes that takes into account whether processes are product, location, and/or work center specific is recommended. Here, based on the name we can tell that it is a process at the existing production line at PLT_1, which makes FG_SWI (Swiss cheese).
3. Step Name: specify a step name here that is unique within this process (i.e. a Step Name cannot repeat within the same process, but different processes can have the same Step Name as one of their steps). Here, all specified processes have only 1 step, named Production.
4. Step Number: the position of the specified step in the process sequence. As these are all single-step sequences, Step Number is set to 1 for all records.
5. Status: set whether the Process Step should be used (Status = Include) or ignored (Status = Exclude) during an optimization run.
6. Work Center Name: if a work center is used as part of this process step, then associate it here.
7. Unit Cost & Unit Cost UOM: the cost per quantity, volume, or weight of this process. Here set to 0.01 EA, meaning producing 1 unit of product using this process costs 1 eurocent.
8. Here the Notes field is used to indicate which finished good is produced by this process step. This can be used to easily filter the table and set up scenario items that make specific changes based on the finished good.
9. These 2 records are 2 processes to make FG_SWI at the second plant PLT_2, 1 record for production on the existing line and 1 for production on the potential new line.

Let us also look at an example in a different model which contains somewhat more complex processes for a car manufacturer where the production process can roughly be divided into 3 steps:

1. We are again on the Processes input table, although in a different model as compared to the screenshots so far.
2. We see that the Process named SUV1_Detroit_1 has 3 steps associated with it: Welding (step 1), Assembly (step 2), and Painting (step 3)
3. Each step uses a different work center specific to that step, and each step also has a different Processing Rate. For example, the Welding step (step 1) utilizes the Detroit_Welding_1 work center, which can process 60 cars (EA = eaches) per day. This means 24 minutes per car. The rate limiting step on this process is the Assembly step with 36 cars per day (= 40 minutes per car).
4. There is another process specified for producing SUV1 at the Detroit plant, SUV1_Detroit_2, in the bottom 3 records of the screenshot. All 3 records are the same as for the SUV1_Detroit_1 process, except that the Assembly step (step 2) is done on a different work center, Detroit_Assembly_2. In this example, this work center is considered to be added to the Detroit Plant as demand increases and the assembly step is the rate limiting one.

Note that, like BOMs, Processes can in theory be both location and end-product agnostic. However:

• Often the process name suggests that it is specific for making a certain product at a certain location, but only associating these Processes on a Production Policy which has the specific Facility and Product Names makes these connections.
• Processes that use work centers need to be associated on production policies which have the same facility name as the work center of that process. For example:
  • A Work Center named PLT1_Line1 is located at PLT_1 (per the Facility Name field on the Work Centers table which will be discussed in the next section)
  • This Work Center PLT1_Line1 is specified as the Work Center used by a Process named PLT1_Line1_Stamping.
  • If this PLT1_Line1_Stamping process is associated with a Production Policy where the Facility Name is not PLT_1, this production policy will be ignored.

Other fields on the Processes table that are not shown in the above 2 screenshots are:

• Fixed Time with its Fixed Time UOM field: use these to incorporate changeover times into processes. In each period of the model’s horizon where the process step is used, this fixed time will be applied once.
• Fixed Cost: use this to incorporate changeover costs into processes. In each period of the model’s horizon where the process step is used, this fixed cost will be applied once.

Work Centers

If it is important to capture costs and/or capacities of equipment like production lines, tools, machines that are used in the production process, these can be modelled by using work centers to represent the equipment:

1. We are on the Work Centers input table.
2. Each Work Center that is being modelled needs to have a unique Work Center Name, and the facility where it is located needs to be specified in the Facility Name field. If a Facility group is used in the Facility Name field, then the same Work Center is created at each of the facilities in the group.
3. The Work Center Status can be set to Open, Closed or Consider:
   1. Open means that the work center is part of the network for the duration of the model.
   2. Closed means that the work center will not be used for the duration of the model.
   3. Consider – the behavior depends on what Initial State is set to. If Initial State = Existing, then the Work Center is part of the network at the start of the modelling horizon; it can be closed if it is optimal to do so, in which case Fixed Closing Costs will be applied (if specified). If Initial State = Potential, the Work Center is not yet part of the network at the start of the modelling horizon; it can be opened if it is optimal to do so, in which case Fixed Startup Costs will be applied (if specified).

In the above screenshot, 2 work centers are set up at each plant: 1 existing work center and 1 new potential work center. The new work centers (PLT_1_NewLine and PLT_2_NewLine) have Work Center Status set to Closed, so they will not be considered for inclusion in the network when running the Baseline scenario. In some of the scenarios in the model, the Work Center Status of these 2 lines is changed to Consider and in these scenarios one of the new lines or both can be opened and used if it is optimal to do so. The scenario item that makes this change looks like this:

1. The name of the scenario item “Consider new lines”.
2. The table to be changed is selected from the Table Name drop-down, here the Work Centers table is selected.
3. In the Actions box, the change that needs to be made to the selected table is specified. Here the Work Center Status is set to Consider.
4. In the Conditions section we can indicate if the Actions need to be applied to a subset of the records in the selected Table Name by setting a condition. Here it was done by using the Condition Builder: the Actions will only be applied to records that have Initial State set to Potential.

Next, we will also look at a few other fields on the Work Centers table that the Multi-Year Capacity Planning model utilizes:

1. We are still on the Work Centers input table, now showing the Work Center Name frozen on the left and several other fields that were not visible in the screenshot further above.
2. We are focusing on the 2 lines at PLT_1 here, the existing one (PLT_1_Line) and the new potential one (PLT_1_NewLine).
3. Both the existing and new line have their Throughput Capacity set to 90,000 units. Since the model has 5 yearly periods, this means that in each year, each production line can at a maximum produce 90,000 units of product (this is across all products that these lines can produce). They also both have the same Fixed Operating Cost of €20,000, which is applied once for every year the line is used.
4. The potential new line also has a Fixed Startup Cost of €50,000 associated with it. If the model determines it is optimal to start using the new line, this cost will be applied once in the year when the line is first being used.

In theory, it can be optimal for a model to open a considered potential work center in one period of the model (say 2024 in this model), close it again in a later period (e.g. 2025), for it then to open it again later (e.g. 2026), etc. In this case Fixed Startup or Fixed Closing Costs would be applied each time the work center was opened or closed, respectively. This type of behavior can be undesirable and is by default prevented by a Neo Run Parameter called “Open Close At Most Once”, as shown in this screenshot:

After clicking on the Run button, the Run screen comes up. The “Open Close At Most Once” parameter can be found in the Neo (Optimization) Parameters section. By default, it is turned on, meaning that a work center or facility is only allowed to change state once during the model’s horizon, i.e. once from closed to open if the Initial State = Potential or once from open to closed if the Initial State = Existing. There may however be situations where opening and/or closing of work centers and facilities multiple times during the model horizon is allowable. In that case, the Open Close At Most Once parameter can be turned off.

‍

Other fields on the Work Centers table that are not shown in the above screenshots are:

• Fixed Closing Cost: if a Work Center with Work Center Status = Consider and Initial State = Existing is closed during the model horizon, this cost is applied in the period(s) where it is closed.
• Minimum Throughput with its Minimum Throughput UOM field: the minimum amount of product or time the work center needs to produce / be used during each period of the model’s horizon. For example, if this is set to 80 DAY in a model with 5 yearly periods, it means that the work center needs to be used at least 80 full days (e.g. 80*24 = 1,920 hours) during each of those 5 years.

Fixed Operating, Fixed Startup, and Fixed Closing Costs can be stepped costs. These can be entered into the fields on the Work Centers input table directly or can be specified on the Step Costs input table and then used on the Work Centers table in those cost fields. An example of stepped costs set up in the Step Costs input table is shown in the screenshot below where the costs are set up to capture the weekly shift cost for 1 person (note that these stepped costs are not in the Multi-Year Capacity Planning model in the Resource Library, they are shown here as an additional example):

• The first shift of 8 hours every day of the week costs €15 per hour, so total cost for those 7*8 = 56 hours is €15*56 = €840.
• A second 8-hour shift can be added on weekdays for a cost of €25 per hour, so total cost for those 5*8 = 40 hours is €25*40 = €1,000, making the total costs of the 2 shifts together €840 + €1,000 = €1,840.
• This makes the maximum weekly capacity of the work center 56 + 40 hours, which can be set as the Throughput Capacity on the Work Centers table (Throughput Capacity = 96, Throughput Capacity UOM = HR).

1. We are on the Step Costs input table.
2. The multiple steps of a cost function need to have the same Step Cost Name, here the stepped cost function consists of 2 steps and it is called WC_Shifts. The Step Cost Behavior can be Stepwise, as is applicable here (e.g. from a certain lower throughput level to a certain higher throughput level the cost stays the same) or it can be either Incremental or All Item where unit costs change based on the level of throughput with either incremental discounts (only applied to units over the throughput level) or all item discounts (discounts applied to all units once a certain threshold throughput level is reached).
3. The first step of the step cost function is set to start from 0 throughput (Throughput Level) as measured in hours (Throughput Level UOM) and costs €840 (as calculated above, the cost of the first shift of 8 hours/day every day of the week). The second step of the step cost function starts from 56 hours and the total cost between 56 and 96 hours (the maximum throughput capacity) is €1,840 (also calculated above).

To set for example the Fixed Operating Cost to use this stepped cost, type “WC_Shifts” into the Fixed Operating Cost field on the Work Centers input table.

Multi-time Period Production Input Tables

Many of the input tables in Cosmic Frog have a Multi-Time Period equivalent, which can be used in models that have more than 1 period. These tables enable users to make changes that only apply to specific periods of the model. For example, to:

• Increase costs & prices over time.
• Increase capacities for planned expansions that are coming online.
• Decrease capacities for planned downtime/closures.
• Use different BOMs during different periods of the modelling horizon.

The multi-time period tables are copies of their single-period equivalents, with a few columns added and removed (we will see examples of these in screenshots further below):

1. A Period Name field is added to indicate in which period of the model the change as compared to the single-period table should be applied: the record in the multi-time period table for the specified period(s) essentially overrides the matching record in the single-period table. If a model has 5 periods and a multi-time period input table contains a record for period 4 and none for periods 1-3 and 5, then the single-time period input table is used for periods 1-3 and 5 and the record from the multi-time period input table for period 4.
2. Additional Status fields (e.g. Work Center Status or Process Step Status) to indicate whether or not the policy exists in the specified period. For example, say there is a policy in the Work Centers single-time period input table for Line1_Detroit with Status = Include. This work center has planned downtime in the 3^rd period of the model, so a record for this work center is added to the Work Centers multi-time period input table where Period Name is set to the name of the 3^rd period and the Work Center Status is set to Exclude. This way the work center is included in all periods of the model, except the 3^rd.
3. A few columns that cannot be changed on a period-by-period basis have been removed from the multi-time period input tables as compared to their single-period equivalents. Examples of these include the latitude and longitude fields on the Customers, Facilities, and Suppliers input tables.

Notes on switching status of records through the multi-period tables and updating records partially:

• It is important to note that changing the status of a record in a single-period table for specific periods using the multi-period table only works properly if all the key fields of the 2 tables are set to the same values. Records on the multi-period table which do not have records that have the same values for the key fields on the single-period table will be ignored. Key fields are easily recognized in the Cosmic Frog tables, as they have a key icon in front of the column name and the column name is in bold font. The Work Centers table has 2 key fields for example: Work Center Name and Facility Name, which is pointed out in the next screenshot. From the screenshot it is also clear that Status and Work Center Status are not key fields:

• When updating values of non-key fields for specific periods through a multi-time period table, it is again important that the records on the single- and multi-period table have the same values for the key fields. However, for the non-key fields that are being updated, only those fields that need to be changed during specific periods need to be populated with the changed numbers on the multi-period table. For non-key fields that have a value in the single-period table and are left blank on the multi-period, the value from the single-period table will be used.

Three of the 4 production specific input tables that have been discussed above have a multi-time period equivalent: Production Policies, Processes, and Work Centers. There is no equivalent for the Bills Of Materials input table, as BOMs are only used if they are associated on records in the Production Policies table. Using different BOMs during different periods can be achieved by associating those BOMs on the Production Policies single-period table and setting the Status of them to Include for those to be used for most of the periods and to Exclude if they are to be included for certain periods / scenarios. Then add those records for which the Status needs to be switched to the Production Policies multi-period input table (we will walk through an example of this using screenshots in the next section).

The 3 production specific multi-time period input tables do have all of the same fields as their single-period equivalents, with the addition of the Period Name field and additional Status field. We will not discuss each multi-time period table and all its fields in detail here, but rather give a few examples of how each can be used.

Note that from this point onwards the Multi-Year Capacity Planning model was modified and added to for purposes of this help article, the version in the Resource Library does not contain the same data in the Multi-Time Period input tables and production specific Constraint tables that is shown in the screenshots below.

Production Policies Multi-Time Period

This first example on the Production Policies Multi-Time Period input table shows how the production of the cheddar finished good (FG_CHE) is prevented at plant 1 (PLT_1) in years 4 and 5 of the model:

1. We are on the Production Policies Multi-Time Period input table
2. Four records are set up and these are all for the cheddar finished good (FG_CHE) at PLT_1 – note that the values of the key fields of these (Facility Name, Product Name, BOM Name, and Process Name) match those of records on the Production Policies single-period input table.
3. The first 2 records are for the period named YEAR4 and the last 2 for the period named YEAR5.
4. The BOM Name field is set to the BOM that makes the cheddar finished good (BOM_FG_CHE) and the Process Name field indicates the 2 process options for FG_CHE at PLT_1: the process for FG_CHE on the existing line and the process for FG_CHE on the potential new line.
5. The Status field reflects if this record of the Production Policies Multi-Time Period model is to be used (included) or ignored (excluded) when the model is run. These 4 records are all set to include, so they will be used when the model is optimized.
6. The Policy Status field indicates if the policy that has been set up here and exists in the Production Policies Single-Period table should be included or excluded during the specified Period Name. Here, all 4 are set to Exclude, meaning that together these 4 records ensure that no FG_CHE can be made at PLT_1 during periods YEAR4 and YEAR5.

In the following example, an alternative BOM to make feta (FG_FET) is added and set to be used at Plant 2 (PLT_2) during all periods instead of the original BOM. This is set up to be used in a scenario, so the original records need to be kept intact for the Baseline and other scenarios. To set this up, we need to update the Bills Of Materials, Production Policies, and Production Policies Multi-Time Period table, see the following screenshots and explanations:

On the Bills Of Materials input table, all we need to do is add the records for the new BOM that results in FG_FET. It has 2 records, both named ALTBOM_FG_FET, and instead of using only BULK_FET as the component which is what the original BOM uses, it uses a mix of BULK_FET and BULK_BLU as its components.

‍

Next, we first need to associate this new BOM through the Production Policies table:

1. Two new records at PLT_2 to make FG_FET using the new BOM (ALTBOM_FG_FET) are added to the Production Policies table. We need 2 records so that the new BOM can be used on both the existing production line and the new potential production line at PLT_2 (see the Process Name field).
2. The Status of these 2 new records is set to Exclude, so when the Baseline or any already existing scenarios are run, these are not included.

Lastly, the records that need to be added to the Production Policies Multi-Time Period table are the following 4 which have all the same values for the key columns as the 4 records in the above screenshot of the Production Policies single-period input table, which contain all the possible ways to produce FG_FET at PLT_2:

1. We are on the Production Policies Multi-Time Period input table.
2. As mentioned, this is for making FG_FET (Product Name) at PLT_2 (Facility Name).
3. In this case, we want to change to using the alternative BOM for FG_FET at PLT_2 during the whole modelling horizon, so for Period Name a period group named ALL_PERIODS is used. This group contains all 5 periods that exist in this model (see Groups input table).
4. There are 2 records for each of the 2 possible processes to make Feta at Plant 2, the top 2 records are for the existing production line.
5. The BOM Name of the first record is set to BOM_FG_FET which is the original BOM that we do not want to use anymore. The second record has the BOM Name set to the new BOM ALTBOM_FG_FET which we do want to use for the specific scenario we are creating.
6. To make this change from the original BOM to the alternative BOM, the Policy Status of the first record will be set to Exclude and that of the second record will be set to Include.
7. The Status of these records in this multi-time period table is set to Exclude, meaning that if we run any other scenarios, these records will be ignored and the original BOM (BOM_FG_FET) for FG_FET at PLT_2 will be used. This Status field is changed to Include through a scenario item in the scenario(s) where this change from original to alternative BOM for FG_FET needs to be made (see next screenshot).
8. The bottom 2 records are for the new potential production line at PLT_2 and make the same change from original BOM to alternative BOM by switching the Policy Status fields. These records again also have their Status set to Exclude, which is swapped to Include through a scenario item for the scenario(s) in which the new BOM needs to be used:

1. This scenario item is named “Alternative_Feta_BOM”.
2. The table that is being changed is the Production Policies Multi-Time Period table.
3. The change that is made is to set the value of the Status field to Inlcude.
4. The records that this change is made on are those where the Product Name is equal to FG_FET.

Processes Multi-Time Period

In the following example, we want to change the unit cost on 2 of the processes: at Plant 1 (PLT_1), the cost on the new potential line needs to be decreased to 0.005 for cheddar cheese (CHE) and increased to 0.015 for Swiss cheese (SWI). This can be done by using the Processes Multi-Time Period input table:

1. These records are for the processes that make cheddar and Swiss cheese on the new potential line at PLT_1. The Process Name and Step Name fields are the key fields on this table, and their values match those of records on the Processes single-period table.
2. We only want to change the costs of these processes, which we do by setting the Unit Cost field to 0.005 for CHE and 0.015 for SWI.
3. These changes need to be applied to all 5 periods of the model so the period group ALL_PERIODS is used as the Period Name.
4. By setting the Status of these 2 records to Include, these changes will be made to all scenarios, including the Baseline.

Note that there is also a Work Center Name field on the Processes Multi-Time Period table (not shown in the screenshot). As this is not a key field on the Processes input tables, it can be left blank here on the multi-time period table. This field will not be changed and the value from the single-time period table Work Center Name field will be used for these 2 records.

Work Centers Multi-Time Period

In the following example, we want to evaluate if upgrading the existing production lines at both plants from the 3^rd year of the modelling horizon onwards, so they have a higher throughput capacity at a somewhat higher fixed operating cost, is a good alternative to opening one of the potential new lines at either plant. First, we add a new periods group to the model to set this up:

On the Groups table, we set up a new group named YEARS3-5 (Group Name) that is of Group Type = Periods and has 3 members: YEAR3, YEAR4 and YEAR5 (Member Name).

1. We are on the Work Centers Multi-Time Period input table.
2. The first record is for the Work Center called PLT_1_Line (Work Center Name) at PLT_1 (Facility Name). The new group for years 3-5 of this 5 year model is used as the Period Name to make this change for just those 3 years.
3. The changes that are being made are to set the Throughput Capacity to 110,000 (from 90,000) and the Fixed Operating Cost to €22,000 (from €20,000). Note that the Throughput Capacity UOM field is not set on this table (left blank, not shown in screenshot), meaning that the UOM set in the single-period table (EA for eaches) still applies.
4. The Status of this record is set to Exclude, it is switched to Include through a scenario item in the scenario(s) that we want to include these changes in while leaving the other scenario(s) and Baseline as they are.
5. The second record is set up in the same way, except it is for the existing line PLT_2_Line at plant PLT_2.

Constraint Input Tables related to Production

Cosmic Frog contains multiple tables through which different types of constraints can be added to network optimization (Neo) models. A constraint limits the model in a certain part of the network. These limits can for example be lower or upper limits in terms of the amount of flow between certain locations or certain echelons, the amount of inventory of a certain product or product group at a specific location or network wide, the amount of production of a certain product or product group at a specific location or network wide, etc. In this section the 3 constraints tables that are production specific will be covered: Production Constraints, Production Count Constraints, and Work Center Count Constraints.

A couple of general notes on all constraints tables:

1. The fields which specify facilities, products, periods, BOMs, work centers, processes, etc. (the “… Name” fields) almost all allow the use of groups. If a group is used, a “… Group Behavior” field (e.g. “Facility Name Group Behavior, Period Name Group Behavior, etc.) that is placed to the right of the name field is used to indicate how to interpret the constraint for this group. If the Group Behavior field is set to Enumerate (the default for all Group Behavior fields), the constraint applies to each member of the group individually; if the Group Behavior field is set to Aggregate, the constraint applies to all members of the group together. In the below explanations of the production related constraint input tables, not all individual Group Behavior fields are discussed, since they all work this same way.
2. These same Name fields (Facility Name, Product Name, Period Name, etc.) can all generally be left blank, in which case the default of ALL will be used, e.g. meaning all included Facilities in the model, all included Products in the model, etc.

Production Constraints

In this example, we want to add constraints to the model that limit the production of all 5 finished goods together to 90,000 units. Both plants have this same upper production limit across the finished goods, and the limit applies to each year of the modelling horizon (5 yearly periods).

1. We are on the Production Constraints input table.
2. The first record is set up for PLT_1 (Facility Name), one of the 2 plants in the model.
3. The ALL_FG_GROUP product group which consists of the 5 cheese finished goods is used for the Product Name. As the Product Name Group Behavior field is set to Aggregate, the constraint that is specified here is applied over these 5 finished goods together.
4. The ALL_PERIODS period group which consists of the 5 yearly periods that the model contains is used for the Period Name. Since the Period Name Group Behavior field is set to Enumerate the constraint applies to each individual period.
5. The Constraint Type field is set to Max which indicates that this constraint sets an upper limit (production needs to be less than or equal to the Constraint Value). The upper limit itself is set by the Constraint Value field and its accompanying UOM field: 90,000 eaches. Other options for Constraint Type are:
   1. Min – sets a lower limit, i.e. the production needs to be equal to or greater than the Constraint Value.
   2. Fixed – sets a fixed limit, i.e. the production needs to be equal to the Constraint Value.
   3. Fixed With Tolerance – sets a fixed limit with some leeway in either direction: the production needs to be equal to the Constraint Value * (1 +/- Relative Constraint Tolerance), where Relative Constraint Tolerance is set as a percentage in the Neo (Optimization) Parameters section on the Run screen, with a default value of 0.02.
   4. Conditional Min – production needs to be either 0 or equal to or greater than the Constraint Value

Note that there are more fields on the Production Constraints input table which are not shown in the above screenshot. These are:

• BOM Name and its Group Behavior field: if the constraint should apply to a BOM or group of BOMs specifically, use these fields to indicate to which BOM(s) and, if applicable, what the group behavior should be.
• Process Name and its Group Behavior field: if the constraint should apply to a Process or group of Processes specifically, use these fields to indicate to which Process(es) and, if applicable, what the group behavior should be.

Production Count Constraints

In this example, we want to limit the number of products that are produced at PLT_1 to a maximum of 3 (out of the 5 finished goods). This limit applies over the whole 5-year modelling period, meaning that in total PLT_1 can produce no more than 3 finished goods:

1. We are on the Production Count Constraints input table.
2. This constraint applies to PLT_1 (Facility Name).
3. The Product Name field uses the ALL_FG_GROUP product group, which consists of the 5 finished good cheeses. Since the Group Behavior field is set to Aggregate, the constraint applies to all finished good cheeses together.
4. The Period Name field uses the ALL_PERIODS period group, which consists of the 5 yearly periods. Since the Group Behavior field is set to Aggregate, the constraint applies to all periods together (i.e. to the whole modelling horizon).
5. The Max value for Constraint Type, the Constraint Value of 3, and the Counting Rule being set to Products together specify that a maximum of 3 products is allowed to be produced. Possible entities to count on for the Counting Rule are: Facilities, Products, BOMs, Processes, and Periods. Here, since the Counting Rule is set to Products, it means “count the number of unique products for which production occurs for the specified facility-BOM-Process-Period combination”. If it were set to Period it would mean “count the number of unique periods for which production occurs for the specified facility-product-BOM-Process combination”, etc.

Again, note there are more fields on the Production Count Constraints input table which are not shown in the above screenshot. These are:

• BOM Name and its Group Behavior field: if the constraint should apply to a BOM or group of BOMs specifically, use these fields to indicate to which BOM(s) and, if applicable, what the group behavior should be.
• Process Name and its Group Behavior field: if the constraint should apply to a Process or group of Processes specifically, use these fields to indicate to which Process(es) and, if applicable, what the group behavior should be.

Work Center Count Constraints

Next, we will show an example of how to open at least 3 work centers, but no more than 5 out of 8 candidate work centers. These limits apply to all 5 yearly periods in the model together and over all facilities present in the model.

1. We are on the Work Center Count Constraints input table.
2. The Work Center Name is set to a group of work centers which contains 8 new potential Work Centers (these Work Centers all need to have Status = Consider and Initial State = Potential on the Work Centers table). As the Group Behavior is set to Aggregate, the constraint applies to all 8 work centers together.
3. The constraint applies to all 5 yearly periods in the model together.
4. The first record sets the lower limit by using Constraint Type = Min. As the Constraint Value = 3 and Counting Rule = Work Centers, this means at least 3 Work Centers need to be opened and used during the modelling horizon. Other options for Counting Rule are Facilities and Periods.
5. The second record has the same values except that is sets the number of work centers to be opened and used to Max = 5: an upper limit of 5 out of the 8 candidate work centers.

Again, there are more fields on the Work Center Count Constraints table that are not shown in the above screenshot:

• Facility Name and its Group Behavior field: if the constraint should apply to a facility or group of facilities specifically, use these fields to indicate to which facility(/facilities) and, if applicable, what the group behavior should be.

Production Output Tables

After running a network optimization using Cosmic Frog’s Neo technology, production specific outputs can be found in several of the more general output tables, like the Optimization Network Summary, and the Optimization Constraints Summary (if any constraints were applied). Outputs more focused on just production can be found in 4 production specific output tables: the Optimization Production Summary, the Optimization Bills Of Material Summary, the Optimization Process Summary, and the Optimization Work Center Summary. We will cover these tables here, starting with the Optimization Network Summary.

Optimization Network Summary

The following screenshot shows the production specific outputs that are contained in the Optimization Network Summary output table:

1. We are on the Optimization Network Summary output table.
2. As all output tables, the first field on the Optimization Network Summary output table is the Scenario Name field, indicating for which scenario the outputs of the record are.
3. This subset of the costs fields on this output table are partially or entirely the result of production:
   1. Total Production Cost: the total production costs of this scenario as resulting from the Unit Cost set on the Production Policies input table.
   2. Total Fixed Operating Cost: this is the total of fixed operating costs for facilities and work centers. The inputs for these are specified on the Facilities and Work Centers input tables.
   3. Total Fixed Startup Cost: this is the total of fixed startup costs for facilities and work centers. The inputs for these are specified on the Facilities and Work Centers input tables.
   4. Total Fixed Closing Cost: this is the total of fixed closing costs for facilities and work centers. The inputs for these are specified on the Facilities and Work Centers input tables.
   5. Total Process Cost: this is the total of the variable and fixed costs associated with processes, resulting from the Unit Cost and Fixed Cost fields on the Processes input table.

Other production related fields on this table which are not shown in the screenshot above are:

• Total CO2 Emissions: this is the total of all CO2 emissions in the scenario. This includes for example facility and transportation related CO2 emissions. The production related CO2 emissions that are part of this total are the result of the CO2 Emission Rate field on the Production Policies input table.
• Total CO2 Cost: the total cost associated with the Total CO2 Emissions in this scenario. Costs are calculated based on the CO2 Cost field in the Model Settings input table.

Optimization Production Summary

The Optimization Production Summary output table has a record with the production details for each product that was produced as part of the model run:

1. We are on the Optimization Production Summary output table.
2. The first several columns indicate the Scenario Name, the Starting Period Name of the production, the Completion Period Name of the production (not shown in screenshot), the Facility Name where the production takes place and the Product Name of the product being produced.
3. If a BOM was used for the production, the BOM Name will be populated. If it contains the word “none” it means no BOM was used for the production.
4. If a Process was used for the production, the Process Name will be populated. If it contains the word “none” it means no Process was used for the production.
5. The Production Quantity field and its UOM field (not shown in screenshot) indicate the amount of this product produced at this facility in this scenario, during these Starting and Completion periods, using specified BOM (if any) and Process (if any).
6. The costs associated with the production are listed in the next 2 fields: Production Cost is the result of the Unit Cost field on the Production Policies input table, and Process Cost is the result of the Unit Cost and Fixed Cost fields on the Processes input table.

Other fields on this output table which are not shown in the screenshot are:

• Production Volume, Production Weight, and Production Time, together with their UOM fields to summarize the production amount in several units of measure.
• CO2 Emissions and CO2 Cost: the CO2 Emissions are the result of the CO2 Emission Rate set on the Production Policies input table; the CO2 Cost results from the emissions multiplied by the CO2 Cost set on the Model Settings input table.
• Latitude and Longitude: the coordinates of the facility at which the production occurs.

Optimization Bills Of Material Summary

The details of how many components were used and how much by-product produced as a result of any bills of materials that were used as part of the production process can be found on the Optimization Bills Of Material Summary output table:

1. We are on the Optimization Bills Of Material Summary output table, filtered to look at BOMs for Feta cheese in period YEAR3 in the Sc2_Consider_New_LINES scenario.
2. The first few fields indicate the name of the scenario (Scenario Name = Sc2_Consider_New_LINES) in which this BOM was used, the name of the period the BOM was used (Period Name = YEAR3), the name of the BOM (BOM Name) used – here BOM_BULK_FET which is used to make the bulk material BULK_FET, and the name of the facility at which the BOM was used (Facility Name = PLT_1). For these 3 records, these are all the same.
3. The next 3 fields indicate the names of the products used as part of this BOM_BULK_FET BOM, here those of the 3 raw materials, which are used as Components (Product Type) in this BOM. How much of each component is used is listed in the BOM Quantity field. The other value for Product Type can be Byproduct if any are specified for a BOM on the Bills of Materials input table.
4. This record uses the BOM_FG_FET at PLT_1 in the same scenario and period as the first 3 records. The component here is BULK_FET and 24,102 units of it are used.

Note that aside from possibly knowing based on the BOM Name, it is not listed in the Bills Of Material Summary output table what the end product is and how much of it is produced as a result of a BOM. Those details are contained in the Optimization Production Summary output table discussed above.

‍

Other fields on this output table which are not shown in the screenshot are:

• BOM Volume, and BOM Weight, together with their UOM fields to summarize the production amount in several units of measure.
• Latitude and Longitude: the coordinates of the facility at which the BOM was used for production.

Optimization Process Summary

The details of all the steps of any processes used as part of the production in the Neo network optimization run can be found in the Optimization Process Summary, see these next 2 screenshots:

1. We are on the Optimization Process Summary output table.
2. The table is filtered to look at the production of FG_CHE (Product Name), in period YEAR5 (Period Name) of the Sc2_Consider_New_LINES scenario (Scenario Name).
3. The first record is for production at PLT_1 (Facility Name), using a process named PROC-PLT_1_Line_FG_CHE which tells us that the existing line at PLT_1 was used.
4. The second and third record are for production of FG_CHE at PLT_2 (Facility Name), using 2 different processes: 1 that uses the existing line at PLT_2 and 1 that uses the new potential line at PLT_2.

1. The Current Step Name and Next Step Name fields are also part of the outputs on this table. In this case they are straightforward as all Processes in this model consist of 1 single step that is named “Production”. If the current step is the last step, then the next step is indicated as “end” like we see in this example.
2. If a work center was used for the process step, it is listed in the Work Center Name field.
3. The quantity of this product made using this process at this facility during this period in this scenario and using the specified work center is listed in the Processed Quantity field, while the associated costs can be found in the Process Unit Cost field. These costs are a result of the Unit Cost field on the Processes input table.

Other fields on this output table which are not shown in the screenshots are:

• Process Type: to be used in future when processes can also be associated to other parts of the network.
• Processed Volume, Processed Weight, and Processed Time, together with their UOM fields to summarize the production amount in several units of measure. The Processed Time is the result of the Processing Rate and Fixed Time fields on the Processes input table.
• Process Fixed Cost: the changeover cost of the process step, resulting from the Fixed Cost field on the Processes input table.
• Latitude and Longitude: the coordinates of the facility at which the process was used.

Optimization Work Center Summary

For each Work Center that has its Status set to Include or Consider, a record for each period of the model can be found in the Optimization Work Center Summary output table. It summarizes if the Work Center was used during that period, and, if so, how much and at what cost:

1. We are on the Optimization Work Center Summary output table.
2. The table is filtered for the scenario called “Sc2_Consider_New_LINES” (Scenario Name) and the new potential line at PLT_2 (Facility Name) named “PLT_2_NewLine” (Work Center Name). The Initial State of Potential is repeated from the Work Centers input table.
3. There are 5 records for this work center, 1 for each yearly period in the model.
4. These fields summarize the Initial Status at the start of the period, the Optimized Status at the end of the period, and the amount of throughput in eaches (Throughput Quantity). This Work Center PLT_2_NewLine was Closed at the beginning of period 1 as its Work Center Status = Consider and Initial State = Potential on the Work Centers input table. We see that at the end of the third period (“YEAR3”), the Optimized Status has changed to Open: the work center has started being used in this period, with a throughput of 21,293 units. It stays open and is used in periods 4 and 5 too.

The following screenshot shows a few more output fields on the Optimization Work Center Summary output tables that have non-0 values in this model:

1. The Total Work Center Cost field is the sum of all costs associated with this work center for this period. It is the sum of any Operating Costs, Startup Costs, Closing Costs, and Process Costs.
2. The Operating Costs are the result of the Fixed Operating Cost inputs on the Work Centers input table. If this is a stepped cost, the work center throughput is used to determine which step of the stepped function to use as the cost.
3. The Startup Costs are the result of the Fixed Startup Cost inputs on the Work Centers input table. If this is a stepped cost, the step to use as the cost is determined based on the largest throughput recorded in any of the periods the work center is used after it was initially opened.
4. The Total Process Costs are the result of the Unit Cost and Fixed Cost inputs specified on the Processes input table. If any Process Steps use the specified work center in the specified period, all Unit and Fixed Costs for those steps are summed together and reported here.

Other fields on this output table which are not shown in the screenshots are:

• Closing Cost, which are the result of the Fixed Closing Cost inputs on the Work Centers input table. If this is a stepped cost, the step to use as the cost is determined based on the largest throughput recorded in any of the periods the work center was used before it closed.
• Throughput Volume, Throughput Weight, and Throughput Time, together with their UOM fields to summarize the work center throughput in several units of measure. The Throughput Time is the result of the Processing Rate and Fixed Time fields on the Processes input table if the Work Center is associated on any Processes. If the Work Center is directly associated with a Production Policy, then the Throughput Time is the result of the Production Rate set on the Production Policies input table.
• Throughput Capacity and its UOM field: this repeats the inputs on the Work Centers input table of what the maximum throughput of this Work Center is during this period.
• Throughput Utilization: if a throughput capacity is set, the utilization is calculated as throughput / throughput capacity.

Optimization Constraint Summary

For all constraints in the model, the Optimization Constraint Summary can be a very handy table to check if any constraints are close to their maximum (or minimum, etc.) value to understand where the current and future bottlenecks are and likely will be. The screenshot below shows the outputs on this table for a production constraint that is applied at each of the 3 suppliers, where neither can produce more than 1 million units of RAW_MILK in any 1 year. In the screenshot we specifically look at the Supplier named SUP_3:

1. We are on the Optimization Constraint Summary output table.
2. In this scenario, we are looking at 1 constraint, the name of which is auto generated based on the values of the constraint in the Production Constraints input table. This name is repeated for all 5 records (1 for each yearly period).
3. These fields indicate we are looking at periods YEAR1 – YEAR5 (Period Name) for SUP_3 (Facility Name) and RAW_MILK (Product Name).
4. The Constraint Type and Constraint Value are repeated here, which are set to Max = 1,000,000, i.e. SUP_3 cannot produce more than 1 million units of RAW_MILK in any of the periods of the model. The Constraint Activity field indicates how much RAW_MILK SUP_3 actually produced in each period of the optimization run: in YEAR1-YEAR3 the activity increased from ~850K to ~956K and in YEAR 4 and YEAR5 the activity reached the 1million units limit.

Other fields on this output table which are not shown in the screenshots are:

• Destination Name: the destination of a flow in case of a flow (count) constraint).
• Origin Name: the origin of a flow in case of a flow (count) constraint.
• Mode Name: the mode of a flow in case of a flow (count) constraint.
• Process Name: the process name in case of a production (count) constraint.
• BOM Name: the BOM name in case of a production (count) constraint.
• Work Center Name: the work center name in case of a work center count constraint.
• Slack Percentage: shows how close the constraint is to being binding (e.g. maxing out in case of Type = Max, etc.).
• Constraint Violation: if a model is infeasible and this constraint needed to be relaxed to make it feasible, this field indicates by how much it had to be relaxed.
• Suggested Constraint Value: if the model is infeasible and this constraint needed to be relaxed to make it feasible, this suggested constraint value contains the value the original constraint should be set to in order to make the model feasible.

Other output tables that contain some production related outputs

There are a few other output tables of which the main outputs are not related to production, but still contain several fields that result from productions. These are:

1. Optimization Cost To Serve Summary: the Per Unit Production Cost, Per Unit Process Cost, Per Unit Work Center Fixed Operating Cost, Per Unit Work Center Fixed Startup Cost, Per Unit Work Center Fixed Closing Cost, and Per Unit CO2 Cost outputs are all (partially) the result of production activities in the model.
2. Optimization Facility Summary: the Total Production Cost, Total Process Cost; Total Production Quantity, Total Production Volume, and Total Production Weight, and their UOM fields; Total Production CO2 Emissions, and Total Production CO2 Cost outputs are all the result of production activities in the model.
3. Optimization Facility Cost To Serve Summary: the Per Unit Production Cost, Per Unit Process Cost, Per Unit Work Center Fixed Operating Cost, Per Unit Work Center Fixed Startup Cost, Per Unit Work Center Fixed Closing Cost, and Per Unit CO2 Cost outputs are all (partially) the result of production activities in the model.
4. Optimization CO2 Emissions Summary: the Total CO2 Emissions, Total CO2 Cost, Total Fixed CO2 Emissions, Fixed CO2 Cost, Total Production CO2 Emissions, and Total Production CO2 Cost are all (partially) the result of production activities in the model.

Addendum – Additional Example Use Case Setups

Use Facility Count Constraints to Consider Entire New Plants

In this help article we have covered how to set up alternative Work Centers at existing locations and use the Work Center Status and Initial State fields to evaluate if including these, and from what period onwards if so, will be optimal. We have also covered how Work Center Count Constraints can be used to pick a certain amount of Work Centers to be opened/used from a set of multiple candidates, either at 1 location or multiple. Here we also want to mention that Facility Count Constraints can be used when making decisions at the plant level. Say that based on market growth in a certain region, a manufacturer decides a new plant needs to be built. There are 3 candidate locations for the plant from which the optimal needs to be picked. This can be set up as follows in Cosmic Frog:

• Add the 3 potential plants to the Facilities table:
  • Facility Status = Consider
  • Initial State = Potential
• Set up all the required Production Policies, Processes, Work Centers, and BOMs needed for each potential new plant
  • Note that the Status of all these can be set to Include, they will only be used if the model finds it optimal to start using the new plant these are located at
• Set up the required inventory policies at the potential new plants and the sourcing and transportation policies going into and out of them. Again, the Status of all these can be set to Include. Think for example of the following policies which may need to be added:
  • Inventory policies for raw materials, finished goods, intermediates, and by-products
  • Procurement policies and corresponding transportation policies into the plant for acquiring raw materials
  • Replenishment policies and corresponding transportation policies out of the plant to DCs
• Create a group for the 3 new potential plants in the Groups table, Group Type = Facilities
• Set up a Facility Count Constraint for this group of new potential plants, with most likely following values for certain fields:
  • Facility Name Group Behavior: Aggregate
  • Period Name: group of all periods in the model
  • Period Name Group Behavior: Aggregate
  • Constraint Type = Max or Fixed, Constraint Value = 1
    • When set to Max, the model is also allowed to not use any of the 3 new potential plants at all (if optimal)
    • When set to Fixed, the model has to open 1 of the 3 new potential plants during the modelling horizon
  • Status = Include (or set to Exclude and change to Include through a scenario item)
  • Counting Rule = Facilities

A couple of alternative approaches to this are:

1. Run it as in the above-described approach, but just without the Facility Count Constraint (set Status = Exclude). This way the model is allowed anything from not opening/using any of the new potential plants to opening and using all 3 during the modelling horizon.
2. Set up 3 separate scenarios where in each scenario 1 of the 3 new potential plants is opened (do not use the facility count constraint when using this approach). This way the outputs can be compared to see how different the costs for inclusion of each new potential plant are. Should it for example turn out that the costs of 2 plants are very close to each other, then other factors may determine the final choice of new plant location.

(Flexible) Demand for By-Products

As mentioned above in the section on the Bill Of Materials input table, it is possible to set up a model where there is demand for a product that is the By-product resulting from a BOM. This does require some additional set up, and the below walks through this, while also showcasing how the model can be used to determine how much of any flexible demand for this by-product to fulfill. The screenshots show the set-up of a very simple example model built for this specific purpose.

On the Products table, besides the component (for which there also is demand in this model) that goes into any BOM, we also specify:

1. EndProduct_1, the finished good for which there is demand and which is produced using a BOM. The Unit Price is set to 5, meaning for each unit sold, the revenue is $5.
2. ByProduct_1, this is a by-product that results from the BOM that produces EndProduct_1. There is also flexible demand for this by-product, and for every unit sold, the revenue is $3 as set by its Unit Price.

The demand for the 3 products is set up on the Customer Demand table and we notice that 1) there is demand for the Component, the End Product, and the By-Product, and 2) the Demand Status for ByProduct_1 is set to Consider, which means it does not need to be fulfilled, it will be (partially) fulfilled if it is optimal to do so. (For Component_1 and EndProduct_1 the Demand Status field is left blank, which means the default value of Include will be used.)

The EndProduct_1 is made through a BOM which consumes Component_1 and also make ByProduct_1 as a Byproduct. For this we need to set up a BOM:

1. FG_BOM is the BOM that produces EndProduct_1. It is associated with the production of EndProduct_1 via a Production Policy (see next screenshot below).
2. This second line of FG_BOM tells us that for each unit of EndProduct_1 produced, half a unit of ByProduct_1 is also made.
3. To make it work to have demand for ByProduct_1 in the model, this product needs to have its own Production Policy, and since it is the result of making EndProduct_1, it also needs its own BOM, which is set up here in records 3 and 4, named “BYP_BOM”.
4. We use the EndProduct_1 here and set it as a Byproduct for this BOM, by setting Quantity = 2, we maintain the correct ratios between EndProduct_1 and ByProduct_1. I.e. this BOM (once associated with the ByProduct_1 on the Production Policies tables) says that 2 units of EndPrododuct_1 are made as a result of making 1 unit of ByProduct_1.
5. Again, to maintain the correct ratios, we also need to set that 2 units of Component_1 go into making 1 unit of ByProduct_1 (and 2 units of EndProduct_1 at the same time).

Next, on the Production Policies table, we see that Component_1 can be created without a BOM, and:

1. End_Product_1 is made by using FG_BOM
2. ByProduct_1 is made by using BYP_BOM

In reality, these 2 production policies result in the same consumption of Component_1 and same production amounts of EndProduct_1 and ByProduct_1. Both need to be present however in order to be able to also have demand for ByProduct_1 in the model.

‍

Other model elements that need to be set up are:

• Inventory Policies for Component_1, EndProduct_1, and ByProduct_1 at the locations where they can flow through / be stored. Note that for EndProduct_1 and ByProduct_1 it is recommended to have at least 1 inventory policy with Stocking Site = True at a location the product can get to, the model may otherwise return infeasible (unless the BOM and demand ratios for these products always exactly match each other).
• Sourcing and transportation policies between the nodes of the network, including Customer Fulfillment Policies from the customer facing location(s) into the customer location(s)

Three scenarios were run for this simple example model with the only difference between them the Unit Price for ByProduct_1: Baseline (price of ByProduct_1 = 3), PriceByproduct1 (Unit Price of ByProduct_1 = 1), PriceByproduct2 (Unit Price of ByProduct_1 = 2). Let’s review some of the outputs to understand how this Unit Price affects the fulfillment of the flexible demand for ByProduct_1:

The high-level costs, revenues, profit and served/unserved demand outputs by scenario can be found on the Optimization Network Summary output table:

1. The Baseline scenario with the highest Unit Price for ByProduct_1 has the highest Supply Chain Cost, but also highest Revenue and Total Profit as compared to the other 2 scenarios. All demand is served as the Total Unserved Demand Quantity is 0. As a reminder, demand was 300 units of Component_1, and 100 units each of EndProduct_1 and ByProduct_1.
2. The PricByproduct1 scenario which has the lowest Unit Price for ByProduct_1 has the lowest Supply Chain Cost, and also the lowest Revenue and Total Profit. There are 100 units of Unserved Demand, which is for ByProduct_1 as that is the only product with flexible (considered) demand.
3. The PriceByproduct2 scenario which has Unit Price for ByProduct_1 set to 2, which is in between the value for Unit Price in the other 2 scenarios, also sits in between the other 2 scenarios when we look at Supply Chain Cost, Revenue, and Profit. 50 units of unserved demand means that half of the demand for ByProduct1 was served.

On the Optimization Production Summary output table, we see that all 3 scenarios used BYP_BOM for the production of EndProduct_1 and ByProduct_1, it could have also picked the other BOM (FG_BOM) and the overall results would have been the same.

1. In the Baseline scenario, 100 units of Byproduct_1 have been produced, as it is overall optimal to fulfill all demand for Byproduct_1.
2. In the PriceByproduct1 scenario 50 units of Byproduct_1 have been produced as there is demand for 100 units of EndProduct_1, which is Included (i.e. this demand must be fulfilled). And, based on the BOMs, for each unit of EndProduct_1, half a unit of Byproduct_1 is made too.
3. Same as in the PriceByproduct1 scenario, 50 units of Byproduct_1 have been produced in the PriceByproduct2 scenario as there is demand for 100 units of EndProduct_1, which is Included.

As the Optimization Production Summary only shows the production of the end products, we will also have a look at the Optimization Bills Of Material Summary output table:

1. Since it is optimal to fulfill all 100 units of the flexible demand of Byproduct_1 in the Baseline scenario, this results in production of 200 units of EndProduct_1, even though there is only demand for 100 units of it. In other words: it is more beneficial to overproduce and store 100 units of EndProduct_1 than not fulfilling the flexible demand for Byproduct_1.
2. Since the 100 units of demand for EndProduct_1 have to be fulfilled, the PriceByproduct1 scenario produces that exact amount, but no more as it is not beneficial to produce more to fulfill more of the flexible demand for Byproduct_1 due to the lowered Unit Price.
3. Same as in the PriceByproduct1 scenario, the PriceByproduct2 scenario also produces no more than the 100 units needed to fulfill the demand for EndProduct_1. The lowered Unit Price for Byproduct_1, even though higher than in the PriceByproduct1 scenario, is not high enough to warrant over production of EndProduct_1 in order to fulfill more of the flexible demand for Byproduct_1.

Lastly, we will have a look at the Optimization Inventory Summary output table:

1. In the Baseline scenario, we saw that it was optimal to fulfill all flexible demand for Byproduct_1, which led to over production of EndProduct_1: 200 units while 100 were demanded. The remaining 100 units are ending up in inventory at PLT_1.
2. In the PriceByproduct1 scenario, 50 units of Byproduct_1 were produced as a result of the production of 100 units of EndProduct_1. Because the UnitPrice is so low ($1), it is cheaper to keep these 50 units in Inventory at PLT_1 than to ship them through the DC to the customer to fulfill the customer’s demand for 100 units of Byproduct_1 partially.
3. There is no record for the PriceByproduct2 scenario in this output table, as there is no inventory of any of the products at the end of the optimization run. The Unit Price of Byproduct_1 in this scenario ($2) made it profitable to use the 50 units of it that were produced as a result of needing to serve the demand for EndProduct_1 to partially fulfill the demand the customer has for Byproduct_1.

Note that had the demand for Byproduct_1 been set to Include rather than Consider in this example model, all 3 scenarios would have produced 100 units of it to fulfill the demand, and as a result have produced 200 units of EndProduct_1. 100 of those would have been used to fulfill the demand for EndProduct_1 and the other 100 would have stayed in inventory, like we saw in the Baseline scenario above.

Cosmic Frog’s Integrity Checker

Finding problems with any Cosmic Frog model’s data has just become easier with the release of the Integrity Checker. This tool scans all tables or a selected table in a model and flags any records with potential issues. Field level checks to ensure fields contain the right type of data or a valid value from a drop-down list are included, as are referential integrity checks to ensure the consistency and validity of data relationships across the model’s input tables.

‍

In this documentation we will first cover the Integrity Checker tool’s scope, how to run it, and how to review its results. Next, we will compare the Integrity Checker to other Cosmic Frog data validation tools, and we will wrap up with several tips & tricks to help users make optimal use of the tool.

Integrity Checker Scope

The Integrity Checker extends cell validation and data entry helper capabilities to support users identify a range of issues relating to referential integrity and data types before running a model. The following types of data and referential integrity issues are being checked for when the Integrity Checker is run:

Here, we provide a high-level description for each of these 4 categories; in the appendix at the end of this help center article more details and examples for each type of check are given. From left to right:

1. Numeric: checks that the field contains a number within the expected valid range. Specific checks for this type of issue are:
2. Unit of Measure (UoM): checks that UoM fields contain valid values from the Units of Measure table Symbol column for any of the allowed Unit of Measure Types for that field.
3. Master table: these are referential integrity checks to ensure data relationships between input tables are consistent and valid.
4. Data Type: checks that the type of data that is entered into the field matches the data type of the field.

Running the Integrity Checker

The Integrity Checker can be accessed in two ways while in Cosmic Frog’s Data module: from the pane on the right-hand side that also contains Model Assistant and Scenario Errors or from the Grid drop-down menu. The latter is shown in the next screenshot:

1. We are in Cosmic Frog on the Optilogic platform (optilogic.app).
2. If you are not in the Data module yet, click on the Module Menu icon (the icon with 3 horizontal bars) and select “Data”.
3. From the Grid drop-down menu, user can choose from 2 options to run the Integrity Checker:
   1. Check all tables* in the model.
   2. Check only the selected table, which is the active table showing in the center of Cosmic Frog.

*Please note that in this first version of the Integrity Checker, the Inventory Policies and Inventory Policies Multi-Time Period tables are not included in any checks the Integrity Checker performs. All other tables are.

‍

The second way to access the Integrity Checker is, as mentioned above, from the pane on the right-hand side in Cosmic Frog:

1. This pane on the right-hand side either shows the Model Assistant when the first of the 3 icons (the one with the check mark) is clicked, Scenario Errors when the second icon (with the exclamation mark) is clicked, or the Integrity Checker when the third icon (with the database picture) is clicked. Here the Integrity Checker icon has been clicked, which you can tell by the color of the icon: it is a darker blue than the other 2 icons.
2. When you run the Integrity Checker for the very first time on a model, 2 buttons are available here:
   1. Click on the top button to run the Integrity Checker on all tables in the model (except on the Inventory Policies and Inventory Policies Multi-Time Period tables).
   2. Click on the second button to run the Integrity Checker on the selected table, which is the active table showing in the center of Cosmic Frog, which would be the Products table here.

If the Integrity Checker has been run previously on a model, opening it again will show the previous results and gives user the option to re-run it by clicking on a “Rerun Check” button which we will see in screenshots further below.

‍

After starting the Integrity Checker in one of the 2 ways described above, a message indicating it is starting will appear in the Integrity Checker pane on the right-hand side:

Integrity Checker Results

While the Integrity Checker is running, the status of the run will be continuously updated, while results will be added underneath as checks on individual tables complete. Only tables which have errors in them will be listed in the results.

1. In the top part of the Integrity Checker pane, the status of the run is listed. Here, the run is still in progress (“Running”). In parentheses it tells the user if this is a run on all tables, like it is here, or on an individual table (the “selected table” option). If it is on an individual table, the name of the table is listed in the parentheses. A summary of the results so far is included in this part:
   1. The time and date when the Integrity Checker run started.
   2. The total number of errors found so far.
   3. The number of tables that have errors in them so far.
2. For each table with any errors in it, a card appears in the bottom part of the Integrity Checker. It lists the table name, the number of errors found, and the time and date when this table was last checked. So far, the Integrity Checker found errors in the Products table (1 error) and the Transportation Policies table (3 errors).
3. To stop the Integrity Checker run before it has completed, user can click on the Cancel Check button.

Once the Integrity Checker run is finished, its status changes to Completed:

1. Now the status of the Integrity Checker has changed to Completed after the run has finished. The red exclamation mark icon in front of the status indicates that errors were found by the Integrity Checker. When an Integrity Checker run does not find any errors, this icon will be a green checkmark instead.
2. Besides the errors in the Products and Transportation Policies tables, there is a third table with errors, the Production Constraints table.
3. Users can type into the Search box to filter the list of tables with errors in them down to those containing the search text in their name.
4. Sorting the list of tables with errors in them can be done too: click on the icon with horizontal bars and choose 1 of 3 sorting options:
   1. Default – this will sort the tables in the same order as they are in the Input Tables list.
   2. A-Z – this will sort the tables alphabetically; in ascending order when selected for the first time, in descending order if clicked on a second time.
   3. Checked Time – this will sort tables in the order of when their checks completed from first to last; clicking on this option again will sort in the reverse order from last to first completed check.
5. After addressing the errors identified by an Integrity Checker run, user may want to double-check they have mitigated all the issues. They can then run the Integrity Checker again by clicking on the Rerun Check button.

Users can see the errors identified by the Integrity Checker by clicking on one of the table cards which will open the table and the Integrity Checker Errors table beneath it:

1. User clicked on the Transportation Policies table card in the bottom part of the Integrity Checker where it was listed that this table contains 3 errors, this opens the Transportation Policies table.
2. At the bottom another table named Integrity Checker Errors is opened as well. By default, it is expanded; it can be collapsed by clicking on the down caret button. The button then changes to an up caret one, which will expand the table when clicked on.
3. For each of the errors identified by the Integrity Checker this table contains 1 record describing the error using the following fields:
   1. Relevant Technology: this lists the technology/technologies the error applies to. If you are using the model to run a technology not listed here, you can ignore the error, as the technology does not use the column that has the error. As a reminder, the 5 technologies available in Cosmic Frog and their names are:
      1. Neo – network
      2. Throg – simulation
      3. Hopper – transportation
      4. Dendro – inventory
      5. Triad – Greenfield
   2. Type: what sort of error was found; this can for example be one of the following (for a full list of the error types see the high-level overview in the Integrity Checker Scope section above and for more details and examples see the Appendix – Integrity Checker Scope Details further below):
      1. Required Field – the field is required to have a value, it cannot be empty.
      2. Invalid Enum – the value the column should have can be one of a finite set of options, as listed in the field’s drop-down list when double-clicking in it.
      3. Invalid Reference – the value in the field should exist in another Cosmic Frog Model Elements input table, or as a group or named filter.
      4. Non Negative – the value in the field cannot be negative, it should be greater than or equal to 0.
      5. Positive Numeric – the value in the field cannot be negative or 0, it should be greater than or equal to 1.
   3. Description: explains the type of error and in case the value of the field should be one of a finite number of options (e.g. there is a drop-down list in the column), it lists the valid values.
   4. Column: the column (/field) the error was found in.
   5. Row Count: the number of rows (/records) in the table that have this error in this column.
4. Let us walk through the first error listed for the Transportation Policies table:
   1. The error only applies to the Neo technology (network optimization): if user is planning to run a network optimization on this model, then the error should be addressed. If they are planning to run another technology on this model, they can ignore this error.
   2. The error type is Invalid Enum – the value should be one of a finite number of options but is something different currently.
   3. The description column explains the error in more detail. In this screenshot, the whole description is not visible due to the width of the Description column, but hovering over shows the full text: “Value must be one of: To Optimize, By Ratio (Auto Scale), By Ratio (No Scale)”. In other words, there are 3 valid values for this field, and the current value is not one of these.
   4. Column indicates which column in the Transportation Policies table the error was found in, this is the Optimization Policy column in this case.
   5. The Row Count for this error is 1 – there is 1 record in the transportation policies table that has this error where the Optimization Policy field contains an invalid value.

Clicking on a record in the Integrity Checker Errors table will filter the table above (here the Transportation Policies table) down to the record(s) with that error:

1. We have clicked on the first record in the Integrity Checker Errors table underneath the Transportation Policies table. As covered in the previous screenshot, this error applies to the Neo technology only, and it says that 1 record in the Transportation Policies table has an invalid value in the Optimization Policy column.
2. By clicking on the record in the Integrity Checker Errors table, the Transportation Policies table is filtered for the record(s) with that error. User can recognize this by the label at the right top of the table that the “Integrity Checker” filter is applied, which has a red database icon associated with it. User can take this filter off by clicking on the x on the right of the filter name.
3. We see that indeed 1 of 9,342 rows in this table has been filtered out.
4. In this record, the Optimization Policy field is set to “Not to optimize” which is not one of the three valid values for this field.

User can go through each record in the Integrity Checker Errors table at the bottom and filter out the associated records with the errors in the table above to review the errors and possibly fix them. In the next screenshot, user has moved onto the second record in the Integrity Checker Errors table:

1. User has clicked on the second record in the Integrity Checker Errors table. This error applies to 3 technologies (Neo, Throg, and Dendro) and it is an Invalid Reference type of error in the Destination Name column found in 1 record in the Transportation Policies table. The description explains that the value of this field has to exist in one of 3 master tables (Customers, Facilities, Destinations) or corresponding groups or filters.
2. We see again that an Integrity Checker filter is applied and that this results in 1 record being filtered out in the Transportation Policies table.
3. The Destination Name of this record is set to Ports. Checking the master tables, there are no Customers, Facilities or Suppliers named Ports present in the model. Neither are there any named filters named Ports present in these master tables. Lastly, there also are no groups with a group name of Ports present in the Groups table. Therefore, Ports is not a valid destination for a transportation policy.

We will look at one more error, the one that was found on the Products table:

1. In the Integrity Checker results, user clicks on the Products card.
2. This opens the Products table and the Integrity Checker Errors table beneath it.
3. There is 1 error listed here, which user has clicked on to filter the Products table down to the 1 record that has this Non Negative error in the Unit Price column. The description explains that the value for this field should be larger than or equal to 0. This error applies to the Neo, Throg, and Dendro technologies.
4. Looking at the filtered out record with the error, the Unit Price field contains a value of -1500, which is an invalid value for this column.

Finally, the following screenshot shows what it looks like when the Integrity Checker was run on an individual table and in the case no errors are found:

1. The Integrity Checker was run on an individual table by selecting the “Selected Table” option. The name of the table, Replenishment Policies here, is listed in the parentheses.
2. There were no errors found, which can be seen from:
   1. The green checkmark icon as opposed to the red exclamation mark icon when errors have been found.
   2. Total errors and tables with errors both are 0.
   3. The text “No Validation Errors Found” in the lower part of the Integrity Checker.

Comparison with other Cosmic Frog Data Validation Tools

There are additional tools in Cosmic Frog which can help with finding problems in the model’s data and overall construction, the table below gives an overview of how these tools compare to each other to help users choose the most suitable one for their situation:

Tips & Tricks

Please take note of the following so you can make optimal use of the Integrity Checker capabilities:

1. You do not have to wait for the Integrity Checker tool to complete to start looking at its results. Once a table card has appeared in the results indicating a table has errors, you can click on the card and start reviewing the errors in that table. You can also go to other parts of Cosmic Frog or even out of the model and back in at a later time while the Integrity Checker is running, and the results will be available to you once you get back into the Data module of the model you ran the Integrity Checker on.
2. If the Integrity Checker has been run on a model previously, the next time you open the model, the most recent results of the Integrity Checker are still available to you. Depending on what was run previously (all tables or selected table) and in the case of selected table, which table, running the Integrity Checker again will behave as follows:
   1. If the Integrity Checker was run on all tables previously, then clicking on the “Rerun Check” button will run the Integrity Checker on all tables again. If you want to run the Integrity Checker on 1 selected table, you will need to do it from the Grid drop-down menu.
   2. If the Integrity Checker was run on the selected table previously, then there will be a “Rerun Check” button if the same table is the currently active table and the check will be run on that table again. If a different table is the currently active table, the button will read “Run Check” and clicking on it will run the Integrity Checker on the 1 selected table. If you want to run the Integrity Checker on all tables, you will need to do it from the Grid drop-down menu.
3. The Integrity Checker Errors table has a lot of the same options for configuration as the other Cosmic Frog input and output tables have, such as dragging and dropping the columns to change their order, clicking on column headers to sort by that column, resizing columns, etc. In addition, user can choose certain configuration actions from a context menu:

1. 1. To open the context menu for configuring the table and its columns, click on the icon with 3 dots next to the column you want to configure.
   2. The context menu comes up: select your desired configuration action from the list.
2. Progress of the Integrity Checker and optionally cancelling it, can be done from within the Integrity Checker pane on the right-hand side in Cosmic Frog as we have seen above. In addition, user can also check progress of or cancel Integrity Checker runs, through the Run Manager application on the Optilogic platform:

1. 1. User has opened the Run Manager application.
   2. Two jobs are showing here, both are of Integrity Checker runs in Cosmic Frog:
      1. This integrity checker job has been submitted and is still running. If needed it can be cancelled by right-clicking on it and selecting “Cancel Job”.
      2. This integrity checker job has finished running successfully as indicated by its “done” status.

Appendix – Integrity Checker Scope Details

We saw the next diagram further above in the Integrity Checker Scope section. Here we will expand on each of these categories and provide examples.

From left to right:

1. Numeric: checks that the field contains a number within the expected valid range. Specific checks for this type of issue are:
   1. Non-negative: the field needs to have a value greater than or equal to 0. Example: the Unit Price field on the Products table will be flagged if it contains a numeric value <0.
   2. Positive numeric: the fields need to have a value greater than 0. Example: the Lot Size field on the Processes table will be flagged if it has a value <1.
   3. Percentage: the field needs to have a value between 0 and 100 as it represents a percentage. Example: the Service Band Percentage field on the Greenfield Service Bands table will be flagged if it has a value > 100 or < 0.
   4. Risk scores: a risk score field needs to have a value between 0 and 10. Example: the Risk Score field on the Risk Band Definitions table will be flagged if it has a value <0 or >10.
   5. Latitude: any field that contains a latitude of a location needs to have a value between -90 and 90. Example: the Latitude field on the Customers table will be flagged if it has a value <-90 or >90.
   6. Longitude: any field that contains a longitude of a location needs to have a value between -90 and 90. Example: the Longitude field on the Facilities table will be flagged if it has a value <-90 or >90.
2. Unit of Measure (UoM): checks that UoM fields contain valid values from the Units of Measure table Symbol column for any of the allowed Unit of Measure Types for that field.
   1. Example: valid unit of measure types for the Unit Cost UOM field on the Customer Fulfillment Policies table are: Quantity, Volume, and Weight. So, if a non-existing Unit of Measure or one of a different type is entered here, the field is flagged. If the field contains “MI”, which is a valid Distance type unit of measure, it will be flagged, because it can only accept unit of measure types of Quantity (e.g. EA for each), Volume (e.g. CFT for cubic foot), and Weight (e.g. LB for pound).
   2. Please note that UoM fields can be left blank, in which case it will use the primary UoM of one of the allowed UoM types for the field. Primary UoMs are defined on the Model Settings table. If in the previous example the Unit Cost UoM field on the Customer Fulfillment Policies table is left blank, the Primary Quantity UoM value on the Model Settings table is used (can also see this by hovering over the column name, which shows a tooltip containing a “Default Value” among other details of the field). By default, the Primary Quantity UoM on the Model Settings table is set to EA; users can update this to another valid UoM of Type = Quantity listed on the Units of Measure table.
3. Master table: these are referential integrity checks to ensure data relationships between input tables are consistent and valid. The entities of the supply chain being modeled are entered into the Model Elements input tables (i.e. Master tables) – Customers, Facilities, Suppliers, Products, Periods, and Organizations, and several other model-type specific tables (Processes, Work Centers, Work Resources, Bills of Materials, Transportation Modes, Transportation Assets, and Shipments). In addition to the single entities specified in these tables, they can be grouped together either through the Groups table or by using Named Filters. When members of a group behave the same way in (parts of) the supply chain (e.g. same cost structure, same O-D pairs, etc.) the group’s name / named filter’s name can be used in the other input tables which define the supply chain’s behavior in terms of costs, demand, policies, constraints, etc. instead of having to define a record for each individual member of the group. These master table integrity checks ensure that for fields that reference a master supply chain element the value of that element exists in its master table(s) or as a corresponding group or named filter.
   1. Example: the Product Name field on the Transportation Policies table will be flagged by the Integrity Checker if the value in the field does not match the Product Name of a record on the Products table (“basic referential”), does not match the name of a saved filter on the Products table (“table filter”), and does not match the Group Name of 1 or multiple records on the Groups table (“groups”).
4. Data Type: checks that the type of data that is entered into the field matches the data type of the field. Specific checks for this type of issue are:
   1. Double, integer, string, date/time, time: if the data in the field is not of the specified data type it will be flagged. Example: the Optimization Policy Value field on the Customer Fulfillment Policies table will be flagged if it contains a string (e.g. “ten”) instead of a numeric value (type double) like for example 20.5 or 60.
   2. Required: the field cannot be blank. Example: the Product Name field on the Production Policies table needs to be populated, it will be flagged if it is left blank.
   3. Enums: the field needs to contain a value from a finite list of valid values (i.e. there is a drop-down list available when double-clicking in the field). Example: there are only 2 valid values for the Status field on the Warehousing Policies table – Include and Exclude (leaving blank is fine too, it will then default to use Include); entering for example “Consider” into the field will be flagged as an error.

Note that the numeric and data type checks sound similar, but they are different: a value in a field can pass the data type check (e.g. a double field contains the value -2000), but not the numeric check (a latitude field can only contain values between -90 and 90, so -2000 would be invalid).

‍

We hope you will find the Integrity Checker to be a helpful additional tool to facilitate your model building in Cosmic Frog! For any questions, please contact Optilogic support on support@optilogic.com.

Adding Sourcing Policies via the Sourcing Tables (Simulation)

In a supply chain model, sourcing policies describe how network components create and order necessary materials. In Cosmic Frog, sourcing rules & policies appear in two different table categories:

In this section, we will discuss how to use these Sourcing policy tables to incorporate real-world behavior. In the sourcing policy tables we define 4 different types of sourcing relationships:

• Customer fulfillment policies
• Replenishment policies
• Procurement policies
• Production policies

First we will discuss the options user has for the simulation policy logic used in these 4 tables and the last section covers the other simulation specific fields that can be found on these sourcing policies tables.

Sourcing Policies Tables and their Simulation Policy Options

Customer fulfillment policies

Customer fulfillment policies describe which supply chain elements fulfill customer demand. For a Throg (Simulation) run, there are 3 different policy types that we can select in the “Simulation Policy” column:

• By preference
• Single source
• Allocation

If “By Preference” is selected, we can provide a ranking describing which sites we want to serve customers for different products. We can describe our preference using the “Simulation Policy Value” column.

‍

In the following example we are describing how to serve customer CZ_CA’s demand. For Product_1, we prefer that demand is fulfilled by DC_AZ. If that is not possible, then we prefer DC_IL to fulfill demand. We can provide rankings for each customer and product combination.

Under this policy, the model will source material from the highest ranked site that can completely fill an order. If no sites can completely fill an order, and if partial fulfillment is allowed, the model will partially fill orders from multiple sources in order of their preference.

If “Single Source” is selected, the customer must receive the given product from 1 specific source, 1 of the 3 DCs in this example.

‍

The “Allocation” policy is similar to the “By Preference” policy, in that it sources from sites in order of a preference ranking. The “Allocation” policy, however, does not look to see whether any sites can completely fill an order before doing partial fulfillment. Instead, it will source as much as possible from source 1, followed by source 2, etc. Note that the “Allocation” and “By Preference” policies will only be distinct if partial fulfillment is allowed for the customer/product combination.

Example By Preference vs Allocation

Consider the following example, customer CZ_MA can source the 3 products it puts orders in for from 3 DCs using the By Preference simulation policy. For each product the order of preference is set the same: DC_VA is the top choice, then DC_IL, and DC_AZ is the third (last) choice. Also note that in the Customers table, CZ_MA has been configured so that it is allowed to partially fill orders and line items for this customer.

The first order of the simulation is one that CZ_MA places (screenshot from the Customer Orders table), it orders 20 units of Product_1, 600 units of Product_2, and 160 units of Product_3:

The inventory at the DCs for the products at the time this orders comes in is the same as the initial inventory as this customer order is the first event of the simulation:

When the simulation policy is set to By Preference, we will look to fill the entire order from the highest priority source possible. The first choice is DC_VA, so we check its inventory: it has enough inventory to fill the 20 units of Product_1 (375 units in stock) and the 160 units of Product_3 (500 units in stock), but not enough to fill the 600 units of product_2 (150 units in stock). Since the By Preference policy prefers to single source, it looks at the next priority source, DC_IL. DC_IL does have enough inventory to fulfill the whole order as it has 750 units of Product_1, 1000 units of Product_2, and 300 units of Product_3 in stock.

‍

Now, if we change all the By Preference simulation policies to Allocation via a scenario and run this scenario, the outcomes are different. In this case, as many units as possible are sourced from the first choice DC, DC_VA in this case. This means sourcing 20 units of Product_1, 150 units of Product_2 (all that are in stock), and 160 units of Product_3 from DC_VA. Then next, we look at the second choice source, DC_IL, to see if we can fill the rest of the order that DC_VA cannot fill: the 450 units left of Product_1, which DC_IL does have enough inventory to fill. These differences in sourcing decisions for these 2 scenarios can for example be seen in the Simulation Shipment Report output table:

Replenishment policies

Replenishment policies describe how internal (i.e. non-customer) supply chain elements source material from other internal sources. For example, they might describe how a distribution center gets material from a manufacturing site. They are analogous to customer fulfillment policies, except instead of requiring a customer name, they require a facility name.

Procurement policies

Procurement policies describe how internal (i.e. non-customer) supply chain elements source material from external suppliers. They are analogous to replenishment policies, except instead of using internal sources (e.g. manufacturing sites), they use external suppliers in the Source Name field.

Production policies

Production policies allow us to describe how material is generated within our supply chain.

There are 4 simulation policies regarding production:

• Make by demand – triggers production events based on sourcing policies that pull from the site.
• Make by schedule – the production scheduled is defined in the Production Orders table, where quantities, and order & due dates can be specified. A production pattern can be specified in the Production Order Profiles table.
• Make by demand and schedule – combines the previous 2 policies.
• Continuous production – continuously triggers production orders of the amount specified in the “Lot Size” field as soon as the production of the previous lot is completed.

Other Fields on the Sourcing Policies Tables

Besides setting the Simulation Policy on each of these Sourcing Policies tables, each has several other fields that the Throg Simulation engine uses as well, if populated. All 4 Sourcing Policies tables contain a Unit Cost and a Lot Size field, plus their UOM fields. The following screenshot shows these fields on the Replenishment Policies table:

1. Unit Cost and its UOM field – specifies the variable cost for replenishing this product at this facility from this source. The cost can be a number, a step function, or a custom function.
2. Lot Size and its UOM field – if a Lot Size is set, the facility needs to order integer multiples of this number from the source location.
3. The first record in this table shows that DC_AZ can replenish all products from MFG_CA. The replenishment cost is $2.50 per unit and replenishment orders need to be placed in multiples of 50 units.

The Customer Fulfillment Policies and Replenishment Policies tables both also have an Only Source From Surplus field which can be set to False (default behavior when not set) or True. When set to True, only sources which have available surplus inventory are considered as the source for the customer/facility – product combination. What is considered surplus inventory can be configured using the Surplus fields on the Inventory Policies input table.

‍

Finally, the Production Policies table also has following additional fields:

1. Production Rate, and its Rate Quantity UOM and Rate Time UOM fields – together these can be used to specify how long production of the product at the facility takes. For example, when set to 50, EA, and HR, respectively, the production rate is 50 units per hour (= 60 min / 50 units = 1.2 min per unit).
2. BOM Name and Process Name – if detailed production is modelled by using the Bills Of Materials and/or Processes tables, these can be associated to a facility – product pair through these fields.

Adding Inventory Policies (Simulation)

Inventory policies describe how inventory is managed across facilities in our supply chain. These policies can include how and when to replenish, how stock is picked out of inventory, and many other important rules.

In general, we add inventory policies using the Inventory Policies table in Cosmic Frog.

In this documentation we will cover the types of inventory simulation policies available and also other settings contained in the Inventory Policies table.

Inventory Simulation Policies

(R,Q) inventory policies

An (R,Q) policy is a commonly used inventory management approach. Here, when inventory drops below a value of R units, the policy is to order Q units. In Cosmic Frog, when an (R,Q) policy is selected, we can define R and Q in “SimulationPolicyValue1” and “SimulationPolicyValue2”, respectively. We can define the unit of measure (e.g. pallets, volume, individual units, etc.) for both parameters in their corresponding simulation policy value UOM column.

‍

In the following example, MFG_STL has an (R,Q) inventory policy of (100,1900) for Product_2, measured in terms of individual units (i.e. “each”).

(s,S) and (Min,Max) inventory policies

(s,S) policies are like (R,Q) policies in that they define a reorder point and how much to reorder. In an(s,S) policy, when inventory is below s units, the policy is to “order up to” S units. In other words, if x is the current inventory level, and x < s, the policy is to order (S-x) units of inventory.

‍

In the example below, DC_VA has an (s,S) inventory policy of (150,750) for Product_1. If inventory dips below 150, the policy is to order so that inventory would replenish to 750 units.

(s,S) policies may also be referred to as (Min,Max) policies; both policy names are accepted in the Anura schema and both behave as described above.

(T,S) inventory policies

A (T,S) inventory policy is like an (s,S) inventory policy in that whenever inventory is replenished, it is replenished up to level S. Under an (s,S) inventory policy, we check the inventory level in each period when making reorder decisions. In contrast, under a (T,S) inventory policy, the current inventory level is only checked every T periods. During one of these checks, if the inventory level is below S, then inventory is replenished up to level S.

‍

In the example below, DC_VA manages Product_1 using a (T,S) inventory policy. The DC checks the inventory level every 5 days. If inventory is below 750 units during any of these checks, inventory is replenished up to 750 units.

Do Nothing Inventory Policies

As the name suggests a Do Nothing inventory policy does not trigger any replenishment orders. This policy can for example be used for products that are being phased out or at manufacturing locations where production occurs based on a schedule.

‍

In the example below, MFG_STL uses the Do Nothing inventory policy for the 3 products it manufactures.

Other Fields on the Inventory Policies Table

On the inventory policies table, other fields available to the user to model inventory include those to set initial inventory, how often inventory is reviewed, and the inventory carrying cost percentage:

1. Initial Inventory and its UOM field – indicate how much product is on hand at the facility at the time the simulation starts. Not setting initial inventory will lead to a bunch of replenishment orders being triggered all at the same time at the simulation start.
2. Review Period First Time, and Review Period and its UOM field – these together set when and at what interval inventory is reviewed to determine if and if so how much inventory needs to be re-ordered. Review Period First Time specifies when on the simulation clock, the first inventory review occurs; if left blank the first review is at the start of the simulation. From the first review onward, the review period indicates the interval after which the next review will be. If Review Period is left blank, it means that inventory is monitored continuously.
3. Carrying Cost Percentage – if inventory carrying costs are calculated, the calculation is as follows: time the unit(s) are in stock (days) * product value (set on Products table) * carrying cost percentage / 365 (days in a year). If no carrying cost percentage is set on the Inventory Policies table, the value set in the Model Settings table will be used. Enter 10 for a carrying cost percentage of 10%.
4. The settings for Product_3 at DC_IL are as follows: there is an initial inventory of 300 units, the first review occurs at 10AM on January 6th, 2025 (a Monday), and reviews occur every 7 days. So every Monday at 10AM inventory of product_3 at DC_IL is checked and re-ordered if needed according to the inventory policy logic and values. The carrying cost percentage is set to 12%.

Surplus Level Settings

When Only Source From Surplus is set to True on a customer fulfillment or a replenishment policy, the Surplus fields on the Inventory Policies table can be used to specify what is considered surplus inventory for a facility – product combination:

1. Surplus Level and its UOM field – here user can set the inventory level above which inventory is considered to be surplus. This can be set using units of measure of quantity, weight, and volume (e.g. EA for eaches, LB for pounds, and CFT for cubic foot), but also Days Of Supply (using the DOS symbol for this unit of measure).
2. Surplus Review Period First Time, and Surplus Review Period and its UOM field – these only need to be set in the case of using Push replenishment policies to specify when inventory will be checked to see if there is any surplus. They work similar to the Review Period fields (explained in the previous section): Surplus Review Period First Time specifies when the inventory is first checked for surplus, if left blank this occurs at the simulation start. Surplus Review Period and its UOM field set the interval between each surplus review. For all customer fulfillment policies with Only Source From Surplus = True and Pull replenishment policies with Only Source From Surplus = True, inventory will be checked for surplus at the time the customer/replenishment order occurs and its source is being determined.
3. For Product_3 at DC_IL the Surplus Level is set to 600 units. If for example the inventory on hand = 750 at a given time, it means there are 150 units of surplus available. If at that time a customer fulfillment order with Only Source From Surplus set to True of 200 units for Product_3 comes in, DC_IL can fulfill 150 of those units.

Note that if all inventory needs to be pushed out of a location, Push replenishment policies need to be set up for that location (where the location is the Source), and Surplus Level needs to be set to 0.

Days of Supply (DOS) Settings and Calculations

Inventory Policy Value fields can also be expressed in terms of the number of days of supply to enable the modelling of inventory where the levels go up or down when (forecasted) demand goes up or down. Please see the help center article “Inventory – Days of Supply (Simulation)” to learn more about how this can be set up and the underlying calculations.

Adding Transportation Policies (Simulation)

Transportation policies describe how material flows throughout a supply chain. In Cosmic Frog, we can define our transportation policies using the Transportation Policies (required) and Transportation Modes (optional) tables. The Transportation Policies table will be covered in this documentation. In general, we can have a unique transportation policy for each combination of origin, destination, product, and transport mode.

Typically in simulation models, transportation policies are defined over the group of all products (which can be done by leaving Product Name blank as is done in the screenshot above), unless some products need to be prevented from being combined into shipments together on the same mode. If Transportation Policies list products explicitly, these products will not be combined in shipments.

‍

Here, we will first cover the available transportation policies; other transportation characteristics that can be specified in the Transportation Policies table will be discussed in the sections after.

‍

Available Transportation Simulation Policies

Currently supported transportation simulation policies are:

• On Quantity / On Volume / On Weight
• By Preference
• By Due Date

On Volume / Weight / Quantity

Selecting “On Volume”, “On Weight”, or “On Quantity” as a simulation policy means that either the volume, weight, or quantity of the shipment will determine which transportation mode is selected. In this case, the “Simulation Policy Value” defines the lowest volume that will go by that mode. We can use multiple lines to define multiple breakpoints for this policy.

1. We are on the Transportation Policies table.
2. Origin Name, Destination Name, Product Name (not shown, left blank for all records), and Mode Name – these define a mode of a transportation lane (a lane is an origin-destination-product combination): the rules and other characteristics specified in the record apply to this origin-destination-product-mode combination. Note that Mode Name needs to match a Mode specified in the Transportation Modes table.
3. Simulation Policy and Simulation Policy Value – what the simulation policy and its value field are set to determine how a mode is picked for the origin-destination-product combination, if multiple modes are available. Options for Simulation Policy are: By Preference, By Due Date, On Quantity, On Volume, and On Weight. The latter 3 will be explained in the next bullet points, and the first 2 in the next 2 screenshots.
4. The lane DC_VA to CZ_MA has 3 possible modes (for all products traveling on this lane), W0, W2500 and W7500. Its simulation policy is set to On Weight, with the Simulation Policy Value matching the mode names: 0, 2500, and 7500, respectively. This means that from a weight of 0 pounds, the W0 mode will be used, until a weight of 2500 pounds is reached. From a weight of 2500 pounds, the W2500 mode will be used. And shipments of 7500 pounds and over will use the W7500 mode. Not shown in the screenshot is that the unit cost goes down with increasing weight: $10 per unit for the W0 mode, $5 per unit for the W2500 mode and $2 per unit for the W7500 mode (specified in the Unit Cost and Unit Cost UOM fields further right in the Transportation Policies table). So for each mode a weight fill level (minimum weight that needs to be on the shipment before it is allowed to be dispatched) and a weight capacity (maximum weight allowed on the shipment) are set through the Simulation Policy Values, except there is no capacity set for the highest weight mode (W7500):
   1. W0: weight fill level = 0 (simulation policy value of this On Weight mode) and weight capacity = 2500 (simulation policy value of the next On Weight mode).
   2. W2500: weight fill level = 2500 (simulation policy value of this On Weight mode) and weight capacity = 7500 (simulation policy value of the next On Weight mode).
   3. W7500: weight fill level = 7500 (simulation policy value of this On Weight mode).
5. The lane DC_IL to CZ_OH has 3 possible modes (for all products traveling on this lane), Q0, Q400 and Q1000. Its simulation policy is set to On Quantity, with the Simulation Policy Value matching the mode names: 0, 400, and 1000, respectively. This means that from a quantity of 0 units, the Q0 mode will be used, until a quantity of 400 units is reached. From a quantity of 400 units, the Q400 mode will be used. And shipments of 1000 units and over will use the Q1000 mode. Not shown in the screenshot is that the unit cost goes down with increasing number of units: $10 per unit for the Q0 mode, $5 per unit for the Q400 mode and $2 per unit for the Q1000 mode (specified in the Unit Cost and Unit Cost UOM fields further right in the Transportation Policies table). Similar to what is explained under the previous bullet for On Weight policies, a quantity fill level (minimum quantity that needs to be on the shipment before it is allowed to be dispatched) and a quantity capacity (maximum quantity allowed on the shipment) are set through the Simulation Policy Values, except there is no capacity set for the highest quantity mode (Q1000).
6. The lane DC_AZ to CZ_WA has 3 possible modes (for all products traveling on this lane), V0, V200 and V1000. Its simulation policy is set to On Volume, with the Simulation Policy Value matching the mode names: 0, 200, and 1000, respectively. This means that from a volume of 0 cubic foot, the V0 mode will be used, until a volume of 200 cubic foot is reached. From a volume of 200 cubic foot, the V200 mode will be used. And shipments of 1000 cubic foot and over will use the V1000 mode. Not shown in the screenshot is that the unit cost goes down with increasing volume: $10 per cubic foot for the V0 mode, $5 per cubic foot for the V200 mode and $2 per cubic foot for the V1000 mode (specified in the Unit Cost and Unit Cost UOM fields further right in the Transportation Policies table). Similar to what is explained under bullet 4 for On Weight policies, a volume fill level (minimum volume that needs to be on the shipment before it is allowed to be dispatched) and a volume capacity (maximum volume allowed on the shipment) are set through the Simulation Policy Values, except there is no capacity set for the highest volume mode (V1000).

Please note that:

1. The units of measures for the On Quantity, On Volume, and On Weight simulation policies are fixed to be units, cubic foot, and pounds and cannot be changed by the user currently.
2. The Transportation Modes table can be used to set fill levels and capacities for individual modes. When using these On Volume / Weight / Quantity policies, fill levels and capacities are set by the simulation policy values as explained above, so if also using the Transportation Modes table to specify these for these specific modes, user should take note of the effects this can have which are explained in the Transportation Modes documentation.
3. The capacity of the highest on weight / volume / quantity mode (the mode with the highest simulation policy value) is not set when using these simulation policies as described above, but if desired can be set by using the Lane Capacity / Lane Mode Capacity fields described in the Lane and Mode Capacities section further below or on the Transportation Modes table.

By Preference

If “By Preference” is selected, we can provide a ranking describing which transportation mode we want to use for different origin-destination-product combinations. We can describe our preference using the “Simulation Policy Value” column.

This screenshot shows that all MFG to DC transportation lanes only have 1 Mode of Container and the Simulation Policy is set to By Preference for all of them. If there are multiple Modes available, the By Preference policy will select them pending availability in the order of preference specified by the Simulation Policy Value field, the lowest value being the most preferred mode. If there were 2 modes available and the policy set to By Preference, where 1 mode has a simulation policy value of 1 and the other of 2, the Mode with simulation policy value = 1 will be used if available, if it is not available, the mode with simulation policy value = 2 will be used.

‍

In the following example, the “Container” mode is preferred over the “Truck” mode for the MFG_CA to DC_IL route. Note that since the “Product Name” column is left blank, this policy applies to all products using this route.

By Due Date

Selecting “By Due Date” is like “By Preference” in that different modes can be ranked via the “Simulation Policy Value”. However, selecting “By Due Date” adds the additional component of demand timing into its selection. This policy selects the highest preference option that can meet the due date of the shipment. The following screenshot shows that the By Due Date simulation policy is used on certain DC to CZ lanes where 2 Modes are used, Truck and Parcel:

1. CZ_MA can be served from DC_AZ, DC_IL, and DC_VA (sourcing choice is made based on rules specified in the Customer Fulfillment Policies sourcing policy table, which is covered in this Help Center article). If DC_AZ is chosen as the source, both a Truck and a Parcel Mode are available. Since the Simulation Policy is set to By Due Date, the highest priority mode (the one with the smallest Simulation Policy Value) that can get the shipment to the destination by the due date will be chosen. In this case Truck is the highest priority Mode as it works out cheaper than Parcel usually ($350 fixed cost for Truck vs $2/unit for Parcel), but by Truck the Transport Time takes 12.5 days, whereas for Parcel it only takes 3.5 days. So, for orders with a due date less than 3.5 days, the Parcel mode will always be chosen.
2. If DC_IL is chosen as the source for CZ_MA, then only the Truck mode is available.
3. If DC_VA is chosen as the source for CZ_MA, then 3 On Weight modes are available, see screenshot further above in the On Quantity / Volume / Weight section.

Transportation Costs, Transport Distance & Time

Costs associated with transportation can be entered in the Transportation Policies table Fixed Cost and Unit Cost fields. Additionally, the distance and time travelled using a certain Mode can be specified too:

1. Fixed Cost – the total cost of a shipment for this Lane Mode combination.
2. Unit Cost and its UOM field (latter not shown in screenshot above) – the variable cost and how it applies for this Lane Mode combination.
3. Transport Distance and its UOM field (latter not shown in screenshot above) – specifies the distance from the origin to the destination, using the mode specified. If user does not enter a distance, then the simulation will calculate it based on the latitudes and longitudes of the origin and destination. This distance is used in case distance-based costs have been specified and/or to calculate transport time from when not set (transport time = transportation distance / average speed set on the Model Settings table).
4. Transport Time and its UOM field – specifies the time a shipment takes from the origin to the destination, using the mode specified. If a user does not enter a transport time, the simulation will calculate it based on the transport distance and average speed set in the Model Settings table.
5. This DC_AZ to CZ_MA lane using the Truck mode is the number 1 choice for the By Due Date policy used on this lane (see screenshot previous section). The cost structure for using the Truck mode is a fixed one: each shipment costs $350. The simulation logic determines how much is on a shipment when it is dispatched and each shipment is then costed individually. We also see that the Transport Distance and Transport Time fields are populated for this mode, where Transport Time is 12.5 days. This is used when determining if the mode can get the product to the customer by the due date.
6. This DC_AZ to CZ_MA lane using the Parcel mode is the number 2 choice for the By Due Date policy used on this lane (see screenshot previous section). The cost structure for using the Parcel mode is a variable one: the cost for each unit using this mode is $2 (the UOM field is set to EA, not shown in the screenshot). The options on how the variable cost is applied include quantity, volume, weight, distance, time, and combinations of quantity/volume/weight with time or distance. For example, if the UOM field is set the EA-MI, it means the variable unit cost is applied per unit per mile traveled.

Lane and Mode Capacities

Maximum flow on Lanes (origin-destination-product combinations) and/or Modes (origin-destination-product-mode combinations) can also be specified in the Transportation Policies table:

The Lane Capacity field and its UOM field specify the maximum flow on the Lane, while the Lane Capacity Period and its UOM field are used to indicate over what period of time this capacity applies. In this example, the MFG_CA to DC_AZ lane (first record) has a maximum capacity of 30 shipments every 13 weeks. Once 30 shipments have been shipped on this lane in a 13 week period, this lane cannot be used anymore during those 13 weeks; it is available for shipping again from the first day of the next 13 week period. If a lane’s capacity is reached, it depends on the simulation logic set up what happens. It can for example lead to the simulation making different sourcing decisions: if By Preference sourcing is used and the lane capacity on the lane of the preferred source to the destination has been reached for the period, this source is not considered available anymore and the next preferred source will be checked for availability, etc.

‍

Analogous to the 4 fields to set Lane Capacity shown and discussed above, there are also 4 fields in the Transportation Policies table to set the Lane Mode Capacity where the capacity is specifically applied to a mode and not the whole lane in case multiple Modes exist on the lane: Lane Mode Capacity and its UOM field, and Lane Mode Capacity Period and its UOM field.

Other Simulation Specific Fields on the Transportation Policies Table

There are a few other fields on the Transportation Policies table that the Throg simulation engine will take into account if populated:

• Duty Rate – duties will be calculated based on the rate set in this field (enter 10 for a rate of 10%) and the value of the product moving over this Lane Mode combination, based on the Unit Value set for the product(s) being moved in the Products table.
• Load Process Name – if a Process representing the loading of a shipment using this Lane Mode combination is specified in the Processes table, it can be associated with the transportation policy through this field.
• Unload Process Name – if a Process representing the unloading of a shipment using this Lane Mode combination is specified in the Processes table, it can be associated with the transportation policy through this field.

Adding Sourcing Rules via the Model Element Tables (Simulation)

In a supply chain model, sourcing policies describe how network components create and order necessary materials. In Cosmic Frog, sourcing policies and rules appear in two different table categories:

• In the Customers and Facilities tables in the Model Elements category, users can define several sourcing rules.
  

• How to set sourcing policies via the Sourcing tables is described in this Help Center article.
  

In this section, we describe how to use the model elements tables to define sourcing rules for customers and facilities. Specifically, we can decide if each element is single sourced, allows backorders, and/or allows partial fulfillment.

Single source policies

Single source policies can be defined on either the order level or the line-item level. Setting “Single Source Orders” to “True” for a location means that for each order placed by that location, every item in that order must come from a single source. Setting this value to “False” does not prohibit single sourcing, it just removes the requirement.

Setting “Single Source Line Items” to “True” only requires each individual line-item come from a single source. In other words, even if this is “True”, an individual order can have multiple sources, as long as each line item is single sourced.

‍

If “Single Source Orders” is set to “True” and “Single Source Line Items” is set to “False”, the “Single Source Orders” value takes precedence.

Backorder policies

In case an order cannot be fulfilled by the due date (as set on the Customer Orders table in the case of Customers), it is possible to allow backorders where the order will still be filled, but it will be late, by setting the “Allow Backorders” value to “True”. A time limit can be set on this by using the “Backorder Time Limit” field and its UOM field, set to 7 days in the below screenshot. This means that the orders are allowed to be backordered, but if after 7 days the order still is not filled, it is cancelled. Leaving Backorder Time Limit blank means there is no time limit, and the order can be filled late indefinitely.

Partial fill policies

We can also decide to allow partial fulfillment of orders or individual line-items. If “Allow Partial Fill Orders” is set to “False”, orders need to be filled in full. If set to “True”, then only filling part of an order on time (by the due date) is allowed. What happens with the unfulfilled part of the order depends on if backorders are allowed. If so (“Allow Backorders” = “True”), then the remaining quantity of a partially filled order can be satisfied in the future with additional shipments. If a time limit on backorders is set and is reached on a partially filled order, the remaining quantity will be cancelled. “Partial Fill Orders” and “Partial Fill Line Items” behave similarly to the single sourcing policies, where it is possible to for example allow partially filling orders, but not partially filling line items. If “Partial Fill Orders” is set to “True”, then “Partial Fill Line Items” will also be forced to “True”.

Adding Transportation Modes (Simulation)

The Transportation Modes table is an often used optional input table to run a simulation. Mode attributes like fill levels and capacities are specified in this table to control the size of shipments, which will be explained first in this documentation. Rules of precedence when using multiple fill level / capacity fields and when using On Volume / Weight / Quantity transportation simulation policies will be covered also.

Fill Levels and Capacities

1. We are on the Transportation Modes input table.
2. Mode Name – a unique name for the mode. Note that each Mode used in the Transportation Policies table in the Mode Name field needs to have a matching record in this Transportation Modes table.
3. Volume Capacity and its UOM field – the maximum amount of product a shipment on this mode can take, in terms of volume.
4. Volume Fill Level and its UOM field – the amount of product a shipment needs to have on it at a minimum to be considered full enough to dispatch, in terms of volume.
5. This record indicates that for the Truck mode, 10,000 cubic foot is the maximum volume of product that can be put on a shipment and a shipment can be dispatched if at least 500 cubic foot of product is on the shipment.
6. These 3 records are the Modes that are used for transportation policies with simulation policy = On Volume. Since their simulation policy values in the Transportation Policies table set the capacity and fill levels (except for the capacity of the V1000 mode), they are not set here. For the V200 mode for example, the fill level is 200 cubic foot (the lowest fill level at which it can be dispatched) and the capacity is 1000 cubic foot, as it switches to the next mode (V1000) from 1000 cubic foot onwards.

The same capacity and fill level fields as for Volume are also available in this table for Quantity and Weight (not shown in the screenshot above).

Setting Multiple Fill Levels and/or Capacities

When utilizing more than 1 of the Fill Level fields, the one that is reached first is applied. For example, if a shipment’s weight has reached the weight fill level, but its volume has not yet reached the volume fill level, the shipment is allowed to be dispatched.

‍

Similarly, if more than 1 Capacity field has been populated, the one that is reached first is applied. For example, if a shipment’s volume has reached the volume capacity but not yet the weight capacity, it cannot be filled up further and will be dispatched.

On Quantity / Weight / Volume Simulation Policies and the Modes Table

As mentioned above, when transportation simulation policies of On Quantity / Weight / Volume are being used, the fill levels and capacities of these Modes are specified in the simulation policy value field on the Transportation Policies table. If also using the Transportation Modes table to set any fill level and/or capacity for these modes, user needs to take note of the effects this may have:

1. Setting a capacity for the highest volume / weight / capacity mode on the Transportation Modes table makes sense as this is not set through a simulation policy value on the Transportation Policies table.
2. Setting a fill level on the Transportation Modes table that is higher than the one set through the simulation policy field on the transportation policies table does raise the fill level and can create a situation where shipments of certain sizes are not allowed, typically resulting in lower service metrics. For example, if the V200 On Volume mode has a simulation policy of 200 in the transportation policies table, 200 CFT is the volume fill level for this mode. If on the Transportation Modes table a volume fill level of 300 is set for this mode, then shipments of sizes between 200 and 300 CFT are not allowed anymore (V0 for 0-200 CFT, V200 for 300-1000 CFT, and V1000 from 1000 CFT upwards).
3. Setting a fill level on the Transportation Modes table that is lower than the one set through the simulation policy field on the transportation policies table does not have any effect.
4. Setting a capacity on the Transportation Modes table that is higher than the one set through the simulation policy field on the transportation policies table does not have any effect.
5. Setting a capacity on the Transportation Modes table that is lower than the one set through the simulation policy field on the transportation policies table does lower the capacity and can again create a situation where shipments of certain sizes are not allowed, typically resulting in lower service metrics. For example, if the V200 On Volume mode has a simulation policy of 200 in the transportation policies table, 200 CFT is the volume capacity for the V0 mode (the lowest volume mode, used between 0-200 CFT). If on the Transportation Modes table a volume capacity of 100 CFT is set for this V0 mode, the simulation will enforce this capacity and shipments of sizes between 100 and 200 CFT are not allowed anymore.

Adding Demand (Simulation)

Simulations are generally (mostly) driven by demand specified as customer orders. These orders can be entered in the Customer Orders and/or the Customer Order Profiles input tables. The Customer Orders table typically contains historical transactional demand records to simulate a historical baseline. The Customer Order Profiles table on the other hand contains descriptions of customer order behaviors from which the simulation engine (Throg) generates orders that follow these profiles.

‍

In this documentation we cover both these input table, the Customer Orders table and the Customer Order Profiles table.

Customer Orders Table

To achieve the level of granularity needed and the time-based events to mimic reality as best as possible, every customer order to be simulated is explicitly defined in the customer orders table; this includes line items, order and due dates, and order quantities:

1. We are on the Customer Orders input table
2. Order ID and Line Item ID – these are user-defined IDs which will be repeated in the output tables so individual orders can be traced through the simulation. Records with the same Order ID together make up an order. Line Item IDs within an order should be unique. If transactional systems contain naming conventions for orders and line items, they can be used here. Otherwise user can for example use the same method as in the screenshot to start numbering orders from 1 and increment for each next order, and have line items within the orders also start at 1, incrementing by 1 for each next line item.
3. Customer Name and Product Name – specifies for which customer – product combination the line item is being specified.
4. Quantity and its UOM field – how much does this customer order of this product. This can be a numeric value or a distribution to be sampled, which will add variability to the simulation. This help article covers distributions in simulation. The UOM can be in terms of quantity, volume or weight.
5. Order Date – when does the order occur. When just a date is entered, the order occurs at midnight on that day. User can also enter a date and time into this field.
6. Due Date – when is the order due at the customer: it needs to be received before or at this date & time to be counted towards the on-time service metric. If a due date cannot be met, it depends on settings like allowing backorders and partial fill what happens with the order, which are settings that can be set per order (see additional fields listed below) or at the customer level.
7. CZ_MA places an Order with ID 1, which consists of 3 line items, 1 for each of 3 products. CZ_MA requires 20 units of Product_1, 600 units of Product_2 and 160 units of Product_3. The order is placed on January 2^nd, and is due 13 days later, on January 15^th.
8. Similarly, CZ_TX places an order (ID = 3) with 3 line items, 1 for each product. This order is placed on January 5^th and is due 17 days later, on January 22^nd.

Users can utilize the following additional fields available on the Customer Orders table if required. The single sourcing, allow partial fill, and allow backorder settings behave the same as those that can be set on the Customers table (see this help article), except these here apply to individual orders/individual line items rather than to all orders at the customer over the whole simulation horizon. Note that if these are set here on the Customer Orders table, these values take precedence over any values set for the particular customer in the Customers table:

• Unit Price and its UOM field – the price the customer pays for 1 unit of the product, used for revenue calculations. If set here, this overrides any Unit Price entered for the product on the Products table.
• Source Name – if the line item needs to be sourced from a particular source, the name of the source (which needs to be a facility or supplier) can be entered in this field. If multiple sources can be used, a facilities of suppliers group name can be used in this field too.
• Single Source Order – set this field to True if the whole order needs to be fulfilled by just 1 source.
• Single Source Line Item – set this field to True if the line item needs be fulfilled by just 1 source. Single Source Order = True overrides Single Source Line Item = False.
• Allow Partial Fill Orders – set this field to True if it is allowed to fill part of an order. If set to False, the whole order needs to be filled. If partially filling orders is allowed and backorders are allowed too, any remaining quantity that cannot be filled by the due date may be filled late on a future shipment. If there is a time limit on backorders, then any amount not fulfilled by the time this limit is reached will be cancelled.
• Allow Partial Fill Line Items – set this field to True if it is allowed to fill part of a line item. If set to False, the whole line item needs to be filled. Allow Partial Fill Orders = False overrides Allow Partial Fill Line Items = True.
• Allow Backorders – set to False when an order needs to be fulfilled by its due date; the order will be cancelled if it has not been filled by its due date. When set to True, a backorder is created in cases where the order cannot be fulfilled by the due date.
• Backorder Time Limit and its UOM field – if backorders are allowed, then this time limit indicates after which time any unfulfilled backorders will be cancelled. Not setting a time limit (leaving the field blank) means that backorders can be filled indefinitely. Setting the time limit to 0 is effectively settings Allow Backorders = False.

Customer Order Profiles Table

Rather than specifying individual orders and line items, the Customer Order Profiles table generates these individual orders from profiles which can for example disaggregate monthly demand forecasts into assumed or inferred profiles, using variability to randomize characteristics like quantities and time between orders.

1. We are on the Customer Order Profiles table.
2. Order ID – unique identifier for the profile. Orders generated from the profile have the same Order ID appended with a number for the order and line item within the order, i.e. CO_Profile_a_1-1, CO_Profile_a_2-1, etc.
3. Customer Name and Product Name – the customer – product combination for which the order profile is being set up. Group names can be used, and the product name can also be left blank.
4. Quantity and its UOM field – the amount of product that is being ordered. This can contain a single numeric value or a distribution.
5. Number Of Orders and Number Of Lines – when an order is generated (based on Start Date, Time Between Orders, and End Date, see next screenshot), this Number Of Orders field’s value indicates how many orders need to be placed at that time. If the Product Name is left blank, recurring orders can be generated with a varying number of line items in each order. If the Number Of Lines is 2 or greater, the Order Profile Product Selection and Order Profile Product Affinity tables will be used to determine which products are to be ordered.
6. This profile CO_Profile_a sets up orders for Product_4 at customer CZ_CO for 200 units in each order.
7. This profile CO_Profile_b also sets up orders for Product_4 at customer CZ_CO, however, it is excluded so will not be used unless the status is changed to Include through a scenario or in this table directly. Also, the quantity is now not a fixed number, but a Poisson distribution with a lambda of 200.

1. Service Level and its UOM field – this determines the due date for an order: due date = order date + service level.
2. Start Date – date & time from which the profile will start generating orders.
3. Time Between Orders and its UOM field – the interval between subsequent orders. This can be a fixed amount of time or can be randomized by using a distribution.
4. End Date – the last date the profile can generate any orders.
5. The CO_Profile_a profile requires a service level of 10 days, meaning the due date of any generated orders is 10 days from its order date. The interval between orders is set to be 14 days.
6. The CO_Profile_b profile also requires a service level of 10 days. Its time between orders is set to sample from the Normal distribution with a mean of 14 days and standard deviation of 2.

Note that by using start and end dates for profiles, users can control the portion of the simulation horizon in which a profile is used. This enables users to for example capture seasonal demand behaviors by defining a profile for Customer A/Product X in winter, and another profile for the same customer-product combination in summer.

Example Orders Generated by Customer Order Profiles

Two scenarios were run, 1 named “CZ_CO P4 profile a” where customer order profile a to generate orders at CZ_CO for Product_4 is included and 1 named “CZ_CO P4 profile b” where customer order profile b to generate orders at CZ_CO for Product_4 is included. These are the profiles shown in the 2 screenshots above. In the Simulation Order Report output table one can see the individual orders generated by these profiles during the simulation runs of these 2 scenarios:

1. These 6 records are the first 6 orders generated by customer order profile a:
   1. The quantity of each order is 200 units.
   2. The orders are exactly 14 days apart.
2. These 5 records are the first 5 orders generated by customer order profile b:
   1. The quantity of the orders varies as they are generated by sampling the Poisson distribution with lambda = 200.
   2. The interval between the orders varies too, as these too are generated from sampling a distribution, in this case the Normal distribution with a mean of 14 days and a standard deviation of 2. Looking at the Order Dates of the first and second order we see that there are about 15.5 days in between these 2 orders.

When running models in Cosmic Frog, users can choose the size of the resource the model’s scenario(s) will be run on in terms of available memory (RAM in Gb) and number of CPU cores. Depending on the complexity of the model and the number of elements, policies and constraints in the model, the model will need a certain amount of memory to run to completion successfully. Bigger, more complex models typically need to be run using a resource that has more memory (RAM) available as compared to smaller, less complex models. The bigger the resource that is being used, the higher the billing factor which leads to using more of the available cloud compute hours available to the customer (the total amount of cloud compute time available to the customer is part of customer’s Master License Agreement with Optilogic). Ideally, users choose a resource size that is just big enough to run their scenario(s) without the resource running out of memory, while minimizing the amount of cloud compute time used. This document guides users in choosing an initial resource size and periodically re-evaluating it to ensure optimal usage of the customer’s available cloud compute time.

Resource Size and its Components

Once a model has been built and the user is ready to run 1 or multiple scenarios, they can click on the green Run button at the right top in Cosmic Frog which opens the Run Settings screen. The Run Settings screen is documented in the Running Models & Scenarios in Cosmic Frog Help Center article. On the right-hand side of the Run Settings screen, user can select the Resource Size that will be used for the scenario(s) that are being kicked off to run:

1. On the right-hand side of the Run Settings screen, user can select the Resource Size.
2. The Resource Size drop-down shows all resource sizes available for selection, from Mini (smallest) to Supernova (biggest). A resource size is a combination of the number of CPU cores and the amount of memory (RAM) that is available when using that resource. Using more cores leads to shorter runtimes as certain tasks can be handled simultaneously instead of sequentially. In the above screenshot, Resource Size S has been selected. This resource has 4 CPU cores available and up to 16 Gb (Gigabytes) RAM. The resource size drop-down also lists the Billing Factor of the resource. For example, the 3XS resource size has a Billing Factor of 0.5. This means that if a scenario run takes 2 minutes, 1 minute is billed to the user and subtracted from the total cloud compute time still available. The 3XS resource is a small resource (1 CPU core and up to 2 Gb of RAM), and therefore cheaper to run on. As another example, the 2XL resource size has a billing factor of 2, meaning that a run taking 15 minutes will be billed as 30 minutes of cloud compute time used. This resource has 8 CPU cores, so will be able to perform quite a few tasks in parallel, and up to 128Gb of RAM, which is why it is more expensive to run.
3. Note that in order to use Supernova, the biggest resource available with 32 CPUs, 2,000 Gb of RAM and a billing factor of 50, users need a professional business account (see also the compare plans webpage). To ensure a user wants to go ahead with using this large and more expensive resource, an additional message confirming the choice will be shown to the user; this message reads: “Supernova is our most powerful resource size (billing factor 50x), designed for extremely large and complex models. Please ensure this size is appropriate before proceeding”:

1. The Supernova resource size has been selected. To warn the user they have done so, the font color of Supernova is gold, and there is a gold exclamation mark icon next to it.
2. The text of the warning is displayed at the bottom of the run screen.
3. Users can also click on the Resource Size Selection Guide link to open this Help Center article.

Choosing an Initial Resource Size

In this section, we will guide users on choosing an initial resource size for the different engines in Cosmic Frog, based on some model properties. Before diving in, please keep following in mind:

• One can more quickly home in on the most appropriate resource size for a scenario by starting with one that is too large, rather than too small. A scenario run will end in an error if not enough memory is available with no indication of how much memory is required. In the logs of a successful run, maximum memory usage will be listed, which users can use to select the most appropriate resource size for their next run(s). See more on where to find this information in the "Evaluating the Resource Size" section further below.
• It is not possible to predict exactly how much memory a scenario needs to run to completion successfully. However, we can make an educated best estimate based on test models run by Optilogic. This is intended to be a starting point from which users can finetune the resource size further by taking the steps described in the “Evaluating the Resource Size” section further below.

Neo (Optimization)

There are quite a few model factors that influence how much memory a scenario needs to solve a Neo run. These include the number of model elements, policies, periods, and constraints. The type(s) of constraints used may play a role too. The main factors, in order of impact on memory usage, are:

1. The number of lanes. This is the number of source-destination-product-period combinations that are considered in the model.
2. The number of demand records.
3. The number of constraints.

These numbers are those after expansion of any grouped records and application of scenario items, if any.

‍

The number of lanes can depend on the Lane Creation Rule setting in the Neo (Optimization) Parameters:

1. After clicking on the Run button at the right top in Cosmic Frog, the Run screen opens.
2. When Neo is selected as the Engine, the Neo (Optimization) Parameters will be applied. To view these, expand this section in case it is collapsed.
3. One of the parameters users can select is the Lane Creation Rule and this will impact the total number of lanes that the scenario(s) will contain. There are 4 options here:
   
   1. Transportation Policy Lanes Only: the lanes that are set up on the Transportation Policies table will be part of the scenario(s); any records on the Sourcing Policies tables will not be used for lane creation.
   2. Sourcing Policy Lanes Only: the lanes that are set up on the Sourcing Policies tables will be part of the scenario(s); any records on the Transportation Policies tables will not be used for lane creation.
   3. Intersection: lanes that exist both in the Transportation Policies and Sourcing Policies tables will be part of the scenario(s); any lanes that only exist in either the Transportation Policies table or a Sourcing Policies table will not be used (this is like an inner join).
   4. Union: all lanes that exist in the Transportation Policies table and all lanes that exist in the Sourcing Policies tables will be used for lane creation (any duplicates will be removed).

Note that for lane creation, expansion of grouped records and application of scenario item(s) need to be taken into account too to get at the number of lanes considered in the scenario run.

Users can use the following list to choose an initial resource size for Neo runs. First, calculate the number of demand records multiplied with the number of lanes in your model (after expansion of grouped records and application of scenario items). Next, find the range in the list, and use the associated recommended initial resource size:

# demand records * # lanes: Recommended Initial Resource Size

• <1*10³: Mini
• ~1*10³ – 1*10⁸: 4XS – 3XS
• ~1*10⁸ – 1*10¹⁰: 2XS – S – M
• ~1*10¹⁰ – 1*10¹¹: L - XL
• ~1*10¹¹ – 5*10¹⁴: 2XL – 3XL
• ~5*10¹⁴ – 5*10¹⁵: 4XL
• >5*10¹⁵: Overkill

Throg (Simulation) & Dendro (Inventory)

A good indicator for Throg and Dendro runs to base the initial resource size selection on is the order of magnitude of the total number of policies in the model. To estimate the total number of policies in the model, add up the number of policies contained in all policies tables. There are 5 policies tables in the Sourcing category (Customer Fulfillment Policies, Replenishment Policies, Production Policies, Procurement Policies, and Return Policies), 4 in the Inventory category (Inventory Policies, Warehousing Policies, Order Fulfillment Policies, and Inventory Policies Advanced), and the Transportation Policies table in the Transportation category. The policy counts of each table should be those after expansion of any grouped records and application of scenario items, if any. The list below shows the minimum recommended initial resource size based on the total number of policies in the model to solve models using the Throg or Dendro engine.

Number of Policies: Minimum Resource

• 1000: XS
• 10,000: S
• 50,000: M
• 100,000: L
• 500,000: XL
• 1,000,000: 2XL
• >10,000000: 4XL

Hopper (Transportation)

For Hopper runs, memory is the most important factor in choosing the right resource, and the main driver of memory requirements is the number of origin-destination (OD) pairs in the model. OD pairs are determined primarily by all possible facility-to-customer, facility-to-facility, and customer-to-customer lane combinations.

Most Hopper models have many more customers compared to facilities, and so we often can use the number of customers in a model as a guide for resource size. The list below shows the minimum recommended initial resource size to solve models using Hopper.

Customers: Minimum Resource Size

• 100: mini
• 500: 4XS
• 1,000: 3XS
• 2,000: 2XS
• 3,000: XS
• 4,000 – 5,000: S
• 6,000 – 8,000: L
• 9,000 – 10,000: XL
• 15,000: 2XL
• 20,000: 3XL
• >20,000: Contact Support

Triad (Greenfield)

Most Triad models should solve very quickly, typically under 10 minutes. Still, choosing the right resource size will ensure your Triad model solves successfully, without paying for unneeded compute resources.

As with Hopper, memory is the most important factor in resource selection. In Triad, the main driver of memory requirements is the number of customers, with a smaller secondary effect from the number of greenfield facilities.

The list below shows the minimum recommended initial resource size to solve models using Triad where the number of facilities is assumed to be between 1 and 10:

Customers: Minimum Resource Size

• ‍‍100: 3XS
• 500: 2XS
• 1,000: XS
• 2,000: S
• 3,000: S–L*

Please note:

• Models with more facilities than customers are infeasible.
• S and M resources have the same memory, so S can almost always be used in place of M.
• When running Triad with 3,000 or more customers, try an S resource first. If you encounter an “out of memory” error, then try an L resource. Triad implements clustering to reduce the memory requirement for each run. This is based on how far apart your customers are, and how well they naturally cluster – if your customers are close together and cluster naturally, a smaller resource size may be used.

Evaluating the Resource Size

After running a scenario with the initially selected resource size, users can evaluate if it is the best resource size to use or if a smaller or larger one is more appropriate. The Run Manager application on Optilogic’s platform can be used to assess resource size:

1. Go to the Run Manager application by clicking on its icon on the left-hand side when logged in to the Optilogic platform on cosmicfrog.com. Should you not see the Run Manager here, then click on the icon with 3 dots to show all applications; the Run Manager application should now be visible too.
2. Under Type at the top of the screen, filter for Scenario.
3. Find the scenario you want to evaluate the resource size of in the list of jobs, click on it to select it.
4. The Job Info for this scenario will be displayed on the right hand-side.
5. If not already selected, click on the i-icon to show the Job Info, other details of the job can be viewed by clicking on the icons to the right of the i-icon.
6. Part of the job information listed is what resource size the job was run on, in this case resource size 2XS was used, which has 2 CPU cores and up to 4Gb of RAM.
7. Next, click on the bar chart icon to see the Job Usage Metrics:

1. The legend tells us that the red bar represents the percentage of CPU that was used at peak usage and the yellow bar the percentage of memory used at peak usage.
2. 26.6% of the available memory was used at peak usage. This is with 4Gb of RAM available, so about 0.266 * 4 Gb = 1.064 Gb of RAM was used.

Using this knowledge that the RAM required at peak usage is just over 1 Gb, we can conclude that going down to resource size 3XS, which has 2Gb of RAM available should still work OK for this scenario. The expectation is that going further down to 4XS, which has 1Gb of RAM available, will not work as the scenario will likely run out of memory. We can test this with 2 additional runs. These are the Job Usage Metrics after running with resource size 3XS:

As expected, the scenario runs fine, and the memory usage is now at about 54% (of 2Gb) at peak usage.

Trying with resource size 4XS results in an error:

1. The State of the job now says "error" instead of "done".
2. View the Job Error Log by clicking on the 6^th icon at the top of the right panel.
3. The Job Error Log contains a warning that the job ran out memory, which is what we expected as the scenario requires a bit over 1Gb of RAM at peak usage and the 4XS resource size has up to 1Gb of RAM available.

Note that when a scenario runs out of memory like this one here, there are no results for it in the output tables in Cosmic Frog if it is the first time the scenario is run. If the scenario has been run successfully before, then the previous results will still be in the output tables. To ensure that a scenario has run successfully within Cosmic Frog, user can check the timestamp of the outputs in the Optimization Network Summary (Neo), Transportation Summary (Hopper), or Optimization Greenfield Output Summary (Triad) output tables, or review the number of error jobs versus done jobs at the top of Cosmic Frog (see next screenshot). If either of these 2 indicate that the scenario may not have run, then double-check in the Run Manager and review the logs there to find the cause.

In the status bar at the top of Cosmic Frog, user can see that there were 2 error jobs and 13 done jobs within the last 24 hours.

In conclusion, for this scenario we started with a 2XS resource size. Using the Run Manager, we reviewed the percentage of memory used at peak usage in the Job Usage Metrics and concluded that a smaller 3XS resource size with 2Gb of RAM should still work fine for this scenario, but an even smaller 4XS resource size with 1Gb of RAM would be too small. Test runs using the 3XS and 4XS resource sizes confirmed this.

Summary of Resource Size Selection Guidelines

1. When building out your model and its scenarios, be cognizant of the size of your model, and base your initial resource size on the appropriate table in the section “Choosing an Initial Resource Size” above. Remember that:
   
   1. If you choose a resource size that is too large, the scenario will run to completion fine and you can determine the more appropriate smaller resource size using the job usage metrics of this run.
   2. If you choose a resource size that is too small, the scenario will end in an error that will indicate that there is not enough memory available, but it will not tell you how much more it needs. In this case a good approach can be to select a resource 2 or 3 sizes bigger and then scale back again from there if the scenario runs fine and the job usage metrics indicate a smaller resource size should suffice.
   3. By using groups, it is easy to for example create all to all policies by just populating 1 record in the sourcing and/or transportation policies tables. However, these can make a model unnecessarily big and in need of more RAM to solve. Try to be aware of the number of enumerated records a grouped record will result in and ensure no more options than those realistically under consideration are included in your model.
2. Use the Job Usage Metrics that can be found in the Run Manager to assess if the resource size used is appropriate. Based on the peak RAM usage you can determine if you could use a smaller resource size and if so, which one should be the smallest one that has sufficient RAM.
   
   1. You can do this for each scenario that may be re-run frequently in future to see if it is worthwhile to run different scenarios with different resource sizes.
3. It is recommended to periodically re-assess if the resource size(s) used are still appropriate, this is especially important after making changes to your model.

Transportation lanes are a necessary part of any supply chain. These lanes represent how product flows throughout our supply chain. In network optimization, transportation lanes are often referred to as arcs or edges.

In general, lanes in our supply chain are generated from the transportation policies and sourcing policies provided in our data tables.

Transportation policies are stored in the TransportationPolicies table. Sourcing policies are stored in the following tables:

• CustomerFulfillmentPolicies
• ReplenishmentPolicies
• ProcurementPolicies

From the data in these tables, the software automatically generates the lanes (i.e. arcs or edges) in our network before sending it to the optimization solver. We can control how these lanes are generated as a parameter of our Neo model.

Neo models can follow 4 different lane creation policies:

• Transportation policy lanes only
• Sourcing policy lanes only
• Intersection
• Union

Transportation policy lanes only

If the “Transportation Policy Lanes Only” rule is selected, Cosmic Frog will only generate transportation lanes based on data in the TransportationPolicies table. If a lane between two sites is not explicitly defined here, product will not be able to directly flow between those sites. Note that any additional information specified in a Sourcing Policy table (unit cost, policy rule etc.) will still be respected for the lane so long as it exists in the Transportation Policies table.

Sourcing policy lanes only

If the “Sourcing Policy Lanes Only” rule is selected, Cosmic Frog will only generate transportation lanes based on data in the Sourcing tables. Even if an origin-destination path is defined in the TransportationPolicies table, product will not be able to flow via this lane unless there is a specific sourcing policy defining how the destination site gets product from the origin site. Note that any additional information specified in a Transportation Policies table (cost, policy rule, multiple modes etc.) will still be respected for the lane so long as it exists in a Sourcing Policy table.

Intersection

If the “Intersection” rule is selected, Cosmic Frog will only generate transportation lanes if they are defined in both the transportation policy table and one of the sourcing policy tables.

‍

For users converting models from Supply Chain Guru©, the default SCG© lane creation rule is “Intersection”.

Union

If the “Union” rule is selected, Cosmic Frog will generate transportation lanes if they are defined in either the transportation policy table or one of the sourcing policy tables.

Transportation Costs in Optimization

Here we will cover the options a Cosmic Frog user has for modeling transportation costs when using the Neo Optimization engine. The different fields that can be populated and how the calculations under the hood work will be explained in detail.

There are many ways in which transportation can be costed in real life supply chains. The Transportation Policies table contains 4 cost fields to help users model costs as close as possible to reality. These fields are: Unit Cost, Fixed Cost, Duty Rate and Inventory Carrying Cost Percentage. Not all these costs need to be used: the one(s) that are applicable should be populated and the others can be left blank. The way some of these costs work depends on additional information specified in other fields, which will be explained as well.

Note that in the screenshots throughout this documentation some fields in the Cosmic Frog tables have been moved so they could be shown together in a screenshot. You may need to scroll right to see the same fields in your Cosmic Frog model tables and they may be in a different order.

We will first discuss the input fields with the calculations and some examples; at the end of the document an overview is given of how the cost inputs translate to outputs in the optimization output tables.

Unit Cost Field

This field is used for transportation costs that increase when the amount of product being transported increases and/or the transportation distance or time increases. As there are quite a few different measures based on which costs can depend on the amount of product that is transported (e.g. $2 per each, or $0.01 per each per mile, or $10 per mile for a whole shipment of 1000 units, etc.) there is a Unit Cost UOM field that specifies how the cost specified in the Unit Cost field should be applied. In a couple of cases, the Average Shipment Size and Average Shipment Size UOM fields must be specified too as we need to know the total number of shipments for the total Unit Cost calculation. The following table provides an overview of the Unit Cost UOM options and explains how the total Unit Costs are calculated for each UOM:

Example of Unit Cost field with different Unit of Measures

With the settings as in the screenshot above, total Unit Costs will be calculated as follows for beds, pillows, and alarm clocks going from DC_Reno to CUST_Phoenix:

1. Beds: say 75 beds are being transported, then the cost associated with this flow = $0.02 (cost per bed per mile) * 75 (# beds) * 703 (distance in miles) = $1,054.50.
2. Pillows: say 500 pillows are being transported, then the cost associated with this flow = $3.50 (cost per pillow) * 500 (# pillows) = $1750.00.
3. Alarm clocks: say 2,000 alarm clocks are being transported, then the cost associated with this flow = $4 (cost per mile) * 703 (distance in miles) * 2,000 (# alarm clocks) / 1000 (average # alarm clocks per shipment) = $5,624.00. In other words, the cost per shipment = $4/mi * 703 mi = $2,812 and there are 2 shipments needed to ship 2000 units when 1 shipment contains 1000 units on average.

The Unit Cost field can contain a single numeric value (as in the examples above), a step cost specified in the Step Costs table, a rate specified in the Transportation Rates table, or a custom cost function.

Product Name Group Behavior field and example

If stepped costs are used as the Unit Cost for Transportation Policies that use Groups in the Product Name field, then the Product Name Group Behavior field determines how these stepped costs are applied:

• If Product Name Group Behavior = Enumerate, then the step of the cost to be used will be determined for each individual product based on the total flow of that product. This option is used by default if the Product Name Group Behavior field is left blank.
• If Product Name Group Behavior = Aggregate, then the step of the cost to be used will be based on the total flow of all products.

See following screenshots for an example of using stepped costs in the Unit Cost field and the difference in cost calculations for when Product Name Group Behavior is set to Enumerate vs Aggregate:

On the Step Costs table (screenshot above), the stepped costs we will be using in the Unit Cost field on the Transportation policies table are specified. All records with the same Step Cost Name (TransportUnitCost_2 here) make up 1 set of stepped costs. The Step Cost Behavior is set to Incremental here, meaning that discounted costs apply from the specified throughput level only, not to all items once we go over a certain throughput. So, in this example, the per unit cost for units 0 through 10,000 is $1.75, $1.68 for units 10,001 through 25,000, $1.57 for units 25,001 through 50,000, and $1.40 for all units over 50,000.

The configuration in the Transportation Policies table looks as follows:

1. A Group is used in the Product Name field, this group “AllProducts” contains all 3 products that are being modelled: beds, pillows and alarm clocks.
2. The stepped costs that have been set up are used in the Unit Cost field by typing the Step Cost Name of “TransportUnitCost_2” in this field.
3. Since stepped costs are used on a record that uses a Group for the Product Name, the Product Name Group Behavior field needs to be used to indicate if the stepped cost function is to be applied over the flow of all 3 products together, in which case this field should be set to Aggregate, or if the stepped function is to be applied to the flow of each product individually, in which case this field should be set to Enumerate.

The following screenshot shows the outputs on the Optimization Flow Summary table of 2 scenarios that were run with these stepped costs, 1 scenario used the Enumerate option for the Product Name Group Behavior and the other 1 used the Aggregate option. The cost calculations are explained below the screenshot.

1. When the stepped costs are applied to all 3 products together (Product Name Group Behavior = Aggregate), the total Transportation Cost is calculated based on the total Flow Quantity which is: 22,950 (# beds) + 45,899 (# pillows) + 9,180 (# alarm clocks) = 78,029 units. The stepped costs are then applied to this number as follows: 10,000 (units 1-10,000) * $1.75 + 15,000 (units 10,001-25,000) * $1.68 +25,000 (units 25,001-50,000) * $1.57 + 28,029 (units 50,001-78,029) * $1.40 = $121,190.60. These costs are divided over the 3 products by prorating the cost based on the individual flow quantities. For beds this results in: $121,190.60 * 22,950 / 78,029 = $35,644.75; similar calculations are used for pillows and alarm clocks.
2. When the stepped costs are applied to the products individually (Product Name Group Behavior = Enumerate), the Transportation Cost for each product is calculated based on its Flow Quantity. So, the stepped costs are applied to the 22,950 beds as follows: 10,000 (beds 1-10,000) * $1.75 + 12,950 (beds 10,001-22,950) * $1.68 = $39,256. Similar calculations are used to calculate the transportation costs for pillows and alarm clocks.

Fixed Cost Field

The Fixed Cost field can be used to apply a fixed cost to each shipment for the specified origin-destination-product-mode combination. An average shipment size needs to be specified to be able to calculate the number of shipments from the amount of product that is being transported. When calculating the number of shipments, the result can contain fractions of shipments, e.g. 2.8 or 5.2. If desirable, these can be rounded up to the next integer (e.g. 3 and 6 respectively) by setting the Fixed Cost Rule field to Treat As Full. Note however that using this setting can increase model runtimes, and using the default Prorate setting is recommended in most cases.

In summary, The Fixed Cost field therefore works together with the Fixed Cost Rule, Average Shipment Size, and Average Shipment Size UOM fields. The following table shows how the calculations work:

The Fixed Cost field can contain a single numeric value, or a step cost specified in the Step Costs table.

Example of Fixed Costs and use of Fixed Cost Rule field

Following example shows how Fixed Costs are calculated on the DC_Scranton – CUST_Augusta lane and illustrates the difference between setting the Fixed Cost Rule to Prorate vs Treat As Full

This setup in the Transportation Policies table means that the cost for 1 shipment with on average 1,000 units on it is $100. 2 scenarios were run with this cost setup, 1 where Fixed Cost Rule was set to Prorate and 1 where it was set to Treat As Full. Following screenshot shows the outputs of these 2 scenarios:

1. In the Prorate scenario, 3,828 pillows are moved from DC_Scranton to CUST_Augusta. This means 3,828 / 1,000 = 3.828 shipments. The Shipment Cost for 3.828 shipments = $100 * 3.8282 = $382.80. Similar calculations are used for the Shipment Costs foralarm clocks and beds.
2. In the Treat As Full scenario, again 3,828 pillows are moved from DC_Scranton to CUST_Augusta, which again leads to 3.828 shipments being calculated. Since the Fixed Cost Rule is set to Treat As Full here, this is rounded up to the next integer, which is 4. Then the Shipment Cost is calculated as $100 * 4 = $400. Again, similar calculations are used for the Shipment Costs for alarm clocks and beds.

Product Name Group Behavior field and example

For Fixed Costs on Transportation Policies that use Groups in the Product Name field, the Product Name Group Behavior field determines how these fixed costs are applied:

• If Product Name Group Behavior = Enumerate, then the fixed costs to be used will be determined for each individual product based on the total flow of that product. In other words, the number of shipments for each individual product is determined, and the fixed costs then applied based on that number. This option is used by default if the Product Name Group Behavior field is left blank
• If Product Name Group Behavior = Aggregate, then the fixed costs to be used will be based on the total flow of all products. In other words, the number of shipments needed if different products can be on the same shipment is calculated and the fixed costs are applied to that number.

See following screenshots for an example of using Fixed Costs where the Fixed Cost Rule is set to Treat As Full and the difference in cost calculations for when Product Name Group Behavior is set to Enumerate vs Aggregate:

The transportation policy from DC_Birmingham to CUST_Baton Rouge uses the AllProducts group as the ProductName. This Group contains all 3 products being modelled: beds, pillows, and alarm clocks. The costs on this policy are a fixed cost of $100 per shipment, where an average shipment contains 1,000 units. The Fixed Cost Rule is set to Treat As Full meaning that the number of shipments will be rounded up to the next integer. Depending on the Product Name Group Behavior field this is done for the flow of each product individually (when set to Enumerate) or done for the flow of all 3 products together (when set to Aggregate):

1. When the fixed costs are applied to the products individually (Product Name Group Behavior = Enumerate), the fixed Shipment Cost for each product is calculated based on its Flow Quantity. So, the fixed costs are applied to the 45,123 pillows as follows: number of shipments = roundup (45,123 / 1,000) = 46 and therefore the fixed Shipment Cost for pillows = 46 shipments * $100 = $4,600. Similar calculations are used to calculate the transportation costs for beds and alarm clocks, which result in 10 shipments for alarm clocks and 23 shipments for beds. So, the total number of shipments is 46 + 10 + 23 = 79, and the total Fixed Shipment cost for the 3 products is $7,900.
2. When the fixed costs are applied to all 3 products together (Product Name Group Behavior = Aggregate), the total Fixed Shipment Cost is calculated based on the total Flow Quantity which is: 22,450 (# beds) + 45,123 (# pillows) + 9,180 (# alarm clocks) = 76,753 units. This translates to a number of shipments of: roundup (76,753 / 1,000) = 77. The fixed costs are then applied to this number and calculated as: 77 * $100 = $7,700. These costs are divided over the 3 products by prorating the costs based on the individual flow quantities. For pillows this results in: $7,7000 * 45,123 / 76,753 = $4,526.82. Similar calculations are used for beds and alarm clocks.

Duty Rate Field

When products are imported or exported from/to different countries, there may be cases where duties need to be paid. Cosmic Frog enables you to capture these costs by using the Duty Rate field on the Transportation Policies table. In this field you can specify the percentage of the Product Value (as specified on the Products table) that will be incurred as duty. If this percentage is for example 9%, you need to enter a value of 9 into the Duty Rate field. The calculation of total duties on a lane is as follows: Flow Quantity * Product Value * Duty Rate.

Example using the Duty Rate field

The following screenshots show the Product Value of beds, pillows and alarm clocks in the Products table, the Duty Rate set to 10% on the DC_Birmingham to CUST_Nashville lane in the Transportation Policies table, and the resulting Duty Costs in the Optimization Flow Summary table, respectively.

Alarm clocks have a Product Value of $30. With a Duty Rate of 10% and moving 24,049 from DC_Birmingham to CUST_Nashville, the resulting Duty Cost = 24,049 * $30 * 0.1 = $72,147.

Inventory Carrying Cost Percentage Field

If in transit inventory holding costs need to be calculated, the Inventory Carrying Cost Percentage field on the Transportation Policies table can be used. The value entered here will be used as the percentage of product value (specified on the Products table) to incur the in transit holding costs. If the Inventory Carrying Cost Percentage is 13%, then enter a value of 13 into this field. This percentage is interpreted as an annual percentage, so the in transit holding cost is then prorated based on transit time. The calculation of the in transit holding costs becomes: Flow Quantity * Product Value * Inventory Carrying Cost Percentage * Transit Time (in days) / 365.

Note that there is also an Inventory Carrying Cost Percentage field in the Model Settings table. If this is set to a value greater than 0 and there is no value specified in the Transportation Policies table, the value from the Model Settings table is automatically used for inventory carrying cost calculations, including in transit holding costs. If there are values specified in both tables, the one(s) in the Transportation Policies table take precedence for In Transit Holding Cost calculations.

Example using the Inventory Carrying Cost Percentage field

The following screenshots show the Inventory Carrying Cost Percentage set to 20% on the DC_Birmingham to CUST_Nashville lane in the Transportation Policies table, and the resulting In Transit Holding Costs in the Optimization Flow Summary table, respectively. The Product Values are as shown in the screenshot of the Products table in the previous section on Duty Rates.

For Pillows, the Product Value set on the Products Table is $100. When 120,245 units are moved from DC_Birmingham to CUST_Nashville, which takes 3.8909 hours (214 MI / 55 MPH), the In Transit Holding Costs are calculated as follows: 120,245 (units) * $100 (product value) * 0.2 (Carrying Cost Percentage) * (3.8909 HR (transport time) / 24 (HRs in a day)) / 365 (days in a year) = $1,068.18.

Summary of Transportation Cost fields on Output Tables

The following table gives an overview of how the inputs into the 4 cost fields on the Transportation Policies table translate to outputs in multiple optimization output tables. The table contains the field names in the output tables and shows from which input field they result.

Note that the 4 different types of transportation costs are also included in the Landed Cost (Optimization Demand Summary table) and Parent Node Cost (Optimization Cost To Serve Parent Information Report table) calculations.  

The SQL Editor helps users write, edit, and execute SQL (Structured Query Language) queries within Optilogic’s platform. It provides direct access to database objects such as tables and views stored within the platform. In this documentation, the Anura Supply Chain Model Database (Cosmic Frog’s database) will be used as the database example.

Anura model exploration and editing are enabled through the three windows of the SQL Editor:

1. Database Browser: Explore and select from your databases.
2. Query Editor: Construct & execute SQL queries and view results.
3. Metadata Explorer: Access table structure and query information.

The Anura database is stored in PostgreSQL and exclusively supports PostgreSQL query statements to ensure optimized performance. Visit https://www.postgresql.org/ for more detailed information.

To enable the SQL editor, select a table or view from a database. Once selected, the SQL Editor will prepopulate a Select query, and the Metadata Explorer displays the table schema to enable initial data exploration.

Database Browser

The Database Browser offers several tools to explore your databases and display key information.

1. Search Bar: Find databases based on keyword search and extend the keyword phrase to limit results.
2. Collapse Databases: Hides all exposed database objects, leaving a list of available databases.
3. Custom Items: Limit displayed results to user-modified schema objects (models, tables, views).
4. Show/Hide Empty: Toggle to include or exclude empty schema objects (tables, views).
5. Refresh: Update the list of available schema objects (databases, tables, views).
6. Database: Anura (Cosmic Frog) model.
7. Table: Data table stored within a Cosmic Frog model. Note: The row count & schema modification icons appear to the right of table names.
8. View: A virtual table generated from one or more tables to present a customized subset of data by executing a SQL query.

Query Editor

The Query Editor enables users to create and execute custom SQL queries and view the results.  Reserved words are highlighted in blue to assist in SQL editing.  This window is not enabled until a model table or view has been selected from the database browser; once selected, the user is able to customize this query to run in the context of the selected database.

1. Builder: Filter table or view data by adding additional query logic to the where clause.
2. Format: Parses the current SQL statement inserting line breaks and indentations, making it easier to follow query logic.
3. Replace: Removes all query text and replaces with the currently stored clipboard data (Ctrl+v).
4. Bookmark: Add the current query to a metadata library stored at the database level
5. Clear: Removes all query text.
6. Execute: Execute the enabled text within the query editor. Note: To disable a single line, add the prefix — to the statement line. To disable multiple lines, add the prefix /* to the first line and the suffix */ to the last line.
7. Export Results: Exports the results from the executed query to CSV or XLS.

Metadata Explorer

The Metadata Explorer provides a set of tools to efficiently create and store SQL queries.

1. Data Types: Explore the complete list of column names with data types of the selected table or view.
2. Bookmark: Access queries that have been previously saved within the database.
3. Suggestions: Browse a list of template queries to accelerate your query creation.
4. History: View the session history of executed queries.

SQL Query Basics

SQL is a powerful language that allows you to manipulate and transform tabular data.  The query basics overview will help guide you through creating basic SQL queries.

Example 1: Filter Criteria - Customers with status set to include without latitude

SELECT A.CustomerName, A.Status, A.Region
FROM customers A
Where A.Latitude IS NOT NULL and A.Status = ‘Include’

‍
Example 2: Summarizing Records - Regions with 2 or more geocoded customers

SELECT A.Region, A.Status, Count(*) AS Cnt
FROM customers A
Where A.Latitude IS NOT NULL
Group By A.Region, A.Status
Having Count(*) > 1
Order by Cnt Desc

Table Joins

Often, your model analysis will require you to use data stored in more than one table. To include multiple tables in a single SQL query, you will have to use table joins to list the tables and their relationships.

‍

If you are unsure if all joined values are present in both tables, leverage a Left or Right join to ensure you don’t unintentionally exclude records.

Example 1: Inner Join - Join Customer Demand and Customers to add Region to Demand

SELECT A.CustomerName, A.ProductName, B.Region, A.Quantity
FROM customerdemand A INNER JOIN Customers B
  on A.CustomerName = B.CustomerName

‍
Example 2: Left Join - Find Customer Demand records missing Customer record

SELECT A.CustomerName, A.ProductName, B.Region, A.Quantity
FROM customerdemand A Left JOIN Customers B
  on A.CustomerName = B.CustomerName
Where B.CustomerName is Null

‍
Example 3: Inner Join & Aggregation – Summarize Demand by Region

SELECT B.Region, A.ProductName, SUM(Cast (A.Quantity as Int)) Quantity
FROM customerdemand A INNER JOIN Customers B
  on A.CustomerName = B.CustomerName
Group By B.Region, A.ProductName

Table Union

When data is separated into two or more tables due to categorical differences in the data, a join won’t work because there is a common structure, not a relationship between the tables. A UNION is a type of join that allows you to merge the results of two separate table queries into a single unified output. Ensure each query has the same number of columns in the same order.

‍

Example 1: UNION – Create a unified view of all customers and facilities that are geocoded

SELECT A.CustomerName as SiteName, A.City, A.Region, A.Country, A.Latitude, A.Longitude, 'Cust' as Type
FROM customers A  
  UNION
SELECT B.FacilityName as SiteName, B.City, B.Region, B.Country,B.Latitude, B.Longitude, 'Facility' as Type
FROM Facilities B

Sub-Query

As queries grow in complexity, it is often easiest to reset the table references by creating a sub-query.  A sub-query allows you to create a new virtual table and reference this abbreviated name and structure as you build out a query in phases.

‍

Example 1: Subquery +UNION – Create a unified view of all customers and facilities that are geocoded

SELECT C.SiteName, C.city, C.Region, C.Country, C.Latitude, C.Longitude, C.Type
FROM (  
  SELECT A.CustomerName as SiteName, A.City, A.Region, A.Country, A.Latitude, A.Longitude, 'Cust' as Type  
  FROM customers A    
    UNION  
  SELECT B.FacilityName as SiteName, B.City, B.Region, B.Country,B.Latitude, B.Longitude, 'Facility' as Type  
  FROM Facilities B
) C
WHERE C.Latitude IS NOT NULL

Table Search Filter

As data tables grow, it is often more efficient to use a table filter to find missing values than a left join and null filter criteria.

‍

Example 1: Table Search Filter – CustomerDemand without a Customer match

SELECT * FROM customerdemand A
WHERE NOT EXISTS (SELECT B.CustomerName FROM Customers B WHERE A.CustomerName = B.CustomerName)

Deploying Queries in Cosmic Frog Analytics

The Analytics module in Cosmic Frog allows you to display data from tables and views. Custom queries can be stored as views, enabling the analytics module to reference this virtual table to display results.  Creating a view follows a very similar query construct as a sub-query, but rather than layering in a select statement, you add CREATE VIEW viewname as ( query).

‍

Once created, a view can be selected with the Analytics module of Cosmic Frog.

Example 1: Create View – Creating an all-site view

CREATE VIEW V_All_Sites as
(  
  SELECT C.SiteName, C.city, C.Region, C.Country, C.Latitude, C.Longitude, C.Type  
  FROM (    
    SELECT A.CustomerName as SiteName, A.City, A.Region, A.Country, A.Latitude, A.Longitude, 'Cst' as Type    
    FROM customers A      
      UNION    
    SELECT B.FacilityName as SiteName, B.City, B.Region, B.Country,B.Latitude, B.Longitude, 'Fac' as Type    
    FROM Facilities B
  ) C
)

‍

Example 2: Delete View – Delete V_all_sites view

Drop VIEW v_all_sites

Altering Tables

SQL queries can also modify the contents and structure of data tables. This is a powerful capability, and the results, if improperly applied, cannot be undone.

‍

Table updates & modifications can be completed within Cosmic Frog, with the added benefit of the context of allowed column values. This can also be done within the SQL editor by executing UPDATE and ALTER TABLE SQL statements.

‍

Example 1: Modifying Tables – Adding Additional Notes Columns

ALTER TABLE Customers
ADD Notes_1 character varying (250)

‍

Example 2: Modifying Values – Updating Notes Columns

UPDATE Customers
SET Notes_1 = CONCAT(Country , '-' , Region)

‍
Example 3: Modifying Tables – Delete New Notes Columns

ALTER TABLE Customers
DROP COLUMN Notes_1

‍
Example 4: Copying Tables – Copy Customers Table

SELECT *
INTO Customers_1
FROM Customers

‍
Example 5: Deleting Tables – Delete Customers Table
DROP TABLE Customers_1

DROP TABLE Customers_1

‍

Visit https://www.postgresqltutorial.com/ for more information on PostgreSQL query syntax.

Trouble Receiving Account Confirmation Email

A confirmation email is sent following account creation, however this email could potentially be blocked due to an organization’s IT policies. If you are failing to receive your confirmation email, please make sure that www.optilogic.com is whitelisted, as well as the following email address: support=www.optilogic.com@mail.www.optilogic.com.

If possible, please request that a wildcard whitelist be established for all URL’s that end in *.optilogic.app.

After confirming that these have been whitelisted, try and send another confirmation email. If the problem persists, please send a note in to support@optilogic.com.

One of Cosmic Frog’s great competitive features is the ability to quickly run many sensitivity analysis scenarios in parallel on Optilogic’s Cloud-based platform. This built-in Sensitivity at Scale (S@S) functionality lets a user run sensitivity on demand quantity and transportation costs with 1 click of a button, on any scenario using any of Cosmic Frog’s engines. In this documentation, we will walk through how to kick-off a S@S run, where to track the status of the scenarios, and show some example outputs of S@S scenarios once they have completed running.

Starting S@S Scenarios

Kicking off a S@S analysis is simply done by clicking on the green S@S button on the right-hand side in the toolbar at the top of Cosmic Frog:

After clicking on the S@S button, the Run Sensitivity at Scale screen comes up:

1. User can select the Resource Size that they deem most appropriate for the scenarios to be run on. Larger resource sizes have more CPUs and more RAM available; however, they also have higher billing factors. Therefore, the aim is usually to choose a resource size as small as possible to limit the number of hours used for running the scenarios, while still being large enough for the scenarios to not run out of memory. A good strategy is to first run 1 scenario (the scenario that the sensitivity is to be performed on for example), review its CPU and Memory usage in the Run Manager, and then decide which resource size is best to use for the sensitivity scenarios. More guidance on Resource Size selection and assessment can be found in this help article “Resource Size Selection (Neo)”.
2. To facilitate finding the scenario(s) to run, users can type into the Search box to filter the scenario list for scenarios that contain the entered text.
3. Clicking on this icon with the horizontal bars allows the user to sort the scenario list. Two sort options are available:
   1. Default: the Baseline scenario, which is running the model with all the input tables as is, will be listed first, then followed by the scenarios in the order they were added to the model.
   2. A-Z Name: this sorts the scenario list in alphabetical order. Clicking on this sort option again sorts the list in reverse alphabetical order.
4. The scenario(s) to run S@S on can be selected by checking their checkboxes in the scenario list. Here 2 scenarios have been selected: Sc1d_Site selection, a network optimization (Neo) scenario, and Sc1c Greenfield 3 New Sites, a Greenfield (Triad) scenario.
5. At the bottom of the scenario list, it is summarized how many scenarios have been selected.
6. Underneath the scenario selection list, a note tells the user how many scenarios will be run, and which model values will be changed. In this case 100 scenarios in total will be run:
   1. The 2 selected in the list, Sc1d_Site selection and Sc1c Greenfield 3 New Sites.
   2. Sensitivity on each of the 2 selected scenarios where Transportation Unit Cost is varied from -15% to +15% in 5% increments and Demand Quantity is also varied in this same manner. So, 7 values for each are used (-15%, -10%, -5%, 0%, 5%, 10%, and 15%), which leads to 7 * 7 = 49 sensitivity scenarios for each selected scenario.
7. When ready to start running the sensitivity at scale scenarios, user can click on the Run button. Should user decide to not run the S@S scenarios at this time, they can close the Run Sensitivity at Scale screen without kicking off any runs by clicking on the Cancel button.

Please note that the parameters that are configured on the Run Settings screen (which comes up when clicking on the Run button at the right top of Cosmic Frog) are used for the Sensitivity at Scale scenario runs.

The scenarios are then created in the model, and we can review their setup by switching to the Scenarios module within Cosmic Frog:

1. In the search box “Sc1d” was typed in to find all scenarios that contain this text.
2. This is scenario Sc1d_Site Selection, which was 1 of the 2 original scenarios selected to run S@S scenarios for. We see that it has 5 scenario items assigned to it and that it is a network optimization (Neo) scenario.
3. Then we see 49 more scenarios in the list (not all shown in the screenshot), which all start with Sc1d_Site selection. The rest of the scenario name indicates which fields in which tables were changed and by what percentage. The name of the scenario outlined in green is “Sc1d_Site Selection TransportationPolicies.UnitCost by -15.0 CustomerDemand.Quantity by -15.0”. So, this is the sensitivity scenario where both the transportation cost and the demand quantity are reduced by 15% as compared to the values in the input tables.
4. The sensitivity scenarios each have all 5 items the original scenario had assigned to it also assigned to them.
5. In addition, the sensitivity scenario items have been created and been assigned to the correct sensitivity scenarios.

As an example of the sensitivity scenario items that are being created and assigned to the sensitivity scenarios as part of the S@S process, let us have a look at one of these newly created scenario items:

1. The “TransportationPolicies.UnitCost by -15.0” scenario item has been selected and its configuration is shown on the right-hand side.
2. Table Name: Transportation Policies – the table the change is being made to.
3. Actions: UnitCost = UnitCost * 0.85 – the change that is being made, which is to multiply the current value in the Unit Cost field by 0.85, so it is reduced by 15%.
4. Conditions: none are applied here – the change is being made to all records in the transportation policies table.

Once the sensitivity scenarios have been created, they are kicked off to all be run simultaneously. Users can have a look in the Run Manager application on the Optilogic platform to track their progress:

1. On the Optilogic platform, select Run Manager as the application to open on the left-hand side of the screen. Should the Run Manager icon not be visible, then click on the icon with 3 dots (not shown here), which will show any applications that were not visible yet.
2. The overall job for this batch of Sensitivity at Scale scenarios is listed here, with the identifier of “frogspawn” for S@S added to it in parenthesis.
3. Each of the individual sensitivity scenarios that are being run is listed separately as a job too. Hovering over the Job Name will show the complete name in the blue tooltip. State will indicate whether the scenario is running still or if it has already completed. State will indicate “Error” in case the scenario could not run to completion. User can also view job info such as (error) logs, resource usage, etc. for the selected scenario on the right-hand side of the screen (not shown in screenshot above).

S@S Scenario Outputs

Once a S@S scenario finishes, its outputs are available for review in Cosmic Frog. As with other models and scenarios, users can review outputs through output tables, maps, and graphs/charts/dashboards in the Analytics module. Here we will just show the Optimization Network Summary output table and a cost comparison chart as example outputs. Depending on the model and technology run, users may want to look at different outputs to best understand them.

1. The Sc1d_Site selection scenario and its sensitivity scenarios were run using the network optimization (Neo) engine. A good starting point for reviewing outputs is the Optimization Network Summary output table.
2. The table has been filtered to only show the Sc1d scenario and its sensitivity scenarios, so 50 rows are visible in the grid (not all are captured in the screenshot).
3. We have chosen to focus on a few columns in this table to start understanding the outputs: Scenario Name, Solve Time, Total Supply Chain Cost (sorted in ascending order), and Total Served Demand Quantity. As generally expected, the scenario with both the demand and transportation costs reduced by 15% (the first one in the list) has the lowest overall supply chain cost.

To understand how the costs are divided over the different cost types and how they compare by scenario, we can look at following Supply Chain Cost Detail graph in the Analytics module:

1. For each scenario, a bar is drawn in the chart where the colors indicate the cost bucket: blue for fixed operating cost, green for transportation cost, yellow for sourcing cost, and red for processing cost. What stands out here is that even though the cost sensitivity was on transportation costs, these do not make up the majority of the total cost, which are mostly driven by sourcing and processing costs. Since the sourcing costs are unit based, the scenarios where there is less demand are the ones with the lowest sourcing cost and therefore lowest overall total supply chain cost.
2. Hovering over an individual bar in the chart shows the detailed cost breakdown for the particular scenario.

Optimization (NEO) will read from all 5 of input tables in the Sourcing section of Cosmic Frog.

• Customer Fulfillment Policies
• Replenishment Policies
• Procurement Policies
• Production Policies
• Supplier Capabilities

We are able to use these tables to define the sourcing logic that describes costs and where a product can be introduced into the network through production at a Facility (Production Policies) or by way of a Supplier (Supplier Capabilities). We can also define additional rules around how a product must be sourced using the Max Sourcing Range and Optimization Policy fields in the Customer Fulfillment, Replenishment, and Procurement Policies tables.

Max Sourcing Range

The Max Sourcing Range field can be used to specify the maximum flow distance allowed for a listed location / product combination. If flow distances are not specified in the Distance field of the Transportation Policies table, a straight-line distance will be calculated based on the Origin / Destination geocoordinates. This will take into account the Circuity Factor specified in the Model Settings as a multiplication factor to estimate real road distances. Any transportation distances that exceed the Max Sourcing Range will result in the arcs being dropped from consideration.

Optimization Policy

There are 4 allowable entries for Optimization Policy. For any given Destination / Product combination, only a single Optimization Policy entry will be supported meaning you can not have one source listed with a policy of Single Source and another as By Ratio (Auto Scale).

To Optimize

This is the default entry that will be used if nothing is specified. To Optimize places no additional logic onto the sourcing requirement and will use the least cost option available.

Single Source

For the listed destination / product combination, only one of the possible sources can be selected.

By Ratio (Auto Scale)

This option allows for sources to be split by the defined ratios that are entered into the Optimization Policy Value field. All of the entries into this Policy Value field will be automatically scaled, and the flow ratios will be followed for all inbound flow to the listed destination / product combination.

‍

For example, there are 3 potential sources for a single Customer location. There is a flow split enforced of 50-30-20 from DC_1, DC_2, DC_3 respectively. This can be entered as Policy Values of 50, 30, and 20:

The same sourcing logic could be achieved by entering values of 5, 3, 2 or even 15, 9, 6. All values will be automatically scaled for each valid source that has been defined for a destination / product combination.

By Ratio (No Scale)

Similar to the Auto Scale option, By Ratio (No Scale) allows for sources to be split by the defined ratios entered into the Optimization Policy Value field. However, no scaling will be performed and the Optimization Policy Value fields will be treated as absolute sourcing percentages where an entry of 50 means that exactly 50% of the inbound flow will come from the listed source.

For example, there are 3 possible sources for a single Customer location and we want to enforce that DC_1 will account for exactly 50% of the flow while the remainder can come from any valid location. We can specify that DC_1 will have a Policy Value of 50 while leaving our other options open for the model to optimize.

If Policy Values add up to less than 100 for a listed destination / product combination, another sourcing option must be available to fulfill the remaining percentage.

If Policy Values add up to more than 100 for a listed destination / product combination, the percentages will be scaled to 100 and used as the only possible sources.

You can create a free account on the Optilogic platform, which includes Cosmic Frog, in just a few clicks. This document shows you two ways in which you can do this. Use the first option if you have Single Sign On (SSO) enabled for your Google (Gmail) or Microsoft account and you want to use this to log into the Optilogic platform.  

This video posted on the Optilogic Training website also covers account creation and then goes into how to navigate Cosmic Frog, a good starting point for new users.

Option 1: Account Creation via signup.optilogic.app

To create your free account, go to signup.optilogic.app. This will automatically re-direct you to a Cosmic Frog Log In page:

1. If you have Single Sign On enabled for a Microsoft account, you can click on “Continue with Microsoft”.
2. If you have Single Sign On enabled for a Google account, you can click on “Continue with Google”.
3. If you do not use Microsoft or Google Single Sign On, you can click on the Sign Up link at the bottom, and then continue the Account Creation steps which are the same as described further below in this document in the “Option 2: Account Creation via www.optilogic.com” section.

Steps for the “Continue with Microsoft” Option

First, we will walk through the steps of continuing with Microsoft where the user has Single Sign On enabled for their Microsoft account and has clicked on “Continue with Microsoft”. In the next section we will similarly go through the steps for using SSO with a Google account.

After the user has clicked on “Continue with Microsoft”, the following page will be brought up. Click on Accept to continue if the information displayed is correct.

You will see the following message about linking your Microsoft account to your Optilogic account:

Go into your email and find the email with subject “Link Microsoft”, and click on the Link Account button at the bottom of this email:

Should you not have received this email, you can click on “Resend Email”. If you did receive it and you have clicked on the Link Account button, you will be immediately logged into www.optilogic.com and will see the Home screen within the platform, which will look similar to the below screenshot:

From now on, you have 2 options when logging into the Optilogic platform via cosmicfrog.com (see the first screenshot in this documentation): you can log in by clicking on the “Continue with Microsoft” option which will immediately log you in or you can type your credentials into the username / email and password fields to manually log in.

Steps for the “Continue with Google” Option

After the user has clicked on “Continue with Google”, the following page will be brought up. If you have multiple Google email addresses, click on the one you want to use for logging into the Optilogic platform. If the email you want to use is not listed, you can click on “Use another account” and then enter the email address.

If the email you choose to use is not signed in on the device you are on currently, you will be asked for your password next. Please provide it and continue. If it is the first time you are using the email address to log into the Optilogic platform, you will be asked to verify it in the next step:

The default verification method associated with the Google account will be suggested, which in the example screenshot above is to send the verification code to a phone number. If other ways to verify the Google account have been set up, you can click on “More ways to verify” to change the verification method. If you are happy with the suggested method, click on Send. Once you have hit Send, the following form will come up:

You can again switch to another verification method in this screen by clicking on “More ways to verify”, or, if you have received the verification code, you can just enter it into the “Enter the code” field and click on Next. This will log you into the Optilogic platform and you will now see the Home screen within the platform, which will look similar to the last screenshot in the previous section (“Steps for the “Continue with Microsoft” Option”).

From now on, you have 2 options when logging into the Optilogic platform via cosmicfrog.com (see the first screenshot in this documentation): you can log in by clicking on the “Continue with Google” option which will immediately log you in after you have selected the Google email address to use, or you can type your credentials into the username / email and password fields to manually log in.

Option 2: Account Creation via www.optilogic.com

To create your free account, go to www.optilogic.com and click on the yellow “Create a Free Account” button.

The following form will be brought up, please fill out your First Name, Last Name, Email Address, and Phone Number. Then click on Next Step.

Your entered information will be shown back to you, and you can just click on Next Step again. Next, a form where you can set your Username and Password will come up. Click on Next Step again once this form is filled out.

In the final step you will be asked to fill out your Company Name, Role, Industry, and Company Size. Click on Submit after you have filled out these details.

A submission confirmation will pop up with instructions to verify your email address. Once you have verified your email address you can immediately start using your free account!

Cosmic Frog for Excel Applications provide alternative interfaces for specific use cases as companion applications to the full Cosmic Frog Supply chain design product. For example, they can be used to access a subset of the Cosmic Frog functionality in a simplified manner or provide specific users who are not experienced in working with Cosmic Frog models access to a subset of inputs and/or outputs of a full-blown Cosmic Frog model that are relevant to their position.

Several example use cases are:

• Allowing users such as transportation planners to interact with a transportation optimization (Hopper) model through a simple user interface in Excel. Simply update Shipment quantities and review the outputs, including routes on a map, all in Excel.
• An App that geocodes locations and shows them on a Map, where the markers are optionally scaled by an attribute.
• Easily set up sensitivity analysis for demand and supply: increase/decrease demand based on certain customer or product factors and increase/decrease the capacity of facilities based on multiple factors too (location, type of facility), run multiple versions and compare outputs.
• Create all input data for a Cosmic Frog model in Excel (e.g. Greenfield) and run from there, including getting the outputs required read back in and showing those on a map.
• Quickly set up scenarios and run them from the App.
• Output viewers for different types of uses, e.g. executive summaries vs detailed outputs of certain supply chain areas for planners.

It is recommended to review the Cosmic Frog for Excel App Builder before diving into this documentation, as basic applications can quickly and easily be built with it rather than having to edit/write code, which is what will be explained in this help article. The Cosmic Frog for Excel App Builder can be found in the Resource Library and is also explained in the “Getting Started with the Cosmic Frog for Excel App Builder” help article.

Here we will discuss how one can set up and use a Cosmic Frog for Excel Application, which will include steps that use VBA (Visual Basic for Applications) in Excel and scripting using the programming language Python. This may sound daunting at first if you have little or no experience using these. However, by following along with this resource and the ones referenced in this document, most users will be able to set up their own App in about a day or 2 by copy-pasting from these resources and updating the parts that are specific to their use case. Generative AI engines like Chat GPT and perplexity can be very helpful as well to get a start on VBA and Python code. Cosmic Frog functionality will not be explained much in this documentation, the assumption is that users are familiar with the basics of building, running, and analyzing outputs of Cosmic Frog models.

In this documentation we are mainly following along with the Greenfield App that is part of the Resource Library resource “Building a Cosmic Frog for Excel Application”. Once we have gone through this Greenfield app in detail, we will discuss how other common functionality that the Greenfield App does not use can be added to your own Apps.

There are several Cosmic Frog for Excel Applications that have been developed by Optilogic available in the Resource Library. Links to these and a short description of each of them can be found in the penultimate section “Apps Available in the Resource Library” of this documentation.

Throughout the documentation links to other resources are included; in the last section “List of All Resources” a complete list of all resources mentioned is provided.

High-level Overview of Steps to Build a Cosmic Frog for Excel Application

The following screenshot shows at a high-level what happens when a typical Cosmic Frog for Excel App is used. The left side represents what happens in Excel, and on the right side what happens on the Optilogic platform.

1. A lot of the work to create a Cosmic Frog for Excel App will be done in Excel like entering and configuring any inputs that the Cosmic Frog model needs to be updated with.
2. Once all the inputs and any needed settings are configured, the data will typically be exported, often either in CSV or TXT format.
3. The next step is to upload the data to the Optilogic platform.
4. A Python script is used on the Optilogic platform to run the Job, which will often involve updating, running, and/or analyzing a Cosmic Frog model.
5. Once the job is complete on the Optilogic platform, the data (typically outputs of a Cosmic Frog model) that we require will be exported, again often to CSV or TXT format, and downloaded from the Optilogic platform.
6. The outputs are loaded back into Excel, which may require conversion from Unix to Windows format.

Building your App – Excel Worksheets

A typical Cosmic Frog for Excel Application will contain at least several worksheets that each serve a specific purpose. As mentioned before, we are using the MicroAPP_Greenfield_v3.xlsm App from the Building a Cosmic Frog for Excel Application resource as an example. The screenshots in this section are of this .xlsm file. Depending on the purpose of the App, users will name and organize worksheets differently, and add/remove worksheets as needed too:

1. Admin – a worksheet where:
   1. The App Key can be entered before running the App for the first time. An App Key is required in order to authenticate the user on the Optilogic platform. See the section “App Keys and the app.key File” further below for more details.
   2. The local path to the Excel workbook if the Excel workbook is for example in an online drive – this is needed to be able to create an app.key file in the same folder as the Excel workbook.
   3. A high-level description of the App, including its purpose, what inputs are required, and what outputs it will provide.
   4. User instructions telling the user what inputs to update and how to run the App.
   5. A set-up guide: steps to get the Excel App working the first time, e.g. where to put files related to the App, what information to provide where for set-up, etc.

2. Cosmic Frog model input data worksheets: 1 or multiple worksheets where data that needs to be uploaded into a Cosmic Frog model is entered. This could be data that together makes up a complete Cosmic Frog model or it could be a subset of Cosmic Frog model data that will be used to update an already existing Cosmic Frog model. Some Apps, for example output viewer Apps, may not have any of these. Here there are 3: Customers, Suppliers, and Facilities. These 3 worksheets contain data to populate an empty model on the Cosmic Frog platform, which will then be used for a Greenfield run. The Customers worksheet contains:
   1. Customer Name data – this will be used to populate the Customers and the Customer Demand tables in Cosmic Frog.
   2. Latitude & Longitude for each customer – this will be used to populate the Customers table; this is required to run Greenfield so the algorithm can calculate distances between possible source-destination pairs.
   3. Quantity – this will be used to populate the Customer Demand table in Cosmic Frog, where Greenfield takes this into account to calculate Fulfillment and Procurement Costs (both costs are on a per unit per mile/kilometer basis).

3. Settings: any specific settings that are needed to run the Cosmic Frog model are typically specified here. For Greenfield these are shown in the screenshot above.
   1. Greenfield specific settings that are taken from this section and put into the Greenfield Settings table in the Cosmic Frog model.
   2. Resource Size and File name for results – these are model run options that the user can specify here. Resource Size sets what size of solver will be used for the Greenfield run and “File name for results” will be part of the file name of the output files that will be downloaded from Cosmic Frog into the folder where the Excel workbook is saved. This is so users know which results are from which Greenfield run.
   3. For Cosmic Frog model runs using different engines (e.g. NEO for optimization, HOPPER for transportation optimization, etc.) you can also put relevant Model Run Options that the user may want control over on this Settings tab (those shown on the Run screen in Cosmic Frog after clicking on the Run button). For example, turning on/off Load Balancing for Hopper models could be an option that can be set on the Settings worksheet.

4. A “Run …” worksheet:
   1. This worksheet will often contain a button that can be clicked to kick off the App’s Macro which performs the steps of exporting and uploading data to the Optilogic platform, including the Python Job that will be run on the Optilogic platform, and the steps of what to do with the outputs of the Job once it has completed on the Optilogic platform. We will discuss building and assigning Macros in the next section. The contents of the Macro assigned to the Run Greenfield button are also covered in a lot of detail in a later section.
   2. Status updates can be shown on this worksheet too.
5. Cosmic Frog model output data worksheets: 1 or multiple worksheets where select outputs are read back into after a Job has completed. In the MicroApp_Greenfield_v3.xlsm that we are using as an example here, the outputs are just downloaded as CSV files and not read back into the Excel workbook, so we do not see these in this example, but we will cover how this can work in a later section titled “Reading CSV Outputs Back into the App Workbook”.
6. Worksheets with other Cosmic Frog model outputs such as Maps, which are again not used in this example, but can be and will be discussed in a later section titled “Using Maps”.

Excel: Building and Assigning Macros

To set up and configure Cosmic Frog for Excel Applications, we mostly use .xlsm Excel files, which are macro-enabled Excel workbooks. When opening an .xlsm file that for example has been shared with you by someone else or has been downloaded from the Optilogic Resource Library (Help Article on How To Use the Resource Library), you may find that you see either a message about a Protected View where editing needs to be enabled or a Security Warning that Macros have been disabled. Please see the Troubleshooting section towards the end of this documentation on how to resolve these warnings.

‍

To set up Macros using Visual Basic for Applications (VBA), go to the Developer tab of the Excel ribbon:

If the Developer option is not available in the ribbon, then go to File > Options > Customize Ribbon, select Developer from the list on the left and click on the Add >> button, then click on OK. Should you not see Options when clicking on File, then click on “More…” instead, which will then show you Options too.

Now that you are set up to start building Macros using VBA: go to the Developer tab, enable Design Mode and add controls to your sheets by clicking on Insert, and selecting any controls to insert from the drop-down menu. For example, add a button and assign a Macro to it by right clicking on the button and selecting Assign Macro from the right-click menu:

1. Now you can create a new Macro that will be assigned to this button by clicking New, or
2. You can select another workbook or any open workbook, to select a Macro from to assign to “Button 1”.
3. Here the Say_Hello Macro from the workbook MicroApp_BestPractices_v3.xlsm is selected. Once an existing Macro is selected, the New button changes to Edit and clicking on it opens the Macro in VBA:

1. The VBA Project of a workbook of which we are editing a Macro, here it is in the MicroApp_Best_Practices_v3.xlsm workbook.
2. The worksheet that contains the Macro we are editing, here it is the second sheet which is named Getting Started.
3. In the drop-down on the right top of the VBA editor we see that the Sub named Say_Hello is selected from the Subs that are contained in the worksheet. To view which other Subs are available in this worksheet, click on the drop-down menu.
4. This Sub is Public meaning it can be used by any Macro-enabled workbook, its name is Say_Hello, and what it does is show a message box with the text “Hello World” as shown in the next screenshot, taken after clicking on Button 1:

To learn more about Visual Basic for Applications, see this Microsoft help article Getting started with VBA in Office, it also has an entire section on VBA in Excel.

The Optilogic.bas VBA Module

It is possible to add custom modules to VBA in which Sub procedures (“Subs”) and functions to perform specific tasks have been pre-defined and can be called in the rest of the VBA code used in the workbook where the module has been imported into. Optilogic has created such a module, called Optilogic.bas. This module provides 8 standard functions for integration into the Optilogic platform.

You can download Optilogic.bas from the Building a Cosmic Frog for Excel Application resource in the Resource Library:

You can then import it into the workbook you want to use it in:

Right click on Modules in the VBA Project of the workbook you are working in and then select Import File…. Browse to where you have saved Opitlogic.bas and select it. Once done, it will appear in the Modules section, and you can double click on it to open it up:

1. 1. The drop-down at the right top of the VBA window contains a list of all Sub procedures and functions defined in the Optilogic Module.
   2. These are the 8 Subs and functions contained in the module, you can jump to any 1 of them by selecting it from the drop-down list. Here follows an explanation of these Subs and functions, and the arguments they take. Users do not need to know the actual code of these, just what they can be used for and what their syntax is:
      1. Upload_File_To_Optilogic (localFilePath, localFileName, platformFilePath, platformFileName, appKey) – this will upload a file from user’s local machine to the Optilogic platform. The arguments are as follows:
         1. localFilePath = File path on the local machine
         2. localFileName = Name of the local data file to be uploaded
         3. platformFilePath = File path in the platform workspace. You can get this from the Explorer (1) on the Optilogic platform (click on the > button at the top left to open it up): right click on the folder and select Copy (2) > Folder Path (3):

1. 1. 1. 1. 4. platformFileName = Name of the data file once it is uploaded to the platform workspace. Name can be different to local file name
            5. appKey = User’s App Key for authentication. See the section “App Keys and the app.key File” further below on how to get this key
      2. Download_File_From_Optilogic (platformFilePath, platformFileName, localFilePath, localFileName, appKey) – this will download a file from the Optilogic to user’s local machine. The arguments are as follows:
         1. platformFilePath = File path in the platform workspace. You can get this from Explorer, right click on the folder and select Copy > Folder Path, see also screenshot under bullet 2.a.iii above
         2. platformFileName = Name of the data file in the platform workspace
         3. localFilePath = File path on the local machine
         4. localFileName = Name of the local data file when downloaded. Name can be different to the platform workspace filename
         5. appKey = User’s App Key for authentication. See the section “App Keys and the app.key File” further below on how to get this key
      3. Run_Job_On_Optilogic (jobPath, jobName, appKey) – this will run a Job (a Python .py file) on the Optilogic platform. The arguments are as follows:
         1. jobPath = File path in the platform workspace where the Job (.py file) resides. You can get this from Explorer, right click on the folder and select Copy > Folder Path, see also screenshot under bullet 2.a.iii above
         2. jobName = Name of the Job (.py file) in the platform workspace
         3. appKey = User’s App Key for authentication. See the section “App Keys and the app.key File” further below on how to get this key
      4. Monitor_Job_On_Optilogic (jobKey, worksheet, interval, usrMsg, msgCell, statusCell, timeCell, appKey) – this monitors the job that is running on the Optilogic platform and can give the user regular updates on the status of the Job that is being run. The arguments are as follows:
         1. jobKey = Unique Job Key identification returned from Run_Job_On_Optilogic(…) function
         2. worksheet = Name of the Worksheet where the status information is being updated
         3. interval = The time waited between getting an update on the Job status from the platform in seconds. In this example status will be checked every 5 seconds
         4. usrMsg = A free form message that will be shown to the user during status updates
         5. msgCell = Cell reference in the Worksheet where the message is updated. For example B7
         6. statusCell = Cell reference in the Worksheet where the Job status is updated. For example B8
         7. timeCell = Cell reference in the Worksheet where the time of the last update is updated. For example B9
         8. appKey = User’s App Key for authentication. See the section “App Keys and the app.key File” further below on how to get this key
      5. Convert_Unix_File_To_Windows (unixFilePath, unixFileName, windowsFilePath, windowsFileName) – files downloaded from the Optilogic platform can be in Unix format and in order to be able to read them in a Windows program such as Excel, they may need to be converted, users can use this Sub procedure to do so. The arguments are as follows:
         1. unixFilePath = File path on the local machine. You should use Get_Workbook_File_Path() to set this
         2. unixFileName = Name of the local data file in Unix format
         3. windowsFilePath = File path on the local machine. You should use Get_Workbook_File_Path() to set this
         4. windowsFileName = Name of the local data file in Windows format
      6. Get_Workbook_File_Path (sheetName, pathCell, usrMsg) – gets the file path of the current workbook. It will attempt to retrieve this without any user input, however if the workbook is in an online folder, user may need to enter the local file path manually. The arguments are as follows:
         1. sheetName = Name of the sheet in the workbook that contains the file path that has been input by the user
         2. pathCell = Cell in the sheet that contains the file path that has been input by the user, for example B3
         3. usrMsg = The message that is to be displayed to the user should the file path be invalid
      7. Export_CSV_File (filePath, writeFileName, sheetName, firstRow, colRange) – this will export a certain range in a worksheet as a CSV file the Sub will dynamically determine how many rows need to be included in the export. The arguments are as follows:
         1. filePath = The file path on the local machine. You should use Get_Workbook_File_Path() to set this
         2. writeFileName = Name of the local data file that will be created or updated
         3. sheetName = Name of the sheet in the workbook that contains the data
         4. firstRow = Name of the column in which the dynamic row range should be selected, for example A
         5. colRange = Defines the column range, for example A1:F where the row value for F will be dynamic
      8. Manage_App_Key (sheetName, keyCell, usrMsg, filePath) – this function takes the App Key that user has entered into a certain cell on a certain worksheet in the workbook and puts it into an app.key file in the directory where the Excel .xlsm workbook is saved. It validates the App Key and if valid, replaces the App Key in the Excel file with following text “app key has been saved, you can keep running the App”, thus ensuring the App Key is not visible for others. The arguments are as follows:
         1. sheetName = Name of the sheet in the workbook that contains the App Key that has been input by the user
         2. keyCell = Cell in the sheet that contains the App Key that has been input by the user, for example B3
         3. usrMsg = The message that is to be displayed to the user should the App Key be invalid
         4. filePath = File path on the local machine.  You should use Get_Workbook_File_Path() to set this

These Optilogic specific Sub procedures and the standard VBA for Excel functionality enable users to create the Macros they require for their Cosmic Frog for Excel Applications.

App Keys and the app.key File

App Keys are used to authenticate the user from the Excel App on the Optilogic platform. To get an App Key that you can enter into your Excel Apps, see this Help Center Article on Generating App and API Keys.  During the first run of an App, the App Key will be copied from the cell it is entered into to an app.key file in the same folder as the Excel .xlsm file, and it will be removed from the worksheet. This is done by using the Manage_App_Key Sub procedure described in the “Optilogic.bas VBA Module” section above. User can then keep running the App without having to enter the App Key again unless the workbook or app.key file is moved elsewhere.

It is important to emphasize that App Keys should not be saved into Excel Apps as they can easily be accidentally shared when the Excel App itself is shared. Individual users need to authenticate with their own App Key.

When sharing an App with someone else, one easy way to do so is to share all contents of the folder where the Excel App is saved (optionally, zipped up). However, one needs to make sure to remove the app.key file from this folder before doing so.

Python Job File & requirements.txt

A Python Job file in the context of Cosmic Frog for Excel Applications is the file that contains the instructions (in Python script format) for the operations of the App that take place on the Optilogic Platform.

Notes on Job files:

• Jobs are Python scripts that run on the Optilogic Platform. They are Python scripts, but typically have a .job extension, so that inexperienced users do not accidentally open them and change the Python code
• A Job could do anything, from transforming data, running a custom engine or updating a Cosmic Frog model
• A Job can be built in Atlas on the Optilogic platform or using another, external IDE (Integrated Development Environment). A rich text editor geared towards coding, like for example Visual Studio Code, will work fine too for most Cosmic Frog for Excel App purposes
• Cosmic Frog has a Python library for easily building Python scripts against Optilogic’s supply chain data model, this library is called cosmicfrog and needs to be imported in order to be used. The functions that this library provides are explained in the next section titled “Getting Started with Python and Optilogic’s cosmicfrog Python Library”.

For Cosmic Frog for Excel Apps, a .job file is typically created and saved in the same folder as the Macro-enabled Excel workbook. As part of the Run Macro in that Excel workbook, the .job file will be uploaded to the Optilogic platform too (together with any input & settings data). Once uploaded, the Python code in the .job file will be executed, which may do things like loading the data from any uploaded CSV files into a Cosmic Frog model, run that Cosmic Frog model (a Greenfield run in our example), and retrieve certain outputs of interest from the Cosmic Frog model once the run is done.

For a Python job that uses functionality from the cosmicfrog library to run, a requirements.txt file that just contains the text “cosmicfrog” (without the quotes) needs to be placed in the same folder as the .job file. Therefore, this file is typically created by the Excel Macro and uploaded together with any exported data & settings worksheets, the app.key file, and the .job file itself so they all land in the same working folder on the Optilogic platform. Note that the Optilogic platform will soon be updated so that using a requirements.txt file will not be needed anymore and the cosmicfrog library will be available by default.

Like VBA, users and creators of Cosmic Frog for Excel Apps do not need to be experts in Python code, and will mostly be able to do the things they want to by copy-pasting from existing Apps and updating only the parts that are different for their App. In the greenfield.job section further below we will go through the code of the python Job for the Greenfield App in more detail, which can be a starting point for users to start making changes to for their own Apps. Next, we will provide some more details and references to quickly equip you with some basic knowledge, including what you can do with the cosmicfrog Python library.

Getting Started with Python and Optilogic’s cosmicfrog Python Library

There are a lot of helpful resources and communities online where users can learn everything there is to know about using & writing Python code. A great place to start is on the Python for Beginners page on python.org. This page also mentions how more experienced coders can get started with Python.

Working with Python Locally

Working locally on any Python scripts/Jobs has the advantage that you can make use of code completion features which helps with things like auto-completion, showing what arguments functions need, catch incorrect syntax/names, etc. An example set up to achieve this is for example one where Python, Visual Studio Code, and an IntelliSense extension package for Python for Visual Studio Code are installed locally:

• Visual Studio Code can be downloaded and installed from the “Download for Windows Stable Build” button on the https://code.visualstudio.com/ website or from the Microsoft store
• Python itself can be downloaded and installed from https://www.python.org/downloads/ or the Microsoft store
  • If asked to add Python to the PATH variable, say yes or check the box to do so
  • If you are unsure Python is already installed on your computer, you can type “python” into a command prompt (type “cmd” in your Windows search bar to open a command prompt). If Python is installed, the command prompt will return the text Python together with its version number and some additional information.
• Get IntelliSense (code completion) for Python in Visual Studio Code working with an extension package like this one https://marketplace.visualstudio.com/items?itemName=ms-python.python
  • Again, if asked to add to the PATH variable, say yes or check the box to do so

Once you are set up locally and are starting to work with Python files in Visual Studio Code, you will need to install the pandas and cosmicfrog libraries to have access to their functionality. You do this by typing following in a terminal in Visual Studio Code:

1. pip install pandas, then hit Enter
2. pip install cosmicfrog, then hit Enter

More experienced users may start using additional Python libraries in their scripts and will need to similarly install them when working locally to have access to their functionality.

If you want to access items on the Optilogic platform (like Cosmic Frog models) while working locally, you will likely need to whitelist your IP address on the platform, so the connections are not blocked by a firewall. You can do this yourself on the Optilogic platform:

1. After logging into the Optilogic platform on cosmicfrog.com, go to Cloud Storage.
2. If you do not see the Cloud Storage icon, click on the icon with the 3 dots to see more apps, and then click on the Cloud Storage icon which should be visible now.
3. Click on the Firewall tab, the 4^th tab across the top.
4. Your current IP address will be shown and pre-populated in a rule.
5. Configure the Rule: give it a Name if you so desire (can leave blank) and enter the Start and End IP addresses if you are whitelisting a range of IP addresses. You will only need to enter a Start IP if there is one static address you are connecting from. Click on the calendar to pick your own end date for the rule. Once done configuring the rule, you can click on the Add Rule button.
6. The rule that was added is listed at the bottom: it shows when the rule was created, the IP address(es) the rule applies to, and when the rule expires. Once the rule expires, you will need to create a new one to be able to keep connecting to the Optilogic platform with your local Python scripts.

A great resource on how to write Python scripts for Cosmic Frog models is this “Scripting with Cosmic Frog” video. In this video, the cosmicfrog Python library, which adds specific functionality to the existing Python features to work with Cosmic Frog models, is covered in some detail already. The next set of screenshots will show an example using a Python script named testing123.py on our local set-up. The first screenshot shows a list of functions available from the cosmicfrog Python library:

1. The FrogModel module from the cosmicfrog Python library is imported on the first line (“from cosmicfrog import FrogModel”), this is needed to be able to use the specific functions in the cosmicfrog library that can work with Cosmic Frog models (line 1).
2. A variable named “model” is created and the FrogModel function is used to point it to a model named Global Risk Analysis that is in my Optilogic account (line 3). Now whenever the Python code does something with “model” it is applied to the Global Risk Analysis model.
3. The list of functions available in the FrogModel module of the cosmicfrog Python library (lines 5-28).

When you continue typing after you have typed “model.” the code completion feature will auto-generate a list of functions you may be getting at. In the next screenshot ones that start with or contain a “g” as I have only typed a “g” so far. This list will auto-update the more you type. You can select from the list with your cursor or arrow up/down keys and hitting the Tab key to auto-complete:

When you have completed typing the function name and next type a parenthesis ‘(‘ to start entering arguments, a pop-up will come up which contains information about the function and its arguments:

1. A short description of what the function does.
2. A description of each argument of the function.
3. The information given after the -> arrow tells us what type of output this function generates, a list of strings in this case.

As you type the arguments for the function, the argument that you are on and the expected format (e.g. bool for a Boolean, str for string, etc.) will be in blue font and a description of this specific argument appears above the function description (e.g. above box 1 in the above screenshot). In the screenshot above we are on the first argument input_only which requires a Boolean as input and will be set to False by default if the argument is not specified. In the screenshot below we are on the fourth argument (original_names) which is now in blue font; its default is also False, and the argument description above the function description has changed now to reflect the fourth argument:

The next screenshot shows 2 examples of using the get_tablelist function of the FrogModel module:

1. In this example, the 3^rd argument for technology_filter is set to NEO, which means network optimization tables will be returned only. Tables that are not used by NEO, but only other technologies will not be returned. The 1^st, 2^nd, and 4^th arguments are set to ‘’, which means the defaults for these will be used:
   1. First argument: input_only – the default is False, meaning that the list of tables returned will not only be model input tables (like Customers, Facilities, Customer Demand, Transportation Policies, etc.).
   2. Second argument: output_only – the default is False, meaning that the list of tables returned will not only be model output tables (like Optimization Network Summary, Optimization Facility Summary, etc.). So, in this case all network optimization input and output tables will be returned.
   3. Fourth argument: original_names – the default is False, meaning that the table names as they are in the underlying database will be returned (e.g. “customerdemand”), not the names as they are used in the Cosmic Frog User Interface (e.g. “CustomerDemand”). Differences are mainly in capitalization of the names.
2. In this example only Greenfield output tables will be returned, and the names will be as they are in the Cosmic Frog user interface. This is because the arguments are set as follows:
   1. First argument: input_only – is set to the default of False by using ‘’, which means the list of tables returned will not only be model input tables.
   2. Second argument: output_only – set to True, which means the list of tables returned will only be model output tables.
   3. Third argument: technology_filter – set to “TRIAD” which is the Greenfield engine in Cosmic Frog. For reference the names of the technologies in Cosmic Frog are:
      1. NEO: network optimization
      2. THROG: simulation
      3. TRIAD: Greenfield
      4. HOPPER: transportation optimization
   4. Fourth argument: original_names – set to True, meaning that the table names as they appear in the Cosmic Frog user interface will be returned.

Working with Python in Atlas on the Optilogic Platform

As mentioned above, you can also use Atlas on the Optilogic platform to create and run Python scripts. One drawback here is that it currently does not have code completion features like IntelliSense in Visual Studio Code.

The following simple test.py Python script on Atlas will print the first Hopper output table name and its column names:

1. On the Optilogic platform on cosmicfrog.com, go to Atlas. Note that the order of the applications available on the left-hand side on the Optilogic platform may be different for you as compared to what is shown here. If you do not see Atlas, then click on the icon with 3 dots to show all applications, and then click on Atlas.
2. The test.py script. It:
   1. Assigns a Cosmic Frog model named Transportation Optimization as the “model” variable (line 7).
   2. Uses the get_tablelist function with arguments False (input_only), True (output_only), “HOPPER” (technology_filter) and True (original_names), so that the output of this function will be the list of Hopper ( = transportation optimization) output tables where the names will be as they are in the Cosmic Frog UI. This is assigned to a variable named “my_list” (line 9).
   3. Assigns the first table name from the list created under bullet b. to a variable named “first_table” (line 10).
   4. Prints the name of this first table (line 11).
   5. Prints the names of the columns in this table, by using the cosmicfrog get_columns function (line 12).
3. The script runs when you click on the Run In Studio button at the top. In this case, the output will be shown in a window below the Python script.
4. Instead of running the script by using the Run In Studio option as shown in the previous bullet, you can instead also use the Run As Job button. In this case the progress and outputs of it will be shown in the Run Manager app of the Optilogic platform, see next screenshot:

1. After clicking on Run As Job, switch to the Run Manager app.
2. The Job will appear in the list of items that have been run, the State will indicate if it is still busy running or already done. It will say Error if anything has gone wrong while running the Job.
3. On the right hand-side of the screen there are multiple logs where information about the run can be found. Clicking on the Job Log item (the 4^th one at the top), you will see the outputs of the test.py Python script (note that not all column names have been captured in this screenshot).

Summary of Local and Uploaded Files

After running the Greenfield App, we can see the following files together in the same folder on our local machine:

1. xlsm – the Macro-enabled Excel workbook that contains the App Key, the data we want to upload to the Cosmic Frog model, and the Run Greenfield Macro.
2. job – the Python file that contains the code of what needs to be done on the Optilogic platform: load data into a new model, run Greenfield on the model, and retrieve specific output tables.
3. key – the first time the Run Greenfield macro is run, this file is created so the user can be authenticated on the Optilogic platform.
4. txt – contains the text cosmicfrog so the cosmicfrog Python library can be used by the Python code in the .job file.
5. These 4 .csv files are created as one of the first steps when the Run Greenfield Macro is run, these are the data exported from the Customers, Facilities, Suppliers, and Settings worksheets in the .xlsm file.
6. Once the Job has finished running on the Optilogic platform, the results will be downloaded into 2 files, which are named Results – “file name for results” (set on Settings worksheet) – “name of Cosmic Frog Greenfield output table”. Here the Optimization Greenfield Facility Summary and Optimization Greenfield Flow Summary output tables are downloaded as .csv files. User can then open these to analyze the Greenfield results.

On the Optilogic platform, a working folder is created by the Run Greenfield Macro. This folder is called “z Working Folder for Excel Greenfield App”. After running the Greenfield App, we can see following files in here:

1. Files that were uploaded from the local machine as is: app.key for user authentication, requirements.txt for the Python file to run properly, and the 4 .csv files with the date to update the Cosmic Frog model with.
2. py – the Python file that will execute on the Optilogic platform, renamed from Greenfield.job when the file was uploaded to the Optilogic platform by the Run Greenfield Macro. It needs to .py extension so that it is recognized as a Python script file.
3. The .csv files that contain the results of the Greenfield run that we want to download to the local machine. Note their names are slightly different here than what they end up being on the local machine, where the “File name for results” that the user has set on the Settings worksheet in the .xlsm workbook is added in, which is done by the Run Greenfield Macro when downloading the files from the Optilogic platform.

Run Greenfield Macro

Parts of the Excel Macro and Python .job file will be different from App to App based on the App’s purpose, but a lot of the content will be the same or similar. In this section we will step through the Macro that is behind the Run Greenfield button in the Cosmic Frog for Excel Greenfield App that is included in the “Building a Cosmic Frog for Excel Application” resource, where it will be explained what is happening at a high level each step of the way and mention if this part is likely to be different and in need of editing for other Apps or if it would typically stay the same across most Apps. After stepping through this Excel Macro in this section, we will the same for the Greenfield.job file in the next section.

The next screenshot shows the first part of the VBA code of the Run Greenfield Macro:

1. We are in the MicroApp_Greenfield_v3.xlsm (1a) Macro-enabled Excel workbook (the Cosmic Frog for Excel Greenfield Application), on the Run Greenfield worksheet (1b) and are looking at the VBA code for the RunGreenfield_click public Sub procedure (1c).
2. There is some error handling embedded within the Macro which will be called if an error occurs; we will see the actual error handling code in the last screenshot of this Macro.
   1. This can be left as is for most Apps. More advanced users may over time start adding their own, more elaborate error handling.
3. Dim is used to declare variables and their data type. Here 2 string variables are declared: filePath and writeFileName
   1. This can be kept as is for most Apps.
4. The next 3 sets of lines of code each check if certain settings have been selected by the user (Number of Facilities (4a), Only use Facilities (4b), and Resource Size (4c), respectively): they check if a certain cell (C5, C8, and C13, respectively) on a certain worksheet (Settings worksheet for all 3) is empty (= “”) and if so, pops up a message prompting the user to select a value. If the cells are not empty, the code continues without any messages.
   1. These will change depending on the App. In this case the first 2 are Greenfield specific, so for other technologies, you would not have these specific settings, but may have others where user input is required. Remove/update or add to these (and the corresponding cells on the Settings worksheet) depending on how many settings a user is required to select for the specific App. The Resource Size setting can be used for all technologies and may be left here. However, if this should not be set by a user, the Resource Size can be hardcoded into the Macro or the Python Job.

Note that throughout the Macro you will see text in green font. These are comments to describe what the code is doing and are not code that is executed when running the Macro. You can add comments by simply starting the line with a single quote and then typing your comment. Comments can be very helpful for less experienced users to understand what the VBA code is doing.

‍

Next, the file path to the workbook is retrieved:

This piece of code uses the Get_Workbook_File_Path function of the Optilogic.bas VBA module to get the file path of the current workbook. This function first tries to get the path without user input. If it finds that the path looks like the Excel workbook is stored online in for example a Cloud folder, it will use user input in cell B3 on the Admin worksheet to get the file path instead. Note that specifying the file path is not necessary if the App runs fine without it, which means it could get the path without the user input. Only if user gets the message “Local file path to this Excel workbook is invalid.  It is possible the Excel workbook is in a cloud drive, or you have provided an invalid local path.  Please review setup step 4 on Admin sheet.”, the local file path should be entered into cell B3 on the Admin worksheet.

This code can be left as is for other Apps if there is an Admin worksheet (the variable pathsheetName indicated with 1 in screenshot above) where in cell B3 the file path (the variable pathCell indicated with 2 in screenshot above) can be specified. Of course, the worksheet name and cell can be updated if these are located elsewhere in the App. The message the user gets in this case (set as pathusrMsg indicated with 3 in the screenshot above) may need to be edited accordingly too.

The following code takes care of the App Key management:

The Manage_App_Key function from the Optilogic.bas VBA module is used here to retrieve the App Key from cell B2 on the Admin worksheet and put it into a file named app.key which is saved in the same location as the workbook when the App is run for the first time. The key is then removed from cell B2 and replaced with the text “app key has been saved; you can keep running the App”. As long as the app.key file and the workbook are kept together in the same location, the App will keep working.

Like the previous code on getting the local file path of the workbook, this code can be left as is for other Apps. Only if the location of where the App Key needs to be entered before the first run is different from cell B2 on the worksheet named Admin, the keysheetName and keyCell variables (indicated with 1 and 2 in the screenshot above) need to be updated accordingly.

This App has a greenfield.job file associated with it that contains the Python script which will be run on the Optilogic platform when the App is run. The next piece of code checks that this greenfield.job file is saved in the same location as the Excel App, and it also sets the name of the folder to be created on the Optilogic platform where files will get uploaded to:

This code can be left as is for other Cosmic Frog for Excel Apps, except following will likely need updating:

1. The name of the .job file will usually be something descriptive of the App.
2. The name of the folder to be created on the Optilogic platform. As the files and model keep getting overwritten with each run of the Greenfield App, you will want to make sure to use a folder naming convention for your Apps that prevents it from accidentally overwriting anything that should be kept. Creating separate folders for each App and naming them something descriptive of the App will therefore be helpful. Having separate folders by App will also make troubleshooting easier.

The Greenfield settings are set in the next step. The ones the user can set on the Settings worksheet are taken from there and others are set to a default value:

1. First the progress of the App is updated on the Run Greenfield worksheet in cells C5 (the status message) and C6 (the current time).
   1. This can be left as is for other Apps, except for updating the name of the worksheet and the cells if these are different in your App. The message itself (“Exporting data locally…”) can of course be updated too.
2. This code checks if a file named Settings.csv is already present in the folder where the Excel App is located, and if so, deletes it.
   1. If a Settings.csv file is used for your Excel App, this code can be kept unchanged. It can be removed if no files need to be checked if they exist already, and it can be updated to check for the existence of any files by other names in the same location and delete those if present. Copy-paste the code and update it for each individual file you want to check and delete if it exists.
3. All Greenfield settings are created as string variables here. A design decision was made for the Greenfield App to clear out the Greenfield Settings table in the Cosmic Frog model entirely and re-populate with values each run of the Greenfield App. This is to prevent not updating any Settings that were changed in between App runs manually in the model itself.
   1. For other Greenfield Apps, this can be kept as is.
   2. For non-Greenfield Apps, these do not apply and can be removed or replaced by other variables for settings that the technology does use. An example would be to re-populate the Model Settings table either entirely or for certain settings for a network optimization (NEO) run. Think of inventory carrying cost %, circuity factor, average speed, or any of the primary UOM fields for example.
4. All Greenfield variables, except for the last 3 (see next screenshot) are set here. Either to a value set on the Settings worksheet in cells C5-C11 or to defaults when user cannot set them on the Settings worksheet of the Excel Greenfield App. Notes:
   1. If Then Else statements are used to set the values of 2 of the variables: MaxNumberOfNewFacilities and OnlyUseFacilitiesAsCandidateLocations. For example, if the value in cell C5 on the Settings worksheet is set to Optimize, the value of the MaxNumberOfNewFacilities variable becomes “”, meaning there is no upper limit on how many new facilities the Greenfield run can use, we are asking the algorithm to return the optimal number of new facilities. If cell C5 is not set to Optimize, it should contain a number and in this case that number will be set as the value of the MaxNumberOfNewFacilities variable.
   2. The Trim function is used frequently here to ensure leading and trailing spaces are not included when setting the variables to their values.
   3. Again, as mentioned under bullet 3b. it is dependent on the App what to remove, what to keep exactly as is, and what copy-paste plus update as needed here.

Next, the Greenfield Settings and the other input data are written into .csv files:

1. The Resource Size and Scenario Name are set in this part of the VBA code. These are not Greenfield specific and similar/same setup + code can be used to set these for other Cosmic Frog technologies too.
   1. The Resource Size is set in cell C13 on the Settings worksheet, and has a value from a drop-down list:

The firstSpaceIndex variable is set to the location of the first space in the resource size string.

1. 2. This firstSpaceIndex value is then used together with the Left function to get the first part of the Resource Size value: the part before the first space. E,g. a value of 4XS (1 Core 1 Gb RAM) in cell C13 will be written in as 4XS for the ResourceSize variable, M (6 Cores 16 GB RAM) becomes M, etc.

2. All Greenfield settings that have been set so far (except the Resource Size and Scenario Name) are now written into a Settings.csv file (note that the screenshot only captures the Print lines partially):
   1. The Open … For Output As #1 statement opens, or, when it does not yet exist, creates the Settings.csv file in the same location as the Excel App. The Mode of the Open function is set to Output which means that we can write to this file, including overwriting anything in it.
   2. The first Print statement writes the first line into the Settings.csv: it writes the column names in, which are the same as the Greenfield variable names.
   3. The second Print statement writes the second line to the Settings.csv: it writes the values of the Greenfield variables in, separated by commas.
   4. The Close statement closes the Settings.csv file.
   5. For other Excel Apps you will likely not need this exact same code unless the App also runs Greenfield. It is a good example however of how settings can be written into a .csv file using the Open, Print, and Close statements.
3. Here the Export_CSV_File Sub procedure is called 3 times to write the Customers, Facilities and Suppliers worksheets into .csv files. We will use the first for Customers as an example and go through the arguments of this Sub:
   1. 1^st argument – filePath: the file path on the local machine where the csv file will be created. Here set to the variable filepath, which is where the Excel App is located.
   2. 2^nd argument – writeFileName: the name of the local file that will be created or updated. Here Customers.csv will be created.
   3. 3^rd argument – sheetName: the name of the worksheet that contains the data. Here the data on the worksheet named Customers will be used.
   4. 4^th argument – firstRow: name of the column in which the dynamic row range should be selected. Here set to column A, which is the Customer Name column.
   5. 5^th argument – colRange: defines the column range where the row value for the last column will be dynamic. Here set to A1:D, meaning the data from columns A through D will be used and the Export_CSV_File Sub will dynamically determine what the last populated row is on the Customers worksheet and use that as the last row to include in the export.

Looking in the Greenfield App on the Customers worksheet we see that this means that the Customer Name (column A), Latitude (column B), Longitude (column C), and Quantity (column D) columns will be exported. The Customers.csv file will contain the column names on the first row, plus 96 rows with data as the last populated row is row 97. Here follows a screenshot showing the Customers worksheet in the Excel App (rows 6-93 hidden) and the first 11 lines in the Customers.csv file that was exported while running the Greenfield App:

Other Cosmic Frog for Excel Applications will often contain data to be exported and uploaded to the Optilogic platform to refresh model data; the Export_CSV_File function can be used in the same way to export similar and other tabular data.

As mentioned in the “Python Job File and requirements.txt” section earlier, a requirements.txt file placed in the same folder as the .job file that contains the Python script is needed so the Python script can run using functionality from the cosmicfrog Python library. The next code snippet checks if this file already exists in the same location as the Excel App, and if not creates it there, plus writes the text cosmicfrog into it.

This code can be used as is by other Excel Apps.

The next step is to upload all the files needed to the Optilogic platform:

1. A variable named appKeyFileName is created and set to app.key. This code can be kept as is in other Excel Apps.
2. Cells C5 and C6 on the Run Greenfield worksheet are updated to a different message and the current time. This code can be kept as is in other Excel Apps, just the worksheet name and cells need to be updated if they are different. Also, additional status updates can be set up in a similar way (copy-paste and update worksheet name, cell references and message as required) if the App has more steps it is going through.
3. The Upload_File_To_Optilogic Sub procedure is called multiple times to upload all required files to the Optilogic platform: the app.key file containing the App Key for authentication on the Optilogic platform, the 4 exported .csv files containing the Settings, Customers, Facilities and Suppliers data, the greenfield.job file which contains the Python script to be run on the Optilogic platform, and the requirements.txt file to ensure the Python script can use the cosmicfrog Python library to run properly. Take the line that uploads the greenfield.job file as an example to discuss the arguments:
   1. 1^st argument – localFilePath: the file path on the local machine where the local file to be uploaded is located. Here set to the variable filePath, which is where the Excel App and all the needed files to be uploaded are located.
   2. 2^nd argument – localFileName: the name of the local file that will be uploaded. Here greenfield.job will be uploaded.
   3. 3^rd argument – platformFilePath: file path on the Optilogic platform where the file will be uploaded to. If you want to know the path of an existing folder on the Optilogic platform, you can get this from Explorer: right click on the folder, select Copy > Folder Path (see also the screenshot in the Optilogic.bas VBA Module earlier in this documentation). Here it is set to the variable platformFolderName, which was set to My Files/z Working Folder for Excel Greenfield App earlier in the Macro.
   4. 4^th argument – platformFileName: name of the file once it is uploaded to the platform workspace. Name can be the same as or different from the local file name. For the other files, the names are kept the same locally and on the Optilogic platform, but for the Python script it is changed to greenfield_job.py. The file needs to have the .py extension to be recognized and executed as a Python script file. It has a .job extension on the local machine to prevent accidental changes made to the Python code in the file.
   5. 5^th argument – appKey: the user’s App Key for authentication. This has been set earlier in the Macro by the Manage_App_key function.

Besides updating the local/platform file names and paths as appropriate, the Upload_File_To_Optilogic Sub procedure will be used by most if not all Excel Apps: even if the App is only looking at outputs from model runs and not modifying any input data or settings, the function is still required to upload the .job, app.key, and requirements.txt files.

The next bit of code uses 2 more of the Optilogic.bas VBA module functions to run and monitor the Python job on the Optilogic platform:

1. The variable jobKey is used to run the Greenfield Python script on the Optilogic platform. The arguments of the Run_Job_On_Optilogic function are set as follows:
   1. 1^st argument – jobPath: file path in the platform workspace where the Job (.py file) resides. Set to the variable platformFolderName here, which has been set to My Files/z Working Folder for Excel Greenfield App earlier in the Macro.
   2. 2^nd argument – jobname: name of the Job (.py file) in the platform workspace. Set to greenfield_job.py, which is the name of the Python script as it was uploaded to the Optilogic platform.
   3. 3^rd argument – appKey: the user’s App Key for authentication. This has been set earlier in the Macro by the Manage_App_key function.
2. These 2 lines of code set usrMessage to monitoring the job running on the Optilogic platform with the Monitor_Job_On_Optilogic function and then displaying this in cell C5 on the Run Greenfield worksheet. The arguments of the Monitor_Job_On_Optilogic function are set as follows:
   1. 1^st argument – jobKey: unique Job Key identification returned from Run_Job_On_Optilogic function. Set here to the jobKey variable that uses the Run_Job_On_Optilogic function.
   2. 2^nd argument – worksheet: name of the worksheet where the status information is being updated. Here, this is on the Run Greenfield worksheet.
   3. 3^rd argument – interval: the time waited between getting an update on the Job status from the platform in seconds. Here it is set to 5, so the status will be updated every 5 seconds.
   4. 4^th argument – usrMsg: a free form message that will be shown to the user during status updates. Here it is set to “ is processing…”.
   5. 5^th argument – msgCell: the cell reference in the worksheet where the Job status is updated. Here it is set to cell C5. The monitor job function then concatenates the unique jobKey and the usrMsg (“… is processing”) to be displayed in this cell while the greenfield_job.py is running on the Optilogic platform.
   6. 6^th argument – statusCell: the cell reference in the worksheet where the Job status is updated (like “running” or “done”). Here it is set to cell C7.
   7. 7^th argument – timeCell: the cell reference in the worksheet where the time of the last update is updated. Here it is set to cell C6.
   8. 8^th argument – appKey: the user’s App Key for authentication. This has been set earlier in the Macro by the Manage_App_key function.

This piece of code can stay as is for most Apps, just make sure to update the following if needed:

• The jobname, 2^nd argument of the Run_Job_On_Optilogic function (greenfield_job.py here).
• The worksheet, 2^nd argument of the Monitor_Job_On_Optilogic function (Run Greenfield here).
• The msgCell, statusCell, and timeCell, 5^th, 6^th, and 7^th arguments of the Monitor_Job_On_Optilogic function in case these are moved to a different location.
• The worksheet and cell reference in the second line of the code block indicated with 2 in the above screenshot (Run Greenfield and C5 here).

The last piece of code before some error handling downloads the results (2 .csv files) from the Optilogic platform using the Download_File_From_Optilogic function from the Optilogic.bas VBA module:

1. First the progress message and current time are updated in cells C5 and C6 on the Run Greenfield worksheet to indicate that data is now being downloaded from the Optilogic platform.
2. Then the 2 files are downloaded:
   1. Variables to set the names of the 2 files to be downloaded are created.
   2. These 2 file name variables are then set to a concatenation of “Results”, the ScenarioName (set earlier in the Macro on the Settings worksheet as “File name for results”) and a descriptive name of the file (Facility Summary.csv and Flow Summary.csv which are the outputs from the 2 Cosmic Frog Greenfield output tables of similar name). Note that if the “File name for results” is not changed in between runs of the App, the results will be overwritten.
   3. Uses the Download_File_From_Optilogic function 2 times to download the files. Going through the arguments of the function when it is called first:
      1. 1^st argument – platformFilePath: file path on the Optilogic platform where the file will be downloaded from. If you want to know the path of an existing folder on the Optilogic platform, you can get this from Explorer: right click on the folder, select Copy > Folder Path, see also screenshot in the “Optilogic.bas VBA Module” section. Here it is set to the variable platformFolderName, which was set to My Files/z Working Folder for Excel Greenfield App earlier in the Macro.
      2. 2^nd argument – platformFileName: name of the data file in the platform workspace. Here set to “results_FacilitySummary.csv” which has been created and named by the greenfield_job.py Job that was run and we will discuss in detail in the next section.
      3. 3^rd argument – localFilePath: file path on the local machine where the file will be downloaded to. Here it is set to the variable filePath which is the location of the workbook.
      4. 4^th argument – localFileName: name of the local data file when downloaded. Can be different than the name of the file on the Optilogic platform. Here it is set to the names set under bullet b.
      5. 5^th argument – appKey: the user’s App Key for authentication. This has been set earlier in the Macro by the Manage_App_key function.
3. Next, the progress message and time in cells C5 and C6 on the Run Greenfield worksheet are updated again to indicate that the Macro has completed successfully.
4. Lastly, a message box will now pop up that has the text “Completed successfully, open <name of output file 1> and <name of output file 2> to review results”.

This piece of code can be used as is with the appropriate updates for worksheet names, cell references, file names, path names, and text of status updates and user messages. Depending on the number of files to be downloaded, the part of the code setting the names of the output files and doing the actual download (bullet 2 above) can be copy-pasted and updated as needed.

The last piece of VBA code of the Macro shown in the screenshot below has some error handling. Specifically, when the Macro tries to retrieve the local path of the Macro-enabled .xlsm workbook and it finds it looks like it is online, an error will pop up and the user will be requested to put the file path name in cell B3 on the Admin worksheet. If the Macro hits any other errors, a message saying “An unexpected error occurred: <error number> <error description>” will pop up. This piece of code can be left as is for other Cosmic Frog for Excel Applications.

We have used version 3 of the Greenfield App which is part of the Building a Cosmic Frog for Excel Application resource in the above. There is also a stand-alone newer version (v6) of the Cosmic Frog for Excel – Greenfield application available in the Resource Library. In addition to all of the above, this App also:

• Prevents locking of the workbook while the Macro is running; in version 3 Excel becomes unresponsive when running the Macro until it completes entirely.
• Reads outputs back into the workbook.
• Has outputs on a map in a browser that users can open from within the workbook.

This functionality is likely helpful for a lot of other Cosmic Frog for Excel Apps and will be discussed in section “Additional Common App Functionality” further below. We especially recommend using the functionality to prevent Excel from locking up in all your Apps.

greenfield.job

Now we will go through the greenfield.job file that contains the Python script to be run on the Optilogic platform in detail.

This first piece of code takes care of importing several python libraries and modules (optilogic, pandas, time; lines 1, 2, and 5). There is another library, cosmicfrog, that is imported through the requirements.txt file that has been discussed before in the section titled “Python Job File and requirements.txt”. Modules from these libraries are imported here as well (FrogModel from cosmicfrog on line 3 and pioneer.API from optilogic on line 4). Now the functionality of these libraries and their modules can be used throughout the code of the script that follows. The optilogic and cosmicfrog libraries are developed by Optilogic and contain specific functionality to work with Cosmic Frog models (e.g. the functions discussed in the section titled “Working with Python Locally” above and on the Optilogic platform.

For reference:

• Pandas is an open source Python library providing high-performance, easy-to-use data structures and data analysis tools for Python. Pandas documentation can be found here; however, users do not need to review this to follow along with this script.
• Time is a Python module providing various time-related functions; its documentation can be found here. Again, users do not need to review this documentation to be able to follow along with this script.

This first piece of code can be left as is in the script files (.job files locally, .py files on the Optilogic platform) for most Cosmic Frog for Excel Applications. More advanced users may import different libraries and modules to use functionality beyond what the standard Python functionality plus the optilogic, cosmicfrog, pandas, and time libraries & modules together offer.

Next, a check_job_status function is defined that will keep checking a job until it is completed. This will be used when running a job to know if the job is done and ready to move onto the next step, which will often be downloading the results of the run. This piece of code can be kept as is for other Cosmic Frog for Excel Applications.

The following screenshot shows the next snippet of code that defines a function called wait_for_jobs_to_complete. It uses the check_job_status to periodically check if the job is done, and once done, moves onto the next piece of code. Again, this can be kept as is for other Apps.

Now it is time to create and/or connect to the Cosmic Frog model we want to use in our App:

1. A new function named create_model is defined. Its arguments are 1) key to pass an App Key to the function and 2) model_name, the name of the model we are creating and/or connecting to. This function can be kept as is for other Apps.
2. These 2 lines of code open the app.key file uploaded to the Optilogic platform. It contains the App Key needed for user authentication on the platform. It creates a variable named yourappkey, the value of which is set to the App Key contained in the app.key file. Leave this code as is too.
3. A model_name variable is created and set to Greenfield App. For other Apps:
   1. If you are creating a new model and connecting to it, update this to your chosen model name.
   2. If you are connecting to an already existing Cosmic Frog model, use its name here as the model name. Make sure to use the exact same name (e.g. check spaces, underscores, and capitalization).
4. This piece of code tries to first create a model named Greenfield App and set the variable model to refer to this model (a). If it cannot be created because it already exists, we connect to it, again set the variable model to refer to it, and clear out several tables (b). For other Apps, leave the first part (a) as is and as needed update part b:
   1. Use the clear_table function as is used here for the customers, customerdemand, facilities, suppliers, and greenfieldsettings tables for any tables that you do not want to have any data in at the start of your script. Depending on your App it may not be needed to clear any tables or clear 1 or multiple tables.
   2. It is possible to update records in tables that already have data in them or append additional data to them. Clearing tables may not be necessary in this case or possibly you need to clear some, but keep the data that is already there in others.
   3. You can partially clear tables by for example using the exec_sql cosmicfrog function. If you want to remove all records from the Facilities table that have Status = Exclude, you can do so with this line of code:
5. A variable called model_scenario_name is set to Baseline, which will be used to set which scenario will be run when running a Cosmic Frog model. Note that here the Baseline is always run, but that user can set any name they desire as the “File name for results” on the Settings worksheet in the Excel workbook: these 2 can be different. In the Cosmic Frog model on the Optilogic platform side of things, the scenario named Baseline is run every time the Greenfield app runs. On the Excel App side, when the results are downloaded, the “File name for results” is used in the names of the output files, so the user knows which run they belong to. If this “File name for results” input is kept the same between runs, then outputs that are downloaded to the local machine will be overwritten too.
6. As this is an App where we want to run Greenfield analysis, the variable named engine is set to triad, this will be used when kicking off a Cosmic Frog model run to ensure the correct engine is used. As a reminder, the names of the Cosmic Frog technologies are:
   1. NEO: network optimization
   2. THROG: simulation
   3. TRIAD: Greenfield
   4. HOPPER: transportation optimization

Note that like the VBA code in the Excel Macro, we can add comments describing what the code is doing to our Python script too. In Python, comments need to start with the number (/hash) sign # and the font of comments automatically becomes green in the editor that is being used here (Visual Studio Code using the default Dark Modern color theme).

‍

After clearing the tables, we will now populate them with the date from the Excel workbook. First, the uploaded Customers.csv file that contain the columns Customer Name, Latitude, Longitude, and Quantity is used to update both the Customers and the CustomerDemand tables:

1. First Customers.csv is used to populate the Customers table of the Greenfield App model:
   1. A variable df_Customers is created, and the Pandas read_csv function is used to read in the uploaded Customers.csv (line 68). df in the naming of the variable is short for dataframe which is what the read_csv function creates.
   2. The Pandas rename function is used to change the Customer Name column name to customername (line 69), this is to match with the column name in the Customers table in the Cosmic Frog model. The inplace argument in this function is set to True which means that the dataframe is modified, no new dataframe with this change is created.
   3. The Pandas drop function is used to remove the Quantity column from the dataframe (line 70). The axis argument is set to 1 which means that the drop refers to columns and not to indices of rows. Again inplace=True is used to modify the dataframe rather than creating a new dataframe.
   4. The dataframe is then imported into the model’s Customers table using the cosmicfrog write_table function (line 71). The first argument specifies the target table in the model (Customers) and the second argument specifies the data that is to be used, which is the df_Customers dataframe that was created and modified in the previous 3 bullets.
2. In a similar fashion, the Customers.csv fle is read into a dataframe named df_CustomerDemand (line 74), then modified to rename the Customer Name column to customername (line 75), and to drop the Latitude and Longitude columns (lines 76 and 77), before being written into the Greenfield App model’s CustomerDemand table (line 78).

It is very dependent on the App that you are building how much of the above code you can use as is, but the concepts of reading csv files, renaming, and dropping columns as needed and writing tables into the Cosmic Frog model will be frequently used. The following piece of code also writes the Facilities and Suppliers data into the Cosmic Frog tables. Again, the concepts used here will be useful for other Apps too, it may just not be exactly the same depending on the App and the tables that are being written to:

1. Here the same functions read_csv, rename, and write_table as for the Customers and Customer Demand tables are being used to read the Facilities.csv file and write it into the Facilities table of the Cosmic Frog table. In addition, following functions are used too:
   1. The pandas fillna function is used on line 82 to replace any N/A or NaN values in the df_Facilities dataframe with blanks (‘’).
   2. The pandas astype function is used to convert all values in the df_Facilities dataframe to strings (str), line 87.
   3. The pandas str.replace function is used multiple times (lines 89-92) to remove commas and spaces in the values of the fixedoperatingcost and throughputcapacity columns (they are replaced by blanks which is the equivalent of removing them here). This is needed because the Excel Macro wrote “ 4,500,000 ” for the fixed operating cost into the .csv file that was uploaded to the Optilogic platform and similarly contains commas and leading and trailing spaces for the throughput capacity entries. The Cosmic Frog model will not run properly with data that has commas and leading and/or trailing spaces in it, therefore cleaning this up in the Python script is best practice and will apply to all data that similarly comes through from the Excel Macro with commas and spaces in it.
2. All the same functions as used above for Customers, CustomerDemand, and Facilities are used here to read the Suppliers.csv file in and populate the Suppliers table in the Cosmic Frog model.

Next up, the Settings.csv file is used to populate the Greenfield Settings table in Cosmic Frog and to set 2 variables for resource size and scenario name:

1. First the Settings.csv file is read in (line 107) and the fillna function is used (line 108) to replace any N/A or NaN values with blank values (‘’).
2. On lines 110 and 111, 2 new variables are created, resource_size and app_scenario_name and are set to the values of the ResourceSize and ScenarioName columns of the df_GreenfieldSettings dataframe. The pandas iloc function is used to get the values of the first row of data (the [0] index) of these 2 columns.
3. On lines 112 and 113, the ResourceSize and ScenarioName columns are dropped from the Settings.csv as these are not part of the Greenfield Settings table in Cosmic Frog; the variables set in the previous 2 lines will be used when kicking off the model run in the next piece of code.
4. The pandas fillna and astype functions and the cosmicfrog write_table function are used to finalize the df_GreenfieldSettings dataframe, and to write the data of it into the Greenfield Settings table of the Greenfield App Cosmic Frog model.

Now that the Greenfield App Cosmic Frog model is populated with all the data needed, it is time to kick off the model and run a Greenfield analysis:

1. First, on line 120, we set a variable called api and connect to the Optilogic api that runs models, using our App Key that was defined earlier in the code to authenticate. This code can be kept as is in other Cosmic Frog for Excel Applications.
2. Next, the model run is kicked off, lines 121-125. For the arguments needed to run the model, several variables that have been set earlier in the code are being used:
   1. model_name, model_scenario_name, engine, and resource_size have been set to Greenfield App, Baseline, triad, and L respectively in code discussed further above.
   2. Optionally, users can give their runs a tag, which will be shown for the job in the Run Manager when a job is running on the Optilogic platform. Here the tag “Greenfield App” is used.
3. This piece of code uses the wait_for_jobs_to_complete function which was defined at the beginning of our Python script to regularly check if the model is done running and will only move onto the next bit of code if the job has indeed completed.

Besides updating any tags as desired (bullet 2b above), this code can be kept exactly as is for other Excel Apps.

‍

Lastly, once the model is done running, the results are retrieved from the model and written into .csv files, which will then be downloaded by the Excel Macro:

1. The cosmicfrog read_table function is used to populate 2 dataframes, df_FacilitySummaty and df_FlowSummary, with the data from 2 of the Cosmic Frog Greenfield output tables: the optimizationgreenfieldfacilitysummary and the optimizationgreenfieldflowsummary, respectively.
2. The app_scenario_name is used to update the scenarioname columns of both output tables. This was set by the user as “File name for results” in the Excel workbook (on the Settings worksheet).
3. The pandas to_csv functon is used to write the 2 dataframes with results into 2 .csv files. The argument “index” is set to “False” to not write out the indices of the rows (i.e. row numbers are not written into the .csv files).

Run Manager to Monitor Jobs

When the greenfield_job.py file starts running on the Optilogic platform, we can monitor and see the progress of the job in the Run Manager App:

1. Go to the Run Manager app on the Optilogic platform at cosmicfrog.com.
2. If you do not see the Run Manager app, then click on the icon with the 3 dots to see all apps, now the Run Manager should be visible too.
3. When running a Job file (.py extension on the Optilogic platform) that itself kicks off a Cosmic Frog model run (here for a Greenfield analysis), you will see 3 related to it in the list of Jobs. The state, execution file, tags, start date & time, etc. are listed here.
4. On the right-hand side you can view details of the Job through a set of logs by clicking on one of the 6 icons at the right top. These show, for the icons from left to right:
   1. Job Info (shown in the screenshot here)
   2. Job Records
   3. Job Usage
   4. Job Log
   5. Job Error Log (if applicable)
   6. Optimality Gap (if applicable)
5. Here, the Job Info is shown.
6. If a Job for some reason is not finishing within the amount of time expected, a used can manually cancel it by clicking on the Cancel Job button. Jobs that have been run before can also be re-run here by clicking on the Run Job button.

Additional Common App Functionality

The Greenfield App (version 3) that is part of the Building a Cosmic Frog for Excel Application resource covers a lot of common features users will want to use in their own Apps. In this section we will discuss some additional functionality users may also wish to add to their own Apps. This includes:

• Ability to cancel runs from the Excel workbook and prevent Excel from locking up while Macros are running.
• Reading output data back into a worksheet within the App Excel workbook.
• Visualizing outputs on maps: using Excel 3D Maps, an Arc GIS Excel add-in, or the Python library folium.

Prevent Locking of Excel & Ability to Cancel an App Run

A newer version of the Greenfield App (version 6) can be found here in the Resource Library. This App has all the functionality version 3 has, plus: 1) it has an updated look with some worksheets renamed and some items moved around, 2) has the option to cancel a Run after it has been kicked off and has not completed yet, 3) it prevents locking up of Excel while the App is running, 4) reads a few CSV output files back into worksheets in the same workbook, and 5) uses a Python library called folium to create Maps that a user can open from the Excel workbook, which will then open the map in the user’s default browser. Please download this newer Greenfield App if you want to follow along with the screenshots in this section. First, we will cover how a user can prevent locking of Excel during a run and how to add a cancel button which can stop a run that has not yet completed.

‍

The screenshots call out what is different as compared to version 3 of the App discussed above. VBA code that is the same is not covered here. The first screenshot is of the beginning of the RunGreenfield_Click Macro that runs when the user hits the Run Greenfield button in the App:

1. These lines of code are added to disable the Run Greenfield button once it has been clicked on once to prevent a user from kicking off multiple runs at the same time. DoEvents on the second line here ensures that other applications (including other Excel workbooks) are also prioritized when user interacts with them, so that the App run does not take up all the machine’s resources.
2. In this piece of code, if the App run ends here with a message to the user that they need to select a value for a certain setting before the Greenfield App can be run, then the Run Greenfield button is made active (enabled) again, so user can click on it again once they have fixed the input issue. DoEvents is again used here to make sure the resources of the local machine are also available for other applications. This pattern of adding these 2 lines of code is repeated in all other cases in the code where the App can end with a message to the user about fixing something in the inputs/App Key. Users are encouraged to adopt this strategy in their own Apps too.

The next screenshot shows the addition of code to enable the Cancel button once the Job has been uploaded to the Optilogic platform:

1. The button that has the CancelRun Sub assigned to it (which will be discussed below) is now enabled, so the user can click on it if it is needed to cancel a run that has already started on the Optilogic platform.
2. If the Job fails on the Optilogic platform, a user message “Job failed to run” will pop up and added here are 3 lines of code to then make the Run Greenfield button active (enabled) again, make the Cancel button inactive (disabled again), and DoEvents is again used to make sure other applications can still take up computer resources too.

If everything completes successfully, a user message pops up, and the same 3 lines of code are added here too to enable the Run Greenfield buttons, disable the Cancel button, and keep other applications accessible:

Finally, a new Sub procedure CancelRun is added that is assigned to the Cancel button and will be executed when the Cancel button is clicked on:

This code gets the Job Key (unique identifier of the Job) from cell C9 on the Start worksheet and then uses a new function added to the Optilogic.bas VBA module that is named Cancel_Job_On_Optilogic. This function takes 2 arguments: the Job Key to identify the run that needs to be cancelled and the App Key to authenticate the user on the Optilogic platform.

Reading CSV Outputs Back into the App Workbook

Version 6 of the Greenfield App reads results from the Facility Summary, Customer Summary, and Flow Summary back into 3 worksheets in the workbook. A new Sub procedure named ImportCSVDataToExistingSheet (which can be found at the bottom of the RunGreenfield Macro code) is used to do this:

The function is used 3 times: to import 1 csv file into 1 worksheet at a time. The function takes 3 arguments:

1. filePath – the path to the folder where the CVS output file to be imported is located. Set to the variable filePath here which is the path to where the Excel App is saved.
2. importCSVFileName – the name of the CSV file to be imported, set to “Results – Facility Summary.csv” here when the function is called first. This CSV file is created by the greenfield_job.py Python script on the Optilogic platform after the Greenfield run completes; it takes outputs from the Optimization Greenfield Facility Summary table and saves them into the Results _ Facility Summary.csv file.
3. importSheet – the name of the worksheet in the workbook the CSV file is to be imported into. Set to Results – Facility Summary here when the function is first called. Note that this worksheet needs to be in the workbook already, it will not be created by the Macro.

Using Maps

We will discuss a few possible options on how to visualize your supply chain and model outputs on maps when using/building Cosmic Frog for Excel Applications.

This table summarizes 3 of the mapping options: their pros, cons, and example use cases:

3D Maps in Excel

There is standard functionality in Excel to create 3D Maps. You can find this on the Insert tab, in the Tours groups (next to Charts):

Documentation on how to get started with 3D Maps in Excel can be found here. Should your 3D Maps icon be greyed out in your Excel workbook, then this thread on the Microsoft Community forum may help troubleshoot this.

‍

How to create an Excel 3D Map in a nutshell:

1. Open a workbook that contains location data.
2. Click on Insert > 3D Map > Open 3D Maps.
3. The map may automatically use your data to display on the map, but if not, go to the worksheet with the data, select the range (including any headers) and click on 3D Map > Add Selected Data to 3D Maps.
4. Configure your Tour(s) and their Layer(s) in the 3D Maps configuration window.

With Excel 3D Maps you can visualize locations on the map and for example base their size on characteristics like demand quantity. You can also create heat maps and show how location data changes over time. Flow maps that show lines between source and destination locations cannot be created with Excel 3D Maps. Refer to the Microsoft documentation to get a deeper understanding of what is possible with Excel 3D Maps.

‍

The Cosmic Frog for Excel – Geocoding App in the Resource Library uses Excel 3D Maps to visualize customer locations that the App has geocoded on a map:

Here, the geocoded customers are shown as purple circles which are sized based on their total demand.

Arc GIS Excel Add-In

A good option to for example visualize Hopper (= transportation optimization) routes on a map is the ArcGIS Excel Add-in. If you do not have the add-in, you can get it from within Excel as follows:

1. Go to your Developer tab.
2. Click on Add-ins. If you do not have an Add-ins option here, it may be elsewhere in your Excel ribbon. One way to find it is to go to File > Options (or File > More > Options if you do not see Options under File), then click on Customize Ribbon. Look for Add-ins in the list on the left and if it is there select it and click on the Add >> button. If it is not in the list on the left, then look for it in the list on the right, expanding the names of the tabs, until you locate it. This will tell you under which tab the Add-ins button is located.
3. In the Office Add-ins window that pops up, go to Store.
4. Search for ArcGIS in the search box.
5. Click on Add.

You may be asked to log into your Microsoft account when adding this in and/or when starting to use the Add-in. Should you experience any issues while trying to get the Add-in added to Excel, we recommend closing all Office applications and then only open one Excel workbook through which you add the Add-in.

‍

To start using the add-in and create ArcGIS maps in Excel:

1. In the Excel worksheet with the data to map, click on the ArcGIS tab.
2. Click on Show Map. You will be prompted to sign into your ArcGIS account which you can do if you have one to access additional functionality, or you can choose to continue as a guest.
3. Next you are prompted to add a Layer to the map, choose Excel here. You could add ArcGIS layers later too if you want to overlay your data with a specific ArcGIS map.

Excel will automatically select all data in the worksheet that you are on. You can ensure the mapping of the data is correct or otherwise edit it:

1. We are in the Layers area of the map configuration pane.
2. Automatically, all data in the sheet we are on is selected for the layer. In the drop-down you can select data from different worksheets.
3. If you want to manually set the data range to be used on the map, you can do so by clicking on this icon.
4. There are multiple options for Location types. When using the ArcGIS add-in as a guest, the 2 that are available are 1) Coordinates, which needs latitudes & longitudes of the locations to be mapped, and 2) EsriJSON Geometry, which can draw points, polylines, polygons, and other geometries if the data is in the correct format (we will see an example of this a bit further down). Here, based on the data, the option of Coordinates was automatically selected and the longitude and latitude columns in the data mapped correctly to the Longitude (X) and Latitude (Y) fields.
5. A spatial reference defines the coordinate system used to locate the geometry for a feature. It controls how and where features are displayed in a map or scene. There are many different types of spatial references for defining geographic data. To simplify accessing and referencing them, they are commonly referred to by a well-known ID (WKID) — an integer value. Two of the most common WKIDs are 4326 (also known as WGS84 or WGS 1984), and 3857 (also known as Web Mercator). Here, in the drop-down list for Spatial reference, select the one you want to use for your map. By default, WGS 1984 will be used. To learn more about spatial references, please see this ArcGIS documentation on it.
6. Click on Add when you are finished configuring your layer and it will be added to the map. Multiple layers can be added if desired.

After adding a layer, you can further configure it through the other icons at the top of the Layers window:

1. We are still in the Layers area of the Map configuration pane.
2. To add another layer click on the Add button.
3. When this Layers icon is selected, the list of Layers that have been added to the Map are shown.
4. Currently there is 1 Layer added to the Map, click on the carrot sign to expand the Layer.
5. To remove a Layer from the Map, click on the recycle bin icon.
6. To further configure a Map Layer, use these 3 icons at the top of the Layers pane:
   1. Under the Symbology icon, Layers can be styled by selecting 1 or multiple fields from the mapped data, and Symbol type can be set to either Location or Heat map.
   2. Under the Style Options icon, symbols can be styled by setting shapes, sizes, colors, and outlines. Under Vary by attribute, transparency and/or rotation by attribute can be enabled and configured.
   3. Under the Layer Properties icon, pop-ups, visible range, labels, and transparency can be selected and configured.

The other configuration options for the Map are found on the left-hand side of the Map configuration pane:

1. Clicking on this icon with the 3 horizontal lines will collapse or expand the Map configuration pane.
2. Layers can be added and configured as described through the previous screenshot through the Layers area of the Map configuration pane.
3. A Basemap on which the selected data will be shown can be selected in this area of the Map configuration pane.
4. The selection tool has different options for selecting one or multiple locations on the Map.
5. One can find an address or place by using the Search option.
6. Under the Analysis icon, several ways to analyze data, like for example distance or area measurements are listed, however not all services are available to those using the ArcGIS add-in as a guest.
7. Under Navigation tools, the Map extent (the region that is zoomed into) can be configured to be set to a certain home extent and/or to be locked. Bookmarks for certain zoom levels on certain parts of the world can be saved here too.
8. Sharing and Export options are not available to those using the ArcGIS add-in as a guest, but for those with accounts they have the option to do so here.
9. Under Settings, you can sign in to your ArcGIS account, provide feedback, and change Settings around sending usage data to Esri and Microsoft account integration.

As an example, consider the following map showing the stops on routes created by the Hopper engine (Cosmic Frog’s transportation optimization technology). The data in this worksheet is from the Transportation Stop Summary Hopper output table:

1. We have filtered the data to routename = 3 to just visualize the stops and their order of 1 route on the map.
2. In the Map configuration pane, under Layers > Style Options, green squares with a black outline were chosen as the shapes to represent the stop locations on the route.
3. In the Map configuration pane, under Layers > Layer Properties, Pop-ups and Labels have been configured:
   1. Under Pop-ups, the switch to Enable pop-ups was turned on. In the Layer fields to include drop-down, all fields in the worksheet are selected, so when the user clicks on a location all the fields are shown in the pop-up. In the screenshot you can see this for the location labelled 2.
   2. Under Labels, the switch to Enable labels was turned on. Stopid is selected in the Label field and font, font size, color, and placement of the label are configured here too.

As a next step we can add another layer to the map based on the Transportation Segment Summary Hopper output table to connect the source-destination pairs with each other using flow lines. For this we need to use the Esri JSON Geometry Location types mentioned earlier. An example Excel file containing the format needed for drawing polylines can be found in the last answer of this thread on the Esri community website: https://community.esri.com/t5/arcgis-for-office-questions/json-formatting-in-arcgis-for-excel/td-p/1130208, on the PolylinesExample1 worksheet. From this Excel file we can see that the format needed to draw a line connecting 2 locations:

‍

{“paths”: [[<point1_longitude>,<point1latitude>],[<point2_longitude>,<point2_latitude>]],”spatialReference”: {“wkid”: 4326}}

‍

Where wkid indicates the well-known ID of the spatial reference to be used on the map (see above for a brief explanation and a link to a more elaborate explanation of spatial references). Here it is set to 4326, which is WGS 1984.

‍

The next 2 screenshots show the data from a Segments Summary and an added layer to the map to show the lines from the stops on the route:

1. The Transportation Segment Summary Hopper output table contains the latitudes and longitudes for the origin and destination of the route segment.
2. In the ESRI JSON Polyline column (column I) we create the format needed to draw lines on the map through concatenation of multiple text strings and the origin and destination latitudes and longitudes (char(34) is the code to create double quotes). Note that creating this Esri JSON specific format so lines can be drawn on the map can be done like it was done in this example, in a column in an Excel worksheet, but can also be done through VBA code when bringing downloaded results back into Excel, or (preferably) as part of the Python script that runs on the Optilogic platform, where it adds this to the outputs when the model has finished running and outputs are extracted from the model.
3. On the map we then add a new Layer using the data on this worksheet. For Location types we now choose EsriJSON Geometry and use the ESRI JSON Polyline column as the Geometry column (this is usually automatically selected already). With both layers visible (the layer with the stops from the Stops worksheet and the layer with the segments from the Segments worksheet), the map now looks like this:

Note that for Hopper outputs with multiple routes, we now need to filter both the worksheet with the Stops information and the worksheet with the Segments information for the same route(s) to synchronize them. A better solution is to bring the stopID and Delivered Quantity information from the Stops output into the Segments output, so we only have 1 worksheet with all the information needed and both layers are generated from the same data. Then filtering this set of data will update both layers simultaneously.

folium Python library

Here, we will discuss a Python library called folium, which gives users the ability to create maps that can show flows, tooltips, and has options to customize/auto-size location shapes and flow lines. We will use the example of the Cosmic Frog for Excel – Greenfield App (version 6) again where maps are created as .html files as part of the greenfield_job.py Python script that runs on the Optilogic platform. They are then downloaded as part of the results and from within Excel, users can click on buttons to show flows or customers which then opens the .html files in user’s default browser. We will focus on the differences with version 3 of the Greenfield App that are related to maps and folium. We will discuss the changes/addition to both the VBA code in the Excel Run Greenfield Macro and the additions to greenfield_job.py. First up in the VBA code, we need to add folium to the requirements.txt file so that the Python script can make use of the library once it is uploaded to the Optilogic platform:

To do so, a line to the VBA code is added to write “folium” into requirements.txt.

‍

As part of downloading all the results from the Optilogic platform after the Greenfield run has completed, we need to add downloading the .html map files that were created:

1. First 3 variables that will be set to the names of the maps to be downloaded from the Optilogic platform are created.
2. Then these names are set to what the .html files will be called after downloading the map files from the Optilogic platform.
3. Lastly, the Download_File_From_Optilogic function is called 3 times to download the map files from the Optilogic platform. Note that the names of the files on the Optilogic platform are somewhat different from what we name them after downloading to the user’s local machine.

In this version of the Greenfield app, there is a new Results Summary worksheet that has 3 buttons at the top:

Each of these buttons has a Sub procedure assigned to it, let’s look at the one for the “Show Flows with Customers” button:

1. This piece of code gets the file path of where the Excel workbook is saved. It is similar to the code in v3 of the Greenfield app that gets the file path, just with a different worksheet name and cell on that worksheet.
2. Here the map name is set and then opened in the user’s default browser:
   1. The variable mapFileName is created.
   2. mapFileName is set to the path of where the workbook is saved concatenated with the name of the map, which in this case in “Maps – Flows with Customers.html”.
   3. The VBA function FollowHyperlink is used to open the map in a new window in the user’s default browser.

The map that is opened will look something like this, where a tooltip comes up when hovering over a flow line. (How to create and configure the map using folium will be discussed next.)

The additions to the greenfield.job file to make use of folium and creating the 3 maps will now be covered:

First, at the beginning of the script, we need to add “import folium” (line 6), so that the library’s functionality can be used throughout the script. Next, the 3 Greenfield output tables that are used to create the 3 maps are read in, and a few data type changes are made to get the data ready for mapping:

1. The cosmicfrog function read_table is used to read the Optimization Greenfield Facility Summary table into a dataframe named df_Res_Facilities (line 137).
2. Next on lines 138 and 139, the pandas function to_numeric is used to convert the data type of the latitude and longitude fields to numeric (from strings).

This is repeated twice, once for the Optimization Greenfield Customer Summary output table and once for the Optimization Greenfield Flow Summary output table.

‍

The next screenshot shows the code where the map to show Facilities is created and the Markers of them are configured based on if the facility is an Existing Facility or a Greenfield Facility:

1. A variable “map” is created and set by using the folium function map (line 152). The location argument of the function sets the latitude & longitude the map will be centered on. Here it is set to the means of the destination latitudes and destination longitudes of the df_Res_Flows dataframe. The zoom_start argument is set to 4 and indicates the initial zoom level for the map.
2. The pandas function iterrows is used to iterate through each row in the df_Res_Facilities dataframe (line 155):
   1. If the Facility Type is equal to Existing Facility (line 157), then the variable facility_tooltip is set to the name of the facility appended with (existing) (line 158).
   2. The Marker folium function (line 159) is then used to configure what the marker for this location will look on the map. Its arguments are:
      1. location (line 160): set to the latitude and longitude of the facility.
      2. tooltip (line 161): set to facility_tooltip which was created and set before (line 158).
      3. icon (line 162): uses the folium function Icon to set to a dark blue circle with a prefix of “fa” which means the icon is used from a collection of scalable vector icons, called Font Awesome. For a nice overview of types of markers one can use with folium, see this blog post “Beautiful leaflet markers with folium and fontawesome”.
   3. Finally, the folium function add_to is used to add the location that was just configured to the map.
   4. If the facility type is equal to Greenfield Facility (line 165), the location is also added to the map in the same way as Existing Facilities, with the one difference that the color of its marker is set to green instead of dark blue (line 171)

In the next bit of code, df_Res_Flows dataframe is used to draw lines on the map between origin and destination locations:

1. A function called add_line is defined (lines 177-179). It takes the following arguments:
   1. map – the map to which the lines will be added.
   2. origin – the latitude and longitude of the location from which the flow line will be drawn.
   3. destination – the latitude and longitude of the location to which the flow line will be drawn.
   4. origin_name – the name of the location from which the flow line will be drawn.
   5. destination_name – the name of the location to which the flow will be drawn.
   6. flow_quantity – the size of the flow from origin to destination in terms of quantity.
   7. color – the color of the flow lines, set to blue here.
2. A flow_tooltip variable that is set to the concatenation of “<origin_name> to <destination_name> with <flow_quantity>” where these are all converted to strings is created (line 178).
3. The folium function Polyline is used to draw a line on the map from the origin to the destination with the tooltip that shows when hovering over the flow line set to the just configured flow_tooltip variable, the color of the flowline set to color, weight to 2.5, and opacity to 1 (line 179).
4. This is then added to the map by using the folium add_to function (line 179). Note that this is building on the map that was created previously that has the facility locations on it, so the result of using this function will be a map with facilities and flow lines.
5. The iterrows pandas function is then used again to now iterate through the rows of the df_Res_Flows dataframe and add a flow line to the map (using the add_line function, on line 189) for each row (lines 182-189).
6. The map is saved under the name greenfield_flows_map.html (line 191).

Lastly, the customers from the Optimization Greenfield Customer Summary output table are added to the map that already contains facilities and flow lines, and is saved as greenfield_flows_customers_map.html:

1. The pandas iterrows function is used again, to now iterate through all rows in the df_Res_Customers dataframe (lines 194-201).
2. The variable customer_tooltip is set to a concatenation of “<customername> with <totaldemand>” (line 199).
3. The folium Marker function is used to configure the markers for customers on the map (line 197):
   1. The location of the markers will be the customer’s latitude and longitude (line 198)
   2. The tooltip that shows when hovering over the customer is set to the customer_tooltip variable that was just set on line 196 (line 199)
   3. The icon of the marker is a light blue circle with prefix “fa” (line 200)
4. Each customer is then added to the map by using the folium add_to function (line 201).
5. The map which now contains facilities, flow lines, and customers is saved as greenfield_flows_customers_map.html (line 203).

Tips & Tricks

Here are some additional pointers that may be useful when building your own Cosmic Frog for Excel applications:

1. Use a generative AI like for example Chat GPT or perplexity to help with generating VBA and/or Python code. In most cases this will help you along quite far and quickly.
2. You can record manual steps that you are taking in Excel as a Macro and see the resulting VBA code. This can be a starting point to write your own VBA code. To record a Macro:
   1. Go to the Developer tab in Excel.
   2. Click on Record Macro in the Code group.
   3. Give the Macro a name, choose where it should be stored, and optionally write a description of what the Macro will do.
   4. Click on OK.
   5. Manually take the steps you want to automate and create VBA code for.
   6. Click on Stop Recording when done.
   7. You can now find this Macro and its code under Modules > Module1 (or Module2, etc.) in the Visual Basic Editor.
3. To ensure users of the App do not accidentally change data or instructions in any of the worksheets that should be kept as is, password protecting worksheets or whole workbooks can be helpful. This can be done in ways where certain cell ranges on certain worksheets are not locked, for example inputs that may be varied, so the user can still change those. See for example this documentation on locking/unlocking specific areas of a protected worksheet.
4. You can use VBA code to reset data that can be changed by a user back to a default value and assign that Macro to a button that the user can click. This way a user can always go back to those default values after changing any values without having to take note of the original values. See following example where a user can click a Reset Defaults button (located on the DemandFactor_Customers worksheet) to set multiple cell ranges to the value of 1 for DemandFactor and to the value of Consider for DemandStatus. The VBA code to do this is shown in the second screenshot:

5. Generate a basic App where non-power users cannot change many settings/inputs, and where power users do have access to change more. Good practice is to put the settings that power users can change on a separate worksheet, so that the workflow for non-power users stays as simple as possible. Locking/unlocking worksheets/workbooks may also be helpful for achieving this.
6. In the Apps covered in this documentation, the read_sql and exec_sql cosmicfrog functions have not been used much, but deserve a mention here as they can be a powerful tool to work with your models on the Optilogic platform. Some examples are:
   1. Downloading partial outputs tables, e.g. filtering for records of only a certain scenario, filtering for values greater/less than a cutoff (where utilization > 0.9 for example), only downloading a subset of the columns of the output tables instead of the whole table, etc.
   2. Updating a table instead of overwriting tables, for example changing the status of records that have a certain text in the Notes field from Include to Exclude or vice versa, multiplying a numeric field by a certain factor, etc.
7. If a CSV or Excel file created by the Excel Macro is ready to be used by the Cosmic Frog model as is, then there is no need for the Python script to read it into a dataframe first, and instead the upsert_csv / upsert_excel functions can be used to load the data into the correct Cosmic Frog table(s). Note that user should check if the data does not contain things like leading/trailing spaces and commas that should be removed first.

Troubleshooting

You may run into issues where Macros or scripts are not running as expected. Here we cover some common problems you may come across and their solutions.

Excel: Protected View and/or Security Warnings

When opening an Excel .xslm file you may find that you see following message about the view being protected, you can click on Enable Editing if you trust the source:

Enabling content is not necessarily sufficient to also be able to run any Macros contained in the .xlsm file, and you may see following message after clicking on the Enable Editing button:

Closing this message box and then trying to run a Macro will result in the following message.

To resolve this, it is not always sufficient to just close and reopen the workbook and enable macros as the message suggests. Rather, go to the folder where the .xlsm file is saved in File Explorer, right click on it, and select Properties:

At the bottom in the General tab, check the Unblock checkbox and then click on OK.

Now, when you open the .xlsm file again, you have the option to Enable Macros, do so by clicking on the button. From now on, you will not need to repeat any of these steps when closing and reopening the .xlsm file; Macros will work fine.

It is also possible that instead of the Enable Editing warning and warnings around Macros not running discussed above, you will see a message that Macros have been disabled, as in the following screenshot. In this case, please click on the Enable Content button:

Anti-virus Software blocking Macros from Running

Depending on your anti-virus software and its settings, it is possible that the Macros in your Cosmic Frog for Excel Apps will not run as they are blocked by the anti-virus software. If you get “An unexpected error occurred: 13 Type mismatch”, this may be indicative of the anti-virus software blocking the Macro. Work with your IT department to allow the running of Macros.

Local Python Scripts not Connecting to cosmicfrog.com

If you are running Python scripts locally (say from Visual Studio Code) that are connecting to Cosmic Frog models and/or uploading files to the Optilogic platform, you may be unsuccessful and get warnings with the text “WARNING – create_engine_with_retry: Database not ready, retrying”. In this case, the likely cause is that your IP address needs to be added to the list of firewall exceptions within the Optilogic platform, see the instructions on how to do this in the “Working with Python Locally” section further above.

Cells with Formulas in Excel Exporting to CSV as 0’s

You will find that if you export cells that contain formulas from Excel to CSV that these are exported as 0’s and not as the calculated value. Possible solutions for this are 1) to export to a format other than CSV, possibly .xslx, or 2) to create an extra column in your data where the results of the cells with formulas are copy-pasted as values into and export this column instead of the one with the formulas (this way the formulas stay intact for a next run of the App). You could use the record Macro option to get a start on the VBA code for copy-pasting values from a certain column into a certain column so that you do not have to manually do this each time you run the App, but it becomes part of the Macro that runs when the App runs. An example of VBA code that copy-pastes values can be seen in this screenshot:

Closing Output Files

When running an App that has been run previously, there are likely output files in the folder where the App is located, for example CSV files that are opened by the user to view the results or are read back into a worksheet in the App. When running the App again, it is important to not have these output files open, otherwise an error will be thrown when the App gets to the stage of downloading the output files since open files cannot be overwritten.

Apps Available in the Resource Library

There are currently several Cosmic Frog for Excel Applications available in the Resource Library, with more being added over time. Check back frequently and search for “Cosmic Frog for Excel” in the search bar to find all available Apps. A short description for each App that is available follows here:

1. Cosmic Frog for Excel – Basic Routing – this app enables you to change shipment product quantities and generate optimal multi-drop routes. Understand the impact on routes and asset utilization as shipment quantities change. Visualize routes on a map directly in Excel and drill into specific routes to understand fixed and variable costs, as well as driving times and distances.
2. Cosmic Frog for Excel – Capacity – in this app, users can adjust the number of available shift hours and the production demand to optimize the pairing of resources and production. The app will then graphically display the resulting outputs based on these inputs. Additionally, power users have the ability to modify naming conventions to suit different use cases or industry requirements.
3. Cosmic Frog for Excel – DC Capacity – this app enables you to understand capacity limitations and demand allocation when the DCs in the network have certain throughput capacities. Specify the throughput capacity for the 7 US DCs in the network and then the App runs a network optimization taking the new capacities into account. The App returns results detailing the facility throughput utilization as well as how demand and supply is allocated to the facilities within the scenario.
4. Cosmic Frog for Excel – Geocoding – this app enables geographic data including address, city, region, country, and postal code to be entered. Running the App will geocode (return a latitude and longitude) each record. This geocoded data can be displayed on a 3D map directly in Excel. Demand quantity can also be included which is displayed as bars on the map, enabled relative demand by each location to be easily visualized.
5. Cosmic Frog for Excel – Greenfield – this app enables you to understand the optimal number of facilities required for a given demand and supply profile. Specify customer demand quantities and optionally supplier quantities. You are also able to provide existing facilities that must be part of the solution as well as specific candidate facilities that you want to test as part of the scenario. Fixed facility costs, facility throughput capacities and variable costs associated with supply and demand quantities can be defined. The App returns results detailing which facilities are selected as well as how demand and supply is allocated to the facilities within the scenario. Version 3 of this App is what we followed along with initially in the documentation, and then in the section on Additional Common App Functionality we used version 6 of the App to cover a few more useful features.
6. Cosmic Frog for Excel – Network Optimization Output Viewer – this app can be used to quickly retrieve Cosmic Frog network optimization (NEO) model outputs and review them in Excel. These outputs can include flow maps, dashboards, reports, and output tables which can be selected and configured by the App user. Power users can configure the App so members of the leadership team, who may not be familiar with the particulars of Cosmic Frog, can easily retrieve and review the latest Cosmic Frog model results also at their own convenience.
7. Cosmic Frog for Excel – Scenario Runner – this app enables the Supply Chain Designer to rapidly deploy decision-making capabilities to others within their organization. This app allows users to leverage the power of Cosmic Frog by simply changing predefined parameters, such as demand or cost. Users can review results from Cosmic Frog to understand the impact on cost, service, and risk.

List of All Resources

As this documentation contains many links to references and resources, we will list them all here in one place:

• Cosmic Frog for Excel – App Builder resource in the Resource Library – learn how to build Cosmic Frog for Excel applications without writing any code. Also refer to the “Getting Started with the Cosmic Frog for Excel App Builder” help article.
• Chat GPT – a generative Artificial Intelligence platform which can be helpful when for example trying to get syntax right for VBA or Python code.
• perplexity – another generative Artificial Intelligence platform which can be helpful when for example trying to get syntax right for VBA or Python code.
• Building a Cosmic Frog for Excel Application – a resource in Optilogic’s Resource Library which will help users kickstart building their own Cosmic Frog for Excel Applications, it contains a video, PowerPoint presentation, and multiple macro-enabled Excel workbooks showing the various steps.
• Resource Library – Optilogic’s Resource Library where various Cosmic Frog models, reference data, apps and tools can be found to help users with their own model and App building.
• Help Article on How To Use the Resource Library – article in Optilogic’s Help Center on how to take advantage of the resources contained in the Resource Library
• Getting started with VBA in Office, which also has an entire section on VBA in Excel.
• Help Center Article on Generating App and API Keys
• Python for Beginners page on python.org
• https://code.visualstudio.com/ – website where Visual Studio Code (a rich text editor for for example writing Python scripts) can be downloaded from.
• https://www.python.org/downloads/ – website where Python can be downloaded from for those who want to use it on their local machines
• https://marketplace.visualstudio.com/items?itemName=ms-python.python – website where a Python extension for Visual Studio Code can be downloaded from. This will enable automatic code completion, easy debugging, etc.
• “Scripting with Cosmic Frog” video – video on how to use the cosmicfrog Python library to access, edit, and download/upload outputs and inputs of Cosmic Frog models, both local setups and through Atlas on the Optilogic platform are covered.
• Cosmic Frog for Excel – Greenfield – latest version of the Greenfield App in the Resource Library.
• Pandas documentation can be found here. Pandas is a Python library used in Optilogic’s Cosmic Frog for Excel Apps.
• Time is a Python module providing various time-related functions; its documentation can be found here.
• Documentation on how to get started with 3D Maps in Excel can be found here.
• If your 3D Maps icon is greyed out in your Excel workbook, then this thread on the Microsoft Community forum may help troubleshoot this.
• Cosmic Frog for Excel – Geocoding App – latest version of the Geocoding App in the Resource Library.
• ArcGIS documentation on spatial references.
• https://community.esri.com/t5/arcgis-for-office-questions/json-formatting-in-arcgis-for-excel/td-p/1130208 – has an example Excel file containing the format needed for drawing polylines in the last answer of this thread on the Esri community website.
• Font Awesome – a collection of scalable vector icons that can be used with the folium Python library for configuring maps.
• Overview of types of markers one can use with folium: blog post “Beautiful leaflet markers with folium and fontawesome”.
• Documentation on locking/unlocking specific areas of a protected worksheet.
• Cosmic Frog for Excel – DC Capacity – latest version of the DC Capacity App in the Resource Library.
• Cosmic Frog for Excel – Basic Routing – latest version of the Basic Routing App in the Resource Library.
• Cosmic Frog for Excel – Capacity – latest version of the Capacity App in the Resource Library.
• Cosmic Frog for Excel – Network Optimization Output Viewer – latest version of the Network Optimization Output Viewer App in the Resource Library.
• Cosmic Frog for Excel – Scenario Runner – latest version of the Scenario Runner App in the Resource Library.

We love for our users to connect, keep up to date, learn from and share with other Cosmic Frog users & experts through the Frogger Pond Community! If you have an Optilogic account (see this page on how to create your free account if you do not have one yet), you can use that same account to log into the Frogger Pond Community.

Here, we will describe what the Frogger Pond Community consists of, how to interact with, search, sort, and contribute to Topics, and how to manage your account. Recommended reads for new users are included in the last section too.

1.    Frogger Pond Community Homepage

When you login to the Frogger Pond Community, the homepage you see will look similar to the screenshot below:

1. Clicking on the Frogger Pond Community button at the left top of the screen will take you back to this homepage if you are on any other page within the community.
2. You can choose how the Topics list is shown to you: you will see all 5 categories if you click on Categories and you can then choose the one you want to explore. The topics will be ordered by most active posts if you click on Top; if you click on Latest, they will be ordered by most recent activity. By default, the Top option is used, which you can also see as the one highlighted in blue in box 5 and is described in bullet 5 further below.
3. There are 5 categories of Topics in the Frogger Pond Community:
   • Product Feedback – we love to hear your feedback on the software here, both good and bad! Feature requests can be posted here too.
   • Modeling Best Practices – if you are new to supply chain design, you can learn from experienced designers and ask your questions here. If you are very experienced and can share your insights on how to model certain aspects of a supply chain, please do so here!
   • Marketplace – have a look here if you are looking for the next step in your supply chain design career. Or, in case you have an interesting opportunity to offer, you can post that here too. You can create new topics in the Marketplace category if you are part of the Professional user group.
   • Tips and Tricks – share your best shortcuts, videos, and insights on how you use Cosmic Frog, Atlas and the overall Optilogic platform here.
   • General – for questions or posts that do not fit into the other 4 categories. You are also encouraged to link to interesting articles or posts in this category.
4. At the right top there are a few buttons as follows:
   • The arrow button takes you to the Optilogic platform.
   • The magnifying glass button opens a search box where you can type your search term to find topics of interest. You can also click on the icon on the right side in the search box that takes you to an advanced search form where you can for example filter for certain tags, a certain date range, etc.
   • The button with 3 horizontal bars opens a menu where you can quickly go to different parts of the Frogger Pond Community. We will discuss the options here in section “4. Options from the Menu” further below.
   • The 4^th button is your profile picture and here you can get to your Notifications, Bookmarks, Messages, and Preferences. Section “5. Managing your User Account” covers this in more detail.
5. In this area you can filter out and sort the topics that are being shown.
   • The first filter on the left can be used to look at 1 of the 5 categories only or to look at topics from all categories.
   • The second filter can be used to select or search for certain tags so that only topics with those tags are shown in the topics list.
   • You can click on any of the next 5 buttons to order the list of topics differently, the one that is selected will appear as a blue box with white letters.
     • Top: shows you the most active topics in the last year, month, week, or day.
     • Categories: shows all topics grouped by category.
     • Latest: shows the topics ordered by most recent posts.
     • New: shows the topics created in the last few days. By default, this is set to the last 2 days, you can change this in your Preferences.
     • Unread: will show topics that you are watching or tracking that have unread posts. Again, you can change what is considered Unread in your Preferences.
6. If you have selected Top as the way to order the Topics list (see previous bullet), then you can change if you want to look at the top comments over all time or over the last year, quarter, month, week, or day here.
7. This is the Topics list itself, where you can click on any of the Topics to open them and start interacting with them. You can sort the topics list by clicking on Replies, Likes or Date Last Edited at the right top of the Topics list. Clicking once will order them in descending number of replies / likes order and most recent date to last edited; clicking on them again will reverse the order to number of replies / likes in ascending order and from last to most recent date edited. We will discuss what you see and how to interact within a topic in the next section titled “2. Interacting with a Topic”.
8. Click on this “+ New Topic” button if you want to create a new topic yourself. The form that opens up and how to fill it out is detailed in section “3. Creating a New Topic”.

2.    Interacting with a Topic

Once you have clicked on a topic that you want to read and possibly interact with, you will see something similar to the following screenshot:

1. At the top, the title of the Topic is shown; here, the topic “Ability to sort models by latest modified” is being viewed. Underneath the title, we can see the category this topic is posted in, which is Product Feedback in this case. Next to the category, the tags that are associated with this topic are shown. In this case the topic is tagged with the cosmic-frog and feature-request tags.
2. Then the original post by the original poster is shown, which has the user name (evan.james), the group(s) the user is part of (Professional) and the date when the topic was created (May ’23) at the top and then the text of the post itself. As you will see in the next section, besides text you can also use images, hyperlinks, bullet lists, and other formatting in your posts.
3. Underneath the text of the post, there are multiple options to interact with this topic as follows, from left to right (note that you may not see the flag and bookmark icons, but an icon with 3 dots instead; click on the icon with 3 dots to see all icons):
   • Click on the heart icon to like the post.
   • Click on the chain icon to share a hyperlink of this post. In the pop-up window that comes up you can copy the link, share it via email or create a New Linked Topic on the Frogger Pond Community that links to this post.
   • Click on the flag icon to privately flag this post for attention to the community staff or to send a private notification to the poster. In the pop-up window that comes up you can choose to do one of the following: send the original poster a private message or notify staff privately that the post is either off-topic, inappropriate, spam or requires attention for another reason.
   • Click on the bookmark icon to bookmark this post. You can quickly go back to Bookmarked posts from your user account.
   • Click on the Reply icon to post a reply to this post. You can type your reply, add links and images, use formatting, etc. in the window that pops up. Once your reply is ready click on the Reply button at the left bottom. If you do not want to post it (yet) you can click on Close next to the Reply button and your reply will be saved as a draft.
4. At the bottom of the post, some statistics around when the topic was created, when the last reply was posted, and how many replies, views, users and likes there have been on this topic are shown. If this part is expanded with the button on the right-hand side, then Frequent Posters in this topic are shown too. You can collapse this part by clicking on the ^ icon.
5. On the right-hand side of the topic a timeline of the posts within the topic is shown. You can drag this up or down to go to the original post (drag up) or to the most recent reply (drag down) while scrolling through all replies. The timeline will also indicate which post of how many in total within this topic you are currently viewing.
6. Underneath the topic’s timeline, there are 2 icons:
   • You can use the curved arrow icon to start a Reply to this topic, which will bring up the same Reply window as the one described under bullet 3.e. above.
   • You can click on the bell icon to start watching or tracking this topic or to mute it. You have 4 options that pop up when you click on the icon, see the screenshot below. By default, this is set to Normal for all topics.

3.    Creating a New Topic

After you click on the “+ New Topic” button, the following window will pop-up at the bottom of your browser:

1. At the right top of this window, you have the options to make this composer window full screen by clicking on the icon with the 2 arrows pointing out and to minimize the composer window by clicking on the collapse icon (upside down caret).
2. Type the title of your topic in this box or if this is a topic linked to another one, paste the hyperlink to that other topic here.
3. Choose one of the 5 categories that this topic belongs to from the list that pops up when you click on the “category…” box.
4. Click on the “optional tags” box to select and/or search for any tags to associate with your new topic. Tagging topics is good practice so that other users can easily find topics of their interest.
5. In the edit toolbar, you can, from left to right:
   • Quote a whole post or part of a post by clicking on the speech bubble icon.
   • Make text strong (bold) by clicking on the B icon.
   • Emphasize text (italics) by clicking on the I
   • Insert a hyperlink by clicking on the chain icon.
   • Use blockquotes by clicking on the ” icon to define quotations.
   • Use preformatted text (like for example HTML) by clicking on the </> icon.
   • Upload something from your computer (like for example an image) by clicking on the upload icon (arrow pointing up).
   • Create a bulleted list by clicking on the icon with 3 lines and square bullets.
   • Create a numbered list by clicking on the icon with 3 lines and number bullets.
   • Use emojis by clicking on the smiley face icon.
   • Access settings by clicking on the gear icon.
6. Enter your text in this area. You can use the icons on the edit toolbar to format, and you can also drag or paste images here.
7. Once your post is ready, you can click on the Create Topic button to post it. If you do not want to post it yet, you can click on the Close button to save it as a draft to come back to later.

4.    Options from the Menu

The third icon at the top right of the homepage opens a Menu that you can use to quickly navigate to different parts of the Frogger Pond Community.

1. In the top part of the menu, you have following options to navigate to:
   • Latest – takes you to the homepage where the topics are sorted by topics with the most recent posts.
   • New – takes you to the homepage where only new topics in the last few days are shown. In your Preferences you can change what is considered new.
   • Unread – takes you to your unread topics. In your Preferences you can change what is considered unread.
   • Top – takes you to the homepage where the topics are sorted for those with the most activity.
2. In the second part of the menu, you have options to start exploring following:
   • Badges – takes you to the Badges page where it is shown which badges users can earn and how. For each badge it also shows you how often the badge has been granted to a user with a counter at the right top. The badges that you have earned yourself already are shown with a green check mark at the left top.
   • Users – this takes you to a list of users which are sorted by most active in the last week. You can change the timeframe to look at most active users by year, quarter, month, or day instead or over all time of the community. You can sort the list in different ways by clicking on the column headings of username, likes received, likes given, topics created, topics posted, topics viewed, posts read, and days visited. There are also options to filter the list by username or to find the users of any groups. Clicking on a username in this list opens a window with some of the user’s details and the option to private message them. From here, clicking on their username takes you to the user’s profile page where you can view the user’s profile details, Summary, Activity and Badges. Some of this is discussed in more detail in the next section “5. Managing your User Account”.
   • Groups – takes you to the page where the existing Frogger Pond groups are listed. You can click on a group to see its members, activity, and permissions.
   • Tags – takes you to a list of all the tags that have been used on any of the topics. You can click on a tag to show you all topics that have that tag associated with them.
3. In the Categories section of the menu, you can click on one of the 5 categories to be taken to the topics within that category.
4. From the bottom part of the menu, you can navigate to:
   • About – here you can find a description of Optilogic and an overview of the Admins, Moderators and Site Statistics for the Frogger Pond Community. From here you can also easily navigate to the FAQ, Terms of Service, and Privacy sections.
   • FAQ – this takes you to guidelines on how everyone can ensure to be a constructive and civil user of the Frogger Pond Community. This is a recommended read for all new users.
   • Keyboard Shortcuts –this opens a list of keyboard shortcuts that are available to Frogger Pond Community users. These shortcuts make browsing and interacting with the community easier and quicker.

5.    Managing your User Account

When you click on your profile picture at the top right of the homepage, a small window that gives you quick access to (from left to right) Notifications (bell icon), Bookmarks (bookmark icon), Messages (envelope icon), and Preferences (person icon) opens up:

If you click on the upside-down caret at the bottom of notifications, bookmarks, or messages or click on any item in the preferences list, you will be taken to that area of your account. In the following screenshot, we have gone to the Messages section of the user account:

1. At the top of the Account page the username and profile picture (if any) are shown. If this section is expanded by clicking on the Expand button at the top right, then statistics on when the user joined, when they were last seen, what their trust level is (more on this in the next section “6. Recommended Reading for New Users”), and their email address are shown too.
2. Users can navigate to different parts of their account here by clicking on:
   • Summary – here the user’s statistics on how often they have visited, number of topics viewed/read, likes given/received, top replies & topics, top badges, etc. are shown.
   • Activity – the user’s activity can be viewed and accessed here. From the menu on the left, you can quickly go to your Topics, Replies, Read posts, Drafts, Likes, and Bookmarks.
   • Notifications – by default All notifications will be shown here which includes emails that have been sent to the user. These can be dismissed if the user so chooses. From the menu on the left the Notifications can be filtered for Responses, Likes, Mentions, and Edits.
   • Messages – this shows the messages the user has received and sent. By default, the Inbox is shown and from the Menu on the left, user can choose to just look at Sent, New, Unread or Archived messages. You can use the New Message button at the bottom of this menu to send a private message to another user.
   • Badges – any badges the user has been granted will be shown here.
   • Preferences – here the Account details of the user will be displayed and can be edited. Accessible here also from the Menu on the left are Security, Profile, Emails, Notifications, and Interface settings. Under Notifications a user can specify what they consider new topics and one can also set up a custom notification schedule here. Users can set up watching / tracking / muting of Categories, Users and Tags in this Notifications section.

6.    Recommended Reading for New Users

Using a platform you may not be familiar with can be overwhelming. To help new users getting started, we recommend reading the following items. These are also mentioned in the “Welcome to Optilogic!” message all new users of the Frogger Pond Community receive.

1. Blog post by Discourse on New User Tips and Tricks
2. Blog post by Discourse on User Trust Levels
3. The Frogger Pond Community FAQ which contains guidelines on constructive and civil use of the community.

We look forward to your questions & contributions over at the Frogger Pond!

There are two methods for establishing a secure connection to the Optilogic platform:

• App Key
• API Key

An App key is a code that can be linked to your account and will not expire. API keys are generated with code and only last for one hour before they expire. Both keys can be useful depending on how you wish to access the platform. Without either an App Key or an API Key you will not be able to run any API endpoints.

Generating an App Key

Login to the Optilogic website and click on your name in the top right corner, then click on “Account.”

Click on the “App Key Management” tab from their name your app key and click on the “Create Key” button.

At this point you may copy your App Key to be used for authentication purposes.

Generating an API Key

To generate an API key you will need to leverage python and the following instructions.

In a python file copy and paste this code and replace the USERNAME and PASSWORD with your own. Make sure to remove both sets of {{}} curly brackets so that it looks like this: headers = {‘X-USER-ID’: ‘CMorrell’ }

import requests

url = ‘https://api.optilogic.app/v0/r…’
headers = {
‘X-USER-ID’: ‘{{user_id}}’,
‘X-USER-PASSWORD’: ‘{{user_password}}’
}

‍

response = requests.request(‘POST’, url, headers=headers)
print(response.text)

The result of this code will be an API key that can be used for authentication.

When running geocoding through the default Mapbox provider, all of the available location data from the Customers, Facilities and Suppliers table will be used to try and determine the latitude and longitude coordinates. Mapbox will use all of these components and perform the best mapping possible and will return a latitude / longitude coordinate along with a confidence score. By default, Cosmic Frog will only accept scores with a confidence score of 100. You can optionally turn this option off and the top confidence score will then be returned by Mapbox.

More information on how Mapbox calculates latitude and longitude coordinates can be found here: Mapbox Geocoding Documentation.

If you’d like to use an alternate provider instead of Mapbox, setup instructions can be found here: Using Alternate Geocoding Providers.

Every account holder has access to create the Global Supply Chain Strategy demo model. Following is an overview of the features of the model (and of Cosmic Frog).

If you wish to build the model instead, please follow the instructions located here: Build Your First Cosmic Frog Model

When running Cosmic Frog models and other jobs on the Optilogic platform, cloud resources are used. Usage of these resources is billed based on the billing factor of the resource used for the job. Each Optilogic customer has an amount of cloud compute hours included in their Master License Agreement (MLA). Users may want to check how many of these hours have been used up and in this documentation 2 ways to do so will be covered. In the last section we will touch on how to best track hours at the team/company level.

Option 1: Account > Usage

The first option for hours tracking that will be covered is through the Usage tab in the user’s Account:

1. On the Optilogic platform, open the drop-down menu by clicking on the down arrow to the right of your username, and then click on the first option in the menu: Account.
2. Click on the Usage tab, the tab farthest right.
3. Configure the period you want to see the usage for by using the following options:
   1. Group By: select the time periods you want to sum the usage hours up to. Options are: Total, Day, Week, Month, Year.
   2. Time Window Preset: select the timeframe you want to see the usage hours for. Options are: Week to Date, Month to Date, Year to Date, and Custom.
   3. Start and End: for the Time Window Presets of Week to Date, Month to Date, and Year to Date these are automatically filled out. For the Time Window Preset of Custom, user can manually enter the start and end dates of the timeframe for which the usage hours will be shown.
4. Based on the timeframe set as described under the previous bullet point, the Total Billed Compute Time will be shown here.
5. Bar charts for the time periods configured under bullet 3 (in this screenshot grouped by months) are shown here. When hovering over a bar in a chart, the number of hours will pop up.
   1. The dark green bar on the left indicates the sum of the billed compute time of the jobs that were run during that period.
   2. The lighter green bar in the middle indicates the sum of the compute time of the jobs that were run during that period.
   3. The blue bar indicates the sum of the total RAM (in gigabytes) used for the jobs that were run during that period.
6. The table at the bottom shows the sums of the compute time, billed compute time, CPU cores, and RAM for the entire configured timeframe and for the individual time periods within that timeframe.
7. User has the option to export this data, either to CSV format or to the Excel .xlsx format by clicking on these buttons.

If a user is asked by their manager to report the hours they have used on the Optilogic platform, they can go here and use the Custom Time Window Preset option to align the start and end date of the reporting period with the dates of the MLA. They can then report back the number shown as the Total Billed Compute Time (box 4 in the above screenshot).

Option 2: Run Manager

Through the Run Manager application on the Optilogic platform, user can also analyze their jobs run, including retrieving the Total Billed Compute Time:

1. On the Optilogic platform, go to the Run Manager in the list of applications on the left-hand side of the screen. Should you not see it there, click on the icon with the 3 dots to show all applications; the Run Manager should become visible then.
2. Along the top of the list of jobs run there is an option to View Charts, which will be covered next.
3. Another option is to download information of the jobs to a CSV format file. This will be covered a bit further below too.

After clicking on the View Charts icon, a screen similar to the following screenshot will be shown:

1. The View Charts icon now has changed to a View Jobs List icon to allow user to go back to the list view.
2. In the first part of the charts section, some overall statistics are listed:
   1. First, the number of jobs that did not complete successfully (status = Error), that ran to completion successfully (status = Done), and that were cancelled (status = Cancelled) are listed.
   2. Next, the Total Compute Time and Total Billed Compute Time are listed. The total billed compute time is calculated based on the time the job took and the Billing Factor of the resource used for the job.
3. The title of the chart, here it is Job Counts by Status. There are 3 options for showing the job counts, click on the following icons to toggle between them:
   1. Show Jobs by Resource Size.
   2. Show Jobs by Tag.
   3. Show Jobs by Status (the default, shown in blue when active).
4. Click on this i-icon to see guidance on how to pan and zoom within the chart.
5. Here the bar for the week of July 15-22 shows that there were 15 jobs with status = Error (did not complete successfully), 37 with status = Done (did complete successfully), and no jobs with status = Cancelled during this week.
6. At the bottom of the chart user can toggle all status types on or off simultaneously by clicking on Toggle All. The individual statuses can also be toggled on or off by clicking on them.
7. The types of Jobs included in the statistics and chart can be filtered by clicking on the Type filter drop-down. The following 4 types of jobs exist, and each can be included or excluded: Scenario, Model Run, Python, and Utility.
8. When clicking the Download button, user can choose to either Download All Jobs or Download Current Filtered Jobs. When downloading, the jobs are saved to a jobs.csv file which will look similar to this screenshot:

If a user needs to report their hours used on the Optilogic platform, they can download this jobs.csv file and:

1. Filter columns B-D to align the dates of jobs included to the period of the MLA.
2. Sum column G billedComputeTime to get the Total Billed Compute Time for the filtered date range and report this number back.

Individual & Team/Company Level Tracking

Currently, only tracking of usage hours at the individual user level is available as described above. To get total team or company usage, a manager can ask their users to use 1 of the above 2 methods to report their Total Billed Compute Time and the manager can then add these up to get the total used hours so far. Tracking at the team/company level is planned to be made available on the Optilogic platform later in 2024.

With Intelligent Greenfield Analysis (the Triad engine in Cosmic Frog), you have control over several different solve settings. For ease of use with scenario modeling, these have been placed in a dedicated table called Greenfield Settings. This allows for quick scenario building that leverages the column names. We will cover the settings which can be configured on the Greenfield Settings table and show an example of how scenarios can be used to change these settings.

Following screenshot shows the Greenfield Settings table:

1. In Cosmic Frog’s Module menu, choose Data to go to the Data module.
2. The Input Tables are showing in the Data module as the square grid icon has been selected. Output and Custom tables can be shown by clicking on the other 2 icons, the round grid icon for Output tables, and the square icon with solid top rows for Custom tables.
3. The Greenfield Settings table can be found in the Functional Tables section of the input tables list. When it is clicked it is also opened and made the active table.
4. The Greenfield Settings table has 1 record and this record is pre-populated with defaults in all Cosmic Frog models. Users can change the values of this record, either in the table itself so the setting apply to all scenarios that are being run, or through scenarios, so that the change only applies to a specific scenario. We will see an example of changing a Greenfield setting through a scenario further below.

An explanation of each setting is as follows:

• Min Number Of New Facilities – The minimum number of new facility locations to be opened during solve. This will include any Candidate Facilities that are selected as well as any Greenfield Facilities. This value is ignored if Minimize Total Facilities is set to True.
• Max Number Of New Facilities – The maximum number of new facility locations to be opened during solve. This will include any Candidate Facilities that are selected as well as any Greenfield Facilities. This value is ignored if Minimize Total Facilities is set to True.
• Minimize Total Facilities – If True, the number of new facility locations to be opened will be minimized (Greenfield Facilities and Candidate Facilities). If False, the total cost (Facility Fixed Cost and Transportation Cost) will be minimized.
• Exclude Facilities – If True, all Facilities in the model will be ignored during the Greenfield run. This value is set to False if Only Use Facilities As Candidate Locations is set to True.
• Exclude Suppliers -If True, all Suppliers in the model will be ignored during the Greenfield run.
• Only Use Facilities As Candidate Locations – If True, the selection pool for Greenfield locations will be restricted to Facilities that are Included. Facilities with a Facility Status of ‘Open’ will be forced to open and have their fixed operating cost charged in the Greenfield solve. Facilities with a Facility Status of ‘Consider’ will have the option to be opened as a Candidate Facility location and if selected, their fixed operating cost will be charged.
• Customer Max Sourcing Distance – The maximum distance that any Customer can source a flow from a Greenfield Facility, a Candidate Facility, or an Existing Facility.
• Greenfield Facility Min Throughput -The minimum quantity of demand that a Greenfield Facility must service to be selected. This will apply to all Facilities, both new and existing, for the Greenfield solve.
• Greenfield Facility Max Throughput – The maximum quantity of demand that a single Greenfield Facility can service. For existing or candidate Facilities the throughput capacity will be specified in the Facilities table.
• Greenfield Facility Fixed Cost – The fixed cost applied to each Greenfield Facility used in the solve.

• Customer Fulfillment Cost – The cost charged for all flows from Facilities to Customers on a quantity-distance basis.
• Procurement Cost- The cost charged for all flows from Suppliers to Facilities on a quantity-distance basis.
• Run Location Refinement Heuristic – If True, the Greenfield Facilities selected by the solver can be improved beyond their initial location that would be co-located with a Customer / Cluster location.
• Customer Cluster Radius – The distance that Customers should be clustered over. The UOM of this distance measure is the primary distance UOM specified in the Model Settings table.
• Auto Detect Cluster Radius – If True, large models with more than 3000 customers will be automatically clustered to have less than 3000 nodes during the optimization phase. Clustomers are then disaggregated for outputs.
• Clustering Approach -The method used to identify customer clusters. The ‘fcluster’ method requires a computation of the full distance matrix between all customers pairs which can be a time consuming process. The ‘utm’ method is based on the Universal Transverse Mercator (UTM) coordinate system and looks to identify clusters in each UTM zone. While the UTM based method may in some cases be less accurate it does not need to precompute the full distance matrix between all customer pairs.

Note that the “Getting Started with Intelligent Greenfield Analysis” help article contains a visual explanation of customer clustering too.

Finally, we will look at an example where a scenario item changes a Greenfield setting:

1. From the Module menu we have chosen Scenarios to go the Scenarios part of Cosmic Frog.
2. In the list of scenarios set up in the model, there is one named 01b_7 Optimal Facilities.
3. This scenario has 4 scenario items of which the second one, named Max 7 New Facilities, is selected and this is the one of which the configuration is shown on the right hand-side.
4. The Greenfield Settings table is selected as the table to which the scenario item’s changes apply. Users can select the table from the drop-down list and one can also start typing the name of the table to reduce the drop-down list to only items containing the typed text.
5. The action is set to change the value of the Max Number Of New Facilities field in the Greenfield Settings table to 7. When starting to type the name of a field in the action box, autocomplete suggestions pop up which the user can click on to select the suggested text.