**
Fill This Form To Receive Instant Help**

Homework answers / question archive / 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

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

Adela Mountain Resort

FINANCIAL FUNCTIONS, DATA, TABLES, AND CHARTS

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

0. 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:

· **Support_SC_EX16_CS4-7a_Cabins.docx**

With the file **SC_EX16_CS4-7a_ FirstLastName_2.xlsx** still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

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

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_Years** by **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.

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.

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:

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

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

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

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

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 Cells** conditional 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**.

Begin completing the Amortization Schedule as follows:

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

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

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:

k. Use the IF** **function to check if the value in cell **H4** (the balance remaining on the loan each year) is **greater than 0**.

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

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

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

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

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

o. Use **Amortization_Schedule** to define a custom name for the range G2:K21.

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

Protect the *Inn Mortgage* worksheet against unauthorized changes as follows:

q. Select and unlock the range B5:B6.

r. Select and unlock cell D3.

s. Protect the worksheet without a password.

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

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

u. 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.)

v. Apply the **Comma** **Style** 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.)

w. Ungroup the worksheets.

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:

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

y. Use **Adela Condos 2019 Revenue** as the chart title.

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

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

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

ab. Change the data label’s position to **Outside End**.

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

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

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

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

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

ag. Use **New Inn** as the name of the new worksheet.

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

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

Update the *All Locations* worksheet as follows:

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

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

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

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

al. Move the 2-D clustered column chart to a new chart sheet and use **Total Revenue Chart** as the worksheet name.

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

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

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

ao. Use **Monthly Revenue** as the chart title.

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:

ap. Open the **Support_SC_EX16_CS4-7a_Cabins.docx** Word document.

aq. 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.]

ar. Format the range A10:J20 as a table with headers using the **Table Style Medium 13** table 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.)

Prepare for extracting data based on criteria as follows:

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

at. Use **Criteria** as the name of the range A5:J6.

au. Use **Extract** as the name of the range A23:J24.

av. Look up the value in cell **M10**.

aw. Use **Cabin_Rentals** as the table_array argument.

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

ay. Use **FALSE** as the range_lookup value to find an exact match.

az. Look up the value in cell **M10**.

ba. Use **Cabin_Rentals** as the table_array argument.

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

bc. Use **FALSE** as the range_lookup value to find an exact match.

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

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

Already member? Sign In