Here is the video for more details.
https://scranton.learntoday.info/AngelUploads/Content/FAL11-Session2- OM503-
91256/_assoc/site/MM/WK6_Aggregate_Planning_Video/Aggregate%20Planning.html
For this assignment, please complete problem number 8, page 504 of your textbook. Use "Solver" to set up and answer all three parts. Save all parts in three separate worksheets (e.g., part a, part b and part c) in a single spreadsheet file (make sure that your Excel file name includes your last name).
Once you set the problem in a spreadsheet, parts b and c should be quite easy to complete as the only variations will entail different decision variables and constraints.
Please view the Camtasia video file "Aggregate Planning" as a guide (located on the Multimedia page).
Hint: This assignment is pretty much based on the video that I have prepared for you on aggregate planning. All parts need to be completed using Solver. All three parts a, b, and c are almost identical in terms of set up. The only differences lie in identifying the correct
decision variables and constraints from Solver. Let's look at part "a". The problem states to use level production and supplement with overtime as needed. This means every month we will be producing 5000 units and if short to use overtime. Note that subcontracting does not play a role here and there is no constraint on the number of units produced in overtime. So, once you set the problem up in Excel and invoke Solver, what would your decision variables be? They will be the rows for "Regular production" and "overtime production". What are your constraints? Make sure that one of your constraints equates the regular production to equal 5000 units (remember we are using a level production). Anything else? Not really just add the non-negativity constraint and check
the box "assume linear model". Don't also forget to ensure that your "ending inventory" row will be greater than or equal to zero. You are done with part "a". Approach parts "b" and "c" in a similar fashion. For part "b" you are told to use a combination of overtime, inventory and subcontracting. So, your decision variables will be the regular production, overtime production, and subcontracting. Regarding the constraints you should have three of them right? One for the regular production (<=5000), one for the overtime production (<=500) and another one for the subcontracting (<=500). Also, don't forget the non- negativity constraint and ensuring that ending inventory must be greater than or equal to zero. Finally for part "c", you are told to use overtime, regular production and inventory. So, what are your decision variables in this case? Regular production and overtime production. What are your constraints? Regular production <= 5000 units, and overtime production <=750 units. Note that once you set the problem up and solve it, you may see some sort of an error message stating that the linearity assumption is not satisfied. Don't be alarmed by this message since we know for a fact that the model is indeed linear. When certain Excel functions such as "SUMPRODUCT()" are used in building the model, sometimes Excel "guesses" that your model may be non-linear. That is the rationale behind it. You may also see certain values in Excel such as 6E-5. This is literally zero as this value is actually 6 divided by 10 to the power 5 or 0.00006. Quite often, if you click on Solve again from the Solver window, the error message about linearity disappears and all values in scientific notations get rounded to zero.
Attachment:- OM2 (1).png
Attachment:- OM1 (1).png