If you are looking for a way to easily calculate your beginning and ending inventory this template is here to help! If you have to file a schedule C tax form in the US, you’ll need to know what your beginning and ending inventory is and there are several different ways to go about doing this.
This template is one way you can track your inventory, and is designed for someone who does an actual physical inventory of their items/materials at the end of the year.
The best part about this template that will save you a ton of time, is a macro inside the template that will ‘reset’ your inventory at the end of each year. So as the new year starts, your ending inventory turns into your beginning inventory for the new year. Instead of having to transfer all your ending inventory to now show as beginning inventory, the macro will do this for you.
The macro does a lot of work for you:
•The macro will update the ending inventory (previous year) and move it to the beginning inventory for the New Year.
•It will change all Current Year purchases to Previous Year purchases.
•It will also re-set formulas needed and pre-set all formulas so the beginning inventory= ending inventory, until you go in and enter the new ending inventory quantity.
•It also will set the file to print.
How it works~
*Enter your purchases anytime you add new inventory, or save receipts to enter when you have time
*Select whether the purchase was made during the current year or previous year.
*Enter the name of your items, price paid and quantity. (You can enter information in the columns for Category, Vendor, Size & Quantity Measured by, for your own tracking information, but are not necessary for the formulas to work).
For ‘Previous Year’ purchases – the first time you use the worksheet, you will be able to select a beginning inventory quantity that is different than the original receipt.
For ‘Current Year’ purchases, you will be prompted to enter the ‘Ending Inventory’ quantity. The beginning year inventory is set to 0 for that purchase/row.
When doing your ending inventory, you will select whether the quantity changed since the beginning of the year and if so you’ll enter the ending quantity. The ending inventory is calculated as quantity * cost per unit. Once you’ve completed a year, save a copy of the file and run the macro to reset it for the next year. Directions will be included.
You will receive by email an MS Excel spreadsheet and a PDF with directions.
*You do not have permission to distribute or re-sell the template, so please don’t do it.
**This file is not meant to provide any type of tax advice.
If you have any questions, feel free to contact me.