What are Lookup Tables and how are they used in Simulation Models?

Introduction

In our previous post, we introduced the three basic elements of a Silico simulation model: stocks, flows and variables. In this blog, we’re introducing another kind of element used in Silico, Lookup Tables. 

What are Lookup Tables?

Lookup Tables are a way of specifying the relationship between two variables in a custom function. Sometimes, relationships between variables can be quite complex. For example, imagine you want your model to capture how the price of a good affects its demand. Or you might want to model how a change in a company's workload will affect its attrition rate. These relationships are highly "nonlinear", meaning that a change in the affecting variable (say, price) will result in a disproportionate change in the affected variable (say, demand). Lookup Tables capture these complex relationships without the need to create complex equations. As their name suggests, they simply "look up "the value of an outcome variable in a table that maps the values of the input variable to the outcome variable.

Why does Silico use Lookup Tables?

Let’s ground this in an example, by continuing our model of the drinks company from our previous 'Core Simulation Elements' blog. 

In that model, we made projections about how our total units sold over the next year will be determined by the number of stores that we sell our products in and the rate of sale within those stores (i.e. the Drinks sold per store per week). 

‍

So far, we have simply assumed a rate of sale of 10 drinks per store per week, and we haven’t included any factors that might impact this or change it over time. To make our model more realistic, we can introduce at least one factor that is likely to affect the rate of sale: price. 

Consumers are highly price-sensitive, especially for consumables like soft drinks. If we increase our price, it is likely to result in a lower rate of sale. At the same time, there might be thresholds within this relationship. If our competitor's retail price is currently €2 per can, and we increase our price from €2 to €2.10, this will likely have a much bigger impact on the rate of sale than if we increased it from €2.4 to €2.5. This means that the relationship between Price and rate of sale is highly nonlinear. As such, instead of representing how price impacts the rate of sale through writing a typical equation, we can better represent it by using a Lookup Table. We can use the Lookup Table to specify the rate of sale (i.e. drinks sold per store per week) for a given price.

Specifying the Input Variable

First, we need to introduce the retail price as a variable in our model, as this will be the input used by our Lookup Table. For simplicity, let’s say that our drinks company is able to determine the retail price of our drinks in different stores (in reality, most drinks companies would actually determine the wholesale price that their distributors pay. The distributors would then sell on with a markup to retailers, who would then sell to consumers after another markup - but we can leave this detail out of the model for now). Let’s say that at the moment, we set the retail price of our drinks at €2.00. Let’s set this as the value of this new variable. 

‍

Adding a Lookup Table

Now we can introduce a Lookup Table of how the Retail price will impact the Drinks sold per store per week (i.e. the rate of sale, or ROS). Let’s call this “Price vs ROS” because it will generate a certain ROS for a given price. 

The next step is to fill in the Lookup Table to specify the rate of sale will be a given Price. This can be based on past data or assumptions guided by some research/common sense. Of course, we likely won’t have perfect data for determining the relationship between Price and rate of sale. But that doesn’t mean we shouldn’t model it - excluding it from the model is the equivalent of saying that there is no such relationship. We know that isn’t the case, so our model will be improved somewhat by including at least some effect, as long as it is realistic. 

Now that we have our Lookup Table, the next step is to fill it in. Here’s how we do just that: 

How to Use Lookup Tables

Step 1: Set the “Start” and “End” values. These specify the range of the input variable (i.e. the variable that is affecting others) for which we will be setting outputs. If the input exceeds the defined range, the last defined value will be used. If it's below, the first defined value is used.

The x-axis of the chart will automatically update to the range that we set. In this case, Retail price is the input variable affecting the rate of sale, and the two values indicate the possible range for the price. Let’s say that the lowest price we would consider charging is €1.50, and the highest price we would consider charging is €4.00. In that case, we would set 1.5 as the “Start” value, and 4 as the “End” value. 

Next, we can determine the “Min” and “Max” values. These relate to the minimum and maximum output values. In this case, we are using this Lookup Table to determine how Retail price (the input) will affect the rate of sale (the output). 

“Min” and “Max” will relate to the minimum and maximum rate of sale. What might these be? Well, common sense tells us that if we charge a high enough price, no one will buy our drink. Consequently, the minimum should be set to zero because a rate of sale of zero is a possibility. We also presume that no consumer will buy our drink when we charge at or above €4 per can. 

And what about the maximum rate of sale? We might charge a very low price, but even if we gave it away for free, we would still likely sell only a certain amount per week. Let’s say that we assume, based on research on the number of potential customers we have in the market and their rate of consumption of soft drinks, that the maximum rate of sale would be 30 drinks per store per week. Let’s say that this will be the rate of sale when the can is given away for free, i.e. when the price is €0 per can. So let’s enter 30 as the Max value. 

Having set the “Start” and “End” values of the input variable, as well as the “Min” and “Max” values of the output, the next step is to determine the number of Steps. This determines the number of prices we can set values within the specified range of 1.5 to 4. If we set it to 1, it means we only have to set a rate of sale value for 1.5 and another for 4. We already have estimates for those prices, so let’s start there. We can easily enter these in the “Data” tab of the Lookup Table. This allows us to specify what value the Lookup Table will return for a given input value. They map values of the affecting input variable to the Lookup Table's outcomes

With just two such values entered manually, Silico will then determine the rate of sale for all intermediate prices based on a straight line, i.e. linear interpolation. 

Next, we can start to make use of the Lookup Table. To do that, we connect the Price and the Lookup Table to the variable “Drinks sold per store per week”. We then change the formula for the “Drinks sold per store per week” by removing 10 (it is no longer a constant), selecting the Lookup Table, and then choosing Price as the variable within the brackets. That is essentially telling the software that Retail price is the input variable to this Lookup Function. So the “Drinks per store per week” variable will now be determined by the Lookup Table and the Retail price variable.

Because we set our “Start” value to 1.5, any price below that will have the same value as the value we set for 1.5 (in this case, 30). So a price of €1 or €0 would still return a rate of sale of 30 drinks per store per week. Similarly, because we’ve set our End value as 4, any price above that will have the same value what we set for 4 (0, in this case). So a price of €5 or €6 would still return a rate of sale of 0 drinks per store per week. As mentioned, the output for any price between 1.5 and 4 will be determined by a straight line automatically drawn between the points of 1.5 on the axis and 30 on the y-axis, and the point of 4 on the x-axis and 0 on the y-axis. Let’s see how playing around with the Retail price variable will now change the “Drinks sold per store per week” variable. 

As we see, any change in price between 1.5 and 4 will change the rate of sale - higher prices result in lower rates of sale (which in turn leads to lower total sales, all else being equal). Any changes below 1.5 or above 4 don’t have any effect, because we’ve already reached our maximum rate of sale (of 30) with a price of 1.5, and we’ve already reached our minimum rate of sale (of 0) for a price of 4. 

However, as we’ve said before, the relationship between price and rate of sale is not likely to be linear, and there might be certain thresholds in the relationship, based on things like competitor pricing and psychological barriers such as paying over a certain whole number (hence why we see €1.95 as a price more often than €2.00!) As such, we don’t want to assume a straight downward line between a price of €1.5 and a price of €4. Instead, we might want to set a specific rate of sale for, say, every increase of €0.10 in price from €1.5 to €4. There are 25 such increases between 1.5 and 4, and so we should set the number of Steps to 25:

Now, we can set a specific output value for each price in increments of €0.10. Let’s say that we know that with our current price of €2, we have a rate of sale of 10. If our competitors also charge €2 per can, then we might assume (or have evidence) that an increase to €2.10 might have a pretty significant effect, so let’s say the rate of sale would drop from 10 to 8 in such a case. From there on in, however, every €0.10 increase in price might result in a reduction of just 1 in terms of rate of sale, because the psychological effect on the consumer isn’t as big. By the time we get to the higher prices, our product will be firmly in the premium space, and an increase of €0.10 might only reduce the rate of sale by 0.5 and eventually 0.1 units per week, instead of 1. 

On the flip side, reducing the Retail price from €2 to €1.90 might have a particularly positive effect on the rate of sale, due to the psychological barrier of the €2 mark, as well as the fact that we now would charge less than our competitors. So let’s say that the rate of sale will increase from 10 to 18 when we change our price from €2 to €1.90. Any further reductions in price are likely to have a less dramatic effect, so we can say from there on in that a reduction of €0.10 will increase the rate of sale by just 4 units and eventually 2 units per week. We have now represented a truly nonlinear relationship between Price and ROS: 

We’ve now completed our Lookup Table structure in the model. This new structure allows us to test how a change in the price will impact not only demand, but also revenues (and ultimately profits). As we can see, a higher price means less units sold. However, it also means higher revenues per unit sold. As such, if higher profits is the primary goal of our drinks business, then there is a sweet-spot price to be found, where revenue per unit is high but without affecting volume of sales too much. 

Finding this sweet spot is something we can do in our Silico’s Scenarios blog!

If you prefer learning in video format, why not check out our video on YouTube: