Hi everyone welcome back. Before, we did a very basic spreadsheet about the business of you. Money you make, money you spend, and what's leftover at the end of the day. Now we're going to take our first actual example of a small business, and we're going to create a spreadsheet model, and we're going see even more Excel functionality. Before we begin, pause this video, and just read the question on the screen if you want to type it out as well. But read it first to get a handle of the question. I'm not going to read the question during these videos. I assume you're going to pause it, but you can imagine any word problem. It might take a couple of times through the problem to actually understand what the question's even asking to realize that there are some subtle given in there that you might not catch them the first time through. Don't feel bad if you have to read it a couple of times. Normally though, whenever we want to make a spreadsheet model, I want to start off with a nice title. We saw this last time, NCAA T-shirt vendor, then we're going to apply a nice cell style to it. Let's apply heading one, and make it all pretty. And probably will merge and center it in a little bit. I don't know how wide I'm going to be at my spreadsheet, but we'll get there eventually. Assuming you read the problem, here we go. The first thing I'd like to do is write down the givens. What is it that I'm being told, and then what is it that I need to figure out? Randy, the t-shirt vendor, he's going to order t-shirts with the name of these teams and I have some costs associated to those orders. I'm going to call them manufacturing, and I have a fixed cost. Let's write down our costs here. I clearly have a fixed cost of $750, so we'll type in 750. Remember, I type it in naked, and then I go, and apply the formatting. I don't want the pennies, so I'll clear the zeros once they appear. Then the question is there's a variable cost per shirt, so eight dollars, so whatever the number of shirts that I order, I need to put eight dollars. So this is the variable cost. If I order 10 shirts, it's going to cost me 80 bucks. If I order 100 shirts, It's going to cost me $800, whatever it's going to cost, but it's variable. And this is at eight dollars format that as well. Maybe I'll leave this here because I can imagine the price changing perhaps by a quarter, by $0.50. I actually do want to see this. This is small enough, usually numbers under 10 or so, I will show the decimals if it's important, but variable costs of eight dollars, and it's not bad to remind yourself, this is for each shirt, it's not for a batch of ten, so we have variable costs here. Let's make this a little prettier like we did before. Let's bold, let's indent. We can put borders on in a minute. But I have fixed cost 750, and then a variable cost of eight dollars per shirt, that sounds better. The things that I need to know some way somehow to compute the total cost is the number of shirts ordered. Now I have to figure out the number of shirts ordered. Number of shirts ordered. Once again, I'm going wide on my column, that is totally okay. Let's widen the column a little bit. Notice I put my mouse between A and B. It turns into this sort of left, right cursor. Then I double-click to get the auto formatting that I want. We're going to play around this number a little bit. Maybe more, maybe less. You can imagine if you were consultant on this client, you might want to tell the person how many shirts they order. We have to figure that out. That's going to come later in the problem as well. We're going to need some more information. Sometimes when I have a variable, things that I could possibly change, I color-code it, I use green. You can imagine using anything you want. But green for me, it is going to be a kind of play with the cell to affect the outcome. Keep going. I have a fixed cost of 750 got that. The variable cost is eight dollars got it. Now Randy is going to sell the price for 18. Now, 18, where's that going go? Where should I put this here. I'm somehow some way you need to get my total cost going, something going on here, and I'll compute that in a second. If I keep reading, now I'm getting into my profit, how much I'm actually making. Now I have my selling costs. I'm going to try to keep all the givens together. There's no right or wrong way to do this. Maybe I'll drag this down. By the way, if you haven't seen dragging before, you highlight what you want, and you put your mouse on the top or on the bottom of the cell, and then you can move it all around wherever you want. And that's sort of the beauty of Excel is that when you have a problem, you don't know if you need to go back, and add some lines or move some things down, or all these things are like magnets. You can move them around wherever you want. Nothing is set in stone. I'm going start getting into my revenue here. My selling price is $18, but this price will be charged only until a week after the tournament. Then it gets reduced to six. It looks like I have a full price, and then I have my reduced price. There's two revenue things that I have to keep track of and you need to write them both down. Let's indent a little bit and let's write down my 18 for full price and I reduce prices six. Again, this is all per shirt so I just want to keep track of my units. I can imagine later why they change units on us. Let's format this to the dollar sign. Let's kill the pennies or maybe we'll keep the pennies because they're so small, I don't know. I'll leave it for now but then I have my revenue. Then there's a question here of course of how many do I sell at full price? Then how many do I sell at reduced price? This is something that I need to figure out and keep track of. I don't know where that should go. Maybe we'll put it here for now. Let's see number of shirts sold, say full price, I'll abbreviate this F P, and then I want number of shirts sold and then reduced price. You can write it out maybe you have a better way to say it, but these are things that I need to figure out, what is actually being sold TBD. Then once I know these things, then I can compute my total cost, I can compute my total revenue these will rise, then last but not least, what's my total profit? Now I'm almost going to work backwards here. One thing I look at when someone brings me a spreadsheet, whether it's working or not, the first thing I go to is not "did copy if the givens correctly?" You can imagine people copy things wrong, not "do you have the right formulas and calculations", I actually go to the profit formula. A good spreadsheet will have a profit formula. It looks something like this. It's going to be the money you make minus the total cost. It should be very simple because all the work should be in how is the cost calculated? How is the revenue calculated? I don't care how complicated your business is, you always want to know where your streams of costs are, what you're spending your money on. That can be a very large spreadsheet, a very simple one and then of course you have your revenue. Where am I making my money? Those should be shown and clearly worked out in other places in the spreadsheet. Do not over complicate formulas, they get very difficult to pass off to people. Remember, the whole point of this is for readability. To pass this off to a client, to your boss, to a colleague, and they can be able to absorb this spreadsheet and then understand what's happening right away. For a profit formula, I always want to see your revenue minus your cost. Let's show that formula using our formula text and there it is, so we have revenue minus cost. Now, of course, I have no costs, and I have no revenue so that's why it's showing zero, so let's actually go ahead and put something in there. These are pretty important numbers that I probably want to figure out. Let's put some dummy numbers in. Now did they tell us this? Here we go. The number of shirts ordered and then the number of shirts sold at full price. He's going to order 1,450 t-shirts, so it tells us how many he's going to order, and then he is also telling us that he thinks the demand for the t-shirts full price period is 1,500, so we're getting told these things. You can imagine playing around with these and seeing what the right number is. But the number of shirts ordered is 1,450, and then we have these shirts that are sold. He's thinking about he wants a little spreadsheet that lets him experiment with uncertain demand and the order quantity so these two things need to change. I don't really know how many I should order. I don't really know the demand. Let's just think about this for a second. I have this line in the way here. Let's clean this up. I don't know if I need this just yet, but I have 1,450. Let's imagine for a second you have the number of shirts that are sold. This is your demand, you have orders in for 1,500 shirts. Now you order 1,450. Again, usually you order them to have them on stock so you can give them to the customer first. You don't know how many are coming in, so you take your best guess and you order this many. How many shirts are left over? How many shirts are left over here? Here it would be zero, every single shirt that I order is going to be sold. In fact, I'm going to have, unfortunately a bunch of unhappy customers, 50 of them that aren't going to get their shirts. I really should maybe perhaps order more. Maybe you can see where this is going. I need to keep track of if I happen to order too many, I don't know what's better. Do you want angry customers that are going to complain about you on social media or would you rather overestimate the number that are ordered and just take a loss in the rest of them? I don't know. This is a good question. Let's just play around with this for a second, we can play around these two numbers. You're going to color-code them in green and then, I need to compute somehow the overflow, what's leftover, what doesn't get sold on that initial round and then what gets like six bucks for and that's I don't know, take a second, pause the video and see if you can come up with a formula for this. What is going to be the shirts that are sold at reduced price? Now remember this problem is assuming that everything gets sold, everything that I order is going out the door. I have nothing left over in inventory. I either sell it for the full $18 or I just sort of give it away at $6 per shirt. Come up with a formula that actually works for the cell, for me it's B14, wherever you are, pause the video, see if you can do it. Ready? Now you might have said 1,600 minus 1,500 and hit Enter and you say, great, it's 100. Well, that's true, if the number of shirts ordered is larger than the shirts that are sold. What if we go back to the initial number? What if I order 1,450? Now remember, how many do I have at the reduced price I certainly know how negative 50, and this is where you have to be very careful and clever with your formula. If you just subtract the two, you're going to have a negative number of shirts sold, and of course that's a little silly you can't sell a negative number of shirts. Somehow some way you need to say that the smallest this can ever get is 0. In this particular case, you order 1,450, they all get sold at full price, and then you have none leftover. All of a sudden, you realize there's something else going on here. I can't sell 1,500 shirts if I've only ordered 1450. What you need to do is introduce a new variable, and this is where we can insert a new row and actually call a new variable called the demand. What is actually demanded? Now you realize it's not the number of shirts that are sold at 1,500 it's what's demanded. All of a sudden, we have to be very careful about this, how many can I actually sell? If I have less than the demand, I'm going to sell 1,450, right? If I order 1,450, but I have 1,500,1,450 are going out the door. On the other hand, what if this is more? Imagine this for a second. What if I ordered 1, 600 and demand is still 15, do I sell 1,600 or do I sell the demand? I sell 1,500. In either case, and this is where you have to be very clever, we have a new function here. It's always the smaller of the two. In Excel, you can pick the smaller of two numbers by using the Min function equals Min, and we're going to take the minimum of the shirts ordered and the demand. This is a little bit clever. This is not entirely obvious, and it might take you a minute to think about this. Why is it the minimum of these two? Again, plug in numbers and you should see this working. If I order 1,600 and I only sell 1, 500 than the number of shirts that are sold is actually 1,500. The number of shirts that are leftover is 100 and this all seems to be working. Problem is you have to make a spreadsheet that is malleable, that is adaptable. If I go the other way and I have 1,450. Now, look, my spreadsheet is working, in B14 the number of shirts that are sold are only what I have. I can't say what I don't have, so I'm taking the minimal. I solve a problem with this negative, and there's a nice little way to do this when I want something to not be negative, It's a little counter-intuitive. I can pick the max or 0, stare at that for a second, pause the video and just think about what that means. If I take a number and I said a max of 0, it means if I'm negative, 0 will always be larger. It will always put 0, is this will never show a negative number. Notice when I do that now correctly, instead of a negative number that was there, I have no shirts leftover. Stare at this for a second make sure these formulas make sense. This is a nice thing, a nice little trick, but it's pretty common when you have ordering versus sort of demand. You're always going to sell the minimal of those two numbers, and what's going to be leftover is the difference unless it's negative and in that case you want 0. Here's two little formulas that work on nicely, and notice now when I really started experimenting or playing around my numbers, everything's working out I have 1,500 orders, I sell them, and I have leftover 150. Now I can figure out my total cost and my revenue. Ready? Here we go. Remember total cost, there's a fixed cost. Putting the order is going to cost me 750 bucks and then it's eight dollars per shirt. Now be careful, is it the shirts that I order, the demand or the number of shirts sold? Hopefully you see this it's per order, right? If I order it, it's eight dollars a shirt. Plug that all in. Again, just grabbing the formatting, let's kill the pennies. Let's show the formula here. There's where that's coming from, be careful, I have a couple of things to choose from. In this the number of shirts that are ordered against it's the number of shirts that I ordered. Then last but not least, my revenue that I make, now be careful, this is the number of shirts that I sold has two ways to make money here, you can sell the full price shirts, and this would be at my full price of 18 dollars and then I have my reduced price at six dollars and I have some revenue here, and I will also format everything correctly. I will make this there, and so now I have my total profit. In this scenario here, it's not the end of the world, to play around with this and I have 1,650 shirts ordered, I'm still going to make a nice profit, and you can play around with this and plug in different numbers. If I did 1,450, the client may come back and say, Is it okay to order more? Well, at 1,600 we're making almost 14,000 if you order less, you're actually making about the same. It's very small and you can imagine collecting a bunch of numbers and perhaps graphing those. In another video, I'll show you how to do that a little bit more effectively than just writing everything down. But for now, this is a very nice spreadsheet that you can pass off to the client. They say, what if demand increases? It's popular with two teams. What if demand decreases? And if you notice everything updates automatically, we don't have any negative numbers. Very clever use of min, max, total cost, total revenue. Let's show the formula and total profit. We can be informed in our decision and make good recommendations to our client. Play around with the spreadsheet, make sure you get it to work. Understand the Min and the Max, the functions and their uses, as well as these other formulas about where revenue, costs and profit comes from. Great job on this video, we'll see you next time.