Question Details
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:
Answered
QUALITY
Approved
ANSWER RATING
This question was answered on: Oct 07, 2020
PRICE: $15
Solution~000.zip (25.37 KB)
This attachment is locked

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
About this Question
STATUSAnswered
QUALITYApproved
DATE ANSWEREDOct 07, 2020
EXPERTTutor
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).
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 A DEADLINE.
