Hello! I have been a long time lurker and have come across a problem that i'm not sure how to proceed, and was wondering if people can give me some advice.
I have to work with a materials entry sheet (excel), for the construction of new bathrooms, and how they organise that sheet, is not how it needs to be entered into the MRP (Manufacturing resource planner). I have made an approximate generic sheet for how it needs to be entered, using similar formatting etc to the original sheet, however it is difficult and time consuming to look at the original schedule, and enter the materials, qty and price into the new sheet - and it requires the person entering to have knowledge of the MRP and where parts/materials/labour need to be entered.
People that create these sheets (estimators) are traditional "building" guys/girls, not manufacturers so they dont have the understanding of how a manufacturing plant works (and is different to a construction site). They do not have access to the MRP software, so cannot enter the data directly.
Now compared to the guru's on this board, I would only say that my skills are basic - intermediate (I have mostly done data analysis etc, with some VBA/macros). I am willing to invest time into the sheet, and learn along the way, but i'm wondering if excel is the best tool, or a combination of excel/access is better or even just straight out access?
Some details for those without a construction background :-
The original sheet is broken up into
Base
Wall & Ceiling Frames (materials)
Linings/Carpentry
Electrical/Mechanical Services
Plumbing
Sanitary Fixtures & Tapware
Floor & Wall Tiling
Preparation & Delivery to site
The new sheet, that is to be reference to enter the data into the MRP is essentially setup as it would flow around the factory.
So for example
Workstation 1 + Materials / Labour
Workstation 2 + Materials / Labour
Workstation 3 + Materials / Labour
...
...
...
Workstation 50 + Materials / Labour
Now each of the workstations does a specific function/task, and it may have multiple trades work there, and the materials/labour required are workstation specific. Multiple workstations may do the work listed in the original sheet (i.e. the parts/materials/labour for linings is actually split across about 6 workstations).
My problem is I need to find an easier way to get the estimators a way to enter data, easy for the estimator to enter, then easy for someone with MRP access to enter it into the system for production, based on the fact that we will likely have to enter every single bathroom as a unique entity and we have a few thousand bathrooms, you can imagine how much time this would take doing things manually.
I can think of many ways of doing certain parts of the above task, but wanted a reality check from others to see if what i'm trying to do is achievable within excel or access? The MRP Looks after scheduling (i.e. we just enter materials/parts/labour and delivery date and it calculates it all, creates PO's etc) so we really just need the data to be able to be captured so the data entry person for the MRP doesnt have a heart attack!
My thoughts at the moment - create something in Access (so I can export database data from the MRP straight to access) and create a sheet/template for the estimators. Ideally, if they could use the current estimate excel sheet as a front end, correlate it with this "new" access database and spit out a sheet for data entry to the MRP (and down the track, automatic entry to the MRP once some software development was done by the MRP people) that would be my goal.
Happy to provide examples of the estimators sheet, and the sheet that resembles how the data needs to be entered to the MRP if people require that to think about the problem.
Note also, that my experience with access is somewhere between 0 and 0. I have a basic understanding of tables etc, but havent ever written an SQL query etc, so anything access would require some serious time investment on my behalf (I am willing to do this, because long term this makes my job infinitely easier)
I have to work with a materials entry sheet (excel), for the construction of new bathrooms, and how they organise that sheet, is not how it needs to be entered into the MRP (Manufacturing resource planner). I have made an approximate generic sheet for how it needs to be entered, using similar formatting etc to the original sheet, however it is difficult and time consuming to look at the original schedule, and enter the materials, qty and price into the new sheet - and it requires the person entering to have knowledge of the MRP and where parts/materials/labour need to be entered.
People that create these sheets (estimators) are traditional "building" guys/girls, not manufacturers so they dont have the understanding of how a manufacturing plant works (and is different to a construction site). They do not have access to the MRP software, so cannot enter the data directly.
Now compared to the guru's on this board, I would only say that my skills are basic - intermediate (I have mostly done data analysis etc, with some VBA/macros). I am willing to invest time into the sheet, and learn along the way, but i'm wondering if excel is the best tool, or a combination of excel/access is better or even just straight out access?
Some details for those without a construction background :-
The original sheet is broken up into
Base
Wall & Ceiling Frames (materials)
Linings/Carpentry
Electrical/Mechanical Services
Plumbing
Sanitary Fixtures & Tapware
Floor & Wall Tiling
Preparation & Delivery to site
The new sheet, that is to be reference to enter the data into the MRP is essentially setup as it would flow around the factory.
So for example
Workstation 1 + Materials / Labour
Workstation 2 + Materials / Labour
Workstation 3 + Materials / Labour
...
...
...
Workstation 50 + Materials / Labour
Now each of the workstations does a specific function/task, and it may have multiple trades work there, and the materials/labour required are workstation specific. Multiple workstations may do the work listed in the original sheet (i.e. the parts/materials/labour for linings is actually split across about 6 workstations).
My problem is I need to find an easier way to get the estimators a way to enter data, easy for the estimator to enter, then easy for someone with MRP access to enter it into the system for production, based on the fact that we will likely have to enter every single bathroom as a unique entity and we have a few thousand bathrooms, you can imagine how much time this would take doing things manually.
I can think of many ways of doing certain parts of the above task, but wanted a reality check from others to see if what i'm trying to do is achievable within excel or access? The MRP Looks after scheduling (i.e. we just enter materials/parts/labour and delivery date and it calculates it all, creates PO's etc) so we really just need the data to be able to be captured so the data entry person for the MRP doesnt have a heart attack!
My thoughts at the moment - create something in Access (so I can export database data from the MRP straight to access) and create a sheet/template for the estimators. Ideally, if they could use the current estimate excel sheet as a front end, correlate it with this "new" access database and spit out a sheet for data entry to the MRP (and down the track, automatic entry to the MRP once some software development was done by the MRP people) that would be my goal.
Happy to provide examples of the estimators sheet, and the sheet that resembles how the data needs to be entered to the MRP if people require that to think about the problem.
Note also, that my experience with access is somewhere between 0 and 0. I have a basic understanding of tables etc, but havent ever written an SQL query etc, so anything access would require some serious time investment on my behalf (I am willing to do this, because long term this makes my job infinitely easier)