Creating your Own Accounting System - Inventory

I hope you liked last week's discussion on Invoices.  This is the last in the series of creating your own accounting system.  I hope you have enjoyed it as much as I have enjoyed writing it.  If you decide to implement all or some of these ideas, please don't hesitate to ask, but do keep in mind I am not an accountant or a tax professional this is merely how I do business and what seems to work for me.  Now onto the fun stuff...

This week we will discuss how I track and maintain my inventory for both my sanity and for tax purposes.

I use this page in my accounting system for multiple purposes.  I track:

• Finished products I have in stock
• Finished products I have sold
• Material for products in process (i.e. incomplete products or raw materials)

For each of these three items, I track (I would show a screenshot, but I think readability of the image may defeat the purpose):

• Product Identification Number
• Product Description
• Posted Price (for in process products, the estimated price)
• Tax
• Whether or not it has sold, and it's sold date
• Material Cost
• My wage/commission for the product
• Profit before any fees
• Profit Percentage based on the a fore mentioned profit
• Etsy costs (renewals and sold transaction fees)
• Paypal/Square/Merchant Processing fees
• Profit after all applicable fees
• Profit Percentage based on the associated profit

So how do I use these categories play into the tracked products?  As you may recall from my Accounts Receivable post, your inventory is considered an asset and should be tracked as such.  This includes all finished products as well as not yet finished products.  How do I do that you ask?  Do you remember that "SUMIF" function I couldn't stop talking about?  Well it's popping up to say hello again.  In the column where I mark if an item has sold, I put an 'X' there.  To track the unsold inventory for the Accounts Summary page, I simply sum the entire posted price column by calculating

=SUM(Inventory!C:C)

then I subtract out any sold items with

SUMIF(Inventory!E:E, "X", Inventory!C:C)

So the overall equation looks like

=SUM(Inventory!C:C)-SUMIF(Inventory!E:E, "X", Inventory!C:C)

Ok, so you link your inventory back to the Account Summary, but what do you do with all the other columns?  Great question!

For product that are not complete, I typically use it to help me develop a price for my products.  I enter the cost of the material, the wage I am paying myself for the product then I have it set up so have the remaining columns automatically calculate the percentages and dollar figures when I put in a particular price.  I have in my head a specific profit I am going for on each product and will play with the price numbers accordingly.  I also use the reason test to check my price.  For instance, I don't think I could ever sell a single burp cloth for $100, but I probably could sell it for $6.50. 

Once an item is sold, I record the paid price before taxes, mark it as sold, the date of the sale then what fees were actually assessed.  For instance, I get requests for orders through my personal networks, therefore Etsy fees don't apply, or I am paid in cash, or check, therefore no paypal fees.  

That is the general overview of how I use this tab, but what is probably more important to you is what equations are used and how do they make my life easier?  Let's work out way down the list above.

• Product Identification Number: 
• No equation
  
• Product Description: 
• No equation
• Posted Price: 
• No equation
• Tax: 
• In Virginia we pay a 5% tax on sales so it is calculated as such:
• = 0.05 * Posted Price
• Whether or not it has sold, and it's sold date
• No equation
• Material Cost: 
• Summation of material to make said item
• My wage/commission for the product
• No equation typically, but occasionally I will base my wage/commission on the cost of the good
• Profit before any fees
• =Posted Price - Material Cost - Wage
• Profit Percentage based on the a fore mentioned profit
• = Profit / Price * 100 (or you simply can format the column to read out as a percentage, in which case leave off the 100
• Etsy costs (renewals and sold transaction fees)
• =Number of renewals * 0.20 +  Price * 0.035
• Paypal/Square/Merchant Processing fees
• Specifically for Paypal:
•  = (Price + Tax) * 0.029 + 0.30
• Profit after all applicable fees
• =Profit before fees -  Etsy Fees - Paypal Fees
• Profit Percentage based on the associated profit
• =Profit After/Price * 100 (again, you can use the percentage formatting and leave off the *100 if you wish)

I hope that this helps with your inventory tracking.  I has helped me see the big picture of my store.  There are several built in tools that I use in excel to help me with my projects.  I generally like to know my average price point so I can roughly predict my sales for the year.  Excel has this great feature that if you highlight more than one cell at a time in the bottom right corner of the window, they will display the Average, Count, and Sum of the highlighted cells.  You can do this for price, percentage, or profit to give you an idea what your overall all average profit of your inventory.  I have found this tool more than useful on countless occasions.

For your reference here is a link to this whole series:
Invoices
Accounts Payable
Accounts Receivable
Accounts Summary