Problem Set #2: Aggregate Planning For this problem set assignment, please go to the link
https://bu.leganto.exlibrisgroup.com/leganto/public/01BOSU_INST/lists/29445370950001161?auth=SAML and click on the chapter titled "Aggregate Planning in a Supply Chain." Consider the information and data presented in the chapter Exercise 4 & 5 (concerning FlexMan, on Page 228-229). Although you will make use of the information presented in the exercise, you will address the questions below instead of the questions listed in the chapter. One exception - for the changing workforce information (Exercise 5) do not include the assumption of 50% worker productivity in the first two months (assume each worker is fully productive immediately after being hired). For this assignment, you will be presented with an aggregate planning system that will work for FlexMan and you will use the system to develop various plans. The system is found on Blackboard. Start by developing an understanding of the Excel spreadsheet that would be used by a planner at FlexMan to create a 12-month aggregate plan. Note that a color coding scheme is used that highlight cells that are inputs and those that are to be determined by the planner (i.e., decision variables). Keep a "master" or "template" of the system in one tab, and create other tabs for the specific plans that you develop (see below). It is assumed that all capacity buffering is accounted for in the production rates and therefore needs no explicit consideration in the planning system. Use the aggregate planning system for FlexMan to develop a series of aggregate plans. Although workforce levels can be changed, you must end the year with 6300 workers. [Note: you may need to add or subtract workers in December (last month of plan) to get back to 6300.] Although not specifically mentioned in the textbook, at no time should you allow inventory levels fall below their beginning levels (100,000 and 50,000 units). Specifically, in each case below, use the aggregate planning system to develop a feasible plan for the demand forecasts in Table 8-11; To simplify your work, you may enter all production units as multiples of 10,000 (e.g., 800,000 units; 810,000 units; 820,000 units; etc.) and, if changing workforce, you may keep workforce level as a multiple of 10 (e.g., 1000, 1010, 1020, etc.). The assessment of your work will be based on its accuracy. Plan #1: Enter production values for each product so that the workforce is maintained at the initial level of 6300 and overtime is not used. Keep inventory at or above initial levels throughout. Plan #2: Enter production values for each product so that the workforce is maintained at the initial level of 6300 and overtime is used in high demand months to minimize inventory buildup. Keep inventory at or above initial levels throughout. Plan #3: Enter production units for each product so that monthly inventory levels stay exactly at their beginning levels. Change workforce levels throughout to limit idle workers, and use overtime to limit worker increases. For the last month, adjust the workforce so that the plan ends with the initial level of 6300 workers. Plan #4: Enter production units for each product that improves on the plans above. Do NOT Use Solver or Linear Programming because you do not need to find the "true" optimum or absolute best plan - a trial-anderror approach is fine. Remember - do not enter production units that result in backlogs, maintain inventory levels each month at the initial levels or above, and end the year with 6300 workers. Plan #5: Use Solver to find the optimum solution. Remember - do not enter production units that result in backlogs, maintain inventory levels each month at the initial levels or above, and end the year with 6300 workers.