provide Proposed Solution methodology (EXCEL work)

CASE STUDY COVOLO DIVING GEAR, PART 2 June 15, 2009.-It has been two weeks since their con- tentious semiannual planning meeting, and the senior staff members for Covolo Diving Gear are getting ready to start their first monthly S&OP meeting. Gina Covolo, CEO, gets the ball rolling: I know it's been a busy two weeks for all of you, and I appreciate you working extra time to get ready for this meeting. Production is already set for the next two months, so we're going to start by planning for this September through the following August. I've had Patricia from mar- keting develop a sales forecast for these 12 months, and I've also had David from manufacturing estimate manufac- turing costs and labor requirements, as well as capacity in the plant. Mary from HR was also good enough to come up with some estimates of how much it costs to liire and train new workers, as well as the cost of laying off folks. Finally, Jack from purchasing was able to get the accounting folks to estimate the cost of holding a gauge set in inventory for a month. So let's see what we've got. Mary passes out the following information to all of the attendees: MONTH September 2006 October November December January 2007 February March April May June July August SALES FORECAST 30,000 gauge sets 31,500 35,000 37,000 22,000 18,000 17,500 27,000 38,000 40,000 42,000 40,000 Manufacturing cost per gauge set: $74.50 Holding cost: $8 per gauge set per month Average labor hours required per gauge set: 0.25 hours Labor hours available per employee per month: 160 Plant capacity: 35,000 gauge sets per month Cost to hire and train a new employee: $1250 Cost to lay off an employee: $500 Beginning and ending workforce is 50 Beginning inventory is 10,000 4 Proposed Solution methodology (EXCEL work)