All right, what we're going to be looking at in this exercise focusing on acquisition and retention behavior, both of which are components of customer lifetime value and customer equity. And so the exercise that we've been presented with is one in which we're looking at David's Revolutionary Atlanta Telecom. So DRAT, offering high speed internet services. And company's research has identified three segments of consumers in the Atlanta market that it serves, and so we've got three different segments. And we have the relative sizes of those segments, so the techs-savvy singles, the dual income no kids, and the professional with children, each of a different size. And so we know those proportions, we have an estimate for the size of the market. Let's assume that it's a million people that we can reach. And the tables presented in the instruction sheet that's up on the course website, we have the acquisition probability as well as the churn probability. And what you'll see is for the more I'm willing to invest, the higher the acquisition probability, up to a point in some cases. So that's the first table, in the instructions. First, when it comes to churn, the more I'm willing to invest in customers, the lower my turn rates, the higher my retention rates. Again, up to a point. And so what we'd like to be able to do is build a tool that allows us to see by segment and as well as overall. What's profitability look like? How many customers do we expect to get? What's profitability by segment? And so those are the metrics that we're going to be summarizing in this particular sheet, in this blue region. In the pink region, that's where we're going to make our decisions. So the pink region we're going to specify how much we're going to be spending on different customers, different customer segments. So can we specify the acquisition spending by segment, can we specify the retention spending by segment? And then what we're going to do all of our work we're going to do on the CLV analysis tab. So let's take a look at how that's set up. Based on the amount that is being spent for acquisition and retention by segment, we'll calculate out survival probability or you can think of this as the retention probability, which is directly tied to discounted expected revenue by segment. The final tab in the worksheet that we have, these are the same tables just reproduced here and we're going to use these as reference tables. All right, so let's turn to the second page of the instructions for this particular exercise labeled as our walkthrough. All right, so we're going to go back to our financial calculation sheet. And since we're dealing with three different market segments what we want to do is let's make sure that our math is correct. And that somebody who is using this cover sheet doesn't make a mistake and enter segment sizes that exceed 100%. So in cell B7, we're going to just use an if statement. So if the sum of the percentages in B4 through B6 equals one, we're going to say we're okay. And otherwise, we're just going to put in a message that says Market Size Must Equal 1. All right, so that's just to check things. And so you see if I change a segment size to 45%, I get that error message. All right, and so let's take that back down to the 40%, and we're okay. All right, and so in the red region we want to specify what our level of spending is going to be. Okay, and so we're going to use drop down menus here. So this is designed so that people can't enter values that are not permitted. So we're going to click on the data ribbon, and we're going to use data validation. And instead of allowing any value, we're going to choose list. I'm saying we're only going to allow a value that falls on a particular list, and where is that list? Well, that's our reference table. So for acquisition spending, it's the reference table A3 through A7. And so you see, in that particular cell when it's highlighted, we see a drop down menu and that's going to allow me to specify particular values. And if I just drag that down to the other cells, it's also going to drag down that data validation. And so I can change my acquisition spending per segment, To any of the values on that list. Same idea for the retention spending. So in column C, we're going to click on, for C10 I'm going to click on the data ribbon, data validation, and again I'm only going to allow values on a list. And the source of that list is going to be the reference tables and it's going to be A11 through A15. So those are the only values permitted. And let's drag that down from C10 into C11 and C12 and let's just check that to make sure that that formatting has carried over. And so in this particular example, we have really six decision variables. Acquisition and retention spending, so two decision variables for each of three segments that we're choosing. All right, so now we've got to go and figure out how much are each of these segments ultimately worth to us? So we're going to perform our CLV analysis. And then we're going to use that information that we put into the CLV analysis tab and we're going to come back and fill in this blue region on the financial calculation sheet. All right so let's move to the CLV analysis tab. Well, first and foremost let's retrieve the acquisition and retention probabilities based on the acquisition and retention spending that was specified on the the financial calculation tab. So we're going to use a Vlook-up reference command. At the value that we want to look up is on our financial calculation sheet. I want to look up the amount of acquisition spending. So I want to look up acquisition spending for the tech-savvy single segment. We're going to look that up in the reference tables. And we're going to highlight the region from A3 through D7. We're going to put in absolute reference on that table. We're going to tell it to pull the value in the second column. So find the acquisition spending amount, pull the second column, is how much spending for tech-savvy singles and we're only allowing exact matches. All right, so we got the acquisition probability for the text having single segment. We need to do same thing for the other two segments. So again use our v lookup command. Now we could try to drag that formula down, and we'll do that next. But again let's be sure that we're mapping onto the appropriate reference table. So in this case, same table we can highlight, with our absolute reference. But remember, now we're looking for how much you spent on that second segment, the. So we're going to pull from the third column of the table that we just highlighted. All right, and then if we wanted to drag that formula down, that's fine, but let's be sure that we pull from now the fourth column, all right. And so we now have our acquisition probabilities mapped appropriately, we're going to do the same thing when it comes to retention probability. We're going to use our reference table, but instead of just pulling, keep in mind that what was reported in the handout as well as in our tables, these are churn probabilities. And so we're going to take 1- the churn probability to get the retention probability, all right. So again we'll do 1- our v lookup of the churn probability. So for the tech savvy single that's 1- v lookup retention spending is C 10. going to look that up in the table, we'll put in absolute reference, and we're going to pull from column 2. So we have the retention probability there, and we'll fix the formatting in a second since we're not really pulling dollar amounts anymore. We're going to drag that formula down, but for the DINKS let's change that so that it references the third column of the table. And for the professionals with children, make that the fourth column of the table. And let's just clear up that formatting. All right. So these numbers will change based on how much we're spending on acquisition and retention for each of the segments. All right, so we've got our acquisition and retention prob. Next thing that we're going to set up is, how likely is it that customers stick around for a particular length of time. All right, and so just following the walk-through that we have in cell B9, we're going to enter is B8 multiplied by the retention probability for the corresponding segment, okay. And that's a formula that we're going to be able to copy all the way down to the bottom of our table, we've projected out 20 years. So let's just copy down what's in B9 to the rest of the table. Now notice what we're assuming is people stick around 100% for their first month of service. There's a 75% chance that they're around for two months of service, just over 56% chance that they're around for three months of service, and so forth according to a geometric distribution. All right, same idea for the DINKS. Its going to be how likely were you around in the previous month, multiplied by what's your retention probability. And that retention probability, we have the absolute reference. And for the professionals with children, how likely were you around in the previous month multiplied by that retention probability. And we'll copy those formulas down the rest of the table. All right, so we know how likely it is now that a customer or a subscriber sticks around for a particular length of time. Now we've gotta translate that into how much is that customer worth? So, for someone who sticks around, where each month of service I've got it discounted not by how likely it is that the customers is around, I've also got to take into account the time value of money. And so on the second page of our walkthrough, you'll see the formula that we need to put into place to calculate that discounted expected revenue less costs. So you can copy this formula that's in the walk-through for you. I'm going to recreate it in the text, or in Excel here. And so, I'm going over to our Financial Calculations, B1. That's going to be how much do we get in payments each month from customers? We've gotta subtract how much we're spending to acquire those customers. All right? And then we're going to multiply that by, Our CLV analysis, the likelihood that the customer sticks around that long. And we're going to multiply this by our discounting equation, so the time value of money. And so our discount term that we had upfront, and this is something that we could conduct our sensitivity analysis around afterward. Now that's an annual, so we've gotta take into account that, we want to convert that to a monthly amount And then how many months are we actually doing that discounting? With the ref, so when we drag this formula across, we want to make sure that we're discounting it appropriately, always referencing that first column, all right? And let's just check to make sure we've got enough parenthesis here. So, this first piece, how much do we get from the customer in the month, taking into account that acquisition cost, how likely is it that we still have this customer, And the last piece, taking into account that time value of money. And we can drag that formula across, since we've got our relative references correct. You'll notice that the pieces that change were changing where we refer to the survival probabilities. That's the only difference in this formula as we drag it across for the other two segments. All right, and we can copy this formula down our entire worksheet. Right, and so what we've done here is we're saying that we start with [COUGH], excuse me. We're starting with the payments that we're getting from the costumers, that's our revenue piece. There's the how likely it is that we still have you piece. And then there's the final piece is that time value of money component. And I have no side. Looks like well, it should not be referencing B10, I just noticed. It should be referencing not the acquisition probability. It should be capturing that retention probability, right. So the retention spend rather. And so let's correct. Since that's something that's going to change for each of our segments, let's make sure that we get that piece in there correctly. So for the DINKS, we're not going to be referencing B10, what we should actually, what we should be referencing is C11 and C12 for that final segment. All right. So, C11 for that group and then C12. And then let's copy those formulas all the way down that worksheet. All right, so now with these updated numbers, let's walk through this one more time. We have the monthly subscription fee minus the amount we're spending on keeping the customers, so that's my revenue less my cost each month for retention. How likely is it that I actually have that customer? Because I'm only incurring that retention cost if I still have that customer. And then, discounting for a time value of money, all right? And so if we were to sum within one of these columns, if I were to calculate the sum, and you can see that in Excel, for this particular segment, the over, a 20 year period, pretty close to the lifetime, given how unlikely it is that the customers are still around, this customer's worth around $90. Just shy of $90 for a customer in that particular segment. What about the DINKS segment worth $194, and the PWC segment worth $362, all right? So we've taken into account the retention spending by segments, but we also have to take into account acquisition spending by segments. And we're going to do that on the financial calculations sheet. All right? So, to complete our work, we're going to just go onto the last page of the walk through. The revenue per acquired customer, again this being the revenue less the retention cost. We're just going to sum up that discounted expected number for each of our segments. So in cell B14 we're going to sum up from the CLV analysis tab, it's E8, Through E247. For the DINKS, we're going to sum up F8 through F247. And for the PWC segment on the CLV analysis tab, that's going to be column G8 though G247. All right, so that's the revenue per expected customer. Now we've gotta take into account how many customers are we expecting that we're going to get. All right, well, expected customers, it's going to be, how big is the market? Multiplied by the size of each segment. And I'm going to keep that as a relative reference since I want to drag that formula down if possible. And I'm going to multiply this by the acquisition probabilities. Which we summarized at the top of the CLV analysis tab, all right? So the number of expected customers from a given segment, it's how big is the overall market, what fraction of the market is from a particular segment, and then what's the acquisition probability. Based on my expenditure amount on acquisition per customer for that segment. All right, and so we know how many customers we're expecting based on how much we're spending on acquisition. All right, and now we gotta calculate the segment profit. You have the formula on the last sheet of the walk-through. We're going to take our revenue per acquired customer, multiply that by the number of customers that we expect to get, and what do we have to back out from that? Well, my acquisition spending per costumer or per individual about we go after. And so in order to get that, I'm going after all of those costumers. So, total market Size multiplied by the size of the segment. Multiplied by how much are we spending on acquisition for all of those individuals. Even though we don't get them, we're still spending the money trying to acquire that. Right, so looking at our absolute and relative references, looks like B14 and B17. Those will shift as we drag this formula down. B3 stays fixed. That's our market size. B4 and B10. We want those to shift as we drag this formula down as well. So our absolute and relative references look fine. So we can copy that down. And overall profit. Let's just sum up the profit by segments. And so now we've got a tool that puts together for us how much profit do we get for each segment, and what's overall profit based on acquisition and retention spending. And so, does it makes sense, for example, for me to change my acquisition spending in the PWC segment? Should I make it higher or lower? Well, let's just look at what does it do to over our profit. Turns out that increasing acquisition spending seems to increase my profit for that particular segment. All right. Yeah if we only spend $1 on this particular segment, it doesn't seem, Yeah looks like we're taking a loss based on overall size of the segment, it turns out I've got to spend more to make that acquisition probability high enough to make going after that segment profitable. What about our retention spending? Is it worth it to increase that? Looks like going higher on the acquisition spending for the PWC segment, or on the retention spending, doesn't help us in this regard. So, doesn't pay for us to spend a lot on acquisition in this, well, on retention in this particular segment, we're better off spending the money on the acquisition side in this segment. Now what's not built onto this worksheet, and something that you could do is, well I've got a limited amount of money. You said, I can change each of these six variables but I have to stay with it in particular constraint. All right, so maybe I've got, I can't spend more than, let's say $3 million. Well, what are the appropriate levers that we should be spending? We haven't built out the cost piece here but that would be easy enough for us to break out. We can break out how much we're spending on retention per acquired customer. How much are we spending on customer acquisition? We can build in that budget. All right, so we have now a tool that allows us to say, changes to acquisition and retention spending, how do they impact profit? Using some of the techniques we've looked at, throughout the course, such as logistic regression, we could look at how do different marketing techniques impact acquisition probabilities? How do different marketing techniques impact retention probabilities? And make this tool more general, make it more suited to the contexts that you might be facing.
