Ch. 013. Creating a Pricing Calculator Pricing Engine

In this chapter:

Unlike Unit of Measure and Price Break Pricing Engines, which only base pricing on quantity, Pricing Calculators offer you the ability to set your pricing based on parameters other than just quantity. Creating the Pricing Calculator is also handled differently than Unit of Measure and Price Break Engines. The entries are not completed in a simple grid area but instead the calculations will come from an Excel spreadsheet. The actual spreadsheet can be created using two different methods:

  1. The standard method: Using the form page within the pricing engine area of preferences. This form will walk you through the different bits of information the system needs in order to create the spreadsheet for you. Once it is created you will fill in the pricing information and then upload it to the pricing engine.

  2. The other option is to use a fairly new spreadsheet method. You can read more about the spreadsheet method in the chapter that covers the process. Please note that using this method requires advanced knowledge of Excel and is included only in the Enterprise Packages. 

We will discuss the first option (standard method ) in this chapter. It is highly suggested that you watch the training videos on creating pricing calculators since there are many ways to approach this type of engine, and several options available to you to create what you need. 

In the example of a pricing calculator engine above, note that a buyer can be given a number of options that will determine the cost of the product. 

Adding a new Pricing Engine

Like UOM and Price Breaks, you can use each calculator engine in multiple sites, with multiple products.  To create a new Pricing Calculator: 

  1. Click Create New Pricing Engine to create a new pricing engine. 

  2. You will then select from a drop down list the type of engine you are creating. Select Calculator.
  3. You will then need to name the pricing engine. It is good to keep in mind that all your pricing engines for all your sites are listed in this one area, so give it a name that will be easy to identify later when you assign it to a product within a site. All sites can share the same pricing engines so if it is a standard price that you will use over and over among many sites (ex: 8.5 x 11 digital color copies) then give it a general description for the name. If it is a price that is specific to only one customer then you may want to start it with the customers account name (ex: ABC Corp 4 page color brochures). 
  4. Click on the button "Click to Begin New Pricing Calculator"

A form will display at this point for you to complete. You will want to have a clear understanding of the pricing engine you want to create before starting this process. If this is the first calculator you are creating, we suggest you start off with a very simple calculator (you can watch the  training video and follow along if you wish) so that you get an understanding of how each area works. 

Completing the form

Quantity Parameters:

You can have a total of 5 Quantity Prompts. The first quantity entry will always be shown in the grid area and then the remaining 2-5 quantity parameters will be set apart in the spreadsheet.  In many calculators you may just need one quantity field such as the total amount the buyer wants to order. In more complex calculators you may need more. See the above image for an example of this. Notice we are asking for the number of Copies or Sets they are ordering. We also need to know the number of pages in the document they are sending and if the pages are single or double sided. Therefore, we have three quantity parameters.  This example would be fitting for a digital print job where we need to find out the total number of clicks. The spreadsheet will be calculating the number of sides x the number of pages they want x the number of sets.  

  1. Name: Enter the name you want to give the quantity prompt. In the example shown at the top of this page this would be "Number of Copies/Sets". 

  2. Options: This is where you list the options you are giving the customer for that particular quantity prompt. Each quantity needs to be on its own line. Do not use commas and be sure to enter a return after each prompt. Only numeric entries are allowed here, and you can only have one option listed once - no duplicate entries. You will have a choice when you add this pricing engine to a particular product in a store whether you want to allow arbitrary quantities or not. This means that even though you may be entering quantity choices of 100, 200, 300, etc. so that you can set certain prices based on each choice, that you will let the customer enter any quantity they want (125 for example) and have the calculator look up the range in the spreadsheet. For now, you need to either enter the quantities you will have them choose, or the quantity breaks you need. 
  3. Each Additional... Quantity Increment: You will only see this for the first quantity parameter.  If you choose to allow the user to enter arbitrary quantities, you can enter an additional quantity increment here.  In the spreadsheet you will be able to enter a price for this quantity increment that goes beyond your greatest quantity option created in #2.  For example, if your quantity options were 250, 500 and 1000, and your each additional quantity increment is 1000, the system would use the additional quantity increment price to determine the price for quantities greater than 1000.  This would only apply if you choose to allow the user to enter arbitrary quantities rather than limiting the user to a dropdown list of quantities entered in the options and you choose to use grid parameters.

About Grid and Calculator Parameters

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. 

  1. 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.
  2. 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. 

Calculator Parameters: 

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. 

  1. 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. 

  2. 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

  1. 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.

  2. 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.