Tiêu đề FTA Qualification Instructions.pdf ✅

1.0 Purpose To perform calculations for products that cross the border to see if they qualify (“PASS”) for free trade. 2.0 Scope The following Free Trade Agreements and related legislation will be the main focus of this project: • USMCA (United States Mexico Canada Free Trade Agreement) • ORIGIN (Country of Origin) 3.0 Overview The RUN worksheet will initiate the VBA processes (that you will be programming). The user will indicate the product numbers for which the process should run and press the associated button(s). Qualify Goods will perform the calculations for each product in a copy of the QUALIFICATION worksheet and save a pdf version of each. The calculations are in efforts to 1. Obtain a PASS for the FTA (free trade agreement) and 2. Determine the ORIGIN, both according to the legislative rules (which have been broken down and provided in the LOGIC worksheets). Solicit Certifications button will send an e-mail to the supplier of the integral parts which caused the overall product to FAIL (if obtained, this allows us to remove them from the calculations making it easier to obtain a PASS). We will keep track of these products in a “PENDING” status temporarily. The Enter Certificates button allows for simple updating of the PARTS worksheet once a certificate is e-mailed back from a supplier. The Create Certificates will put all the products that PASS on a Certificate of Origin and all the products that FAIL on an Addendum (one each per customer). Please note - the FTA Qualification Analysis spreadsheet holds all the templates and raw data so copies will need to be made of templates before completion then moved to a new spreadsheet or pdf and saved accordingly. Please see Appendix 1 - Spreadsheet Flow Chart. 1.0 Purpose 2.0 Scope 3.0 Overview 4.0 Actions and Methods 5.0 Appendices Free Trade Agreement Qualification WI-18.030 Borderflow Inc. – Programming Project 2024-02-11
Free Trade Agreement Qualification WI-18.030 ©2024 Borderflow Inc. [CONFIDENTIAL] Page 2 of 10 4.0 Actions and Methods 4.1 Qualify Product • The Qualify Product button will initiate the process to perform the calculations, save a pdf copy of them, update the ERRORS worksheet with any missing data, and update the RESULTS worksheet with the outcome of the calculations. • To begin, open the FTA Qualification Analysis spreadsheet and make a copy of the QUALIFICATION worksheet. • Note – we make a copy because this is the template and in case you accidentally save on top of it there is a back-up copy hidden named QUALIFICATION (Back-up). • Start with the first product number listed in the RUN worksheet under “Specify Parts” and look-up the Bill of Material (break-down of integral parts in a product) in the BOMS worksheet. • In the BOM worksheet, first remove any rows for parts that have an end date indicated in BOM_End_Date (column J). • Also, remove any rows directly below the part (which indicates an end date) for parts which have a higher number in BOM_LVL (column C). • Note - Stop deleting rows once you reach a LVL number of equal or lower value. • Example - In the BOM below, the part which has an end date is a level 2 so you would delete it and the highlighted rows below it containing the 3’s and 4’s.
Free Trade Agreement Qualification WI-18.030 ©2024 Borderflow Inc. [CONFIDENTIAL] Page 3 of 10 • Copy the LVL, Part Number, Description, QTY (columns C:F) within the range BOMS_Data (BOMS worksheet) for the specified product to a copy of the QUALIFICATION worksheet. • Note – the “parent” product number is indicated in BOMS_Product_Number and the “child” part/component numbers in BOMS_Part_Number. Each new product will be identified on the BOM worksheet by a zero level and highlighted first row. • Complete one product at a time. • Paste as values in BOM_Data (QUALIFICATION worksheet) • Convert to number by clicking the exclamation menu. • Delete any extra lines (ie – delete blank rows within BOM_Data after pasting the BOMS_Data) • Note - This needs to happen in order for some of the formulas in the Product Details section to calculate properly. • Complete red fields in the first section labelled Product Details from the RUN worksheet. • Note - As you complete fields other boxes will auto-populate due to the formulas.
Free Trade Agreement Qualification WI-18.030 ©2024 Borderflow Inc. [CONFIDENTIAL] Page 4 of 10 • Anything in yellow is missing data in the PARTS worksheet (specific logic already entered to generate the yellow cells for errors/missing data is in the conditional formatting menu). • Note - This information must be entered before you can proceed with the calculations. • Example – see worksheet EXAMPLE-1. • If you get any yellow cells: o Copy the Product_Number (cell D5), part numbers which are highlighted yellow withing Part_Number and their description within Part_Description (columns C:D) to the ERROR worksheet with a generated error message (these will be provided) to put in Error_Message (column D). o Copy the Product_Number (cell D5) in QUALIFICATION to the RESULTS worksheet and enter “Error” as the status in RESULTS_Statuses and the make the explanation in RESULTS_Explanations (column H) read “Missing information in Parts Database which is required to run qualifications.” o Abandon the qualifications for this product as you can’t go any further with the calculations until the user enters the missing data into the PARTS worksheet. o Delete copy of the QUALIFICATION worksheet and save spreadsheet (to save the changes made to the ERRORS and RESULTS worksheets). o Repeat process for next product number listed in RUN_Specify_Parts. • If you don’t get any yellow cells: o Continue with next steps if you did not produce any yellow boxes due to missing data. • Next Steps: lookup the logic for the code in Product_HS (cell L6 of QUALIFICATION) and determine for each cell marked as “Required” in TS_FTA if the component “tariff shifts” for FTA purposes: o Indicate “YES” when appropriate tariff shift occurs (logic in LOGIC-FTA) o Indicate “NO” when the tariff shift does not occur (logic in LOGIC-FTA) • Note - The logic will first determine which fail indicating “NO” (because less parts will fail than pass) and subsequently the remaining will be “YES”. • Likewise, lookup the logic for the HS code in Product_HS of QUALIFICATION and determine for each cell marked as “Required” in TS_Origin if the component properly tariff shifts for country of origin purposes: o Indicate “YES” when appropriate tariff shift occurs (logic in LOGIC-ORIGIN) o Indicate “NO” when the tariff shift does not occur (logic in LOGIC-ORIGIN) • If the product passes the criteria in the LOGIC_ORIGIN (columns B:N), the origin selected in range Product_Origin will be “Canada - CA”. • If it fails the logic, you need to identify the single component part within BOM_Data (QUALIFICATION worksheet) which has the highest value in Part_Value and use the country indicated for this part in Part_Origin. To do so: o Filter Part_Type (column F) by “Parts” and “Raw Material”
Free Trade Agreement Qualification WI-18.030 ©2024 Borderflow Inc. [CONFIDENTIAL] Page 5 of 10 o Identify the single part which has the highest value in Part_Value o The corresponding country indicated in Part_Origin (in the same row) for this part will be the origin that should be indicated in Product_Origin. o You will need to convert the abbreviated country code in Part_Origin (column H) to the full version of the country available in Origins. o Note – look-up the Part_Origin country code in Country_Code within the Country_Codes (COUNTRY worksheet) and return the corresponding text in Origins to Product_Origin. • If there is a tie for highest value use “Canada - CA” as the origin in Product_Origin. • If the country of origin is blank in Product_Origin_Previously (cell H15) or if Product_Origin is different than Product_Origin_Previously (cell H15) then update the PARTS table (PARTS worksheet) with the abbreviated country code version of Product_Origin. To do so: o Step 1: Lookup the Product_Origin in the Origins range of the Country_Codes table and return the corresponding abbreviated Country_Code o Lookup the part number in Product_Number (D5) in Part_Numbers (column A) of the PARTS table (PARTS worksheet) and update the PARTS_Origin (column M) with the abbreviated country code determined in the first step. • Based on the criteria in the LOGIC-FTA worksheet, determine if the product produces a PASS or FAIL in Results_Status and the appropriate Explanation in Results_Explanation. • Note – some HS codes IN LOGIC_ORIGIN_HS have one set of logic applicable, and some have multiple (multiple rows). • If there is more than one set of logic per code identified in cell Product_HS (L6 of QUALIFICATION), then: o First, ensure all the conditions are met in LOGIC_FTA_Conditions (LOGIC worksheet columns B:D) as some are contingent on the Product_Description (cell D6 of QUALIFICATION). o Then, apply them in order in efforts to try and achieve a PASS ▪ If the first set of logic fails, try the second and if that fails try the third, if the all fail then the result is FAIL but refer to section 4.2 Solicit Certificates for additional steps in efforts to potentially achieve a PASS. ▪ Adversely, if you get a PASS on the first or second set of logic there’s no need to apply the rest etc. • Copy the AALA and CAFÉ data (already calculated at the bottom) in CAFE_Percentages and CAFE_Values (M247 to N261) and AALA_Data (E269, L269, E271, L271) to the corresponding columns in the RESULTS worksheet.