I need help on a mathematical modeling assignment.  Must be able to define variables, parameters and constraints of the problem within Excel.  The file is attached.

 

SLOP Becomes Hearty GLOP
Ann is a plant manager at Private Foods. Her plant makes Hearty Glop, a microwaveable
dinner that is particularly popular in winter. Ann has to minimize her total annual cost while
meeting demand each month. Monthly demand for Glop for the next year is forecasted as
shown in the table below and in the GlopData.xlsx . The plant can make at most 2,500 units of Glop in any month. The main raw material for making Glop is an ingredient called Slop. Each unit of Glop
requires 8 units of Slop. Private Foods purchases Slop from a number of different
vendors. The price of Slop is fairly volatile. The accompanying table shows Ann’s
forecasts of the monthly prices per unit for Slop for the next year.
Each unit of Glop also requires 2 units of labor. Unit labor costs for the next year are forecasted as shown in the table. The plant can change the labor employed from month
to month without additional costs.
Up to 2,500 units of unsold Glop can be stored in the plant at a monthly cost of $20 per unit. Unused Slop cannot be stored because it congeals into an inedible form.
The plant has 50 units of Glop available at the beginning of the first month. Note that the sequence of events in each period is: production, then sales, then storage. Table 1: Glop/Slop Demand and Prices
Month Glop
Demand Slop Price Labor Price 1 1100 $60 $15 2 1200 $60 $16 3 1300 $65 $17 4 1000 $70 $18 5 800 $50 $19 6 500 $40 $19 7 200 $20 $19 8 200 $10 $18 9 400 $25 $17 10 700 $35 $17 11 900 $45 $16 12 1000 $50 $16 Prepare a linear decision model to help Ann develop her production plan.
Note: In answering questions a. – c., use the guidelines developed in class handouts. In
particular, the reader should be able to write out all formulations using your answers on
these pages only (without referring to the Excel exhibit). Functions such as integer, maximum, minimum, if/then, etc. are not linear functions. a. Specify the decision variables. b. Specify the objective. This must be based on the inputs specified in the
problem (the parameters), the decision variables specified in (a), and/or
intermediate variables/values that you choose to specify. c. Specify the constraints. These must be based on the input numbers specified
in the problem (the parameters), the decision variables specified in (a), and/or
intermediate variables/values that you choose to specify. d. Include a picture/print of your spreadsheet formulation as Exhibit 1. Please
note that in order to receive credit, your spreadsheet must be sufficiently
annotated so that it is readable. Fit it to one page; if necessary, use landscape
orientation to print. What is the optimal production schedule? e. Ann would like to investigate the impact of the monthly unit storage cost on
the optimal monthly production schedule, total annual cost, annual storage cost,
annual Slop cost, and annual labor cost. Use SolverTable to exhibit this impact by
varying the monthly storage cost from $0 per unit to $140 per unit in steps of $20.
Include well-formatted, well-labeled table asExhibit 2. Fit to one page; use
landscape orientation if needed. How would you describe the impact of storage
costs on the production schedule?

• 1492746447-Homework Assignment 3.docx

ComputerScienceExpert

(11)

Status NEW Posted 21 Apr 2017 03:04 AM My Price 8.00

-----------

Attachments

file 1492746449-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer