During the next four months Capps Shoes Company must meet the following demand for pair of shoes: 2000 shoes in month 1, 3000 shoes in month 2, 4000 shoes in month 3, and 5000 shoes in month 4. At the beginning of month 1, 500 shoes are on hand. This company has 100 workers. A worker is paid $11.5 per hour in month 1, $11 per hour in month 2, $10.5 per hour in month 3, and $10 per hour in month 4. Each worker can work up to 160 hours in month 1, 150 hours in month 2, 140 hours in month 3, and 160 hours in month 4 before he or she receives overtime. Each worker can work up to 20 hours overtime per month and is paid $13 per hour for overtime labor. It takes four hours of labor and $15 of raw material to produce a pair of shoes. At the end of each month, a holding cost of $3 per pair shoes left in inventory incurred. During all these four months, the option of buying form a subcontractor is also available. Buying a pair of shoes from the subcontractor costs $80 and its current capacity is 200 shoes per month. Currently, production in a given month can be use to meet that same month’s demand and the following months’ demands. The company wants to determine its optimal production schedule.
Create a spreadsheet model for this problem using the transportation tableau. Name rows and columns properly, and solve the problem using Excel Solver. Provide screen shots of the formula view and normal view of your spreadsheet and the Excel Solver window at the end of this word document.
