The Pricing Spreadsheet Price Engine option was developed to provide a way of doing complex calculations that cannot be achieved with Price Breaks, UOM or Price Calculator price engines. With the Pricing Spreadsheet Price Engine, nearly any spreadsheet that you can use in Excel can be used as a pricing engine.
The spreadsheet price engine is an Excel worksheet allows for complex calculations such as inside a press production system where there are 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.
A downloadable example of a complex spreadsheet pricing engine
Before continuing further down this article, you may want to look at a working example of a spreadsheet method calculator for an advanced workflow. The article linked below has a downloadable example Excel worksheet, the coordinates listed as they would be entered into the pricing spreadsheet configuration window in Pressero, and there is a presentation about spreadsheet price engines that can be downloaded as a PDF for additional guidance.
Restrictions
A Spreadsheet Pricing price engine allows for complex calculations to be done within the cells of the worksheet, but does have a few limitations. 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.
- A quantity AND a weight parameter must be defined as output fields in order for the pricing engine to function on the site without error.
- The data that will used by the price engine must be in the first Excel sheet (tab) in the Excel file.
- Macros are not supported.
- Special Excel function libraries, like the Analysis Toolpak, not supported.
- Design controls such as checkboxes, drop-downs or other controls that float over the spreasheet are not supported.
- More complex calculations may take longer to display prices (sometimes it takes 5 or 6 seconds to get the price for each option selected when using complex calculations).
- Calculations are performed only with the available formulas in the server library on Pressero (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.
- Cyclic formulas are not supported; it is not possible to get a result if the result is a determining factor for the calculation.
- Spreadsheet pricing engines support up to 5 quantity parameters.
Excel main sheet planning
The main sheet must be a specific structure for Pressero to read it.
- 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).
- 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.
- Option parameters and other parameters are composed from the same three (3) elements that quantity parameters.
- 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.
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:
- First – Name Cell – pointing to the cell that defines the “Displayed Name”.
- Second – User 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:
Common Questions and Answers: