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 run, currently 3 custom tables are used. These will become standard input and output tables in Cosmic Frog in the very near future:

1. We are in Cosmic Frog’s Data module, accessible from the Module menu (the icon with 3 horizontal bars at the top left in Cosmic Frog) drop-down.
2. When in the Data module, click on the third icon at the top right to go to Custom Tables.
3. The tariffs custom table is used by the network optimization engine as an input table. In this table the tariff numbers themselves are to be entered. We will go into more detail on this table in the next section.
4. Two custom output tables are generated when running a network optimization with tariffs specified in the Tariffs custom input table:
   1. Optimization Path Flow Summary – the individual segments of all the paths are listed in this table, which also shows to which segment of a path a tariff is applied.
   2. Optimization Tariff Summary – in this table the tariff costs are rolled up to the scenario – path origin – path destination – product level.

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 custom input table. As mentioned above, this table will become a standard Cosmic Frog input table in the near future:

1. We are on the Tariffs custom input table.
2. Path Origin Value and Path Origin Property: the Path Origin Property indicates at which level the origins (most upstream locations of a path) are specified. Options are:
   1. Name – for individual locations
   2. Region – for region of the location
   3. Country – for country of the location
   4. The Origin field then needs to be set to the path start value in accordance with the property of the most upstream location of the path.
3. Path Destination Value and Path Destination Property: the Path Destination Property indicates at which level the destinations (most downstream locations of a path) are specified. Options are:
   1. Name – for individual locations
   2. Region – for region of the location
   3. Country – for country of the location
   4. The Path Destination Value field then needs to be set to the path end value in accordance with the property of the most downstream location of the path.
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. 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.
7. These 3 records show that 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.
8. This record shows that for the finished good Consumables, a 40% tariff is applied on paths starting in the Mexico region and ending in the US region.
9. 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.
   4. Status: this tariff record will be included when running a network optimization if Status = Include and it will be ignored when Status = Exclude.
   5. Notes: user can put notes relating to the tariff record here. If a utility is used to create the Tariffs table, then the Notes field will contain text stating this, including a date/time stamp.

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. Baseline:
   1. Uses tariffs of around 15% from before any major changes took place. These rates are illustrative in nature.
   2. Flow constraint for 3.5M units maximum from El Bajio factory in Mexico applied.
2. Baseline New Tariffs:
   1. Uses tariffs as set up in the Tariffs table, which are similar to right after major changes took place but have changed since. These rates are illustrative in nature.
   2. Flow constraint for 3.5M units maximum from El Bajio factory in Mexico applied.
3. Optimized New Tariffs:
   1. Uses tariffs as set up in the Tariffs table, which are similar to right after major changes took place but have changed since. These rates are illustrative in nature.
   2. Flow constraint for 3.5M units maximum from El Bajio factory in Mexico removed.

Please note that since scenario items cannot be set up for custom tables, the Tariffs table was set up with the initial tariffs to run the Baseline scenario. Then the numbers in the Tariffs table were updated and the other 2 scenarios were run. The Tariffs table will soon become a standard Cosmic Frog input table. From that point onwards, scenario items can be applied to it as for any other Cosmic Frog input table.

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 standard Optimization Scenario Comparison dashboard in the Analytics module of Cosmic Frog has been updated to add Tariff Cost to the Financials stacked bar chart so all costs for all 3 scenarios can be compared 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 both the highest transportation costs and highest tariff costs as compared to the other 2 scenarios. This is driven by the tariffs, which are steep. The higher 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 Baseline scenario, but lower than the Baseline New Tariffs scenario. Its Tariff costs are much lower than in the Baseline New Tariffs scenario, so it is likely utilizing the El Bajio Factory a lot more (since the flow constraint is removed) which increases transportation cost as compared to the Baseline scenario but also avoids the steepest tariffs.

Note that in the Appendix it is explained how the Tariff Cost field can be added to this chart.

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 $73.5M 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:
   • 
6. 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.
7. 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 Transportation Policies table to find all possible path origin – path destination – product combinations and populates the Tariffs table with these.
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 table shows the recommended steps for users with varying levels of Tariffs data available to them (assuming an otherwise complete Cosmic Frog model has been built):

Data Use Case	Step 1	Step 2	Step 3
User has no Tariffs data	Run the 1. Generate Tariff Paths utility	Run the 2. HS Code Classification utility	Run the 3. Lookup Duty Rates utility, modify outputs
User just has path origin and path destination pairs	Load the path origin – path destination – product combinations into the Tariffs table*	Run the 2. HS Code Classification utility	Run the 3. Lookup Duty Rates utility, modify outputs
User has path origin and path destination pairs, and HS codes	Load the path origin – path destination – product combinations into the Tariffs table and populate the HS Code field too*	Run the 3. Lookup Duty Rates utility, modify outputs
User has path origin and path destination pairs, and duty rates	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)

*Note that the Tariffs table is currently a custom table which user can create themselves, however this will be somewhat laborious due to having to specify each column and its data type. Therefore, it is recommended to use 1 of the following 2 methods to get a Tariffs table in the model:

1. Even though path origin – path destination pairs are known, use the “1 Generate Tariff Paths” utility to generate the table from the Transportation Policies table. This will build the Tariffs table and add it to the Custom Tables section in the Cosmic Frog model. User can then export the Tariffs table, update it with their own data and re-import (see also this Help Center article “Importing and Exporting Data in Cosmic Frog”).
2. Use the Tariffs model described further above in the “Tariffs Model” section from the Resource Library as a starting point:
   1. Copy it to user’s own account from the Resource Library and open it in Cosmic Frog.
   2. Overwrite all tables, including the Tariffs table, with users own data (e.g. use Import (REPLACE)). No need to create the Tariffs table from scratch as it already exists in this model.
   3. Remember to clear out any tables that are populated in the Tariffs example model but are not used in the final model.

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. Before running the utility, user can configure following parameters:
   1. 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.
   2. 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.
   3. 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.
3. To get back to default settings, click on Reset.
4. 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 the following small Transportation Policies table as an example to run this first utility on:

1. We are on the Transportation Policies input table.
2. The first record is from Guangzhou, a supplier in China, to Princeton Factory in the US for a raw material product named RM. At the factory, RM is consumed to make the finished good Rockets.
3. The second record is from Princeton Factory to distribution center Dallas DC (also located in the US), for Rockets.
4. The third record is from Dallas DC to customer CZ1213 (also located in the US), also for Rockets.

After running the 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. Before running the utility, user can configure following parameters:
   1. HS Code Classification Provider: currently the only option here is Avalara.
   2. 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.
   3. 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. This file needs to be placed in user’s My Utilities folder on the Optilogic platform or if placed elsewhere the complete path to the file needs to be entered here. Furthermore, it needs to use a specific format, which is shown in the next screenshot.
3. To get back to default settings, click on Reset.
4. When Parameters have been configured as desired and user is ready to run the Utility to look up HS Codes, click on Run Utility.

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 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 now 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. Before running the utility, user can configure following parameters:
   1. Duty Rate Lookup Provider: currently the only option here is Avalara.
   2. 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).
3. To get back to default settings, click on Reset.
4. 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 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 third record, the duty rate is 27.5% (7.5% + 20%), and user needs to enter 27.5 in this field. For the fourth record, the total duty rate is 145% (20% + 125%), and user needs to enter 145 in this field.

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 – Analytics Updates

In this appendix we will show users: 1) how they can add Tariff Costs to the “Financials: Scenario Cost Comparison” stacked bar chart in the “Optimzation Scenario Comparison” dashboard, and 2) how to create a stacked bar chart for each path origin – path destination pair, showing the tariff costs by product.

Add Tariff Costs to the Optimization Scenario Comparison Dashboard

To go to the Analytics module of Cosmic Frog, click on the 3 horizontal bars icon at the top left of the Cosmic Frog screen to open the Module menu drop-down. Choose Analytics from the list.

1. Click on the Optimization Scenario Comparison dashboard in the dashboards list on the left in the Analytics module to open it.
2. Click on the icon with 3 dots at the right top of the dashboard, and then choose Edit.

Click on the pencil icon in the “Financials: Scenario Cost Comparison” stacked bar chart to edit its configuration.

1. The name of the dashboard is shown at the top of the configuration screen.
2. We see that this chart is generated from the optimization network summary output table.
3. Click on the plus icon to start adding the Tariff Costs from the optimization tariff summary output table to this chart.

1. In the New Visualization form that comes up, type “tariff” in the search box to quickly find tables that have this text in their table name.
2. In the list that appears at the bottom, check the box for the optimization tariff summary table.
3. Click on the Select Data button.

1. In the Data Blending configuration window that comes up, select scenario name for both output tables – this means they will be joined on this field.
2. Type “cost” in the search box to find fields in the optimization tariff summary output table that contain this text in their column name.
3. In the list that appears beneath the search box, check the box for the tariff cost field.
4. Click on the Join Data button.

1. Back in the dashboard configuration window, scroll all the way down in the Fields list on the left to find the tariff cost field from the optimization tariff summary table.
2. Drag the tariff cost field on top of the Add Values box, this will add it as another cost to be used to draw the stacked bar chart.

Create the Optimization Tariff Summary Dashboard

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.