Inventory vs. Sales Mix Calculator

PURPOSE

Go directly to Tool

Many businesses that trade in a number of products [e.g. +20] often find that the stock available on-hand for the best sellers is often lower than desired [and 'stock-outs' cost sales!], while lower volume products often suffer from 'inventory build up' - which means that cash is tied up in slow moving stock!  A business offering a "range of products" will often face the challenge of maintaining a constant balance between sales ratio and stock mix, particularly for the top (say) 10 products.  

A consistent lack of balance between sales and stock is often a "double cash whammy" - BOTH lost gross margin from missing out on sales AND having cash tied up in products that are selling slowly or not at all.

For businesses offering 20 - 200 products, inventory management and control is a critical function, particularly considering the cash invested in the asset value of inventory.  In companies that trade in high numbers of products [e.g. 1000+], at any one time there is likely to be 10-15% of "redundant inventory" which will be tying up considerable cash assets.  Balancing inventory to product demand usually means saving vast sums of working capital.

This tool is designed to be a sales ratio/product inventory mix calculator which seeks to balance stock levels with products sales, and allows a specific number of 'weeks sales' to be specified for product stock levels.  The asset value (and working capital) released by creating a inventory which reflects product demand and which stipulates an acceptable forward sales "cover", is the objective output of this business analysis tool.

User inputs required are:

• Total sales for a "period" (i.e. Year or quarter only)

• Individual current product gross margin rate

• "Period sales" per selected product (space available for 10 products and product name can be entered)

• "Period" current inventory values per product (from a recent stock take)

• A selected number of weeks of products sales cover (default value is six weeks

The tool firstly creates a product sales mix from sales value inputs.  After both the gross margin and inventory values per product have been entered the tool creates a 'sales stock-on-hand' and generates a corresponding inventory mix ratio for those products.  It then produces an output, depending on the "number of weeks stock" desired, which shows how the asset value from inventory can be better balanced in relation to the product sales mix, and generates a value of inventory asset cash that could be saved if stock levels were realigned with proven demand.

Both the product sales mix ratio AND the inventory mix ratio are 'percentages of total "period" sales'.
NB: The greater the proportion of total sales that the ten products entered actually represents [e.g. 40% vs. 80%] the closer the two 'mix' percentage values will become. 
The inventory mix and ratios are a reflection of the EXISTING PRODUCT INVENTORY VALUE converted to SALES VALUE by factoring in the GROSS PRODUCT MARGIN PER PRODUCT.  Weeks Stock-on-Hand levels reflect sales values using the sale per period input - i.e. 52 or 13 weeks.
 

NB:   IMPORTANT USER INFORMATION

It is important that users understand how this tool is working to gain the full commercial benefits from its results.
The key objective is to project a stock value, per product, that reflects as close as commercially possible to the product ratio mix generated by sales demand.  Thus the two most important columns are the blue columns, with the inventory mix attempting to match the sales mix where financially viable.  
The commercial outcome of this exercise is found, by product, in the purple column – this is where the net inventory asset loss or gain (depending on the week’s stock cover you select) is calculated.  
The best way to explain what is happening here is to take you through the maths of Product Line 1 in the example.

There are three sections to the tool:

The net result of this example is that to re-apportion stock to reflect the existing sales mix AND have six weeks sales cover for the top ten products will save the company $68,094. 
You can use this model to vary the stock cover for each individual product by selection a different number to insert into the Target Stock Mix box.
For example, you may decide that product 1, as your best seller, should always have 8 weeks cover.
That stock cost is an extra $22,981 however, it may be better to put the cash resources into that product and take it out of the savings from product 10, which are $27,704 at six weeks cover.
However, if you chose to drop product 10 to only 4 weeks stock cover, which only requires $11,804 of cost value – you can use the $33,660 saved there to spend on product 1 stock.  [Use the tool to check these decisions out]
This tool can be used in many ways to readjust stock to save cash and lower inventory value.  That means you should carry fewer liabilities in your balance sheet to ‘balance’ the lower inventory value, or you could convert the saving in inventory into other assets.  At least you can free up the cash sitting in “wasteful inventory” for better use in the company.
 

USER INSTRUCTIONS

• To see how the tool works, click on the "Show Example" button. 
   

• Step 1 requires the user to enter the TOTAL sales for the period to be analysed - i.e. Annual or a Quarter.

• The next entry is the GROSS MARGIN RATE PER PRODUCT. This ratio needs to be ACCURATE in order to correctly calculate the SALES VALUE from the INVENTORY VALUE input, and thus the WEEKS SOH figure.

• Step 3 requires input of the ten selected product sales values, in descending order - this is to produce a logical sales mix result, from top to bottom of the range.

• Step 4 requires the input of ACCURATE stock take [Cost of Sales] values for each of the ten products. These entries are very important because this column of figures is used to calculate both the SALES VALUE (and thus the WEEKS SOH), and the change in inventory value which delivers the VALUE OF TOTAL SAVINGS.

• Always use the TAB key to re-calculate after every entry!

• You may continue to change any individual figure in the fields by clicking the cursor on that field then "TABBING" to calculate. NB: Once you have hit the "Reset Table" button and pressed "OK", ALL ENTERED INFORMATION WILL BE DELETED