Collaborate on Gold Jewellery Bill Format for Inventory with Ease Using airSlate SignNow

Gold jewellery bill format for Inventory

hi welcome to a tutorial for the inventory and pricing workbook from accounting for jewelers I just wanted to give you a quick run-through of what you can expect with this workbook this is ideally for someone that does a lot of one-of-a-kind custom work or if you have collections it's great because you can price you know each SKU and manage the number of finished items that you have on hand when you open up the spreadsheet it opens to the summary tab you can click up here directly to the instructions which is on the last tab read through this I definitely recommend it it has some good tips one of the things that you can do easily is enter in your company on the first page and it does that across all the pages and then you know what I recommend also is doing this for each year so at 2017 you will save this one as 2016 open it up save it as 2017 and carry it forward so that you have you retain that history of what went on in 2016 or whatever prior year so it's really good because you get to keep that ongoing history with you know rolling it forward to the new spreadsheet but you also retain that spot check so if you ever needed to go look you could look at what happened in 2016 and that's really helpful so all of these yellow cells are pre formulated for you you can always look click in a cell and see up in this formula bar where that information is coming from so this one's coming from the finished jewelry tab Selvi 30 so you can see where that coming is coming from it's very making the quantity on hand and so one of the things about this spreadsheet is that it is manually updated by you so you don't have barcodes you have to come in here and enter in your purchases and to the spreadsheet the relevant tab so we've got your your metals which is split by wire and she or you know you can refine it you more finding such as jump rings diamonds if you use them gemstones beads and you can delete the tag that you don't need to customize it for your specific business and you can rename them to whatever you want so it can be as customizable as you want it I mean the the spreadsheets are back-end to all of the inventory programs that you see so you really can do anything in here you could come in here and even with the chains you know if you wanted to add a picture of what it looks like so they you know rather than trying to say oh it was a Rolo that was two millimeters sometimes you know we're visual creatures so that can help but also in the finished jewelry you can add the picture of the finished good so you have you build out a database that has the picture all of the components that go into the finished piece and you're also managing the quantities of what you've purchased and what you've got on hand as well as what you've sold and this is a really good back-end to managing your inventory values and an accounting program so it's very comprehensive again all of your data in here you can enter in the prices and then as you use it you'll have to come in here and update the quantity in stock so this doesn't mean you this doesn't automatically update so let's say you were gonna use this casting and a finished good you could come in here and grab that amount so you just put it equal sign and we're going to go to castings and you received eight the unit price is 78 so that's going to be our cost for one piece and so you do this for each item and every component that goes into the finished piece and so this this formula is based on David Feldman's pricing formula so the muf and the duf are factors that you multiply your materials and designs depending on how difficult it was to source the materials or you know paying your designer yourself for the uniqueness so it's the materials uniqueness factor and the design uniqueness factor so it's a little markup and it you don't even have to do one at all but if you're using like black gold or a different alloy or you know the design is really different and unique you can pay yourself for that the allocated overhead is based on your income statement for a given period and how many pieces you've made during that period so the more refined you are with your accounting program the easier it is to get this number you can see that this one is multiplied by 0.18 so it's 18 cents on the dollar of your materials and labor is what that means and this reinvestment profit is that for every eight cents of the dollar of your costs that's gonna go back into your business and so it's important because you don't get paid for doing the research and development and finding the stones all the time you spent designing something you don't get paid for that and the metals and gem costs unless you're you are out of date adding it into your labor so most likely you aren't so this is where you have to make sure you're covering yourself for that stuff and that's why I really like this formula so this always gives your actual bare cost so it only shows without any markup so you have to cover that much then the wholesale price you know this this can vary depending on where you are who you're selling to what your products are most of the time it's a two-time markup sometimes it's two point two to two point four and then this is kind of a friends-and-family markup and then the retail markup is usually two to two-and-a-half times and again that depends on who you're selling it to where you are because costs in new york let's say are a lot more expensive than Oklahoma then the quantity completed you manually enter this is just however many of this product you made so if it's one-of-a-kind it's just gonna be one and this is where you do once you sell them you always have to come up here and and enter how many you sold the benefit of using a spreadsheet is that you do have complete control over it but it does require you to come in and manage it if you have an online management system you still have to go in and manually enter when you order something and when you make something so it's really not that much different but when you sell something then it's all automatically updated online so this is not automatic so you have to come in and update it because it doesn't integrate with your accounting software or a website or anything like that but this is then populated by the formula to let us know what's on hand and that feeds into the summary page so that we know the value of what's currently in stock and then the inventory value is the quantity on hand multiplied by the bare cost so it's not with the markups nothing else and it's not with your personal labor it's only with outsourced labor so we have to consider that - if you put in your labor then we want to change that formula to only capture the metal costs and then this is you know if you if you wanted to keep maintain a history of things that you've made but that you're making not making anymore then you can use this to to exit out and so then you have if you put an X in it it turns red so pretty much everything in here is pre formulated to help you and then this is also helpful because and I've got notes always anytime you see a little red mark it's because they've left a note for you you could change this to be whatever your current markup is and it's a good way to compare what your current listing price is versus what this formula tells you because while it's really good for this this formula is excellent to get a base and like you know your bearings I guess with your pricing to make sure you are covering all of your cost it's not necessarily in line with the market always so playing with your hourly wage for their labor cost playing with the markups for the wholesale and the retail price as well as how much you're giving overhead and reinvestment profit drastically change these prices so you can play with these and and also this is where it's really important to have a good handle on who your customer is because that dictates very much what your pieces are gonna sell for because it really doesn't matter what the price is what we come up with it matters what someone will pay for it always this is gonna be what you're actually listing it at so it's and you can add lines and you know things to look out for is if you came in here and added a line we just want to make sure that the formulas came down so you can grab all of these formulas and pull them down and then you just want to make sure that it's capturing the same information and depending on your comfort level with Excel you know you can you can take classes online always YouTube is a great source if you have a question on how to do something or just Google but I created this so that it's easy to have control over your inventory as well as a customized pricing structure and really being able to understand what you're buying and what you're using and your costs that go into the finish good and then also from you know my accounting perspective making sure that we have the correct and Venturi values so you know the cost of goods sold is never a cost of goods sold until you actually sell it so all of your purchased prices of all your raw materials and labor that you pay to contractors and casters and setters all of that is an inventory asset until you actually sell it and so this is the spreadsheet that I recommend using to really manage that and also what I want you to do is I've put in demo example information in here but you'll want to come in here and and remove it put in your information you know so don't keep this information in here because it comes over to this summary page and would mess up your actual values so just keep in mind that this is information and that you want to come in and put your actual information in here and replace that if you have any questions for me please do not hesitate to ask I am always open to improving this spreadsheet to help you I created it for you so that you can manage your inventory and understand your business better so I always want to improve my business and I can't do that without you so please let me know what you find complicated or that I didn't explain enough or any issues that you run into with this spreadsheet because I am happy to improve it and help you customize it for you so you can always reach me at support at accounting for jewelers comm or call during normal business hours to six one five nine seven five five four zero six and we are happy to help you [Music]