Need help doing this in an excel spreadsheet. Sales and Operations Planning Prob
ID: 453917 • Letter: N
Question
Need help doing this in an excel spreadsheet.
Sales and Operations Planning Problem MMS, Inc, a manufacturer of medical supplies, uses aggregate planning to set labor and inventory levels for the year. While a variety of items are produced, a standard kit composed of basic supplies is used for planning purposes. Demand varies with seasonal illnesses and the quarterly ordering policies of hospitals. The average worker at MMS can produce 1000 kits a month at a cost of dollar 9 per kit during regular production hours and dollar 10 per kit during overtime production. Completed kits can also be purchased from outside suppliers at dollar 12 each. Inventory carrying costs are dollar 2 per kit per month. Overtime is limited to regular production, but subcontracting is unlimited. Due to high quality standards and extensive training, hiring and firing costs are dollar 1500 per worker. MMS currently employs 25 workers. Maximum sustainable OT is 10 percentage of the regular work hours. Given the demand forecast below, develop a seven-month aggregate production plan for MMS using a "Stable Workforce" strategy utilizing the current workforce, OT, the subcontractor and inventory as needed to fulfill the demand. What is the total cost to produce the 262k kits.Explanation / Answer
No of workers = 25 Each worker can produce = 1000 kits per month Month Demand Regular Prodn OT Sub Inv No of workers Hire Fire April 55000 25000 2500 27500 0 25 May 22000 25000 0 22 3 June 15000 22000 0 15 7 July 46000 15000 1500 500 45 30 August 74000 45000 74 29 September 15000 15000 15 59 October 35000 15000 20 Total 262000 Costing of the above aggregate plan Month reg worker's cost@9 OT cost@10 sub cost@12 inv cost@2 hiring /firing cost@1500/ worker Total cost April 25000*9 = 225000 2500*10=25000 27500*12= 330000 580000 May 25000*9 = 225000 3*1500=4500 229500 June 22000*9 = 198000 7*1500= 10500 208500 July 15000*9=135000 1500*10= 15000 500*2=1000 30*1500=45000 196000 August 45000*9= 405000 29*1500=43500 840000 September 15000*9=135000 59*1500=88500 223500 October 15000*9=135000 20*1500= 30000 165000 Total cost 2442500
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.