9. Estimating Market Demand

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

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.

Summary

  1. Understanding Demand (00:04)

    The first input to be calculated in our model is the demand for each flight. The demand is simply the number of tickets that the market wants to purchase for a particular flight. 

    If the number of tickets purchased is below capacity, then demand is equal to the number of tickets bought. However, for a fully booked flight, demand is higher than the number of bookings, as there are some people who wanted to buy a ticket, but were unable to do so. Therefore, for full flights we need to create a formula to estimate the demand for the flight.

  2. Calculating the Conversion Rate (01:13)

    Our data set tells us the number of people who searched for a price quote for each flight. We can use this to calculate the conversion rate, that is the percentage of people who searched for a particular flight and then purchased a ticket for that flight. We can calculate the conversion rate correctly for all flights that were not fully booked. We can then use this rate to estimate the demand for fully booked flights.

    In our data set in Excel, we create a new column to calculate the conversion rate. We use an IF statement to identify flights that were not full, then calculate the conversion rate for those flights by dividing total bookings by total price quotes. 

    In our example, we find that the conversion rate is not constant across flights. This means we cannot assume a constant conversion rate when calculating demand. Instead, we take the average conversion rate as a representative value, and add the conversion rate to our control panel as a variable assumption.

  3. Calculating Demand (04:22)

    Once we have established the conversion rate, we can calculate the demand for each flight in the data set. We use an IF statement to test if the flight is full. If it’s not full, then demand is equal to the number of bookings completed. If it is full, demand equals the number of price quotes multiplied by the conversion rate.

Transcript

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.

Excel Excel for Business Analytics Learning Plan
Modeling for Business
Build Your First Model