Sir, It seems like you didn't get credit for the answers you posted to my questions. I needed that in excel file but you just posted in .jpg format.

Sir, It seems like you didn't get credit for the answers you posted to my questions. I needed that in excel file but you just posted in .jpg format. I will credit $10 to your account when you will reply to this post (The price of my post was $8 but $2 for your inconvenience) . Also, Is it okay if you could upload an excel file for it, please? Thank you! Thanks for all you help!

The question in case you forgot which one did you answers!

Question:

Gang Aft Agley, a manufacturing company, faces the aggregate planning problem shown in the table below. Cost of regular production is $5 per unit, the cost of producing the same unit on overtime is $7.50, the cost of subcontracting is $9 per unit, and the cost of carrying a unit in inventory from one month to the next is $2.

The labor contract at the plant prohibits overtime output to exceed 300 units in any five month window (that is the entire time horizon being considered); likewise, subcontracting output also can't exceed 300 units in any five month window. The plant capacity is 600 units per month (during regular time) produced using two shifts, regardless of the number of days in a month. By policy, management wants to avoid stockouts.

Formulate the aggregate plan considering regular time costs, overtime costs, subcontracted production costs, inventory costs and the necessary constraints using linear programming and solve it using Excel Solver for obtaining the optimum minimum cost for the 5-month horizon

*table in attached file*

Your case study report must contain

A. Objective function

B. Constraints

C. Excel Solver output ( Attach Excel output)

Your answer:

The decision variables in this problem are the monthly units produced through each of the three sources of production:

· Regular time (Rt)

· Overtime (Ot)

· Subcontracting (St)

There are five periods; therefore, a total of 15 decision variables are involved. In addition, there is the cycle inventory that may or may not result due to any particular production quantity for a given period. This inventory has a cost and will, therefore, be included in the total cost expression. Our objective function for the problem becomes:

Minimize C = ∑n (Rj crj + Ojcoj + Sjcsj) + ∑Ijcij

Subject to: ∑Rn <= 600, ............, 600

∑On <= 300, ............, 30 0

∑Sn <= 300, ............., 300

∑Rn,On, Sn >= 500, 750, ............ 300

The entire problem is modeled in Excel and the spreadsheet formulation, as well as the Solver dialog box, is shown in the attached figures.

The optimal solution to the problem returns a value of $18,875. The quantities produced through each of the three sources are also displayed on the spreadsheet.