All right hi everyone, welcome back. Today we're going to look at another example for the BN Bookstore. So please give the question a read. Pause the video, read the question if you haven't done so already and when you're ready let's begin. All right, as always let's create a header file across the top. You can apply some cell style formatting to it. Once we have our header cell made we're going to read the question, take off the given information, and put it into our Excel spreadsheet model, ready? Here we go. B&N Bookstore, with many locations across the US places orders for all the latest books and then distribute them to its individual bookstores. B and N needs a model to help it order the appropriate number of any titles. So right away we're looking for the number of books to order. For example B and N plans to order a popular new hardback model which it will sell for $30. They can purchase any number of this book from the publisher, but due to quantity discounts, the unit cost for all books order depends on numbers ordered. Specifically it's less than 1000, the cost is 24 etc. So these couple of sentences here is telling you buy in bulk you get a discount and maybe you've seen this before. So let's take the $30. And this is going to be a regular price of the book and this is our $30. So we'll put that on a model. And then we have this order, we need to know how many to order some way, somehow we haven't ordered. Play around this number, this number is a little bit of the unknown, color that green. Then we have this table for costs for ordering, put that over here on the right. If the number ordered is less than 1000 the unit cost is 24. Keep track of how many are ordered and then the cost, unit cost. Pay attention here, this is important. Even though it says less than 1000 the cost is 24, if I buy 1000 the cost is not $24 I have to buy less than 1000. So what you do is you start the table off at 0. That's going to be your $24. When you get to 1000, you gotta read this very carefully, after each 1000 the unit cost drops 23 for at least 1000 copies. You order 1001 copies It's $23. Then if you order 2000 copies, the cost drops again to 22.25. Then if you order 3000 the cost is 21.75. And last but not least the lowest possible unit cost $21.30 for at least 4000, so this is important. Now let me put it up here just to clarify this, it's at least this many ordered. Notice the table has to start at 0 to get the unit cost of 24. A mistake people make here is that they start the table at 1000. But remember you can order less than 1000 and that cost would be $24. So this table here, the reason why this question is a little bit wordier the other ones is because we have this table, it would be nice if they hand us a table like this. But of course they don't, our job to create the table. Format the table, so everything looks nice. Keep it off here on the side, and we're ready to move on. B and N is very uncertain about the demand for this book and it estimates that demand could be anywhere from 500 to 450. So I need a cell here for demand. And this is also sort of an unknown variable, but they give us a range. So they're saying 500 to 4500. When we have these numbers we could throw in some dummy numbers. I'd like to put in numbers just to make sure the spreadsheet is working as I build off of this number. So I'll make up the number here, I'll put in 3000. For the order, say I order more let's say 3500. And this just helps me put numbers in, I call these dummy numbers just to make sure the spreadsheet is still working. Okay, also as with most hardback novels, this one will eventually come out as a paperback and therefore if it has any hardbacks left from the paperback comes out, it will put it on sale for $10. So I have my regular price and I have my sale price which is $10. Now I think I've gone through and used all the givens - at which price all the leftovers will be sold. So anything left over will be sold. So now I need to know how many are coming in as leftovers or what is my surplus? Now, this should feel familiar where we had a difference between order and demand. So in this particular case, just think about this for one second: I ordered 3500, my demand is 3000, so how many do I have leftover? Say well the answer is going to be to subtract and let's watch why this could work or it's going to cause some problems. This is fine, it's perfectly fine. You're right if you order 3500 and the demand is 3000, there are 500 left. What if I ordered 3000 but my demand was 3500. Now I should have no surplus left over. But this formula just having the difference here is telling me that I have negative 500 left over for the surplus. That's a problem, you don't want a negative number leftover for surplus. Yes, we're short 500 books but that doesn't mean I have negative 500 books in the warehouse. Somehow, someway I need to not have a negative. Now there's a couple ways to do this. We saw this before that one way to do this was to use the MAX statement and we can use the max of the two numbers of the difference or 0. This is being a little clever, this says if the number is negative, replace it with 0 and when you do that you can see it works. Now, if I order 3000 and I have a demand for 3500, yes 500 clients are not getting their books but I have no surplus left over and this is working. On the other side if I switched the numbers back so I order more than demand, now there is 500. So we saw this before with MAX in another example. Let me show you another way to do this. So like way number 2 with surplus and we'll just pick one of them to move forward. But let's use a new function. This could be a new function for you if you haven't seen this before, the IF function. Nice function that will test for something to see if it's happening and then you tell it what to do if it's true or you tell it what to do if it's false. So for here I want to test is my demand bigger than my order> And if that's true I'm going to put 0 and if it's not true then I can take the difference. Expand out the columns, we can actually see the formulas. So this should be working for both of them. So read this one more time, think about what this is doing. Its checking first if the answer is going to be negative, this says if B9 is greater than B10. So if the demand is bigger than the order then put 0. So let's see that in action here. So let's say my order is 3500 but my demand is 4000, checking is this bigger? Is my demand bigger? If so it needs the surplus to be 0. So this is the IF statement, it does the exact same thing as the MAX statement pick the one that you are most comfortable with. However you do it though, we need to keep track of the surplus because that's what I'm going to multiply by, that's what I'm going to sell for the assumptions in the story here. I sell all those at the sale price. Right, so now the question is how many copies of this hardback novel should be in an order from the publisher? We're trying to maximize profit here, so let's go through and look at our costs. We are going to look at our revenue and then of course we're going to look at our profit. And I almost work backwards here, if I do this right, my profit is always my revenue minus my cost, we'll play around with these two things. But really, let's think about how does this business spend money? So the only way this business spends money is I have to order the books. I have to order the books and be careful, there's two numbers here, demand and order. The number that costs me money is the order. So whatever I order, that's what it costs. Now, here I have 3,500. That means that I ordered at least 3000. So this should cost me 21.75. But you have to be careful here, like I have to use the table somehow. You really want to automate this, you don't want to have to put the number in by hand. But understand that I would like to Excel to come back with 21.75. This is another powerful tool in Excel, this is a very strong formula. This is a fantastic formula to add your catalog if you don't have it already, it's called VLOOKUP. VLOOKUP or vertical look up, looks up values on the table for you so we can pass this 3500 to the table and it will return the unit cost. So it tells you what it's looking for and in the order that it's looking for. So here's how it works, it says, tell me what I'm looking up, where am I looking it up, what column of the table should I return? You want the first column or second column, here we want the costs. So I want the second column. And then it's going to say true or false, do you want to be an exact match, like am I looking for exactly 3500 or false, do I want just the range? So let's try this one more time, ready? So look up value, what am I looking up? We're looking up the order, comma. Where am I looking it up? In my little table over here with the cost and price, comma. What column am I returning? Do you want the numbers from the first column or the second column? I want the second column, and then true or false, do I want an approximate match or an exact match? Here because 3500 and on the table I want an approximate match, so I want true. You can type true or just click the button, close parentheses and hit Enter. And look at that it pulls back for us as expected, 21.75. This function is amazing. And if you have tables, it does a really nice job of looking up values on tables. And it works if you change this number, let's say I did 3000, let's say I did 4500. You can tell it's working because it's pulling up 21.30 now. Let's format this a little better. And so we have VLOOKUP function. This is the only cost associated to us, I just have to order these books. And multiply this by the number of books ordered. Be careful, this is just the price. I have to take this number and multiply it by the number of books that are ordered. There's my total cost, built into this is a nice VLOOKUP function. So watch out for that piece at the end here, this is the price times the number of books ordered. Great, now, how do I make money? Be careful here, I have to be careful I have to sell what I have. So I don't want to just take the order, I can't say well it's the order times the regular price or something like that. Because here I've ordered 4500 books, but the demand is only 4000. On the other side, if I were to switch these numbers and play around with this and we saw this in another video, but you have to think about this for a little bit. If I order 3500 and the demand is 4000 I'm only going to be able to sell what I have in stock, I can't sell these missing 500 books, I have no surplus here. So the revenue is going to come from, remember this? The min of the demand and the order, that's going to be what I am able to sell, the smaller of the two. And then what am I selling those two for? That's the regular price, this is my fix. And you may want to play around with this before you do anything else and just make sure it's working in all scenarios. Here if my demand is bigger, I have no surplus, being able to sell 3500 that's going to be at a price of 21.75. And the revenue here is going to be the minimum of these two which is working the order times the regular price. Now, if in the case the numbers are swapped and let's say I ordered more than what's actually demanded. So let's put 3000 and 4000 in. I have 1000 left over I'm using the smaller number. And then we said we're going to add on to the surplus, we're going to be able to sell these at the sale price. So there's two pieces in here and again maybe I want to break this off. And the form is get a little complicated but there's two pieces here, I have my revenue from my regular sales and then revenue from the reduced, the sale price, hit Enter, you get both. Profit of course is your revenue minus your cost, format that also. And in this way we're making a profit of $14,800. At this point I think my spreadsheet looks great, it's working and now just being a little bit cosmetic and cleaning it up. Maybe we'll merge and center the header cell, show all the formulas, and we'll bold the profit. All right so stare at this, I think this is done. Make sure it's working and run through some different scenarios. Now let's actually go back and answer the question how many copies of the hardback novel should B and N order from the publisher? So the question is what is the right order amount that maximizes the profit? To do that, there's two variables in play, the demand and the order. What I'd like to do now is make a table to show all possibilities and we know the demand is from 500 to 4500. So let's put some of these numbers down 500, 1000 and then we'll highlight the two and drag it down to 4500. And because I know what the demand is going to be, this is probably what I'm going to order as well. So we can paste this number transposed. I don't know if you saw how I did that, we're going to highlight, come over here right click or do special transpose. This will take a row to a column and a column to a row. So just a little bit of a shortcut to make a table. I'll put some borders on this table and I will give these tables a header, so you know what I'm working with. Let's merge and center this, I can have my demand, and I'm told it's between 500 and 4500. And then on the side merge this as well. This would be my order amount and we can play around with the spacing here and we can turn the text vertically. You can have some fun typing sideways. Ideally I'd want to fill in the entire table. Now one way to do it of course, is to do it by hand. And you can say, okay, if the demand is 500 and the order is 500 then my profit is 3000 and I would put 3000 right there. You can imagine though to fill in this entire table is going to be pretty rough. I don't want to do it by hand. There's a nice way to let Excel do it. First let's format the table, so when we do it looks all pretty. And to do this, this is called the two way table. We've already seen a one way table where I fill in a column. Now let's see a two way table, while I fill the entire table. Like the one way table, I need to tell Excel what to fill in the middle of my table. And so this is going to be what I call the ghost cell and this is going to be the profit. I want this cell to populate the entire table. And this cell is not really for us it's for Excel, Excel needs it to work. And so I often gray it out to recognize that I don't need this, but it's part of the requirement to use the two way table. All right so, this is going to tell Excel where in our model the cells are going to come from. All right so let's do a two way table, highlight the entire table, head over to the data tab and then what if analysis and then data table. Now I realized that the data tables little dialog box didn't show in the last recording. So I'm just going to paste a little image of it over here on the side, but this is what you should be looking at. It's asking you for two things. It's asking for your row input cell - now this isn't it, this isn't actually the box, so just bear with me here. It's asking you for the row input cell. What this means is out of your table of the header that's a row. So rows going across, which is really for us the demand. Now obviously doesn't know it's the demand. But of the top of your table, where is that cell? Where am I plugging in the 500? Where am I plugging in the 1000? Where am I plugging the 1500? And on your box here, what you want to click is, at least for me B9. Now again, this is an image because I know it's not showing when I record for some reason but you want to click B9 into here. And then the column input cell says, okay now your table has a column with inputs, where is that in your spreadsheet model? Right, Excel can't read, it can't match these things. So it's asking you of your columns, which for us is the order, where is that in your spreadsheet model? If you color code the numbers that you're playing with, then it's easy to find these things, so it's the green one. So in this particular box and the column input cell you're going to type B 10 or click B 10. So one more time on your data table put in here. B 9 probably with the dollar signs and B 10. Again, click these. Do not type it. Don't draw like I do, I'm just trying to show you for the box so I'll do it now you will be able to see this unfortunately cause the little dialogue box doesn't show. Data, What if analysis, Data Table, row input is my demand. So I'll click that again. You can't see this. Column input is the order I hit OK. And it fills up the entire table. Notice the value that I was expecting at 3000 is right there. If you get these little hashtags of course it just means the column is too small. So just expand the column so you can see the numbers. Don't panic there. You have all the answers you want. So you can control as a manager of the situation, you can control what you order. So if you think that the demand is 2000 then you can find the profit that maximizes and here for us and here for us if we think the demand is 2000 then clearly the best is 2000 and we can find all these pieces. Sometimes if you want to color code just to make things pretty, this is again just cosmetic, we can do some conditional formatting, we can throw some color scales on here and you can see where the most money is to be had. Of course as demand goes up then my profit will go up as well. And you can see at each individual column, What the demand will be. Now we can control, unfortunately not the demand of the clients, only the order. So really as a manager we'd be looking across saying, Okay, if I order 2500 books, you know what demand am I shooting for here. I would like to try to sell all my books and have these demand equal to my order. But if it doesn't I can at least budget accordingly and see where I lose money where I make money where I break even in this particular example, I'm not going to answer with a fixed number. Instead I'm going to hand back a table and say what, you know budget wise, are we comfortable with ordering? And I think this table is good enough for our purposes to answer this question, to have an Excel model to work with two way tables to use our IF functions, to use VLOOKUP. Those are the three new things in this example. Make sure you understand how they work: IF, VLOOKUP, and then two way data tables and then conditional formatting for aesthetic so that it looks beautiful. All right, great job on this example. We'll see you next time.
