Bill of Materials and Inventory Reductions

[Audiman]

Hey All,

Your help is needed. I am setting up a mini ERP and need help with bill of materials, production entries and inventory reductions. Basically, I have a list of raw materials, finished goods, bill of materials and production entries. I would like to have the following:

When a production entry is added, the finished goods will increase and the raw materials will decrease based on the components of the bill of materials. Each finished good is composed of approximately 2 or 3 components.

Your help is much appreciated. Thanks.

Audiman

[dflak]

A sample workbook with non-sensitive data will help a lot.

Also some questions: Do the finished goods have a unique identifier? Do the parts within the bill of materials have unique identifiers.

This would be something much better handled by a database, but we can make Excel "make do."

How do you want to address the issue of replenishing parts?

Some more information about what data you currently have and how it's set up will help.

[6StringJazzer]

What you are asking is a rather large task starting from scratch. You have not given anywhere near enough information to do this. You may not find anyone willing to do that much work for free. I suggest you develop an Excel file with all your data, showing how you want the results to appear. Then we can help finish it.

Alternatively you can repeat the above post in our Commercial Services thread and offer to pay for it.

Some of the questions that you will need to answer regardless:

1. "When a production entry is added" How does this happen?
2. "finished goods will increase and the raw materials will decrease" How are these increases and decreases represented in your data?
3. "based on the components of the bill of materials" What does this mean? What is the relationship?
4. "Each finished good is composed of approximately 2 or 3 components." How do we know which components make up each finished good?

[Audiman]

Sorry for the brief description. Sample attached. Thanks.

[dflak]

As 6StringJazzer said, this is a rather tall order.

We’ll start with the production entries. How many operators do you have and are they all trying to access the same spreadsheet at the same time? Also even with data validation, there are some checks (like leaving a field blank) that can’t be accomplished without VBA. Also data validation can be defeated by a simple copy / paste.

Here is how I would design the system. There is a central “database” (back end) that contains the data. Multiple “front end” spreadsheets can be distributed to the operators.

Upon opening these spreadsheets, they will load whatever data they need from the back end. Instead of spreadsheets, Excel forms will be used for data entry. This gives you tighter control over the format and allows to perform as-needed calculations.

When the operator completes a job, the commit button is clicked with creates a series of transactions to increment product and decrement raw materials per the BOM. These transactions are transmitted back to the mother ship – I mean back end. The operator can then start a new job. Perhaps the next job in the queue for that machine can be transmitted at that time.

The system will be transaction-based, and the actual number of whatever is never stored, but always calculated as the sum of the transactions. You might have to store the results temporarily when checking reload points.

You’ll need a side module to do inventory adjustments based on cycle counts, and to resupply the raw materials – again done by forms that create transactions.

You’ll probably need another side module to reverse a bad transaction – one entered in error.

There’s a bit of work involved in setting up a transaction-based system, but there are a lot of benefits: auditing for one. You know where everything’s going and when it went there. This will help you with forecasting. Reporting is almost automatic: a pivot table summing up the right kinds of transactions can tell you just about anything you want to know.

Having multiple front ends eliminates competition for the spreadsheets. It also compartmentalizes the work – the operators don’t have to worry about even seeing the data for reordering. And management doesn’t have to see the details of the work, but they can if they take a look at the transactions or a summary of a subset (like a day’s, week’s or month’s production) thereof.

Whoever is going to take on this project will probably change the way your spreadsheet looks now.

[Audiman]

Thanks so much for your feedback.