Google Sheets Inventory Tracking Template | Step-by-Step Tutorial

This article will walk you through how our inventory tracking template is created.

Prepare the Sheet

In order to have all of the right column headers, start the spreadsheet by adding the following labels in the first row:

• Item
• Beginning balances
• Purchases
• Sales
• Ending balance
• Purchase Price
• Ending Value

Enter the Items and Amounts

Enter your item descriptions in the first column under the Item header. When you use this spreadsheet for the first month, you need to hard-code the beginning balances. In subsequent months, you will be able to link the beginning balances to the prior period’s ending balances. We will review how to do that later but, for now, just type your amounts in.

We’re using an ice cream shop as an example so the example is using vanilla as one of the flavors. During the first month, you purchased four units and you sold two.

For the ending balance, you will use a formula. If your table is set up with the same rows and columns as the example, your formula for the ending balance should be =C4+D4-E4.

  

Purchase Price and Ending Value

For purchase price, use the last price that you paid for a gallon of this ice cream flavor. That way, the value will reflect the market value closely if it’s the most recent market price. For the ending value, we’re going to take the ending balance, which is column F, multiply it by the latest purchase price, which is G4, =F4*G4 and that’s the ending value of your vanilla inventory.

  

You need to remember to update the purchase price every time you buy it or it’s not going to reflect the current value.

Watch the video

Total Inventory Value

Next, we’re going to total the value of our inventory. Go to the bottom of the ending value column and type =SUM for your formula. Open up the parentheses and choose the range of all the ending values. I went back in and I filled out some activity for two more flavors of ice cream.

The ending value of this inventory is the total shown in the ending value column. To reflect the proper value of your inventory in your financial records, you need to adjust it to this number if that’s not what the balance is now.

 

Formatting your Sheet

Let’s do a little bit of formatting so it’s easier to read. In order to get all of the numbers in the value column to have two decimal points, change the formatting to Number by going to the Format menu, choosing Number, and then selecting Number again. Now all of the decimal points are lined up which makes it visually easier to read.

 

Let’s do a bottom border to show that this is the sum at the end of the table. Using a thick line at the end of a column of numbers helps a reader see that it is the end of the series.

 

Book to Actual Comparison

After all of your careful tracking, some of your inventory is going to mysteriously shrink, right? Or, you’re going to purchase something and record it incorrectly. In other words, this ending value over the months is going to become inaccurate no matter how hard you try to keep it right.

What you can do is a monthly or a quarterly physical inventory. Let’s add a physical count column. Let’s recheck the purchase price to make sure there are no errors there either. Then, add in actual value using a formula, in this case, of =I6*J6.

 

You don’t need to do physical counts throughout the month. You don’t really need to do one every month. But, if you want to double check yourself, this is a great way to do it. If any of these amounts or prices are different, the ending value of the count won’t match the ending book value in the spreadsheet to the left.

Adjust your GL

After you do your physical count and you check your prices, this is the dollar amount that you should have recorded in your general ledger as your inventory. For the months that you do a physical count, you should adjust your inventory to actual and then book the difference to your cost of goods sold.

Note that the first worksheet in the template is linked to a second that you can use for a subsequent month. This process can be repeated for as many months as you would like. Link the beginning values of the subsequent month to the ending values of the preceding month. You do this by typing =, left-clicking on the cell that you want in the other worksheet, and hitting enter.

That wraps it up for creating and using your new simple inventory management template. Hopefully you find this helpful for your business!