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

 

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

 

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

 

Upgrade.

 

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.

 

a. Cost of Upgrades ? the cost to upgrade

 

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

 

each one Add (ship name) Ex. Add Grand, Add Millennium. For each worksheet add one ship for

 

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,

 

and the addition of Australia as a destination). State your recommendation by answering the

 

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:

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Oct 07, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

• As a reference for in-depth understanding of the subject.
• As a source of ideas / reasoning for your own research (if properly referenced)
• For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).

This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN YOUR SET DEADLINE.