Never Out of Stock: Create an Inventory Plan in Five Easy Steps | Candy Boerwinkle | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Never Out of Stock: Create an Inventory Plan in Five Easy Steps

teacher avatar Candy Boerwinkle, Superwoman.

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

5 Lessons (33m)
    • 1. The Item Master

      6:40
    • 2. Demand Plan

      6:29
    • 3. Reports and Data

      5:34
    • 4. Fun with Formulas

      8:38
    • 5. Format That Data

      5:09
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

238

Students

--

Projects

About This Class

70e9b481

You have at least one product. 

You have a listing and sales are coming in. 

Now what? 

You need a Demand Plan! 

In this class, I'll show you how to easily build an inventory management system using spreadsheets and simple formulas. You'll be able to forecast out of stock dates based on sales velocity and new order dates. We'll format your inventory spreadsheet to show you when you are in a danger zone and you never need to worry about being out of stock again!

Meet Your Teacher

Teacher Profile Image

Candy Boerwinkle

Superwoman.

Teacher

Hello, I'm Candy.

I love all things related to data and information. 

I've been working as a data and research analyst for various companies and universities for the past ten years. I have a masters degree in Library and Information Science which also means I'm a bibliophile. 

I am solution-oriented and optimistic. 

I'm also Superwoman under these clothes. 

See full profile

Class Ratings

Expectations Met?
    Exceeded!
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. The Item Master: Hi there. Welcome to the first lesson and never out of stock. Five easy steps for inventory manager. My name is Candy and I'm gonna walk you through setting up on inventory management system using spreadsheets. The domain planning is key to any business selling consumer packaged goods. Now, whether you created them by hand or you manufacture them, you need to know how many products you have in stock and yourself velocity. So that is how many units you're selling per day. This is important because the worst thing that can happen is for you to go out of stock. If a consumer goes to buy one of your products and it isn't available, they'll likely turn to another brand and you might lose a customer forever. I worked with several companies over the past 10 years to prevent us from happening. We consistently analysed trends and forecasting order dates. This is not as complicated as it might sound. I created a simple inventory management system that works, and the best thing is, as long as you keep it up to date, you won't need to purchase on expensive database system until your team grows. You have 10 or 20 different sales channels and billions of dollars in revenue or coming your way every week, and then you could just retire. Right then you might want to look at something more expansive and automated. So this system has five simple steps. You create an item master, build a demand plan, add your reports tabs, connect your formulas to your data and conditionally format the results so you can see them quickly. In this video, we're going to create a night. A master. You'll need to have some type of spreadsheet software. If you have a team, you might want to use Google Sheets. Since it's easy to collaborate, build on for other people who's gonna need the information. Plus, it's stored in the clouds. You never have to worry about losing your data. Of course, you can also create it in an Excel spreadsheet and save it to a drive in the cloud as well . The night and master is essentially your main database for all of your products. It holds all of the data points relevant to each item you sell. The most important piece of data for the purpose of this class will be the skew, which Dance for stock keeping unit. It's basically the unique identifying for your product, like its Social Security number if it were in America. So other things that are handy to have here are the U. P. C. That is the universal product code, which is a 12 digit number and is represented by a scannable barcode for point of sale purchases. You could also have specific dimensions of your product for warehousing and orders. Hostages, weights and measurements. Length, width, height, etcetera. You can also have an order code here if you place orders with the manufacturer, you can include any other point of data that is relevant to this product. So I'm gonna be using Google Drive and opening new spreadsheet. Um, I'll title it. I don't master. And here's for I'm gonna create a master, A table of all my products. You probably have something similar already. If so, go ahead and use that. I'm so Lucy will have the skew. Um, I can go ahead and put UBC here. We can have a description of our products dimensions. Let's put the weight of our product, the height, the leak, um, maybe have a a case amount an order code, which is like a part number for the manufacturer and an M O que, which is a minimum order quantity. So we'll make a some excuse, so we'll just say, like, product one product to and product three you PC is that 12 digit number? If you have one, you put it here. Not all products require it, but you might need it. Might be useful. Ah, description is kind of what is printed on the receipt. It's what the consumer sees. So we might see you might say, like incredible product. One amazing product to I can't live without product. Three, uh, double Click this so we don't over laughing other calls. Maybe the way of my product is in, uh, how that will say product. One is £2 product. Three is or two is £3 can't live without products. Three. And that one is £5. And then we can have high links if we order in cases. But that here, the manufacturer code and the minimum or quality that's for for you. Um, so when a title this tab down here by the master and then we'll add a little bit of formatting. Just so it's easy on the eyes. So let's put this as blue and we'll make it bold. And we want to freeze this top line, especially if you have, like, 100 products. Because when you start scrolling down, see, everything kind of disappears. You don't have the headers. So they just appeared of view and freeze, and we're gonna freeze one row. You can freeze many roses you want, but I only want to freeze that roast. What states? And then we'll put some, um, alternating colors. You change our header to be a different color. We want it to be blue, and then we can change it here. So let's say we have, you know, 20 products. So instead of going a one here to I four, I want to go. So I 50 it done? Because I know I'm gonna essentially fill this out and add more products. Um, so that's it. You finish the first step. Now, Step two is building your demand plan template. So when you're ready, head over and get started. See you there 2. Demand Plan: Okay, Welcome back to listen to in the never out of stock. Siri's I'm candy back. We were gonna continue on our journey to inventory control. So in the first step, we built a simple item master, which is a database of relevant information about your products. Now it's time to build a demand plan. So exciting. So the plan is the main template they're gonna be looking at every day. Demand is a code word for forecast simply means how much in demand. Your product is based on yourself velocity, which can vary depending on seasonal shifts, marketing campaigns, etcetera, for example of its winter. And you're selling coats. You're likely already be expected. An increase in sales over the summer months. I'm most importantly, you want to have enough form jackets and stock to cover that demand, right? So obviously, without inventory, there can be no cells. So forecasting is gonna be key now in a man plant. IPL it. Here's where you'll be pulling a data from your reports and calculating window place orders . Or maybe you need to run a sale because you're over sucked. Now to build this thing, you're going to have some data points such a skew inventory amounts and sells velocity, etcetera. But there's three essential sections inventory cover and manufacturing need the world of consumer packaged goods. This is referred to as a supply chain. Goes from the manufacturer all the way down to the consumer, including shipping and orders. So let's head over to our item master and create this template. So you'll recognize this from from the first lesson. So I don't master that we created with our awesome products 12 and three. And now we're gonna build upon this by keeping our item master spreadsheet inside, uh, and make a new tap down here by clicking that, I'm gonna call it Dash and move it over front of item Master. And I'm actually going to re title this workbook to be demand plan. Okay. All right. So in artemia plan, we're gonna have it's que inventory open orders, and we're going to do a total inventory which calculates both of those together. Green have a cells velocity, and we're just gonna look at a daily one. But you can look at any amount that you want, Uh, days of cover and out of stock date, we're gonna have, um, Elsie Target inventory in days. Just gonna be, um How about you want to set it amount to order in days, amount to order units believe time for your orders and the date toe order. So what I actually want to do Let's make it on road to, um and we're gonna section this out. So from skew all the way down to total inventory, his top row here gonna emerge thes we're gonna call this inventory, and we want to put this in the center and then sells philosophy and days of cover all the way to out of Stock date. This is gonna be our cover section. Put that in the center and then over here is gonna be manufacturing merge these manufacturing and center. So let's do these air. All of our headers gonna make them bold and yeah, let's just do 12. 14 way today. It's a little bit bigger. Let's wrap. Hm. The ones that we need to so we can read them. Now we're gonna make ah the section a little different just for formatting. We'll go this darker green. We'll make the ones under it a lighter green. Let's make cover uh, purple with a lighter colored purple underneath. Get a little bit player and manufacturing. Let's make it orange. Actually, no, We're gonna make it blue because we're gonna use orange later on to highlight things. All right, So, blue. There we go. Um, that will dio gonna freeze this so that when it moves, we won't need to worry about losing or headers. We're gonna freeze to rose this time instead of one. So what goes up and down and we're going to at our products, which were we can go over here out of master to remember. Just copy them 12 and three back to our desh paste, the mint. And this is we're going to start here. So essentially, we can go ahead and at our alternating colors to really helps to have those want to go all the way to l. And I'm only gonna do three that you could do. However, many, uh, products you have, uh, didn't work. Do it again. Oh, it only did so til three. And I meant to do it. L life. That makes sense. Why Must will do six that way. You can see. Okay. All right. So we've made our demand plan template. We put our the Resch use here, and that is the end of the second lesson. So we'll head over to the 3rd 1 and put in our reports tabs and kind of bring the information over and start doing some calculations. All right, see you there. 3. Reports and Data: Okay, here we go with step three and then never out of stock. Serious. So in the 1st 2 videos, you created an autumn Esther, and you built a demand plan. Template waited. Go. You this video, we're gonna add in some data, uh, favorite part. So reports are essential in your forecast because they tell you what you have, and then you can make decisions on what you need. If you're going to need your inventory report, which maybe you create yourself for you download from a warehouse. You also need your open purchase order report, which we lovingly call in the fuel. The oboe are which comes from your manufacturer. Your open order report and you need yourself state a report. And if you have multiple channels, you add multiple columns and drop in those reports. Okay, so here we go back in our demand plan, which we created of our item master here. Solar products, um, are dashes our template. And so we're gonna create some new tabs down here. We're gonna Adam one that says, inventory for inventory report. You're gonna add another one that says, um oh, are open order report and another one sales Okay, let's move these So we can keep some here. All right. So you you might download these. And if you dio, you'll get them from the same place. You just export them, and they look the same every time. And that's great. You just download it. Uh, copy it and paste it in here and all your data will just be here in this place. Um, so that's what that is, ideally, what we want to do. However, since I don't have a report, I can do and kind of show you to that. I mean, if it was Amazon specific or eBay or some other marketplace, but we're just going to make it up. So we're gonna have essentially, we need a Oh, let's not call in the tour. We're gonna call it available. You might also have things like incoming or inbound if you If this is coming from your warehouse and it's telling you what shipment throwing the way, um, you and you might have something like unsellable inventory and you might have a whole bunch of columns, especially that Amazon report. It's gonna be a 1,000,000 columns, but we have our skews. What you're gonna be the same in our report and everywhere. So let's put them in our inventory report again. Normally, just gonna copy and paste Listen and available is gonna be so, product one. We have 300 units and part of two. We have 200 units and product three that we can't live without. 700 units. Um, so let's copy skew and products here. Go to the open order report, paste them here and let's say, um, amount on order here, however that's gonna be stated in your report, could be something different. Essentially, that's what it is. So let's say you have 500 units on order of product one, 200 of product to and maybe you have 400 units on order for product three. All right. And you again, you might have a bunch of other columns and data and other things in here, but these were the two main things that we're looking for in this report. You probably would have an e t A. When this products going to be finished and shipped, you might have the date that it was ordered. The date it's going to be completed. You might have a product number, which we had in the item. Master, you might have a whole bunch of stuff, but for the purpose of this class, to make a simple the two things you really need rescue on the amount on order. All right. So, Leslie, we're gonna go to sales hopes. Actually, let's take our excuse again. Put them here in sales. We always wanna have it as far to left us possible. And you understand that later when we are pulling in, Ah, putting in our formulas. OK, so we're going to see the daily sales column. That's what we're interested in. And you might have something like you might be able to run the support as a daily cells report, or you might be able to run it as a 30 day or two week. If you can put in a date range, you can use whatever date you want. But so here. Let's just say we sell three units a day of product one, and we saw ah, 20. A day of products to and the one that people can't live without. Um, we sell, I don't know. 30. Sorry. It's good number. All right. Okay, so we didn't copy and paste the men, but you can. So wherever you're downloading them again, you just kind of dropped the data in here, which makes it really simple. And because all the columns will be the same in the same place every time you put the report in. So you just come in here, delete everything, and copy and paste. It'll back the new report back in. All right, we have our all of our are three reports in here. And the next step that we're going to do is going to be calculated our formula so that this data from all these reports is gonna pull into here, and we're going to start to see all of this change. All right, so head on over to, uh, video four lesson for and I will see you there. 4. Fun with Formulas: Okay. Welcome back to the number of stocks. Siri's Step four, Brianna breeze right through this thing. More than halfway. Done. Built out of master Demand plan at it are raw data. Now we get to the really fun part which is create the formulas. Tell us what we need to know. All right, So formulas are so much fun, and they're super easy. We're gonna be using some really basic ones, which is just be lookups, essentially telling the spreadsheet where to go in what room? To find what we need. Thumbs in division. Easy, breezy. So let's get started. All right, back here in our demand plan. What you're gonna you know now. Very well. So first thing we need to do, here's our excuse. And we need to find all this information out of the reports down here that we created before s we need to know how much of the tour we have of product one. We're gonna tell the spreadsheet that we're gonna do a calculation by starting off with an equal sign. All that does is tell it that so equals, do you look up. So we need to look up this information from somewhere else where what we're gonna look up is product one is the skew. So look up this point of data in our inventory report calm A to B. So we have to. And the first column that you select is gonna be wherever you're skew is so make sure that's accurate and it's only two points over two columns over is where we're gonna find the data. So when the spreadsheet finds the skew in in the first column and then you tell it which position toe find the what you wanted to bring back column two and then hit false and closer presidencies and done all right. So far, one is saying from the inventory report, it found product one and it went to call him two. And there's 300 there, and it just pulls it back, puts it right there. Now we're gonna drag it down and do the same thing here. Open orders. How many? How many units do we have on order of each product? Let's find out. Let's tell the spreadsheet. Go find this cube and our open order reports. And the first column is Sq. Where the data lives is in the second column. So put it to and Paul says, Bring back exactly what's in that column, which it did Pull it down. All right, Now, I need to sum this up. How much toe Total inventory we have. We're going to sum up inventory plus open orders. So some is the key here. Open and close parentheses and bring it down. All right. It's something. The right things. That's why it's highlight. Okay, we're going to have you. Look up here. Go find product. One Frenchie in the sales stuff In column A and then in column B two places over from column one. Tell me what is there? All right, So, Product wanna selling three units a day about the other products? All right, here we go, and it matches on. I always go back in double check. Make sure that I'm pulling from the right place. All right. How many days of cover is this? That's easy. So we want to go. Our total inventory will wanted to calculate how much total we inventory. We have divided by what are daily cells. Rate is so we have 267 days of cover product one Awesome. Oh, No, we'll be in trouble. We only have 20 days of cover product to 37 days of cover product. Three. Well, we want to know what is are out of stock Date then if this is how many days of cover we have Well, first we got a We gotta know what today is. So there's a formula for that ray and it is easy Typing equals today. Open, close parentheses enter, and it's gonna return today's date. And every day when you go in and you open this demand plan, it's gonna be whatever day it is So perfect. So, hey, spreadsheet tell May from today some select today state. If I add how many days of cover I have, how when am I gonna go out of stock and the spread? She says, Folks, look, it didn't work. When I drag it down, let me tell you, wife. So when you dragged things down, it automatically moves down to the next one so great and moved down to this one, which is right. But then over here it moved down to this one, which isn't so we can't always dragged down. So be aware of that. So Actually, we want to stay here. And there's also a formula where if you wanted to do that, you could and you just put a dollar sign in front of him. And to that total spread, she when I drag this formula down, stay there. But move here. So easy peasy. Okay, so now we know when we're gonna be out of stock. What exact day? Unless these sales change. So if you come in here tomorrow and product one has shot up because you did a marketing campaign to, you know, 20 units a day Well, that's going to change all of this. And that's the beauty of uploading your reports every day and monitoring your cells. If they drop or they increase, then you can plan and know that the so strange and you need toe make a different plan. So our target inventory in days, let's say our target inventory and days of 75 days, Ideally, we would like to always have 75 days of stock. All right, so then we take our target inventory days subtracted from how many days of cover we have right now inventory and try to get down. Right? So this is showing us that, um we're well over 75 days of cover. We don't need to order anything. That's why it's coming back. Negative. Here. This is telling us that we're 55 days short. If we want 75 days of cover, we only have 20. We need to order 55 more days, cover in 38 and then we need to know how much do we need to order? But we need tohave. Whatever our amount in days is times ourselves velocity so and again. Well, actually, what we can do here to keep these negative numbers from Fox enough as we can do it if then formula where you tell it if the if the result of this formula equals zero or negative is a negative number, then just return a zero, and then you can my pestle this, And if you need that formula, just shoot me a message or put it in the comments and all added in. All right, So this is telling us that we need 55 days of cover and we're selling at 20 units a day, so we need to order 1100 units to get up to our 75 target. Let's say that we have a Let's say we have a 10 day lead time, which is really not realistic, but, um, usually lead times there quite a bit higher, but it depends on the manufacturer and what your product is. But just for sake, let's say 10. All right, so it takes 10 days once we place the order for the manufacturer to finish and ship it to us or to the warehouse. So when do we need to place disorder? Well, we need to place the order you take are out of stock date, subtract the 10 days lead time for to place the order. I'm not so Okay, so this tells us that, you know, 20 days we're going to be out of stock on February 24th. So we need to place a new order on February 14 placing your order on March 1st. All right, so that's how formulas work. Easy, breezy. No big deal. And look at all this information we have already. We can see where deficits aren't what we need to do. And maybe we need to run a selling product one. But the last video we get to highlight and form a format it so that it pops out and quickly we can see it. And then we'll be done with this project and you'll be well on your way to inventory control. OK, CNN in the last video. 5. Format That Data: Okay, Welcome back to the last video and the never out of stock. Siri's. You stuck it out with me in the last four steps, and here we are at the end. You, we're already done with the hardest ones. So no worries this it's fun to. So now you have a demand plan, you can start making decisions about your products. Look at you go. In this last video, I'll show you how to add some additional formatting to your demand plan. And that's just important because it sets alerts and pops out. So for me, looking at loads of spreadsheets every day, having colors that alert me visually is just a huge, uh, help. So let me show you what we're gonna do. Let's get going. All right, back in our demand plant with our skews in our inventory. And now we have all this data and we know how much days of cover we have, and we know when we're gonna go out of stock. And we know how much to order how many days and how many units and we know what day we need to place the order. So instead of just having all these numbers here sometimes can be a wall of numbers, especially if you have a whole bunch of products going down. Let's make this so that it pops out. You know it will come in here and we'll put in our updated data. However, often you decide to do it once a week or once a day or once a month. Or, you know, however, works best for you. And then when you do that, all of these numbers are going to change. So instead of looking line by line at every number, we want to see what's most important, what we need to be worried about. Ah, so that's gonna be your out of stock. Date your days of cover your date that you need to order. So let's look a, um, days of cover. Let's just use that as a point. We go appear to format conditional formatting and, yeah, the ranges right. A three toe F five. We want to tell it if this number is greater than or equal to 75 which is our target inventory that we set here. Let's make a green awesome. Now if it iss between, let's say, 2174 days of cover. We want to make it yellow. And lastly, to complete our traffic light if it's less than or equal to 20 days of cover, we wanted to be read because we want to know we wanted to stand out. All right, so this tells us we're good shape. We're in bad shape with, I need to order soon. And you can make this number anything you want, whatever works for you based on your lead time and all the things that you need to account for on this says we're kind of in a danger zone. But we're okay, right? You can make these whatever colors you want, but the's work for me. Let's also over here on a date to order. We want this to pop out to. So let's go back to conditional formatting. And the range is 03 Tell file it's right. But we want to use a custom formula because if none of this is gonna work, um, so it's a, um, are formula is if l three right, the stage minus, um, today, but in close parentheses, it's less fan 30 days. Then we wanted to pop out as yellow so we can do it that way. Or we can say less than 10 days since our lead or we wouldn't want to say less than 10 days I would be too late. We want to say less than 15 days or 20 days or whatever. You can change that. They're so it pops out. So whenever we come in and drop in our formulas and our data popular Aminata for our data into the report, tabs and all of this re populates all of this changes, Um, And then it will alert us that we need to place an order pretty soon and we're in a danger zone over. Not so. I think we need to run a sale here on product one because we've got over stock of inventory , we want to move it. We need to slow ourselves of product to so that we can place an order and make sure we have plenty of stock because what if there's a problem with the manufacturer and and they actually let us know that there's gonna be a week delay will. Then we're at 17 days and so close to running out of inventory, which we don't want to do so anyhow. So now you have all this information. You can make decisions you can plan for your stock and inventory and your orders and never be out of stock. And it's so sits a simple Is that so? If you have any questions or you need more clarification or you didn't understand something , I did feel free to reach out. And I'm happy to help in any way. Thanks so much for joining me. I hope this was helpful. Please give feedback and let me know. All right. Thanks. Take care.