Managing / Organizing / Viewing Summary of data

[DKOAKL]

Dear All,

I have just joined this forum. I find Excel very interesting and have been working with Excel 2003 for few years now - but just basic worksheets and formula's. I run a small manufacturing business and cannot afford to invest in special softwares but I want to know whether it is possible to computerize all my paper ledgers into excel worksheets with automatic analysis / snapshot / overview. Below is my business details:

We manufacture small components for several customers. Each customer has a list of items.

So there are several raw material types, different customers, different products.

Basically, I want to use excel to know the finished or in-production status of different components.

Raw Material (Metal in diff. sizes): For eg. AL 18 items + MS 28 items + CU 6 items + BR 7 Items + SS 3 Items. Total 62 items and this could change by 5%. In our Paper register, for each item variety we keep a page and following are the columns:
(a) Date (b) Details (c) Receipt (d) Issue (e) Balance

Next the Raw Material conversion into products starts. Say Raw Material 'X' into Product A but there are 1/2/3 interim processes depending on the product. Each Raw Material type gets converted into 4-5 product types (on an average) i.e. approx. 300 product types which can change by 5-10%
So next for each product and process, another tracking sheet with same columns as above i.e.
(a) Date (b) Details (c) Receipt (d) Issue (e) Balance


Now my problem is as follows:
1. Do I have to create separate excel sheets (like existing Paper Ledger sheet) for each Raw Material type?
2. Again do I have to create separate excel sheets for each Product / Process type?

Actually all the Raw Material / Product Processes do not have daily entries but if I have to create different worksheets / workbooks for each item, how do I summarize?

My target is that in 1 excel sheet, I get to see a summary of my entire inventory (in which ever stage - Raw Material, Product - Process1, Process2, Process3) and also keep control if there are any gaps in the process.

Next at the end of Process, I have Finished Product which again will have same entries
(a) Date (b) Details (c) Receipt (d) Issue (e) Balance

For this I was reading some excel tutorials where it is stated that it is possible to link different excel sheets / files. But before I venture into this experiment, I seek honest guidance from Excel Experts like you.

PLEASE NOTE THAT DAILY THERE ARE MAX. 25 ENTRIES TO BE MADE. but then I will have to search from the 60 + (300x3) = Total approx. 1000 excel worksheets / files. Whether this is the right approach? Or what should be done?

Thanks in advance for your patience in reading the above and also helping me.

[Pepe Le Mokko]

Can you show us what you achieved so far?

[DKOAKL]

Please find the Raw Material and Finished Product excel files. Each Raw Material and Product data has to be entered in similar way.

[DKOAKL]

As required, have uploaded 2 excel files. Looking forward to your comments. Thanks.