Sean Barker plans to open a small boating marina in the east coast. You have been hired by Mr. Barker to apply your Excel skills to help him make better business decisions.
Q1. (Small Business Loan Analysis) Mr. Barker has secured a 5-year small business loan of $100,000 with 7% interest rate to buy jet skis for his boating marina business. In the Q1_Loan Analysis worksheet, calculate the monthly payment amount, total monthly payment amount over the life of the loan, and total interest payment amount.

Q2. (Goal Seek) Create a copy of the Q1_Loan Analysis worksheet and name it as Q2_Goal Seek Analysis. In this worksheet, use Goal Seek to find the interest rate Mr. Barker should get for the small business loan if he wants to pay $1850 per month for the loan. Answer obtained not using Goal Seek will get a grade of 0.

Q3. (Scenario Manager) After seeking alternative funding sources and discussing with a variety of banks, Mr. Barker has also received three other options for the $100,000 small business loan from Bank of America, Sandy Spring Bank, and Capital One Bank as shown below (continued next page).
Create a copy of the Q1_Loan Analysis worksheet and name it as Q3_Scenairo Analysis. In this worksheet, use Scenario Manager to create a scenario summary report named Q3_Scenario Summary that displays the monthly payment and total interest payment for each possible bank loan. The labels for Changing Cells and Results Cells in the scenario summary should be informative. Make sure that the scenario summary is in a new worksheet. Namely, you need to keep both the Q3_Scenairo Analysis worksheet and the resulting scenario summary worksheet. A scenario summary that is not created by Scenario Manager will get a grade of 0. A scenario summary without the Q3_Scenairo Analysis worksheet will get a grade of 0.

Q4. (Solver) With the secured loan, Mr. Barker has $100,000 budget to buy jet skis. He is evaluating one-person, two-person, and four-person jet skis. The one-person jet ski costs $8,700; the two-person unit costs $11,000; and the four-person unit costs $15,000. The maintenance cost per day for a one-person jet ski is $15; the maintenance cost per day for a two-person jet ski is $20; and the maintenance cost per day for a four-person jet ski is $25.
The rental season lasts for 90 days. Because many people come for boating in the rental season, every jet ski is rented for every day. The rental price per day for each jet ski is listed in the worksheet. Because it is easiest to get customers for one-person jet skis and it is most difficult to get customers for four-person jet skis, Mr. Barker does not want to buy more than 3 four-person jet skis and he wants to buy at least 3 one-person jet skis. In the Q4_Jet Ski Business worksheet, use Solver to find out how many one-person, two-person, and four-person jet skis Mr. Barker should buy to maximize his grand total profit from the jet ski rental business. Answers generated not using Solver will only get partial credit for correct formulas while losing most of the points.
Hint:
Scenarios
                                          Bank of America       Sandy Spring Bank       Capital One Bank
Loan Term (years)                            3                               7                                     10
Interest Rate                                  6.35%                         7.75%                            8.15%

Total maintenance cost = Maintenance cost per day*Purchase quantity*Rental season in days Total profit = Total revenue from rental - Total purchase cost - Total maintenance cost

Q5. Create a copy of the Q4_Jet Ski Business worksheet and rename it as Q5_Jet Ski Business. In this worksheet, use scenario manager to generate a scenario summary named Q5_Scenario Summary that compares the purchase cost and grand total profit for the following three scenarios of jet ski purchases.
In the resulting scenario summary worksheet, explain in texts why each of the above scenarios is not the correct answer to Q4 that would be produced by Solver (i.e., why each scenario does not satisfy the constraints or objective in Q4).
Scenarios
                                    Scenario 1          Scenario 2          Scenario 3
One-person jet ski                4                         5                         4
Two-person jet ski                2                         2                         3
Four-person jet ski                3                         2                         2

Sean Barker plans to open a small boating marina in the east coast....