Hi everyone, welcome back. In this video, we're going to model the Quality Sweater Company and we're going to learn some new Excel skills, some slightly advanced techniques, and we're going to go through and answer these problems 1-4 that you see on the screen. If you haven't done so already, please pause the video and give the question a read. Again, imagine you are the consultant and first thing you ask yourself is, how does this company make money? How does it spend its money? Then again, the difference there will be the profit. Read the question, pause the video before we begin. Ready? Here we go. Start off with the header, put it up in A1. We can create cell styles and give it a heading 1. Then once again, what you like to do is in the beginning is go through and think about the givens, think about the inputs from the problem. Let's take the numbers out of the paragraph, put them in the spreadsheet, clearly label them, clearly format. Remember that the order they appear in the question may or may not be the way that we want them to appear in the spreadsheet. But let's go through it in order and we'll move things around later as needed. Here we go. The Quality Sweaters Company sells hand-knitted sweaters. Adorable. The company is planning to print a catalog of its products and undertake a direct mail campaign. I'm going to print a catalog and try to get a mailing response. Now, the cost of printing the catalog is $20,000. Right away, we're talking about costs and we have a fixed printing cost, $20,000. We're going to enter 20,000 and we will format that in a minute. We're not going to add the dollar signs in. Let's just format it to accounting, and we'll kill the pennies since we don't need to see the pennies for $20,000. So this is my printing cost. This is my fixed cost. Just by engaging the printing company, this is what I have to pay. Then becomes some other costs. Now, I have an additional cost, a variable cost, if you will, of printing each catalog, and that is at 10 cents. I have my fixed cost and maybe we have our variable costs. Let's label these out. Our variable cost, we have printing. We said this is 10 cents. So let's actually include this, Ten cents, of course, we want to show the pennies, we want to show what this is, and this is per catalog. Then we keep going. The cost of mailing each catalog, including postage, order forms, buying names from mail-order database, is an additional 15 cents. So I have to mail these things, make sense? This would be 15 cents, so let's add that in. Once again, notice I'm putting the units in here per catalog so that I can keep track of what the costs are. I have two variable costs based on the catalogs that I order, that I have printed. Next, in addition, the company plans to include direct reply envelopes in its mailing and incurs an additional 20 cents in extra costs for each direct mail envelope used by a respondent. Now be careful here. Maybe you've got these in the mail or not where it comes with a pre-stamped envelope and all you have to do is drop it in the mail. I am only incurring this cost for each direct mail envelope used for each order that I'm receiving. This is for, what should we call this? Reply envelopes, went a little long, so we'll expand column A. That's not a problem. This is not for catalog. Even though I print these, I may not get charged the 20 cents for having this. First, let's update this to 20 cents. This is actually per order. I only get this charge for orders. It's a common mistake here. You got to be very careful. Again, writing the units down will help you see the difference here. But it's 20 cents per order. Let's keep going. The average size of a customer order is 40 bucks. I need the customer order, average customer order. This is no longer going under my cost. This is actually money I'm making at this point. This would actually go under revenue and my revenue, the average would be $40. Let's put that in the book. We're almost at the end of the question. The company's variable cost per order, primarily due to labor, materials costs, etc. This average is about 80 percent of the order's value, which is $32. I printed all these catalogs. I've mailed them out, I've paid for the stamps. Then I sit back and wait, and then some come back. When they come back, it costs me 20 cents for shipping, for creating and shipping those reply envelopes, and then I actually have to fill the order. Now the orders will be all over the place, but on average, they are about 40 bucks each. It's just an average. Once they come in, I still have to fill the order. It says the company's variable cost per order. So they're helping you out here with this question to do this, so like labor and materials. Labor and materials. Again, this is a variable cost. So I'll put it under my four variable costs here. You might even imagine there could be more, if this were an actual business problem for a real company. It's kind of nice, they actually do the math for you. It's 80 percent of the order's value, which is $32. They're doing it for you. You can imagine you might have to do it yourself. The 80 percent doesn't actually appear anywhere in the problem. It's used to compute the 32, so do be careful just because the number appears, be smart about how you include it. Once again, I'm going to very clearly label this as per order versus per catalog printed. That will help me keep track of my totals. Last but not least, the company plans to mail 100,000 catalogs. What am I going to order here? This is going to go into my cost. Catalogs ordered, this is me sending them out the door. We said this is going to be 100,000. This is not money, it's just the number of catalogs. I want to develop a spreadsheet to model and answer the following questions. Think about this, I'm ordering 100,000 catalogs. Just to engage the printing company is 20 grand, and I have all these variable costs per catalog. If they come back, usually about $40 on average, I have some additional variable costs. I look at the first question, how does a change in the response rate affect profit? You realize very quickly, you don't have an actual response rate to sell. One thing we should probably put in here is a response rate. This is going to be the thing we play with. This is going to be the thing that we change. Again, I like to color these cells green. Green means go, green means play with these cells. There's no bad guess here initially. The one thing I would say here is do not leave this blank. I want to make sure the spreadsheet is working. So I'm going to completely make up a number. Sometimes I call this a dummy value where you're just plugging in a number for the sake of it. You're just plugging in a number. Let's use one decimal point to show this. But I'm going to make up a response rate of five percent. Once you send out 100,000 catalogs and you can estimate your response rate, you can then compute, the responses mean orders, your order number, your total orders. This is going to be a formula. I want five percent of 100,000. Of course, that means multiply. I'm going to send out 100,000 catalogs. I'm assuming a response rate of five percent, and that means that 5,000 catalogs orders come back. I have an average order of 40,000. Now I can actually start to compute my total revenue. My total revenue is going to be the number of orders that I receive times 40,000. This company makes $200,000 in revenue. Careful, let me show you the FORMULATEXT to just show where this formula is coming from. I have some dummy values, I have some formulas that are being computed here. Now, remember this isn't 200,000 in the bank. This is just the orders received at 200,000. I still have to process the order, and I also haven't computed yet my total costs. Let's start doing that as well. Where should I put this? I guess maybe it doesn't matter. You can already tell I'm running out of room. I might want to slide some things down, and this is the beauty of Excel. If you need some room, go off and get it. Let's compute our total cost. What's that going to be? Remember I have my fixed cost of just engaging with the printing company. Then I have, to add here, 10 cents plus 15 cents per catalog ordered. I'm ordering 100,000 catalogs, so we add that up. And then, don't forget, I also have variable costs of 20 cents plus $32 for every single order. Now, this is where it's nice I've computed the total order. This formula is getting a little messy, but hopefully you can see where each piece comes from. I might, again, just depending on interest and time, I might want to break this up into maybe my total fixed cost. My total variable costs, we take this formula and break it up into three lines. But hopefully you're okay with me. You can see where everything comes from. Again, I have my fixed costs from printing. I have to add 10 to 15, that's 25 cents per catalog times B14. Then I have to take my 20 cents and $32 to actually process the order. But then again, that is per order. Writing the units down really does help me figure out what the right answer is and keep track of everything here. Now we have it. We have our total cost of $206,000. We have our total revenue of $2,000. You can see where this is going. Pull all this up so I can fit it all on the screen. Let me show the formula for my total cost. That's a good one. Slide this over. Then last but not least, of course, the number that we're actually after is our total profit. This is what I look for in a spreadsheet. I'm always looking to see how the profit is calculated. A good spreadsheet will always have a very simple formula in the profit. It is, of course, the revenue minus your total cost. In this case, oh boy, we're losing $6,000 with these numbers here. This is where we get to play. The first question says, how does a change in the response rate affect profit? If I get a little more, let's say I had 10 percent return. That seems ridiculously high, but let's just go with it for a second. That's not too bad. Maybe now I'm earning a profit. I'm making $33,000. If I had somewhere in the middle, perhaps eight percent, I'm still making money. There's some point when I go from losing money to making money. I really love to see when and where that happens. Let me slide the question over to make some room on my spreadsheet here. What I'm going to do here is I'm going to make something called a data table. This is the skill that I want you guys to have here. This is a data table. This is a feature in Excel where you can play around with these numbers and you can go through and see, let Excel create the table for you, and get these values for you. Really, I don't know, there's a number coming later of three percent in the question. I'm going to use that as my first value. What do I want to know? I want to know my response rate and I'm going to start to that three percent, and then I get to pick what I want to show. So let's just increase by five percent each time. I'm creating a table for different response rates. I'm trying to answer the first one. How does a change in the response rate affect the profit? I'm making up these x values and I'm going to pick the first two and then drag it down, and I'll stop at, for no good reason, eight percent. These numbers here I get to pick. One thing you get to do is you can draw on your spreadsheet, and I'm going to say you pick these values. They can be whatever you want. You want to start at two, great. You want to go up to nine, great. You want to break it up into quarters so you see 3.25, 3.5, do whatever you want. Sometimes it tells you, sometimes it doesn't, use your best judgment. It's probably not realistic or very useful to talk about a mailing catalog and go all the way up to 50 percent. If you're like me and you get a lot of these things in the mail, they often get thrown out or recycled pretty darn quickly. But you pick the rates that you want to see. Then we're going to look at, of course, we're after a change in the response rate and how does it affect the profit, and we're going to look at the profit. Now, here's what we could do and just appreciate this is going to be painful by hand, but I'll show you how to do it in Excel. I could type into our green cell or response rate cell three percent, I take the value and it's negative 21, so I do negative 21,600, and then I can format this thing to accounting and we're all set. But you can't drag it down. This is me just typing it. There's no formula here. I would have to then come over to the cell and do 3.5 percent and then list this down and go negative 177,700 and this would be a pain in the butt. This isn't the world's longest table. But you can imagine just listing every single value and then typing in the profit for the y-value, to go ahead and make a chart, it's going to be pretty bad. What I want to do, first and foremost, let's get the formatting down and then I create the values. I really would like Excel to fill this in. This is exactly what the feature of a data table does. There's one little quirk to it, and that is I got to slide up the label for profit and I have to tell Excel where on my model the profit cell actually is. I call this cell like a ghost cell. We don't use it. It's not actually for us It's for Excel to figure out where the profit is, where I'm going to populate the y-value. I will tell Excel that, hey, my y-values, the second column in my model is coming from B20. Again, I don't use this number, this is strictly for Excel. It's a little quirky, but we're going to use it. Because I treat it as like it's not really there, I often put it in gray and I hide it. I make it a little darker than what I'm used to, I usually hide it pretty well. But hopefully you guys can see that I've made it gray and this is a number that I'm not going to use, but Excel is going to use it. This formula is saying grab B20 from the table. Here we go. Ready? Let me move this up as well just so we have it. You don't need anything on this one. You highlight the entire table, including this ghost row, this grayed-out link to the profit cell in the model, highlight everything, and here's the feature of a data table of Excel. I've seen people who are very, very good at Excel and not know how to do this. So this is a really nice skill to have. Head over to the data tab and find the What-If Analysis. You'll see three options, and one of them is called Data Table. Select "Data Table". We're going to make a lot of these, so we'll get very good at this. For this type of two-column data table, we'll create more complicated ones in a little bit. But for this type of data table, it's asking, what it's waiting for us to tell it is, hey, where is the column input? I want to fill in the second column, so I'm going to leave the first row blank. Really going to fill this in and saying, okay, I'll put in for each x value and I'll grab the profit that you told me up here in this ghost formula. But where am I putting in the response rates? Where are the inputs going? Because it obviously doesn't know how to read and it can't figure out your model. You just have to help it and say where are the response rate is going to get put into. This is where we click our green highlighted cell over in B14. Again, what we're saying is take all the possible x values, work your way down the table, plug it into B14, plug into our green cell. Then its profit, it's going to update and that will appear right here. Then just copy and paste it please into each table. This is how this works. Pause video here and play around with it. Make sure you understand how this works. It's very nice because once you hit "Okay", boom, we get the entire table filled out. Again, appreciate how quickly that happened and if I had to do this by hand, you can imagine there could be errors for me just copying things incorrectly or going through it, but you filled out the entire table. If you missed that, go back and watch the video to see that one more time. Maybe I'll kill the pennies, and again, we have a beautiful table here, I'm going to highlight the values. Unfortunately, I don't want to move or mess around with this ghost cell. So I'm just going to highlight the values. I don't highlight the gray cell. That's not really for me, that was for Excel, that was for the data table. Once I have the values, I'll go in and I will create a recommended chart. There's lots of good options here. For no good reason, I'll pick the first one. You can pick another one if you so wish. I'll put it, where can I put it? I'm running out of room here. I'll put over here for now. I would update this accordingly and I want to update this as a better title or really anything better than "chart title." "Quality Sweater Company," and we'll do "response rate versus profit." We'll hit "Enter". We can play around it, so it appears on one line and move this thing over as we need. But for now, it's fine. What's a good shortcut here? Instead of company, I'll just call it a company like that. I'll abbreviate company, great. Now it all fits. You can play around with trying to get these things to show a little better. But the point is, I lose money for awhile if my response rate isn't a certain amount, and then once I pass this threshold, looks like somewhere between 5.5 and 6, then this company actually starts to turn a profit. I have both the table and the graph, and I think with both of these combined, I've answered question 1 in full. How does a change in the response rate affect the profit? There's the first one. Just to skip ahead in number 3 for a second. If the company estimates a response rate of three percent, should it proceed with the mailing? Well, let's see. Three percent's back here, we're going to lose a lot of money. We're going to lose $21,600 with this three percent response rate. If that's the expectation, our advice as the consultants here would be to not go ahead with this business. Do not go ahead with the printing. We can answer actually one and three from the graph. Let's look at number 2 for a second. For what response rate does the company break even? Breaking even means that our profit is zero. We know from this sort of analysis that it's between 5.5 and 6. I'd like to do a little better and find the actual break-even point. Now, again, we could do this by hand. Say, is it 5.6? No. Is it 5.7? Again, I'm looking for when profit is zero. No. Is it 5.8? You can tell this is awful and annoying, and we could be here for awhile. Is it 5.9? No. Which way do you go? Is it 5.91? Is it 5.92? That sounds awful. What I'm going to do is use another feature of Excel, where you can have Excel play this guessing game, too hot or too cold, for you, and that's called Goal Seek. Once again, highlight that the response rate, the thing that you want to play with, and let's let Excel do all the hard work. Ready? Data, What-If Analysis, Goal Seek. Goal Seek is another box that pops up asking for some inputs, but now it asks for three things. It says, what do I want to set? What cell are you looking to fix? Whenever you have break-even, we're setting the profit to the value of zero. You can imagine, you can pick anything you want, any cell you want. But for break-even in particular, profit gets sent to zero and it says great. I'll go do that, but you have to tell me what cell I'm allowed to change. Here we are going to change our response rate. You plug in these three pieces and hit "Okay" when you are ready, and Excel will do that guessing game going a little too high, a little too low. But of course, it all happens instantaneously. It'll tell you when it found a solution. You say, okay, thank you very much. Click "Okay", it goes away, and there it is. It looks like it's 5.8. You're like, well, maybe I would have found 5.8. But if you remember, when we plugged in 5.8 exactly, we didn't get zero. If you look up in the formula bar, you can see Excel really did a good job. It found that it's 5.76923, it found more decimals than you'd ever find. The odds of you coming up with this percentage, which makes the profit actually zero, is slim to none. You can decide how many decimals you want to show, usually one or two is more than enough for any percentage. So 5.77 percent, or if you want to get fancy, get the whole thing. But 5.77 percent is the response rate that the company breaks even. This is our minimum sort of thing where we make the same amount of money as we did if we didn't even open the store. This is the number to beat in our response rate to actually turn a profit. We've seen Data Tables turned into nice graphs, which I'm moving around here, and we've also seen Goal Seek. These are two important tools that we want in Excel. Last but not least, this is a little bit of an open-ended question. How does the presence of uncertainty affect the usefulness of a model? Just because we have a working spreadsheet doesn't mean it's entirely accurate. I'll leave this as a little thought, one for you guys to linger on. But which one of these change? How trustworthy are you of this model? What would it take if you were the client, if I was presenting this to you for you to sort of go all in and make sure that you spend enough money, perhaps on advertising and marketing, to make sure you get past 5.77 percent to actually turn a profit? Lots of new things with this one. I like this example a lot. Go over this one once or twice more and make sure you can build this from scratch and understand how Data Tables work and how Goal Seek works. Those are our two new objectives in this video. Great job on this video. We'll see you next time.
