Hi everyone, welcome back. In this video, we're going to model a new company called the Woodworks Bookshelf Company and we're going to look at some new skills in Excel and we're going to increase sort of the difficulty every problem that we do. So before we begin, please pause the video, read the question, understand where this company spends money, understand how this company makes money, but take a second, read the problem and imagine how you try to model this in Excel before we actually walk through it together. Okay, ready. Let's begin first. Let's add a header to our spreadsheet. So here's the Woodworks Bookshelf Company. We like pretty spreadsheets. So let's call that one heading one. You can certainly call it whatever you'd like. And here we have a company that produces two types of custom designed furniture. Either from cherry or from oak. And here we're trying to model possibly different projections or different increases in cost for each one. One thing you might not be familiar with here is sort of the board-feet, think of this as how many boards, how many feet of wooden boards that you need, how many pieces that you need to construct each bookshelf. So let's first think about how this company spends money and where the sources of revenue go. So let's look at our costs. Again, we have two items here. We're going to be looking at our cherry wooden bookshelf and of course our oak wooden bookshelf. So we have to separate the cost accordingly. You can see from the table that sometimes they are different and we have our unit cost here, we have 5 50 cherry and we have 4 30 for oak. format these as dollars, as accounting, we'll leave the pennies on so that we show everything. We'll center some stuff in the columns to make it look pretty. Perhaps we can also merge and center a little later when we know what our spreadsheet looks like. We're always going for readability, so that when we pass this off to either a colleague or the client or to our boss that they can read it, they can understand where the formulas come from, where the numbers come from. Anyway, we're going to take this table and probably put it nicely in perhaps a different format as well. So we have our unit cost for cherry and oak and then of course we need the required board feet. So maybe we put that in here, board feet. Now here we're building the same size bookshelf, it's exactly the same. So perhaps we can list it twice. You might get away with listing it once, but each bookshelf, whether it's cherry or oak requires 30 board feet and I'm inheriting some of the formatting from before. So we'll override that and just list it as 30 and there's our cost total. So we have our labor costs that go together, we have our materials cost that we can put these two together. So let's compute these two. So again, if I need 30 boards or 30 ft of boards, $5.50. How am I going to do that? I'm going to multiply two together. Cherry is going to cost 165. And if I do the same for oak, we'll do 30 times $4.30. I get 129. Perhaps I'll leave this without the pennies just for now. Although you can certainly show if you want, I tend to turn it off. So there's our materials cost. But of course I can have all these materials in the warehouse. But then I need to pay someone to of course, come put it together and this is where our labor costs are going to come in. We currently have our labor's cost here of labor required - let's put these numbers in. So, each one requires 16. Again, that's the same bookshelf. Just different wood, so requires 16 hours. And then the cost per labor. And the rate of labor is 18.50 an hour. And that is both for cherry and oak. And so we'll keep listing things twice. Put those two together. Maybe we'll split this up here a little bit again, you gotta be careful here. I need someone for 16 hours at 18.50 an hour. My total labor cost - two sources of cost here. I multiply, 16 times 18.5. And it's exactly the same. So we'll drag over the formula and get these two numbers. All right. So, we put these things together and we'll get our total cost. As you can imagine. Here is going to be what? materials plus our labor. Not surprising. The cherry, which has a higher unit cost is actually a little bit more for the company in terms of total cost than the oak bookshelf. Now, here's where the modeling comes in. We have anticipated annual cost increase. So what would be our increase? Well, we have 2.4% expected increase for our cherry. This is the materials cost. Now we want to start to build the models so we can see both numerically and graphically how the cost of bookshelves vary in the next few years. Things that I want to put down here are the anticipated annual cost of increase. So cost increases. You gotta be careful here. I have for cherry my materials. This is going to be 2.4%, increase. I have 1.7% for my oak. And then don't forget we also have labor floating around somewhere else. Where can we put this? I guess there's not really a good spot for it. I'll just throw at the end here, 1.5 as well. This will be my labor increase. Now, whenever you want to build a projection, we'd like to make a table. From the table, we will then make a graph. All good graphs come from tables. So let's build a table with our current costs and then we'll project forward based on these cost increases. Whenever we do this, let's label the table, we'll call it year and then we'll have our cherry and our oak. This is going to be our materials cost and then our labor and we're going to start with the current costs in year zero. So that's going to be zero and then we'll go 1, 2, 3, 4, 5. You can imagine we might go more or less. It doesn't matter as long as you get the table down, then we're good. You can expand this as we go. Let's put on some borders to make the table more readable. Let's bold the top label again. Don't forget to start at year zero. These are your current costs. So we have our materials cost, we have our labor and then maybe we can even look at our total, we'll add these all up at the end. I can get our total costs. Alright, so let's grab the numbers that we already have from year zero. Our cost to build a cherry bookshelf is 165. Notice I'm referencing the cells. I am not retyping the numbers. Cost to build an oak bookshelf is our 129. Our total current cost for labor is 296. Doesn't matter where you grab it from. Perhaps we even merge and center of the cells later to clean things up. I have to be careful here when I want the total, remember a bookshelf is either going to be cherry or oak. So I really want to split this up. What is my total cherry cost and then what is my total oak cost? So let's have two columns going. And if I build the cherry, I need to add the materials plus the labor, adding up the materials, plus the labor and then for the oak as well I'm going to do oak material plus labor and I get $425. Let's not show the pennies. Let's keep things as neat as we can. Let's add more borders, center all our numbers and let's see what happens. All right. So just be mindful of these formulas. So one more time. What am I doing here? The cherry number is the total materials that I calculated as of today. The oak numbers same thing. The unit cost times the board feet. Labor is being calculated from either column grab whatever you want, it's the total number of hours times the rate. And then our cherry would be the labor plus materials. And oak does exactly the same. Now I'd like to go ahead and use the cost increases from above. And let's sort of do one of these together. And let's see what you what you know for Excel here. So I want to take the number of materials, the cost for materials of cherry, 165. And then I want to increase it by 2.4%. This is my expected cost. And I want to do this. Pause the video, see if you can find the formula that will actually do this. You ready? How do we do this? Be careful. The mistake is just to multiply by it. But you can already tell it's going to be wrong. If you just multiply your cost is going to go from $165 to $3.96. That's clearly wrong. When you want to increase something you do one plus the rate of increase. One plus the rate. So we would do one plus 2.40%. You do that again. I'm expecting something a little bit more than 165 and in fact I get it at 168.96. We can show the pennies, we can not. This kind of passes the sniff test. That makes a little sense. Oak as well. Let's do the same thing. I take the number for oak and I'm expecting a 1.7% increase. So again multiplied by parentheses one plus and then 1.7 don't type the 1.7 of course - click it, reference it, hit enter and you can see it also goes up by a little bit. I don't know. I guess I'll show the pennies here why not? And labor costs. I'm expecting them to go up. It's given this number is given by 1.5%. So I click my labor, times parentheses one plus 1.5% - it's yelling at me because I didn't put the close parentheses on. But they'll do it for me. Perfect. Now I want to add these up and again these formulas are fine. I'm adding materials plus labor. There's no problem there. I'm adding oak plus labor. So what I'll do is I'll highlight both of them and I'll drag the formula down and now we have a very nice total. So our total columns are forming. One of the nice things about Excel is once you have formulas going that reference everything you can just drag these down. But there's a problem. So watch me - don't copy this first. Just watch. If I highlight everything, and say "Great, I have all my formulas. They're increasing, everything's great," and I drag it down for five years or beyond... Something weird happens. I'm getting these value errors and I'm also not even working for year two. It looks like the numbers are exactly the same. So whenever this happens don't panic. Double click on one of them and see what's going wrong. Let's go back to cherry for a second and let's look at year 2's value and see what happened. And it looks like when I dragged it down it took B18 - it took last year's number, which is good. And remember my goal was to grow that by 2.4%. But when I dragged it down it also moved the cell from B14 to B15 and that's bad. So I want the B18 to drag with me but I don't want cell B14 to drag - that should stay the same. Now there's a way to do this inside Excel. So first off if you get this error, just undo it, remember you can't really break Excel just undo whatever you need to do. So I'll hit control Z. Or command Z to undo. You can also use the arrows up here and sort of undo the mistake. So the way in Excel to tell a cell to sort of not drag with you is called absolute referencing and you go into the formula and you put a dollar sign and it goes before the row and before the column. When you do that, you lock the row in place and you lock the column in place. You can also turn on and off just the row or the column. For this example, I'm going to be super conservative and just put dollar signs on everything and what this will do, these dollar signs in the formula, it doesn't change the value. The formula is still working as the formula should. But I lock in the cell that I want so I can drag, watch this when I drag it down now I'll just do one to show you again. This looks right, it feels right, it's a little more than last year's and when I click, it the blue cell move down but the red cell, B14 stayed the same. That's what those dollar signs do and this is kind of the reason why we don't like to put dollar signs as we type inside the formula bar. These dollar signs mean different things. This is called absolute referencing. This means hold this cell in place. I will drag but you will stay and it's a wonderful tool that lets you drag and create tables very quickly. So what I'll do is I'll go ahead and put dollar signs on all my formulas here. So I'll go back and put a dollar sign before the C. And before the 14. I'll do it before the D. And before the 14. I'm not going to put a dollar sign inside my total because these cells when I add up the cherry and labor for each year, I want these to drag. I don't want any particular year to hold with me, I do want these to drag. So now that I have the dollar signs in, let's try to drag one more time down to row five and this looks right. Each number is increasing each year. You can randomly click on one just to check that it's working. It does some nice color coding for you. You can see that it's grabbing last year's number, dragging down as you wanted to and then grabbing the number from row 14 to increase by that percentage. So this table is wonderful. This is really the skill for this video to learn how to use absolute referencing to learn when to put the dollar signs in. Now we built a spreadsheet model that enables the company to experiment with the growth rates. The beautiful thing about this is if I want to change this 2.5 plug in and of course all the numbers update. So you can do whatever you want to play around with these. And I'll show you in another video how to do perhaps a better job of letting the customer see more values. Right now though, I like to color code whenever we have some cells that I want to play with that are there sort of to be changed. Then I'll color code them green if I have formulas that I don't want anyone to touch because I've had to figure out the formula and took me a while to get it. I will leave those sort of not color coded. And you can also color code the given. Sometimes people color code the givens as well. But remember for me, green means go, let's play with these numbers and see how the outcome is. So a company now can look at their five year projection and they can look at their total cost and they can have a sense of how their total costs are going to rise for both the cherry or the oak and then usually from here where this will go next is you will come up with a pricing scheme accordingly. So the company makes money. So we've done the first part. Right? We've built a spreadsheet model to experiment with growth rates in wood and labor costs so that a manager can see both numerically and graphically. We haven't done the graph yet, but that's coming next. How the cost of the bookshelves vary in the next few years. Everything here works great. Just make sure that you're at this point. Last but not least, is to create a beautiful graph. And we can do that a couple different ways. We can highlight the entire chart, head over to the insert tab. And then you can pick whatever chart you want. I like to look at the recommended charts sometimes Excel has a better idea than I do and sometimes it doesn't, there's nothing wrong with really any of these to kind of show what we're after. For no good reason, let's pick the first one and let's start to remind ourselves how to work with Excel graphs. Usually these graphs get sent to the client instead of the table or perhaps you're sending both. So often we want the graphs to have some sort of best practices of what things we want. Right now, there's a lot going on. But the first thing, hopefully you realize that the chart says chart title, please, please please don't be that person who sends over the chart with the chart title not updated. So update this to whatever we want. Let's call it, "Woodworks Bookshelf Company, and we can look at "Cost Projections," give it a name. We have the actual years. We can plug in the years. So this is pretty good. There's a lot going on here. You can see we're starting at 100 we're going up to 600. You can see the cherry and the oak lines are all different colors. If you don't want to show all these lines, like you just want to show perhaps the totals. Let's just show the totals. You can click on any particular line and just make it go away. Just hit delete. So maybe I'll get rid of these and just show the totals. If I wanted to show just labor costs or just materials costs, we can make a totally separate graph and have two graphs, but it just looks a little busy for my preference. Again, this gets into a little bit of sort of personal preference. Excel lets you change everything you want. If you want to change the colors of the line, you certainly can. This is looking pretty good. The only thing that I would add here sort of as best practices is the axes titles. And where's that? That's under chart design. And by the way to keep things clean. This is a very common question I get from my students. When I click off the chart. Notice the chart design tab is going away whenever you want to manipulate something, make sure you have the item selected. Some new tabs may appear usually for design elements, add chart element, add an axis title. Let's add a horizontal one or the X axis. And then let's add a vertical one for the Y axis. It defaults to "axis title," which is probably the worst title you can think of and then just update it accordingly. If we had units you'd update this. And then here I guess we'll have dollars. Really. This is total cost. And it's clear that it's in dollars but you know just because I add it in now I have a title, I have my axes, they are labeled, and now I can show the client the sort of projections. And again this is just the first step after this comes pricing and then we look at profit and put it all together. But this is just a good start. And a good practice spreadsheet for you to work on referencing or those dollar signs inside the formula that tell the cells to lock in place as you drag. The ability to make a good graph comes from a good table, and a good table comes from having these dollar signs or referencing absolute references inside. Alright, so, great job on this example. I'll see you next time.
