Estimating Overview *
TracManager Estimating utilizes a product catalog that facilitates custom products with associated features and options. Products are organized into Feature/Option groups that will share the same choices for features and options.
All estimates utilize the product catalog including one-of-a-kind custom products that don’t seem to fit into a catalog item, can be created by referencing an empty ‘custom product’ and adding labor and materials as desired. The most preferred method for estimating is to utilize repeatable products that are customized through the use of features and options and dimensions. This method facilitates the fastest and most consistent result.
Product Catalog – The Foundation for all Estimates *
A sample Demo catalog is available that provides a small sample of products that demonstrate the relationship of the products, features and options.
Normally, the catalog is built using Excel spreadsheets which are imported into TracManager. The Product spreadsheet describes the product level including the organization, Product ID, description, UOM and UOM Formula.
Product Price Calculations *
Products may be priced by a unit of measure such as Each, LF, SF etc. The UOM Formula determines the calculation of the number of units. For example a product sold by the linear foot would have a UOM of “LF” and a formula of “W” for width. The price is set to the price of one linear foot. When the product appears on the estimate it will use the Width dimension to determine the number of units. The (number of units) x (price per unit) = product price.
Additionally, features and options are added to the base product price, the feature choice quantity is also determined by a formula and the total of the features, options and product equals the total for the single product line item.
The final line item product is the sum of the initial (product price times the number of units) and the total of the selected feature and options prices for the product.
Importing From Spreadsheet – Price Method Values *
Use the following chart to determine the value to be used in the PriceMethod column when importing a product, feature or option.
- 0 : Standard unit price. This is the only selection that may be used at the product level
- 1 : Percentage Mark Up based on the Feature and Option costs only
- 2 : Percentage Mark Up based on the Extended Product Unit Price (that is the unit price * quantity)
- 3 : Percentage Mark Up based on subtotal1 (product + features and options)
- 4 : Percentage Mark Up based on subtotal2 (total plus upcharges1)
- 5 : Percentage Mark Up based on Total Product Price
- 6 : Percentage Margin based on the Feature and Option costs only
- 7 : Percentage Margin based on the Extended Product Unit Price (that is the unit price * quantity)
- 8 : Percentage Margin based on subtotal1 (product + features and options)
- 9 : Percentage Margin based on subtotal2 (total plus upcharges1)
- 10 : Percentage Margin based on Total Product Price
Product Cost Calculations *
Products and features may also include labor and material quantities. The labor and materials listed may ultimately be used to populate the labor and material budget for a new order. Initially, labor and material details are excluded from the catalog and added after the catalog has been created. When these values are provided, the estimator will be able to click on the Budget Tab to view the estimated profit/loss for the current estimate.
Product Strategy *
Since the estimate is based upon products, we must start with a list of products that we will use to estimate with. How a product will be sold should be decided first. For example you could have a list of base cabinets with no doors sold by size such as B21, B24, B27 for 21”, 24” and 27” wide cabinets. Another method would be to sell by the linear foot and the price would change as the dimension changes.If sold by the box, the “UOM Formula” would be set to one (1). If sold by the linear foot the” UOM Formula” would be set to the width (W).
The product features and options will also contribute to the final product price. For example a feature named “Material” could present a list of choices. Each choice could be priced at a different price. A paint grade material may be priced lower than a high quality hardwood or veneer. The quantity of the feature would most likely be determined by a formula using the dimensions of the product.
Options may also be added to the product and may also increase the price such as special trim, installation, design charge etc.
Step #1 Determine the desired pricing strategy for the product level. This will involve both product level and the break out for the desired features and options.
Step #2 Create a naming system for the products, features and options along with a more verbose description. For example B21 would be a product name and “Base cabinet 21x24x30” could be a description. Furthermore, the products are organized into a three level category for organization.
Step #3 Organize products into groups that would share common features and options. Then create a name for the feature/option group and assign this name to the products. For example Base Cabinets, “Counter tops” etc. All products in the “Counter tops” group would only be presented with choices from that group (and not the Base Cabinets group).
Step #4 Populate the Product spreadsheet. Use the TracManager products template for importing
Step #5 Populate the Features spreadsheet. This is normally a separate page of the same spreadsheet
Step #6 Populate the Options spreadsheet. Again, this is a normally another page on the same spreadsheet.
Step #7 Populate the Feature/Options FOBaseQty spreadsheet. This spreadsheet establishes the relationship between the products and the features and options.
TracManager provides the ability to import and export the product catalog using Microsoft Excel spreadsheets. It is recommended that the product catalogs be managed using spreadsheets. This makes it easy to copy and paste sections that repeat.
Note that the names of the spreadsheet pages have a number in parenthesis. Such as (1) Products, (1) Features and (1) Options. The value of one (1) indicates designates the type of information contained in the spreadsheet. Always use a (1) for the Products, Features and Options.
Spreadsheet Type designations by value in parenthesis:
- (1) Products, Features and Options
- (2) Feature/Option Groups and formulas
- (3) Product BOM details
- (4) Materials and Inventory
- (5) Suppliers and Vendors
Features and Options *
The desired feature and option choices along with the formulas for determining quantity are defined in a spreadsheet. This spreadsheet references the same BOMID product ID used in the products. features and options spreadsheet.
A feature is a named list of choices that are always presented when a product is added to an estimate. The feature list includes one choice as the standard choice.
Assume we have created a Feature/Option group named “Base”. We could also create a feature within this group name “Material”. Then we would designate a list of choices for the feature we names Material. In addition, each feature choice will have a formula that will be used to calculate the quantity of the feature we choose. Each feature choice will also include a specified price for the feature.
Feature/Option Group: Base
Feature Name: Material
|1/2 maple therm||(W*H) + (2*(D*H))+(W*D) * 1.3|
|1/2 Oak 2s||(W*H) + (2*(D*H))+(W*D) * 1.3|
|1/2 White 1s||(W*H) + (2*(D*H))+(W*D) * 1.3|
|1/2 White 2s||(W*H) + (2*(D*H))+(W*D) * 1.3|
|1/4 Cherry 1s||(W*H) + (2*(D*H))+(W*D) * 1.3|
|1/4 Dusty 1s||(W*H) + (2*(D*H))+(W*D) * 1.3|
|1/4 EbStar 1s||(W*H) + (2*(D*H))+(W*D) * 1.3|
|1/4 Marbelized 1s||(W*H) + (2*(D*H))+(W*D) * 1.3|
An option is a named list of choices that are only presented when a the Add Option menu choice is clicked. The option otherwise functions in the same way as the features. An example of an option could be the addition of an LED lighting fixture, decorative hardware, distance charge etc.
The Feature/Option “(2) FOBaseQTY” spreadsheet consist of the following columns:
- OptionGroup: The name of the feature option group. All products having the same option group will also have the same feature choices.
- FeatureName: The name of the feature list – such as “Material”.
- parentBOMID: Left empty unless the choice only applies to a specific product within the group. In that case this is the BOMID of the parent product.
- choiceBOMID: The BOMID of the feature/option choice.
- bOption: Set to TRUE if the line entry relates to an option. otherwise FALSE.
- bStdChoice: Set to TRUE for the choice that is to be used as the standard choice. There should always be one (and only one) choice within the named feature set to the standard choice.
- Formula: The formula to calculate the quantity of the feature or option choice. For example if the feature quantity is for square feet, the formula may be “(W * H)”. If the quantity of the feature is one per product the formula may be “1”.
- PriceMethod: The method for determining the product, feature or option price. See “Importing From Spreadsheet – Price Method Values” above for values
|Option Group||Feature Name||parent BOMID||choice BOMID||bOption||bStd Choice||Formula|
|1 Door Base||Finish||FI LIGHT STAIN||FALSE||FALSE||(W*H) + (2*(D*H))|
|1 Door Base||Finish||FI MED STAIN||FALSE||FALSE||(W*H) + (2*(D*H))|
|1 Door Base||Finish||FI NATURAL||FALSE||FALSE||(W*H) + (2*(D*H))|
|1 Door Base||Finish||FI NATURAL DUSTED||FALSE||FALSE||(W*H) + (2*(D*H))|
|1 Door Base||Finish||FI NATURAL GLAZE||FALSE||FALSE||(W*H) + (2*(D*H))|
|1 Door Base||Finish||FI PAINTED||FALSE||FALSE||(W*H) + (2*(D*H))|
|1 Door Base||Finish||FI PAINTED DUSTED||FALSE||FALSE||(W*H) + (2*(D*H))|
|1 Door Base||Finish||FI PW NATURAL||FALSE||FALSE||(W*H) + (2*(D*H))|
Sample Catalog *
A sample product catalog provides an example of how the estimating system works. You may download a Demo Catalog with zipped file for the spreadsheets.