All right, what we're going to be doing in this session, we talked about some of the issues involved in designing extended warranty plans or service plans, very common when we're looking at electronics, but there are a couple of different features that might be available. So what we're going to do is we're going to build out a worksheet that lets us look at a couple of different options that might be part of those service plans. So the first one that we're going to look at on the first tab, option 1, is going to be you pay a premium for a year's worth of coverage. The second option is going to be that you pay a premium and there's a deductible that each time that you encounter service, or that you need something to be repaired. So we're going to start with just paying your premium then we're adding in a deductible each time that service is performed. The final option that we're going to impose a limit on the number of services that you can have performed. So we're going to put a quota on the warranty coverage. Common threads that you're going to see throughout all of these, we're going to use the Poisson distribution to characterize how likely we are to see a given number of service or a product failures over a one year period. We're going to make the assumption that if you don't purchase coverage, that you could go to a third-party company. That is, if I don't buy my warranty coverage for my iPhone and I drop it and smash the glass, that I could get it repaired but it may cost me more to do that. We're going to make the assumption that the company selling the warranty coverage is able to save money compared to what a consumer would pay for the third party. All right, so let's begin with option one, and the instructions that we're going to follow generally are up on the course website in the Word document. So you'll see that the way that the sheet has been structured, we have some constraints that we face, those are in red. Lambda is going to be the rate of service failure. Think of it as the average number of incidents that are going to occur over a one-year period. That's something that we are going to assume is fixed. We're going to assume the cost of getting it serviced by a third party is $100. And if a retailer were to offer a warranty plan that they'd be able to make those repairs at a fraction of the cost. What we as the company selling the warranty coverage get to control, we get to set the price for one year of warranty coverage, we can also look at doing a two-year plan, what would that warranty coverage have to look like? The output that we're going to calculate, what is the profit? How much it costs the consumer without a warranty? How much it costs the consumer with a warranty? And we can do the same for the two-year. So let's start with one year, all right? The way that we're going to characterize the number of service incidents. We're going to use the Poisson distribution, let me see it, = Poisson.dist. And what it's asking us for is, What x is? In our case is the number of incidents, so how likely are we to have a particular number of incidents? So that's going to be how likely are we to have zero incidents? What's the average number of incidents per year? And make that an absolute reference, and do we want the cumulative or do we want the probability of mass function? We want to know the exact, we want the probability associated with an exact number of failures, so we're going to put that as false, get the exact match there, all right? And so for probability of 0 failures is 60%, just over 60%. Let's copy and drag this formula down, all right? So the majority of service failures, 0, 30% chance of 1. 7.5% chance of 2, and so forth. So that's the probability of having a given number of failures, and that's going to be the same, with or without the service contract that we have. Now what's it going to cost the consumer? All right, well, we know how likely we're to have zero failures, well, what does it cost us if we have zero failures? What is the cost if we have one failure without a contract? Well,our total cost would be how many failures do we have, multiplied by what does it cost me every time I need to get it repaired. And that's what's going to be reflected by that third party cost,again, absolute reference. So if I have no failures, it doesn't cost me a dime. If I have one failure, it costs me $100. If I have two failures, it's going to cost me $200, and so forth. Now, if we want to look at the average number or the average cost, the expected cost of fixing my device without a contract. Well, 60% of the time, it costs me 0. 30% of the time, it costs me $100. 7.5% of the time, it costs me $200, and so forth. But what we're going to do in calculating the expected cost to this consumer without a warranty is we're going to put in the sum product command. So were going to use the sum product. And what that asks us for is the columns that we want to multiply together. So I want to multiply the probability of failure, I'm going to hold Ctrl + Shift down to go down to the end of this column. Let's multiply that by the respective costs. So it's multiplying each column of the first two rows together. Each column of the second row together. Each column of the third row together. And then it's adding up all of those products, all right? And so our expected cost to the consumer if they don't get the warranty is $50, all right? Which means anything more than $50, and the risk neutral consumer is going to say that it's not worth it to them. All right, so we know how much it costs to consumer if they do not purchase a warranty or how much it's expected to cost them if they don't purchase the warranty. What if they do purchase a warranty? Well, same distribution for the number of failures that they're going to encounter. So we'll use the Poisson distribution here again. So the number of service incidents, the average, because owning the product or owning the warranty coverage is it going to change that. All right, so we still have the same likelihood of service failures. All right, so that's what's in column F. Now we've gotta calculate how much money does, if I only have one service failure during the year, how much revenue does the retailer bring in if I purchase the warranty, all right? Well he makes money if I purchase the warranty, that's the revenue that he gets. What does it cost him if I have zero failures? It's $100, so it's the number of failures that I have multiplied by the cost. Now the cost is at a discount, so it'd be the $100 multiplied by how much the retailer saves. And then both of those cases. Let's go back and put in, absolute references. And then lets calculate out the profit, it's going to be my revenue less the cost. Right. So we know retailer brings in money, when he sales me the warranty. He has the class base on number of the incident the occur. And then, what's his overall profit. So let's copy those formulas all the way down this sheet, okay? And so in this case, if there are no service failure and I sell it for $50, the retailer makes $50. And that's going to happen 60% of the time. If I have one service incident, retailer makes $50 in revenue, but costs him $50 to do the repairs, now he's down to break even. Anything beyond that, it's costing the retailer money. And so, the way that this plan is working, you're making all your money Off of the people that do not have any service failure. So the 60% of cases where you don't need the warrantee coverage, that's where you're making your money. You're breaking even on the people, that need one repair. And then for everyone else it's costing you, but that only amounts to. 9% of consumers. Now we've already calculated up top here, what's the cost of warranty coverage using the, for the consumer, and what's the retailer's profit. So in this case, to get the retailer profit, we're just multiplying, likelihood of a different number of service incidences being required. By the profit under each one. Retailers making, or expecting to make $25. In this case it's break even for the consumer. Without a warranty, it costs me $50. With a warranty, it costs me $50. Well, what if we change this? What if we made it $45? We change it out to $45. The retailer makes a little bit less money there, but consumers more willing to adopt, to purchase this. And so that's the tradeoff. The higher price that I charge, the more money that the retailer's expecting to make from offering this benefit, but not as many consumers. Are likely to pick that up. If we drop this even further, go down to $30. All right, well, $50 versus $30 for the warranty coverage, good from the standpoint of the consumer. For the retailer, not making a whole lot when you're offering this. And so yes, you might get more people to purchase it, but at the same time, you're not making a lot for each of them, all right. So you an change that out,and this is looking out providing service for one year, all right. Now we can go through this same calculation, if we wanted to do two years worth of service. So the no contract for two years. We're still going to use the plus-on.disk. But the main difference is the number of failures. That rate that we gave, the 0.5, that was only assuming over the course of one year. Over the course of two years, well they're going to expect twice as many. So it'd be two times the value for lambda, and we're still going to look at the exact value, the cost to the consumer? Well, this is it's going to be the number of incidents multiplied by Third party costs. Same idea now, we'll copy these two formulas down. Now notice, now we've got less likelihood of zero failures, more likely to have one or two failures. So that's our cost over a two year period. And let's do the same idea. I'm going to copy the formula that's currently in L9, we're going to paste it into P9. And say, alright, that's giving us the likelihood of zero failures with the contract. How much money does the retailer pull in, if you purchase the contract? Now for a two year contract, well we have to purchase both the first year and the second year. So It's going to be E2 plus, and let's make sure to put our absolute references. But it's going to be E2 + E3. That's what the revenue, that gets pulled in. The cost? Well, the same as what we did for one. It was the number of failures multiplied by how much does it cost each time to do the repairs. So B3 multiplied by the fraction, since they don't pay the full price. And what's the retailer's profit, revenue minus the cost. So not that much changing here. For the number of failures that we have, we change the rate because that was an annual rate. We've got to take that into account, and on the revenue side, we added the additional cost for the second year. Now in this case, price at $50 for, if for a one year, $50 for two years were positive for 01 or two incidents. Or is non negative for 01 or 2 incidents. Were positive for 01, we're breakeven at 2, we lose money on any more than that. And so, we're positive for 73%, almost three quarters we're positive on. When we count the amount that we're break even on, we're fine for almost 92%. But on 8% of the contract sold, that's where we're expecting to lose money. If we look at the output for the two years, $50 each year is our breakeven. We can change maybe the first year's priced at 50, the second year if you want to incentivize people to sign up for it. What if we did that at 25, all right? More people signing up for it, but let's looking at the profit one year. Retailers making $25 on each of these contracts. The difference is, for the consumer, $50 for a one year contract, versus $50 is my cost without the one year contract, yeah, doesn't really matter to me, versus the two year deal. Cost me $100 to repair my device without the warranty, it only cost me $75 with the warranty. So we can play around with the pricing here. Now, that's only assuming that I charge a premium, we haven't added in the deductible, we have not added in the quota. So, this is one option that's available in designing the plan. What's the next option? Well let's look at what's going to happen for option tw,o where we add in the deductible. And what I'm going to do is while we work through this one, I'm only going to focus on looking at the one year contract. And I'll leave as an exercise for you to work through, what the differences that you would have to make for the two year contract. So the no-contract option, it's going to look exactly like what we had before, so the number of failures, we're using the plus-on distribution. Number of incidents, what's the value for Lambda? And the false statements, then we want the exact value. We'll copy that equation down. The cost, it's the number of incidents multiplied by third party cost. And we copy that formula down, so if we look at, in column H, column H3, cell H3, expected cost to the consumer without the warranty still costs $50. All right, now let's look at designing something with a deductible. Well, the deductible is not going to change the service failures that we have. So we're still using our poisson distribution there. Number of incidents, the rate of the service failures. And false, right. What's the revenue that the retailer pulls in? Right, well, the revenue he's pulling in, we're going to pull in the premium for that one year of coverage. But we're also going to pull in deductible amount each time that service is required. So, each time service is required, so for each incident, I'm pulling in however much that deductible amount is. Cost to the retailer? That's going to be the number of incidents multiplied by per incident cost, multiplied by their savings rate, and the retailer's profit. Get difference between the revenue and cost. And so if we take this and copy it down, All right, so let's take a look, At what's going on with the retailer. So, we've put in different prices here. If I charge a $25 premium and a $25 deductible per incident, the retailer is making money when there are the zero failures, break even when there's one failure, losing money on the rest. And so we're using the sum product command to understand his profit. In terms of understanding cost to the consumer, we're looking at, again, the sum product, likelihood of number of service incidences and the costs. And then in terms of the cost to the consumer with the warranty, well, it's the money that they're paying the retailer. All right, so we're looking at column G, that's the money going to the retailer from the consumer, and column F. And so we can change these prices here. It's squeezing the profit a little bit on the retailer, but it's giving the consumer a clear benefit. So by offering this deductible, I'm able to lower the sticker price. We could go to a lower deductible. Let's say all right, our pricing's going to be $50, $50 and, So this is essentially what we had before, $50 per year for coverage and zero as a deductible. If I want to lower that upfront cost to the consumer, well, without taking too much away from profit, all right, that's one way for us to go about doing it. Is to say, all right, I'm going to charge a lower amount up front, but each time there's a service incident you need to kick in a little bit. So my profit's higher than not having a deductible, and so that may motivate consumers a little bit to say, the cost, the upfront cost to me is not as high. So we go to 25, and 25 seems to provide profit for the retail or to offer it and savings for the consumer, compared to what they could expect to pay on their own. I'll leave the two-year exercise to you, we're going to fill in failures the same way, costs the same way we did that with option one. Difference is again going to come in in calculating the retailer revenue, and then that's going to trickle through to their profits. We can see, when we get the additional option of price for year one, price for year two of coverage, and the deductible amount, gives us a bit more flexibility to be able to lower that initial price point. All right so, that's looking at option two. Let's take a look at one more option. So we've gone from just having a premium to charging the deductible for each service incident. Now, let's look at one more option. Let's look at adding in a quota. That is, there are only a certain number of incidents that are covered. So if you're horrible to your devices and you constantly break them, not all of those instances are going to be covered. It's a way of Protecting the service provider from people who are being particularly abusive to their devices and taking advantage of that warranty coverages. We'll cover the number of incidents that a majority of people are going to encounter, but if you exceed that limit, you're going to be on your own. All right, so let's take a look at option three. Now, you'll notice in the green section, warranty for one year, warranty coverage for two years, the price of the deductible, and the quota. The No contract section has not changed at all. So let's fill that in the same way that we've done before. The number of failures. We're going to use this Poisson distribution, so number of incidents, the average rate of occurrence, the lambda. And we want the probability mass function, so that's the false statement. The cost is number of incidents multiplied by what it would cost you to go to a third party. All right so, the no contract option is exactly what we've done for the previous two options. Let's go over to, what if you did get the contract. So probability of failure, that's going to be the same, and I'm going to copy cell B9 and save myself the trouble of typing it again. Let's copy cell B9 into cell F9, all right. So now it's pulling from E9, referencing the appropriate rate, and let's copy that down, right? Now, is the incident covered? All right, well, we're only going to cover the number of incidents less than or equal to our quota. So if this incident number, Is, Less than Use less than or equal to my quota. Then we're going to put in that number of incidents. And if it's not, we're going to put in the quota. All right, so let's take a look at this command. So, how many incidents are ultimately going to be covered? If you had no incidents, no incidents is fine. If you have one incident, that's fine. If you have two incidents, that's covered. Anything more than that, and not all of the incidents are going to be covered. Now, the other way of doing this would be to use the minimal commands, so let me show you what that looks briefly. And so, it's the minimum of the number of incidents that you encounter and the quota. We're going to retrieve which ever value is smaller. Notice that doesn't change things. All right. Now, what's the retailer pulling in as far as revenues? Well, my warranty price, that gets paid. What else comes in? For each covered incident, we get the deductible, because we don't get the deductible for incidents that are not covered. So, we have the price for the warranty, we have the deductible for each covered incidents. Because once it gets beyond two incidents in this particular case, once you get beyond the quota, the retailer doesn't pull in that revenue anymore. Cost to the retailer, it's the numbered of covered incidents multiplied by the price they pay per incident. So, we're going to multiply it by the third-party prize, multiply it by the retailer saving. So, that's the cost, or profit for the retailer. This minus this. Now, the total cost that the consumer phases. All right. Well, we need to take into account what they're going to be paying the retailer. Plus, how much it's going to cost them going to the third-party when necessary. All right, so, it's going to be the money that they paid the retailer, plus, and let's type this out first, we're going to use the minimum. All right, so let me walk through the logic of this command. So, what is the cost to the consumer? It costs how much you pay the retailer, that's your insurance price, or the warranty price, plus the deductible for the covered incidents for all incidents that are not covered. So, for incidents above the quota, you're going to have to pay the third-party price. When we change that third-party price to our appropriate cell reference, Right, so G9, that's the number of covered incidents you have minus the quota. So, whenever G9 is. Whenever the number of incidents that you have, actually, that should be E9. All right, so let's walk through this logic. All right, so how many, there's what I paid for the coverage with the deductible, and then the difference between how many incidences do I actually have and the quota. So, whenever this number is positive. So, we actually need to be using the max. All right, so, you can see constructing these commands gets a little bit tricky. But this looks like it's going to work for us. There's the amount that we paid the retailer, then there's the amount that I'm paying third-party support when I exceed my quota. So, E9 minus E5 whenever that is positive. I'm going to pay for that many uncovered incidences. Now, we can restructure this a number of ways. If this looks a little bit convoluted, let me just copy this formula down first, and we'll take a look at it. All right, so notice, it cost me $25 If I have no instances, that's because that's my warrantee. Just for the purchase of the warrantee. If I have one incident, it costs me $50. That's my purchase price plus $25 deductible. Same thing when I have two instances, purchase price plus two deductibles. For anything above and beyond that, it's costing me $100 more. All right, that's because I've got to go out to a third-party. Now, let's use a command that makes a little bit more sense. I'm going to replace this MAX statement, so I'm going to leave everything else, I just want to rewrite the way that we calculate the number of uncovered incidences. It's going to be incurred incidences minus the covered incidences. And that's it. All right, so let's just copy that formula down and talk through that one. So, H9, that's what you pay the retailer, that's what you pay for the warranty through the retailer, including your deductible. E9 minus G9 is how many service failures actually happen minus the number of covered failures. So, that's the uncovered number of failures multiplied by the third-party cost. And so, if we take a look at the retailer profit, what we're doing with this quota is we are essentially, the retailer is essentially protecting himself. I make money on the people who don't require any service, don't incur any service failures. I'll break even on the people who have one, and I lose a little bit of money on the people who have two failures or more. But remember, before, that number kept on climbing based on the number of service failures that a consumer incurred, it would go up, because the retailer is going out of pocket to get those repairs done. Now, we're capping the downside, for the retailer, that cost being pushed off to the consumer. And if we look at our output here, it would cost us $50 if we do not get the warranty, that's our expected cost. Expected cost to the consumer, with the warranty and the quota Is $38. Expected profit for the retailer, just shy of $13. Let's look at what happens as we slowly increment up the quota. So, right now we're at two, what if I change it to three? All right, retailer makes a little bit less money going back to two, little bit lower costs for the consumer, right? If I go up to five. Retailer makes little bit less. Cost to the consumer gets a little bit lower. As we update that, as we make that quota higher and higher, it's going to look more like option two. What if we made it as we'll give you one incident covered? All right, well, now we've capped the downside for the retailer completely. I make money off of the people who have no service failures. One service failure, I'll break even, and I don't cover anything else, so you're on your own as a consumer. But it limits the benefits of the consumer. Notice that the cost to the consumer has jumped. So, we go to covering two incidences, take a little bit more profit, but it lowers the cost of consumer making this more enticing to them, right? So, we've done everything here for one year of output, and I'll leave it to you to kind of fill in what the no contract and the contract looks like with two years of service using the same logic. So, the no contract for two years looks like what we did with option one. The contract for two years, we've gotta fill in that failure rate. We look at the number of covered incidents that occur, and we've got to calculate out both the retailer profit and the consumer costs. And we will have completed versions of these spreadsheets posted up on to the course website. So, here what we're doing is recognizing the uncertainty, how can we shift the burden between the retailer who sells the warranty coverage incurring that cost, versus the consumer incurring that cost? The ideal want that profits to be high for the retailer selling it, but we've also gotta make purchasing the warranty worthwhile for the individual consumer.