Shelly Cashman Excel 2016 | Modules 4–7: SAM Capstone Project 1a

Adela Mountain Resort

FINANCIAL FUNCTIONS, DATA, TABLES, AND CHARTS

GETTING STARTED

· Open the file SC_EX16_CS4-7a_FirstLastName_1.xlsx, available for download from the SAM website.

· Save the file as SC_EX16_CS4-7a_FirstLastName_2.xlsx by changing the “1” to a “2”.

o If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

· To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

o Support_SC_EX16_CS4-7a_Cabins.docx

· With the file SC_EX16_CS4-7a_FirstLastName_2.xlsxstill open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. Michael Germaine is the general manager of Adela Mountain Resort, a year-round sports resort in Ketchum, Idaho. He needs to work with financial data for the resort, including the loan for a new inn. He also wants to consolidate rental data from three locations that provide lodging and analyze rental data for the resort’s cabins. Michael wants to start by calculating how the loan for the inn will affect the company’s budget each month and over the life of the loan. He also wants to consider how mortgage interest rates will affect the total cost of the new inn.

Switch to the Inn Mortgage worksheet, and then determine the monthly loan payments for the new inn as follows:

a. In cell D5, use the PMT function to calculate the monthly payment for the inn mortgage using the defined names Rate, Term_Years, and Loan_Amount as the rate, nper, and pv arguments in the formula.

b. In the formula, divide Rate by 12.

c. Multiply Term_Yearsby 12 to calculate the total number of monthly payments.

d. Insert a negative sign before the PMT function to make the formula return a positive value.

2. To determine the effects of different interest rates on the total cost of the inn, fill the range A14:A22 with a percent series based on the values in the range A12:A13.

3. Create a one-input data table as follows to determine how variable interest rates in the range A12:A22 will affect the total cost of the new inn:

a. In cell B11, insert a formula that references cell D5 (the monthly payments).

b. In cell C11, insert a formula that references cell D6 (the total interest paid on the loan).

c. In cell D11, insert a formula that references cell D7 (the total cost of the mortgage).

d. Based on the range A11:D22, create a one-input data table using an absolute reference to cell D3 (the mortgage interest rate) as the Column input cell.

4. To make it easier to see the interest rate in the Varying Interest Rate Schedule that matches the interest rate Michael expects for the loan, apply a Highlight Cellsconditional formatting rule to the range A12:A22 that formats any cell with a value equal to an absolute reference to cell D3 using Green Fill with Dark Green Text.

5. Begin completing the Amortization Schedule as follows:

a. Insert a formula without using a function in cell J4 that subtracts the value in cell I4from the value in cell H4 to determine how much of the loan principal is being paid each year.

b. Copy the formula you created in cell J4 to the range J5:J18 without copying any cell formatting. (Hint: Use the Paste Gallery.)

6. In cell K4, create a formula using the IF function to calculate the interest paid on the mortgage (or the difference between the total payments made each year and the total amount of the principal paid each year) as follows:

a. Use the IFfunction to check if the value in cell H4(the balance remaining on the loan each year) is greater than 0.

b. If the value in cell H4 is greater than 0, multiply 12 by the value in cell D5 and then subtract the value in cell J4. Use an absolute cell reference to cell D5 and a relative cell reference to cell J4.

c. If the value in cell H4 is not greater than 0, return a value of 0.

d. Copy the formula you created in cell K4 to the range K5:K18 without copying any cell formatting. (Hint: Use the Paste Gallery.)

7. In cell K20, insert a formula that references the defined name Down_Payment to insert the down payment amount.

8. To make parts of the worksheet easier to print, assign names to ranges as follows:

a. Use Amortization_Scheduleto define a custom name for the range G2:K21.

b. Define names for the cells in the range D5:D7 based on the values in the range C5:C7.

9. Protect the Inn Mortgage worksheet against unauthorized changes as follows:

a. Select and unlock the range B5:B6.

b. Select and unlock cell D3.

c. Protect the worksheet without a password.

10.     Consolidate and standardize the format of the data for each lodging location as follows:

a. Group the Highland Hotel, Adela Condos, and Pinehill Lodge worksheets.

b. With all three worksheets selected, apply the Accounting number format with zero decimal places and $ as the symbol to the range B15:N15. (Hint: Depending on how you perform this step, the number format may be displayed as Custom.)

c. Apply the CommaStyle format with zero decimal places to the range B16:N19. (Hint: Depending on how you perform this step, the number format may be displayed as Custom.)

d. Ungroup the worksheets.

11.     Go to the Adela Condos worksheet. Michael wants to analyze the rentals of each suite in the Adela Condos. Create a chart illustrating this information as follows:

a. Insert a 2-D Pie chart based on the data in the ranges A15:A19 and N15:N19.

b. Use Adela Condos 2019 Revenue as the chart title.

c. Resize and reposition the 2-D pie chart so that the upper-left corner is located within cell A22 and the lower-right corner is located within cell G39.

12.     Modify the 2-D pie chart as follows to clearly identify what each pie slice represents:

a. Include data labels to show only the Category Name and the Percentage values.

b. Change the data label’s position to Outside End.

c. Update the data label’s number format to use the Percentage number format with 1 decimal place.

d. Explode the slice of the 2-D pie chart representing the revenue from the Fir Ridge suite rentals by 15% to highlight the suite with the lowest percentage of revenue.

e. Remove the legend since the data labels already show the category names.

13.     Create a copy of a lodging worksheet that Michael can use for the new inn:

a. Create a copy of the Pinehill Lodge worksheet between the Pinehill Lodge and the All Locations worksheets.

b.       Use New Inn as the name of the new worksheet.

c. Clear the contents (but not the formatting) of the merged range A2:N2, the range A7:M11, and the range A15:A19.

14.     Go to the All Locations worksheet. In cell A3, enter a formula using the TODAY function to display the current date.

15.     Update the All Locations worksheet as follows:

a. In cell B7, enter a formula using the SUM function, 3-D references, and grouped worksheets to total the values in cell B7 on the Highland Hotel:Pinehill Lodge worksheets.

b.       Copy the formula you created in cell B7 to the range B7:M11 without copying any cell formatting. (Hint: Use the Paste Gallery.)

16. To help analyze the revenue data for all locations, create a chart as follows:

a. For the ranges B14:M14 and B20:M20 of the All Locations worksheet, insert a 2-D Clustered Column chart that shows each month as a data series.

b. Move the 2-D clustered column chart to a new chart sheet and use Total Revenue Chartas the worksheet name.

c. Vary the fill colors of the columns by point.

17. To make the new chart easier to interpret, make the following changes:

a. Add a Primary Vertical Axis Title and use Revenue as the text.

b. Use Monthly Revenue as the chart title.

18. Go to the Cabin Rentals worksheet. Michael stored Adela cabin rental data for the second week of January 2019 in a Word file. Import the data as follows:

a. Open the Support_SC_EX16_CS4-7a_Cabins.docxWord document.

b. Select the table, copy the data to the Office Clipboard, paste the Word data into cell A10 of the Cabin Rentals worksheet, matching the destination formatting, and then close the Word file. [Mac Hint: Paste the contents of the table as normal, and then use the Paste Options button that pops up to match the destination formatting.]

c. Format the range A10:J20 as a table with headers using the Table Style Medium 13table style to match the formatting of the rest of the workbook. (Hint: Depending on your version of Office, the Table Style option may appear as Red, Table Style Medium 13.)

19. Name the range A10:J20 Cabin_Rentals to identify it. (Hint: The Refers To field in the Name Manager may list this as Table1[#All].)

20. Prepare for extracting data based on criteria as follows:

a. Copy the headings in the range A5:J5 and paste them to the ranges A10:J10 and A23:J23.

b. Use Criteriaas the name of the range A5:J6.

c. Use Extractas the name of the range A23:J24.

21. In the Criteria range, fill in the comparison criteria to select cabins that have a kitchen and more than two bedrooms. Use Final Figure 8 as a guide.

22. Use the Advanced Filter dialog box to extract records from the range Cabin_Rentalsthat meet the criteria in the Criteriarange. Copy the extracted records to the Extractrange. (Hint: Select Yes to respond to the dialog box that displays a warning about the destination range.)

23. In the Cabin Rentals worksheet, Michael created an area for retrieving information from the range Cabin_Rentals. In cell M11, enter a formula using the VLOOKUP function to retrieve the renter’s name as follows:

a. Look up the value in cell M10.

b. Use Cabin_Rentalsas the table_array argument.

c. Return the corresponding renter’s name shown in column 2 of the table array.

d. Use FALSEas the range_lookup value to find an exact match.

24. In cell M13, enter a formula using the VLOOKUP function to retrieve the weekly rate for the cabin and multiply the rate by the number of weeks rented:

a. Look up the value in cell M10.

b. Use Cabin_Rentalsas the table_array argument.

c. Return the corresponding weekly rate shown in column 10 of the table array.

d. Use FALSEas the range_lookup value to find an exact match.

e. Multiply the result of the VLOOKUP function by the value in cell M12.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Inn Mortgage Worksheet

Final Figure 2: Highland Hotel Worksheet

Final Figure 3: Adela Condos Worksheet

Final Figure 4: Pinehill Lodge Worksheet

Final Figure 5: New Inn Worksheet

Final Figure 6: Total Revenue Chart Worksheet

Final Figure 7: All Locations Worksheet

Final Figure 8: Cabin Rentals Worksheet