Ch. 014. Creating a Spreadsheet Method Pricing Engine

Note: Using this method requires advance knowledge of Excel and is included only in the Enterprise Packages. Because of the technical nature of this method, we are unable to provide free technical support beyond what is already included in this article. 

The Spreadsheet option has been developed to meet the needs of complex calculations that cannot be done with the standard pricing engines and enables you to manage your own pricing, especially when it is difficult or impossible to fit into the Calculator Pricing Engine schema. With the Spreadsheet Pricing Engine, nearly any spreadsheet that you can use in Excel can be used as a pricing engine.

Macros, special function libraries like the Analysis Toolpak, and design controls -- checkboxes, drop-downs, or other controls that float over the spreadsheet -- are not supported however, drop-downs that live within a cell are supported.

The Spreadsheet is essentially an Excel file and contains multiple worksheets based on Excel formulas allowing complex calculations such as the following example: 

Inside a press production system we can find several stages, involving several workflows, consumption of raw materials (paper, plates, etc.), labor time data, equipment depreciation, and other print consumables. The calculations of these items will allow a choice of which print machine should be used for print a specific job, what kind of paper has to be purchased, what format, costs and processes involved, etc. 

In this chapter:

Excel main sheet planning

The main sheet must be a specific structure for Pressero to read it. 
  1. Output cells are values of price and weight. (The output cells are the result of different calculations, that`s the way we get the price and the weight of the print job). 
  2. Quantity parameters are composed by 3 elements: 
    • Prompt cell, text displayed to the subscriber. (The prompt cells are those where the input data is collected, in other words, are those questions that the subscribers has to answer to prepare the budget, such as: width and height of the product, paper stock, binding, one or two sides, etc). 
    • Option cell, filled by the subscriber. (The option cells are those filled by the subscribers, the best option is work with a drop down list with all the available options for a customer). 
    • Range cells containing the offered options for subscriber. (The range cells contain all what we want to offer to our customers. Although we allow the customer to enter an arbitrary amount we must enter at least one option in the range). The range cells are typed with a colon, indicating the first and the last choice.
  3. Option parameters and other parameters are composed from the same three (3) elements that quantity parameters.   
  4. Attribute cells. We can retrieve a lot of data from the Excel sheet in order to prepare the job. Using Spreadsheet, we are able to do complex calculations allowing us budget creation. When we receive the Job Detail it is very helpful to know all the calculation process performed by the pricing engine, for example: the machinery selected to do the print job, the size of the printed sheet, the sheet number that we need to do the job, etc. This information comes through the Product Attribute cells inside an order. You can find more details on Pricing Attributes in this article under T. Pricing Attributes.

Restrictions

Spreadsheet allows us to perform complex calculations and can be very useful but it has some restrictions:
  • Macros are not supported.
  • The data that will uploaded to Pressero must be in the first Excel sheet.
  • More complex calculations, the longer it takes to display prices (sometimes it takes 5 or 6 seconds to get the price for each option selected).
  • Calculations are performed only with the available formulas in the server library (which is periodically updated). If you want to perform a calculation using a special formula (not included in the library), you could find some problems.
  • A quantity parameter MUST be defined in order for the pricing engine to function on the site.

Follow the steps below to set up an example pricing calculator 

Unlike the other pricing engines, the starting point of a Spreadsheet Pricing Engine is within Excel. To get started, you’ll need a spreadsheet that defines what components make up that pricing engine such as quantity, number of sides, substrates, finishing, proofing etc., what you're looking to achieve and an actual calculation of the end-result. Let’s take a simple step-by-step approach to designing such a spreadsheet using this simple spreadsheet:

 
It is important to note that the structure of the spreadsheet can help reduce the complexity and make it far easier for anyone to edit and understand the details at a later stage. For this reason in this example, we have created a section to the right that allows the user to define the specific parameters relating to the product price calculation called Set Price/Options Area, and a section to the left for the calculations to be made titled Calculations Area. The actual structure you decide to adopt is entirely at your discretion.
 
Starting with the Set Price/Options Area, we have defined Quantity Parameters shown here as “Quantity” and “Number of Sides” and Other Parameters i.e. those items that are not a Quantity, in this example shown as “Paper Type”, “Fixed Costs” and “Variable Costs”. In reality this could extend much further to incorporate any number of items such as inks, finishing, sizes, additional variable components such as applying a click charge that varies in cost depending on volume ordered etc.
 
The spreadsheet is simply calculating the cost of a sheet based on a selectable paper size size (the other paper size costs are calculated from the A3 cost), furthermore the user can select 1- or 2-sided printing; additionally there are some fixed and variable costs applied.
 
Having established the various parameters that are available for the pricing engine, the next stage is to determine the actual formulae and calculations to derive the final price. Moving to the left-hand side of the spreadsheet we then build a sub-table that defines the actual “user” selection when applied on a web site shown here as TEST CONDITIONS. This allows us to mimic the user selection and final price determination shown here in cell F11 and related total weight shown in cell F13.
 
 
Keeping in mind ease-of-understanding within the spreadsheet for other staff that may have to make changes in the future, we also recommend making smaller sub-calculations as shown here under Cost Calculations, to derive the price calculations for the fixed costs, variable costs including click charges, actual paper used (if not A3) and related price per sheet etc., separately. These smaller calculations will then be totaled to produce the final calculations; of course that is entirely your decision as to the approach adopted for the respective calculation.
 
Having built the spreadsheet, with user defined options for selection and respective cost calculation formula checked via the “Test Conditions,” we are now ready to load this spreadsheet into a Spreadsheet Pricing Engine.

Building the Spreadsheet Pricing Engine in Pressero

Within Pressero, go to Preferences > Pricing Engines > + Add New and select Spreadsheet .

 
Once selected the following format is shown that allow us to link the spreadsheet created to the pricing engine itself.
 
 
 
First give the pricing engine a name, in the Engine Name* field (default shows New Engine as a name, that should be changed to reflect actual name).
 
Second, we need to start linking the data from the spreadsheet to the pricing engine. This process is made far easier with the format of spreadsheet defined.
 
Let us start with entering links from the pricing engine to the spreadsheet starting with Output Cells: The Output Cells define the actual final output for the cost calculation and the weight calculation. There is no need to write in names for these as the Output Cell box understands that the first value will be COST and the second value WEIGHT.
 
Hence with reference to our spreadsheet we have:
 
 
 
Hence the Output Cells section will look like this:
 
 
Next we move onto the Qty Parameters section. As expected, this defines the reference within the spreadsheet for any quantity related parameters, thus with reference to our spreadsheet we have two quantity parameters i.e. Actual Quantity to be ordered, and number of sides as shown below.
 
 
 
When entering data into the Qty Parameters field, each parameter requires three cell references from the spreadsheet in the following sequence:
  • FirstName Cell – pointing to the cell that defines the “Displayed Name”.
  • SecondUser Selection or Input Cell – displaying the actual user choice from the selection provided for the defined parameter
  • Third – Options Range – referencing the selection of choices open for the parameter (this can be a single value or a range).
 
Thus with reference to our spreadsheet:
 
 
 
the Qty Parameters first line will look like this:
 
 
 
where B12 = Quantity (Name); C12 = actual user value selected & J12 = choices available
 
Taking this further for Number of Sides we add a second line thus becomes:
 
 
 
where in the second line B13= name or “Number of Sides”, C13 = the user selection and in this case we have a range I15:I16 indicating the choices available being 1 (I15) or 2 (I16).
 
Repeating the process above but this time for non-quantity parameters i.e. Other Parameters we define the remaining options in this case for Paper Type and Paper Size, thus following exactly the same process we have:
 


where in the first row, B14 defines the name as Paper Type:, C14 the user selection and L13:L20 the different paper types available for selection.
 
In the second line repeating this sequence you note that the paper sizes are derived from a horizontal selection in the sub-table to determine A3, A4, A5, A6, etc.
 
Thus the final sheet looks like:
 
 
 
The final stage is simply to load the spreadsheet itself via the Upload Completed Excel File * link shown above and press SAVE
 
You can now use the Test tab (see next section) to confirm how the engine will behave before assigning it to products on the products page as shown below:
 
 
 
If you ever need to change the pricing engine, simply select the Download – Existing Excel File as shown below, make the necessary changes to the spreadsheet, save and Upload Completed Excel File to reload the modified spreadsheet and SAVE.
 
 

Testing your sheet

If you are not getting the output that you expect you can validate the input fields that you setup in Pressero against your file. To do this, select one of your Pressero Input cells and go to: Data > Data Validation > Allow: List > Source: =b19:b40 (as example of the range you entered in Pressero). You can then click on that cell, make a selection and confirm what you are getting in the output fields to make sure that is what you expect to see in Pressero. 
 
Conditional Logic in Spreadsheet Pricing Engine
Here's a link to a video on how to do a conditional logic spreadsheet calculator:

And a link to the spreadsheet used in the video above: