Financial Modeling for Subscription Software: Cohort Analysis

1. CH0 Cohort Introduction: welcome to another class from low finance teaching practical financed entrepreneurs through modelling. My name is Win and I'm gonna be teaching cohort analysis today. This course is designed for entrepreneurs seeking to better understand and track the cohorts and their business to improve their decision making and customer acquisition and retention. Or you may just be trying to improve your projection and tracking for the purposes of talking to third parties. Whether that's a board that's requiring additional reporting from you, more sophisticated reporting you've done in the past or you're going out to V sees for a potential fundraising, and they want to understand how you think about cohorts and how those change over time. First, I'm gonna take a second to talk about what a cohort is, what you would traditionally see in a very basic model. Looking at cohorts is months stepping up, and each month you get some new amount of subscribers. You turn some subscribers, and at the end of the month you have what's left. What cohort analysis allows you to do is look at each month independently and continue to track it in future months so that you are bundling each new set of users over some set period of time and then treating them as an independent group of users going forward. What that means is, in any given month as future months occur. I know the new subscribers in that month, but I'm still tracking the churn on behavior of those users in prior months. So in Month three, I'm independently looking at months two and one. If this doesn't totally make sense yet, that's okay. The goal of this modeling exercises to really help you understand the cohorts and how they impact your financial returns and projections over time up a quick bit on how to use this course. I will model, sometimes pretty quickly. I'll introduce new formula slowly, but as it comes back up, I may not. You composite any time and look in the upper left hand corner. Here, you'll be able to see the formula that we've built on match up what you are doing with that , and I urge you to ask questions to the to the message board as well as you go post some of the work you've done and uh, with that will get started with setting up the sheet 2. CH1 Cohort Setting up the Sheet: So what we're doing with this exercise is bridging the gap between users and revenue and just adding some more complexity in how those users are arrived at and the revenues arrived at. We're going to start by adding a new tab and let's call it revenue build, because that's indicative of what we're breaking out from the assumptions here. And, um, the first thing I always do. As with other courses, if this is your 1st 1 is, bring that timeline over. If you need a refresher on how to do that or if this is your first time taking a course with me, I just dropped back to our first course. It's a very quick and simple, um, set a formula to build that timeline, and it makes life a lot easier. What's gonna be unique about how we do the timeline in this revenue build is that we're gonna add another dimension to it. So the goal with cohort analysis is that we look at buckets of users that come in every month in this case, and then look at how they individually act each month going forward. So that means in month three we're gonna want to know how the users in month two and month one are behaving separately from how those use new users in month three or behaving if you're continuing on from our last course. Or maybe you've done this on your own made some sort of averaging assumption or blanket assumption about how your users behave. So they grow net net every month where they grow and turn a certain amount every month. And you may be done the same thing with your revenue. So you have a blended revenue per user per month. What we're doing is is really unpacking those numbers and arriving at a more accurate build out by using those chords. So the way we're gonna accomplish this is we're gonna re redo our month timeline here, and that's just gonna be a direct copy of the above. And then we're gonna introduce a new function, and that function is gonna call it be called transpose. Transposed is a little unique in that we're going to use it as an array. But what it will do is reflect downward exactly the same as what we've written across in the months. You're more than welcome to just start with a zero and add up by one each month. I'll just show you transposed because it can come in handy. In other situations, you count the number of cells you want to transpose, right, And then you highlight down that same amount of cells, and then you select the array that we're gonna choose from, which is those months, 0 to 60 and then in command shift ender will be the shortcut for calculating that Ray. And all that means is it's gonna follow that transpose function all the way down, which means it'll just jump to the next cell and calculate again and again again. So again, long winded way of getting 0 to 60. But what you should have now is months 0 to 60 going across and months, 0 to 60 going down. Now we're going to start filling in What? What that table does the next video 3. CH2 Cohort Bringing In Assumptions: all right, we're going to start by bringing in the relevant assumptions. We need to consider our revenue buildup not spending as much time repeating some of those core bottling principles in this course. They didn't the intro modeling course. But reminder that when we say bring in assumptions you're not typing new numbers or assumptions into this new tab when we need to add more flexibility to our assumptions, which we will, we'll just add that to the assumptions page. So I stayed in formatting terms. We're not gonna have any yellow cells on this new page. Um, remember, this whole exercise is unpacking the customer journey from new users through canceling our app. Eso. Let's start by bringing in those new subscribers, we don't need to adjust those. If you need to make some assumptions about how those customers show up in the first place, refer back to the intro modeling course. There is a video on drivers and unit build that you should check out. First thing we want to unpack here is an assumption about the customers by product tear, so we'll need a mix. We had previously just done a blended r p you I don't know how you're doing it in your model, but, uh, we're gonna assume that we launch with Tier one, and then the other tears will be released at different points in the future. So what? I'm changing. What I'm doing here is changing my revenue build. If you're doing this for the first time, start by thinking about your new customer journey. So how did they show up? What do they do when they're there? And then how did they leave? Until you have launched multiple tiers, they're all gonna be in your based here. So I'm just going to give us a row between our other to tears and tear one to indicate that we think about those a little differently. Um, meaning in modeling terms will be a different formula for those. I'm gonna assume that if a customer isn't two or three, they're gonna join his tier one. So that makes percent will just be one minus the tier two and three mixes. So what? We need to know to classify how a new customer joins which tear we're gonna start with an assumption of initial mix. Right? So for every 10 new customers that join how Maney fall into which category. That's what we're calling rate here. There's gonna be a growth of customers joining that tear over time because of my business matures. I'm able to move upstream away from my small early adopters and towards larger customers with more sophisticated needs. But there's going to be a limit to that. So let's give us cap right so we'll we'll never have more than what I'm saying. Roughly 1/3 or 30% of new joiners joining each of these tears and also that they can't join until the product actually exists. So give us a start month. Next, I'm gonna bring in a formal. I've used a lot of my assumption box. Don't feel like you need to go back to other videos to look at that. I'll just walk through it briefly now, kind of what it what it means. But it's new to this course, so this is an embedded if function that says, if our month is the start, month used the initial rate if the month is greater than that. So in other words, if we're after launch, grow it by the amounts of change assumptions of the prior month times that growth rate. But I've also wrapped in that minimum here. You can see to say that, and that's how we do the cap. It's saying, Do that. But as soon as the cap is less than whatever that growth rate gives you used the cat on, that's that's how the minimum it is functional there. And let's see what that looks like on our mix appears to be flowing through well. So let's and now another block to spell out the number of new subscribers by each tier over on a revenue build. Um, and that all looks like it's coming through, right? We're just saying that the mics per the mix of new customers for that tier is how many will show up in the initial month on. And we're gonna be pulling from that in the future videos. But for now, let's just get that on the page and just something that I'm doing here, that you may have noticed that I'll mention I'm on this page aligning my timeline to be in the same columns so month, zero month, one month to or the same as they are, the assumptions page. What that allows me to do is just copy paste more easily across. So I know that if I'm bringing in the churn header than the month zero Cell is going to be the month zero sell on the assumptions page. Let's also bring over churn again. This is something we'll do more with later. But for now, let's just assume we don't know enough about our customer behavior to make unique assumptions by each tier. Eso Let's leave that to a future video, and we're going to start building up our users to a revenue number next. So tune in the next video now that we've got some of assumptions that will allow us to do that here on this page. 4. CH3 Cohort Analysis: in this video, we're going to start filling in the grid we created here, and we're gonna have one of these for each customer tear. So I'm just gonna go ahead and add a label that this is Tier one. Next, directly under month, I'm going to add a new subscribers line. What that line represents is the number of new tier one customers that will arrive in that corresponding month. I'm also gonna get myself a copy of churn here because while we haven't done this yet, we may want to have a churns specific to each teach customer tear. For now, we're just gonna like that to the one turned Ray we have above. I would think about the formula that we want to have represented in this grid. Let's build our formula in three stages. First, what happens if our column month our timeline month equals R ro month or our code word month? That means that the cell were in if those two are equal is the current month that we were going to get new subscribers just as a quick reminder on the dollar sign fixing a reference holding a reference constant the month the timeline month. We want that to move forward each month s so we will hold the row constant. In other words, when we copy this formula down, it will not copy with it. But across it will we do the inverse for the cohort month so that it can flow down, but not across. If you need a little bit more detail on how those dollar signs work began, I point you towards the introduction toa software modeling course doing this if statement, I'm currently adding an n a for false as a placeholder, but that will ultimately become other if statements embedded of statements. So again, we're looking at this in three parts. First were saying What happens if these months equal each other? We're not yet addressing what happens after that were before that, which would be which would be the if not imbedded of statements. So let's fill that out. And what we see is that new customers triggered by that current month, if statement is working correctly and we see Anais on either side so that that's what we would expect, right, we only actually trigger a number if those two are equal to each other. and that is the number of new subscribers we would see in that month. Now, let's say, Well, it's not the current month. It's either before or after that time period. So let's build out another. If statement this time I'm going to start with addressing what happens if the cell were in represents a time prior meaning. The cohort month, which is our month running down on the left again, are before the current month. So what we're saying is in month, the month 15 cohort doesn't exist right until month, 15 occurs. So in that scenario, if a cohort month is less than the current month, we should just see a zero because no customers have been added yet and we're gonna still leave that final if not as an A for now and again. That's going to address what happens if it does not equal each other. We're not prior to that. We are after that filling in now. What we see is that each cohort is zero until the current month equals the month of that cohort. Then we see the new subscribers, but we still see the enemies flowing after that to make this a bit more clear. I'm just gonna change the headers to cohort. We refer to Cohort Month and Timeline Month. It's a little bit more obvious. So what does happen to those customers after the month they appear? Right now, all they do is either stay and keep paying us where they leave because they churn out. So I'm gonna give us a churn rate, associate it with each cohort. So let's use our transposed function. Remember, this means that we highlight the same number of cells we want to transpose going down and then control shift. Enter is the shortcut for copying Honore, which the curly brackets on either side is is the representation of that. Now I'm gonna replace R N A. For what happens when current month is after a cohort launches. So this is any time. The current month is now greater than that code for a month. And I'm going to say we lose X percent or the cohorts churn rate of prior month subscribers . Let's fill that out and we see that each cohort in each month is depreciating over time, which is what we would expect. For example, by months, 13 are month to cohort has 130.3 users left a quick word on that because it seems silly to talk about 0.3 of a user. But I like to leave that in because the point of your model right is to make the most accurate projection of average likelihoods. 5. CH4 Cohort Adding Subscriber Tiers: So what everyone should have now is a fully functional tier one cohort block of cells here . And what we're gonna do is take that grid and that we've filled out. And we're gonna make two more of those for tier two and Tier three, respectively. So you see me just updating the name first of all the month we could just set equal to the tear directly above us, right? Those will not ever be changing. Month zero through 60 will always be the same. So we'll just give ourselves a month timeline for each block so that it's easier to make sure that our formulas air working correctly when we when we do some auditing of our formulas, what we want to be able to do is see which sells our formulas are referencing. You see me updating, even though we're repeating co or turn and month for each of these blocks, you see me just sitting those equal to the one above court is something that we may want to ultimately change by by tear group. But for now, we just wanted to be the same as the above. We will want to update new subscribers or That is one number that is gonna be unique to each of these tears. For now, let's just get three of those blocks together. And the next thing you'll see me do to make this a little bit less unwieldy toe work on is grouping each of these blocks. What that allows me to dio is hide or open each of these independently so that we don't get lost on which tear we're working on. And so now you'll see three plus signs. Those represent the three blocks for the three tiers of the cohort grids we've built. So now let's start updating those blocks to accurately represent each individual co court. First thing we're going to do is make sure that those new subscriber numbers are pointing to the right tear. So remember, we're bringing in different new subscriber numbers for each individual tear. So now let's build out our total subscribers at the top of the page and take a look at that . What we will do is just use the some function for each individual tear, and we'll drag that across. And this is this is a good exercise in since checking your work as you go What we see here is on error somewhere in our spreadsheet because they should not all be the same number. So let's use this opportunity to do a little formula auditing and try to find the source of our error. So what did we do wrong here? So what I see when I open up our tier two is that our formulas not pointing to the right assumption numbers. What it's doing is getting stuck pointing to the tier one assumptions above why that is rumor is we put a dollar sign toe lock, the timeline months from going down and the court months from going across. So what we're gonna do is just update our spread sheet so that we make sure that each of those blocks is now pointing to the set of assumptions that we've built in for ourselves here and a good way to just since Check that we've got the right formula is pick a cell somewhere in into your grid in the middle of your grid and double click on it and then see where those cell references are pointing. We can use the color coding very helpful in the formula. Builder toe help show that. So now we have three functioning blocks of cohort analysis for each of our three customer tears. In the next video, we're gonna start tying all this together and start to make sense of it and measure what this means for our financial performance. 6. CH5 Cohort Completing the User <> Revenue Bridge: So now that we have all our data, let's think about how we want to use this and build it back into our model. Because, remember, the goal here is to take our new subscribers, break that out, really understand how those cohorts move through our tears through turn and become revenue . First thing we want to do is break out churn. This is because, for example, a larger customers in our higher tier products and those larger customers tend to churn less because if only because they go out of business less, you'll want to think about those differently, most likely. So I'm gonna add That is an independent assumption, and we've already built that into our our blocks below, so that will be easy to link in. And then what we're gonna want to do is just also show lost subscriber. So what is? The number of customers were losing in each of those tears independently, and that total the subscriber number won't change rights of This will just be a way of breaking out how those net movements happen, so we know how many new subscribers there were. We'll find out how many we lost and the difference will be the total subscribers. So what else do we need to complete the flow back to revenue? We're gonna want to know that pricing by tear and that's something that we don't currently have. In our assumptions, we we were initially in our model anyway, using a blended r p you revenue or revenue per user, that is now gonna be come something we calculate and that will allow us to look at revenue by tear. So how much revenue are we bringing in for each specific tear? Not just in aggregate. And then another thing. I want to show the bottom because we've now broken out thes lost subscribers for each tier , we might as well look at how each of our three tier user groups are moving over time. I'm gonna give us a little user summary on What I'll do is we've we've mentioned this a lot and other courses, but build a corkscrew, which means we're gonna look at the beginning of period and an end of period and the movement in between which for us is just right now new and lost. You may very well already be thinking about other things that can happen. For example, maybe a tier one upgrades to a tier two. That is definitely on example of something a lot of you may want to build in. We're gonna avoid that for now. Just keep it simple that customers weren't assume they either become new or they are lost. They churn out. And when I used GOP and GOP, that stands for beginning of period and end of period. So let's get back to the assumptions and filled out what we need for calculating churn by tear. We've already got the formula in our assumptions. I'll remind us what that is, but what we're gonna do is just have are three tiers under churn. And instead of having that one blended assumption, we're now going to do that three times for each of tier 12 and three. And just a reminder those elements are rates change, cap and start month. So a reminder of what those means. The rate is the initial rate at which we start. Whatever that start month we deem is the change is how that moves over time. So what happens to that churn rate and then the cap is the threshold for what that number can become. So what we're saying here for the first year, for example, is that when we launch, we will have a churn rate of 10% that will decrease by 1% each month, and it will never get lower than 5% quick formatting point as a reminder for those who took the earlier course or new information for those who haven't. We always put assumptions that are linked, so assumptions that are not input cells in green with black as the text color. Now, let's bring that into our revenue build page. And you see, I've just staggered the start dates and the percentages. What that allows me to do is just quickly make sure we're hitting the right assumption. So what I'm gonna do is just link each of those tier 12 and three turn rates now to their individual tier 12 and three assumptions. And I'm gonna use our transpose function again just to make sure that our cohort churn is pulling in the right month. So remember the way we've calculated our cohort churn is that the month in which that court launches will be the churn of that cohort. We assume that does not change for the cohort over time. It only changes with the next cohort. Assuming we've gotten better at bringing in the right kinds of customers, that improvement should be seen in the next group on. That's how you'll find a lot of managers like to look at their cohort analysis, so understand churn by cohort as opposed to in aggregate movement. Over time, that ladder in aggregate over time will ultimately become one of our KP eyes that we look at, however, to measure the health and performance on tracking of our company. We do expect in aggregate are churn rate to go down. So now let's fill in the lost subscribers. We already have all the information to do this. The way we should think about it, right is is what is what is left. We know the total subscribers because that's what we've calculated in our blocks below. We know the new subscribers because that's what initiates those blocks below, and the difference should be the law subscribers. So what we're gonna do is take the current month of total subscribers in a particular tear attract the prior month. Total subscribers in a particular tear and subtract the new subscribers that we got in that month. I'm just gonna quickly calculate that for our tears 12 and three and just a reminder here as we do this toe do quick sense. Trek says you go. Easiest way to spot an error is as you build the formula. So I always just drag it out a few cells and take a look and make sure the numbers make sense to you much easier to audit a model as we go versus at the end. The next thing will want to build into our assumptions page because we did not have that before his a break out of the pricing by tear. What we have used previously was RP you, uh what we were doing here was making that a driver, right? So just again, making a blanket assumption on what our monthly net moving r p you will be. Now what we can do is look at that on a much more actor basis by by tear and R p. You will become a k p I that we look at when we when we want to measure the business. So the aggregate revenue per user that we're getting over time and how that's increasing. You'll find that a lot as you build out your models what you might have used as an early driver. Your assumptions page will often become a measurable KP I as you start to break out into some more finite, measurable pieces. And remember, a time on assumption becomes a formula based assumption. So just put that in green with black text. I will go through again, but you can see we're using our same formula that we've used for some of those other assumption Rose where we have a new initial starting point, a growth rate over time in a cap and a start month. So now when we do the revenue, what we're gonna say is the pricing per month. It's gonna be multiplied by the average users in that month, the prior month's end of period and the current month's end period. And just to note, another thing we talk about that can be addressed by cohort. We're currently doing it just by tear is the pricing. So what we may want to dio I've got our prices going up over time just to make sure that the calculations work so I can see it happening. But realistically, I'm gonna set that growth rate to zero, and I'm gonna assume our price points remain the same throughout. If you're working with a business where those price points may very well change quite a bit over time, uh, you'll probably want to include that in your cohort. So again, when we look down, the only thing that we really see changing is our new and lost rates are unique to each cohort. Um, if you were Teoh, add the example I gave earlier was upgrades might be something that would be unique to a cohort. Price points could be a swell. So remember, what we're doing here is the corkscrew. So the beginning of period just equals the end of the prior month. New and lost, we pull from above, and then the end of month is with some of that change. We're then going to sum that up for total users and note that we've calculated this in a slightly different way than the one we have above. So whenever you do that, it's a great time to check yourself. So what? I'm gonna do is just at a quick check line here. And make sure that the total users were calculating based on these corkscrews is the same as the total users we've got based on summing are three blocks together. So what I'd expect to see is zeros all the way out, which we do. Dropping in those checks occasionally is a good way toe spot on error from time to time. So now we're just gonna bring it all together. Our assumptions Page has all of our income statement line items on it again. If you want a little bit more detail on how you build out that assumptions page, check out our prior course introduction to software modeling. What we're gonna do is sum that up, and now we have a revenue number that is not based on blended averages but based on cohorts by month by tear, which will allow us a lot more flexibility as we start tracking our user movement over time , our revenue movement over time and understanding how are users interact with our revenue and even cost base. If we have across base that is associated with each tier, all right, you might have a tear that includes free customer support. You might have a much heavier usage, so there might be, ah, hire server burden that you need to account for any number of things that you you can now allow for by tracking independently by cohort by tear. Just make life a little easier. You'll see me moving the tier one tier two tier three headers for our blocks just above the grouping. That way we know which 1 to 1 group later. Lastly, in the next video, we're gonna put together a dashboard and start Teoh. Add in some of the measurement tools that I've been looting Teoh and give you something that is presentable, digestible, one page snapshot of how your business is tracking over time. 7. CH6 Cohort User Dashboard: all right. At this point, you should have ah, cohort, summary or revenue built page that looks something like this. And just as a reminder, right. We started with the new subscribers line, and we broke out enough of the assumptions there to look at the cohorts of monthly on boarded users by tear on that build us up to what is now a new revenue line. What we're gonna do now is show you a few ways that you can present this use it as a. What I'm gonna do is just show you a few different ways to cut and present data that might be helpful. By no means is this prescription for what, exactly? You should show what your KP I should be, how you should present them. Let's start by opening up a new page and let's call that are user dashboard. And now let's let's think about some of the some of the things we might want to show and how will want to show them. Initially, I want to show over a three year period, and what I'm gonna do is show quarterly. This may be something that prints out on its own. It maybe landscape portrait, maybe something that drops in a slide deck. Eso you'll you'll want toe play around with it a lot again, the content of it and the presentation of it on your own. So let's let's think about some of what we can now pull out of our our user data and are our revenue data That might be interesting for us for a board for Ah, for an investor. Well, 1st 1st I'm just gonna pull our aggregate total customers and I'm gonna introduce a function here called Offset. And what that's going to dio is use our prior monthly data and give us the ending balance of each quarter reference Your month zero, We're gonna fix that, and we're not gonna offset by any rose. But we are gonna offset by columns equal to our quarter times three because we have three months in 1/4. We're gonna fix that rose so it doesn't drop down on us. By the end of the first quarter, we have 1.5 users, so let's just check that. And, yes, we're now pulling end of Q one, right? We're referencing this quarter line up here and you see at the end of that, we are getting our 1.5. So let's fill that in, and that's not working because we're not letting this float down. So let's do that. Now, let's look at our net new customers, and I'm gonna make this monthly. So again, I'm gonna be looking at the end of our quarter to pull this number. This is a variable number writes every month. What we're doing is saying number of customers we gained and lost. What is the net of that? So this is gonna be a little bit more involved. So what I'm gonna do is give us another reference point. Any calculations, right? I don't want to be doing those on my dashboard has already mentioned that that is a place for outputs on Lee s. So I'm gonna give us some room here because we're gonna be looking at a few KP eyes that will require us to do a few extra calculations, and we're gonna keep those all on our revenue build. So it's going to be the total customers in one period less than total customers in the prior period. And then what we can do back on our user dashboard is used that same formula, but are Tier One reference is now going to be starting in rose 75 When you see run rate, that is another way of saying you take the last measurable period. So in this case, the last month, and that is what we're showing that is our run rate monthly net new customers as at the end of Q one Q two Q three, etcetera. All right, so next I wanna show just some substandard KP eyes that a lot of software investors boards are gonna want to look at. So for our churn rate, right, we're gonna want to show this on a blended basis, even though we're doing a break out of that calculation here. So the way we're gonna do that, Goto lost subscribers in a period over the total subscribers at the beginning of that period. That'll be Archer Marie, and that does look like a reasonable blended rate of of what we've used in our assumptions , because we've got this div zero and we may want a chart. Some of this and we certainly want to not have that in our presentation of rating that two aboard. I'm gonna use a function called is error. So we're going back up the calculation. We just did. Then just give me a zero. Otherwise, go ahead and do the calculation, and that will be a little cleaner. So lifetime. For For those who don't know, Lifetime is just the inverse of churn rate. So what we're going to say is, one over our turn rate we're gonna wrap this in is Eric, And this is gonna be our lifetime in months. So what this is saying is that when our term rate is 9.8% our average customer is sticking around for 10.2 months. The reason that is relevant is so that we can come up with a lifetime value. So how much is that customer worth to us? This is one of the most important numbers you could be tracking as a manager because it indicates how much you should be spending whether they're profitable and what you need to do to get profitable. So let's look at our revenue per user, and we're going to look back at some of the financials we have from our operating model. You hopefully have built that before coming to this exercise, we're also gonna pull marketing costs in a second to do our cost of acquisition again. Reference are introduction to software modeling course. If you want to have some help building up that entire model so revenue per user, what we're gonna do is pull our revenue from the assumptions page. We're gonna divide it by our average total users, the whole thing in his air. And that looks about right. So what we're saying here, that's we knew we are receiving her customer. Is this which will be, ah, blend of those products we've got and as a sense check right While before our tier two and tier three come in, it should be exactly equal to the assumption the driver that we're using and and it is, and what we're gonna do that for the LTV is just use The numbers we've got were to take our revenue per user month, multiply that by our lifetime, and that's what this customer is worth to us. And the counter to that is, how much is that customer costing us to get? So the way we're gonna do this is we're gonna look at our marketing costs. I'm gonna go to my monthly financials. And again, if you haven't consolidated this, uh, use, use our introduction modeling toe show how you should do a monthly consolidated model such as this is going to take our monthly marketing costs were gonna divide that by the new users we got in that month. All right, so what we're looking at here is the cost it takes to get our customers the revenue they're bringing in each month and what those customers are worth tow us over their total lifetime . So you'll very often see people ask what is your cac to elf TV? So if this were 100% we would be in trouble. You see that a lot, though, In a lot of high upfront acquisition cost models that are a winner take all market. Sorry, this is error gets tedious. Gonna drag these KP eyes out. These are same offset formula. Just change our row. And now we're starting to get a pretty nice snapshot, albeit very boring with numbers. So let's add some charting 8. CH7 Cohort Dashboard Charts: what I'm gonna show you is a methodology here for charting again. Don't pay too much attention to what I decided to chart in and of itself. But I'm gonna show you some tricks to present it in a few different ways that it could be pretty helpful. So let's look at total customers and will do that by tear. And we're gonna do this exercise with total customers. I'm just gonna put net new down here because that's another set of numbers. You may want to do something very similar. So we're gonna leave that alone for now, Just focus on the total customers. All right, let's drag that out. And what I'm gonna do is chart this over our 1st 3 year period and I'm gonna do a couple different things. So what's imagine that we're actually using this. We're six months out of the way. We've built this model if if you haven't been able to tell already, is that it can be filled in as we make these projections become historical. So as time moves on, so what does that mean? It means we wanna have a way to look at the historical numbers. First, the projections And we also wanna have an indication of what our original projections were , and I'm gonna call the total Here are original projection. So I'm gonna do something that I say never to dio. But again, when we're working with the dashboard, we can break some of these rules and I'm gonna copy that and paste, it is a value that means that this is no longer summing any active changes. It is purely the values as that today. Now I'm gonna put these in blue just so we remember that going forward, what I'm gonna dio is at a few rows for historical. And so what we're gonna need for this is a historical line. We've got this in our timeline from the assumptions that this is something we built in our intruder modelling time line. But what this question is asking is are we standing in the past or the future? So if you're updating this at the end of every month, right, your hard coating in what you spent on each of your marketing costs, what your users were what your new your turn were the rest of the model is gonna flow through an update on DSO. What we are assuming is that let's say Roberta's of this exercise. It is 12 31 17 launched this January last year, and this question looks at today's date and it says, Are we above that? Are we below that? And it is just a simple binary 10 to tell us it for historical. Not if you aren't familiar with the function. It's just a simple today. Open close brackets will always open up with today's date. We're gonna have two versions of our formula here in an additive statement that points to historical line on our timeline and for the projection and for the projection. It's just gonna be if historical equals zero and then are seen formula and then read it to the inverse if historical equals one for the historical and then the same formula. So what you'll see is zeroed out set of either historical or projections, but the same set of data What I'm gonna do is take us out to let's just go out to 24 months were a highlight, that data, and we're going to insert. I'm gonna use a stacked column for this. I'm gonna go ahead and bring it onto our dashboard page so we can see it in use their I'm already seeing that this total line, we the hard, hard put a user line we put in is messing with our data here. So what we can do is change the tart chart type of that specific set of that specific Siris of data by right clicking on it, which, if you're on an apple, is control. Click, and I'm gonna change this to a line. So now what you see is what it should be, right? The way stacked, anything stacked, stacked area stepped. Columns that were using here works. Is it at its additive, which is what we want, right? So we're seeing at the end of q eight, that should be 85 at the end of Q eight were right at 85 right? So these three numbers here represent these three numbers on our table. Then just to show what, uh what that historical function we added in does right. So we've got these three colors. What I'm gonna do is just play with the date again, and that's done to our chart. It's now updated, so it's very clear, which you know which part of the data Siris are historical and projected. What I would typically do is make the same data. Siri's a lighter version or a darker version of what's to come. So, for example, I would say instead of yellow and let's let's bring up the format So it's already up on here. But the way you bring it up is again right Click format data. Siri's. We have some options over here, and I'm just gonna change the field color. So let's make the asked Dark blue on. Let's make the future later glue do that for a different data. Siri's also just add a title here and I'm actually well, I'm up. You're gonna throw in some totals. Actually, what I would I'm also noticing here is that our timeline is just a random count up. So when we're using months, that's not the end of the world. Were just a month off. If we're using dates, it's gonna look very funny. So what I'm gonna do first, we'll make this very clear that we're counting up by month, and when you have any need any set of data that you want to link to an access. I'm gonna go back to our select data have highlighted the chart. I'm in select data and you can see there's an opportunity here to give your chart horizontal axis labels, which we're talking about. The months zero through 24. Great. And now we're starting a month zero and going up to 24 4 I want to talk a little bit now about this line which obviously, since the entire thing is projected, it's gonna be exactly perfect. The chances of that happening in reality, being a year in and having your user counts exactly what you thought are slim to none. So let's try to break our model here, and this is when I recommend everybody does is think about how you're gonna use it out. Maybe others on your team might be picking up and using this model and what might go wrong . One of those areas where things often go wrong is when you're updating the historical numbers as they happen, so month to month always recommend that you update the data so that your chart you excuse me, your models always building off of the most realistic numbers. That doesn't mean your assumptions change. They are what? You last had them in it. But we want to have built our model in a robust enough way so that when we are standing a few months in So let's say that we are I'm gonna say just just a few months in. So let's, uh So let's just change this toe middle of this year and go back to our, you know, dashboard. See that that has now updated so that only the first few months are the 1st 5 months. This will be our first month of projection are updated here. What are the things that you will actually be updating? So in practice, right in operations at the end of your month, What will you know? Well, you'll know the number of active users you have by each category. You will know this by cohort. Hopefully, if you're taking this course, got a mechanism to track by cohort bundle your users by the month they signed up in or whatever month one of the core mechanism you're using. So you would not just be typing numbers into new or lost. We've built this model in a way that should override should override that. So So we were down in our tear groups. We would say we're now standing in month six. These numbers should be riel. And what they would actually look like is that as we update these five months, we're making it very clear that these air hard codes any number we change here is gonna be a hard coat update. Let's say you actually got to users. One went away right away. But then that user stayed. And I'm just making up numbers here for the purpose example. Let's say you actually got five new in that month. And by updating in this particular field, what we're allowing toe have happen is the model to pick up what we still project will be the case. So as we look forward, we know that month six is no longer having a run rate because it's looking back at a zero we're seeing that are sumption zehr still picking up those hard coded numbers accurately. What is different, though, is exactly what we know. What we know to be true. The way rebuilt up our revenue is that we take a some of this block we're not using anything other than this block for our revenue buildup. So if we go back up here and we think about is our revenue accurate for that period? We are so right. We're still looking at back up at our pricing by tear and our total subscriber number, but that total subscriber number is getting pulled from from the months themselves. So you know that you only have to update in that one place. And as you let other people work on the model, you can just make sure and always since check, because if they're doing their four Matic properly, you'll be able to see where those hard codes worked their way into the model. I always recommend trying to, uh, put yourself a few months in the future, updating the model and see what happens. Try to break it on, then try to work out what is the least possible cells that someone's gonna have to update in order to flow through the whole model. Let's do an extreme example and say, actually, a month five, we got 500 users as we can see our projections airway off into the future. Because of that one month right and we'll always have that line there guiding us more realistically. Let's just say it was something like 10. We're still gonna be tracking that change live and, as you can see over time, our projections that 10 becomes a very minimal amount of the total users as their run off. But the idea is that you can always see a few things. You can see the historical versus projected. You can see the original projection and how far off you are from that. So I encourage everybody to think of one or two data sets of their KP eyes that they want to really visually represent, and then think about how to do, Ah, projection, historical and forward looking set of that what we've done here by total user. But as with any dashboard, you're looking for a clean one page visual representation of the data you're trying to assess and measure