Spreadsheets can be a quick, easy and cheap way of setting up an inventory system for your handmade shop, however most craft businesses quickly outgrow this approach.
For starters, the major disadvantage of using an craft inventory spreadsheet is that it isn’t a perpetual system. This means that it’s not a system you can use through the year to keep tabs on your exact stock levels.
To know your stock on hand, you’ll still need to keep doing laborious physical stock takes. If you have a large inventory, once a year counts can be very time intensive and prone to error - especially if you are a solo entrepreneur trying to get orders out at the same time!
This type of system also puts all the work at the end of the financial year when your figures are due: this is one of the most stressful times in the year for a small business. For self-employed people, having an unexpected event occur around this time (sickness, large influx of orders) could jeopardise your chances of getting these numbers together in time for the deadline and thus you risk the chance of fines.
Another disadvantage is that your COGS (Cost of Goods Sold) final figure will not be known until year end, so you’ll need to be ready for an unexpected tax total if your cost of manufacture is less than expected.
Most importantly, craft inventory spreadsheets don’t tend to scale well as you grow your business - eventually you’ll need to switch to something more robust when your inventory and purchasing activities become too complex to track using spreadsheets and paper. This will mean even more stocktaking and data entry when you eventually move across to your new system.
Inventory spreadsheets can also be of varying quality, depending on who has created it. You’ll want to make sure that any spreadsheet you use allows you to satisfy any IRS audits if you are unfortunate to be targeted. In particular, you’ll need to make sure that you “show your working” for any material unit cost calculations you are doing manually and ensure that they are in line with the IRS recommendations (i.e. FIFO, LIFO or Rolling Weighted Average)
Essentially, these systems work by recalculating costs in real time based on inventory changes. The most common approach in perpetual systems is to use a rolling average calculation to produce a constant tally of your material unit costs and cost usage in orders.
This method also gives you a constant view of your stock levels and most systems also provide the ability to configure low stock alerts to be even more on top of your stock situation. COGS (Cost of Goods Sold) can also be seen throughout the year to enable you to tweak your production costs and identify any issues in your manufacturing process.
Every little detail is logged and is thus reviewable and (most importantly) auditable: you can show the IRS exactly how you came to the calculations you did on your return.
The biggest selling point? No time investment is needed at the end of the year for stocktaking - as everything is done in small amounts during the year as Cycle Counts (along with some regular hand-counts to ensure your accuracy) you’ll be able to generate the numbers you need for your bookkeeper instantly!