Modeling Brazilian Taxes using User Defined Variables & Costs

Tax systems can be complex, like for example those in Greece, Colombia, Italy, Turkey, and Brazil are considered to be among the most complex ones. It can however be important to include taxes, whether as a cost or benefit or both, in supply chain modeling as they can have a big impact on sourcing decisions and therefore overall costs. Here we will showcase an example of how Cosmic Frog’s User Defined Variables and User Defined Costs can be used to model Brazilian ICMS tax benefits and take these into account when optimizing a supply chain.

The model that is covered in this documentation is the “Brazil Tax Model Example” which was put together by Optilogic’s partner 7D Analytics. It can be downloaded from the the Resource Library. Besides the Cosmic Frog model, the Resource Library content also links to this “Cosmic Frog – BR Tax Model Video” which was also put together by 7D Analytics.

A helpful additional resource for those unfamiliar with Cosmic Frog’s user defined variables, costs, and constraints is this “How to use user defined variables” help article.

In this documentation the setup of the example model will first be briefly explained. Next, the ICMS tax in Brazil will be discussed at a high level, including a simplified example calculation. In the third section, we will cover how ICMS tax benefits can be modelled in Cosmic Frog. And finally, we will look at the impact of including these ICMS tax benefits on the flows and overall network costs.

Overview Brazil Tax Model Example

One quick note upfront is that the screenshots of Cosmic Frog tables used throughout this help article may look different when comparing to the same model in user’s account after taking it from the Resource Library. This is due to columns having been moved or hidden and grids being filtered/sorted in specific ways to show only the most relevant information in these screenshots.

In this example model, 2 products are included: Prod_National to represent products that are made within Brazil at the MK_PousoAlegre_MG factory and Prod_Imported to represent products that are imported, which is supplied from SUP_Itajai_SC within the model, representing the seaport where imported products would arrive. There are 6 customer locations which are in the biggest cities in Brazil; their names start with CLI_. There are also 3 distribution centers (DCs): DC_Barueri_SP, DC_Contagem_MG, and DC_FeiraDeSantana_BA. Note that the 2 letter postfixes in the location names are the abbreviations of the states these locations are in. Please see the next screenshot where all model locations are shown on a map of Brazil:

The model’s horizon is all of 2024 and the 6 customers each have demand for both products, ranging from 100 to 600 units. The SUP_ location (for Prod_Imported) and MK_ location (for Prod_National) replenish the DCs with the products. Between the DCs, some transfers are allowed too. The demand at the customer locations can be fulfilled by 1, 2 or all 3 DCs, depending on the customer. The next screenshot of the Transportation Policies table (filtered for Prod_National) shows which procurement, replenishment, and customer fulfillment flows are allowed:

1. We are on the Transportation Policies input table in Cosmic Frog, which is filtered for Prod_National in the Product Name field.
2. The first 13 records show which DCs can fulfill demand of Prod_National at which customer locations. Note that this is not an all to all relationship: for example, the customer in Sao Paulo, CLI_SaoPaulo_SP, is only served by DC_Barueri_SP, whereas the customers in Salvador (CLI_Salvador_BA) and Recife (CLI_Recife_PE) can be served by all 3 DCs. The Customer Fulfillment Policies input table contains the same relationships for which DCs are allowed to fulfil the demand of which customers.
3. The bottom 7 records show which DC-DC transfers are allowed for Prod_National, and that the factory (MK_PousoAlegre_MG) can replenish all 3 DCs. The Replenishment Policies input table shows these same transfer and replenishment options.
4. Since the transportation costs are dependent on the distance travelled, the distance (in KM) for each lane has been specified in the Transport Distance field.
5. We see that the customer fulfillment lanes all use an LTL (less than truckload) mode, while the DC-DC transfers and Factory-DC replenishments use an FTL (full truckload) mode. These are specified in the Transportation Modes table, as follows:

1. The LTL mode costs 20 cents per kilogram per kilometer (per the primary weight and primary distance units of measure set on the Model Settings input table).
2. The FTL mode costs 10 cents per kilogram per kilometer.

For the other product modelled, Prod_Imported, the same customer fulfillment, DC-DC transfer, and supply options are available, except:

1. Transfers from DC_Contagem_MG to DC_Barueri_SP are not allowed.
2. The LTL mode is used to supply Prod_Imported from SUP_Itajai_SC to the DC’s.

Brazil ICMS Tax Benefit Example

In Brazil, the ICMS tax (Imposto sobre Circulaçao de Mercadorias e Serviços, or Tax on Commerce and Services) is levied by the states. It applies to movement of goods, transportation services between several states or municipalities, and telecommunication services. The rate varies and depends on the state and product.

When a company sells a product, the sales price includes ICMS, which results in an ICMS debit for the company (the company owes this to the state). Likewise, when purchasing or transferring product, the ICMS is included in what the company pays the supplier. This creates ICMS credit for the company. The difference between the ICMS debits and credits is what the company will pay as ICMS tax.

The next diagram shows an ICMS tax calculation example, where company also has a 55% tax benefit which is a discount on the ICMS it needs to pay.

1. Company has purchased product from a supplier and pays the supplier R$ 1,220, which is the net price of R$ 1,000 (1a) plus the ICMS of R$ 220 (1b). This is an ICMS credit as the company has already paid this.
2. Company sells product to a customer and receives R$ 2,930 from the customer. This is the net price of R$ 2,400 (2a) plus the ICMS of R$ 530 (2b). This R$ 530 is an ICMS debit as company received this and owes it to the state government.
3. The ICMS balance is the difference between debits and credits, and in this example equals R$ 530 – R$ 220 = R$ 310.
4. As mentioned above, this company has an ICMS Tax Benefit discount rate of 55%, so the tax benefit equals R$ 310 * 0.55 = R$ 170.50.
5. The ICMS tax owed to the government is equal to the ICMS balance minus the benefit = R$ 310 – R$ 170.50 = R$ 139.50.

Modelling ICMS using User Defined Variables & Costs

In order to include ICMS tax benefits in a model, we need to be able to calculate ICMS debits and credits based on the amount of flow between locations in different states for both national and imported products. As different states and different products can have different ICMS rates, we need to define these individual flow lanes as variables and apply the appropriate rate to each. This can be done by utilizing the User Defined Variables and User Defined Costs input tables, which can be found in the “Constraints” section of the Cosmic Frog input tables, shown in the below screenshot (here user entered a search term of “userdef” to filter out these 2 tables):

In the User Defined Variables table, we will define 3 variables related to DC_Contagem_MG: one that represents the ICMS Debits, one that represents the ICMS Credits, and one that represents the ICMS Balance (= ICMS Debits – ICMS Credits) for this DC. The ICMS Debits and ICMS Credits variables have multiple terms that each represents a flow out of or a flow into the Contagem DC, respectively. Let us first look at the ICMS Debits variable:

1. Records with the same Variable Name, like the 5 shown here called DC_Contagem_MG|ICMS_Debit, together make up a variable, where each record represents a term of the variable. Note that there are a total of 13 records in the User Defined Variables table with Variable Name = DC_Contagem|ICMS_Debit, the screenshot just shows the first 5 of them.
2. The Term Name needs to be unique and good practice is to use a descriptive name. As the terms here represent flows, the naming convention used is that of Source Name|Destination Name|Product Name. These tell us that these are flows out of the Contagem DC.
3. The type of term is Flow, since we want to apply ICMS values and discount rates based on products leaving the Contagem DC (replenishment / customer fulfillment) where the sales price that the Contagem DC charges includes ICMS, which is a debit for the DC. The unit of measure for the flow is quantity (eaches).
4. The flow quantity of the term will be multiplied by the Coefficient value. The Coefficient value is, in this case, essentially the ICMS rate for this source – destination – product combination.
5. As we want to run a scenario to examine the impact of the ICMS tax benefit, the Status of all these records is set to Exclude; this will be changed to Include through a scenario item.

Still looking at the same top records that define the DC_Contagem_MG|ICMS_Debit variable, but freezing the Variable Name and Term Name columns and scrolling right, we can see more of the columns in the User Defined Variables table:

1. We are looking at the Origin Name, Destination Name, and Product Name columns, which are used to specify which flow this term represents.
2. As mentioned above, the Term Names contain these origin – destination – product combinations too and these match the Origin Name, Destination Name, and Product Name columns. The Origin Name of all these is DC_Contagem_MG as these are flows from the Contagem DC to the destinations it is allowed to serve/replenish.

Note that there are quite a few custom columns in this table (not shown in the screenshots; can be added through Grid > Table > Create Custom Column), which were used to calculate the ICMS rates outside of the model. These are helpful to keep in the model, should changes need to be made to the calculations.

Next, we will have a look at the ICMS Credit variable, which is made up of 3 terms, where each term represents a possible supply/replenishment flow into the Contagem DC:

The last step on the User Defined Variables table is to combine the ICMS Credit and ICMS Debit variables to calculate the ICMS balance:

1. The name of this variable, DC_Contagem|ICMS_Balance indicates which DC the variable is calculated for and also includes “ICMS_Balance” to indicate what the variable calculates. This is similar to the naming convention of the Debit and Credit variables.
2. Here the Type is set to Variable, as we want to combine the 2 variables that were defined above, ICMS_Debit and ICMS_Credit, into 1 balance variable.
3. The Term Name for both records is set to the Variable Names of the Credit and Debit variables defined previously in the User Defined Variables table (descriptions and screenshots above).
4. In order to calculate the balance of how much ICMS is owed, we need to subtract the ICMS credits from the ICMS debits. We can do so by setting the Coefficient of the ICMS_Debit variable to 1 and that Coefficient of the ICMS_Credit variable to -1, which results in: DC_Contagem_MG|ICMS_Balance = 1 * DC_Contagem_MG|ICMS_Debit – 1 * DC_Contagem_MG|ICMS_Credit.

To finalize the setup, we need to add 1 record to the User Defined Costs table, where we will specify that the company has a 55% discount (tax incentive) for the ICMS it pays relating to the Contagem DC:

1. The Cost Name is specified as DC_Contagem_MG_TaxIncentive, which is descriptive of what the cost represents.
2. The Variable Name needs to match a Variable Name specified in the User Defined Variables table, this way the cost that is set up here will be applied to the ICMS Balance calculated at the Contagem DC (the difference between the outflow quantities multiplied with their coefficients and the inflow quantities multiplied with their coefficients).
3. As this is not a cost but a benefit of 55%, a value of -0.55 is entered in the Unit Cost field.
4. Like the records on the User Defined Variables table, the Status of this one on the User Defined Costs table is also set to Exclude, to be changed to Include for the scenario that will take this tax incentive into account.

Scenarios & Outputs

As mentioned in the previous section, all records in the User Defined Variables and User Defined Costs tables have their Status set to Exclude. This way, when the Baseline scenario is run, the ICMS tax incentive is not included, and the network will be optimized just based on the costs included in the model (in this case only transportation costs). We want to include the ICMS tax incentive in a scenario and then compare the outputs with the Baseline scenario. This “IncludeDCMGTaxBenefit” scenario is set up as follows:

1. We are in the Scenarios module of Cosmic Frog and are looking at the IncludeDCMGTaxBenefit scenario, which has 2 scenario items, ActivateUDV_MGTaxBenefit and ActivateUDC_MGTaxBenefit.
2. The ActivateUDV_MGTaxBenefit scenario item is selected. It is configured as follows to change the Status field in the User Defined Variables table to Include for variables relating to the Contagem DC:
   1. The User Defined Variables table is selected as the Table Name to which the change the scenario item specifies is applied.
   2. The Actions section sets the value of the Status field to Include
   3. In the Conditions section, the Condition Builder is used to indicate that the change this scenario item makes should only be applied to records where the Variable Name starts with “DC_Contagem|”. In this example model this means all records in the User Defined Variables table, but one could imagine setting these tax incentive calculations up for multiple locations in the model and including them one by one in scenarios.

Next, we have a look at the second scenario item that is part of this scenario:

1. The ActivateUDC_MBTaxBenefit scenario item is selected. It is configured to change the Status field of the User Defined Costs table to Include for costs related to the Contagem DC:
   1. The User Defined Costs table is selected as the Table Name to which the change the scenario item specifies is applied.
   2. The Actions section sets the value of the Status field to Include
   3. In the s section, the Condition Builder is used to indicate that the change this scenario item makes should only be applied to records where the Cost Name is equal to “DC_Contagem _MG_TaxIncentive”. Again, in this example model this means all records in the User Defined Costs table.

With the scenario set up, we run a network optimization (using the Neo engine) on both scenarios and then first look in the Optimization Network Summary output table:

Notice that the Baseline scenario as expected only contains transportation costs, while the IncludeDCMGTaxBenefits scenario also contains user defined costs, which represent the calculated ICMS tax benefit and have a negative value. So, overall, the IncludeDCMGTaxBenefit scenario has about R$ 331k lower total cost as compared to the Baseline scenario, even though the transportation costs are close to R$ 47k higher. Since the transportation costs are different between the 2 scenarios, we expect some of the flows have changed.

There are 3 network optimization output tables that contain the outputs related to User Defined Variables and Costs:

We will first discuss the Optimization User Defined Variable Term Summary output table:

1. This is the Optimization User Defined Variable Term Summary output table.
2. The first 2 records are for 2 terms of the ICMS_Credit variable at the Contagem DC: inflow from DC_Barueri_SP of the Imported product (first record) and inflow from the factory in Pouso Alegre of the National product (second record). The Term Value here is the amount of product that was flowing between the origin and destination and the Scaled Term Value is this flow quantity multiplied with the Coefficient that was set on the User Defined Variables input table.
3. Similarly, the next 7 records are for individual terms of the ICMS_Debit variable at the Contagem DC. These are all outflows of the Contagem DC to serve customers and replenish the Feira de Santana DC. Again, the Term Value is the amount of product that was flowing between the origin and destination and the Scaled Term Value is this flow quantity multiplied with the Coefficient that was set on the User Defined Variables input table.

The Optimization User Defined Variable Summary output table contains the outputs at the variable level (e.g. the individual terms of the variables have been aggregated):

1. This is the Optimization User Defined Variable Summary output table.
2. The second record shows the Variable Value of the ICMS Credit variable at the Contagem DC, which is the sum of the scaled term values of all the DC_Contagem|ICMS_Credit terms.
3. The third record shows the Variable Value of the ICMS Debit variable at the Contagem DC, which is the sum of the scaled term values of all the DC_Contagem|ICMS_Dedit terms.
4. The first record shows the Variable Value of the ICMS Balance variable at the Contagem DC, which is the value of the ICMS Debit variable minus the value of the ICMS Credit variable.

Finally, the Optimization User Defined Cost Summary output table shows the cost based on the 55% benefit that was set:

The DC_Contagem_MG_TaxIncentive benefit is calculated from the DC_Contagem_MG|ICMS_Balance variable, where the Variable Value of R$ 686,980 is multiplied by -0.55 to arrive at the Cost value of R$ -377,839.

Now that we understand at a high level the cost impact of the ICMS tax incentive and the details of how this was calculated, let us look at more granular outputs, starting with looking at the flows between locations. Navigate to the Maps module within Cosmic Frog and open the maps named Baseline and Include DC MG Tax Benefit, which show outputs from the Baseline and IncludeDCMGTaxBenefit scenarios, respectively. The next 2 screenshots show the flows from DCs to customer locations: Baseline flows on the left and scenario flows “Include DC MG Tax Benefit” on the right:

 

We see that in the Baseline the customer in Rio de Janeiro is served by the DC in Sao Paulo. This changes in the scenario where the tax benefit is included: now the Rio de Janeiro customer is served by the Contagem DC (located close to Belo Horizonte). The other customer fulfillment flows are the same between the 2 scenarios.

This model also has 2 custom dashboards set up in the Analytics module; the 1. Scenarios Overview dashboard contains 2 graphs:

This Summary graph shows the cost buckets for each scenario as a bar chart. As discussed when looking at the Optimization Network Summary output table, the IncludeDCMGTaxBenefit scenario has an overall lower cost due to the tax benefit, which offsets the increased transportation costs as compared to the Baseline scenario.

This Site Summary bar chart shows the total outbound quantity for each DC / Factory / Supplier by scenario. We see that the outbound flow for the DC in Barueri is reduced by 500 units in the IncludeDCMGTaxBenefit scenario as compared to the Baseline scenario, whereas the Contagem DC has an increased outbound flow, from 1,000 to 2,500 units. We can examine these shifts in further detail in the second custom dashboard named 2. Outbound Flows by Site, as shown in the next 2 screenshots:

This first screenshot of the dashboard shows the amount of flow from the 3 DCs and the factory to the 6 customer locations. As we already noticed on the map, the only shift here is that the Rio De Janeiro customer is served by the Barueri DC in the Baseline scenario and this changes to it being served by the Contagem DC in the IncludeDCMGTaxBenefit scenario.

Scrolling further right in this table, we see the replenishment flows from the 3 DCs and the Factory to the 3 DCs. There are some more changes here where we see that the flow from the factory to the Barueri DC is reduced by 500 units in the scenario, whereas the flow from the factory to the Contagem DC is increased by 500 units. In the Baseline, the Barueri DC transferred a total of 1,000 units to the other 2 DCs (500 each to the Contagem and Feira de Santana DCs), and the other 2 DCs did not make DC transfers. In the Tax Benefit scenario, the Barueri DC only transfers to the Contagem DC, but now for 1,500 units. We also see that the Contagem DC now transfers 500 units to the Feira de Santana DC, whereas it did not make any transfers in the Baseline scenario.

We hope this gives you a good idea of how taxes and tax incentives can be considered in Cosmic Frog models. Give it a go and let us know of any feedback and/or questions!