If you have only a few options (besides quantity) from which your customer will choose, use the Grid Parameters area to enter the option name, and the choices your customers will choose from. If you will be providing many options with many selections, you may want to enter them in the Calculator Parameters instead. Why? With many parameters and many options, the Grid method can create a very large table with cells you will need to fill in. In contrast, the Calculator Parameters can take advantage of a few standard calculation formulas instead. So a Grid with 5 quantities (100, 200, 300, 400, 500), 3 Parameters (Size, Ink Colors, Sides) each with 2 possible selections will result in (2+2+2)x5=30 price cells that must be filled in. Not too bad. But if the selections increase to 4 each (4+4+4)x5 you will need to fill in 60 price cells. Using the Calculator Parameters instead will likely save some time. Grid and Calculator parameter 's can often be used in combination. It is advised to watch the training videos on pricing calculators before creating your first one for a better understanding of grid and calculator parameters.
When using the grid parameters the spreadsheet will create a grid for you where you will enter in the final cost for each of the combinations that are possible.
- Name: This refers to the prompt your customer will see for the choice they need to make for this product. In the example image at the top of the page, there are two grid parameters - Paper Stock and Color. When creating the example pricing calculator, "Paper Stock" and "Color" were entered in the Name fields for these two grid parameters.
- Options: Enter the options for each of the Grid parameters you are creating. There is a maximum of 5 grid parameters allowed. As mentioned earlier, if you have more than 5 parameters for this pricing calculator, you will want to use the calculator parameter area, or a combination of both grid and calculator to avoid a grid that is very large and difficult to manage. For this example, the options for paper stock are 120# Gloss Coated Cover and 80# Gloss Coated Cover. The options for the color grid parameter are 4/0 and 4/4. Be sure to enter one option per line followed by a hard return. You cannot have the same option listed twice.
When using the calculator parameters the spreadsheet will list each parameter and its options on a separate line. You will set the pricing for each line individually. If you have both grid and calculator parameters being used, the spreadsheet will first calculate the cost from the grid and then add each calculator parameter amount in the order that you enter them here, making adjustments to the overall cost as it goes.
- Name: This refers to the prompt your customer will see for the choice they need to make for this product. In the example image at the top of the page, the calculator parameters are Design Services and UV Coating.
- Options: Enter the options for each of the calculator parameters you are creating. There is a maximum of 5 allowed on this form, but you will be able to enter more directly into the spreadsheet later when you have completed this part of the process. For this example, the options for Design Services are "Yes" and "No" and the options for UV Coating are "Yes" and "None". Be sure to enter one option per line followed by a hard return. You cannot have the same option listed twice.
Completing the spreadsheet
- When you have completed entering the options for your pricing engine, click on "Generate Excel File". If you entered your parameters and options correctly an excel spreadsheet will be downloaded.
- Open the file using the free Mac/Windows www.OpenOffice.org spreadsheet application (or Excel).
Entering your pricing into the spreadsheet
The next step is to enter the pricing information to the spreadsheet that was created for you.
Let's first take a look at an example spreadsheet (above).
A. Grid Parameters section is always at the top of the spreadsheet.
B. Grid Parameter Options. Here you can see the different combinations of parameter choices entered into the form. For every parameter entered, there is a combination of every other choice listed. In the example above, you will see that each paper stock and color choice is matched into a row. We have four rows (rows 5-8) - 120# Gloss Coated Cover with 4/0, 120# Gloss Coated Cover with 4/4, 80# Gloss Coated Cover with 4/0 and 80# Gloss Coated Cover with 4/4. In each row, we will enter the pricing for that combination of choices in our grid.
C. Per Thousand Weight. If you are going to do integrated shipping (FedEx, UPS, etc.) you have to indicate the per thousand weight of your products. For print products, this is typically the weight of the different stock options.
D. Quantity 1 option. Even if your pricing engine uses only Calculator parameters (see area F), the first Quantity parameter will always be shown at the top of the spreadsheet. The quantity parameters you entered will go across the columns. You will be entering in the total cost for that set of combinations and that quantity in the cell. In the example above, the price for 250 sheets of 120# Gloss Coated Cover with 4/01 is entered in cell D5.
E. Each addl. If you entered an Each Additional... Quantity Increment for Quantity 1, you will see it listed here. You can then enter the cost of the additional quantity increment. In the example above, the each additional quantity increment is set as 1000. If we allow the user to enter arbitrary quantities and the user orders 1500 sheets of 120# Gloss Coated Cover 4/0, the system would take the last price entered in the grid for that combination of choices - which $40 for 1000 - and add the additional cost. The price set in the each addl cost for that combination of choices is $35. Since the user ordered 500 more than our greatest quantity in the grid, the system would use the $35/1000 and calculate the cost for that 500 to be half of $35 or $17.50. This would be added to the $40 for the 1000 for a total cost of $57.50.
F. The Package Weight area is optional. If you want to indicate an additional weight for the package that the product will be shipped in for the different set of Quantities in the grid area, you can enter that here. This weight will be added to the per thousand weight when determining the cost of integrated shipping.
G. Quantity options 2-5. This is where the additional Quantity choices are shown. If you choose to have more than one quantity parameter the name and quantity options will be listed here. In the example above, Number of Pages per Document is Quantity 2 and Number of Sides is Quantity 3. These quantities can never be used with a spreadsheet that only uses the grid area. You will refer to these when you are completing the calculator parameters area. They can be used under the "Applies To" column for calculator parameters. See below for further explanation.
H. Calculator Parameters section is always at the lower half of the spreadsheet.
I. Parameter Set. The parameter set is created with the "Name" entered for each calculator parameter. This is what your customer will see as the label for the calculator parameter when they are making a choice. In the example above, the parameter sets are Design Services, UV Coating and Clicks.
J. Calculator Parameter Options. Here you can see that each option is listed with each of its parameter set in the form. Instead of every possible combination of calculator parameters being shown, you are going to indicate a fee for just that option and its choice. This makes managing and updating the calculator much easier when you have a lot of choices that determine the final cost.
K. Per Thousand Weight. Here is another area you can enter the per thousand weight. Again, typically for a print product, this will be based on the paper stock used. If you already have the stock entered in the grid area and filled in the per thousand weight there, you will leave this area empty. If you decide to just use the calculator parameters, or a combination of the two, and your stock is shown in the calculator area, you will indicate the per thousand weight here instead.
L. Show As Option. There are times you want to add in fees that make up the cost of the product, but the fees are not based on a direct customer choice. This could be a markup, or in the example above you can see that "Clicks" calculator parameter is set to not show as an option because it is a calculation based on the various other choices the user is making.
M. Applies To. The choices here are going to be Q1, Q2, Q3, Q4 or Q5 or many other possible calculations. Q1 stands for Quantity 1, Q2 stands for Quantity 2, etc. This is referring to the quantities parameters you created. In the example above, Quantity 1 or Q1 is Number of Copies/Sets. Quantity 2 or Q2 is Number of Pages per Document. Quantity 3 or Q3 is Number of Sides. When setting the applies to, ask yourself, "For that particular row, what does the cost refer to?" In the example above, notice that the clicks parameter has the applies to set as Q1*Q2*Q3. The cost for the clicks will be based on the number of copies/sets x number of pages per document x number of sides to arrive at the total number of clicks. The applies to contains a dropdown list with many different options. To add a calcuulation that you do not see on this list, scroll down on the spreadsheet. You will see a in column A beginning after row 90 that the applies to the dropdown list is being populated from these grey shaded cells. You may type over any of the calculations that you do not need. Once you do that you will then be able to select your from the applies to list in the calculator parameters section.
N. Billing Type. The billing type determines how the cost for the parameter will be calculated. You have five options for the billing type:
Flat Rate - Use this option if you wish to charge a set fee for the option - regardless of the quantity that is ordered. You would enter the flat fee in the Cost Per Billing Unit column. In the example above, we are charging a flat rate of $0 if the user chooses not to use design services. (See row 19.)
Single Unit - Use this billing type if you wish to charge a per piece price. You would enter the per piece price in the Cost Per Billing Unit column. In the example above, we are charging a single unit cost of $0 per piece if the user selects None for UV Coating. (See row 21.)
1000 Units - Use this billing type if you wish to charge a per thousand price. You would enter the per thousand price in the Cost Per Billing Unit column, In the example above, we are charging $5 per thousand if the user chooses Yes for UV Coating. (See row 22.)
Percent of Total - Use this billing type if you want to mark up the total cost by a certain percentage. You would enter the percentage in the Percent Markup column. In the example above, we are marking up the cost by 10% if the user chooses Yes for Design Services. (See row 20.)
Grid - The grid billing type references a value in the grid parameters section. This billing type causes whatever the grid lookup value is to be multiplied by the formula set in the applies to column. This is useful when you want to use the grid to lookup a price per set, and then multiply by the number of sets. In the example above, the clicks calculator parameter is set to use the grid billing type and uses Q1*Q2*Q3 as the applies to. If the user were to choose 250 copies/sets on 120# Gloss Coated Cover and 4/0 as the color, the system would look up the cost set in the grid for that combination of choices - which is $20 (cell D5). That look up value from the grid would be multiplied by our applies to formula of Q1*Q2*Q3. We know that the user chose 250 copies/sets, so Q1=250. If the user chose 4 pages per document and single sided, then Q2=4 and Q3=1. The system would calculate the clicks cost as $20(grid lookup value) x 250(quantity 1) x 4(quantity 2) x 1(quantity 3) which equals $20,000.
O. Setup. The set up charge will be added on to the calculation for the calculator parameter/option. You may want to use this if you are offering folding or cutting as options and need to charge a set up fee for the folding or cutting machine. In the example above, we are charging a $25 set up fee for UV coating in addition to our billing type calculation.
P. Cost Per Billing Unit. The cost per billing unit will be used to calculate the cost of the calculator parameter/option if the billing type is set to flat rate, single unit, or 1000 units.
Q. Min Charge. The minimum charge can be used to insure that the user will be charged a minimum amount if the calculation for the parameter/option falls below the minimum charge. If the minimum charge is set to $10 and the calculation for that parameter option comes to $8, the user will be charged the $10 minimum.
R. Percent Markup. The percent mark up will be used to calculate the cost of the calculator parameter/option if the billign type is set to percent of total.
S. Min/Max Qty. The min qty and max qty settings can be used to set price breaks for various quantities. Leaving both the min and max quantity set to zero will cause the system to ignore the min/max quantity settings. In the example above, the UV Coating parameter charges the user $5 per thousand for quantities of 0-10000, $4.50 per thousand for quantities of 10001-25000 and $4 per thousand for quantities of 25001-10000000. Note that the min/max quantities will be based on the applies to calculation. Since the UV Coating option has an applies to of Q1*Q2, then the min/max quantities will be determined by the result of quantity 1 times quantity 2.
T. Pricing Attributes Pricing attributes allow you to define values based on choices that a user makes in a pricing engine and pass that value to a third party system with which you are integrating. You must define an attribute and a value for that attribute. In the example above, you see that we have defined UV_Coating as an attribute. If the user chooses no UV Coating, the the value of None will be assigned to the UV_Coating attribute (Row21). This information will be available in the order detail area, in the job detail report and in the Pressero XML file generated by the Automated Workflow integrator. Users integrating with the PressWise system have specific attributes that can be used to pass information into PressWise. See this article for more information: http://support.aleyant.com/kb/a661/presswise-integration-passing-values-to-presswise-via-pricing-attributes.aspx
Finishing the pricing engine
Next save your changes to the spreadsheet. Close the calculator parameter form window by clicking the X in the upper-right corner. Choose the spreadsheet file you just created under "Upload Completed Excel File". Click "Choose File" and select your file. Then click "Save" to save the pricing calculator.
Related Knowledge Base Articles: