Captain Hook decided he wants to attack Neverland and take Peter Pan down once and

for all. However, the last attack did not go so well and his boat needs major repair. He

calls on you, his apprentice, to figure out whether he will be able to finance the rebuilding

of the ship in time for his next attack at the end of June $($the $30$th to be exact$).$ Captain

$1$

Hook wants to start in the first week of March. He figures it will take the crew several

months to get the ship back to being sea$-$worthy $($the crew does not work very fast,

especially since no power tools are used and rum is the requisite beverage$).$

Hook knows that you have had some experience in with Excel so he is depending on you

to determine if he can actually carry this out.

To finance this rebuilding, Captain Hook is going to sell some of his assets $$ not the best

idea but he needs cash. He has already contracted to sell his rot gut booze, jewels and

coins to a broker who specializes in pirate bounty over the next $4$ months. In March, he

will sell enough to raise $$22,000$ to use as income; in April he plans to sell $$17,000$; in

May he will sell $$22,000$ and in June, he is contracted to receive $$24,000.$ He hates to

part with his treasure but he is determined to attack Neverland and put an end to his

nemesis.

He will pay each pirate worker $$8\mathrm{.}25$ an hour for the time they spend working the ship. In

order to fix the ship, he needs his workers to work $2,000$ hours in March, $1,600$ in April,

$2,000$ in May and $2,100$ in June.

Pirates require rum while working otherwise they are prone to mutiny $($and killing the

captain$).$ Captain Hook will have to pay out an amount equal to $10\%$ of total labor costs

in rum each month. Hook will also need to stock the ship up with some food and that will

cost him $7\mathrm{.}5\%$ of each month$$s total labor costs. Hook hates to pay for this sort of thing,

but without the rum and food, the crew will not do a thing for him if they are hungry and

thirsty. This is expensive but he has to buy the food from Billy Bones, who knows he can

charge outrageous prices since he is the only pirate food purveyor at the harbor.

The pirates stay in a location that is $25$ miles round trip from the harbor. In order to get

provisions, regular trips are made to the harbor in a borrowed freighter. It runs on steam

and so Hook has to pay for the whale oil that must be used to heat the boiler to get the

ship back and forth to the harbor. The boat gets $9$ miles a gallon and whale oil costs

$$5\mathrm{.}80/$gallon$.$ He does make the boat completely full with each trip to minimize the

travel costs. Hook figures he will have to have $34$ trips in March, $36$ in April, $32$ in May

and $38$ in June.

Lastly, Hook has to pay a one$-$time fee of $$1,100$ in March to dock the boat for these $4$

months. One$-$Eyed Pete, the marina owner, loves it when his friends come to visit but

wants his money up front. This fee has been the same for years and it is not going to

change $($therefore it is not a $$variable$$ to be include at the top of the worksheet$).$

The total cost for the wood to rebuild the ship will be $$1,400$ dollars each month. This

figure may change due to the rising costs of lumber.

Hook has no time to deal with numbers, and as the pirate apprentice, you want to impress

him or risk being marooned on some island. Therefore, you quickly tell him you will

create this worksheet and let him know if this plan to rebuild and attack is feasible.

$2$

As you create the worksheet, you remember that Hook is notorious for changing his mind

about figures so you want to make the worksheet as flexible as possible. He also will

want to $$play$$ with it to see what leeway he might have with some of his expenses, etc.

so you must make it easy to adjust.

HINTS $($this means to do the following or become shark food$)$:

Set up the worksheet with the names of the months as labels across four columns $($all in

the same row$).$ Have the labels for Expenses listed only one column. Determine NET

INCOME for each month and total figures.

Use an input section for all figures related formulas that are used to calculate the

expenses. Hook is a bit wobbly on some of these figures and you want the worksheet to

be easy for him to change since he is unlikely to know anything about how to manage a

worksheet.

Use the worksheet to determine if Hook$$s idea is financially possible.

Use Goal Seek to determine:

a$.$ what the wage rate of the crew would be exactly in order to break even $($this is the rate

for all four months$)?$

b$.$ how low or high the rum $$percentage$$ can be if all the money is spent on this venture?

c$.$ how low or high could the monthly fee for wood be before the pursuit of Pan is$/$is not

viable?

d$.$ how many additional$/$fewer total trips can be taken to the harbor for provisions and

still stay within the budget?

e$.$ how many additional$/$fewer total hours the pirates can work and still stay within the

budget?

Full Complete Excel spreedsheet Please