Inventory Trackers and Forecasting (Document Bundle)

Spreadsheets can be a helpful tool for inventory management. One of its best uses is for forecasting. This doesn't require millions of rows of data or heavy calculations. Another really good use is tracking inventory-specific data from financial statements. Things like understanding and keeping track of your actual cash conversion cycle is easily done with a simple spreadsheet.

There are some more complex things that I've done with FIFO (first in, first out) cost of goods sold calculations that I don't believe have been done anywhere else on the internet. It means being able to enter a log of purchases (price and volume) and then a log of sales (volume) and then see the correct cost per unit and total value of the COGS for each month. This type of inventory calculation takes out all the manual calculations that most will use to perform the right math.

Another broad use of these spreadsheets is figuring out how to forecast inventory within a 3-statement model. I've worked with a lot of Accountants that have trouble with getting this right in the Income Statement, Balance Sheet, and Cash Flow Statement. Inventory forecasting touches all of those and it can get complex with payments happening over time instead of at the moment of purchase. The sheet will show all the right formulas so you can see how to duplicate whenever you need to forecast inventory.

Finally, there are actual inventory trackers in here to keep track of the number of units of each SKU at one or multiple locations. This works well if you don't have millions of transactions to record. These database style sheets will track inflows and outflows to come up with a balance of units. In the same vein as inventory tracking, there is also a spreadsheet in here to figure out when it is likely you will have to reorder SKUs. This can work with 1,000s of SKUs and it runs in Google Sheets. Key assumptions will drive the expected days until a reorder should happen. Things like current balance, expected sales, lead time, and safety stock will factor in the order date and the calculations have an auto-sort feature where you can see 'x' days until inventory as a primary filter.