Total Cost (C30) Must Equal 208000

Use the Solver add on. Minimum

This is just the same info given more clearly because I was not given anymore information myself.

40. Each year, a shoe manufacturing company faces demands (which must be met on time) for pairs of shoes as shown in the file P03 40.xlsx. Employees work three consecutive quarters and then receive one quarter off. For example, a worker might work during quarters 3 and 4 of one year and quarter 1 of the next year. During a quarter in which an employee works, he or she can produce up to 600 pairs of shoes. Each worker is paid $6000 per quarter. At the end of each quarter, a holding cost of $10 per pair of shoes is incurred. a. Determine how to minimize the cost per year (labor plus holding) of meeting the demands for shoes. To simplify the model, assume that at the end of each year, the ending inventory is 0. (You can assume that a given worker gets the same quarter off during each year.) b. Suppose the company can pay a flat fee for a training program that increases the productivity of all of its workers. Use Solver Table to see how much the company would be willing to pay for a training program that increases worker productivity from 600 pairs of shoes per quarter to P pairs of shoes per quarter, where P varies from 625 to 800 in increments of 25. A General Calibri BIU 11 - A - OA == E Wrap Text Merge & Center Paste $ - % Clipboard Number Alignment Font A1 X for Sheet1!A1 D F G 5 #2. Problem #40 (40 points) 6a. Manufacturing shoes (30 points) Caution: Do not alter the structure cells, or renaming sheet na slightly to accommodate pe Not following these instruc 8 Wage per quarter Unit holding cost Production per worker Wage per worker for three quarters All cells in all sheets must be number format and decimal appropriate fashion so that Production/worker schedule Quarter Workers starting this quarter Number of workers in the roll Actual Production Hints: For changing cells use the three quarter sequence, a No of workers in the roll in the present quarter plus Production capacity is bas 4 Add INT constraint for num Production capacity On hand after production 5000 30008000 1000 Demand Ending inventory Sheeta Sheet2 3 4 5 6 WE R E Tab Calibri General -11-AA EE O A . Wrap Text Merge & Center - BIU-B EE $ - % ) Clipboard Font Alignment Number Sheet1!A1 CDF F G H 20 Production capacity I J K in the present quarter J3 Production capacity is 4. Add INT constraints On hand after production 6000 3000 8000 1000 Demand Ending inventory Cost summary for four quarters Labor cost Holding cost Total cost 30 Answer key: C30 - 208000 31 33 b. Run SolverTable and discuss how much the company would be willing to pay for the training program. Give your answer in the box below. Use Justify and make sure the text is properly justified to fit into the box (10 points) 34 Sheet1 Sheet2 o W E R Hints: 1. For changing cells use the number of workers starting each quarter for a three-quarter sequence, and the actual number of shoes produced each quarter. 2. No. of workers in the roll for each quarter will be number workers starting in the present quarter plus workers started in the two previous quarters 3. Production capacity is based on the number of workers in the roll each quarter. 14. Add INT constraint for number of workers starting each quarter. Each year, a shoe manufacturing company face demands (which must be met on time ) for pairs of shoes. Quarter 1: 6000, Quarter 2: 3000 Quarter 3: 8000, Quarter 4: 1000. Employees work three consecutive quarters and then receive one quarter off. For example, a worker might during quarters 3 and 4 of one year and quarter 1 of the next year. During a quarter in which an employee works, he or she can produce up to 600 pairs of shoes. Each worker is paid $6000 per quarter. At the end of each quarter, a holding cost of $10 per pair of shoes is incurred. a. Determine how to minimize the cost per year (laboring plus holding) of meeting the demands for shoes. To simplify the model, assume that at the end of each year, the ending inventory is 0. (You can assume that a given worker gets the same quarter off during each year.) Hints Given: 1. For changing cells use the number of workers starting each quarter for a three-quarter sequence, and the actual number of shoes produced each quarter. 2. No. of workers in the roll for each quarter will be number workers starting in the present quarter plus workers started in the two precious quarters. 3. Production capacity is based on the number of workers in the roll each quarter. 4. Add INT constraint for number of workers starting each quarter. Answer Key: Total Cost (C30) = 208000 40. Each year, a shoe manufacturing company faces demands (which must be met on time) for pairs of shoes as shown in the file P03 40.xlsx. Employees work three consecutive quarters and then receive one quarter off. For example, a worker might work during quarters 3 and 4 of one year and quarter 1 of the next year. During a quarter in which an employee works, he or she can produce up to 600 pairs of shoes. Each worker is paid $6000 per quarter. At the end of each quarter, a holding cost of $10 per pair of shoes is incurred. a. Determine how to minimize the cost per year (labor plus holding) of meeting the demands for shoes. To simplify the model, assume that at the end of each year, the ending inventory is 0. (You can assume that a given worker gets the same quarter off during each year.) b. Suppose the company can pay a flat fee for a training program that increases the productivity of all of its workers. Use Solver Table to see how much the company would be willing to pay for a training program that increases worker productivity from 600 pairs of shoes per quarter to P pairs of shoes per quarter, where P varies from 625 to 800 in increments of 25. A General Calibri BIU 11 - A - OA == E Wrap Text Merge & Center Paste $ - % Clipboard Number Alignment Font A1 X for Sheet1!A1 D F G 5 #2. Problem #40 (40 points) 6a. Manufacturing shoes (30 points) Caution: Do not alter the structure cells, or renaming sheet na slightly to accommodate pe Not following these instruc 8 Wage per quarter Unit holding cost Production per worker Wage per worker for three quarters All cells in all sheets must be number format and decimal appropriate fashion so that Production/worker schedule Quarter Workers starting this quarter Number of workers in the roll Actual Production Hints: For changing cells use the three quarter sequence, a No of workers in the roll in the present quarter plus Production capacity is bas 4 Add INT constraint for num Production capacity On hand after production 5000 30008000 1000 Demand Ending inventory Sheeta Sheet2 3 4 5 6 WE R E Tab Calibri General -11-AA EE O A . Wrap Text Merge & Center - BIU-B EE $ - % ) Clipboard Font Alignment Number Sheet1!A1 CDF F G H 20 Production capacity I J K in the present quarter J3 Production capacity is 4. Add INT constraints On hand after production 6000 3000 8000 1000 Demand Ending inventory Cost summary for four quarters Labor cost Holding cost Total cost 30 Answer key: C30 - 208000 31 33 b. Run SolverTable and discuss how much the company would be willing to pay for the training program. Give your answer in the box below. Use Justify and make sure the text is properly justified to fit into the box (10 points) 34 Sheet1 Sheet2 o W E R Hints: 1. For changing cells use the number of workers starting each quarter for a three-quarter sequence, and the actual number of shoes produced each quarter. 2. No. of workers in the roll for each quarter will be number workers starting in the present quarter plus workers started in the two previous quarters 3. Production capacity is based on the number of workers in the roll each quarter. 14. Add INT constraint for number of workers starting each quarter. Each year, a shoe manufacturing company face demands (which must be met on time ) for pairs of shoes. Quarter 1: 6000, Quarter 2: 3000 Quarter 3: 8000, Quarter 4: 1000. Employees work three consecutive quarters and then receive one quarter off. For example, a worker might during quarters 3 and 4 of one year and quarter 1 of the next year. During a quarter in which an employee works, he or she can produce up to 600 pairs of shoes. Each worker is paid $6000 per quarter. At the end of each quarter, a holding cost of $10 per pair of shoes is incurred. a. Determine how to minimize the cost per year (laboring plus holding) of meeting the demands for shoes. To simplify the model, assume that at the end of each year, the ending inventory is 0. (You can assume that a given worker gets the same quarter off during each year.) Hints Given: 1. For changing cells use the number of workers starting each quarter for a three-quarter sequence, and the actual number of shoes produced each quarter. 2. No. of workers in the roll for each quarter will be number workers starting in the present quarter plus workers started in the two precious quarters. 3. Production capacity is based on the number of workers in the roll each quarter. 4. Add INT constraint for number of workers starting each quarter. Answer Key: Total Cost (C30) = 208000