Sign in or start a free trial to avail of this feature.
9. Estimating Market Demand
Estimating demand is a required task in almost all models. Here I show you one way of doing this for Zippy Airways, with some caveats attached.
- Demand is very difficult to estimate accurately
- Often, you will have to make an assumption and then run a sensitivity on this assumption
- Don't be hesitant to leave the spreadsheet and draw out complicated demand formulas
AVERAGE( array ): Calculates average of array
STDEV.S( sample ): Calculates the standard deviation of a sample of data points
ALT + I , C: Insert new column
CTRL + SHIFT + 5: Convert cell format to percentage
ALT + H , 9: Remove a digit after decimal place
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
CTRL + →: Move to the last cell in the data region
SHIFT + →: Select next cell
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
On the right-hand side of our data set, we have the inputs to our model which will vary by flight. No-shows are already included in the raw data set and so the next input we must calculate is demand. Calculating demand is required by most models, but the method or formula for doing so varies according to the situation. In the case of Zippy Airways, let's first ask ourselves, what does demand actually mean? To my mind, demand is simply the number of tickets that the market wants to purchase for a particular flight. On flights that are not fully booked, say with 168 passengers, everyone that wants a ticket can get one because there is no capacity constraint. Therefore, demand is simply equal to the number of tickets sold. However, if a flight is full, demand may be equal to the 180 seats which are sold or it could even be higher because some people, they want to buy a ticket after the flight is fully booked. So for fully booked flights, the number of bookings is not equal to demand, instead, we have to make an estimate. So how do we do this? Well, let's start by returning to the data set and seeing what additional information we can use. The only other column of data related to demand or customer activity is the number of price quotes given, which Zippy defines as a number of people who visited their website and selected that particular flight. For flights that were not full, we have both, the demand and the number of price quotes. The formula for demand, in this case, is the number of price quotes multiplied by the conversion rate. If we could assume the conversion rate is constant across all flights, then we could calculate demand for the fully booked flights based on the number of price quotes given and this constant. So can our conversion rate be assumed constant? Well, let's return to our dataset and find out. I'll start by creating a new column, with Alt + I, C and I'll call it conversion rate. And the formula for the conversion rate will be as follows. We'll start with an IF statement and the logical test will be, if the number of bookings completed is less than the capacity, which is 180. And if it is, we'll calculate the conversion rate by dividing the number of bookings completed, by the number of price quotes given. But if the flight is full, we'll just return an empty string because you want this cell to remain blank. I'll then close the bracket and press Enter. I'll then copy this formula for the remaining cells. I am going to convert the form out of these cells to percentage. So I'll press Ctrl + Shift + 5. And now you can see that for each flight that isn't fully booked, we have a conversion rate. But unfortunately, it doesn't look like a constant. Let's quantify this quickly, by calculating the average and the standard deviation of this particular column. So to calculate the average, we write the equation, equals average, open a bracket and select all the values. When we close the bracket we can see the average is 17% percent. For standard deviation, we'll select the formula for a sample because we only have a couple of values. Again, we'll select all of the columns, with Ctrl + Shift + down arrow, close the bracket and press Enter. And if I convert this to percentage, you can see that our standard deviation is quite a large number when compared to the average, which confirms that we won't be able to assume a constant for the conversion rate. In truth, this is not a surprising answer. Whenever you have to estimate demand in your model, it should never be as simple as one equation. In this case, we'll simply assume a value for our conversion rate, probably 17% percent which is the average and then re-run the model later in a sensitivity table for different conversion rates. I'm going to include the conversion rate assumption in the control panel and to save some time, I'll do this off camera. I'll also name this cell, conversion rate. We're now ready to calculate our formula for demand in the dataset. So we'll go to the demand column, and we'll write an IF statement, which will test if the number of bookings completed is less than capacity. And if it is, we'll just return the bookings completed. And if it isn't, we�ll multiply the conversion rate by the number of price quotes which will give us demand for the fully booked flights. We'll then press Enter to complete. And then I'll copy for the remaining cells in this column. And I'll fix the formatting by removing the decimal places with Alt + H, 9. There we go. And our estimate of demand is completed. Hopefully, this lesson has shown you that modeling can get quite complicated, quite quickly. And it's always better to move in small stages especially at the start so you don't get mired in the detail too soon in the modeling process. That's why building the single flight model initially was important for us to help understand the overall model structure without having to deal with the difficulty of estimating demand and other parameters such as unit bumping cost, which we'll now focus on for the next two to three lessons.