Question

Notes: Requested info from Excel file must be copied/pasted (as picture) to Word File. Neatness matters, the sloppy presentation will be penalized even if your answers are correct.

           

Snow White and the Seven Dwarfs

Snow White Corporation makes is well known for making children’s toy products. Presently the firm is planning to develop an optimal production schedule to manufacture seven of their popular products - Grumpy, Dopey, Doc, Happy, Bashful, Sneezy, and Sleepy. Relevant product information is provided below.

Profit realized for each product – in $ per unit produced and sold:

Grumpy = $12.00, Dopey = $13.20, Doc = 12.00, Happy = 10.40, Bashful = 11.30, Sneezy $14.20 and Sleepy $9.00

Material Requirements:

To make the products, you need two important materials – A and B. The table below shows the material requirements in pounds per unit. Their availability of both the materials is limited – as shown in the table.

All figures are in Pounds per unit except for the last column which indicates the maximum availability

	Grumpy	Dopey	Doc	Happy	Bashful	Sneezy	Sleepy	Maximum Available
Material A	3.50	4.60	7.20	0.00	0.00	1.80	4.10	15,200
Material B	4.20	5.00	0.00	4.10	2.20	5.00	3.20	11,700

Labor Requirements:

The production process involves the products going thru four different departments, named K, L, M, and N. The table below lists the labor time required for each product, along with the maximum available time. All the figures are in minutes per unit, except for the maximum available time, which is listed in hours.

All figures are in minutes per product except for the last column

	Grumpy	Dopey	Doc	Happy	Bashful	Sneezy	Sleepy	Maximum Available (Hours)
Department K	36	42	19	29	38	44	19	1,200
Department L	19	22	11	23	26	31	25	1,000
Department M	57	69	34	70	75	52	59	3400
Department N	10	12	9	13	14	19	15	730

Other Requirements:

Marketing has provided you with additional restrictions as follows:

• You should not make more than 800 units of Doc
• You should make a minimum of 200 units of Happy
• You should make a minimum of 100 units of Sneezy
• You should make a minimum of 700 units of Sleepy

Quiz Questions

1. ) Formulate the problem as a Linear Program. You need to determine the optimal production quantity of the products that will maximize the profit while satisfying all the constraints – material, labor, and others. Write the mathematical form of the model below.  

2. ) Create an Excel Model. You can do that by modifying the Golf Bag Problem discussed in the class.

Solve it using Excel Solver. Use the copy as a picture/paste feature of Office Suite to display the screenshot of the optimal solution below. Make sure it the optimal screenshot - otherwise you will be penalized. Format it neatly to fit in the page for clear readability.

3. )Answer the following questions:

1. Optimal Profit is: $
2. Optimal Production Mix in the quantity of the seven products to be manufactured are:

Grumpy =                          Dopey =                               Doc =

Happy =                             Bashful =                              Sneezy =

Sleepy =

4. ) Of the two material and four labor constraints, list those that are binding constraints.

For non-binding constraints, indicate how much of the resource is unused.

Answer 1

If you find my answer useful please put thumbs up. Thank you.