VL Shipyard Hints Your Background section should be no more than one page. It should have short paragraphs, single spaced, not indented. Be sure to put a space between paragraphs. At the bottom of your Background, you should set up a VL Shipyard Resources Constraints Table. This Table will have six columns and five rows. There will be a row for each type of constraint (Steel, Anti-Corrosion, etc.) and a column for each type of contract (Repair, Conversion, etc.). Of course, the last column to the right should be Capacity. The detail for the Table can be found in Exhibit 1 and Exhibit 2 on Page 6 of the Case. You will be using Excel Solver to find the optimal total contribution (max profit) LP solution. Your Issue should sound like, How many contracts and of what type should VL accept to get the optimal total contribution? You will have six (6) Alternatives listed: Accept only Repair Contracts, Accept only Conversion Contracts, Accept only Newbuilding Contracts, Accept only Jacket Contracts, Accept a mix of different types of Contracts. Your sixth Alternative is to apply Sensitivity Analysis and alter Contract Capacities (demand) and Constraint Capacities. First, set up the Excel Solver spreadsheet model (with formulas), see below. Then, fill in the Constraint values dsignated by a ? below. Use your VL Shipyard Resources Constraints Table for the values. You now have done Attachment 1: VL Shipyard LP Spreadsheet Model. Then, run Excel Solver LP and create Attachment 2: VL Shipyard LP Optimized Spreadsheet Model Report. In order to analyze Sensitivity, talk to the Marketing VP and add one more Jacket Contract (do 3 Contracts instead of 2). Run Excel Solver and you will find that the results are excellent.Try adding 2 more Jacket Contracts and see what happens. On the other hand, add one Repair Contract, run Excel Solver, and you will find that the results are terrible. Make sure that you set your model back to its original numbers before you try any of these Sensitivity experiments. Now lets see what happens when you ask the Purchasing Manager to find another Steel vendor to add extra tons of Steel Capacity. Again, the results are excellent when you run Excel Solver. The question is how many more tons of Steel should you add? Hint try 336 Tons and then try 337 Tons. See what happens when you run Excel Solver. The Case tells us that we will be unable to add additional Anti-Corrosion or Dock Days, and the labor market is not good. So, just add Tons of Steel. Remember that you need $30M to cover all fixed costs and loan payments.

\begin{tabular}{|c|c|c|c|c|c|c|c|} \hline & A & B & C & D & E & F & G \\ \hline 1 & & & & & & & \\ \hline 2 & & Repair & Conversion & Newbuilding & Jacket & Total (Formula) & Capacity Constraint \\ \hline 3 & Contracts & 1 & 1 & 1 & 1 & & \\ \hline 4 & & & & & & & \\ \hline 5 & Contribution & 520171 & 5014710 & 1600150 & 3098844 & = =SUMPRODUCT(B3:E3,B5:E5) & \\ \hline 6 & & & & & & & \\ \hline 7 & Demand & 40 & 4 & 2 & 2 & & \\ \hline 8 & Steel & ? & ? & ? & ? & = =SUMPRODUCT ($B$3:$E$3,B8:E8) & 5500 \\ \hline 9 & Anti-Corrosion & ? & ? & ? & ? & = =SUMPRODUCT ($B$3:$E$3,B9:E9) & 500000 \\ \hline 10 & Dock Days & ? & ? & ? & ? & = =SUMPRODUCT ($B$3:$E$3,B10:E10) & 700 \\ \hline 11 & Man-Hours & ? & ? & ? & ? & = =SUMPRODUCT ($B$3:$E$3,B11:E11) & 4000000 \\ \hline 12 & & & & & & & \\ \hline \end{tabular}