Use Excel's Solver to determine which of the below 3 strategies yield the minimal total cost. (see the attached image)

 

Hints: 

Part 1: use level production of 60 tankloads every month and supplement with overtime as needed with a maximum of 10 tankloads every month. Subcontracting does not play a role here and can be either removed from the spreadsheet computations or a constraint can be added to set subcontracting production at zero if subcontracting has been included as a decision variable. Decision variables: Regular production and overtime. Constraints: restrict regular production to equate to 60 tankloads per month, and overtime to not exceed 10 truckloads per month. Also, add another constraint to ensure that the ending inventory is greater than or equal to zero.

 

Part 2: use level production of 60 tankloads every month and supplement with overtime (with a maximum of 10 tankloads every month) and subcontracting. There is no limit or a constraint on how many units to be produced in subcontracting. Decision variables: regular production, overtime, and subcontracting. Constraints: equate regular production to equate to 60 tankloads, overtime not to exceed 10 tankloads, and there is no constraint pertaining to subcontracting. Also, add another constraint to ensure that the ending inventory is greater than or equal to zero.

 

Part 3: identical to part 1, however, the maximum tankloads in overtime is increased from 10 to 15.

 

Can you show me how to do it on Excel or upload the excel spreadsheet itself? Of course I will not submit the spreadsheet. I just have a hard time with the solver function. 

 

Transcribed Image Text:

CASE EIGHT GLASSES A DAY (EGAD) The EGAD Bottling Company has decided to introduce a new line of premium bottled water that will include several "designer" flavors. Marketing manager Georgianna Mercer is predicting an upturn in demand based on the new offerings and the increased public awareness of the health benefits of drinking more water. She has prepared aggregate forecasts for the next six months, as shown in the following table (quantities are in tankloads). Month May Jun Jul Aug Sep Oct Total Forecast 50 60 70 90 80 70 420 Production manager Mark Mercer (no relation to Georgianna) has developed the following information. (Costs are in thousands of dollars.) Regular production cost Regular production capacity Overtime production cost Subcontracting cost Holding cost Backordering cost Beginning inventory $1 per tankload 60 tankloads $1.6 per tankload $1.8 per tankload $2 per tankload per month Backlogs are not allowed 0 tankloads Among the strategies being considered are the following: Level production supplemented by up to 10 tankloads a month from overtime. A combination of overtime, inventory, and subcontracting. Regular production should be the same each month. Using overtime for up to 15 tankloads a month, along with inventory to handle variations. Regular production should be the same each month. CASE EIGHT GLASSES A DAY (EGAD) The EGAD Bottling Company has decided to introduce a new line of premium bottled water that will include several "designer" flavors. Marketing manager Georgianna Mercer is predicting an upturn in demand based on the new offerings and the increased public awareness of the health benefits of drinking more water. She has prepared aggregate forecasts for the next six months, as shown in the following table (quantities are in tankloads). Month May Jun Jul Aug Sep Oct Total Forecast 50 60 70 90 80 70 420 Production manager Mark Mercer (no relation to Georgianna) has developed the following information. (Costs are in thousands of dollars.) Regular production cost Regular production capacity Overtime production cost Subcontracting cost Holding cost Backordering cost Beginning inventory $1 per tankload 60 tankloads $1.6 per tankload $1.8 per tankload $2 per tankload per month Backlogs are not allowed 0 tankloads Among the strategies being considered are the following: Level production supplemented by up to 10 tankloads a month from overtime. A combination of overtime, inventory, and subcontracting. Regular production should be the same each month. Using overtime for up to 15 tankloads a month, along with inventory to handle variations. Regular production should be the same each month.

Answer rating: 100% (QA)

Im here to help but unfortunately Im unable to directly upload or manipulate Excel spreadsheets in this textbased interface However I can guide you th... View the full answer