Project Management by Excel 1

[TWong]

For those of you out there working in pre-historic companies, tell me what you think about this. My company currently runs on a financial database only. That means no way to enter and track Bill of Materials, parts purchased against a job, forecasting...etc. Because of this, I have begun to maintain BOM's in Excel, where jobs can order parts and manually write down the PO#'s, who they bought it from, and the lead times. From there we manually check it once a week to see where we are on that job. This is also a problem as many different people are allowed to order parts and we often double order for a job. So how do you update a job so this doesn't happen again? You wait for a job to be invoiced and then you can print a report of all parts charged to that job. Usually, a 30 to 45 day process after the unit ships, if the customer pays on time. Tommy

 

[swertel]

Short of leaving the company that is stuck in the stone-age (of which I will be doing shortly), have you tried using Access instead. Since you already have your information (fields) in Excel, you can just import that spreadsheet into Access and the wizards will, more or less, create the complete database. You really don't need to know how to program the database, although it helps in order to clean up the user interface and get the feedback that you desire. I taught myself Access and can program tasks on it that really simplify my work. Granted, it's not the neatest database, but it works well enough for me.

After you become more fluent in Access, you can control user access so only those ordering have ability to modify certain fields, of which could be "Item purchased on..." and "By..."

There are probably several new groups on the topic of MS Access. You could post this message there. Most likely, someone has already come across this problem and may be able to share their database with you, with a few modifications. If not, you'll definitely find someone able to help you more than I. You may also want to look up news groups / forums for programs that use BOM's. For example, I'm a SolidWorks user and on that newsgroup I have seen many threads that deal with BOM's in Excel and transfer to Access with VB and VBA programs to aid the user.

--Scott

 

[kook]

Access is good - but Quiken may be better.

 

[visitor]

I'd also suggest that using Excel for anything more than one or two projects is probably the wrong application. Access is an improvement but even then you are limited to what you can develop.

For material tracking, invoices, and the other necessary evils that go with a project you really need an information management system built on a platform that is scalable. Now, there are even web-based applications that would work for you off-the-shelf. Be prepared to pay some $ but honestly if you invest a few hours per week of your time building and maintaining your homebrewed system you could have probably paid for something even better by now. You could get scheduling, availability and many other niceties for a reasonable amount. Go ask your competitors what they are using and then buy that system.

Good luck. Hopefully you find a new job soon.