# Solve Complete Example

In this complete example, we seek to maximize revenue by determining the optimal number of baked goods to produce, from a limited selection. Each product requires a selection of ingredients, and each ingredient has a maximum available amount that we cannot exceed.

First, we copy this information into Excel – we also have separate sheets with slightly different quantities, to replicate different demands from different markets. We have one of these sheets representing the UK, one the US, and one representing France.

We import this spreadsheet into Pyramid, taking each of the separate sheets as a separate Pyramid table. The basic model is as follows:

To convert the tables to the correct format for Pyramid, we use Unpivot nodes to flatten the columns:

We next use Calculated Column nodes to add a “Country” column, to associate a country with all the data. We will later use this to filter the displayed data by country:

We must rejoin the tables, using a Union node. Finally, before we run the model, we rename the columns with more sensible names, using the Rename Columns node:

The final model looks as follows:

Building this model and running a new Discovery gives us the following:

We have introduced a slicer, so we can display the spreadsheet for a specific country. We also add the other data specified, which Solve needs to perform its calculations:

Note: As well as the stated data, we must also enter a set of values for “Amounts to Produce”, which have been entered in cells R3 to R7. These are the decision variables, whose value we can change so as to maximize the “Total Revenue” (cell U2), which is defined as the sum of the numbers of the different products to be baked, multiplied by the profit for each one (the values in cells O3 - O7). The formula for the revenue cell, U2, is

=SUMPRODUCT(R3:R7,O3:O7)

## Solve Wizard

Click the Solve button to display the Solve panel:

### Optimization Objective

First, we specify the Optimization Objective:

Note: To enter cell values, you can click on the field where they are to be entered, then on an individual cell, or click and drag over a range of cells to indicate the cells to be entered.

### Constraints

Enter the constraints information from the Constraints section of the grid. All constraints of the same type can be included in one instruction in the Solve panel – in our example, the values in cells K7 – K12 (quantities used) cannot be greater than the values in cells L7 – L12 (maximum quantities available). Here, we have also introduced a constraint that we must have at least six of each item (cells R3 – R7 must all have a value of at least 6):

The option to Make Unconstrained Variables Non Negative is selected by default.

### Optimization

Finally, click Apply or Apply and Close to run the optimization. The details of the optimization are given in the Results List in the Solve panel:

The actual results are displayed in the spreadsheet, which shows the optimal amount of each product to bake, the amount of the different ingredients used, and the resulting revenue:

So, Solve has determined that the optimal revenue is obtained by producing the following, given the figures supplied:

• sauce (4204 bottles)