Inventory

 Inventory management, tracking, and forecasting is one of the great use cases for Excel and Google Sheets. These templates are combined to be the most popular products I have on the entire site. I decided to bundle them all up into a package. This will include every single template / financial model I have built that directly deals with managing existing inventory and forecasting inventory.

$99.00 USD

All templates will be available for download immediately after purchase. You save $156 by buying in bulk.

1. Inventory Restocking and Cash Requirement: The single most popular template on the entire site. This has had more sales and the best feedback out of anything I ever built and for good reason. The logic is unique to anything I have ever seen and it is very effective at doing what it needs to do. This tool will allow the user to enter assumptions about past sales and expectations about how those will change in the future (by month) in order to create a report about expected purchases of inventory to replenish existing counts and never go below safety stock. The data is granular to the day and goes out for up to 36 months. The user can play with a bunch of variables including: Average Cost per Unit, Average Lead Time (days), Starting Inventory, Months to Reorder For, Payment Terms (days), Minimum Inventory Level (safety stock) and all of that will drive the cash flow requirement forecast. Summary visuals were built as well. This includes two versions, the second has all the logic above, but also a start date for when inventory is purchased for the first time for each SKU (for startups that need timing granularity).


2. Inventory Tracking for Single or Multiple Locations: This is a static report that is designed to show existing levels of inventory for up to 500 unique SKUS across 20 Locations and even allows the entry of 'shelf/spot' location identifier if needed. It is not too hard to edit this if you need more SKUs and/or more locations. Summary reports automatically display how much of each SKU is at each location and the movements over time. This also comes with two separate templates for tracking inventory counts at a single location and a simplified single location inventory tracker.


3. FIFO Cost of Goods Sold Calculator: Until this was built, any firm that sells units of something and needs to attach a relevant cost based on purchase history would have to manually figure this out. It is especially hard when you have bulk purchases of different amounts and sell in varying amounts. This uses impressive logic to automatically figure out the COGS for sales of units based on previous purchase prices of that unit. A monthly report will summarize the actual COGS total for up to 12 months (not hard to drag for as many months as you want). All the logic is based on a simple log of purchase history (that has total units and price) against sales history (that has sold units) and the resulting COGS (total and price per unit) displays based on the price history and using FIFO (first in, first out) Accounting methodology to do so. There is also a version included that has up to 5 SKUS. Google sheets versions exist for this and there are light and heavy versions. The lite versions have reduced total batches down to 100 from 400.


4. Inventory Reorder Tracker: This is a standalone template in Excel and Google Sheets that was designed for helping inventory managers that have many SKUS (100s / 1,000s) stay on top of what needs re-ordered. The user can enter data about each SKU (things like current stock, expected future sales, minimum inventory level (safety stock), and lead time. The model will then show how many days it will be before a reorder should happen and advanced filtering functions can be used to show all SKUs that are likely to need a reorder within 'x' days and you can define 'x' as any number of days and the report will update. This is best used in the Google sheet version, which requires a free Gmail account.


5. Inventory within a 3-Statement Model: If you are working with financial statement forecasts for clients and they have a business that involves inventory, you will love this template and can use it as a reference. The purpose is to show how all aspects of inventory touch the Income Statement, Balance Sheet, and Cash Flow Statement. This even gets advanced enough to show when partial payment is made up front and the rest is paid later (you will see accrued liabilities and how that is properly tracked with the inter-connected statements).


6. Cash Conversion Cycle: One of the main components of the CCC is inventory (how fast you can sell it, when you collect your receivables, and when you pay for it). This is an awesome template to input data from your financial statements over time and track what the cash conversion cycle is (measured in days) over time. Improvements in this number are driven by efficiency of inventory management in all aspects.

You can buy all financial model templates and tools from smarthelping.com in one large bundle here: https://www.smarthelping.com/p/excel.html (see 'buy in bulk' dropdown at the top of the page there)