Create Your Bill Excel Sheet for Retail Trade Effortlessly

Bill excel sheet for Retail Trade

this video is about how to get started with the retail business manager excel template will be starting from a blank template and then will start entering the data to do the initial setup of the template so this is our excel template we are in the setting sheet so the first thing we would want to do is to enter the business information so our business name and the addresses and contact information for our business this information will be popularly used to populate the invoices in the purchase orders that we will see later so now I'm going to enter the product categories in our business so for this simple demo I'm going to just use only two categories so let's assume that this is a t-shirt or short business that we are doing and then I will be entering a couple of product categories so I start entering here in just under the column label here I'm gonna call them as boys shirt and then let's say the second category is girl's shirt I type like that you can instantly see that the border line extended here which means that these two are included in my product categories table so I can always confirm that when I click on this cell I will see a table tools ribbon up here at the top that means that I have entered this data inside the table so now we have entered the two product categories we are ready to move on to the next step which is entering our products so I go to the products sheet so this is where we will enter each product in a separate row and the ite column is the unique identifier of a product so for example let's say my first product I'm gonna call the ID to BB are for boys shirt red color and then I will enter the name as boys red and description is just for our reference so that you can identify or document more details about your specific product so let me do a description like this starting inventory is the inventory that we have when we begin our business using this template so let's say as of now I am starting a new business so my starting a new entry is zero I don't have any inventory yet so I'm going to put zero and if you do have any products right now one hand then you would enter that information here reorder point is let's say five so if this products current inventory is 5 or below then we have to place a new order to replenish the specific products so that's what this means so we would enter the reorder point here and let's say I'm going to call this as just unit we are going to measure in unit or let's say pieces and that is the unit of measurement this is again just for displaying in the invoice or purchase order so whatever the unit of measurement is for your product please enter that here now categorizing our products we will have the list from the previous page and so this is a boy shirt so I'm going to select that if I don't want to track inventory for certain products I could choose no if I don't want tax to be applied to a certain product when ordering then I would say no but in this case I am assuming the I want to track the inventory and I want to apply tax to this product so I'm going to leave both blank the custom field is if I want to store any of my own information I can just rename this blue field and enter my information right now I'm going to skip that step this is only this is optional and I'm going to ignore all the green cells at towards the right because they are calculated and we don't have to touch them now I'm going to enter three more products quickly okay so now we enter those three additional products and we have two boys shirts and through two girls shirts there are different colors and then we have entered the reorder points and everything now we are ready to move to the next step one key thing to remember is when you're entering the rows in these tables make sure that you start in the row that follows the end of the tables so that automatically the table will expand and start including your new data in all the calculations now we move on to the next step which is entering the price information in the prices sheet where we can actually go and choose any product and then effective date is the date when you begin using this template so I'm going to assume here that I'm starting the business and using the template from May first so I will use May 1st as my effective date and again if you are starting to use this template Midway during the Oh business then the date when you are going to start using this template put that date in there and then enter the purchase and the sales price for your products so in this case I have four products so I'm going to enter those four very quickly now so now we have entered this an innocent case the price of a product changes in the future we will enter for example if this specific shirts price changes on June 1st so I would just enter this and then let's say the purchase price still remains the same but I want to sell it now for $15 then I would put 15 so what happens now is then all the orders on May 1st until June 1st we'll be using this row for price information but then from the from June 1st the template will actually use this to calculate the prices so this is how you can monitor even if it is a if the prices are changing you can easily use this template to track that I'm going to delete this because I don't want to change the price for this product for this demo now the purchase price is the price we pay to our suppliers to purchase the product and sales price is the price that we would sell to our customers at so that those are the two prices and make sure that both prices are populated for each row and now we go to the partners sheet to enter the basic information about our partners so this table is going to have both suppliers and customers together so let's say for example our first partner is the ABC company and this company we can enter the shipping address and the billing address for the specific company and then we will move on to the next row to enter the next partner keep in mind that it includes both customers and suppliers but we don't have any distinction between the two types of partners we treat them the same and it helps a lot because we have one list to maintain and it's very easy to organize the data this way so now I'm going to continue entering more information about a couple of partners and then we will move on to the next step so now we have entered two partners and then we can have a custom field to store any information that we want other than what's already here and then we can rename this field and start storing more information and then we'd not we won't be touching any of the green colored cells at this point because those are calculated now we are ready to move on to the invoice sheet so we'll skip the next two sheets and go to the invoice sheet so we can customize the invoice and so what we're going to do now is to do some basic setup and save the file so that every time we create an invoice in the future then it will have the right information so the first thing is to change the logo so I'm going to right click here click Change picture and then this will allow you for example to choose a picture from a file in your computer then you could choose that and then insert your logo there and you can also edit the logo by you know going to format picture and then once you have your own image here then you can go and do for basic formatting off the picture if you want to change the size you can you know click and drag make sure that you know you end up with the logo the way you would need it it's very very flexible and you can customize it however you would like so the next step is to make sure that the fields here represent what you want to display on the invoice for example here is the the partner information so the customer name and billing address and shipping address will appear here and then the customer contact information will appear here and then order information such as the date of the order payment due date and all this will appear here so something to keep in mind here is all of this is customizable so you can click for example let me zoom in a little bit so you can click on any of these cells and then the drop down menu will appear for you to choose from a bunch of fields and these are data that actually exist in the partners table you are able to choose any one of those and for example I want the phone at the top I want the tagname here just set these up the way you would like and then when we actually pull out an invoice it will automatically populate the right information so this is the initial setup of choosing the fields that you want so let's say for example you don't want this ID field click on that hit the Delete key and now it won't appear anymore and if you want to put it back then you can always come back and change it to say hey I want the custom field to appear here or any of the other fields here you can easily change it this is very very simple and finally we can also change the list of columns we have in our invoice so instead of product ID if I want quantity you've got to come first I change that to quantity and I change this to product ID for example and then you can readjust your column that's if needed and if I don't want anything to be in this column I can just hit the Delete key and now this is all great so since we haven't entered any order data yet you're not seeing any data being populated but once we enter the order data you will see that the invoice will have the information that we are asking it to have so this is very straightforward change the fields display ing to your needs and then the same thing applies to the purchase order sheet we can change the logo again and then we can change these information to what we would like to have on the purchase order the purchase order the fields that are displayed are slightly different for example our business information will come up here as the ship-to and bill-to information because this is a purchase order we can enter the order number here and then it will auto populate for us then we can print or export to PDF so now the final step in our setup is to set up the currency values so let's save on our invoice what we are going to do is to press ctrl-g choose CU RR for currency hit OK you will see that the template has now selected these specific cells what we're going to do is press control one which will open up the format cells dialog box basically what we're trying to do now is change the currency or format for these cells to be currency and you can choose any of the currency ing to your business needs and then let's say I choose dollar here and I'm going to hit OK now all of these cells have now changed currency so this is how you can customize the template to any currency that you would like and you can repeat the same process here in the purchase order come over to the sheet press ctrl-g choose this and then press control 1 choose currency I'm going to apply dollar and now you will see that dollars appear in these cells so this is how you can customize it and set up the currency so that concludes the steps that we need to take to do the initial one-time setup to get started in the next video we will see how we can enter orders in the order header sheet and the order detail sheet there are five different types of orders we can do and we will go through them each of those in detail in the next video if there are any questions about those templates please let me know in the comments I'll be very very happy to respond thank you very much for watching this video