 So this week we'll be looking into creating what is known as a what-if analysis using Excel That's actually one of the very powerful things about Excel is that we can take all of this data that we're seeing here and we can Sort of now play what if but on top of that we can also actually give it a visualization as well So now these numbers for example just aren't arbitrarily sitting on the screen But we can actually play with them a little bit more Let's say for example, we're working off this cycle green income statement for a bike company Well, right now we have arbitrarily put in a few numbers like five hundred and seventy units at $2,100 now that's going to give us an that income of just a hundred twenty five thousand dollars total at the end of the year minus expenses What we would normally do is we'd come in here and we would just arbitrarily start to Put in numbers and so you can see for example, if I only sold 400 bikes I'm going to actually be in the hole $45,000 if I go to say 500 500, I do have some money well instead of me just going in and fiddling and fiddling and fiddling until we Create our break even point what we can actually do is we can start to utilize sort of this what if analysis approach on Say for example these numbers, let's say over here. We're going to just create something known as my break even analysis Now this break even analysis this is going to be my way of looking at What happens when I do change say for example all of my numbers I change my unit sold from 570 to 300 or something of that nature It'll let me just kind of take a look at each one of the numbers as they happen So again, we'll start with the basics. We'll start with that before our revenue I want to focus in on this summary section down here Nothing too crazy right now. Now. I'm going to just cheat and use some Format painting to kind of make this stuff look a little better So Now I've done that now. I want to say What are we going to do with different types of numbers? Let me just play around with a few different numbers one of the ways we can do this is we can actually Utilize that auto fill command that we've done in the past say I want to go up by 50 50 unit increments. So starting at 300 dragging down till I get to about 700 So I see I have a few different numbers. All right, excellent. Well Now what I can do is I can actually utilize that what if first? I want to go ahead and highlight everything from D4 to G13 all of my numbers this space right here I don't want to go and calculate this myself, you know too much effort way too many variables going on here What I can do. However is I can go into my data tab Inside my data tab I have this what if analysis and what I want to select is the option data table Now data table basically is going to say where is your input going to be coming from in our case? It's me coming from a column input cell before Now this before this is basically our way of saying this is the number that's going to be changing with These numbers being the changed if you will when I select okay You'll see that I get a number of different numbers appearing now all of a sudden so if I just highlight these guys for a second, I'm gonna take them and just give them a Number well, let me go ahead and just cheat no format painter here There we are So you can see immediately now if I only sell 300 I'm not gonna make a lot of money if I sell for 50. I'm barely breaking even versus 700 I've got my net income of 255 thousand dollars So now I can continue with this same effect I can actually take these numbers if you will and I can actually go one step further What I want to go ahead and do is I want to select Units sold revenue and expenses, and I'll go ahead and select unit sold from up here Reason why is I want to actually kind of create a visualization to this This is nice, but it would really be nice for me if I could see sort of the slope Of how these things are kind of creating from here. I want to go to my insert tab Insert tab allows me to once again insert all these different types of things including charts The one I want to select is actually the scatter plot, which just is like a line chart except well It is a line chart actually It's pretty much finding all those little points. So here's my x. Here's my y make that a point Here's my x. Here's my y make that a point x y. That's also it. That's another y make it a point I want to go ahead and select this scatter plot and for me I'm just going to go ahead and select the which one are you the scatter with straight lines Now what this allows me to do is you can see we already have now these two different Lines appear. We've got our expenses and our revenue being shown across different amounts of units sold and What our revenue slash expenses will be I'm going to just clean this up a little bit get rid of that title For example, and let's move it down here to right below our break even analysis There we are One of the other things I'm going to do is I'm going to do a quick little format to my Axis I've got a little too much going on. I'm never gonna you know, I'm never gonna make no money So let's go ahead and start with 600,000 down here and then for my Units will go ahead and say that will at least at minimum typically sell 300 and 700 will be our little capping point So I've got a nice little Look about it now As you can see sort of what we've got is this revenue blue line in this expenses Reddish line where they intersect that's actually known as our break even point and you can see it's somewhere in between 400 and 450 so right a little more than half a little less than halfway What this visual aid allows us to do now is we can actually see not only do we see our different Units sold I don't actually have to change that now all of a sudden all I have to say is let's mess around with my price So right now my break even point is I have to sell just under 450 bikes If I'm selling them at 2100. All right. Well, what if I knock that down? I go to 1800. I See a visual aid notice what happened towards my graph Suddenly my graph changed and I see my break even point suddenly becomes I have to make about 650 just under 650 To break even now if I swap it the other way if I go and say maybe I sell them at $2,400 Suddenly my break even point shrinks and now I only need to sell about 325 of them for 340 ish number So it allows me to now have this visual aid to see where my break even point will be