+1 123 456 7890 instantessays65@gmail.com

(answered) – 1) CHEMSTAT ChemStat uses an expensive and toxic chemical in its


(answered) – 1) CHEMSTAT ChemStat uses an expensive and toxic chemical in itsDescriptionSolution downloadThe Question1) CHEMSTAT ChemStat uses an expensive and toxic chemical in its processes. Table 1 shows the estimated requirements for this chemical in the upcoming six weeks. Currently ChemStat projects it will have 150 pounds in inventory at the beginning of week 1. Table 1. Estimated pounds of chemical required by week 123456 665 350420285650425 The supplier delivers this chemical in batches of 120 pounds for which the cost is $2,250 per batch and charges $850 for delivery of an order independent of the number of batches in the order. The chemical is expensive to store and ChemStat estimates a storage cost of $1.50 per pound per week (based on average inventory). Because there can be unexpected changes in production schedules, ChemStat believes it needs to have a minimum ending inventory each week of 50 pounds. Create a Solver-based spreadsheet model to determine how many batches to order each week in order to minimize total cost and solve the model. Comments and Hints: 1.What is the maximum number of batches that would need to be purchased? 2.Create cells holding the following parameter values: a.Batch size b.Price per batch c.Delivery charge per order d. Carrying cost (in $ per pound per week) e.Initial inventory f.Minimum inventory g.Maximum batches (this will be a formula) 3.You will need 12 changing cells (6 integer and 6 binary) and 6 linking constraints. 4.Create a table with a column for each week and rows for each of the following: a.Beginning inventory b.Order placed (Y or N) c.Batches ordered d.Pounds ordered e.Pounds required f.Ending inventory g.Average inventory 5. Create cells for purchase cost, delivery cost, inventory carrying cost and total cost. 6.Be sure to set integer optimality = 0 and see that “Ignore Integer Constraints” is unchecked. Anticipate a minute or two of computation time. 2) ERVIN MANUFACTURING COMPANY The Ervin Manufacturing Company is planning for the introduction of a new product line to be produced in new facilities dedicated to that product line. They plan to serve a Midwest/West region with distributions warehouses in Kansas City, Minneapolis, Dallas, San Diego, Denver and Seattle. They are considering plant locations in Cleveland, Los Angeles, Memphis, Oklahoma City and Kenosha. Product pricing has yet to be resolved so planning is to be based on meeting annual demand estimates at minimum total cost. Table 1 shows estimated unit shipping costs from potential plant locations to distribution warehouses. Table 1. Unit Shipping Costs Kansas CityMinneapolisDallasSan DiegoDenverSeattle Cleveland302540804580 Los Angeles55655053535 Memphis203020603575 Okla. City1530104525 65 Kenosha202035703570 Table 2 shows estimated annual demand at the distribution warehouses. Table 2. Estimated annual demand Kansas CityMinneapolisDallasSan DiegoDenverSeattle 9001000155016001450 1350 Detailed financial calculations including activity-based costing, fixed-period costs, time value of money, depreciation and tax effects over a ten-year planning horizon have produced estimates for unit product costs and annualized capital costs (required in order to construct and operate plant). Those estimates along with annual capacities are shown by potential plant location in Table 3. Table 3. Unit production costs, annualized capital costs and annual capacities by location ClevelandLos AngelesMemphisOkla. CityKenosha Unit Production Cost250275245250240 Annualized Capital Cost (in $1000)290375325350325 Annual Capacity (in units)25003000220024002100 Create a Solver-based spreadsheet model to determine the which potential plant locations should be selected for plant construction and operation, the annual quantities to be produced at each plant and the pattern of distribution from plants to warehouses in order to cover estimated annual demand at the minimum total annual cost and solve the model. After solvin