Solver Simplex LP setup with Specific example

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.

Quarter 1 2 3 4
Demand 40 60 75 25
Regular time unit cost $430 $430 $430 $430
Overtime unit cost $480 $480 $480 $480
Unit holding cost $20 $20 $20 $20
         
Initial inventory 12      
         
Upper bound on Regular Production 39 39 39 39

 

By: Patrick

Leave a Reply

Your email address will not be published. Required fields are marked *