pathfinder inventory sheet This is a topic that many people are looking for. bluevelvetrestaurant.com is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, bluevelvetrestaurant.com would like to introduce to you Google Sheets – Inventory Tracking System. Following along are instructions in the video below:
” ve just started us out with some nheaders and we re just gonna enter an an item as we go i ll explain what nyou re going to do with of these we re going to have an ice cream shop. And we re going to be tracking the flavors leave all the extra rows on here in case. Nyou add something in subsequent months. They re not going to hurt anything then neach month is going to have an opening balance or beginning balance.
If you will non this first month. We re going to hard code it in subsequent months. We re ngoing to link it i ll show you how to do that but for now let s say five vanilla nwe have ten gallons of mint chocolate chip and one gallon of bubblegum we re just going to work on the vanilla for now during the month of january nyou purchased four you sold two and so your formula here should be c4 d4 e4 for purchase price let s use the last price that you npaid for a gallon of ice cream and then that way it s going to reflect them at nthe market value pretty closely. If it s the last price that you paid then nfor the ending value we re going to take the ending balance.
Which is column f. Nmultiply. It by the latest purchase price which is g4 and that s the ending nvalue of your vanilla you need to remember to update. The purchase price nevery time you buy it or it s not really going to reflect the current value next nwe re going to put in a total column.
We ll go down to the lowest. It could be njust to leave some space so we went into h16..
I m going to hit the equals sign and ntype some for my formula open up the parentheses. And choose the range of all nthe ending values. I went back in i filled out some activity for these other ntwo flavors of ice cream cell f4 has a formula in it there s no need to retype nthat activate the cell f4 by left clicking it with your mouse go to the nlower right hand corner of the rectangle until the mouse turns into a plus sign ndrag this all the way down that s going to copy that formula down do the same thing in column. H let s put in some.
Purchase. Npricesnow the whole spreadsheet is calculating the ending value of each nflavor. And it s coming down to a total let s do a little bit of formatting here nso. It s easier to read.
I think you want all of these rows to have two decimal nplaces. It s just called number use that formatting now all the decimal points nare lined up it just makes it visually easier. Let s do a bottom border to nshow that this is the sum at the end of the table the ending value that you came ndown to and 8478. If you re valuing your inventory on your books you need to nadjust it to that 8478.
If that s not what it is right now but on that subject some of your inventory is going to shrink right or nyou re going to purchase something then record it wrong in other words this nending value over the months is going to become inaccurate. No matter how hard you try to keep it right what you would do is do a monthly or a quarterly nphysical inventory..
This a physical count column let s recheck. The purchase nprice to make sure you didn t forget any and then let s do the actual value you don t need to do this throughout the month. You don t even really need to do it every nmonth just to do it whenever you want. But if you want to double check yourself this nis a great way to do it i made one of these numbers a little bit different so nwe ll see what s going to happen.
Here. Let s go ahead and select all these nformulas and the sum and we re going to copy them over to column k. They re nrelative. So the cells that they re looking at are going to shift to these ncells.
It s already doing the math for you the actual value after you do nyour physical count. And you check your prices. This is the dollar amount that nyou re going to want to have recorded in your general ledger as your inventory for the months. That you do physical count you would adjust your inventory to nactual and then book the remain amount to your cost of goods sold let me bring this bottom border over just to make things look pretty now.
Let s nsay. We re done for the month of january go to the bottom left hand corner nwhere..
You see the tab for the sheet. That you re one left click on the triangle nselect duplicate. That s going to carry over all of the data. We did that on npurpose.
I ll show you why let s rename it to february. Now a few things are ngoing to happen here keep these items like they are if you add something nadd it at the bottom. But the beginning balance isn t right because this is the nbeginning balance from the last month. What we want to do is put your cursor in nc4 hit your equal sign.
We re gonna go pick up the ending balance from the nprevious month since we did an actual count we re going to pick that up nas that s the best value pick up the physical count at i4 hit enter nthat link brought the new value in from january drag that down all the way highlight your purchases and your sales nand just delete those for now since we re starting fresh the ending balance nright now should be right if this is the beginning of the month. Because no nactivity has occurred purchase prices should still be good you did not ndo a physical so come and delete these you re ready to go the activity nfor february. Should just come in here as purchases and sales. This example is nmade for retail.
If it was let s say manufacturing this might be created or made and this could be sold if it s finished goods if it s raw nmaterials. It could be purchased and then used..
But it s going to work the same nbasic way. And it s going to come down and give you the value of your inventory. Nat. The end.
Which is really what you want to accomplish here on a related note nif you are using google sheets. And maybe google docs google drive for nyour business. You can also use gmail with your own custom email address. I nhave an affiliate link down in the description.
And there s a little 20 off ncode for you once you sign up if you re interested go check it out that s all njust repeat this every month. Add on the number of the year. If you want to keep nit going for more than a year which hopefully you will then you have nyour own monthly inventory system in a spreadsheet if you want to see more nabout the wild wonderful world of accounting using google sheets next up nis a depreciation schedule that you can use it should be showing in the nthumbnail right now if you want to click through it of course. If you like nthis video.
It d be great if you could ” ..
Thank you for watching all the articles on the topic Google Sheets – Inventory Tracking System. All shares of bluevelvetrestaurant.com are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.