Create Your Childcare Invoice Template for Inventory Effortlessly

Childcare invoice template for Inventory

and today in this tutorial video I will be talking about the purchasing sales and inventory light version template I will not talk too much about the basic sheet designing concept but instead I will dive deeper into this template I will build all the sheets from scratch I will tell you guys the tools that I use I will explain all the relationships between each sheet so you guys can see and determine whether this is the suitable template to handle your purchasing sales and inventry system so so if you go to our template Library as you can see here we have two version of our PSI the first one is the complete version and we also have the light version and this one will be the one that we will talk about in this video so let's start with the difference between these two two template two templates the complete one and the light version one actually the biggest difference is on the inventory management as you can see here in the complete version there are many sheets to hand the Infantry while in the light version we only have one sheet so how do we handle the inventry in the light version so let's say for example you are selling clothes and you have this one product which is a white tank top the same product a white tank top if you store it in two locations let's say Warehouse South and Warehouse North you will create two entries in the infant in the Infantry sheet so one will be one uh white t to Warehouse South and another one will be white t to Warehouse North this is how we handle the inventry management in the light version so if you think that this is sufficient enough to handle your purchasing sales and infantry um you can choose the light version but then if you need uh more comprehensive system then you can go for the complete version and then because this is the only sheet to handle the inventory it means it will also it will also um um like inside the sheet we will also have two two prices one is the selling price and another one is the buying price because this will be the only sheet that contains the price information for the sales order but also for the purchase order and then here we have accounts and vendors in the light version we combine it as clients and suppliers this part um it is actually not so much of a big deal because let's say in if you combine it as one sheet clients and suppliers when you only want to say clients or when you only want to say suppliers you can use the filtering function or if you combine it uh when you make the sheet you combine it but then at the end you change your mind you want it as two separated sheets you can use the multiple versions so you can uh separate these two sheets but then they still share the same set of data and here's sales order and purchase order uh here is where you key in every time you sell s something or every time you make a purchase to restock something and then later on I will explain why we need these sales items and purchase item sheet so let's get started with building the sheets I'm going to create a new tab and call it PSI light and I'm going to start with customer sheet ah in this tutorial I will separate between uh customer sheet and fer sheet and I will show you guys how to use the quick templates so here I can just click and it will directly load the customer the customer sheet design and later on I can tweak it if there's any feels that I don't want or I want to add more information this is very handy because you know like you don't have to you can save so much time and then you you don't need to design the sheet from the beginning and I will continue with the fender sheet and then I will create the inventory sheet so if you remember just now I mentioned that this will be the only sheet to handle the everything like from price from stock from Warehouse so here I add I added a warehouse field because there is no Warehouse information in this field I'm going to make it a selection field because I know that I'm going to have only two Warehouse only two warehouses north and south and of course if you don't like if you only have one Warehouse one Warehouse maybe you don't need this field or if you have more than one then you can add the choice add the choices here and then I will also add the price inform like just now we mentioned that we need two prices one is for selling price and another one is for buying price and also for the stock because not only I want to handle my total stock I want the information of my total stock in this sheet I will also want the information of my initial stock I'm going to make it a numeric field I'm going to I'm going to change the field name to Total stock and my minimum stock so that I can I can make this template more advanced for example I want to be notified once I go below a certain minimum stock so that I can do something with it I can repurchase I can you know I can prevent I can prevent uh my inventory for from going below zero here is warehouse and now let's create some entries let's say Nadia is a customer I'm going to just quickly fill in the name and the email and of course if you have the complete information of your customer you can fill in the rest of the information I'm going to create another customer and moving on I'm going to create some Fender and contact with and moving on let's create the product that we sell first we will be selling some red t-shirt we buy it for let's say $5 and we sell it for 10 I store it in Warehouse North and another one is the white tank top we buy it for three we sell it for six Warehouse North and just now remember if I have this exact same white t top but it's the same one I I I oops I think I mix I I I didn't write correctly the selling price and buying price I will change it later supposedly my selling price is six my buying price is three and I store it in Warehouse sou it's the same white tank top with the previous one but I just store it in different location then I need to create another entry for it I'm going to change the price it's supposedly the selling price is six buying price is three and the red t-shirt the same buying price is five selling price is 10 so these three sheets are going to be be the source of the linked fields for the sales order and purchase order sheets what do I mean by that let's continue with making the sales order sheet I can also use the quick templates and I'm going to go to the form tools and choose link and load so I'm going to link the customer ID so it it will load the my customer name and customer email and I'm going to do the same for the product ID it's going to load the product name and the price will be selling price because it's a sales order once I make this link and load relationship every time I choose a customer ID then my customer name and customer email will directly be loaded and then uh if you if you fill in more information and you also create the link and relationship with more Fields then you can do the same with the customer's phone number or the billing address it's it's the same with the Infantry when I choose like for example red t-shirt in the product ID then the product name and the price will directly be loaded so let's do the same with purchase order sheet now I'm going to go to link and load I'm going to connect the fender information and inventory for the product name sorry product name the price will be buying price because it's a purchase order and here I'm going to change the field label so matches I'm going to click save so now uh we finished uh creating the purchase order too so now let's create a new purchase order for example I'm going to buy 50 red t-shirt and I've also sold let's say for example Nadia so when I choose Nadia all the information related to Nadia will be automatically loaded and today she buys three red t-shirt so just now if we go back to purchase order we can see that we have repurchased 50 red t-shirt and we have sold three red t-shirt but then what if I want to do something what if when I go to red t-shirt I want to be able to see all the sales history for example this and all the purchase history for example this that I've made for red t-shirt in a glance how can I do that in order to do that we have to input this information to the inventory sheet right so we will need to make this sales information subtable as an independent sheet to be able to input that sheet back to inventory sheet as a subtable it's on sound confusing of course but let's let's do it so you guys can know let's go to change design we use the new sheet from subtable Tool and we create a new sheet once the new sheet is created I'm going to call it sales items so just now I created a red t-shirt purchased by by Nadia in this sales order sheet in the sales items this will uh one entry will be created because there is one entry in the subtable so if I if Nadia make another purchase like white tank top she bought four another entry will be created created in the sales item sheet so for example if there are three sales order entry each with three sales or uh each with three sales items it means in the sales items there will be nine sales items entry now that we have created an independent sheet we can go back to inventory and we can use the tool show references from existing sheets drag the subtable I want the sales order number I want the quantity the unit price and the subtotal and I don't need the product name because uh in the inventory sheet I already have the product and I'm going to move it here I'm going to call it record make it a little [Music] pretty choose the color make it bold and VOA now we can see the sales record of this red t-shirt we can do the same for the purchase order so we are going to go back to purchase order and and then we are going to use show references oh sorry we're going to use new sheet from subtable create a new sheet now we created purchase [Music] items and then we can go back to inventory change design remember after that we can use show references from existing sheet drag the subtable product number I don't need a product name sorry quantity ordered unit price and and I'm going to name this as purchase recorde record so now we can see everything in one glance if you check the red t-shirt then you can see all your sales record and all your purchase record which means if you can already see all the quantity that you you have Sal and all the quantity that you have bought you can calculate your total stock right but then would you want to calculate your total stock if you are not sure about the status of the sales or of the purchase like you are not sure whether it is a new new sales order or is it a closed case actually or it is actually completed I think we should only calculate the stock if the status of the sales order or or the purchase order are already completed right so here but then here we don't see any status fil so then let's go back to the the sheet this sorry this subtable just now they they they were generated from the sales order and purchase order right so if you go back to purchase order as you can see here you have a status field there is new open closed completed but then if you go to sales order there there is there is no status field so I'm going to add a status field here and the default will be new so every time we create a new sales order the status will be new and after if it is already confirmed then we can change it to complete it now that we have added the status field in sales order and the purchase order actually have a status field then we should add this status field to the subtable in the inventry let's check our inventory sheet so it will be ideal if we have this status fi here right do you remember that this subtable is generated from the function uh shows references from existing sheet which sheet is the reference sales record will be sales items and purchase purchase record will be purchase items so if we go back here we use the tool link fields from parent sheet which is the inventory sheet sorry which is the sales order sheet we can drag the status field and add them in the sales items and then we can do the same for the purchase items use the tool link fields from parent sheet status the relationship between purchase items and purchase order the um their relationship is sub sheet and parent sheet because purchase items are is generated from purchase order subtable so this sheet is actually generated from purchase order that's why purchase order is this parent sheet and purchase items is the sub sheet now that we have added this to the independent sheet if you go back to infantry you can use the tool that we used just now sorry here show references from existing sheet and add the status field update this table at the status field sorry make it a little [Music] pretty click save now we have the status filed so so let's apply the formula so that we can calculate our total stock our total stock is supposed to be our initial stock added by our purchase record quantity deducted by our sales record quantity if their status is completed correct so let's apply the formula the initial stock added by some if status in e24 is completed then we add the quantity in B24 and we deduct some if E7 is completed then we deduct B7 click save apply this for going to all save [Music] records exit so if we go back to red t-shirt let's say our initial stock is 10 then our total stock is still 10 because the purchase record and the sales record all of them the status is still new but what if we go if we go to purchase order and we change the status to complet it if we supposedly if we check the if we check the inventory it should have calculated the total stock right it should have been initial stock 10 added by 50 because the status is is still completed but why does it calculate why is it still 10 this is a common Mistake by almost all rajic users it's because we didn't check the recalculate all formulas on on related sheet settings so the relationship between purchase order and infantry sheet is related sheets because they share the same subtable so in order for us to be in order for the inventory sheet to be able to recalculate every time you key in something in the purchase order sheet you have to check the settings it's the same with the sales order because because the relationship with the inventry sheet is also related sheets by default recalculate all formulas on parent sheets will already be checked um this is like for example sales order and sales item items sales items is the sub sheet and sales order is the is the parent sheet so now if we go back and we check red t-shirt as you can see here the total stock is 57 because initially we have 10 we purchase 50 the status is completed and then we sold three so it's 10 added by 5060 and and then we sold three so it's 57 so yeah that's basically it about the formula recalculation in the formula recalculation and the structure of the purchasing purchasing sales and infantry template let's create let's try once [Music] more Rosana she will buy another four red t-shirt and this is a completed sales order so if you go to product and then if you go to the inventory you can see that now the total stock is 53 because we just sold another four so yeah that's basically it about the purchasing sales and inventory template I'm going to tell you some tips so that you can make this template more events one of them is just now we talked about the status in the sales order for example new open close complet it if you find it annoying to manually change the status here you can actually do something which is create an update values action button let's say I create an action B to automatically change the value of status to complete it so by default when I create a new sales order the status will be new right but then when I click complet it it will update the status to complete it and I don't have to manually change here which will make your sheet more convenient you can do the same for the purchase order sheet and the second thing is what if for example in the inventory just now I have a minimum stock field which is like for example I want to set it as five so for all my product let's say I start with 10 for the white tank top and it's the same here and for all my products I I want the minimum stock to be five so every time if there is something that go that that goes below the minimum stock of five I will be notified how should I do that I can do that by adding a field let's say for example I'm going to name it lower than minimum stock and I'm going to apply a formula if total stock A7 is lower than minimum stock A9 then return to yes otherwise return to no I'm going to save and apply to all save records so if I go back this doesn't return to no because total stock 53 is basically way way bigger than my minimum stock but then what if I'm going to create a new sales order and someone bought 50 red t-shirt and it's a completed Sil order when I go back to inventory sheet my total stock will be three and it will return to yes because it is three is lower than my minimum stock five so what I can do here I can filter the entries that filter the entries and see the product that is lower than the minimum stock and I'm going to do this safe as few I'm going to call it lower than minimum stock so let's say if you see all your products but then when you filter here you will only see the product that is lower than minimum stock and you can set up notify me by setting up notify me um you will get notified every time there is a product that goes the minim this that goes below the minimum stock and if uh you already make the purchase and it's no longer on the on the on the lower than minimum stock filter you will also get notified there is another thing which I think is pretty uh ideal is what if um when you create uh uh when you create a new sales order here you can just stop seeing the product that is below the minimum stock in this case let's say for example the red t-shirt because it's already below the minimum stock stock basically I don't want to sell it anymore because it's dangerous uh what if I cannot provide the I cannot provide the uh the quantity that my customer wants what if what if um let's say for example uh I will never never want my product to go below zero right so what if by selling this then I go below zero and another person make the purchase and I go I I have a minus inventory which I don't want to happen we can do this by every time when you create a new sales order I want to stop seeing red t-shirt so I cannot make the sales of red t-shirt I can do it by using the dynamic filter setting so I want to only see the entry the subtable entry that matches the rule lower than minimum stock matches no so this way every time I want to create a sales order I stop saying red t-shirt because red t-shirt is lower than the minimum stock and another another solution is what uh another solution is I can still see the product that goes below the minimum stock but it stops me from saving the sales order entry if what I'm trying to sell is bigger than total stock this also solves the problem of uh overselling something that your inventry doesn't have for example I only have three red t-shirt but I want to place an order for a customer for five red t-shirt in this case I don't want the sales order to happen I don't want to make the sales because I cannot provide enough quantity so instead of not seeing it I want it to stop me from saving the sales order how should I do that so first let let's erase my Dynamic filter setting but I'm going to use the tool validation instead so in order to do that I need the information of my total stock right so I need to load this information from my Linkin load and after I'm going to add a field can I sell and I'm also going to reply I'm I'm also going to apply a formula if quantity D12 is lower than F12 so if quantity that I want to sell sell is lower than my total stock it means I can sell otherwise it means I cannot sell I'm going to save and apply this formula to all save records so for example I'm going to create a new sales order and I'm going to sell seven red t-shirt it's going to return to no because this is way this is way higher than my total stock but then if I'm trying to sell one white tank top it's going to return to yes because uh my quantity is still lower than my total stock I still have six and I can sell one so these are the two Fields but I think I don't necessarily need these two fields to be shown when I make my sales order so I can hide this field I can hide this field and I'm going to set up validation for this field I want the I want only the value only the only the this field with the value yes to be able to be saved so I'm going to create an error message quantity that you are trying to sell is higher than our in so when I create a new sales order and I'm trying to sell seven red t-shirt remember just now our our total stock of red t-shirt we only have three so when I try to save it's going to say Quantity that you're trying to sell is higher than our infantry and I cannot save this record which I also think is pretty uh ideal to stop you from overselling so yeah that's basically it about the purchasing sales and INF temp uh temp template also all the advanced tips just now we talked about validation we talk about Dynamic filtering validation is to stop you from saving the record uh when you try to sell something uh when you try to sell something that the quantity is way higher than what we have in our inventry and then Dynamic filtering is to stop you from seeing the product that is already below the minimum stock and another thing is you can also get notified if there is something that goes below the minimum stock and also the last one is the action button in sales order and PCH purchase order you can click completed and the status will change and then let's summarize a little bit just now at the beginning I made a sales order and purchase order sheet and I said that this will link to these three sheets customer Fender inventory so I use the link and load tool in for customer and inventory in purchase order so every time I choose the customer ID then the relevant customer information will load and it works the same for the purchase uh for sorry for the inventry and I did the same for the purchase order sheet uh every time we choose a fender the relevant information will load and also for the product information and then in order for us to be able to calculate the stock here and see all the sales record and purchase record we created a new sheet from subtable from sales information in the sales order sheet we created sales items and then we use the tool show references from existing sheets we use sales items and purchase items and we drag back the reference sub tabls in the Infantry sheet and then just now I also talked about uh formula we apply formulas here and also here if formula here and also in the inventory sheet just now and then we also talked about formula recalculation for the sales order sheet we have to check the setting of recalculate all formulas on related sheets uh in order for us to be uh to be able to have the inventory sheet recalculated every time we key in something in sales order and purchase order sheet so yeah basically that's it I hope it is useful I hope you can understand this template template and see that if this is sufficient enough to handle your purchasing sales and inventory and if you have any question you can send us a support ticket thank you