Results 1 to 9 of 9

Thread: excell guru question?

  1. #1
    Join Date
    Nov 2006
    Location
    Delton, Michigan
    Posts
    17,470

    excell guru question?

    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.
    If in Doubt, Build it Stout!
    One hand washes the other!
    Don't put off today till tomorrow!

  2. #2
    Join Date
    Dec 2006
    Location
    Bellingham
    Posts
    2,449
    Quote Originally Posted by larry merlau View Post
    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.
    “When love and skill work together, expect a masterpiece.” - John Ruskin
    “Nowadays people know the price of everything and the value of nothing.” - Oscar Wilde

  3. #3
    Join Date
    Nov 2006
    Location
    Delton, Michigan
    Posts
    17,470
    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..
    If in Doubt, Build it Stout!
    One hand washes the other!
    Don't put off today till tomorrow!

  4. #4
    Join Date
    Aug 2007
    Location
    Reno NV
    Posts
    13,353
    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...

    Click image for larger version. 

Name:	larry.jpg 
Views:	33 
Size:	37.2 KB 
ID:	71974
    Programmer - An organism that turns coffee into software.
    If all your friends are exactly like you, What an un-interesting life it must be.
    "A door is what a dog is perpetually on the wrong side of" Ogden Nash


  5. #5
    Join Date
    Jun 2008
    Location
    GTA Ontario Canada
    Posts
    12,243
    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
    cheers

  6. #6
    Join Date
    Nov 2006
    Location
    Delton, Michigan
    Posts
    17,470
    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?
    If in Doubt, Build it Stout!
    One hand washes the other!
    Don't put off today till tomorrow!

  7. #7
    Join Date
    Dec 2006
    Location
    RETIRED(!) in Austintown, Ohio
    Posts
    5,317
    [QUOTE=larry merlau;351656]ok lets go in a differnt route,, small business with many products, what would it cost for a entry level bar code scanner system...

    Try Amazon
    Jim D.
    Adapt, Improvise, Overcome!

  8. #8
    Join Date
    Nov 2006
    Location
    Delton, Michigan
    Posts
    17,470
    yea that gets you a reader, but you need software and a pc to run it correct? seems like they would offer a starter kit..
    If in Doubt, Build it Stout!
    One hand washes the other!
    Don't put off today till tomorrow!

  9. #9
    Join Date
    Dec 2006
    Location
    RETIRED(!) in Austintown, Ohio
    Posts
    5,317
    Quote Originally Posted by larry merlau View Post
    yea that gets you a reader, but you need software and a pc to run it correct? seems like they would offer a starter kit..
    This might work for you.
    Jim D.
    Adapt, Improvise, Overcome!

Similar Threads

  1. radius question, math guru's
    By larry merlau in forum General Woodworking Q&A
    Replies: 34
    Last Post: 12-15-2012, 10:05 PM
  2. question for those HVAC guru's out there.
    By Don Baer in forum Off Topic Discussion
    Replies: 3
    Last Post: 01-09-2011, 09:47 PM
  3. A question for all the BLO guru's
    By Jiggs Elphison in forum Finishing School
    Replies: 23
    Last Post: 12-14-2010, 02:25 PM
  4. Anyone Good at Excell?
    By Travis Johnson in forum Off Topic Discussion
    Replies: 10
    Last Post: 11-17-2008, 10:02 PM
  5. Question for those concrete guru's out there.
    By Don Baer in forum Off Topic Discussion
    Replies: 9
    Last Post: 01-23-2007, 03:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •