Excel Modify
jymojica
SC_EX16_CS4-7a_JesusMojica_1.xlsxDocumentation
| Shelly Cashman Excel 2016 |Modules 4–7: SAM Capstone Project 1a | |
| Adela Mountain Resort | |
| FINANCIAL FUNCTIONS, DATA, TABLES, AND CHARTS | |
| Author: | Jesus Mojica |
| 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. | |
Inn Mortgage
| Adela Mountain Resort | ||||||||
| Mortgage Loan Payment Calculator | Amortization Schedule | |||||||
| Date | 12-Sep-18 | Rate | 4.250% | Year | Beginning Balance | Ending Balance | Paid on Principal | Interest Paid |
| Item | Nordic Inn | Term (Years) | 15 | 1 | $ 678,800.00 | $ 645,732.21 | ||
| Price | $ 876,000.00 | Monthly Payment | 2 | $ 645,732.21 | $ 611,231.34 | |||
| Down Payment | $ 197,200.00 | Total Interest | $ (678,800.00) | 3 | $ 611,231.34 | $ 575,235.28 | ||
| Loan Amount | $ 678,800.00 | Total Cost | $ 197,200.00 | 4 | $ 575,235.28 | $ 537,679.23 | ||
| 5 | $ 537,679.23 | $ 498,495.59 | ||||||
| Varying Interest Rate Schedule | 6 | $ 498,495.59 | $ 457,613.82 | |||||
| Rate | Monthly Payment | Total Interest | Total Cost | 7 | $ 457,613.82 | $ 414,960.33 | ||
| 8 | $ 414,960.33 | $ 370,458.33 | ||||||
| 3.500% | 9 | $ 370,458.33 | $ 324,027.71 | |||||
| 3.650% | 10 | $ 324,027.71 | $ 275,584.90 | |||||
| 11 | $ 275,584.90 | $ 225,042.69 | ||||||
| 12 | $ 225,042.69 | $ 172,310.10 | ||||||
| 13 | $ 172,310.10 | $ 117,292.19 | ||||||
| 14 | $ 117,292.19 | $ 59,889.94 | ||||||
| 15 | $ 59,889.94 | $ - 0 | ||||||
| Subtotal | $ - 0 | $ - 0 | ||||||
| Down Payment | ||||||||
| Total Cost | $ - 0 | |||||||
Highland Hotel
| Adela Mountain Resort | |||||||||||||
| Highland Hotel | |||||||||||||
| 2019 Rentals per Month | |||||||||||||
| Room | January | February | March | April | May | June | July | August | September | October | November | December | Total |
| Colorado | 30 | 29 | 31 | 25 | 20 | 10 | 10 | 10 | 20 | 31 | 30 | 31 | 277 |
| Montana | 30 | 29 | 28 | 20 | 15 | 8 | 7 | 8 | 21 | 28 | 30 | 31 | 255 |
| Utah | 30 | 29 | 31 | 18 | 14 | 12 | 9 | 10 | 30 | 27 | 30 | 31 | 271 |
| Wyoming | 30 | 29 | 29 | 20 | 12 | 10 | 11 | 12 | 18 | 30 | 30 | 31 | 262 |
| Yosemite | 30 | 29 | 30 | 21 | 15 | 11 | 8 | 11 | 18 | 31 | 30 | 31 | 265 |
| 2019 Revenue per Month | |||||||||||||
| Room | January | February | March | April | May | June | July | August | September | October | November | December | Total |
| Colorado | 4950 | 4785 | 5115 | 3375 | 2700 | 1350 | 1350 | 1350 | 2700 | 5115 | 4950 | 5115 | 42855 |
| Montana | 5250 | 5075 | 4900 | 2700 | 2025 | 1080 | 945 | 1080 | 2835 | 4900 | 5250 | 5425 | 41465 |
| Utah | 5250 | 5075 | 5425 | 2430 | 1890 | 1620 | 1215 | 1350 | 4050 | 4725 | 5250 | 5425 | 43705 |
| Wyoming | 5250 | 5075 | 5075 | 2900 | 1740 | 1450 | 1595 | 1740 | 2610 | 5250 | 5250 | 5425 | 43360 |
| Yosemite | 6000 | 5800 | 6000 | 3675 | 2625 | 1925 | 1400 | 1925 | 3150 | 6200 | 6000 | 6200 | 50900 |
| Total | $ 26,700 | $ 25,810 | $ 26,515 | $ 15,080 | $ 10,980 | $ 7,425 | $ 6,505 | $ 7,445 | $ 15,345 | $ 26,190 | $ 26,700 | $ 27,590 | $ 222,285 |
Adela Condos
| Adela Mountain Resort | |||||||||||||
| Adela Condos | |||||||||||||
| 2019 Monthly Rentals | |||||||||||||
| Room | January | February | March | April | May | June | July | August | September | October | November | December | Total |
| Creekside | 30 | 29 | 28 | 30 | 31 | 5 | 9 | 10 | 18 | 30 | 30 | 31 | 281 |
| Fir Ridge | 30 | 29 | 30 | 28 | 25 | 7 | 12 | 12 | 21 | 31 | 30 | 31 | 286 |
| Georgetown | 30 | 29 | 30 | 21 | 24 | 10 | 11 | 8 | 30 | 25 | 30 | 31 | 279 |
| Juniper | 30 | 29 | 31 | 25 | 20 | 9 | 8 | 8 | 21 | 26 | 30 | 31 | 268 |
| Lakeside | 30 | 29 | 31 | 30 | 18 | 11 | 11 | 9 | 18 | 31 | 30 | 31 | 279 |
| 2019 Revenue per Month | |||||||||||||
| Room | January | February | March | April | May | June | July | August | September | October | November | December | Total |
| Creekside | 4500 | 4350 | 4200 | 3900 | 4030 | 650 | 1170 | 1300 | 2340 | 4500 | 4500 | 4650 | 40090 |
| Fir Ridge | 4350 | 4205 | 4350 | 3220 | 2875 | 805 | 1380 | 1380 | 2415 | 4495 | 4350 | 4495 | 38320 |
| Georgetown | 4650 | 4495 | 4650 | 2415 | 2760 | 1150 | 1265 | 920 | 3450 | 3875 | 4650 | 4805 | 39085 |
| Juniper | 4650 | 4495 | 4805 | 3125 | 2500 | 1125 | 1000 | 1000 | 2625 | 4030 | 4650 | 4805 | 38810 |
| Lakeside | 5550 | 5365 | 5735 | 4650 | 2790 | 1705 | 1705 | 1395 | 2790 | 5735 | 5550 | 5735 | 48705 |
| Total | $ 23,700 | $ 22,910 | $ 23,740 | $ 17,310 | $ 14,955 | $ 5,435 | $ 6,520 | $ 5,995 | $ 13,620 | $ 22,635 | $ 23,700 | $ 24,490 | $ 205,010 |
Pinehill Lodge
| Adela Mountain Resort | |||||||||||||
| Pinehill Lodge | |||||||||||||
| 2019 Monthly Rentals | |||||||||||||
| Room | January | February | March | April | May | June | July | August | September | October | November | December | Total |
| Alpine | 30 | 29 | 27 | 20 | 15 | 11 | 10 | 9 | 18 | 31 | 30 | 31 | 261 |
| Bellflower | 30 | 29 | 28 | 14 | 10 | 9 | 7 | 7 | 19 | 25 | 30 | 31 | 239 |
| Edelweiss | 30 | 29 | 30 | 12 | 9 | 13 | 9 | 9 | 28 | 24 | 30 | 31 | 254 |
| Gentian | 30 | 29 | 25 | 14 | 7 | 11 | 11 | 11 | 16 | 26 | 30 | 31 | 241 |
| Wintergreen | 30 | 29 | 27 | 15 | 10 | 11 | 10 | 10 | 16 | 29 | 30 | 31 | 248 |
| 2019 Revenue per Month | |||||||||||||
| Room | January | February | March | April | May | June | July | August | September | October | November | December | Total |
| Alpine | 4950 | 4785 | 4455 | 2700 | 2025 | 1485 | 1350 | 1215 | 2430 | 5115 | 4950 | 5115 | 40575 |
| Bellflower | 5250 | 5075 | 4900 | 1890 | 1350 | 1215 | 945 | 945 | 2565 | 4375 | 5250 | 5425 | 39185 |
| Edelweiss | 5250 | 5075 | 5250 | 1620 | 1215 | 1755 | 1215 | 1215 | 3780 | 4200 | 5250 | 5425 | 41250 |
| Gentian | 5250 | 5075 | 4375 | 2030 | 1015 | 1595 | 1595 | 1595 | 2320 | 4550 | 5250 | 5425 | 40075 |
| Wintergreen | 6000 | 5800 | 5400 | 2625 | 1750 | 1925 | 1750 | 1750 | 2800 | 5800 | 6000 | 6200 | 47800 |
| Total | $ 26,700 | $ 25,810 | $ 24,380 | $ 10,865 | $ 7,355 | $ 7,975 | $ 6,855 | $ 6,720 | $ 13,895 | $ 24,040 | $ 26,700 | $ 27,590 | $ 208,885 |
All Locations
| 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 | January | February | March | April | May | June | July | August | September | October | November | December | Total |
| 1 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 |
| 2 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 |
| 3 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 |
| 4 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 |
| 5 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 |
| Total | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 | $ - 0 |
Cabin Rentals
| Adela Mountain Resort | |||||||||
| Cabin Rentals | |||||||||
| Criteria Range | |||||||||
| Cabin ID | Last Name | First Name | Start Date | Bedrooms | Bathrooms | Sleeps | Kitchen | Mountain View | Weekly Rate |
| Cabin Rentals | Rental Lookup | ||||||||
| Cabin ID | 125 | ||||||||
| Renter | |||||||||
| Weeks Rented | 2 | ||||||||
| Revenue | |||||||||
| Extract Area | |||||||||
