## Answered: - Finish the Duchess cruise ship directions all question.

Finish the Duchess cruise ship directions all question.

44-317 MIS ? Spring 2016

Supplemental Instructions for Excel Solver Case 8

Duchess Cruise Ship Assignment Problem ? pp 183-197

Read the opening case information, pp 183-184. Then follow the supplemental directions below for the

corresponding pages in the Casebook, paying special attention to the business logic explained with the

various strategies. Disregard any directions in the Casebook to print or give a presentation.

Assignment 1 and 1A

1. 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 a

new Excel worksheet. Save the file as lastnameCruise (i.e. SchieberCruise.xlsx). Because you have

used this template, you will not locate the skeleton file in ?data files? as stated in 1A on the top

of p 185 nor complete the bullets for the Constants Section on pp 185-186. You should read

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 the

Sailing Cost per Nautical Mile divided by the Passenger Capacity.

Calculations and Results ? see bullets on pp 186-187

1. The first six bullets describe the different parts of the calculations section. The first formula you

need to complete is the seventh bullet (last bullet on page 186). Complete the bullets as directed

in 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 188

1. 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 array

multiplies them together then adds each entry together to come up with a total value. Ex

D22*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 189

1. Use the bullets at the bottom of page 189 and at the top of page 190 to create your solver

constraints.

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 cause

Solver 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.

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 191

1. 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 Duchess

2. Format cells C38:C42 as currency with no decimal places. Format C43 as a number with two

decimal places.

3. Complete section 1C as directed in the casebook. Note: some of your values will not match the

casebook since you did not allow Solver to run the full 11 minutes to find the perfect solution.

each ship is \$30,000,000.

4. When completed the IRR section should

resemble 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 193

1. 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 destination

a. Ticket Prices/Docking Fees section:

Australia

\$2,700

\$900

\$4.00

b.

Australia

1350

9000

5200

b. Calculating and Results section:

4. Set the ship assignments for Australia all to zero except Coal Duchesses at 1 and Grand

Duchesses 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 fleet

to add Australia as a destination. The addition of one ship should be enough to service Australia

but the big question is which class of ship to add. Duchess Cruises only wants to add either one

Grand Duchess or one Millennium Duchess to the fleet.

7. Close the Solver parameters window. Copy the Australia Cruise worksheet two times. Rename

the 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 answer

report. Just run Solver to determine the weekly profits, weekly costs, and ship assignments.

Assignment 2: Documenting Your Recommendations in a Memo

Follow these modified directions. You will write a memo in MS Word to the management team of

Duchess 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,

following questions. Your recommendation should be supported by a solid argument based on

your 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.

Solution details:
STATUS
QUALITY
Approved

This question was answered on: Oct 07, 2020

Solution~0002210049.zip (25.37 KB)

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy (Deadline assured. Flexible pricing. TurnItIn Report provided)

STATUS

QUALITY

Approved

Oct 07, 2020

EXPERT

Tutor