How to Make a Stock Control Worksheet [Video]

2
3991
Excel Spreadsheets

Hi everyone, good evening, I am Rafael Avila, one of the members of LUZ, and today we will have the online talk about how to make a good inventory control. This is a topic that many people need to know a little more, because if you have a company that sells products in some way or if you need to organize a stock, a raw material or input if you do not have a good inventory control can have a huge bottleneck within your company.

Transcript of the Stock Control Lecture

So today we are going to talk about inventory controlI hope you like the theme. Today is our sixth online lecture that we are giving, is the last of the year, and next year we are already programming to do some other things related to online lectures, management knowledge, to you who have an interest. So stay tuned on our YouTube channel that it will be really cool to be able to continue contributing by sharing the knowledge that we have here with LUZ in management terms.

So to start our talk about how to make a good inventory control, we like to bring a story from one of our customers. It's a story that, a long time ago, we attended a mechanic's workshop here in Rio de Janeiro and, according to the partners, the situation they were having was a lot of money coming in, good sales, but what happened was that every end There was almost nothing left in financial terms and they were not very aware of why.

So from that first information they passed on to us, we started to analyze the whole business, from what they were selling, how much they were selling, what employee they had, what cost they had for rent, wages and so on going, other expenses that they could be facing, direct and indirect expenses. And from all this analysis, we saw that everything seemed to be going very well, as long as there was only a very big mystery in there that was why it hurt at the end of the month if everything was going well. In this analysis of revenue and expenses we could already see a high expense index with items for the stock.

So we continued this investigation until we got to know their tire stock, and when we got there we saw they had hundreds of runners with stacked tires, probably 50% of the store was just tire stock. And we asked, "Do you know how much you have in current stock value?" And obviously, like many managers, probably even you who are seeing this talk today, they did not know exactly how much their current stock was worth.

They asked an employee to do the counting, it was not a big surprise, but in the end the result was that they had roughly R $ 1.000.000,00 in the only tire stock. For some companies it may seem like a little bit, you can have R $ 10.000.00,00 in stock, for others it may seem like a lot, but the fact is that for a company that invoiced roughly R $ 100.000,00 per month, which was their case, having R $ 1.000.000,00 in stock meant having a plus or minus one year of billing stopped in their stock.

So it became clear to us that there was not enough money left over because the inventory-stock-warehousing scheme for them was generating mismanagement, maldistribution, poor overall financial organization, and badly hurt the company. This was causing the business to almost sink into debt, and in any case, it was still cool that, in the end, we managed to come up with some solutions, for example, to create aggressive campaigns with very aggressive promotions to reduce the stock in 50% and with that keep a monthly revenue flow good, positive, and also decrease this inventory so that it could be availed with other types of equipment and tools even from the store.

So this story is a really cool story for us to start by having an analysis of if you have stock and you do not control it well, you may be having a very big bottleneck in your company. So we'll talk over the talk about some points that are super important, for example, the repurchase point, also known as minimum stock which is sensational and super important if you have a stock you know what that repurchase point is. And also others, such as stock-based accounting methods, you can do by the PEPS method, which is the First in First Out, the LIFO, Last First In or First Out, or by the most used method that is the average cost, and so on.

This all helps you to know if you are selling a product at an ideal price or not, and so on. So, just to begin with, we wanted to talk about some of the benefits of inventory management. The first one is to track and reduce costs, so if you know you have a very high inventory, you may not need to spend more money buying other parts or other raw materials, you can leave all this stocked up because you know exactly when you will need to buy, so you have a cost reduction.

Ensures the availability of products, so if you have a market that sells a lot, with a lot of speed, if you do not know how much time you will have for a supplier to give you the raw material that will allow you to deliver the product in the end, you may have lack of products, mean, lack of revenue at the end of the month. So having a sense of your stock ensures the availability of the products in a perfect way, quick traceability too, you know exactly what kind of product you have in stock, which ones are stock low, which ones are stock high, this you allows you to think about promotion policies for items with very high inventory and why there goes.

And of course, also, it ensures you a quality of storage as you will not have more stored than you need, this is essential. And if you are aware that these are the benefits of inventory management, you need to start a step-by-step process today to organize your stock control.

Today we brought four items, four steps, which we consider essential in inventory control. The first one is the survey of your inventory, so if you work, as I said, with product, with anything of the kind, you will have an inventory, be it a raw material inventory or an inventory of your own products , which will be stocked. From this inventory you have to make a control of inputs and outputs, everything that comes in, everything that comes out, so that you keep this inventory, this inventory, updated. This will generate for you a need or a knowledge of your total cost of inventory, as I said, it can be done by different management methods yourself, and in the end, you have to have a habit of having an audit, a control newspaper of everything that has in its stock or not, this to guarantee that nothing gives problem.

So let's start with our first step which is the inventory survey. Basically with the inventory survey, it is the initial step for the inventory control, then all the products you have in stock you need to put inside your inventory, which is nothing more than a list with all these items.

What will you get here from important information? If you have an item that you have a few months ago do not buy more, no longer use it, you can cut it from your inventory, if you have an item that is new, you include it in your inventory to keep it up to date. Basically if you can keep your inventory up to date you will be able to have greater knowledge of where it comes from and enter the money within your company according to your stock.

And of course, if we are talking about inventory that is a list where you will put all the information of your products, be it information of size, weight, quality, supplier and so on, you need a type of tool to do this control.

We have three, obviously this is at your discretion which tool to use, then where you will register and distribute this data, you can do this in a drawing board with products that will have practically zero cost of implementation, a very marginal thing since you only have to buy a standard drawing board and a few sheets to fill it up, but at the same time it is a manual, repeated 100 activity, with risks of losing information, it is only possible with low data volume, so assuming you have a stock with a million products, five thousand products, and you will not be able to record all that on the drawing board with a few sheets.

Already in the excel spreadsheet aggregator will allow you a relatively low cost yet and possibility of customization for your reality, but at the same time, however much you allow a greater amount of data and indicators, if your stock is too large, for example something over ten thousand items, things like that, maybe the aggregator excel worksheet might get small for this stock. If you have a low stock of products, inputs, the excel sheet usually solves your problem and is very good.

And finally we have the option for software that usually has a higher implementation cost but is suitable for companies that have very high volumes of products. So if you have an unbelievably large number of products you might want to look for software that will allow you to have more control over your inventory. The problem is that normally these software does not have much customization and has a certain independence of who is providing this system, but it is still a valid option for you who are thinking about controlling your inventory.

They are the three main methods of inventory control and I think the learning we leave here is that if you do not have an inventory of your upgraded products you can not even begin stock control. After all, when you count entry and exit if you have an entry item that is not in your inventory, this will hurt your control and will give you a shortsighted view of what your stock actually is.
So first step, sit down now, pick up a little list and start making that list of what items you have inside your stock, ie those that have a move or entry from your stock or out of your stock over the months. This will help you a lot already have a sense of what is inside your stock.
From this notion we can go to the second step that is the control of inputs and outputs. So if you have a control of what is coming in and what is coming out, you certainly can get a bigger sense of what is more important than what is less important to your stock and also what is gaining importance and what is becoming less important.

So logically keeping your up-to-date inventory makes all the sense and you should do this with strict control of inputs and outputs, so beyond auditing, we'll talk more forward, input and output is perhaps one of the most important points when we're talking about good inventory management. This input and output can come in a variety of ways, so you can have multiple inputs for own production, deliveries by truck or supplier deliveries, single purchases that you make and put that raw material or product in stock, and at the same time, several exits that is what will generate revenue for you. What are these outputs? By e-commerce, by delivery, by point of sale and there goes.

At the end of the day the only thing that is very important for you to have notion and to keep in mind is that you will have to have this control regardless of how that exit happens. So usually when you have this control and you have an e-commerce it is important that this e-commerce already has integrated or allows you to generate spreadsheets, which you can simply copy and paste inside your spreadsheet, and so on.

After this input and output control you must also have a control of where it is being stored. So if you have an outlet you are going to deliver somewhere, you need to know when it will be this delivery process, then where it will be stocked and how your products will be. If we were talking about a clothing and shoes store, for example, we could have this layout of the store, where shoes are organized in place, pants in another, this facilitates the control of this stock and the management of this entrance and exit product, or as I said, inputs, and this will also enable you, when you control this input and output, you understand when it is your exact repurchase point.

So supposing we sell ten pants a day in our clothing store and we have a stock of a hundred pants, but it takes us to get from our supplier one day to get a hundred pants again. So if I'm selling ten a day and I have a hundred pants, in ten days my stock is zeroed, if I take a day to ask, when my stock goes down and I ask, I'll stay one day without any pants. So this is the point of repurchase, when would it be? When I had ten pants in my stock, which I normally sell ten a day, I would already order 100 new ones, by the time that day ended and I would sell my last ten, by the beginning of the next day a hundred new ones would arrive.

It may seem a little tricky but it's actually quite simple, it's just the moment you need to make a new order for that input or product before it runs out, otherwise you may have a stock management flaw where you do not will have stock to offer to his client and this is one of the worst things that can happen when we are talking about inventory management, since if this happened in fact, you:

1. You are selling that specific sale;
2. If your customer had never bought with you before then he may have a very bad impression of your brand, since you do not have enough inventory to offer it to you, and you may not come back to your store, which would be a very big loss for a customer , not just the sale.
3. Obviously, too, if you do not do it in a well-done manner, you are damaging the entire management of your inventory as a whole, which is very negative for any company.

So our second learning is that your inventory will only stay current if you track all the inputs or outputs regardless if it's an output that comes from an online channel or from an offline channel that can be your same store or point of sale , and regardless if that input comes from one particular vendor or another, you need to have a way to control this. Usually having a person responsible for this control is enough to give you a better idea of ​​all the products you have stocked and so on. Obviously this will depend on the size of your stock and according to the size you can do certain thing or not.

Since we have already understood that we need to have an inventory and that we need to control the inputs and outputs of this inventory, we now enter into the total cost of inventory part. Basically it is, if you are controlling your inventory of inputs and outputs you already have a very good analysis of what your stock is and what is the photograph of it at that moment or at the time of its analysis. Now it's no use knowing how many products you have for each thing and what is the repurchase point, the minimum stock point of these products, if you do not have a notion of what the cost of them is.

You can have, for example, a million pencils in stock and these pencils take up a small amount of your stock and it has a real value for each pencil, and you can have five remedies, medicines, vaccines, five vaccine boxes that has the cost of one million each. This will tell you more or less how much you have in stock and this total cost of inventory is sure to help you get a better sense of what kind of stock you can do.

So just to get into the theoretical part even the total cost of inventory is the cost with all the inventory spend you have, not simply the goods you have, that would be the first point, then all your goods already at a cost, in a value of stock you have, but also other items such as cost of rent, handling cost, cost of product losses in inventories, so if you are working with food industry, for example, it is an industry that has a lot of loss by date validity and so on.

So you need to be aware of how much it costs you not to get a wrong view of how much, in fact, you are spending or losing on your stock. For example, you may have a view that you have a million saved in food, but in fact if half of these foods end up getting lost because of poorly managed or poorly organized your stock you will probably have a very large loss there, bigger than a million that we were talking about, you're going to have five hundred thousand in stock, which suggests a loss of five hundred thousand of your stock that for many companies is a very big loss. So that kind of shelf-life, expiration handling with delivery modes, with the overall logic of your inventory is essential so you do not have any kind of problem with that.

And there is also nice of stock turnover at that time because the faster your stock spins the cheaper it stays, the more predictability you have, you can make the exchange faster, you lose, eliminate this risk of losing products which are in stock for one reason or another. And just to mention what is the stock turnover is how long it takes from the moment your product is finalized in your stock until the output of it. So inventory turnover has everything to do with what we were talking about from the repurchase point, if you have a 10-day stock turnover, in ten days you're going to have to somehow have a new order from that product for you to have it in stock, and so on, are several ways to analyze.

And once you understand your total cost of inventory, how much you have there, you can go into methods of analyzing this stock as a whole that we have the average cost, PEPS and LIFO. Just to explain in a shorter way, PEPS and LIF are management methods when they analyze, PEPS is the acronym for First in First Out and LIF is the Last First In and Out. What does that mean in practice? Basically if you make a purchase today from ten pants to one hundred reais and then you make a purchase of ten more pants at two hundred reais, assuming you sell 15 pants, by the first PEPS method he would pick up the ten pants you bought first, since the first one that comes in is the first one that leaves, then he would get five pants at the cost of two hundred reais. In this method you would have ten trousers going out to a hundred reais, adding a thousand, plus five trousers to two hundred reais, adding another thousand, two thousand of cost of your inventory going out.

In the LIFO method, that we would change, we would have the last one coming in, which was the ten pants with two hundred reais entering, that would generate a cost of two thousand, plus five pants that would be the first five bought to five hundred reais, that would generate a cost of two thousand and five hundred. As I said, in day-to-day practice this will not change your life, because in everyday life if you had twenty pants, ten left, you understand that there are ten pants left, simple as that.

Now for the accounting analysis of your stock this is very important and then we go a little deeper into the average cost why here in LUZ if we think of small companies, which we indicate, if you go some type of work, it is with the average cost that if you know that the twenty pants you bought generated a cost of three thousand reais, if you know that your sale price of the pants is one hundred and fifty reais, you sell twenty pants at one hundred and fifty reais you will get three thousand reais. So, beauty, your average cost of the three thousand reais of purchase that you realized has to be sold at least one hundred and fifty reais. If it sells for less than one hundred and fifty reais you will have a loss, and having this loss you are not doing a good management of your stock and the purchases of your stock.

So just to talk, these are methods of inventory inventory, these three are the most important, it makes a lot of sense that you understand each one of them, but in practice for your day to day what makes the most sense is your average cost, then everything that goes into you will play in a contained one that later you will divide by everything that has entered and you will know what your minimum selling price is so that you do not lose in relation to this stock. Of course in this account you will have to put focus, things like, after all are not only one hundred and fifty net real in our example but it serves to exemplify how this happens.

So our third apprenticeship is that the stock value has to be completely to avoid error, and you can not find that just because you have two hundred products in your stock you can sell them to real X that this can generate a loss to you at the end of the month, and if you are generating loss, once again it is worth reinforcing that you are inventory control badly done, or at least only badly done if you are not aware of it, sometimes you can do it for some business reason.

But now just so we can go to our fourth step, which is one step faster, is an audit, what does that mean? It means that if you have your inventory organized, you control your inputs and outputs, you know what your total cost of inventory is, you need to review it from time to time to see if it's right. So for you to close this process in an excellent and well done way, your company needs to periodically audit your stock, to know if it is not taking any deviations, if you are not having any outdated records, if you are not having any errors of filling of the spreadsheets or gives form of content that you are using, and so on.

So you need to have an audit, even why sometimes that happens, you may have an employee stealing your raw material, stealing your ready product from your inventory, so obviously it's harder for small businesses that you have that kind of problem , for larger companies may be a bit more recurring but the important thing is that we have notion that if you do not do this periodic audit you can be creating a hole in your inventory and you do not even know it, and this is the worst when you do not knows the things that are happening in your company.

This brings people to our learning room that is stock auditing is the best way to pick up holes in your overall inventory management process and improve that management in a general way.

Well, that's more or less the main facts about inventory that we prioritize here in LIGHT and that are essential to control, to work, and if you want to do it with a tool to optimize your sales process as a whole process of inventory control and this process in general, we stock control sheet. It's a very complete spreadsheet that will get you to put your inventory in and out items, you'll be able to see your minimum inventory point, you'll be able to see how much stock you have today for each type of product that you register and this will also generate an automatic stock value according to the entries and exits you have already added directly to the worksheet. It is a very complete and legal worksheet on stock, we recommend you to meet her, just like I said enter that link that is below and take a closer look at her demonstration, her video and so on. And that's all part of good inventory control.

I wanted to thank you very much for the presence of all of you, as I had already said at the beginning, in our sixth online lecture on management practices, our last of this year, so for you who did not take advantage of the other five, it's only you to join our channel of YouTube and have a look at them, they are all recorded there, it has an average duration of half an hour, forty minutes, everyone talks about a management practice that is essential for micro and small companies, even medium sized companies, and people always gives very good tool tips to use in all of them.

So I wanted to thank you very much for the presence of all of you, anyone who has had any questions or wants to chat is just send me an email at rafael@luz.vc. If you are interested you can also write some comment here on YouTube, just below here the video you have a space to make your comments, that as far as possible we will respond all of them within the time that we have.

So once again, I would like to thank you for your presence here, if you have any kind of questions or want to talk a little more, my email is available to all of you, it could be about stock management, management as a whole or What else do you need?

Once again thank you very much, a big hug, good night and a great Christmas and a great new year for all of you who do not talk to us in the next few days. Bye Bye!

Excel Spreadsheets

2 COMMENTS

  1. Hi Anderson, thanks for the feedback. I reviewed the post here and corrected the main mistakes.

  2. The content is great, but it would be good to check the writing errors and concordance in the texts.

LEAVE AN ANSWER

Please, write your comment
Please enter your name