Materials Excel Sheet - Page 3 - Plumbing Zone - Professional Plumbers Forum
CLICK HERE AND JOIN OUR COMMUNITY TODAY, IT'S FREE!

Go Back   Plumbing Zone - Professional Plumbers Forum > Business Zone > Business, Marketing, and Sales



Like Tree1Likes
Reply
 
Thread Tools Display Modes
Old 09-21-2016, 12:19 AM   #21
Junior Member
 
Join Date: Sep 2016
Posts: 8

View Yaser51's Photo Album My Photos
Default

Is this spreadsheet still available somewhere? I'm new here and did post a intro. Thanks
Yaser51 is offline   Reply With Quote
Sponsored Links
Advertisement
 
Old 09-22-2016, 01:13 AM   #22
Member
 
FranLand's Avatar
 
Join Date: Jun 2015
Location: Southeast Texas
Posts: 34

View FranLand's Photo Album My Photos
Default

Hey guys. The way I solved this issue was I contacted my supplier (buy at least 85-90% through them) and asked them to send me a spreadsheet of my order history for the entire last year. I believe since then they added this capability to their website when logged into your account so can do yourself - not sure which suppliers do it, but WORTH ASKING.

I guess if you don't use an accounting software or scheduling/invoicing software in the field you could just save the spreadsheet on your phone at that point and search it for items when needed.

After getting the spreadsheet, I went through and cleaned it up a little adding key words to the item description (this spreadsheet is going to be based on their inventory system - their internal item # & description, etc). Was able to create my minimum % markup by formulating a column in the excel spreadsheet so it would create a 30 to 50% markup on what my most recent cost from my supplier was to create my selling price on each item in minutes. Anyway - I added the columns I needed to just import the spreadsheet all at one time into my accounting software (Quickbooks).

Then once I had all the items created in Quickbooks by just importing this edited spreadsheet - I synced it with my mobile software the guys use in the field for invoicing and bam - all their items were there in one place with guaranteed minimum % margins on profits.

Now when they open an invoice and have to add material they start typing in the search terms (galvanized, etc) and select the right item that pops up and it's added to the invoice with the proper price.

All the invoices from the suppliers get entered into the accounting system as the orders/purchases are made - that way if there is a price change on my cost, the customer's cost is changed immediately to ensure I'm always making a certain profit margin on material.
FranLand is offline   Reply With Quote
The Following User Says Thank You to FranLand For This Useful Post:
plbgbiz (12-08-2016)
Old 05-15-2017, 02:26 PM   #23
Junior Member
 
Join Date: May 2017
Posts: 3

View VanClem's Photo Album My Photos
Default

Quote:
Originally Posted by FranLand View Post
Hey guys. The way I solved this issue was I contacted my supplier (buy at least 85-90% through them) and asked them to send me a spreadsheet of my order history for the entire last year. I believe since then they added this capability to their website when logged into your account so can do yourself - not sure which suppliers do it, but WORTH ASKING.

I guess if you don't use an accounting software or scheduling/invoicing software in the field you could just save the spreadsheet on your phone at that point and search it for items when needed.

After getting the spreadsheet, I went through and cleaned it up a little adding key words to the item description (this spreadsheet is going to be based on their inventory system - their internal item # & description, etc). Was able to create my minimum % markup by formulating a column in the excel spreadsheet so it would create a 30 to 50% markup on what my most recent cost from my supplier was to create my selling price on each item in minutes. Anyway - I added the columns I needed to just import the spreadsheet all at one time into my accounting software (Quickbooks).

Then once I had all the items created in Quickbooks by just importing this edited spreadsheet - I synced it with my mobile software the guys use in the field for invoicing and bam - all their items were there in one place with guaranteed minimum % margins on profits.

Now when they open an invoice and have to add material they start typing in the search terms (galvanized, etc) and select the right item that pops up and it's added to the invoice with the proper price.

All the invoices from the suppliers get entered into the accounting system as the orders/purchases are made - that way if there is a price change on my cost, the customer's cost is changed immediately to ensure I'm always making a certain profit margin on material.



I am currently trying to figure out how to fill out a spreadsheet to import into QuickBooks but am finding it difficult because the owner uses multiple vendors to buy his material from. Is there any way I can get an example of your spreadsheet so I can have something to go off of?? I'm just starting out here and have no idea what goes on in the plumbing world. All I know is there is A TON of parts he uses!
VanClem is offline   Reply With Quote
 
Old 05-16-2017, 09:30 AM   #24
Member
 
FranLand's Avatar
 
Join Date: Jun 2015
Location: Southeast Texas
Posts: 34

View FranLand's Photo Album My Photos
Default

Quote:
Originally Posted by VanClem View Post
I am currently trying to figure out how to fill out a spreadsheet to import into QuickBooks but am finding it difficult because the owner uses multiple vendors to buy his material from. Is there any way I can get an example of your spreadsheet so I can have something to go off of?? I'm just starting out here and have no idea what goes on in the plumbing world. All I know is there is A TON of parts he uses!
First - are you able to get a spreadsheet from the vendors showing recently ordered items, say over the last six months/year? Helpful if this has vendor item #, description, your cost. Plumbing is pretty vast, a lot of items will be standard across the board but some companies do more new construction, some focus on commercial repair, some are mainly residential service/repair, some do variety, etc. I'm saying that because one list of items for one company may have a lot of items that company doesn't order, while missing a lot of items they do. You can create one master spreadsheet with all the vendors to pull in, can even assign different preferred vendors so you know who the item is regularly purchased from.

Are you having an issue coming up with the items used or an issue with how it needs to import into QB?

If coming up with the item list - does the company KEEP INVENTORY? If not or very little, then you could begin to create the items as they are purchased and build database slowly. If there is a lot of inventory on hand then better to try to get the vendor purchase spreadsheets OR may have to walk through shop and create you own list with the owner. (This could be VERY time consuming, but may be the only way to do it.)

Also keep in mind when setting up inventory in QB - sometimes it's better to track the inventory items for pricing purposes & charging customers correctly but not as easy to track QUANTITY of items. I believe you can only change an item ONCE from it's original assignment type. Be sure you decide how to do this before creating the items in QB. If you want to get the item with description & price in but DON'T want to have to track all the quantities use "NON-INVENTORY ITEM" as the item type! This will save you a lot of headache doing this correctly the first time. If the company is not currently set up to track every item on every truck and what is in shop, set up as NON-INVENTORY ITEM. Until there is a system in place (I am not referring to just the accounting system) that will insure all material quantities can be updated properly, set the items up this way. You can convert them to "INVENTORY ITEMS" later (once) when you have a shop process in place.
FranLand is offline   Reply With Quote
Old 05-16-2017, 09:37 AM   #25
Member
 
FranLand's Avatar
 
Join Date: Jun 2015
Location: Southeast Texas
Posts: 34

View FranLand's Photo Album My Photos
Default

Quote:
Originally Posted by VanClem View Post
I am currently trying to figure out how to fill out a spreadsheet to import into QuickBooks but am finding it difficult because the owner uses multiple vendors to buy his material from. Is there any way I can get an example of your spreadsheet so I can have something to go off of?? I'm just starting out here and have no idea what goes on in the plumbing world. All I know is there is A TON of parts he uses!
If you can get the purchased spreadsheets from vendors as a starting point of type of items used along with his costs paid, I can help you set up a custom sheet to import to get a good base going. You really want to try to get this way if possible because then you will also have most recent prices from that vendor. If you don't have that you may end up spending hours compiling the item list & description and then have to get pricing on each of those items!
FranLand is offline   Reply With Quote
Old 05-17-2017, 11:06 AM   #26
Junior Member
 
Join Date: May 2017
Posts: 3

View VanClem's Photo Album My Photos
Default

Quote:
Originally Posted by FranLand View Post
If you can get the purchased spreadsheets from vendors as a starting point of type of items used along with his costs paid, I can help you set up a custom sheet to import to get a good base going. You really want to try to get this way if possible because then you will also have most recent prices from that vendor. If you don't have that you may end up spending hours compiling the item list & description and then have to get pricing on each of those items!
Thank you for your response! My boss would like to track all the items but he doesn't want to have different line items for the same part from different vendors. He also wants me to reword the descriptions in our spreadsheet so it's easier for his guys in the field to read. He is wanting to have our own SKU #s on these items and then update & track the quantity by the packing slip after they have received the items. He doesn't have one vendor that he uses a majority of the time for most of the parts he receives so I would possibly be adding the same item from various vendor onto one line item. I've never used QuickBooks before so I'm not sure if this is something that can be done.
VanClem is offline   Reply With Quote
Old 05-17-2017, 12:07 PM   #27
The Old (antique) Master
 
PLUMBER_BILL's Avatar
 
Join Date: Oct 2009
Location: ALLENTOWN, PA
Posts: 2,065

View PLUMBER_BILL's Photo Album My Photos
Default

Quote:
Originally Posted by Jaret View Post
Does anyone know if there is a formatted sheet with all the various plumbing fittings in for inventory tracking? Is there software? I started to build an excel sheet with the basic fittings one would use. Is there something out there where I wouldnt have to input each fitting?

Much appreciated.

J.
Jaret Nibco has all kinds of xls stuff you can use
Here is an old one from 2009. You can change these files if you want.
Prices are wrong but as far as I know the figure numbers are still in use. You may have to sign up on Nibco's site to use their files. I don't use them anymore as I am retired. On this forum go into tag search, in the search box type pricing lists. click on search a pricing 101 will show up, -- click on that. I think you will find what you are looking for.
__________________
Bill Parr LMP
www.parrsplumbing.net
For my alternate web site, click on ...
A little of this and a little of that
PLUMBER_BILL is offline   Reply With Quote
Old 05-18-2017, 01:28 AM   #28
Member
 
FranLand's Avatar
 
Join Date: Jun 2015
Location: Southeast Texas
Posts: 34

View FranLand's Photo Album My Photos
Default

Quote:
Originally Posted by VanClem View Post
Thank you for your response! My boss would like to track all the items but he doesn't want to have different line items for the same part from different vendors. He also wants me to reword the descriptions in our spreadsheet so it's easier for his guys in the field to read. He is wanting to have our own SKU #s on these items and then update & track the quantity by the packing slip after they have received the items. He doesn't have one vendor that he uses a majority of the time for most of the parts he receives so I would possibly be adding the same item from various vendor onto one line item. I've never used QuickBooks before so I'm not sure if this is something that can be done.
1) YES - you can create one item, and have custom fields for other vendor's item #s. (So can have several linked to the same item.) You can also assign that specific custom field to the purchase order for specific vendors. As in, Ferguson only sees their item # on the purchase order, if you order the same item from Coburn's, Coburn's only sees their item #. (You can customize all this stuff.)

2) Rewording the description - I had to do the same thing - mainly so it was easier for guys to search for items quickly to add to invoices. Purchase description in quickbooks for an item can also be different than sales description if you want - keep in mind. (If it looks better worded a certain way to customers than how it's worded to vendors - you need to customize actual purchase order templates & invoice templates.) This will be a lot of work getting up and going, but once done, well worth it. Then only have to make edits with new items being purchased.

3) Tracking. You are correct, you receive items against a PO to adjust inventory as it comes in if you are tracking QUANTITY (again, you don't have to actually track quantity when you do a database of items with pricing - you can still run reports on quantities purchased and invoiced, but just doesn't show quantity ON HAND). You can also just "enter a bill" from the vendor & use items tab to get the inventory quantity adjusted as it comes in if you don't do purchase orders in QB. When you say SKU #s - I am assuming you want bar codes on all items? I looked into this 2 years ago and didn't see an option that was really workable that synced properly to QB. May be better options out now, but other issues with this is making sure all is accounted for depending on the type of shop this is set up for (what type of work and internal procedures already in place that will support what you want to do).
Also need to know if you are trying to track inventory down to truck level. Also keep in mind inventory quantity is tied to the balance sheet (financial statements) of the company. If everything is not done properly then it will start messing with this and sometimes it is a pain to get it backed out correctly.

You are talking about a lot more than setting up a price list data base with items that can be used on an invoice. (That can be done along with tracking vendor item numbers and customizing invoices & purchase orders fairly simply - as well as watching mark up/profit margins on items.)

When you are trying to track QUANTITY - before you can do anything you need to know EXACTLY what type of system you want in place (for instance, if quantity is tracked when comes into shop, then tracked further to show how much is put on what trucks, and then adjusted when trucks make invoices you will need a certain version of QB). Also matters if trucks are doing mobile invoices on tablets that sync back inventory usage to QB - this in itself will need to be software that will integrate to the version of QB you decide you need and the mobile software will need to be looked at to determine if it has all the functioning you need. You need to be sure proper internal procedures will support what you want to do. If you are wanting scanner capability - that is another variable.

Sorry to say but finding the exact right set up for what you are talking about and then implementing it is going to require outside help. You must have very proficient knowledge with QB and the different versions, along with other types of software that syncs with it and many other things such as making sure the system/procedures you choose do not have gaps in them. He really needs to hire someone that does this type of set up your describing, it is pretty involved.
FranLand is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Buying Overstock and Out-Of-Business Plumbing Materials CFO Plumbers Swap 44 02-22-2009 10:25 PM
Material Safety Data Sheet (MSDS) A Good Plumber Plumbing Safety 10 08-08-2008 09:03 PM


All times are GMT -4. The time now is 06:29 PM.


vBulletin Security provided by vBSecurity v2.2.2 (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
Our Pro Sites Network
ContractorTalk.com | DrywallTalk.com | ElectricianTalk.com | HVACSite.com | PaintTalk.com | RoofingTalk.com
 

Search Engine Optimization by vBSEO 3.6.1