วีดีโอ

You're welcome! 😊

That excel file and my other templates are available for purchase at the following link. You can also use the Part 1 and Part 2 videos to build your own template just like mine. Link: www.numericalinsights.com/store

If you watch parts 1 and 2, you should be able to build your own file. If you don’t want to build your own file, you can purchase one from my web site here: www.numericalinsights.com/store/safety-stock-and-reorder-point-calculator-excel-template-with-video-instructions

I create a unique ID for each table so they can match. That unique ID is the concatenation of as many columns as I need to get a match between the two tables.

Thank you very much.

For new products, I would base a forecast on the most similar product I could find for which I have historical data.

You're welcome!

My pleasure. They don't make it easy. :-)

The file is available for purchase at www.numericalinsights.com/store/safety-stock-and-reorder-point-calculator-excel-template-with-video-instructions

Thank you so much!

Think of committed as being "committed to customers." The committed value is the number of units you have SOLD to customers but HAVE NOT YET SHIPPED OUT to the customer, so the inventory is still in your possession. For example, suppose you have ten units of a product in your building. Your ON HAND = 10 units. Now suppose two of those units have been sold to customers but you haven't sent them out to the customer yet. Essentially two units are promised to customers. Your COMMITTED = 2 units. That leaves an additional 8 units that you can sell to other customers (10 - 2). That makes your AVAILABLE = ON HAND - COMMITTED = 8 units. I hope that helps.

@@TraceySmith thank you! i understand now😄😄

Glad it was helpful!

Happy to help.

The clouds parted for just a few seconds… long enough for one photo of the moon just leaving the sun.

My pleasure!

You're welcome 😊

Glad it was helpful!

Yes, a pie chart is intended only for data with positive values since it’s meant to represent proportions of the whole.

It means you are trying to compare something like 2 to “2”. Check the column type on both tables to make sure they’re the same or do a type conversion to make the comparison consistent in data type.

That’s great news!

Thank you! I find many people managing inventory who still confuse on hand and available and don’t know which one to use when making decisions.

My pleasure. Took me a while to figure it out the first time. 🙂

The template assumes that you use your oldest inventory first. It uses the specific dates you receive inventory in from a supplier and your current inventory level for that item to determine the age of all units of that item. I'm not quite sure what you mean when you say you use 'average inventory" instead of a FIFO or LIFO method of using inventory. FIFO is the most widely used method.

Thank you so much! If there are topics you'd like to hear about, just let me know.

Glad it helped!

The tables do not need to be related for the formula to work. Try putting your table names in front of the field names in your formula. It might be that you have common field names in more than one table.

Happy to elaborate for you. The inventory cost would be just the RM inventory cost and not the cost of the related FGs.

I tend to always include the table name in my formulas to avoid confusion when names are similar. For example, 'Description'[ColumnName] is different than 'TableName'[Description]. In the first case, Description is the TABLE name and in the second case, Description is the FIELD name.

Thank you very much. 🙏

My pleasure.

I apply the SS & REP to both the components and finished goods. For components, I use the supplier lead times. For finished goods I set the lead time to be the typical time from which I send an order into production to the time we log the produced finished goods into inventory as finished goods. Essentially, I treat internal production as an internal supplier of finished goods.

The PBI report was custom-made for a client and connected to the back-end SQL tables of an ERP system. So, that isn't available for download. The following Excel template is available with video instructions in case you find it useful. www.numericalinsights.com/store/safety-stock-and-reorder-point-calculator-excel-template-with-video-instructions

I have a separate video on the topic of calculating the inventory turns at the item level. Generally owners and upper management just want to see the overall inventory turns and professionals managing inventory and marketing people making product decisions want to see turns at the item level.

Thank you.

You personally wouldn't. This Excel template is for smaller companies who don't have the luxury of ERP systems, i.e., think of the number of entrepreneurs who run online shops. They don't have this information built in. I made it in Excel for those entrepreneurs since Excel is the one tool everyone seems to have. For my clients, I custom-code Power BI with a real-time link to the back-end SQL tables to do inventory management because the information I need to make good inventory decisions is spread across 10-12 screens in their ERP systems.

Thank you for your kind comment. You can use days of supply (DOS) or weeks of supply (WOS) as long as you keep your units consistent. For example, you would pair average and maximum weekly demand with weeks of supply and average and maximum daily demand with days of supply. That said, you can always convert as well. Sometimes the data I have is monthly demand and I divide by 4.3 to get the estimated weekly demand. As for holidays/weekends, my opinion is that the small difference between business days and calendar days caused by whether you ship products on the weekend and holidays is not large enough to impact the decisions you will make on inventory ordering. Remember that the point of these calculations is to guide when to order. The difference of a few days of supply won't impact when you order enough to worry about... unless you're running so super-tight on inventory. :-) If you calculate that you have 11 days of supply, then you have 11 BUSINESS DAYS of supply. (BUSINESS DAY = days you ship product out to customers). The difference of a few physical days vs. business days will not be a big enough difference that it will impact your inventory decisions. These calculations are meant to guide decisions. 100% accuracy isn't needed. No-one's inventory counts are even 100% accurate. :-) Like all inventory professionals, do the best you can with the data you have.

Glad it helped!

That means Power BI found more than one record matching your condition.

@@TraceySmith may I know hot to dealt with situation

That depends on the value that you are trying to pull back. If you are expecting more than one record to exist, then do you want the average value? the total value? It depends on what you're trying to present. If you are expecting one record to exist, then there may be an error in your data and therefore the data needs to be corrected or there is an error in your formula.

Hello Amr. You can determine your own formula or to save time, the template is available for purchase to follow along step-by-step with the videos. You can find the template on the following page. Link: www.numericalinsights.com/courses Scroll down on that page and look for "Inventory Management Template with Video Explanations."