Collaborate on Google Sheets Billing Template for Purchasing with Ease Using airSlate SignNow

Google sheets billing template for Purchasing

okay so in this video I'm gonna show you  how I can create a basic inventory system   so it's not gonna be too basic but it's also  not gonna be probably way too complicated so   we'll start by creating a simple product list  so I'll just name this worksheet like items you   can call it anything you want obviously and  here I'll do the ID of the item or you know   whatever you want to call this and then we'll  do the name of the product or description and   I'll also do the pack size here and probably  that's it you can add more things here if you   see fit I'm gonna stick with this so my item  IDs so I'll just do some random stuff here you okay so that should do it so be our items  so I'll create another worksheet so this   will be where we receive new inventory  this is just items so I'll just copy   this headers from here we'll add a  couple of other things like QT why something like this so here I want a drop-down  of different item SKUs that we have available   here so I'll just go here and select pretty much  as long as you think this is gonna go I'll just   select all the way down so I'm gonna press here  command shift down select this entire area go   under data data validation and here lists from a  range if that's not selected go ahead and select   that click in this box click in this little icon  go to items and basically just select this list   and again you want to select more than what it is  just for future as you keep adding to this you can   either the warning or reject any other items  if you want I'm just gonna do reject anything   else hit save and what should happen now let's  just go on top here if I go to here it's gonna   add me these dropdowns where I'm gonna be able to  select one of the items that are coming from this   particular list right so if I'm trying to receive  one of these items like this one I want to auto   populate at least whatever information I can from  here which would be description and pack size so   I'm gonna go here and do a vlookup formula and  I'm gonna do lookup this comma and I'm looking   at that up in this items table starting from this  a column and going down so I'm gonna select this   much I'm gonna press f4 key to lock the table you  can also add to dollar signs manually it doesn't   matter I'm gonna drop this end for a reference to  just shift this all the way down so that's gonna   be the table of R a comma and then assuming this  was description it will be column number two and   then comma zero for exact match make this bigger  a little bit so now if I take this formula and   I drag this formula down well it's gonna give me  n A's for everywhere that it's not selected if I   select something it will autofill the description  for the item now I don't want to say this an ace   though so I'm gonna try to clear those so it  doesn't say any so what we'll do we'll just   put this inside of an if statement so we'll say  if this equals two double quotes meaning blank   then we want to leave it blank otherwise we're  gonna do this vlookup so I'm gonna go here closed   parenthesis hit enter and now if I drag this  down it should just be blank and if I select   something it should just Auto populate whatever  it should be and you should drag this formula   it's pretty much all the way down and I'm gonna  do that by doing command shift down or control   shift down and then command D or control D to  populate that formula down so now this should   all be pre-populated with that formula so as  you go select items here it should just populate   now I need the same four pack size so I'm gonna  take this formula copy that control C command C   hit escape go back here and paste now the only  difference here I want the pack size so that's   gonna be column number three from our source data  which is items so column number three so I have   videos covering all this functions vlookup if  ranges all of them separately in separate videos   if you want to understand in more detail what's  happening here go ahead and watch this videos   otherwise this should do so I'm gonna double click  here I think that went all the way yep it did good   so that's the pack size the same way we have  the description so that's gonna be populated   automatically now I want to do some definition  that shows that like these you select and these   are Auto populated so I'll just do a little bit of  background color for these columns so quantity we   are getting like 7 of this 4 of these 3 of this  9 of that so every time we receive new items to   our inventory we'll just do this so this will  quantity expended so what we'll do we'll just   take the pack size and multiply it by this so that  it will be the pack basically quantity and if you   are just buying by piece you can just do pack  size one for everything here so that's quantity   expanded I'm gonna double click and again that's  gonna drag that down apparently that dragged it   again up to here now I need this to go all the  way so again if I drag this formula down that   just gives me zero for all of these which is fine  I'm gonna stay with that so zero is okay and yet   again I'm gonna highlight that with a color so I  know that that's auto-populated cost per piece so   we're getting seven of this let's say we're  buying the six pack of that for two dollars so if your cost for everything is always gonna be  the same then you could incorporate the cost here   too but most of the time it's probably not so  you're gonna get your items received sometimes   at different costs as you buy them so that's why  I kept it here I'm gonna select this column and   change it to currency so we have this and finally  total cost is gonna be so I'm gonna do this times   this I guess I did this cost per piece not cost  per pack but this should probably be cause pack   not piece that makes probably more sense again  drag that down all the way that formula from the   corner and now we have this so this is a formula  I'm gonna highlight this too so basically here   we'll be entering the SKU our quantity impacts and  then the cost per pack and this should basically   just give you your total cost so if you want to  also get cost for piece you can add that here   too and just take your total cost and divide it by  quantity expended again drag that down so because   this is a division we're gonna get an error when  you're trying to divide by zero so we should   handle that so I'm just gonna put this inside of  an if error function and I'm gonna say if that's   an error comma then we're gonna leave it blank I  think we don't even have to do that just like this   should work in Google sheets in Excel you would  have to do double quotes so again ctrl shift down   ctrl D so now we have this and if I take this and  convert it to currency that should look prettier   and if you want it you could also add some borders  and stuff like that to make it look nice maybe do   some borders in a bottom that are thicker in here  something like this so this so white ones are the   ones we're entering and the rest should pretty  much auto-populate by itself over time see we'll   be getting the same item multiple times here like  here and then you'll probably buy the same thing   all over again see it pre-populates here and if  you go here and do like four of these and maybe   this time it's 210 or something it should just  populate the rest and we should be fine I'm also   gonna do another one for this coca-cola thing so  let's say this is three of those and it's cheaper   or something so now we're gonna do our sales side  I guess so a lot of days is gonna be the same so   I think what I'm gonna do I'm just gonna delete  this and take this receive and just duplicate this   and just call this one sales so this will be the  receiving side when we get new product and this   will be when we sell products we basically ship  them up you can call it receive or maybe ship or   whatever you're gonna call it so when we ship  our items out most likely we'll be shipping by   piece instead of shipping by pack but who knows  I guess it depends on your business so you have   to adjust based on what you do so I'm gonna do  sales now this part is gonna be the same we're   gonna take the item that we sold whatever it's  gonna be description is gonna again are we gonna   get the description we're gonna get maybe we don't  need the pack size in this case and if you wanted   to you could keep it but I think I don't need  it so I'm going to delete that now the quantity   we'll be the items that you sold so let's  say we sold 45 of this and 32 of that and   whatever numbers that would be it doesn't  matter you could still do the pack thing   if it was per pack you would just keep the  same thing but how much we're selling this   items for assuming that's gonna be per  piece I'm just gonna type some numbers this will be price per piece and this is  basically the same it's just this times   this that's total and then cost per piece we  already have that so we don't really need this   thing so this will be my sales side again I just  keep white for whatever we're entering and the   rest is gonna be in a color meaning it's gonna be  pre-populated with a formula it's just gonna work   out so we have our receiving side we have our  sales side now I'm gonna do my inventory side   so I'll just take this copy paste it and you would  probably also have a date in here someplace by   the way so date let's actually just do a little  column here use the format painter do this and   here for your dates you could again select this  area I'm gonna do again ctrl shift down command   shift down to select it data and then do data  validation and choose date it's a valid date and   reject any other input its save so now this will  be like a date picker so you can select the date   and we should probably do the same thing for  sales side so I'm gonna copy that go to sales   and paste that column over here so now we have  sales date as well now our inventory we need to   again get a list of our items in here somehow  so for that we'll probably have to get you know   whatever lists we have received I think populate  unique list out of this so I'm gonna go here and   do unique function and go to receive side and  basically just select starting from this well we   don't want the a1 in here we want to start from a  to the second row and then all the way down so I'm   gonna drop the end reference close parenthesis  and hit enter so that should populate a unique   list from whatever you have here because your  real item list might be a lot bigger than your   actual inventory list now with that we also want  to get the description as usual so that will be   this same formula pretty much so I'm gonna just  copy this hit escape go back to here and paste and then drag that down again control shift down  control D or command D if you're on a Mac and   the pack size is gonna be very similar I think I  already had it here so I'm going to just copy hit   escape go back to this and paste and drag it down  okay so we need total quantity received for each   so I'm gonna do received so the way we're gonna do  that we would have to basically just sum up all of   these received and maybe also all of these so I  need to sum up for that water one here here here   and I'm gonna do that using some ifs function so  I'm gonna go back to this let me zoom out a tiny   bit so I'm gonna do some ifs so I want to sum from  this receive spreadsheet this quantity column and   I'll probably not do the whole column but start  from here and drop the end reference and I'm gonna   do again f4 to lock this range with dollar signs  or you can again type the dollar signs manually   comma I want to do criteria range would be my  items here so again f4 to lock it and drop the   end reference comma and I want to do this for I'm  gonna go back to inventory and do it for this item   so that should give me the total for water so see  it says 20 which means from here it's basically   doing 7 plus 9 plus 4 which ends up being 20 so  that's correct and then we're gonna take all of   these and again drag this formula down and I  think I can leave with those zeros that's fine   so that's our quantity received we also want  to get received expended so we get by piece   and it's gonna be very similar I'm just gonna  use the other column so I'm gonna do some ifs   go to receive show this expanded column as  my some column f4 to lock this throughout   the end reference comma then I want to do it by  again item again f4 and drop the nth reference   to send this all the way down comma and then I'm  gonna go back to inventory and click on the item   again command shift down command B or control  shift down control D that would be that so we   have the number received now we also want  to get the number sold for each one of those hopefully you have better column names for your  columns than I do but it doesn't really matter so   I'll do some ifs again this time I'm gonna go  to sales select this salt quantity LOC remove   the end reference comma and then the criteria  range is gonna be again this f4 to lock it go   back to this comma and then finally go back to  inventory and click on the item ID again command   shift down command D that should do this so that's  sold that's received right so then we can do the   difference so apparently here we sold more than  we received which hopefully doesn't happen but who   knows so we'll do I don't know variance difference  whatever you want to call this I guess variance   would be not exactly that but so this is gonna be  simple I'm gonna do the received quantity - sold   quantity and then drag that formula down I guess  this will be quantity on hand so let's go receive   a little more of this so we have positive stock  so if I go receive add this and then do ten more   cases and let's say it's $2 anyway doesn't matter  so now we have C quantity on hand updated we have   received we have sold and we have the number  basically left and as usual these are formula   columns so I'm gonna go ahead and select all of  these I think these are also formula all of this   is formula columns as a matter of fact so we  should just go ahead and highlight all of them   so we don't touch this this is Auto populating so  we might also want to calculate I guess the cost   so let's see how we're gonna calculate the cost  for us so the cost is gonna be for this basically   the total cost which will basically just sum the  total cost for inventory received so let's do that so that would be again and out if function and  this time I'm gonna go to receive and do total   cost again f-for get rid of the end reference  comma and that's gonna be by item id f4 drop   the end reference comma go to inventories click on  this and drag that formula down and this should be   probably currency and we should make it the same  color as everything else so I also want to do an   estimated cost for quantity on hand now for that  we should probably get an average price for each   item you would probably do this in many different  ways depending on your situation possible to go   buy the latest price for the item that's one  way to go maybe that would probably make the   most sense when I'm thinking about this maybe  get the latest purchase item price for that item   and based on that estimate the cost instead of  doing the average so I was thinking maybe do the   average over time and see what the average is  but now that I'm thinking about it maybe just   get the latest price and multiply by quantity  on hand I think that's what I'm gonna do so now   I need to get the latest price for this water  one well let's see how we're gonna do this I'm   gonna take this this a column from received and  lock that and check if that equals two from this   inventory this item just for clarity I'm gonna  put this in parentheses and I'm gonna multiply   that by the row formula and I'll go to receive and  again select that same column f4 again to lock it   close parentheses and then I'm gonna make this  an array function by doing control shift enter   command shift enter didn't want to do that so I'm  gonna have to do this again there this if nothing   else works you can also just type a rate formula  function in the beginning and closed parenthesis   so what this is giving me is eros for everything  else he says two five six eight so if I go from   here in this list see it's basically water -  we have five we have six and eight that gives   us which rows have this water one in them now I  want to just grab the latest one which is gonna   be the H so I need to basically get the highest  number the eight out of here so I'm gonna do that   by just putting this whole thing inside of a max  function like this and that should give me eight   which is basically the latest row that this  water one was in now what that's gonna help   me do now I can get cost which is gonna be the  latest cost and we should probably change this   so we can actually see the difference 32 cents so  that's what we need to get so I'm gonna do index   from that so I'm gonna take this formula what I  have not the array formula just a max function   without the array formula part I'm gonna cut that  which is basically copy and delete and then I'm   gonna do index function here so in index function  let me delete this part the first part is gonna   be the column where we need the values from so  it's gonna be this cost column I'm gonna select   the whole thing f4 to lock it comma and then  I need the position so the position is gonna   be what the formula was so I'm gonna paste that  max function close parenthesis again hit enter   and there it is 32 that's the latest received  price at least per piece for that item so now   we should be able to drag again this formula down  let's see what happens when we go below okay good   basically we get nothing so that's good so I'm  gonna do a command shift down and command D to   drag that formula down now we got not the average  but latest cost I'm gonna call it or current cost   I guess that's better so now that we have the  current cost we can also estimate the value which is going to be our current cost multiplied  by quantity on hand drag that formula down so   that's estimated value for that particular product  or item whatever you want to call it now I'm gonna   add a couple of rows here select all of this  and clear the formatting now all of these should   be also in that color I should probably do this  colors in the end but here I am so the reason for   this I just want to have some place here to do the  total so we'll do some select this drop the end   reference close parentheses and that will be total  estimated value and any other totals you may want   to do you could do them here because putting  your totals in the bottom is a horrible idea now the only thing I want to add to  this I think is just average item cost you could just do the average right and do average  ifs and get the cost from here basically I want to   get the total cost for the item and divided by  total quantity so that would be I think what we   call a weighted average if I'm not mistaken but  somebody will probably correct me on that so if   you just wanted to do regular average you would  do average ifs and then you would go to receive   and basically just select this cost column and  f4 to lock it remove this end reference comma   and then select this column and f4 to lock it  and remove the end reference for that column   to comma and then go to inventory and click  on your number but instead what I want to do   I want to do this I want to do some ifs and go  to receive and add the total cost for this item   which is going to be this column and lock would  have four comma and then select this column again   lock it and remove the end reference comma and  then go back to inventory and click on this and   maybe we already have done that didn't we  that was our total cost received yeah we   have so we don't even have to do that part we  already have it here that's good we also have   receive expended which is I guess the other one  which is summing the quantities in it let's just   make sure that's doing what it's supposed to do  E and a so E is this a is this yeah so that's   exactly what it is so we don't even have to do  anything too complicated we just have to take   our total cost received and divided by receive  expended and that's I think pretty much it we   have to remove this division by zero error so  I'm gonna do if error around this whole thing again command shift down command D to drag that   formula down and we should  have a cleaner column now maybe Center your headers or whatever but keep  in mind anything I did in this tab is basically   a formula we shouldn't be touching any of this  pretty much at this point so I should be able to   now go here and add a new item I'm receiving from  this items list let's do that 16-ounce bottles   like this and then I'm gonna do 40 of that and  then it's let's say $6 or whatever that should   calculate the rest and if I go to inventory  see it's already here we have our totals we   have our quantity on hand we have everything  else and if we go to sales and or shipping   whatever you want to call this if you add this  amount it should basically just automatically   target this one as well and I suppose sometimes  you'll do some real inventory and find out that   your numbers in your stock are not exactly  the same as what shows up here so let me add   another worksheet for this I'm gonna call this  one inventory check so here I'm just gonna have   go here and do this this column I'm gonna copy  this a column paste it here maybe we'll also   copy this description column and paste it here  to quantity I'm gonna call it quantity offset   so let's say we did an inventory and we found  out that for this item it shows on hand 56 but   we really have 54 or something like that so  we have less - so it would be like negative   2 for this or maybe you found out for something  else you have more so maybe this water 16 8 more apparently this column is not tracked  down so I'm gonna make sure I do that   so basically we'll want to have these numbers  somehow affecting our inventory numbers here   we'll probably have a date on this as well so I'll  just copy and paste that column which it's gonna   be that date that you can select like when you  did this and okay so let's see how we're gonna   add all of that to this and that's really quantity  on hand so right now we're just taking this minus   that so whatever we receive minus whatever is  sold now to debt we want to offset our inventory   check quantity so we need to add all this up now  one other thing we have to remember is that you   could do this inventory over time again and then  the same item could appear here multiple times so   maybe here you have negative two at some point  so now I'm gonna basically just have to sum up   these totals by this and add it to our quantity  on hand so I'm gonna take this and plus I'm gonna   do some ifs go to inventory check select all  of that so LOC quantity offsets remove the end   reference comma and then select ID numbers f4  again remove the end reference comma and then   go back to inventory and click on the item number  hit enter and make sure I drag that formula down   so now we have this so we have 54 items because  we just did an offset of negative 2 so if I go   and change this to positive 2 that should go  to 58 because it was 56 and we add 2 so 58 and   now we can do stuff like this too so negative  too so go back here it's 54 and overtime if   we come back and do this again for this item so  maybe this time we found out we have like eight   more of these so if we go to this here we have  it in our inventory so basically inventory tab   is all auto-completed for us we'll be working  on all this other tabs so I'm just gonna move   this here inventories auto-completed we do the  rest in receive sales inventory and if we want   to do more items that we can work with we just  add it to our items list and I think that's good   enough for this video thanks for watching  please subscribe and I'll see you next one