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