How to calculate Weeks of Supply in Excel
You can watch a video tutorial here.
Excel is well-suited to manage the records of small businesses. If you keep track of your inventory in Excel, Weeks of Supply (WOS) is a metric that you will need to maintain as well. This metric tells you how many weeks your stock of a product will last, given the average rate of sale. Excel does not have a function for this metric but it can be computed by creating a formula.
The formula for calculating the Weeks of Supply metric is:
– Weeks of Supply = Inventory on hand/Average units sold per week
>Inventory on hand is the quantity of stock you currently have
>Average sales per week is computed based on historical data
Step 1 – Create the formula
– Select the cell where the result is to appear
– Type the formula using cell references:
= Stock on-hand (pieces)/ Average sales per week (pieces)
– Press Enter