Formula Management Configuration Workbook Guide

What is the Formula Management Configuration Workbook?

The Formula Management configuration workbook is a tool to assist with bulk upload of formula and recipe data. There are (currently) nine sheets (referred to here as ‘tabs’) in the workbook. The first tab, “Overview”, defines each field as it applies to you. This article was designed to provide an advanced explanation and pro tips for completing the Formula Management Configuration Workbook.

Formula Tab

The Formula Tab is where you add your formulas.

1. Formula ID – The formula's internal ID. It is recommended to create an ID structure that is consistent throughout your site. Must be unique. Example: TG-Amazing Top Shelf Artisan Pizza-F001
2. Formula Name - can be the same as ID. Does not need to be unique. Based on your company’s naming conventions. Example: TraceGains Amazing Top Shelf Artisan Pizza
3. Formula Type – Select between three options: Food, Packaging, or Supplement.
4. Formula Version – You can start at 1 or any version number you want; based on your company’s versioning conventions.
5. Description – Enter a brief description or any desired information into this text field.
6. Template Name – Enter the exact name of a pre-configured template. Your CSM can create new templates upon request.
7. Active Status – This field gives you the option to add inactive formulas to your site via the Configuration Workbook. This could include older versions of an active formula. Select Active or Inactive. Inactive formulas are not displayed in user dashboards unless a user selects 'Show Inactive' on a UDD.
8. Computation Method – Select how you would like to enter the amounts of each item that goes into your recipe. Many users use Amount to start out, and occasionally switch to Percentage once they work through different iterations of the recipe.
   1. Amount: use this method to enter in raw amounts of your ingredients to auto calculate gross and net amounts.
   2. Percentage: use this method if you know the percent of gross for the ingredients in your recipe. Amounts will auto calculate based on the percent gross (input) and the target recipe amount.
9. Primary Recipe – Select Yes or No. Determines whether a recipe is marked as the primary recipe for its associated formula. Only 1 recipe may be designated as the primary recipe for a formula.

   You can add items and/or other formula recipes to another formula. In the pre-configured example, Artisan Pizza contains two sub-recipes from another formula: pizza dough and pizza sauce. Because those two sub-recipes have already been created on the Recipe Items tab, you can add the Recipe ID and Recipe Name to any formula where that is used in the Formulas tab.

10. Number of Servings – Enter the number of servings for your recipe here. Up to 2 decimal places are supported. Can be used for scaling a batch export on a Finished Good specification.
11. Serving Size – Enter your serving size here. Up to 2 decimal places are supported. Drives nutrition report values.
12. Unit of Measure – Unit of measure can be designated with any of the following measures: mg, mcg, g, oz, ml, mcl, fl oz, kg, lb. This Unit of Measure applies to the Serving Size.
13. Unit Size – Used for scaling a formula. Enter the value for the unit size here. Up to 2 decimal places are supported.
14. Unit Size Unit of Measure – Unit of measure can be designated with any of the following measures: mg, mcg, g, oz, ml, mcl, fl oz, kg, lb. This Unit of Measure applies to the Unit Size.
15. Recipe Target Amount – Enter the Recipe Target Amount here. Up to 2 decimal places are supported. (Note: Serving Size * Number of Servings = Recipe Target Amount)
16. Recipe Target UOM – Unit of measure can be designated with any of the following measures: mg, mcg, g, oz, ml, mcl, fl oz, kg, lb. This Unit of Measure applies to the Recipe Target
17. Total Yield – Enter the Total Yield (%) value here.
18. Note – Enter any needed recipe notes here. This is optional.
19. Dosage Form – Applies to Supplement Formulas only. Enter the dosage form in this text field (ie. Capsule, chewable gel, powder, etc.)
20. Dosage Amount – Applies to Supplement Formulas only. Enter the dosage amount in this field.
21. Recipe Potency – Applies to Supplement Formulas only. Enter recipe potency as a number (no % sign or text)

Recipe Items Tab

This section of the workbook is used to add recipe items and associated data that will make up your recipes and ultimately, recipe reports.

Recipe Items can be one of two component types. First, a Recipe (treated as a sub-recipe), which is a group of items that you can manage independently within a separate formula and add to recipes. The second component type is an Item. Each item added to your system has a corresponding data sheet, where you can store nutrition, allergen, and cost information that will be added to any associated recipe’s reports.

1. Formula ID and Formula Name – reference the formula that each recipe belongs to.  Create one row for each item or recipe (pre-blend) that goes into one formula.
2. Formula Version – Use this field to associate the recipe to the correct version of the formula. The system will check whether your version number matches what you have on the Formula Tab.
3. Recipe Items – can be one of two component types:
   1. Recipe (sub-recipe) – groups of items that you can manage independently within a separate formula and add to recipes.
   2. Item – each item in your system has a corresponding data sheet, where you can store nutrition, allergen, and cost information that rolls up into associated recipe reports.

      If no match is found, it will result in an error.

4. Recipe ID - Add a recipe name and ID based on your company’s naming conventions. Used to associate a recipe item (component) to a specific recipe in the formula. Autofills from Formula ID choice.
5. Component Item ID – Comes from the Item Data Sheet Overview Tab.  List the Item ID for each of the items or recipes that are in this recipe. Entering data here will auto-populate the ‘Component Item Name’ column.

   For example, if you entered a Component Item ID and Column G doesn’t auto-populate the name, that would indicate that the Item is not on your Item Data Sheet Overview tab.

6. Component Type – Select whether this is an Item or a Recipe (pre-blend).
7. Component Item Name – Add the name of the component from the Item Data Sheet Overview Tab. If the item is not already on that tab, you can add the itemon the tab. Once the configuration workbook is uploaded, that item will be created for you.
8. Recipe’s Formula ID and Formula Version – If the Component Item is a recipe, this field must be completed.
9. Amount – How much of the item goes into a batch. Enter a number here for the component amount (decimal precision varies by unit of measure). Refer back to what you selected as the computation method on the Formula Tab. For example, if you chose “Amount”, add the amount and Item UOM. 

   When selecting Sizes & UOM, it’s recommended to consistently use the same UOM. The TraceGains system will convert the UOMs for you in the site. Copy and paste to save time!

10. Overage% and Loss% - Enter percentage if applicable. Enter the percentage as a whole number. For example, 95% would be entered as “95”.
    1. Overages–nutrients in excess amounts of what is claimed on nutrient labels, which are added to compensate for any degradation during production, storage, distribution, and retail.
    2. Losses are due to degradation of dietary ingredients in finished products.
11. Notes – Enter any notes or additional information pertaining to the component (item or sub-recipe) here.
12. Gross%/Net% – If you chose Percentage as the calculation method on the Formulas tab, then this field is where you should add what percentage of the formula contains the item.
13. Is Active? – Applies to Supplement formulas only. Enter Yes if the item is active, No if not.
14. Label Text – Applies to Supplement formulas only. Enter label text here.

Recipe Packaging Items Tab

The Recipes Tab is where you add associated packaging data. Enter the Formula ID, Formula Name, Formula Version, and Recipe ID that the packaging item will be linked to.

1. Recipe’s Formula ID and Formula Version If the Component Item is a recipe, this field must be completed.
2. Component Item ID – get the value from the Item Data Sheet Overview Tab. The value in this column will become the recipe item’s internal ID. This will be the sub-recipe ID if the component is a recipe, or item ID if the component is an item. Only choose items or recipes with a type of 'Packaging'.
3. Component Type –Select Item or Recipe based on whether the component is a sub-recipe or item.
4. Component Item Name – Enter the component item name here. Autofills from Component Item ID column.
5. Amount – Enter a number here for the component amount (i.e. how many of these packaging items are needed).
6. Packaging Level - Select whether the item is tertiary, secondary, or primary packaging.

   Primary packaging is the packaging that contains the product. Secondary packaging includes boxes or containers containing specific quantities of primary packages. Tertiary packaging includes pallets and large shipping containers for storing and warehousing.

7. Item UOM – Currently, the only option available for this field is “each.”
8. Food Contact – Select Yes or No.

Formula FG Item Xref

This section of the workbook is used to associate a formula with an existing finished goods item. There is a list of example Finished Goods Items pre-configured on the Item Data Sheet Overview tab. If a particular Finished Goods Item is not listed on that tab, you can add it, and it will be created in the system upon uploading the configuration workbook.

1. Formula ID – Enter the Formula IDs as listed on the Formula tab.
   • Column B – Ensures that the Formula ID exists on the Formula Tab.
2. FG Item ID – Enter the Finished Goods Item ID from the Item Data Sheet Overview tab that this formula is for.
   • Column D – Ensures that the FG Item ID exists on the Item Data Sheet Overview tab.
   • Column E – Ensures that you have not added a duplicate of the Formula ID/FG Item ID combination.

Item Data Sheet Overview Tab

Often, items are added during a previous Supplier Management implementation. However, you can add any additional items that will be used in your formulas here. The Item Data Sheet allows you to upload items that make up your recipes and formulas. TraceGains will not allow you to upload a formula or recipe record with an item that is not already present in your data or on the Item Data Sheet.

1. Item Data Sheet – The item data sheet allows you to upload items that make up your recipes and formulas. TraceGains will not allow you to upload a formula or recipe record with an item that is not already present in your data or on the Item Data Sheet.
2. Item ID – The item's internal ID. Based on your company’s naming conventions. When creating a new Item on this tab, the Item ID must be unique.
3. Item Name – Enter the item name here. Based on your company’s naming conventions.
4. Item Stage –   Item stage can be one of two values:
   • Production – These are items that translate over to your Items Supplied records and have an associated supplier.
   • Research and Development – These items do not have an associated supplier, and are used for research and development purposes only.
5. Item Type – Item type can be one of three values: Raw Material, Finished Good, or Packaging
6. Item Group – A grouping mechanism to organize your item records. These values must be set up in your site before being used in the item data sheet upload. Existing Item Groups can be viewed via your site Items Supplied Tab > Item Group column.
7. Description – Enter a brief description here.

Allergens/Nutrition/Other Tab

This tab is included so that you can add your allergen, nutrition, and/or cost information if needed. Typically, users import allergen and nutrition data into the Item Data Sheet via an existing product specification in Specification Management, Marketplace, or from the Items Supplied Record in their site. However, you can manually add allergen, nutrition, and cost data.

What Counts Toward Your Contracted Formulas Total?

Tiers based on “Record” counts:

• Record “Unique ID” + Active Status = any (active, archive, in-active)
• Formula record, NOT recipes within a Formula
• “Sub-formulas” count as Formula records
• Versions of a record are NOT included in the count.