Financial Modeling For Software Startups | Win Smith | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Financial Modeling For Software Startups

teacher avatar Win Smith, Practical Finance for Entrepreneurs

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

9 Lessons (1h 23m)
    • 1. CH 0 Intro

      5:57
    • 2. Ch 1 Setting Up Assumptions

      8:59
    • 3. Ch 2 Fixed Costs

      8:11
    • 4. Ch 3 Variable Costs

      18:13
    • 5. Ch 4 Revenue and Unit Build

      7:40
    • 6. Ch 5 Monthly Consolidation

      9:03
    • 7. Ch 6 Dashboard Financials

      5:40
    • 8. Ch 7 Dashboard KPIs

      6:50
    • 9. Ch 8 Dashboard Cash Assessment

      11:57
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

279

Students

--

Projects

About This Class

This course is designed for founders of a software subscription business looking to develop a financial model. If you are thinking about a fundraise or looking to better measure and assess your financial performance, this is a great course for you!

My goal as a teacher is to use this modeling course to help entrepreneurs better understand financial drivers and interactions of their business. As an entrepreneur your time and money is precious, and understanding the mechanics of your business will allow you to better assess strategic decisions around where, when, and how to invest that time and money.

This course is meant to be very hands on. We will be building a model together live, and while the run time for this course is under 2h, you should not assume your first model will be built that quickly! Be patient, set aside a day to get the structure of your model up and running and then know that you will constantly update the mechanics of your model and research more refined assumptions throughout the life of your business.

Your goal should be to add financial analysis to your entrepreneurial toolset to improve your strategic decision making!

Meet Your Teacher

Teacher Profile Image

Win Smith

Practical Finance for Entrepreneurs

Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. CH 0 Intro: welcome to an introduction to modeling for a subscription software business. My name is Win, and I'm gonna be teaching the course. We're just going to start by giving you a quick overview of what we're going to cover and make sure that this course is a good fit for you. It's going to entail building a ground up model for a BTC software entrepreneur. If you're considering a fundraise, maybe start of idea if you need a financial profile of your projected business, have some capital already on. You want the tools that to think about the highest return to deploying that capital this course is gonna be for you. If you are looking for high finance training, this is not for you. The goal of this course in any others that come from us will be always to strip down the financial training toe. What matters the most for entrepreneurs to understand the financial mechanics of their business without getting into the complex accounting or modeling concepts this course is meant to be very hands on. We will be building a model together live that will look something like this. The run time for the courses under two hours, but you should not assume your first model will be built that quickly. Be patient. Set aside a day to get the structure of the model up and running, and then know that you will be constantly updating fixing the mechanics of your model and researching more refined assumptions testing assumptions throughout the life of your entire business. Your goal should be to add financial analysis to your entrepreneurial tool set to improve your strategic decision making as opposed to become a finance walk. How you should think about using this course the videos are all gonna be narrations of my life. Modeling on this is just gonna be a very generic B two c business. I'll introduce new formula, um, and new functions. But then, well, very often breeze past those when we recover that again. Know that every formula, though, and function that is used in this model will appear at some point here. Um, whenever I type it and I recommend you pause and look in the upper left, if you need a reminder on the function, or you just wanna match up formula and see what's going on with yours, always look there on that part of the screen to get your formula right. Any time you get stuck or even face a challenging issue that you resolve yourself, please post it and take time to review and respond other posts. Nothing's gonna help you internalize the financial interactions in your business better than being forced to think through how you would represent them in a model about me. I love finance and startups. I have been working in corporate finance, venture capital and as a startup founder. Um, and I consistently found that finances cast aside by a lot of founders as being either irrelevant to customer acquisition and growth too complex to merit the time and energy to learn or just plain don't want to learn accounting. I think Founders Air done a great disservice when told any of these fallacies that there is a great need for practical financial training for entrepreneurs that focuses only on understanding your time and money as investments, thes air investments that have returns and returns that must be measured, it understood. If you want your investments to be more efficient than your competitors, this is the first course built in along those lines. I always like toe start with any financial understanding from the standpoint of a model forces you to think about those interactions in a very real way. Definitely let me know what you think and what you'd like me to cover in the future in the comments section. And with that, let's get started. I'm gonna ask you to keep three principles in mind that you're going to. I roll about now, but at the end of the course, you will totally forgive my obsession be thankfully, you got into the right habits early. All three of these are designed to minimize errors and maximize efficiency, even though it may not feel like it at the time, because inevitably down the road, you may make edits to your model handed off to someone else, and these principles will keep you from having some very tragic errors. When you do that versus formatting, I'll give you some basic formatting rules. As we go. Please abide by them as your models get more complex. Understanding the purpose of every cell by its format will help you a ton. Minimize formula complexity, spread your workout cells or unlimited resource. So if you have a complex formula you may want to break it out into a couple rows, and calculations also try to design your formula to be reused. What I mean by that is, as a general rule, any cell that has a calculation formula in it and he sell that is touching. That should have the exact same formula in it. That way, when you are quickly copying right, copying down, you're going to not overwrite a formula that was unique and special for that one particular line. Don't type numbers more than once. What I mean by this is you should just always think before you type. Don't in bed numbers hard coded numbers in cells, for sure, and when you repeat number, have it be on equals to a prior number. Never type that number into multiple cells. If none of this makes sense and you haven't internalized any of it, don't worry. I will constantly harp on these throughout, and they'll start to make a lot more sense as we get modeling. How to think about your model is as a translation machine. It will convert your operating decisions into measurable performance results. Every model will be in three parts. The assumptions These are the dials and levers of your business. The things that you change, you invest more or less time or less money the dashboard, which will be the measurable results of what you need to know to make decisions on their Spencer commenting already. Um, and everything in between is what I just call a calculation so or ah, rule of your operation. So how how these assumptions and drivers interact to results in measurement in result in measurements. All right, so we're gonna dive right into the model. Now, go ahead and open up your spreadsheet, and we'll get started with the assumptions. 2. Ch 1 Setting Up Assumptions: we're gonna start by naming our first have assumptions here. And then the first thing we're gonna build is our timeline. And this is critical to the model. There's a reason I start here. Uh, everything we do going forward is gonna in some way tie back to this. Let's give ourselves a month, quarter and year, and then I'm gonna put a calendar date in between, and I'm gonna give calendar month, quarter year. This gives us a lot of flexibility on how we make our rules or assumption rules going forward and how we consolidate to present our financials. Ultimately, in the end, for now, just follow along and you'll see what I mean is we keep building. So for the month, quarter year, these air, the time This is the timeline that just goes up indefinitely. So the 13th month will be 13 when we get to the calendar, right? It would be starting over again from one because you'd be January again. So let's start with a month zero. And this is the month ahead of the functionality in our model. But it's always good to start with that month zero on, then our calendar date we're gonna say we launched January 1. So let's give ourselves the date The ending of this, uh, this December 2018. And then we'll assume that the APP launches in January and the model begins. Then first rule of formatting I'm in a put my inputs in blue. What I mean by an input is something I've typed into a cell, right? So there's no formula. This was a number. So what? I want to make sure I do by calling it out in blue is that I never type that number again. Eso that any time I might want to change it, you can see how I might, you know, Let's say the calendar date gets pushed off. We push out our launch or you pull it forward. We want to just change that once we don't want to change it. Every time that date appears in the model. The formula we're gonna use for months is just plus one for 1/4. Every three months it takes up by one. So we're gonna divide it by three. And we already see our first problem here. You can't have a fraction of 1/4 so I mean, introduce a new function called roundup. By placing our calculation in round up and telling XL zero digits, it rounds up the closest imager year. We're gonna apply the same principle. But dividing by 12 instead of three could introduce another function here with Calendar called E O Month. And it just refers to the prior month and then runs the date forward by a certain number of months and gives you the end date of that month. So you know, month one means that the next month will be the end of January, the end of February and so on. For the calendar side of things, Excel helps us out for a month and year and gives us a function. So I'm just gonna look at the date and say, Return me the month, return me the year and then for quarter. It doesn't give us this, but we can apply the same logic we did before to look at instead the calendar month, right? Instead of the time zero month. Let's drag this out, and it all looks to be working as we would expect it to be. Now let's jump to the operations of your business, so I'm gonna ask you a series of prompting questions. And these are gonna be the natural buckets that everything in your business will fit into in one way or another. This is the operating engine of your business. So everything you might invest money in that drives performance in one way or another, right of your bottom line. Well, we'll have a home. Here is the first question. What it What is it? They use cell for us. We've talked about our example is gonna be subscribers monthly seat subscribers. If you have a B two b business, you maybe you have something that looks more like license contracts. Longer term. Maybe you have a service revenue component. Um, so you have you would be selling man hours. But just for now, just jot down what it is you sell, and this is gonna be our unit build next. I want you to write down How do you package that thing for? For sale. So for me, the example is I have three subscription tiers with different levels of service in that, that, for me, is gonna be my revenue build. And just a quick, quick side Note these do not need to be perfect. We're gonna build this model so that you have a lot of flexibility to come back and change these sections and how they operate and without having to redo the entire model. So we build everything in a very compartmentalized way so that when you inevitably do have to fix mistake, change, how you are thinking about an assumption, we make that very easy for you to dio. And we do that by following some of those principles I laid out before. And I'll just keep reminding you along the way. So for now, don't try to be exhaustive. Just try to get the intuitive buckets in your mind and get some examples of what those costs are that lie in those buckets as we kind of go down the income statement here. So the next category I want you to think about, how do you get those customers? So how do you get those units sold? What are the last measurable touchpoints between somebody not being your customer and becoming your customer now? Measurable is key here, and I would urge you to if you haven't already take some classes that really dive in specifically to how to measure your marketing analytics and your marketing channel strategies for now, you know where I'm just gonna give you some examples. It's fine to have one bucket that is tougher to measure. That sort of your catch. All I call that organic. That's just the people who find you. And you weren't trying to get them to find you the people who either through s e o word of mouth. Maybe they read a blogger article that you didn't pay to have published. Um, but for the most part, you're gonna have a lot of channels that you are investing time and money into, and you want to have those all listed here. He's going to your unit drivers. All right. Next, I want you to think about some example costs that change with the size and scope of your business. So, for me, on example, would be server space, right? The more users I have, the more server space. I need the payment processing. So for every sale, I'm gonna pay a percentage of that to whatever I have in my system. They may change over time, but essentially, they go up pretty much in line with some other aspect of your business. We're gonna call these your variable costs. Next. I want you to think about what are those costs that don't really change dramatically with the size of your business. They're the things that are gonna be month over month, relatively the same on and I do say relatively, we'll talk a lot about how costs can kind of move from fixed variable and vice versa. But again, at this point, we're just trying toe intuitively understand the buckets. So I'm putting down some examples of things that I pay for a monthly. I want to set aside money for a monthly. They'll be part of my budget. And these are your fixed costs an hour getting into Who do you need? So who are the people that you need to run your business? You may not have developed a full hiring plan as of yet, but, uh, you wanna give yourself plenty of space to do so, um So give yourself a number of heads here. You are likely the CEO. You'll have a number of developers. You may have see emos, CFO's finance folks just jot down some titles here for now. these are gonna be your staff costs. Lastly, we are going to think about any of those one time costs of these. These are not the people. The things you need to run your business. They're not the costs to go up with every sale. These are the one time expenses that just have to happen. But you wouldn't consider a part of your day to day operations. Some examples might be You're making a large acquisition. You're buying a bunch of equipments. Maybe you have a one time large legal expense because you're applying for a patent. Anything like that would go here. These are gonna be your capital expenditures or cap ex Great. And now you should have a fairly complete albeit I'm sure there's lots of missing pieces, but at least a full picture of your business in terms of these different categories, right? Everything from the units that you were gonna be selling, how you sell them. And what are the costs in your business that you need Teoh? Continue selling them and sell more of them. Um, next we're gonna start to in the later videos, we're going to start to tie some of these together and think about how they actually interact and turn into a profit 3. Ch 2 Fixed Costs: All right. So here is the spreadsheet we've set up under assumptions. And what we're gonna do now is start filling some of this in. So you should have your timeline at the top. And you should have a fairly comprehensive look at the components of your business that fit into those seven categories we talked about before. Okay, um, we're actually going to start from the bottom and work our way back up. So let's start with Cap Ex. What do we need to know about that? So this is very simply some cost that's gonna happen in some month. So let's give ourselves, uh, those two headers here. I'm just gonna put in some example numbers here for now. Uh, again, none of this has any thought applied to it. So, you know, use your own categories in your own numbers. What we're doing is just an example of how to structure the model itself and what you don't me to do here. Quick formatting principle. Remember our our inputs. Anytime we type a number, it's gonna be written in blue on, then, to indicate that this is the kind of input that I'm gonna be changing a lot and I wanna maybe flex, you know, in different scenarios going forward, I'm also going to give it a yellow background. This will be the first time we use our timeline that we made up above and pretty quickly see how much utility it can hold. Our we're building on our model. What we're gonna dio is we're gonna use a new function is gonna be the if function. And for you software minded folks, this will be pretty intuitive. But it says we're gonna test something. So I say if the current month equals the month of this cost item show cost otherwise, show me zero. It's month zero. So we're gonna see zeros That makes sense. Now let's fill it down and right. And that's short cut Is command D Command are for those of you on a Mac control, if you're on windows and we see a problem here immediately, right? I mean, month eight. And I'm supposed to see 5000 so let's see what went wrong. I see that I am now no longer referencing. I am referencing month eight, but I'm not referencing the correct cost in month. So what we're gonna do here is add something called an anchor. That is a dollar sign. And you can put anchors around the letters, the numbers or both of any reference, and that will anchor it. So what we're gonna say here, right is that we do not want the columns to change. But as we dragged down right, we want to look at each line independently. So we are gonna let that drift, but the month is gonna be fixed. So let's put an anchor on the on the month row, but not the column. So what we set up here should mean that the rose will go down, but that comes, not go across for our assumptions. And the months of top will go across but not down. And they receive successfully. Got, um, those now appearing in the right months. Once. What you put those dollar signs in it Maybe a little counterintuitive at first, but I think you'll quickly see how those operates so, uh, mirror what we've done here for your cap ex and see how that that can change and then, you know, play around in the months and the costs and see how that flows through. So The next thing we're gonna look at here is our right above our staff costs category. So what we need to know about our staff? Let's put those headers in. It's a little bit more complicated than just a cost in a month rate. So we're gonna need to know on hourly rate, we're gonna need to know hours per month and start month and then also something called Call It Full Time Premium. But what this is just make sure that this is a common mistake for entrepreneurs is to forget about the hidden costs of a full time staff or a W two employees verse a contractor . What this means is you will have to withhold various tax insurance bonus and everybody's number is different. I'm applying just kind of a basic 1.3 X rule of thumb here for our So you'll see me is that number. But that's an important that can actually have a pretty big cash impact. Because we're talking, it could be much higher. And if you've got a large bonus component, so make sure you account for that variable piece of the staff costs, and as we go forward, we're just going to start filling in some hourly rates hours per month, and you again may not have ah hiring play. And yet, once you do, you'll be able to fill in the exact titles start months and monthly cost base. So again, the numbers aren't as important as the intuitive structure here. So don't mind my fiddling, and we'll just kind of put in some not crazy numbers now and will probably come back and think more about these down the road and a quick note here, you see, with the part time folks, I'm not giving myself that full time premium that we just talked about earlier, and I'm just doing a bunch of random variable months so that weaken, fill it out. We'll see that it's actually working on. And then what I've done is calculated once they're hired, what their monthly burn is going to be, that is the salary and premium portion together. So I'm gonna do a basic if function with a slight tweak to the one we've used before, which is greater than or equal to write because they don't just get paid for the month we hire them. They then get paid every month thereafter, But it still looks a lot like that first cap ex formula. So we're gonna do our same anchors, right? The anchor, the row for our month, and we anchor the columns for our assumptions. Take a look at the and looks like it's worked Great. We're gonna start looking at our fixed costs now, and this should all start to be looking familiar, But we need to know the cost of that item. And I'm just gonna kind of again fill in some not too crazy numbers here for by not too crazy list of fixed costs, years will look very different. Um, and I'm gonna give myself just a stepping up of start months for now, just so that we can when we drag it out, we can sense check that it looks like the formulas been entered correctly and it's working , flowing through properly. I'm also getting myself a bunch of other categories like we've done in the past, just just a create a lot of space because this is this is really my budget, and there are going to inevitably be a ton of stuff that comes up that we didn't really think about in this quick exercise again, I can't stress enough to different exercises. One is building the model. The other will be researching stress testing and figuring out thes thes assumptions. Uh, again, we're gonna use a very similar formula, and we're just gonna again say, if the month were in is equal or greater than the month of our fixed costs Assumption. Give us that cost. Otherwise, give us zero. It's an anchor that let's make sure that oh, looks correct. And that looks like it's all flowing through correctly. And that finishes up our three. What I would call fixed buckets or three fixed categories. And then we're gonna get into some of the variable and revenue driver aspects in our next video. 4. Ch 3 Variable Costs: all right, we're gonna move on up to variable costs and let's give ourselves a bunch of rose here to work with because we're going to add some categories to the section beyond just the two we thought of before. And I'm gonna kind of explain the thinking as we go first. Let's split up these two because they're based on two different things. And just as a general principle, right, Teoh, keep things simple. We want to have any time of formula appears in a cell that the formula in a cell next to it is the same. That way, we can quickly fill with our down and right, like, uh, for example, in this block and again, it just is gonna keep some consistency in how you're XL's. Richard is built. That's gonna make it a lot more functional as you a lot more functional is you make changes and tweaking in the future. So we're gonna do is give ourselves a metric for each variable, and we're getting ourselves and note to tell us what it is that it's gonna be driven by server space. And that's our t a. Gus reminding us that we should put a note next toe, every variable cost. So let's call server space. It's gonna be a price per user and payment processing. We're gonna have as a percentage of revenue, right? Every dollar that gets sold, there is gonna be a percentage we pay to whatever service were using to process that payment. And we're gonna again. But notes here, next to him remember circling back Teoh, one of the areas I was saying are fixed and variable costs can get conflated. Is is over a time horizon. So, you know, I know a lot. I talk a lot. About what? My 12 to 18 month hiring plane as for example, uh, what I don't know is what that plan will be beyond that because I really have no idea where this business will be taken and what will change is, Well, it happened. Um, that is just reality. So what I like to do is give myself some long term variable aspects of things like software development. Um, you know, I don't know where will be, but I know I would be spending x percent on development y percent on operations, uh, and to get myself another category again, these air just so that when we run the model much further out than a year, we have some realistic placeholders Until the time when we get there, we can start putting in the the actual specific costs that are in kind of arm or short term horizon. So another way to refer to what we're doing here is a top down assumption. So the what we've built out below is bottom up, meaning we've taken every single line item and built it up to the cost. This is top down. So this is saying I know the cost is at the end of the day gonna be X and I just don't know yet. What are the components that build up to that? And I I understand that as your doing this it may feel very murky as to when that transition should happen in your model. So how far out should you be budgeting and doing hiring plans at a detailed level versus having these placeholders? There is no hard and fast rule, but a rule of thumb, I would have it back. Your mind is, you know, a large purpose of this model and why you're taking this course may very well be to raise capital and think about how much capital you need. How long of a period will that capital sustain your business? And that would be the period. I would suggest you have, ah, detailed plan in place, a bottom up plan so that when you go to investors, you have a bottom up budget for how you're going to be spending their investment. And there, after you have thought about what the investment will look like longer term, you can certainly go the route where you have a detailed plan hiring plan for a ZA long as your model projects out. I think what is much more realistic right is to say I, as a manager have a very detailed view of what's gonna be happening in next 12 to 18 months , and I as a manager, m self aware enough to know that I don't know what's gonna be happening. They're after, but I do know that if we're going to operate like other companies in our space, this is what's going to happen. And when you're talking to somebody like a board or investors, which is really what you're talking about, when you're looking at multiple year projection start you as a manager are making decisions on a much nearer term basis. You're usually looking at those 12 to 18 months. So, you know, I think this is again largely for those other folks. Your ableto talk through that right? You're able to talk about the near term with a lot of granularity, and you're able to talk about the long term with conservatism. So enough of Ah P s a for me on on that, um, Gus is in the background telling us to move along here so we'll get back to the model. But if you have questions on some of those softer elements of fundraise and timing, once you get through the course, definitely put those in the message board because I imagine a lot of other folks are thinking about similar somewhere questions you may have. So the other thing I like to do is give myself a fixed budget premium. So what this says is, yeah, this is this is short term, right? This is looking at all those fixed costs, and it says, Hey, you know, we're launching in three months. I think I know what everything's gonna be. But reality is there's gonna be another 10% of stuff that gets added along the way that I didn't realize we would need another example of something that can jump from fixed variable rate. Just to give you an idea, let's think about office space. So let's say that I'm in a, um coworking space like a we work. I know how much my but it's gonna cost per head, but I am. They're gonna be totally variable based on the number of employees. So what I'm gonna do is at another variable cost item, which is my price, per we work employees, right? Cost per desk. Now, some of these variables, right? Were basing them on metrics that we don't have yet. So we don't know our revenue or count yet, So let's highlight those to remind ourselves to come back to them later. What's good? And put in the fix budget premium, which is just gonna some all of the fixed cost budget you can do this number way is right. You could included staff costs, premiums. You could make that a percentage of revenue, but, um, we're just gonna put it as a budget premium and then for office space were introduced a new formula. So count if the way count, if works is it looks at a number of cells and it just numerically counts them up. If they apply to a certain rule, we're gonna make that rule over zero. Right? So if we're paying a salary, then headcount exists, We get an error here and let's look at why that might have happened. So, uh, and when you when you formula like this, that ah, in the function you're relying on an open ended formula so greater than zero, what is greater than zero? So what I want to do is put quotes around it, and that will say the answer to the question. What is greater than zero? Look at this question for every cell we're highlighting in the first part of the function and make that change. We see that we're now counting how many of the cells are quote unquote hired? That's our variable costs. Now let's go up to our unit drivers. Okay, again, this is another variable component of our business. But what this says you know as a reminder, is what are all the measurable endpoints of not customer becoming customer. And I'm gonna get myself a lot of space because there are three categories I really care about here for each of these. So because I'm gonna read repeating it, I'm actually going Teoh, make it blue and set it equal to And each of those three are those 2nd 2 references so that this is something that will change, right? I'll add a new Maybe I'll start doing instagram ads. Maybe I'll do print ads. Whatever it is, I may want to change these things light items a lot, so I will keep them in blue. That way I only change the once And what are the categories I care about? It's what is it? I'm spending on each of these items every month. What is it that converts those into a user? Right. So how much do I have to spend on Facebook ads to get one user and then the number of users ? So we're going toe? Uh, use these were to focus on of these three categories. We're gonna make the budget and the CAC the customer acquisition cost the drivers of the users. Right. So the users will be a calculation. And what we're gonna do is give ourselves eight the flexibility to do change over time. So the assumption is we will put more and more budget behind each of these things, and our efficiency with them will get better and better over time. Now, initially, I might have summarily benchmarking data. I probably have maybe an aggregate number based on comparable companies. Or maybe I've even done some testing of what these are gonna just in aggregate, what it's gonna cost to get me a user. So but I'm gonna sit them all equal toe one for now. So I'm gonna do a new formatting technique. I'm gonna set all of the lower ones equal to the one about. So just the 1st 1 right? This they're all gonna equal the same as the first cell. I'm gonna make them black because they're formula. And then to indicate to myself later on, right, As I get a more refined view, I'm gonna want each one of these to have its own its own driver. It's on input. Uh, I am gonna highlight below in green. So what that means and formatting terms is this is a formula, but it's za cell that I expect someday to turn into a yellow so with blue text and that it's an input. I'm just not find enough in my understanding yet to do so. See, what I'm doing with the month over month growth here is that I'm saying each month I'm gonna have a 1% improvement in that CAC That's obviously not something that will go on forever. So I'm gonna give myself a cap, so, um, because this is negative. Um, right. This cack is gonna go down every month. I wanna have some minimum right? Will never be zero. It'll never be less than something on. Don't take a while to get there. But just so the model doesn't break in out years, I'm gonna do the same with my budget, right? My budgets not gonna go up forever. Um, just put some big number in, though that will probably take the full scope of our projected period to really get to, uh, organic. The efficiency of that's gonna definitely pretty dramatically increase over time. Um, and there's gonna be a pretty hard limit to how much that growth, because it's going out of my control. So, um, I'm just gonna say those general marketing tactics are gonna yields more and more, folks, because third party bloggers will start mentioning my app. They'll be better ASIO with more traffic. So it's it's that intangible source of users that are gonna come through just by being by being around longer. So the bitch, But it's gonna go up slower, and the CAC is gonna be a little bit more efficient and the users is just a straight formula, right? It's just gonna be our budget divided by the cost, and that will give us our starting number of users. One thing I'm seeing here, right, it's just actually, if I think about where all these not customers becoming customers are ending up in my app, they are all becoming free users before their subscribers. All my subscribers every month are gonna be a subset of the free trial users from the previous month. It's a 30 day trial. So, uh, I'm just gonna make a little note up here for that to remind myself that we'll have to incorporate that somehow into our to our revenue build. All right, so now let's project these drivers out. I'm gonna get myself a start month here. I may wanna vary it. You know, I may want a layer in new sources over time to help manage my budget or make my testing more efficient. So let's just at a start month. So our formula is gonna say freeze. Or if right, if it's because we're doing to do a growth aspect to this, there's gonna be a wrinkle toe what we've done in the past, we're gonna say if the month equals the start, give us the cost. But now we're gonna in bed another if statement. All right, because if it's above that month, we're below that month. Really. Two different things. It's below. We don't want anything because even started yet, if it's above, we wanted to have grown over the prior month by 1%. So again, this is what's called an embedded if statement we're saying if month is greater than start month, give me the prior month times one plus the growth rate if not to give me zero. See how that works after we've anchored everything. And we're also going to add another wrinkle here and new function, actually. But a pretty intuitive. One called minimum. All right, um, again, for software, folks, this is gonna be pretty intuitive, but it's gonna it's gonna introduce that cap. So the cap is going to say if it's higher, then the month the cost started give me one plus the premium times that prior month. So that's how much it grows. But Onley do that as long as that number is smaller than the cap. So I'm gonna is gonna add a little minimum that says that formula and the cap separated by a comma as we dragged that out, we can see Yes, In fact, our costs are going up by what appears to be about what they should be percentage wise. And as we go out into the out months, we see that, yes, those caps are starting to get hit where we had the 6% growth rates. I see it. We get to 10,000 the end, So we're going to do the exact same thing for the CAC. Um just to keep the formula consistent, I'm gonna have a start month. But it is obviously not an input that will ever be different from the budget. So let's set that equal Teoh and leave. It is just a formula to the above. I'm noticing something odd happening here with our CAC formula in the month that it starts . We're getting the correct initial cost, but then it jumps to the cap and then decreases from there. So we're not accounting for our cap correctly on. And if we think about it, the reason the cap worked in the above budget section is because it was growing in the CAC . It's actually going down. So what we're gonna have to do is actually change our minimum to a maximum, which is a quick fix here. And we see that fixes our problem. And then the users are just going to be a budget divided by cack. Um, quick note. Here we see that dividing by zero gives us an error. Um, so I'm gonna put in a nice, clean up formula that will use from time to time just to make things less visibly disruptive there, where you've got an error? Um, always good toe, get rid of errors, even if they should be there. Right, Which they should in this case. So what I'm gonna do is Adam if is error, then just put a zero and instead. And if it's not an error than actually do the calculation wolf. All right, well, that's Gus telling us that it is definitely time to wrap up the driver section. Justus, as I weren't this is the most complicated and kind of initially counterintuitive modeling. We will do the entire course, So congrats on getting through it. The rest is gonna be really downhill. It's gonna Everything's really flows from this section. We did so congrats on getting through it. I'm sorry. It was a little painful, but we are now going to move on to getting to that revenue number, and then we can start working on consolidating this and putting it all in a presentable format. 5. Ch 4 Revenue and Unit Build: So now we're gonna move up to our unit Bills were jump over revenue and come back to that. Once we have our units, that will be our last section. We did the bulk of the work right in our prior video on understanding how many sign ups were getting per month. So let's take that line item in our assumptions and we're gonna do is some up, right? The users directly below from that month. So remember, all of the users we're getting from our sources are drivers of sign ups are going to are free trial. It's where we added that that element, right? They aren't just going to start paying us A soon as they come into the system, they're going to start paying us, presumably a month later. All right, um, some of that up. We're gonna need to know a few things, right? To get toe how many subscribers we have. We're gonna need to know how many new people come in each month. We need to know how many people leave each month. So we're gonna call the first conversion. Right? So how many of our free trial sign ups again from the prior month convert to subscribers in that next, We're also gonna add a row for churn. Call that monthly churn. You'll see that presented different ways, right? How maney your turn annually monthly is gonna be different. So doesn't mean that monthly and for each of these, we're going to do something similar to what we did below right. We're very similar formula that says we'll have a starting percentage and just won't be a fixed number, but it will have a starting percentage will have, ah, month over month gain or loss in that number. And we will have a cap as to how far we expect that to go. We've put a start month in here just so I can use the same formula. Remember troubleshooting principle. It's always good have as few formulas possible in your spreadsheet. I have separated these because there's gonna be a slight difference doing them. So we want to make sure we remember, um, that because our cap in each of these respects is going to be going up or down. We had this in the prior section as well. One is gonna be a men, and one is going to be a max, meaning right as conversion rate will always go up over time, presumably right will get better and better at converting. Um, and we will get better and better, retaining so sure and will go down, right? So the cap and each will be men for conversion. So it will only be that growth until you, until the cap is the smaller of the two numbers and on the turn, it will always go down by that number until we get to the number we say is the lowest of the max Until that growth rate number is lower, track that out. So now, to get to our monthly movement in users, we have our way to calculate the new and lost users in every month. So let's you'll see me setting this up to have a beginning of period a movement in period in an end of periods. That's our GOP and GOP. That's what that stands for. The beginning of any period is just gonna equal the close of the prior period. Ah, and you'll see that in month zero. This is one of those times when I want to start with a hard coded number, so I'm gonna put it in blue on. Let's say those were the 10 friends and family that I've guilt tripped into being paying subscribers prior to our launch. And then my new subscribers are always gonna be the conversion rate times the prior month's free trial sign ups less the number of those we lost from churn close of the month sums that up. Let's drag it out. And now we have a clear view in our moving our movement of subscribers month over a month. We now have our users, So let's look at revenue. Um, I am. This is kind of like the unit drivers. This is something that, as you evolve your understanding of your business, you'll want a model and more complex ways. What I would recommend to start with is have a single variable for your R p you or your revenue per user month. Um, what I am gonna add, though, as a way to help me benchmark that, and probably something that may at some point become a driver down the road is looking at my Tier three pricing and mix and give it an implied, blended cost of the mix that I've got here. Remember that this is really this is for a reference. I'll have my own R. P. You assumption here in a second I'm gonna do you use a some product function which takes two rows or columns and multiplies them against each other. And there's gonna be a blended right based on those percentages in those prices of blended monthly cost. And let's say most people are tier one very few people two or three initially, but then below. So that's not gonna be a driver of anything. That's just gonna be a reference for me as I'm sitting up my RP variable, which I'm gonna again do in the same way within the last view, which is starting a change and a cap. Um, so I'm gonna say, Let's just start off 200. Let's say that increases month over month, and it's never gonna be more than 4 50 And that number is gonna happen way out in the future, which point we'll have, you know, maybe five tiers will have other ways of getting revenue from our customers. So not crazy to think it will be equal to whatever our current highest prices, but also let's assume it takes us a long time to get there, right, as we kind of convert people up the chain and add new revenue sources from our users now, because we need it as a reference to some of our variables. A variable costs. I'm gonna just give us a revenue right, Which what I'm gonna do here to be a little bit more accurate, is I'm gonna introduce a new function average. What I'm saying here is in each month, I have, ah, blended revenue per user. Uh, and that is gonna be multiplied by the average users in that month, which is right where you started, where you ended. So that's why I have averaged over current month and prior month. Strike that out. And that's gonna be our estimate of revenue. Now we can drop down. We've got subscriber numbers, we've got revenue numbers, so let's fill in the variable costs, right. Pretty straightforward. And now that's all flowing through. And congratulations. So that completes our assumptions Page, which is really that this is the This is the most complex part of the modeling. Everything else from here is deriving different ways of looking at these business drivers right again, These as a summary. If you kind of go up and down, these are the categories and the line items that really represent every meaningful touch point of your business as a manager. What are all the things that you as a manager can change? For better or for worse and the rules of how they interact with each other, the rest of this course is all just gonna be on understanding that. 6. Ch 5 Monthly Consolidation: Let's take a look at how we set up financials. So we're gonna open up the next tab called Monthly Financials. This is gonna be really the translation engine that takes how you think about your operating business and turns it into a presentable financial statement. First thing you're seeing me do here is referenced the timeline from the assumptions tab. It'll be exactly the same sort is gonna set it equal to to that timeline from that over. The next thing we're gonna do is actually put down our income statement headers and everything I'm gonna use here are globally accepted income statement terms. We're gonna start with revenue at the top, and then the first cost item is gonna be our cost of goods sold. So these are the input costs to whatever it is you're selling. Um, next, I'm gonna break out our operating expenses into a few categories, and you'll typically see this done in a similar way. Although each business may be slightly different, you'll usually have some category for marketing and sales for a software company. I'm gonna break out development and then I'm gonna have my operating expenses, so that will be everything that I required operate other than marketing and development, and then I'll typically using other category for the things that don't quite fit into those natural three buckets. But investors would like to see these things broken out so they can kind of understand where you're spending your you were operating budget among those key areas of driving your business growth. Remember, I referred to this as as our translation opportunity, our translation page, where we take your operating assumptions and how you think about the operating categories of your expense line items in your revenue light Adams and translate those into something that's more globally accepted. So what we're gonna do here is take that list and I'm gonna use an Excel a function called data validation on and you'll see why. It's incredibly powerful, valuable way to accomplish what we're doing here in a second. But I'm just gonna copy all those headers to our assumptions Page. I'm gonna highlight them in blue. First of all, because I'm only again gonna want to type these once and what I'll do is set Are categories on the income statement page equal to those right. So again, remember the reason we We'll highlight something in blue is so that when we repeat it, it doesn't appear differently ever. We change it once. So if you want to change your marketing header to marketing and sales, for example, you'll only have to change that in one place. And then I'm gonna come back to my assumptions page, where I've got my blue list. Then add a column and I'm gonna call that designation and this is gonna be a tag, and I'm gonna What I'm gonna do is go through and tag each relevant a line that should be included in my financial statement with the category it should be included under. So the way I'm gonna use do that is going to use data, data validation, allow list. And then I'm gonna put the source of that list as this list of headers that we've got on the bottom of our assumptions page. What you'll see appear is a drop down menu of all of those listed items. The women of format. This is just like an input with yellow background blue because it is something I can change , but it is only from a structured list, so I'm gonna put a border on that when you're not highlighting it, it's not immediately obvious that those are dropped down. A border is a way to call out a drop down menu. And then I'm gonna go through my operating statements here, and I'm going to tag each item. Each cost line that we've calculated as one of those categories and what this is going to allow us to do is think about this once. Change it whenever we need to in the future. If I think a cost should move at some point from marketing up to my cost of goods, for example, just go to the drop down on the assumptions page and change it. But when we build our income statement, we will. This will allow us to have one consolidation formula that just looks at the designation tag and then sums it up under that category, and I'll walk you through how that works. But the power of that is is that you are not doing a lot of one off formula on your income statement, right? It is just one consolidation formula will use. So let's go ahead and fill out those tags. Remember, you're only you'll see. I'm only putting tags on those categories that are cost to revenue items that should be consolidated. My financial statements. I'm leaving anything blank. That is a build up to one of those numbers or a standalone number like users. Next, what I'm gonna do is split out my income statement a little bit further so that I can give my sub headers for, uh, profit along the way that anyone on your board or investors are gonna want to see the first is gross profit. This is the profit you make on an incremental basis with every sale. So the only thing years attracting is your your input costs. Now for you, for a software company that's gonna be typically very high. And then I'm gonna look at those operating costs and the sub profit There is EBITDA, or earnings before interest, tax, depreciation and amortization, which is kind of a mouthful, which is why people will use EBITA. This is really the number that investors will will want to know as Thekla assist representation of how your business is operating and how profitably it's operating. And then under cap ex, we're gonna show our free cash flow. So now we're gonna do the actual consolidation work. We've set the designation tags on our assumptions page and we've got the time line up above . So we're gonna use a new function called some if the way some if works is a lot like an if statement, it's going to test a criteria and then some. If that criteria is true, So the first thing we're gonna have this breaks down into three components the function I's gonna have a range. And this is what we're looking at to question whether is this true or not? So that's gonna be our designation column. Next, we're going to set the criteria, which is just gonna look left to the header the income statement header. Is it revenue, CAW, eggs, marketing, development, etcetera. And then the some range and the some range is gonna be the column off the month in which we are looking so month, zero for the first column than 123 etcetera. And a quick note here, just make sure that you are two columns are the same length, right? So that your designation your range and then the thing you're summing are the exact same start, row and andro. We're gonna set our anchors so that the designation stays fixed at all times. We're gonna let our criteria. So looking at the line item, we're gonna let that go down, but not across and are some column We're gonna want to float with us to the right with each month that we sum up. And now we have one consolidation formula that we can use throughout this entire page so that this is the same formula will use in every row except for those roads that were summing up the gross profit and even, uh um and so just copy that through and fill it out. And I'm just seeing this 20. I shouldn't technically have any cog in that month. Zero, right? I really want to pick up everything on month one month zero is my base month at the month rate before I actually start the model. So I'm just gonna tweet this formula. I'm just gonna go back assumptions Page, and I'm going to say if greater than zero, right, and then beneath our free cash flow, I'm just gonna go ahead and add a cash balance number, so I'm gonna do a corkscrew here with the beginning of period cash number a change which is just gonna look up to that free cash flow and a end of period balance. And here, I'm gonna have an input for the starting cash balance. Let's assume that we started with, say, 100 K got a grant or some friends and family financing. So that's gonna be the moving cash balance based on the operating assumptions we've laid out so far. So that takes us through the end of our monthly financial statement consolidation. And next up, we're gonna look at the dashboard. Were to do some more consolidation and start Teoh, draw out some of those key performance indicators that we want to measure our business performance with. 7. Ch 6 Dashboard Financials: we're now moving on to our dashboard. Um, and then what I'm gonna do first is I I definitely won't always have my income statement here, so I'm just going toe pull in Equalling. Right. So we make sure we re type but Equalling the statement we build on the prior page, I'm gonna bring in some of that formatting. And what I'm gonna do is look at this. Based on initially the year one by quarter and then I'm gonna look at it annually over the 1st 5 years. Now, as we do this, you'll see because we set up the anchors, the timeline anchors the way we did. None of this has to be written in stone. We can look at this based on calendar years quarterly. However, somebody want to see it. And you can make those changes very quickly. And what you're seeing me doing the background is playing around with formatting. Don't feel like you have to follow along with that at all. I attended just format as I go. But here on the dashboard is one place where formatting really is not relevant, is purely aesthetic. So once, once you're done with the modeling go back and play around formatting Teoh. Look and feel the way you want it. Teoh. I like toe set this up so it fits on one page and can be printed out on its own. You might want to format it, so it's easily copy and paste it into a slide. We're going to use some, if again, and we're gonna reference instead of entire columns in a reference entire rose. But the idea is the same. We will look at entire row of quarters and pull in any time the quarter equals what we've asked, it's equal for the year. We're gonna look at the entire row of years and pull in any number that equals the right year. We can do that for any of the categories we've put in. So, um, I'll show you what I mean here. So let's just work through it. So let's anchor it right again. Those are always gonna be looking at the quarters, and I am going to allow the quarter. I'm looking at the reference point of one here to change, so I don't want it to go down, But I do want to go across on that. I'm an anchor again, the entire revenue row to be the same. It's not gonna go across, but I'm gonna allowed to go down again. I'm just kind of formatting as we go here and you'll see a speeding up the videos. We just breeze through it. But feel free to the format as you see fit for yours. Use the same formula for a year. And the only thing I need to change is the top row. So instead of referencing that the quarterly row, I'm gonna drop it by one, right, cause our year is right below and then you see, I'm consulting by year. So a really easy way to consolidate your financial statements with this because this is looking a little bit big and unwieldy. I'm gonna turn this into thousands, just divide everything by 1000 and let's put a few metrics here at the bottom. So, uh, Hamisi wants to put it here. Revenue growth, gross margin and even, uh, margin revenue growth formula is just gonna be the revenue over the prior year. Revenue minus one. And we're gonna because we don't have ah, first year revenue, we're gonna just use our is error again. The margins are just over revenue. So gross profit over revenue even die over revenue gives us the margins and something that jumps out of the years that those were looking exceptionally high in the out years. Understandable because we haven't put much spot into those out years, right? A lot of what we've been doing is looking at the next 18 months before I show this to somebody, I I'm gonna want to understand that why? Those numbers are a little different from my peers. It could be a simple is, uh, we put something in marketing costs that people might put in cost of goods or or could be any number of explanations. But let's just take a look at a some point why those numbers are different. My users, right? I know what the beginning users are just gonna rebuild the corkscrew, and I'm gonna do a sum. If for all my new and lost referencing those assumption Rose. But it's the same principle is my same anchor Timeline could see why. That's just a first thing I do in any model. I always use it to consolidate. Go on, then we're gonna you'll see me using the same sum if methodology here, I'm not gonna walk through it every time. But remember that you can always pause the video on the upper left hand corner. You'll see that function for any given cell I'm and if you need Teoh to refresh your memory on how to do that at the free sign ups and you see me just copying the summit formula from our below user corkscrew. But I'm just changing the rose to align with the free sign ups Row on the Assumption page, and we now have a very presentable income statement. Some financial metrics are users and what's happening to those right? We have a very clear snapshot of our business that anybody without discussion with us, our understanding will be able to look at and judge its financial performance. 8. Ch 7 Dashboard KPIs: next thing we're gonna put on our dashboard are those key performance indicators. So what are those ways we measure performance beyond just kind of the standard financial measures the user count measures. Let's go level deeper and come up with some statistics that can really help us understand where our business is right and how it's performing. No, based on how we tweet, can change. Some of those operating assumptions in the beginning will want to use these as a way to measure success or failure and update and improve as we go. This is clearly Spencer's favorite section, so he's going to little tough to edit out. Apologies for that. The reason we're looking at end of period you'll hear the term run rate a lot that refers to Where have you gotten to by the end of the period when you have a quickly changing growing company, as most software startups will project to be, You'll you'll hear that term asked for by investors, for example, who just want to know where you've gotten two by the end of that period. So cash we've right. We built that cash into our income statement. So let's jump back and look at that. And we're gonna introduce a new formula. It's a new function. Excuse me called Offset and offset is you take a specific so reference and you move it up or down or left, right, a certain number of columns and rows. So what we're gonna say is that we're gonna anchor it to the month zero. And what I want to be returned is a step forward in in the case of quarters, right? I want to step forward if it's 1/4 3 months to quarters, six months. So we're going to say that we don't want to go any rose, right? We don't wanna go up or down, but we do want to go right? The number of months, times the number of quarters. So quarter one times three. And you could do the same with your years by just multiplying by 12 instead of three. Right? So in each case, where we're gonna do is move that sell anchored in month zero forward a certain number of columns which will be the quarter times three or the year times 12. And that will always give us the last month in that period and I'm just gonna also dio consistency gonna put our quarters in thousands as well. Now, I am going to take all of these end of period, uh, KP eyes and I'm just gonna add them here. So we have a single point of reference for each of those going forward. So we don't have toe hunt and peck for them in the future, so end of period churn, right? We know what our turn is because we've already got it as part of our assumptions page, our lifetime, it's just gonna be won over that. So the way lifetime insuring interact, it's if Archer and his X percent per month one over that is the number of months that are typical customer will will exist. R p you again. We've already done that. So it's just look have that reference our assumptions page customer, LTV. That's customer lifetime value. What that means is take the number of months that a customer is gonna be with us and multiply it by the estimated revenue per user in that month. So that's how much that customer is worth to us if nothing changes right. If we assume that they will be around as long as everyone else prior to that has been ends that they have an average revenue per month of what we've decided. And again, these these air KP eyes, right? So the way, the way they're meant to help you or outsiders look at your business is a very intuitive way to see how things are changing and improving over time. Right? So what What is impactful to your business here is turn right the way reducing that number is gonna have a very large impact on your ability to monetized customers. Because if your turn goes down, you'll lose less customers. Their lifetime will go up, their lifetime value will go up and your revenue will be increased. I'm going to usar offset throw these, fill them in, and now I'm just gonna do to others right on customer acquisition costs. So one of these is one we've already assessed, right? Which is what are we paying to get our free customers? I'm also gonna look at what we're paying to get our subscribing customers. And the way I'm gonna look at this is a little different from the assumption driver, because a lot of times you'll see investors and board members. Look at it this way, which is just take all of my marketing costs in a period right and divided by the number of users I acquired in that period. And this is going to be a little bit of ah, a little bit of on off number because of the delay, right? So in theory, we're spending money in one month getting those customers in the next month. But, uh, so you can tweak your formula to look at it, however, makes the most sense. This is just the way I like to show it on the dashboard, and I forgot to add conversion. So let's put that in here, too. That's pretty important number for again improving our customer acquisition costs. So if we get more of our free sign ups to convert into subscribing payers are implied, CAC goes down. This is This is what I would call a pretty standard list of KP eyes. Your business is gonna be unique. There may be parts of this moments of this that you'll want to do a little differently, but put those in the message board. I think this is a section to be as creative as possible and get to what are those statistics that are the most refined list of performance measurement tools? So definitely put some time into creative thought there and see what you can come up with. And if you have anything unique and interesting about your business, even if you think you've got a good solution, I encourage you to share it on the message board because I think in hearing how people wrestle with their own unique performance indicators will be really helpful to everyone else in the class. 9. Ch 8 Dashboard Cash Assessment: All right. So final section here, one of the driving purposes of this model is I want to understand how much I need to raise . And I want to understand how that might be sensitized if things go better or worse than I expect. Right? And that's really the crux of what investors will want to know. Um, And if they don't, you certainly should want to know, because over or under raising both of which can dramatically impact your financial success , right? If you under raise, you'll be raising again in Avery dire situation with a tight runway, and so you'll get bad terms. If you over raise, then you've eroded your capital. And I would not count on investors to be incentivized to get the accurate number, either. Right? So they're incentivized to put as much capital toe work as possible and get more ownership as a result. Uh, with that rational on why we need to know it. Let's look at what are some of those cash stats that we might look at understand the answer to those questions. The first thing I would look at is my minimum cash balance. What that's gonna tell me is This is the amount of money under the current certain circumstances that I need to raise to break even, right for the worst months of my current operating plan, That is my minimum cash balance. And what, that what that means is, if I went into this whole plan with that amount of money, that would be a zero. Let's start with minimum month. So we're gonna just return what month that is happening. So I could kind of have an idea of when the turn around point in my business is, When do I start getting profitable again? What is that kind of next milestone? I would say right is when do I want How far do I want this cash to take me is another way to look at. Uh, look at that. And let's set up a little test and you'll see why I do this here in a second. We're gonna introduce a new function for each each of these tests where we want to return a specific number. I'm gonna set up a little to row way of doing that. It's gonna be a little repetitive, but, uh, that's fine. It will be the simplest way to return us the accurate answer. So I'm gonna repeat my cash balance as a row here and below it. I'm just gonna repeat my month. Then I'm gonna use a function called each look up, which stands for horizontal Look up and I'm gonna look at both those rose and the wage. Look up, works. It's gonna look for a certain number in the top row, and it's gonna return. And Spencer is nicely reminding us that we are looking up a number in thousands. So let's make sure that the row we're looking at is in thousands as well. Otherwise we won't find the number we're looking for. So I'm gonna divide all this by 1000 so that it's in the same denomination as the minimum balance number were referencing on the dashboard. So back to our H. Look up. What we're gonna do is reference the number we're looking for, which in this case, is the minimum balance. Highlight the cells that we're gonna be looking at, which is both of those rows together. And then we're gonna tell the function which row to return once they found that number. Now, this is always gonna be too for me because I will set up individual tests as we're doing here that always have two rows. You can do this in a much bigger array and say, Give me the seven throw down below The number we found and then zero at the end is just indicate that it is looking for an exact number. I would always default to adding a zero there. If you put a one, it'll look for the closest number. There are reasons you would do that, but not what we're doing in this course. So again reference the minimum balance. Highlight the cells that it's looking at the top row for, and then tell it to return the second row, the row bite right below the number. Once you found it right. And we see here that we're returning the month in which that happened so that I would interpret that right as those two numbers, the amount I need to raise. And what milestone is that carrying me to write the next two numbers? Right? So the current balance, that's just you know, as we get historical numbers, I'm gonna want to know what that is. You know, 67 months into my launch without having Toe Hunt and Peck. So I'm going to add a roto our timeline here called historical. And that's just gonna ask the question. Is the current date less than today's date? And you'll see that today's date in Excel is its own function. Just today, open and open and close brackets, Um, and what I'm gonna dio test that's working. I'm gonna set the date to be prior to today's date, which is made October and you'll see a little one appears for every month that has already happened. I mean, the current month. So the first time zero appears, is October. I'm gonna add that to all of my timeline tags here. And then I'm a look at my new H look up test and I'm gonna say Find me zero and return me the cash balance when you c zero the way h look up works as you can tell is it doesn't look up every time it c zero. It looks at the first time in C zero. So, um, the first time one becomes a zero is in October. And so it's gonna tell you what is your estimated balance in October. All right, we're gonna set up 1/3 and final test here for it's gonna work very similarly for our runway. So how many months do we have left toe live? Right, So we have a certain cash balance. Ah, that is being lost every month. We don't have a profit. When does it all go away? What's the month at which? Under the current operating assumptions, we fail as a business, we go bankrupt. So what I'm gonna do is do ah, test number That just gives a one If we're positive zero if we're not right. So we're finding that month at which we first go negative in our cash balance. And again the wage look up. Works is it's the first time it sees that. So, uh, you know, you may have times when you if you're if you've modeled in something cyclical or you've got big capital investments, right, You may go positive and the negative again. What this is gonna return is the first time you go negative, and it's gonna tell you the month that that happens and then what I'm gonna dio is just to be accurate. I am going to subtract the historical months, right? So we don't want to give ourselves extra runway if the months already happened. So we're seeing under this scenario two. So those are your 1st 4 key stats. And now what I'm gonna do is run some sensitivity tables. And this will be the last Excel concept we introduce in this course, but a very useful one again. And we're gonna do it all on that minimum balance number. So again, intuitively, what is that number represent? It represents the amount you need to raise in order to accomplish this plan that you've built that you've constructed in this model. And what I'm gonna do is do a couple tables under, um, on those variables that I see as the most unpredictable. So the ones I'm the least confident in and have the biggest impact on my business. So the 1st 1 I'm gonna look at RPI you and conversion rate, and then I'm gonna look at initial CAC Verse initial churn, right? And those air kind of four variables that will really you know, if I'm wrong about them, we'll have a pretty dramatic impact on my cash needs. So the way sensitivity tables. Work is, they have to be built on the assumptions tab. So let's go back to our assumptions tab, and then we'll carry them forward to our dashboard. Once we're done in the upper left hand corner, you're gonna put the number you want to test our minimum cash number, then going down in a column, I'm gonna put one variable going across the top. I'm gonna put another, and these are the results that I want to see. So, for example, in that first, so I would set r p you toe 1 50 I would set conversion rate to 10% instead of 15 and I would return what? The minimum cash balances. If I changed both of those assumptions and it will go through the entire table making those calculations each time of the minimum balance, same concept for attack and turn. And now that we have the scenarios, we want to see the variable that we want to be looking at. In those sensitivities, I'm gonna go to data. What if and Simon enter the variable that appears in the roads? The variable that appears in the columns, right. So you're you're now locking this table into those assumption cells that you want to flex and you're calculating one thing you want to make. Sure if you see something like this where it doesn't actually run the calculations, um, if you have spreadsheets, where there are a lot of tables with a lot more than 3 to 3, all right, you might have 10 to 10. Um, right. And you have a compound, in effect of how many calculations are being run? Um, you'll want to set this preference the way I have it here, which is Go to your right. Excel preferences. Look at calculation. You see, I've got a setting on automatic except data tables for this. I'm just gonna say automatic. What that means is, any time I change anything I do or don't want all the tables to also update, I'm gonna bring those over to my dashboard. And that's just gonna b Ah, um, or formatted pretty version of the results we've already looked at and, you know, showed 3 to 3 on three here just to, and I've gone below and above our current estimate, what I would really recommend is getting a much more refined view of stepping up and down those assumptions and really looking at the downside scenarios. So realistically, if things don't go right, what kind of impact on your raise is that gonna be? And as you can see, right with these simple numbers, that could be a huge impact. And so I think having that understanding and maybe testing where you can those variables that do have a big impact and are very unknown is a good way to refine that number. That fundraise number that you'll need to be talking to investors about.