Collaborate on Bill Sample in Excel for Retail Trade with Ease Using airSlate SignNow

Bill sample in excel for Retail Trade

in the previous video we saw how we can set up the retail business manager Excel template for the initial setup and now we in this video we're going to see how we can enter the different types of orders so we are back in our Excel template we already have entered the basic information that we need in all the products prices and partners sheet and then we have customize the invoice and the purchase order the way we want so now we are ready to start entering the order information so so let's enter our first order so I'm going to put give the order number as P1 indicating that it's a purchase order and you could use your own method of naming the orders make sure that they are unique from one row to another you don't repeat the same order number other than that you can have your own Convention of naming the orders as you need and so I'm going to use P1 for purchase order one and then the order date is going to be the date when the order was placed and for me let's say May 1st 2016 the expected date is when I will be receiving the products from our supplier so in this case I'm going to say it's 5th May so I place the order on 1 I receive it on 5th and Order type I will choose purchase because we are purchasing products from our supplier and our supplier in this case is going to be the ABC company we expect the payment to be made which means that we need to pay our supplier let's say by 15th or of May and then there are some additional charges that we need to pay to the supplier let's say it's 50 and then the but he's providing a discount of 10 and then there is some notes that we want on the purchase order so let's say please sh quickly and invoice number we're going to leave that out because it's a purchase order and then tax rate let's say the tax rate is 10% so I type in 10 and we have not made any payment to the supplier yet so let's put zero so there we have it so we have entered the first order now in order to complete we need to tell the template what products are we ordering so for that we go to the order detail sheet here we would enter only four pieces of information so the first thing is this is for purchase order P1 and the product ID that we are ordering in this order is boys red t-shirt red shirt so BR and you can see the product name will appear here confirming which product you're ordering and then the quantity let's say we want to order 15 shirts and there is no discount on this so I'm not going to enter any discount that we also ordering in the same order girls red shirt and this one we want 25 units or 25 shirts and the partner will be giving us a discount of $1 or one whatever the currency is one one per shirt so I'm going to put one so let's look at how these calculations work we are ordering 15 units of the red shirt for boys and the unit price is five this is automatically populated because of the price information that we entered here for this product the template will pull that into the order details so each you each shirt costs five and we are buying 15 of them so 15 time 5 is 75 and there is a tax of 10% that we applied to this order so 7.5 and then the total is 82.5 for the next line for the girl shirt we're ordering 25 and each of them again five is the cost but we're getting a discount of 1 so 5 - 1 will be 4 so each shirt will cost four and 4 * 25 is 100 and the tax amount of 10% on the 100 is 10 and the total is 110 so this is how the calculations are done in this template now we don't have to touch any of these all we have to do is to enter these four information and then we are good to go now we are ready to go and see how what we have entered is impacting the template now if I go to the product sheet I will see that the inventory onand calculation has now updated to say that we have 15 unit units on hand and it says on hand because currently the today's date is June 3rd when I'm recording the video now we can go to the invoice sheet for example no purchase order sheet and we can put in P1 and we can see the purchase order information for this specific order that we put and we can see all of those calculations automatically update tax discounts and charges and we get the total order amount we get the notes order notes that we typed up here here everything is is good to go and we can print or send it to PDF so this is how the data that we entered is now populating the rest of the template now let's go to the order headers again and this time we're going to enter a sale order so I'm going to call it S1 we're going to enter um the order date to be 6th of May because we are receiving the products on 5th of May so on 6th of May we will be entering and let's say we expect to ship this to our customer on the same day so now I'm going to put sale and we are selling it to our customer the customer is XYZ company and the uh Let's ignore these fields of information to keep the demo video short but basically you enter your order information here for S1 and then go to the order details and type the what are we selling to this customer let's say we sell the red boys t-shirt for 10 units and then the gr are the girls red shirt 20 of them and so what has happened now is we have purchased already these two shirts types of shirts and then we are now selling them to the customer right and the calculations for amounts and taxes work the same way and if we have entered any tax rate let's say this tax rate is 10% again again for the sale order then you will see that the taxes get calculated again here so this is how we have created a new a sale order the sale order will reduce the inventory on hand so now let's go back and check our inventory we can see that we only have five units on hand for both and that's because we got 15 from the partner we sold 10 of those so remaining is five we bought 25 of the girls shirt but then we sold 20 of them so now we only have five so that's how the inventory calculations work and now let's go to the next type of order before we go to the next type of order um I want to also say that the difference between the the purchase and the estimate is nothing but the when you do a purchase order the inventory calculations will update because it's a confirmed purchase order but an estimate is a purchase order that is not confirmed yet so this is just for you to enter it you you can print it but it will not update the inventory and add more inventory of your products because it's an estimate the quote is very similar to a sale order but it will not impact the inventory calculation by reducing the inventory because you're not actually selling yet because it's just a quote for your customer so that those are the other two types of orders now let's assume that our C the partner sent us those units of inventory but then we found out that there is some damaged um inventory or damaged product in what the partner sent us so now we can handle that in this template by entering another order so I'm going to call it PR for purchase return the purchase order we're now returning some of that so I'm going to call it pr1 and then I'm going to enter the date because we found out on 6th May that you know the there is some damage so I'm going to put in 6th May and we're going to send it to them on the same day and the this is going to be a purchase order and this is going to be for our partner ABC company and what are we returning back to the partner so we come on to the order detail sheet and let's say we're going to return the boys red shirt because out of the 15 units that we received let's say five of them were defective so I'm going to put minus5 this is very important when you you do a return to the partner or the return to the supplier make sure we enter a negative value and then the the you you will see the rest of the calculations work very similarly we don't have to do anything different but now when I go and look at the inventory on hand we don't have any inventory for boys red and again let's walk through this again we got 15 and out of those 15 we found five are defective so we are sending them back and then which means 15 - 5 is 10 and then the first sale order to the customer we sold all those 10 so we have nothing in on hand right now so that is how the calculations for the returns work now let's assume that our customer who received are also returning there for some reason they are returning back to us so how do you handle a sale return so let's look at that now so this one works very similar to the purchase return except we are going to actually do let's say the customer returned it on 10th May and they're returning you know basically the XYZ company will be returning our sale order so we'll be creating a sale order but the main difference here is when we go into the order details and enter what are they returning to us we will put a negative value and so they're returning five units out of the 10 that they purchased from us they are returning five of them back to us so what this will do is um let's go back here to the products now we will have five more on hand because the customer has returned five back to us so the supplier the sale return order is working fine in terms of calculations now these five units let's say that they are not usable anymore and we can't return it back to the partner too so now we want to tell the template that these five units on hand are not usable anymore and we're going to trash them and so we want to update our inventory calculations to reflect that we have zero on hand so this is where the New Order type called adjust will come in so I'm going to call it A1 order and let's say we do it on the uh 11th May and and it'll be and adjust order type for this we don't need to enter a partner because this is just adjustment of the inventory and no partner is involved in this case so we go to the order details we will enter A1 and we're adjusting BR and we want to reduce the inventory by five so we type in minus 5 and if um we want to for some reason increase our inventory then you could put a positive value here in an adjust order so the adjust order is very flexible now the one thing it's important is if the if getting rid of these um you know five units of defective inventory uh is not going to cost anything additionally for you then let's make sure that the amount comes out to be zero so I'm going to type in 10 here as discount and this would mean that the order will Zero out so it will not impact the cash or the finance calculations at all so we are adjusting our inventory to be ne5 meaning reducing five units of boys red shirt and then we are the normal sale price of this is 10 we are giving the entire thing as discount so they will not be impacting our financial situation at all because we already have taken into account that when the supply when the customer returned the five units we're already being penalized by NE 50 so the cash or the cash impact is already been taken care of so the adjust of inventory should only be on the units or the quantity and not on the finances so that's why we do a discount so we have gone through in this video so far how you can do a purchase order a sale order you can do a purchase return sale return and adjustment of inventory and we can see as we go through by entering this data we can see the inventory on hand here will update in the products table and we can also see these things reflect in our report sheet which we will see more in detail in one of the future videos but the data that you enter in the order headers and Order details will be reflected in other sheets as well and just like we saw how we can create the purchase order we can also create the invoice very easily by just typing in this is my sale order show me the invoice and exactly as we customized it you can print or send it to PDF in the next video we'll be seeing more in detail on invoices and purchase orders thank you very much for watching watching this video