**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
- Restrictions
- Set up an example pricing calculator
- Build the Spreadsheet Pricing Engine in Pressero
- Testing your sheet

## Excel main sheet planning

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

## 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

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

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

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

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

**Spreadsheet Pricing Engine**.

## Building the Spreadsheet Pricing Engine in Pressero

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

**Engine Name**

*****field (default shows

__New Engine__as a name, that should be changed to reflect actual name).

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

**Output Cells**section will look like this:

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

**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).

**Qty Parameters**first line will look like this:

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

**Upload Completed Excel File**

*****link shown above and press

**SAVE**.

**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

- How do I Show Per Piece Price to show Each price on spreadsheet method pricing engine?
- Can I create custom options in my pricing engines that will not be visible to the user when ordering but that will show on the order details once the order has been placed?
- What formulas are supported on spreadsheets?