Inventory

 Inventory management, tracking, and forecasting is one of the great use cases for Excel and Google Sheets. These types of spreadsheet templates are combined to be the most popular products I have on the entire site.

$149.00 USD - Inventory Spreadsheets Bundle
(lifetime access to all inventory templates I ever build)

All templates will be available for download immediately after purchase.

1. Inventory Management in Google Sheets ($45): I built this spreadsheet tracker most recently. It is a very clean tool designed exclusively for Google Sheets and it uses the most efficient formulas to provide a seamless user experience. The template was made to be hard to break and easy to use. It has some additional features such as expiration tracking (if needed) and low inventory alerts. As long as you have a free Gmail account, you will be able to access this.

2. Inventory Tracking for Single or Multiple Locations ($45): 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. Inventory Restocking and Cash Requirement ($45): 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).

4. Daily Inventory Tracker ($45): This fun little sheet makes it easy to enter usages / arrivals and see the resulting inventory balances for a 365-day period. You can easily use it for many years as well by making a new workbook for each new year and copy/pasting over the ending balances from the last year. Up to 40 unique inventory items (easily expandable) and dynamic conditional formatting for easy alert triggers if inventory gets to certain levels.

5. FIFO Cost of Goods Sold Calculator ($75): 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.

6. Inventory Reorder Tracker ($45): 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.

7. Inventory Formulas in a 3-Statement Model ($45): 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).

8. Cash Conversion Cycle ($45): 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)