I already did a little. But i think some thing is wrong.

Attachment 1
Attachment 2
Attachment 3

44-317 MIS – Spring 2016Supplemental Instructions for Excel Solver Case 8Duchess Cruise Ship Assignment Problem – pp 183-197Read the opening case information, pp 183-184. Then follow the supplemental directions below for thecorresponding pages in the Casebook, paying special attention to the business logic explained with thevarious strategies. Disregard any directions in the Casebook to print or give a presentation. Assignment 1 and 1A1. Download the Duchess Cruise Ship template file from Doc Sharing (in the Tutorial D Solver area)on the course website. Open the Word file and copy the entire document. Paste it in cell A1 in anew Excel worksheet. Save the file as lastnameCruise (i.e. SchieberCruise.xlsx). Because you haveused this template, you will not locate the skeleton file in “data files” as stated in 1A on the topof p 185 nor complete the bullets for the Constants Section on pp 185-186. You should readthrough these bullets to help you understand the spreadsheet you are creating.2. Note column A is empty. Resize columns B through Q so you can see all items in those columns.Rename the sheet tab name Sheet 1 as Original Cruises.3. In the Constants section fill in the calculation for the Sailing Cost per Passenger Mile. This is theSailing Cost per Nautical Mile divided by the Passenger Capacity.Calculations and Results – see bullets on pp 186-1871. The first six bullets describe the different parts of the calculations section. The first formula youneed to complete is the seventh bullet (last bullet on page 186). Complete the bullets as directedin the casebook.2. Format cells L22:L28 and N22:N28 as percentage values with no decimal places.3. Format cells O22:P28 and cell Q28 as currency with no decimal places.Income Statement – p 1881. First Bullet: use the SUMPRODUCT function to complete this calculation. You will type=SUMPRODUCT(D22:D27,C13:C18) for this cell. (The function takes the values from each arraymultiplies them together then adds each entry together to come up with a total value. ExD22*C13+D23*C14…etc).2. Second Bullet: complete using the SUMPRODUCT function.3. Complete the rest of the bullets as stated.4. Format cells C31:C35 as currency with no decimal places.5. Do not complete the Investment Analysis Section at this point, this will be completed later.Ignore the section Attempting a Manual Solution. Assignment 1B – bottom of p 1891. Use the bullets at the bottom of page 189 and at the top of page 190 to create your solverconstraints.a. Change the Solving Method to: Simplex LP b. Click Options and change the Integer Optimality to 1.c. Set the Solving Limits to a Max Time of 120. (Failure to make this change will causeSolver to run for almost 11 minutes).d. Run Solver. This will take the full two minutes.e. A window will appear stating the max time was reached. Click Stop.f. Create an Answer Report.g. Your result in cell Q28 should be either $65,582,200 OR $65,980,000.h. Rename the answer report tab name to Current Cruises Report. Assignment 1C – bottom of p 1911. Create a copy of the Original Cruises worksheet. (Right click on the tab name, select Move/Copy,check the box for create a copy, tell it to be placed at (move to end)). Rename it Island DuchessUpgrade.2. Format cells C38:C42 as currency with no decimal places. Format C43 as a number with twodecimal places.3. Complete section 1C as directed in the casebook. Note: some of your values will not match thecasebook since you did not allow Solver to run the full 11 minutes to find the perfect solution.a. Cost of Upgrades – the cost to upgradeeach ship is $30,000,000.4. When completed the IRR section shouldresemble the figure to the right.5. Run Solver again. (It will take the full 2 minutes).6. Create an answer report. Rename the tab name as Island Upgrade Report. Assignment 1D – bottom of page 1931. Copy the Island Duchess Upgrade worksheet and rename it Australia Cruise.2. Remove the Investment Analysis Section as stated.3. Modify the worksheet as directed to include Australia as a new destinationa. Ticket Prices/Docking Fees section:Australia$2,700$900$4.00b.Australia135090005200b. Calculating and Results section:4. Set the ship assignments for Australia all to zero except Coal Duchesses at 1 and GrandDuchesses at 2. (These three ships are currently not assigned to one of the other six destinations)5. Change the Solver parameters as directed on pages 195 and 196.6. If you run Solver a solution will not be found as there are currently not enough ships in the fleetto add Australia as a destination. The addition of one ship should be enough to service Australiabut the big question is which class of ship to add. Duchess Cruises only wants to add either oneGrand Duchess or one Millennium Duchess to the fleet.7. Close the Solver parameters window. Copy the Australia Cruise worksheet two times. Renameeach one Add (ship name) Ex. Add Grand, Add Millennium. For each worksheet add one ship forthe type you are adding located in the Constants section.8. Run Solver for each of the two worksheets you created. You No Not need to create an answerreport. Just run Solver to determine the weekly profits, weekly costs, and ship assignments. Assignment 2: Documenting Your Recommendations in a MemoFollow these modified directions. You will write a memo in MS Word to the management team ofDuchess Cruises that includes the following: In the first paragraph, briefly describe the situation and the purpose of your analysis. Summarize the results of the analyses completed (Original Cruises, Island Duchess Upgrades,and the addition of Australia as a destination). State your recommendation by answering thefollowing questions. Your recommendation should be supported by a solid argument based onyour findings and the needs of Duchess Cruises.a. Should the company keep the current ship assignment?b. Should the Island class ships be upgraded?c. Should Australia be added as a destination, if so which ship class should be built?(Remember this is assuming the Island class ships have the engine upgrades completed) Use appropriate screen shots from Excel or create charts/graphs to help illustrate your decision.What to submit:1. Excel file with worksheets in order noted above.2. Word memo with recommendation.3. Zip these two files together and rename the zipped file as lastnameSolverCase8.zip.4. Upload this zipped file to the Dropbox on the course site by the assigned due date.