Use Quantities instead of Money for Inventory KPIs

The accounting formula of Inventory Turnover is Cost of Goods Sold / Average Inventory. To find the COGS you have to use the following equation: Cost of Goods Sold = Beginning Inventory + Inventory Purchases – End Inventory

While this makes perfect sense, in real word the calculation of COGS is little more complex and
several inventory valuation methods are used, such as FIFO, LIFO and Average Cost. In FIFO we assume that the oldest units of inventory are always used first. In LIFO we assume that the newest inventory is always used first. In the Average cost method, the beginning inventory balance is used and the purchases all over the year in order to determine an average cost per inventory unit.

This might still sound easy but remember that in most cases, the repeated purchases of a material, take place with different prices all over the year. So you cannot just divide the inventory balance with the last price because you’ll get a wrong result. In addition, if you don’t purchase but produce a material, estimating its cost is a far more complex procedure and many things should be taken into account such as the Cost of the Raw Materials, the Cost of Labor, the Manufacturing Overhead and the Depreciation Cost.

Most ERP systems do not provide these values “on the fly” but only at the end of the year, when the Balance Sheet is created and the Costing Process is executed.

If you are a Manager who needs to track the Inventory’s KPI, the easy way out is to use Quantities instead of Money.

• Inventory Turnover= Sales in Quantities / Average Inventory in Quantities
• Average Days to Sell Inventory= 365 / Inventory Turnover

Direct Material Costing through BOM explosion

There is often a case when your supplier suddenly changes the price of a material you purchase. The question that arises is how much this change is going to affect your direct material cost. Some ERPs might offer the capability of “What if” analysis while some others don’t. If the second is your case, the easy (or not so easy) way out, is to perform a BOM explosion in an Excel spreadsheet for the SKU you want to check and try to re estimate the total Direct Material Cost.

To do this you have to be an advanced SQL user and have an understanding of Database management and of course Excel and VBA. The trick is to make a connection with your Database Server and perform a recursive query in order to extract every component of your BOM tree diagram. After doing this, you can then change the price of the material which interests you and find out how much it affects the total material cost.
When I faced the same problem I decided to write some routines of my own. I made an "Excel Add In" that presents a form with the tree diagram. There I can easily insert the new price and re estimate the cost without even sweating.



Safety Stock (conventional approach)

Stock is based on an estimation of demand. In order to absorb the error of the estimation we use “Safety Stock”. Its actual use is to protect the company from an unexpected and unwanted stock out. In its simplest form, safety stock is calculated as a percentage of the Base Stock. A small example is given bellow:

Weekly Distribution of Monthly Sales Forecasting

In most cases, the Sales Forecasting is performed on a monthly basis for many reasons. The basic reason is that the monthly forecasts tend to result in higher accuracy and this is what every forecaster seeks to. In addition, forecasting for shorter time buckets tends to be much more difficult.When I started doing some forecasting myself I soon realized that I had to shorten the time bucket. The initial monthly forecast should be distributed over weeks and then the accuracy should be measured. If the accuracy was about the same as on the monthly forecast, which is very unlike to happen, then it would be safe to say that the demand is constant over the month.

Although it seems easy at first, it is a really tricky procedure. Every month has different number of weeks and each week has different number of working days. It’s a really bad idea, just to divide every month with 4 and try to distribute the forecast this way.
Excel has a lot of date functions that can help you perform this, such as the Days360, NetWorkDays, WeekNum, etc, but still this task is not very easy. Apart from this, you should know that Excel does not calculate the ISO Week number as most calendars do (http://en.wikipedia.org/wiki/ISO_week_date)
but the arithmetic week. You have to make your own custom ISOWeek function to calculate it.
Taking under consideration the above, I decided to write a useful Excel Add In, to make my life easier.