|2. Sailco Corporation must determine how many sailboats to produce during each of the next four quarters. The demand during each of the next four quarters is as shown on the Sailco sheet. Sailco must meet demands on time. At the beginning of the first quarter, Sailco has an inventory of 12 sailboats. At the beginning of each quarter, Sailco must decide how many sailboats to produce during that quarter. For simplicity, we assume that sailboats manufactured during a quarter can be used to meet demand for that quarter. During each quarter, Sailco can produce up to 39 sailboats with regular-time labor at a total cost of $430 per sailboat.|
|By having employees work overtime during a quarter, Sailco can produce additional sailboats with overtime labor at a total cost of $480 per sailboat. At the end of each quarter (after production has occurred and the current quarter’s demand has been satisfied), a holding cost of $20 per sailboat is incurred. Finally, if any production (regular or overtime) occurs in a given quarter, a fixed cost of $2,900 is incurred.|
|Determine a production schedule to minimize the sum of production and inventory holding costs during the next four quarters. Indicate your total cost in cell C12 of the Answer Summary Sheet. In cells C13 and C14 indicate the total number of sailboats produced (over all 4 quarters) by regular and overtime production respectively. Finally, in cell C13, report how many of the 4 quarters had no production at all.|
Using the following excel table, how would you set this problem up in the SOLVER using the SIMPLEX method.
|Regular time unit cost||$430||$430||$430||$430|
|Overtime unit cost||$480||$480||$480||$480|
|Unit holding cost||$20||$20||$20||$20|
|Upper bound on Regular Production||39||39||39||39|