Management science -- Mathematical models, Decision support systems, Management information systems -- Applications to manufacturing environments, Mathematical optimization
Continental Mills’ packing plant that produces Krusteaz products consists of several lines. Each line is able to package a variety of flour based mixes such as pancake batter mix, bread mixes and various cake mixes, in several sizes and packing formats, ranging from 12 ounces and up to 50 lbs, and filled in both paper bags, plastic bags or cardboard boxes, or combinations of the three.
The factory has seven packing lines, but we are focusing on the four of them that are used for the Krusteaz pancake mix. The product allocation is allocated to the four lines based on several factors such as dissimilar speeds, labor requirements, down times, and conveyor lengths.
Our main goal for this project is to maximize the Weekly Gross Profit on the four Krusteaz production lines by constructing a spreadsheet model and using Excel Solver Tools. We hope that our model might be helpful for production managers as a decision support tool, especially as it incorporates uncertainty about cost, prices and demand.
This latter point is something that occurred to us as we progressed. The actual line allocation process (optimally allocating eight different package sizes between four different lines) turned out to be the easy part, and we soon realized how this type of analysis could be used by management not just as a production planning tool, but more importantly as a potential tool for finding a better balance between the supply and demand for these products. The spreadsheet can be used to study and evaluate various price and quantity combinations to assess which ones would best equate a proper balance or compromise between production efficiencies and the actual demand patterns for the products. Having this type of tool should allow the production and marketing people to communicate better and make more optimal allocation decisions using both product quantities and prices as decision variables.
As mentioned, Excel was used as the primary platform for this project, and we added and considered both Frontline’s Risk Solver and Oracle’s Crystal Ball add-ins for running optimizations and simulations. Both were found to be valuable and relatively user friendly additions to the spreadsheet package.
Pornsatit, Chakaphan; Udbye, Andreas; and Yan, Chenxi, "Using Excel Based Optimization and Simulation as Powerful Management Decision Tools in a Manufacturing Environment" (2010). Engineering and Technology Management Student Projects. 711.