Lumina Tariff Optimizer – Modeling Tariff Strategies

Optilogic introduces the Lumina Tariff Optimizer – a powerful optimization engine that empowers companies to reoptimize supply chains in real-time to reduce the effects of tariffs. It provides instant clarity on today’s evolving tariff landscape, uncovers supply chain impacts, and recommends actions to stay ahead – now and into the future.​

‍

Manufacturers, distributors, and retailers around the world are faced with an enormous task trying to keep up with changing tariff policies and their supply chain impact. With Optilogic’s Lumina Tariff Optimizer, companies can illuminate their path forward by proactively designing tariff mitigation strategies that automatically consider the latest tariff rates.

‍

With Lumina Tariff Optimizer, Optilogic users can stay ahead of tariff policy and answer critical questions to take swift action:

• How will tariffs affect overall profitability and financial forecasting?
• Should you apply for exemptions or duty drawback programs?
• How will tariffs affect the cost of raw materials, components, and finished goods?
• Do you need to find alternative suppliers in non-tariffed countries?
• What is your risk exposure if key suppliers or customers are impacted by tariffs?

The following 7-minute video gives a great overview of the Lumina Tariff Optimizer tools:

What is Lumina Tariff Optimizer?

Optilogic’s Lumina Tariff Optimization engine can be leveraged by modelers within Cosmic Frog or be leveraged within a Cosmic Frog for Excel app for other stakeholders across the business to evaluate the tariff impact to their end-to-end supply chain. Optilogic enables users to get started quickly with Lumina with several items in the Resource Library that include:

1. A Cosmic Frog example model named Tariffs which shows users how tariffs can be modeled and how they impact the optimal solution. This model can be found here in Optilogic’s Resource Library application. Users can copy the model from the Resource Library to their own account and review it in Cosmic Frog. They can also use it as a starting point to model their own tariffs.
2. A Cosmic Frog for Excel App named Excel Micro App Tariffs Rapid Optimizer. In this Excel application, quick scenarios that test the impact of increasing/decreasing tariffs can be configured, run, and the outputs analyzed. As this Excel application does not require Cosmic Frog modeling expertise, the application is very suitable for executives and managers to run their own tariff sensitivity scenarios. This application and related files can be found here in Optilogic's Resource Library.
3. For users that do not have all the data available to start modeling tariffs in Cosmic Frog, Optilogic has made several utilities available within Cosmic Frog to generate the origin-destination matrix, retrieve HS codes (Harmonized System Codes) for products, and lookup current tariffs (duty rates) based on the HS code. For retrieving HS codes and looking up duty rates the utilities hook into APIs from Avalara, a world leader in cross border tax compliance software solutions.
4. A second Cosmic Frog for Excel App named Excel Micro App Tariffs Builder. This Excel application includes the same utilities as mentioned under the previous bullet point to generate the origin-destination matrix, retrieve HS codes based on product information, and lookup current tariffs based on these HS codes, and can easily be used by users who are less familiar with Cosmic Frog to create the tariffs input data needed. This application and related files can be found here in Optilogic's Resource Library.

This documentation will cover each of these Lumina Tariff Optimizer tools, in the same order as listed above.

Tariffs Model

The first tool in the Lumina Tariff Optimizer toolset is the Tariffs example model which users can copy to their own account from the Resource Library. We will walk through this model, covering inputs and outputs, with emphasis on how to specify tariffs and their impact on the optimal solution when running network optimization (using the Neo engine) on the scenarios in the model.

Tariffs Model – Basic Inputs

Let us start by looking at the map of the Tariffs model, which is showing the model locations and flows for the Baseline scenario:

This model consists of the following sites:

1. Seventy suppliers: 31 located in China and the rest in Europe. These supply raw materials: RM1 through RM9.
2. Four ports: 1 in China (Shanghai) to receive raw materials RM1, RM2, and RM3 from the Chinese suppliers and ship them to the ports in Mexico (Altamira) and the USA (Charleston), and 1 in Europe (Rotterdam) to receive raw materials RM4 through RM9 from the European suppliers and ship them to the ports in Mexico and the USA.
3. Two Factories: 1 in the USA (Princeton) and 1 in Mexico (El Bajio), they receive the raw materials from their respective ports and manufacture the finished goods.
4. Seven distribution centers (DCs): all located in the USA, they receive the finished goods from the 2 factories and ship out to the customers (CZs).
5. Customers: there are 1,333 customers, all based in the USA; they are served by the 7 DCs.

Next, we will have a look at the Products table:

1. We are on the Products input table.
2. There are 3 finished goods modeled here: Space Suits, Consumables, and Rockets.
3. Raw materials are included in this model; there are 9 of them - RM1 through RM9. Only RM1, RM2, and RM3 are shown in the screenshot.
4. Since tariffs are commonly a duty rate based on the value of the product being moved, it is important to populate the Unit Value field in the products table.

As mentioned above, raw materials RM1, RM2, and RM3 are supplied by Chinese suppliers and the others 6 raw materials by European suppliers, which we can confirm by looking at the Supplier Capabilities input table:

The Bills Of Materials input table shows that each finished good takes 3 of the Raw Materials to be manufactured; the Quantity field indicates how much of each is needed to create 1 unit of finished good:

Looking at the Production Policies input table, we see that both the US and Mexico factory can produce Consumables, but Rockets are only manufactured in Mexico and Space Suits only in the US:

To understand the outputs later, we also need to briefly cover the Flow Constraints input table, which shows that the El Bajio Factory in Mexico can at a maximum ship out 3.5M units of finished goods (over all products and the model horizon together):

Tariffs Model – Tariff Inputs

To enter tariffs and take them into account in a network optimization (Neo) run, users need to populate the new Tariffs input table:

There are also 2 new Neo output tables that will be populated when tariffs are included in the model, the Optimization Path Flow Summary and the Optimization Tariff Summary tables:

Tariffs can be specified at multiple levels in Cosmic Frog, so users can choose the one that fits their modeling needs and available data best:

• from individual origin location or region or country
• to individual destination location or region or country

In order to model tariffs from/to a region or country, these fields need to be populated in the Customers, Facilities, and Suppliers tables:

1. To show an example, we are on the Facilities input table.
2. The Facilities input table contains fields for Facility Name (required), Region, and Country, among others. If wanting to model tariffs at the Region or Country level, it is important that these fields are populated in the Facilities table (and Customers and Suppliers tables).
3. In this Tariffs example model, we will model tariffs based on Regions (both from and to). We see that for each record this field is populated in the Facilities table. The regions in this model are China (CN) for all Chinese locations, Mexico (MX) for all Mexican locations, US for all locations in the USA, and Europe (EU) for all European locations. Users will notice the Region fields in the Customers and Suppliers tables are also populated in this model.

Tariffs Model – Tariffs Table

In the Tariffs input table, all path origin location (furthest upstream) – path destination location (furthest downstream) – product combinations to which tariffs need to be applied are captured. There can be any number of echelons in between the path origin location and path destination location where the product flows through. Consider the following path that a raw material takes:

The raw material is manufactured/supplied from China (the path origin), it then flows through a location in Vietnam, then through a location in Mexico, before ending its path in the USA (the path destination, where it is consumed when manufacturing a finished good). In this case the tariff that is set up for this raw material with path origin = China, and path destination = USA will be applied. The tariff will be applied to the segment of the path where the product arrives in the region / country of its final destination. In the example here, that is on last leg (/lane / segment) of the path, e.g. on the Mexico to USA lane.

‍

If we have a raw material that takes the same initial path, except it ends in Mexico to be consumed in a finished good, then the tariff that is set up for this raw material with path origin = China and path destination = Mexico will be applied. To continue from this example: then if this finished good manufactured in Mexico is shipped to the US and sold there, and if there is a path with a tariff set up from Mexico to USA for the finished good, then that tariff will be applied (path origin = Mexico, path destination = USA). I.e. in this last example the entire path is just the 1 segment between Mexico and USA.

‍

So, now we will look how this can be set up in the Tariffs input table:

1. We are on the Tariffs input table.
2. The Path Origin Property field, which indicates the level at which the origin (the most upstream location of a path) is specified, is not shown in this screenshot, but it is set to Region (other options are Name for individual locations or Country). The Path Origin Value field then needs to be set to the path start value in accordance with the Path Origin Property field's value, which will be MX, CN, US or EU in this example model as explained above at the end of the previous section.
3. The Path Destination Property field, which indicates the level at which the destination (the most downstream location of a path) is specified, is not shown in this screenshot, but it is set to Region (other options are Name for individual locations or Country). The Path Destination Value field then needs to be set to the path end value in accordance with the Path Destination Property field's value, which will be MX, CN, US or EU in this example model as explained above at the end of the previous section.
4. Product Name: the product to which the tariff applies.
5. Duty Rate: the tariff that will be applied for this path origin – path destination – product combination. This is a percentage applied to the product value (set on the Products table as mentioned further above). If the rate is 10%, then user needs to put 10 as the value in the Duty Rate field. The total duty on a path is then calculated as: flow quantity * product value * duty rate.
6. Status: like most Cosmic Frog input tables, the Tariffs table contains a Status field through which the whole record can easily be included or excluded during a scenario run.
7. Notes: also a field present on most Cosmic Frog input tables, often used to enter text based on which the Status field can be changed to Include or Exclude during a scenario run.
8. These 3 records show that for the raw materials RM1, RM2, and RM3, a 15% tariff is applied on paths starting in the China region and ending in the Mexico region. The Notes field indicates that these are older tariffs. Note that not all records with the older tariffs are being shown here, but they are all set to 15% in the example model.
9. These 3 records show that for the raw materials RM1, RM2, and RM3, a 65% tariff is applied on paths starting in the China region and ending in the US region. The Notes field indicates that these are new tariffs.
10. These 3 records show that when applying the New Tariffs for the raw materials RM1, RM2, and RM3, a 10% tariff is applied on paths starting in the China region and ending in the Mexico region.
11. This record shows that when applying the New Tariffs for the finished good Consumables, a 40% tariff is applied on paths starting in the Mexico region and ending in the US region.
12. Note that the Status of all records in the Tariffs table have been set to Exclude, they will be selectively included in scenarios by using the text in the Notes field as we will see in the next screenshot.
13. There are several fields which are also part of this table, but are not shown in the screenshot:
    
    1. HS Code: user can enter the Harmonized System code of the product the record is being set up for here. This can facilitate looking up of duty rates.
    2. Unit Cost: instead of or in addition to using the duty rate field to specify tariffs, user can also specify a unit cost as part of the tariff that needs to be applied. The tariff cost calculated based on this is: flow * unit cost. The Unit Cost UoM field specifies the unit of measure for flow in this calculation, see next bullet.
    3. Unit Cost UoM: the unit of measure used for flow in the previous bullet. For example: EA (eaches) for quantity, LB (pounds) for weight, or CFT (cubic foot) for volume.

Please note:

• That the Path Origin Property and the Path Destination Property do not need to be the same, one can set up tariff records from countries to individual locations for example. And different records within the Tariffs table can also use different properties.
• When populating the Tariffs table, remember that all possible path origin – path destination - product combinations should be included. If a possible combination is not listed in the Tariffs table, but it is a possible flow path based on the Transportation Policies table, the path can be used, and no tariffs will be applied to it.
  • To ensure complete enumeration of all path origin – path destination – product combinations, users can make use of the Generate Tariff Paths utility, see the “Utility 1 Generate Tariff Paths” section further below.

Tariffs Model – Running Scenarios

Three scenarios were run in the Tariffs example model:

1. We are in the Scenarios module in Cosmic Frog
2. Three scenarios have been set up:
   
   1. Baseline: this uses the set of Tariffs records that are labeled as Old Tariffs. There tariffs are 15% across the board, except on paths from Mexico to Canada, where tariffs of 10% are applied. All these tariff rates are illustrative in nature.
   2. Optimized New Tariffs: a flow constraint of a maximum of 3.5M units out of the El Bajio factory in Mexico that is applied in the Baseline scenario is removed in this scenario. In addition, this scenario uses the set of Tariffs records that are labeled as New Tariffs, which as we have seen in the previous screenshot are considerably higher than the old ones on certain paths (e.g. 40% and 65%, again illustrative in nature).
   3. Baseline New Tariffs: like the Optimized New Tariffs scenario, this scenario uses the set of Tariffs records that are labeled as New Tariffs. The flow constraint of a maximum of 3.5M units out of the El Bajio factory in Mexico is unchanged and therefore applied in this scenario.
3. The Include Old Tariffs scenario item that is included in the Baseline scenario is shown here. Its configuration is as follows:
   
   1. Table Name - the Tariffs table is selected as the table to make the scenario change to.
   2. Actions - the change to make to the table is entered here: Status = 'Include', meaning the value of the Status field will be set to Include.
   3. Conditions - the Condition Builder is used to filter out the records of the Tariffs table for those where the Notes field contains Old Tariffs, and only for this subset of records will the Status field be set to Include.

Tariffs Model – Outputs

Now, we will look at the outputs for these 3 scenarios, first at a higher level and later on, we will dig into some details of how the tariff costs are calculated as well.

‍

The Financials stacked bar chart in the standard Optimization Scenario Comparison dashboard in the Analytics module of Cosmic Frog can be used to compare all costs for all 3 scenarios in 1 graph:

1. Click on the Module-menu icon (3 horizontal bars icon at the left top in Cosmic Frog) and choose Analytics from the drop-down. Then click on the “Optimization Scenario Comparison” dashboard in the list of dashboards on the left to open it.
2. The first chart in this dashboard is the “Financials: Scenario Cost Comparison” stacked bar chart. By scenario, it shows all costs in a color-coded manner. The purple cost bucket is the tariff costs.
3. Comparing the 3 scenarios, we notice:
   
   1. The Baseline scenario has the lowest transportation costs and lowest tariff costs as compared to the other 2 scenarios. This is driven by the tariffs, which were much lower prior to any major changes taking place.
   2. The Baseline New Tariffs scenario has slightly higher transportation costs than the Baseline scenario, but these are lower than those of the Optimized New Tariffs scenario. This scenario has the  highest tariff costs as compared to the other 2 scenarios. This is driven by the tariffs, which are steep. The increased transportation costs are the result of the model trying to avoid even higher tariff costs by using some more expensive transportation paths.
   3. The Optimized New Tariffs scenario has higher transportation costs as compared to the other 2 scenarios. However, its Tariff costs are much lower than in the Baseline New Tariffs scenario and somewhat increase as compared to the Baseline scenario, so it is likely utilizing the El Bajio Factory a lot more (since the flow constraint is removed) which increases transportation cost, but also avoids the steepest tariffs.

To compare the Tariffs by path origin – path destination and product, a new “Optimization Tariffs Summary” dashboard was created. We will look at the Baseline New Tariffs scenario first, and the Optimized New Tariffs scenario next:

1. We are on the new Optimization Tariffs Summary dashboard.
2. The scenario selected is Baseline New Tariffs.
3. We see in the chart that by far most of the tariff costs (more than $477M) are due to RM1, RM2, and RM3 coming from China into the US (where the Princeton Factory uses them to manufacture the Consumables finished good).

1. We have now changed the scenario to look at the Optimized New Tariffs one.
2. We see that the Mexico to US path now has the highest tariff costs, made up of those for Rockets and Consumables. This tells us that production of the Consumables has shifted from the US (Princeton) to Mexico (El Bajio) now that El Bajio is no longer constrained. The tariff costs related to RM1, RM2, and RM3 are down to around $73.4M altogether, as they are now going to Mexico rather than going to the US (still originating from China).

Note that in the Appendix it is explained how this chart can be created.

Next, we will take a closer look at some more detailed outputs. Starting with how much demand there is in the model for Rockets and Consumables, the 2 finished goods the Mexican factory in El Bajio can manufacture. The next screenshot shows the Optimization Demand Summary network optimization output table, filtered for Rockets and with a summation aggregation applied to it to show the total demand for Rockets at the bottom of the grid:

Next, we change the filter to look at the Consumables product:

In conclusion: the demand for Rockets is nearly 3.5M units and for Consumables nearly 10.5M. Rockets can only be produced in Mexico whereas Consumables can be produced by both factories. From the charts above we suspected a shift in production from US to Mexico for the Consumables finished good in the Optimized New Tariffs scenario, which we can confirm by looking at the Optimization Production Summary output table:

1. The Optimization Production Summary output table is filtered for the 2 scenarios that use the new tariffs and for the finished goods Rockets and Consumables. In the Baseline New Tariffs model where the El Bajio factory is limited to 3.5M units we see:
   
   1. All Rockets are made in the El Bajio factory in Mexico, since it is the only factory that can produce Rockets.
   2. The El Bajio Factory also produces a small number of Consumables, it cannot make more of them as with this amount of 2,520 units, the limit of the flow constraint on the El Bajio Factory is reached.
   3. Except for the 2,520 units of Consumables made by the El Bajio factory, all other Consumables are produced by the Princeton Factory (nearly 10.5M units)
2. In the Optimized New Tariffs scenarios, all Rockets are still made in the El Bajio factory in Mexico, since it is the only option, but now all Consumables are made here too. This is possible since the constraint that limited the amount of flow out of the El Bajio Factory has been removed.

Since the production of Consumables requires raw materials RM1, RM2, and RM3, we expect to see the above production quantities for Consumables to be reflected in the amount of these raw materials that was moved from the suppliers in China to the US vs to Mexico. We can see this in the Optimization Flow Summary network optimization output table, which is filtered for the 2 scenarios with new tariffs, Port to Port lanes, and these 3 raw materials:

1. As the bulk of the Consumables are being produced in the US in the Princeton Factory in the Baseline New Tariffs scenario, we see the bulk of RM1, RM2, and RM3 (2x, 3x, and 4x the production quantity respectively, based on the Bills Of Materials) being moved between the China port (Shanghai) and the US port (Charleston).
2. For the 2,520 units of Consumables being produced in Mexico in the El Bajio Factory in the Baseline New Tariffs scenario, we see the corresponding number of units being moved from the China port to the Mexico port (Altamira).
3. In the Optimized New Tariffs scenario, all units of RM1, RM2, and RM3 are moved from the China port to only the Mexico port as all Consumables are now produced in the El Bajio Factory.

The custom Optimization Tariff Summary and Optimization Path Flow Summary output tables are automatically generated after running a network optimization on a model with a populated Tariffs table. The first of these 2 is shown in the next screenshot where we have filtered out the raw materials RM1, RM2, and RM3 again, plus also the Consumables finished good for the 2 scenarios that use the new tariffs:

1. For the 3 raw materials moved from China to the US in the Baseline New Tariffs scenario we see the flow quantity numbers match those of the flow quantities shown in the previous screenshot. The product values (as specified in the Products input table) for RM1, RM2, and RM3 are $8, $6, and $9 respectively. The tariff (duty rate) set for paths starting in China and ending in the US as set on the Tariffs input table is 65%. As an example, we will calculate the Tariff Cost for RM1: 20,979,840 (flow quantity in units) * $8 (product value) * 0.65 (duty rate) = $109,095,168. The Tariff Cost for these 3 raw materials adds up to just $over 477M. The other tariff costs related to RM1, RM2, RM3 (CN to MX), and Consumables (MX to US) in the Baseline New Tariffs scenario (rows 1-3 and 7) add up to about $32.5k.
2. Looking at the Optimized New Tariffs scenario, we again see the flow quantity numbers matching those in the previous screenshot. Now they are all ending their path in Mexico however, so instead of the CN to US tariff, we apply the CN to MX one, which is set to 10%. For RM1, the Tariff Cost calculation becomes: 20,984,880 * $8 * 0.1 = $16,787,904. The Tariff Cost for these 3 raw materials adds up to just under $73.5M.
3. The other tariff cost related to Consumables (MX to US) in the Optimized New Tariffs scenario (rows 11) equals $62,954,640, based on a 40% duty rate. Even though the Baseline New Tariffs model only has very low total tariff costs of $15,120 for Consumables (MX to US), this much higher tariff cost in the Optimized New Tariffs scenario is outweighed by a greater reduction in Tariff Costs for RM1, RM2, and RM3.

Where the Optimization Tariff Summary output table summarizes the tariffs at the scenario - path origin – path destination – product level, the Optimization Path Flow Summary output table gives some more detail around the whole path, and on which segments the tariffs are applied. The next 2 screenshots show 6 records of this output table for the Tariffs example model:

For the 2 scenarios that use the new tariffs, records are filtered out for raw material RM1 where the Path Start Location represents the CN region and the Path End Location represents the MX region. These Path Start and End Locations are automatically generated based on the Path Origin Property and Value and Destination Property and Value set in the Tariffs input table. Scrolling right for these 6 records:

We see that the path for RM1 is the same in both scenarios: originate at location Guangzhou in China, moved to Shanghai Port (CN), from Shanghai Port moved to Altamira Port (MX), and from Altamira Port moved to the El Bajio Factory (MX). The calculations of the Tariff Cost based on the Flow Quantity are the same as explained above, and we see that the tariffs are applied on the second segment where the product arrives in the region / country of its final destination.

Tariffs Model – Your Turn!

Wondering where to go from here? If you are wanting to start using tariffs in your own models, but are not exactly sure where to start, please see the “Cosmic Frog Utilities to Create the Tariffs Table” section further below, which also includes step-by-step instructions based on what data you have available.

‍

In the next section, we will first discuss how quick sensitivity analyses around tariffs can be run using a Cosmic Frog for Excel App.

Cosmic Frog for Excel Tariffs Rapid Optimizer App

To enable Cosmic Frog users, and also managers and executives with no or limited knowledge of Cosmic Frog, to run quick sensitivity scenarios around changing tariffs, Optilogic has developed an Excel Application for this specific purpose. Users can connect to their Cosmic Frog model that contains a populated Tariffs input table and indicate which tariffs to increase/decrease by how much, run network optimization with these changed tariffs, and review the optimization tariff summary output table, all in 1 Excel workbook. Users can download this application and related files from the Resource Library.

‍

The following represents a typical workflow when using the Tariffs Rapid Optimizer application:

1. When opening the application, first go to the Start worksheet where users can:
   
   1. Enter their App Key in cell C2. To learn more about generating App Keys, please see this Help Center article “Generating App and API Keys”.
   2. Read what the App allows users to do and which steps to take (which are to some extent repeated in the next bullet points here).
2. Next go to the “Run options” worksheet, the content of which is shown in the screenshot.
3. In cell B3 enter the name of the model that you want to connect to. This should be a model that contains a populated Tariffs input table. Here, we are running the App against the example Tariffs model that was described in the previous section of this documentation, and which users can copy to their account from the Resource Library.
4. When ready, user can click on the Generate Tariff Matrix button, which will connect to the specified model and generate an origin-destination matrix from the Tariffs table.
5. Once the Generate Tariff Matrix workflow is done, user can review the matrix and make any changes to it on the Tariff Adjustment Matrix worksheet. For example, to reduce a tariff by 20%, enter 0.8, or to increase it by 50%, enter 1.5. In the following example, user wants to run a sensitivity scenario where the tariffs from the MX region to the US region are doubled, while all others stay as they are in the current Tariffs table in the model:

1. After making the changes to the origin-destination tariff indices in the matrix, user can click on the Optimize button to start running network optimization on the selected model with the Tariffs changed as per the Tariff Adjustment Matrix.
2. Once the optimization finishes, the Optimization Tariff Summary output table will be loaded into the Optimization Tariff Summary worksheet for user to review the impact of the changed tariffs.

Cosmic Frog Utilities to Create the Tariffs Table

For users to take advantage of the power of the Lumina Tariff Optimizer they will want to create their own network optimization model which includes a populated Tariffs input table (see also the “Tariffs Model – Tariffs Table” section earlier in this documentation). Depending on the data available to the user, populating the Tariffs input table can be a straightforward task or a difficult one in case no or little data around tariffs is known/available within the organization. Optilogic has developed 3 utilities to help users with this task. The utilities are available from within Cosmic Frog, which will be covered in this section of the documentation, and they are also available through the Cosmic Frog for Excel Tariffs Builder App, which will be covered in the next section. Here follows a short description of each utility, they will each be covered in more detail later in this section:

1. Generate Tariff Paths – uses the Customers, Facilities, Suppliers, and Products tables to find all possible path origin – path destination – product combinations and populates the Tariffs table.
2. HS Code Classification – uses product information provided by the user to look up the product’s Harmonized Systems code; these codes are developed and maintained by the World Customs Organization.
3. Lookup Duty Rates – uses the product’s HS Code, the path origin, and path destination to look up the current duty rate.

In Cosmic Frog, they are accessible from the Utilities module (click on the 3 horizontal bars icon at the top left in Cosmic Frog to open the Module menu drop-down and select Utilities):

The utilities are listed under System Utilities > Tariff.

‍

The latter 2 utilities hook into Avalara APIs, and users need to use / obtain their own Avalara API keys for each to be able to use these utilities from within Cosmic Frog or the Tariffs Builder Excel App.

‍

The following list shows the recommended steps for users with varying levels of Tariffs data available to them from least to most data available (assuming an otherwise complete Cosmic Frog model has been built):

• Data Use Case: User has no Tariffs data:
  
  • Step 1: Run the 1. Generate Tariff Paths utility
  • Step 2: Run the 2. HS Code Classification utility
  • Step 3: Run the 3. Lookup Duty Rates utility, modify outputs
• Data Use Case: User just has path origin and path destination pairs:
  
  • Step 1: Load the path origin – path destination – product combinations into the Tariffs table
  • Step 2: Run the 2. HS Code Classification utility
  • Step 3: Run the 3. Lookup Duty Rates utility, modify outputs
• Data Use Case: User has path origin and path destination pairs, and HS codes
  
  • Step 1: Load the path origin – path destination – product combinations into the Tariffs table and populate the HS Code field too
  • Step 2: Run the 3. Lookup Duty Rates utility, modify outputs
• Data Use Case: User has path origin and path destination pairs, and duty rates
  
  • Step 1: Load the path origin – path destination – product combinations into the Tariffs table and populate the duty rate field too (if available can import HS Codes too, but not required, model uses the duty rate)

Utility 1 Generate Tariff Paths

To populate the Tariffs table with all possible path origin – path destination – product combinations, based on the contents of the Transportation Policies input table, use this first utility:

1. We have clicked on the 1 Generate Tariff Paths utility in the list of Utilities to open it. A short description appears at the top.
2. User can choose the Resource Size to use for the utility, which sets how much RAM and memory will be available when executing of the utility.
3. A timeout can be set, its unit of measure is minutes. By default, the utility times out (stops execution) after 60 minutes.
4. Append to or Replace Tariffs table: choose if the tariffs that are being generated should replace any content that is already present in the Tariffs table or if the newly generated records should be appended.
5. Data for Path Origin/Destination: as explained in the Tariffs Model section further above, origin – destination pairs of paths can be specified at different levels. Options are: Name, Region or Country. For Region or Country, users need to ensure the Customers, Facilities, and Suppliers tables have this field populated (Name should already be as it is a required field on all these tables). Note that with this utility user cannot choose to specify the origins at a different level than the destinations. When manually populating the Tariffs table, this is possible.
6. Update Table or Export CSV: choose if the generated path origin – path destination – product combinations should be used to update the Tariffs table in the model or if they should be exported to a .csv file.
7. To get back to default settings, click on Reset.
8. When Parameters have been configured as desired and user is ready to run the Utility to generate the path origin – path destination – product combinations for the Tariffs table, click on Run Utility.

Consider a small model with 1 customer in the US, 2 facilities (1 DC and 1 factory) both in the US, 1 supplier in China, and 2 products (1 finished good and 1 component):

After running the 1 Generate Tariff Paths utility (using Region as the data to use for the path origin and path destination), the Tariffs table is generated and populated as shown in the next 2 screenshots:

All combinations for path origin region, path destination region, and product have been added to the Tariffs table. Scrolling further right, we see the remaining fields of this table:

1. The HS Code field is blank.
2. The Duty Rate field is blank too.

Utility 2 HS Code Classification

To update the HS Code field in the Tariffs table, we can use the second utility:

1. We have clicked on the “2 HS Code Classification” utility in the list of Utilities to open it. A short description appears at the top.
2. User can choose the Resource Size to use for the utility, which sets how much RAM and memory will be available when executing of the utility.
3. A timeout can be set, its unit of measure is minutes. By default, the utility times out (stops execution) after 60 minutes.
4. HS Code Classification Provider: currently the only option here is Avalara.
5. API Key: user needs to enter their Avalara API Key for looking up HS Codes. If user does not have an Avalara API key for HS Code lookups, they need to obtain one from Avalara directly.
6. Product Master Data: specify the name of the file that contains product information for the API to use in order to find the most fitting HS Code. The complete path to this file needs to be entered here (the next screenshot shows how this path can be obtained). In our case the Product Master.csv file has been placed in user's My Files/My Utilities folder, the complete path to this file then is: /projects/My Files/My Utilities/Product Master.csv. Furthermore, the file with the product data needs to use a specific format, which is shown in the second screenshot further below.
7. To get back to default settings, click on Reset.
8. When Parameters have been configured as desired and user is ready to run the Utility to look up HS Codes, click on Run Utility.

Users can find the full path of a file uploaded to their Optilogic account as follows:

1. If not yet expanded, expand the Explorer by clicking on the greater than icon. It then changes to a less than icon, which will collapse the Explorer when clicked on.
2. Search for the file you want to know the path of.
3. Right click on the file in question and select Copy > File Path. The file's path has now been copied to the clipboard.

The file containing the product master data needs to have the same columns as shown in the next screenshot:

1. Model Product Name: these need to match the names of the products in the Products table of the Cosmic Frog model.
2. Make sure to use following columns and provide as much detail in them as possible: Product Title, Product Description, Product Category, Product URL, and Price.

Note that columns B-F contain information of products that do not match the product name in Cosmic Frog as this is just an example to show how the utility works.

‍

After running the 2 HS Code Classification utility, we see that the HS Code field in the Tariffs table is now populated:

Utility 3 Lookup Duty Rates

To use the HS Code field to next look up duty rates we can use the third utility:

1. We have clicked on the “3 Lookup Duty Rates” utility in the list of Utilities to open it. A short description appears at the top.
2. User can choose the Resource Size to use for the utility, which sets how much RAM and memory will be available when executing of the utility.
3. A timeout can be set, its unit of measure is minutes. By default, the utility times out (stops execution) after 60 minutes.
4. Duty Rate Lookup Provider: currently the only option here is Avalara.
5. API Key: user needs to enter their Avalara API Key for looking up duty rates (based on the path origin, path destination, and HS Code). If user does not have an Avalara API key for Duty Rate lookups, they need to obtain one from Avalara directly (note that this is a different API and API key than the one used for the second utility).
6. To get back to default settings, click on Reset.
7. When Parameters have been configured as desired and user is ready to run the Utility to lookup duty rates, click on Run Utility.

After running the 3 Lookup Duty Rates utility, we see that the Duty Rate field in the Tariffs table is now populated:

The raw output from the API is placed in the Duty Rate field and user needs to update this so that the field contains just a number representing the total duty rate. For the second record (US region to China region for product RM), the total duty rate is 35% (25% + 10%), and user needs to enter 35 in this field. For the third record (China region to US region for product Rockets), the duty rate is 27.5% (7.5% + 20%), and user needs to enter 27.5 in this field. For the fourth record (China region to US region for product RM), the total duty rate is 25%, and user needs to enter 25 in this field.

Check Utility Progress

When running a utility in Cosmic Frog, user can track the progress in the Model Activity window:

1. Click on the dark blue text bubble icon on the right-hand side in the toolbar at the top of Cosmic Frog to open the Model Activity window.
2. Model activity logs for the most recent activities are shown:
   
   1. The 1 Generate Tariff Paths utility was run about an hour ago and has finished successfully, indicated by the green check mark icon.
   2. The 2 HS Code Classification utility was run 18 minutes ago and also completed successfully.
   3. The 3 Lookup Duty Rates utility is currently running as indicated by the blue play icon; it was started less than a minute ago. The status will be refreshed frequently.

Cosmic Frog for Excel Tariffs Builder App

The 3 utilities covered in the previous section to generate and populate the Tariffs input table are also made available in the Cosmic Frog for Excel Tariffs Builder App, which we will cover in this section. Users can download this application and related files from the Resource Library.

The following represents a typical workflow when using this Tariffs Builder application:

1. When opening the application, first go to the Start worksheet where users can:
   
   1. Enter their App Key in cell C2. To learn more about generating App Keys, please see this Help Center article “Generating App and API Keys”.
   2. Read what the App allows users to do and which steps to take (which are to some extent repeated in the next bullet points here).
2. Next go to the “Run options” worksheet, the content of which is shown in the screenshot.
3. In cell C3 enter the name of the model that you want to connect to and build a Tariffs input table for. Here, we are running the App against a small model named Tariffs Builder App Demo.
4. Specify what data from the Customer, Facilities, and Suppliers tables should be used as the Path Origin Property and Path Destination Property. Options are Name, Region, or Country. Click on the Build Tariff button when ready to build the initial Tariffs table from the possible path origin, path destination, and product combinations.
5. Once built, user can click on the Tariffs worksheet to review the Tariffs table that has been generated, which will have blank fields for HS Code and Duty Rate at this point.
6. If wanting to use the Avalara API to lookup HS Codes based on product information, user needs to:
   
   1. Provide their API key in cell C12.
   2. Provide as much information as possible about the products in the Product Master worksheet. This follows the same format as discussed in the section above on the 2 HS Code Classification utility. A screenshot of this worksheet is shown below.
   3. Once ready, user can click on the HS Code Classification button to start the lookup process. When done, user can check the Tariffs worksheet and should see that the HS Code field now contains values too.
7. If wanting to use the Avalara API to lookup Duty Rates based on HS Codes and path origin – path destination information, user needs to provide their API key in cell C18. When ready to start the lookup, user can click on the Duty Rate Lookup button. Once the lookup is finished, user can have a look at the Tariffs worksheet to see that the Duty Rate field now has values too.
8. Finally, user can click on the Upload Tariffs to Model button to upload the generated Tariffs into the Tariffs table of the model specified in cell C3. Note that this will replace any existing data in the Tariffs table in the model.

The next screenshot shows the Tariffs table after just running the Build Tariff workflow (bullet 4 in the list above):

1. We are on the Tariffs worksheet.
2. The worksheet has been populated with all path origin – path destination – product combinations, while noting that a utility was used to generate these records. Scrolling right will show empty HS Code and Duty Rate columns.

The next screenshot shows the Product Master worksheet which contains the product information to be used by the HS Code Classification workflow, it needs to be in this format and users should enter as much product information in here as possible:

After also running the HS Code Classification and the Duty Rate Lookup workflows (bullets 6 and 7 in the list further above), we see that these fields are now also populated on the Tariffs worksheet:

1. The HS Code field was populated by running the HS Code Classification workflow.
2. The Duty Rate field was populated by running the Duty Rate Lookup workflow. Note that user needs to modify these numbers to just be the value representing the total duty rate, which is 27.5% (enter 27.5 in the field) for the third record and 145% (enter 145 in the field) for the fourth record.

We hope users feel empowered to take on the challenging task of incorporating tariffs into their optimization workflows. For any questions, please do not hesitate to contact Optilogic support on support@optilogic.com.

Appendix – Creating the Optimization Tariff Summary Dashboard

In this appendix we will show users how to create a stacked bar chart for each path origin – path destination pair, showing the tariff costs by product.

‍

In the Analytics drop-down menu in the toolbar while in the Analytics module of Cosmic Frog, select New Dashboard, give it a name (e.g. Optimization Tariff Summary), then click on the blue Visualization button on the top right to create a new chart for the dashboard. In the New Visualization configuration form that comes up, type “tariff” in the Tables Search box, then check the box for the Optimization Tariff Summary table in the list, and click on Select Data.

1. We are using the Optimization Tariff Summary output table to create this chart.
2. We have chosen the chart type to be stacked column.
3. We need to add a custom field to this table that shows the origin-destination pair of the path, the screenshot below shows how this custom field is configured.
4. Once the custom ODPath field has been created, drag it on top of the Add Label box.
5. Drag the Tariff Cost field on top of the Add Values box. Once it is there, user can click on it to configure it further in the Field Settings window (not shown, for example changing the name to be more readable.
6. Drag the Product Name field on top of the Add Category box.
7. Click on the check icon at the top right of the dashboard to finalize the chart (not shown in screenshot above).
8. Click on the Add Filter button just above the chart on the left and select Add Dashboard Filter. In the Select a Field drop-down, select the Scenario Name field. Click on Create Filter (not shown in screenshot above).
9. Click on the check icon at the top right of the dashboard to finalize the dashboard (not shown in screenshot above).

To create the OD Path calculated field, click on the plus icon at the top right of the Fields list and select Calculated Field which brings up the Edit Calculated Field configuration window:

1. Type the name of the field in the “Field Name” box. Here we have called it ODPath. Click on Done.
2. In the box that says “Enter Formula”, type concatenate([pathoriginvalue],” to “,[pathdestinationvalue]). Which will combine the path origin value and path destination value fields into a string, with the word “to” in between them. Click on Create Field. Now the field is available in the Fields list and can be dragged on top of the Add Label box as done in bullet 4 of the previous screenshot.