excell guru question?

larry merlau

Member
Messages
18,741
Location
Delton, Michigan
ok, we have 12 distributors that are supplying company A with various products. some are the same type just from different supplier. pricing is different between them. product codes are all different as well.. its the end of the year and we need to know how many of said product (no.8 brass screw) came from each supplier or from all of them combined. how should one go about it and how should the inventory be keyed in to achieve it in the future.. right now its mixed up badly. i do think they have it set up per distributor, but it isnt necessarily able to be compiled as it should be.
 
ok, we have 12 distributors that are supplying company A with various products. some are the same type just from different supplier. pricing is different between them. product codes are all different as well.. its the end of the year and we need to know how many of said product (no.8 brass screw) came from each supplier or from all of them combined. how should one go about it and how should the inventory be keyed in to achieve it in the future.. right now its mixed up badly. i do think they have it set up per distributor, but it isnt necessarily able to be compiled as it should be.

This is not an Excel question but a database structure question. Excel is just one possible tool you could use to organize all this data. If I understand correctly, the problem may be you have not established your own product code for these brass screws and have been keying in each distributors code for the item. Now you want to pull all the information on this product but you do not have a common search criteria (your own code for this item). Having built databases before, I know there are business processes that are important and probably not in your post above, that would be critical to know. It would require some discussion and investigating of the problem that is beyond the scope of this medium of communicating. I suggest you contact someone near you that has a good grasp of database structure and show them what you are currently inputting and explain what the outputs are you desire. If I was nearby, I would be happy to help.
 
makes sence bill... will look for some one closer.. and there is a old mentality that the old way is the right way yet complains that it takes so long to acquire info..
 
I'd agree with Bill. I sense that the underlying issue probably goes much deeper than just entering a few numbers into a spreadsheet.

Throwing caution to the wind though, I did just that...

It seems like a relatively basica accounting issue. I.e. keep track of how much you buy at what cost from what supplier so you can figure out an average cost per unit.

This example is probably far too simple, but there you go...

larry.jpg
 
Well Larrry i wholeheartedly agree with Bill and would go even further to say Excell is not a tool to be used in cases like this.
This is so typical of small bus and is a cause of where things go wrong.
The biggest issue is not Excell per say its the skill of those that set up its spreadsheets and those that come after those that set them up.

This is a very key part of the accounting system for any entity that holds revenue.
The key key issue you will find if you ever look at a an accountants use of excell when they do use it you will find they always use the concept of casting any table. (meaning they total any data both vertically and horizontally and compare the totals to ensure they are the same.
Why?
Well fundamentally accounting is a closed box. If the entity in question had to use a software package such as say Quickbooks, then what u find is the inventory both in quantity and value is only able to be removed financially by accounting rules embedded in the software. This means it can either be written off or sold but when each of those activities take place the implications of this have to pass through the profit and loss statement and then show up on the balance sheet.
So a small report such as who supplied what and how many is a very small part of what such an accounting system can provide.
It will provide much more when it comes time to value or write off for tax puposes surplus inventory or return inventory and obtain credit.

Now lets say a spreadsheet is created to do this.

1) It ends up being a standalone tool that now has to be seperately maintained. it ends up being prone to errors that are not picked up and when they are the cost and implication would have easily paid for a new computer quickbooks and the training involved to get a couple of people up to speed to use it.

In a small business this kind of thing happens more because
a) The owner or a family member has a basic knowledge of excell has a copy often not even a legal up to date one.
b) Its perceived as a quick fix to a problem that on its own (as Bill has astutely observed) is only indicative of bigger issue in both accounting and record keeping at the business.

Larry i would advise u to urge this business owner to take a bold business renewal step and use the opportunity of the end of the year to set up a proper accounting system which would include most importantly a inventory management element. I would go further to say make use in the business of the UPC Codes that exist on 99% of product and barcode scanners.
Why?
Data capture by the most astute person is flawed with error. Thats the reason the barcodes and scanning were introduced in the first place. They repeatedly capture a number accurately. Not the case when a human punches said number into Excell.

Hope this helps u make a case.



Sent from my MB860 using Tapatalk 2
 
ok lets go in a differnt route,, small business with many products, what would it cost for a entry level bar code scanner system..that would give hi the answers he is after and more.. and be user friendly to all employees?
 
Top