Expert answer:MS Excel project
Expert answer:
instructions_sc_ex16_cs4_7a.docx
support_sc_ex16_cs4_7a_cabins.docx
sc_ex16_cs4_7a_igormbiso_1.xlsx
Unformatted Attachment Preview
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
•
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
•
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.
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.
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 yearround 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.
Shelly Cashman Excel 2016 | Modules 4–7: SAM Capstone Project 1a
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
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.
5.
Begin completing the Amortization Schedule as follows:
6.
a.
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.
b.
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:
a.
Use the IF function 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_Schedule to define a custom name for the range
G2:K21.
Shelly Cashman Excel 2016 | Modules 4–7: SAM Capstone Project 1a
b.
9.
10.
11.
12.
13.
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:
a.
Select and unlock the range B5:B6.
b.
Select and unlock cell D3.
c.
Protect the worksheet without a password.
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 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.)
d.
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:
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.
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.
Create a copy of a lodging worksheet that Michael can use for the new inn:
Shelly Cashman Excel 2016 | Modules 4–7: SAM Capstone Project 1a
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:
16.
17.
18.
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.)
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 Chart as the worksheet name.
c.
Vary the fill colors of the columns by point.
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.
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.docx Word 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 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.)
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.
Shelly Cashman Excel 2016 | Modules 4–7: SAM Capstone Project 1a
b.
Use Criteria as the name of the range A5:J6.
c.
Use Extract as 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_Rentals that meet the criteria in the Criteria range. Copy the
extracted records to the Extract range. (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:
24.
a.
Look up the value in cell M10.
b.
Use Cabin_Rentals as the table_array argument.
c.
Return the corresponding renter’s name shown in column 2 of the table
array.
d.
Use FALSE as the range_lookup value to find an exact match.
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_Rentals as the table_array argument.
c.
Return the corresponding weekly rate shown in column 10 of the table
array.
d.
Use FALSE as 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.
Shelly Cashman Excel 2016 | Modules 4–7: SAM Capstone Project 1a
Final Figure 1: Inn Mortgage Worksheet
Final Figure 2: Highland Hotel Worksheet
Shelly Cashman Excel 2016 | Modules 4–7: SAM Capstone Project 1a
Final Figure 3: Adela Condos Worksheet
Shelly Cashman Excel 2016 | Modules 4–7: SAM Capstone Project 1a
Final Figure 4: Pinehill Lodge Worksheet
Final Figure 5: New Inn Worksheet
Shelly Cashman Excel 2016 | Modules 4–7: SAM Capstone Project 1a
Final Figure 6: Total Revenue Chart Worksheet
Final Figure 7: All Locations Worksheet
Shelly Cashman Excel 2016 | Modules 4–7: SAM Capstone Project 1a
Final Figure 8: Cabin Rentals Worksheet
Adela Mountain Resort – Cabin Rentals
Cabin ID
Last Name
First Name
Start Date
Bedrooms
Bathrooms
Sleeps
Kitchen
Mountain View
Weekly Rate
103 McCarthy
Peter
1/6/2019
2
2
6
YES
NO
2000
104 Bowen
Mary Dell
1/6/2019
3
2
6
YES
YES
2300
110 Green
Rashad
1/7/2019
2
2
4
NO
YES
1800
112 Chambers
Ian
1/8/2019
2
2
6
YES
NO
1700
115 Yang
Kim
1/9/2019
2
1
4
YES
YES
1750
120 Tollitts
Tyrone
1/10/2019
3
3
8
YES
YES
2800
122 Jaspers
Katherine
1/11/2019
1
1
3
NO
YES
1500
125 Rizzo
John
1/11/2019
1
1
2
YES
NO
1475
130 Cruz
Natalie
1/12/2019
3
3
10
YES
NO
2500
131 Washington
Devon
1/12/2019
3
2
8
YES
YES
2500
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from
the SAM website.
Shelly Cashman Excel 2016 |Modules 4–7: SAM Capstone Project 1a
Adela Mountain Resort
FINANCIAL FUNCTIONS, DATA, TABLES, AND CHARTS
Igor Mbiso
t edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from
the SAM website.
Adela Mountain Resort
Mortgage Loan Payment Calculator
Date
Amo
12-Sep-18 Rate
Item
Price
$
Nordic Inn Term (Years)
876,000.00 Monthly Payment
Down Payment
$
197,200.00 Total Interest
Loan Amount
$
678,800.00 Total Cost
4.250%
15
1
2
$
(678,800.00)
3
$
197,200.00
4
5
Varying Interest Rate Schedule
Rate
3.500%
3.650%
Monthly Payment
Total Interest
Year
6
Total Cost
7
8
9
10
11
12
13
14
15
Amortization Schedule
Beginning Balance
Ending Balance
$
$
678,800.00 $
645,732.21 $
645,732.21
611,231.34
$
611,231.34 $
575,235.28
$
$
575,235.28 $
537,679.23 $
537,679.23
498,495.59
$
498,495.59 $
457,613.82
$
457,613.82 $
414,960.33
$
$
414,960.33 $
370,458.33 $
370,458.33
324,027.71
$
$
324,027.71 $
275,584.90 $
275,584.90
225,042.69
$
$
$
$
225,042.69
172,310.10
117,292.19
59,889.94
$
172,310.10
$
117,292.19
$
59,889.94
$
Subtotal
Down Payment
Total Cost
Paid on Principal
$
–
Interest Paid
$
–
$
–
Adela Mountain Resort
Highland Hotel
2019 Rentals per Month
Room
January
February
March
April
May
June
Colorado
Montana
Utah
Wyoming
30
30
30
30
29
29
29
29
31
28
31
29
25
20
18
20
20
15
14
12
10
8
12
10
Yosemite
30
29
30
21
15
11
2019 Revenue per Month
Room
Colorado
Montana
Utah
Wyoming
Yosemite
Total
January
4950
5250
5250
5250
6000
$
26,700
February
4785
5075
5075
5075
5800
$
25,810 $
March
5115
4900
5425
5075
6000
26,515 $
April
3375
2700
2430
2900
3675
15,080 $
May
2700
2025
1890
1740
2625
10,980 $
June
1350
1080
1620
1450
1925
7,425
ela Mountain Resort
Highland Hotel
019 Rentals per Month
July
August
September
October
November
December
Total
10
7
9
11
10
8
10
12
20
21
30
18
31
28
27
30
30
30
30
30
31
31
31
31
277
255
271
262
8
11
18
31
30
31
265
August
1350
1080
1350
1740
1925
September
2700
2835
4050
2610
3150
October
5115
4900
4725
5250
6200
November
4950
5250
5250
5250
6000
December
5115
5425
5425
5425
6200
Total
42855
41465
43705
43360
50900
019 Revenue per Month
July
1350
945
1215
1595
1400
$
6,505 $
7,445 $
15,345 $
26,190 $
26,700 $
27,590
$
222,285
Adela Mountain Resort
Adela Condos
2019 Monthly Rentals
Room
January
February
March
April
May
June
Creekside
30
29
28
30
31
5
Fir Ridge
Georgetown
30
30
29
29
30
30
28
21
25
24
7
10
Juniper
Lakeside
30
30
29
29
31
31
25
30
20
18
9
11
2019 Revenue per Month
Room
Creekside
Fir Ridge
Georgetown
Juniper
Lakeside
Total
$
January
4500
February
4350
March
4200
April
3900
May
4030
June
650
4350
4650
4650
5550
4205
4495
4495
5365
4350
4650
4805
5735
3220
2415
3125
4650
2875
2760
2500
2790
805
1150
1125
1705
23,700
$
22,910 $
23,740 $
17,310 $
14,955 $
5,435
ela Mountain Resort
Adela Condos
2019 Monthly Rentals
July
August
September
October
November
December
Total
9
10
18
30
30
31
281
12
11
12
8
21
30
31
25
30
30
31
31
286
279
8
11
8
9
21
18
26
31
30
30
31
31
268
279
July
1170
August
1300
September
2340
October
4500
November
4500
December
4650
Total
40090
1380
1265
1000
1705
1380
920
1000
1395
2415
3450
2625
2790
4495
3875
4030
5735
4350
4650
4650
5550
4495
4805
4805
5735
38320
39085
38810
48705
019 Revenue per Month
$
6,520 $
5,995 $
13,620 $
22,635 $
23,700 $
24,490
$
205,010
Adela Mountain Resort
Pinehill Lodge
2019 Monthly Rentals
Room
January
February
March
April
May
June
Alpine
30
29
27
20
15
11
Bellflower
Edelweiss
30
30
29
29
28
30
14
12
10
9
9
13
Gentian
Wintergreen
30
30
29
29
25
27
14
15
7
10
11
11
2019 Revenue per Month
Room
Alpine
Bellflower
Edelweiss
Gentian
Wintergreen
Total
$
January
4950
February
4785
March
4455
April
2700
May
2025
June
1485
5250
5250
5250
6000
5075
5075
5075
5800
4900
5250
4375
5400
1890
1620
2030
2625
1350
1215
1015
1750
1215
1755
1595
1925
26,700
$
25,810 $
24,380 $
10,865 $
7,355 $
7,975
ela Mountain Resort
Pinehill Lodge
2019 Monthly Rentals
July
August
September
October
November
December
Total
10
9
18
31
30
31
261
7
9
7
9
19
28
25
24
30
30
31
31
239
254
11
10
11
10
16
16
26
29
30
30
31
31
241
248
July
1350
August
1215
September
2430
October
5115
November
4950
December
5115
Total
40575
945
1215
1595
1750
945
1215
1595
1750
2565
3780
2320
2800
4375
4200
4550
5800
5250
5250
5250
6000
5425
5425
5425
6200
39185
41250
40075
47800
019 Revenue per Month
$
6,855 $
6,720 $
13,895 $
24,040 $
26,700 $
27,590
$
208,885
Adela Mountain Resort
All Locations
Current Date
2019 Monthly Rentals
Room
January
February
March
April
May
June
July
August
September
October
November
December
Total
1
0
2
0
3
0
4
0
5
0
2019 Monthly Revenue
Room
1
January
$
–
February
$
–
March
$
–
April
$
–
May
$
June
–
$
July
–
$
August
–
$
–
September
$
–
October
$
–
November
$
–
December
$
–
Total
$
–
2
–
–
–
–
–
–
–
–
–
–
–
–
–
3
–
–
–
–
–
–
–
–
–
–
–
–
–
4
5
–
–
–
–
–
–
–
–
–
–
–
–
–
Total
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
$
–
Adela Mountain Resort
Cabin Rentals
Criteria Range
Cabin ID
Last Name
First Name Start Date
Bedrooms
Bathrooms Sleeps
Cabin Rentals
Extract Area
ountain Resort
bin Rentals
Kitchen
Mountain
Weekly
Rental Lookup
Cabin ID
125
Renter
Weeks Rented
Revenue
2
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades