Production Optimization - EXCEL / VBA Resource Booking
- or -
Post a project like this1487
$350
- Posted:
- Proposals: 15
- Remote
- #2747869
- OPPORTUNITY
- Awarded
Senior Full Stack Web Developer ✮Blockchain Expert ✮Experienced Graphic Designer ✮WordPress Expert ✮Virtual Assistant ✮ , excel expert ✮ PHP
Islamabad
Excel | Google Sheet | User Interface | Web App | Data Scraping | Internet Research | Photoshop Service
Jakarta
Graphic Designer |Experienced Web Designer | Video/Audio Editor | PowerPoint/Keynote | Content Writer |
San Jose
57355250666351470144947321951692404895242805426125852656985336984534314163761852
Description
Experience Level: Expert
Estimated project duration: Not sure
We are upgrading our production booking system for our company. Currently we run with a manual input for project work order bookings and manually controlled the constrained resource allotments. We believe that this can be solved better with either VBA, linear programming or a genetic algorithm.
PROBLEM OVERVIEW:
1) New projects are added to the work file. They contain the anticipated amount of work required across a number of machine centers. Machine operations need to be in sequence, IE: finishing can't occur prior to fabrication.
1.1) Projects contain multiple truck loads of materials. Each load needs to complete all work parts prior to shipment. However, project information is not currently presented in "work order per load" instead if a project had 8 loads you could estimate that when 1/8th of the final production task was complete, that first load of 8 would be ready for shipment.
2) The project work order is constrained by start date (Issued for Fabrication), 1st shipment date, and a last shipment date (must complete by)
3) Each work centre has a daily maximum booking capacity
4) Projects should not take over 40% of production capacity within a given 2 week period. ( We will want to be be able to change this % and time period constraint). Each machine centre may have a different % max allotted that any single project could book out.
6) If done with a goal seek outcome the goal would be to ship as many trucks as soon as possible and maximize resource (work centre) utilization. Trucks will be tied to revenue recognition for the company and that can be used as a success metric ( along with the delivery constraints).
6.1) Product can be produced and then stored prior to shipment, the goal is to keep all work centres at either full or a consistent capacity, and then delivery on customer shipping dates.
7) If constraints cannot be met, IE production is overbooked and the final shipment or first shipment cannot be made in time then these items need to be identified with the next best possible solution implemented. Project priority levels can be assigned if required, however the priority levels should really be dictated by the shipment required by constraints.
The format of the workbook or solution can change, however we need to clearly see the resource booking on each given day, and what projects are booked on each resource. The sample file contains a concept booking system with notes.
- This is a pilot project and is the basics scope, we will augment with further requirements/ fees if proven viable. Phase 1 should fully meet the current constraints outlined to demonstrate viability. Phase 2 will augment to further constraints in more precise booking, and more data, however I would not expect the basic system to change drastically.
- We are already implementing an ERP system with resource balancing, however we believe there is opportunity to create a better short term example prior to ERP taking over.
- I can email a sample and do a web meeting of the current system to provide more context.
PROBLEM OVERVIEW:
1) New projects are added to the work file. They contain the anticipated amount of work required across a number of machine centers. Machine operations need to be in sequence, IE: finishing can't occur prior to fabrication.
1.1) Projects contain multiple truck loads of materials. Each load needs to complete all work parts prior to shipment. However, project information is not currently presented in "work order per load" instead if a project had 8 loads you could estimate that when 1/8th of the final production task was complete, that first load of 8 would be ready for shipment.
2) The project work order is constrained by start date (Issued for Fabrication), 1st shipment date, and a last shipment date (must complete by)
3) Each work centre has a daily maximum booking capacity
4) Projects should not take over 40% of production capacity within a given 2 week period. ( We will want to be be able to change this % and time period constraint). Each machine centre may have a different % max allotted that any single project could book out.
6) If done with a goal seek outcome the goal would be to ship as many trucks as soon as possible and maximize resource (work centre) utilization. Trucks will be tied to revenue recognition for the company and that can be used as a success metric ( along with the delivery constraints).
6.1) Product can be produced and then stored prior to shipment, the goal is to keep all work centres at either full or a consistent capacity, and then delivery on customer shipping dates.
7) If constraints cannot be met, IE production is overbooked and the final shipment or first shipment cannot be made in time then these items need to be identified with the next best possible solution implemented. Project priority levels can be assigned if required, however the priority levels should really be dictated by the shipment required by constraints.
The format of the workbook or solution can change, however we need to clearly see the resource booking on each given day, and what projects are booked on each resource. The sample file contains a concept booking system with notes.
- This is a pilot project and is the basics scope, we will augment with further requirements/ fees if proven viable. Phase 1 should fully meet the current constraints outlined to demonstrate viability. Phase 2 will augment to further constraints in more precise booking, and more data, however I would not expect the basic system to change drastically.
- We are already implementing an ERP system with resource balancing, however we believe there is opportunity to create a better short term example prior to ERP taking over.
- I can email a sample and do a web meeting of the current system to provide more context.
Structurlam Mass Timber Corporation
100% (2)Projects Completed
1
Freelancers worked with
1
Projects awarded
100%
Last project
1 Apr 2020
Canada
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies