An Example (cont'd): Building a Model Using a Spreadsheet

This page illustrates how the simple contribution margin model described on the previous page might be implemented as a spreadsheet. We break this down into a number of tasks:

• Enter the known values (such as price, marginal cost, etc) into the spreadsheet
• Enter relationships between these and other values into the spreadsheet as formulas
• "Run" the model to seek the results we wish to obtain
• Create visual displays that illustrate the results we have obtained.

We show here how, despite the fact that spreadsheets are often used for modeling problems, they are not a good fit because creating them is a highly manual, tedious process. Furthermore, they are error prone, difficult to experiment with and update, and therefore relying on them can often lead to an incorrect decision.

Enter Known Values into the Spreadsheet

The model we explored on the previous page required that we fix a price and a marginal cost to determine the contribution margin for each sale. To enter this into a spreadsheet, we need actual numbers for these values. For the purposes of this exercise, let us use the values shown below:

Combining these with the segment information, we have the following spreadsheet:

Entering Relationships as Formulas

The numbers shown in the spreadsheet cells above must be entered into the equations that ultimately determine the total contribution margin, taking the various properties of each market segment shown above into account. The result is shown below:

As seen here, the price cutoff function tests whether the price is low enough in a segment so that the product is purchased. If so, then the price cutoff returns a "1", which is then multiplied by the segment size and the percent penetration to determine the segment volume (recall this is the number of people in the market segment who purchase the product). The volume is then multiplied by the price to determine the total revenue per segment. Similarly, the variable cost is multiplied by the volume to determine the variable cost per segment. The difference between these two values is the segment contribution margin, and the sum of all margins is the total contribution margin.

At this point we have created a spreadsheet to run the pricing calculations for our "toy" problem to determine the optimum price for a product that is sold into five market segments.

Running the Model

Now that the model is built, our goal is to find the price that produces the highest total contribution margin. We might experiment by changing the price in increments of $10, starting at $20. The total contribution margin for a price of $20 is is $1,267,500. By raising the price to $40, the contribution margin also rises, to $5,525,000. The spreadsheet in this state is shown below:

However, when we raise the price further, to $50, the contribution margin lowers, because we have crossed the price threshold for two segments, so nobody in those segments purchases the product.

We might conclude from this experiment that the best price is $40. However, if we continued to experiment, we'd find that there is actually a better price, at $60, because this is still below the largest segment's price cutoff threshold. This yields a total contribution margin of $5,625,000, producing an extra $100,000. By using a spreadsheet for model experimentation in this way, we may therefore risk choosing a price for our product that lowers the contribution margin we earn. Even a simple problem like this one has enough complexity that simple experimentation can be fooled.

Visualizing the Results

There are a number of ways to address the problem illustrated above where the best price could have been missed by simply trying higher and higher prices until the price began to decrease. One approach sometimes taken by spreadsheet modelers is to visualize the shape of the relationship between price and total contribution margin in a graph. As a preview, note that the relationship between price and contribution margin given the model parameters developed so far can be viewed as follows:

This graph shows the price of $40 and $60 discussed above, demonstrating why simply trying higher and higher prices until one begins to decrease is not a good strategy.

Unfortunately, creating even a simple chart like this for a modeling problem in a spreadsheet is very difficult. The typical approach is to list all of the possible price values in the leftmost column, and each intermediate calculation in a column to its right. In the rightmost column is the total contribution margin at each price point. This spreadsheet, part of which is shown below (hover over the image to see the full spreadsheet), is incredibly large and very difficult to check for errors.

In particular, to build this model required:

• Over 500 formulas
• Twelve bugs fixed and repaired
• Two hours of time by a quantitative expert

Note, however, that even this exercise did not lead to the optimal price for this model. Upon further examination, the optimal price is at $44, which was not found in the model shown above, where price is tested in increments of ten. At a price of $44, the total contribution margin is $6,409,000 - over $800,000 higher than the value found earlier. The graph showing this price is shown below. The red points indicate the positions at increments of 10 that were identified previously. The red points missed the best price, at $44.

Clearly, a spreadsheet is not an ideal tool for constructing even the simplest of models like this one. Even if a modeler were willing to expend the effort required to undertake the process above, initial model construction is is only one task of many. They will also need to:

• Experiment with changing different input values: In addition to determining the impact of different prices on total contribution margin as shown in this model, there are a number of additional input values that a user might wish to change. These include the variable cost, segment cutoff values, and penetration rates. A user may want to manipulate these if they have some control over their values, or some uncertainty around which values are correct. In a spreadsheet, this experimentation requires a new row with every combination of possible input values, along with associated formulas.
• Change the structure of the model: Introducing a new input, intermediate calculation, or target variable is very difficult, requiring many formula updates. For instance, in our example, the structure of market segments might shift, resulting in six instead of five.

In summary, a spreadsheet is not well suited to be a modeling tool. Basic modeling tasks, even in a "toy" situation, are cumbersome, time consuming and are still error prone even if the data and formulas are all correct. Yet, surprisingly, Quantellia research has revealed that spreadsheets are used in many disciplines for modeling. Furthermore, even quantitative experts find their tool sets cumbersome and inflexible, often taking weeks or months to build a realistic model.

Although sophisticated numerical methods have been developed to solve problems like these, they are not used by the vast majority of decision makers. Our research indicates that there are two primary reasons: 1) the data underlying the models is difficult to obtain and 2) these systems are written for numerical experts, not for an everyday decision maker. For this reason, their use is restricted to a limited set of persons inside an organization.

This does not have to be the case. In the next section we show how non-experts can easily and rapidly build a model by using a tool that views model building as a design task. This tool also serves as an agile front-end to powerful numerical algorithms for the quantitative expert.