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 RSA Project 1a

Adela Mountain Resort

FINANCIAL FUNCTIONS, DATA, TABLES, AND CHARTS

Tsholofelo Modise

ot 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 Am
Date 12-Sep-18 Rate 4.250% Year
Item Nordic Inn Term (Years) 15 1
Price R 11,997,345.00 Monthly Payment R 90,253.44 2
Down Payment R 2,700,772.00 Total Interest R 4,248,273.54 3
Loan Amount R 11,997,345.00 Total Cost R 16,245,618.54 4
5
Varying Interest Rate Schedule 6
Rate Monthly Payment Total Interest Total Cost 7
R 90,253.44 R 4,248,273.54 R 16,245,618.54 8
3.500% 15,438,046.47 9
3.650% 15,597,610.09 10
3.800% 15,758,151.77 11
3.950% 15,919,668.89 12
4.100% 16,082,158.74 13
4.250% 16,245,618.54 14
4.400% 16,410,045.42 15
4.550% 16,575,436.40
4.700% 16,741,788.45
4.850% 16,909,098.43
5.000% 17,077,363.13
 Amortization Schedule
Beginning Balance Ending Balance Paid on Principal Interest Paid
R 11,997,345.00 R 11,412,893.50 R 584,451.50 R 498,589.74
R 11,412,893.50 R 10,803,113.21 R 609,780.29 R 473,260.95
R 10,803,113.21 R 10,166,906.44 R 636,206.78 R 446,834.46
R 10,166,906.44 R 9,503,127.91 R 663,778.52 R 419,262.71
R 9,503,127.91 R 8,810,582.74 R 692,545.17 R 390,496.07
R 8,810,582.74 R 8,088,024.24 R 722,558.50 R 360,482.74
R 8,088,024.24 R 7,334,151.71 R 753,872.53 R 329,168.70
R 7,334,151.71 R 6,547,608.07 R 786,543.65 R 296,497.59
R 6,547,608.07 R 5,726,977.42 R 820,630.65 R 262,410.59
R 5,726,977.42 R 4,870,782.51 R 856,194.91 R 226,846.33
R 4,870,782.51 R 3,977,482.07 R 893,300.44 R 189,740.80
R 3,977,482.07 R 3,045,468.03 R 932,014.04 R 151,027.19
R 3,045,468.03 R 2,073,062.62 R 972,405.40 R 110,635.83
R 2,073,062.62 R 1,058,515.39 R 1,014,547.23 R 68,494.00
R 1,058,515.39 R - R 1,058,515.39 R 24,525.84
Subtotal R 11,997,345.00 R 4,248,273.54
Down Payment
Total Cost R 16,245,618.54
 Adela Mountain Resort
Highland Hotel

2019 Rentals per Month

Room January February March April May June
Johannesburg 30 29 31 25 20 10
Pretoria 30 29 28 20 15 8
Bloemfontein 30 29 31 18 14 12
Durban 30 29 29 20 12 10
Cape Town 30 29 30 21 15 11

2019 Revenue per Month

Room January February March April May June
Johannesburg R 67,800 R 65,540 R 70,060 R 46,225 R 36,980 R 18,490
Pretoria 71,910.00 69,513.00 67,116.00 36,980.00 27,735.00 14,792.00
Bloemfontein 71,910.00 69,513.00 74,307.00 33,282.00 25,886.00 22,188.00
Durban 71,910.00 69,513.00 69,513.00 39,720.00 23,832.00 19,860.00
Cape Town 82,170.00 79,431.00 82,170.00 50,337.00 35,955.00 26,367.00
Total R 365,700 R 353,510 R 363,166 R 206,544 R 150,388 R 101,697
ela Mountain Resort
Highland Hotel

2019 Rentals per Month

July August September October November December Total
10 10 20 31 30 31 277
7 8 21 28 30 31 255
9 10 30 27 30 31 271
11 12 18 30 30 31 262
8 11 18 31 30 31 265

019 Revenue per Month

July August September October November December Total
R 18,490 R 18,490 R 36,980 R 70,060 R 67,800 R 70,060 R 586,975
12,943.00 14,792.00 38,829.00 67,116.00 71,910.00 74,307.00 567,943.00
16,641.00 18,490.00 55,470.00 64,719.00 71,910.00 74,307.00 598,623.00
21,846.00 23,832.00 35,748.00 71,910.00 71,910.00 74,307.00 593,901.00
19,176.00 26,367.00 43,146.00 84,909.00 82,170.00 84,909.00 697,107.00
R 89,096 R 101,971 R 210,173 R 358,714 R 365,700 R 377,890 R 3,044,549
 Adela Mountain Resort
Adela Condos

2019 Monthly Rentals

Room January February March April May June
Kimberley 30 29 28 30 31 5
Port Elizabeth 30 29 30 28 25 7
Mthatha 30 29 30 21 24 10
George 30 29 31 25 20 9
Upington 30 29 31 30 18 11

2019 Revenue per Month

Room January February March April May June
Kimberley R 61,620 R 59,566 R 57,512 R 53,400 R 55,180 R 8,900
Port Elizabeth 59,580.00 57,594.00 59,580.00 44,100.00 39,375.00 11,025.00
Mthatha 63,690.00 61,567.00 63,690.00 33,075.00 37,800.00 15,750.00
George 63,690.00 61,567.00 65,813.00 42,800.00 34,240.00 15,408.00
Upington 76,020.00 73,486.00 78,554.00 63,690.00 38,214.00 23,353.00
Total R 324,600 R 313,780 R 325,149 R 237,065 R 204,809 R 74,436

Adela Condos 2019 Revenue

1; R61,620 ;
5; 76,020.00 ; 19.0%
23.4%

2; 59,580.00 ;
18.4%
4; 63,690.00 ;
19.6%

3; 63,690.00 ; 19.6%
ela Mountain Resort
Adela Condos

2019 Monthly Rentals

July August September October November December Total
9 10 18 30 30 31 281
12 12 21 31 30 31 286
11 8 30 25 30 31 279
8 8 21 26 30 31 268
11 9 18 31 30 31 279

019 Revenue per Month

July August September October November December Total
R 16,020 R 17,800 R 32,040 R 61,620 R 61,620 R 63,674 R 548,952
18,900.00 18,900.00 33,075.00 61,566.00 59,580.00 61,566.00 524,841.00
17,325.00 12,600.00 47,250.00 53,075.00 63,690.00 65,813.00 535,325.00
13,696.00 13,696.00 35,952.00 55,198.00 63,690.00 65,813.00 531,563.00
23,353.00 19,107.00 38,214.00 78,554.00 76,020.00 78,554.00 667,119.00
R 89,294 R 82,103 R 186,531 R 310,013 R 324,600 R 335,420 R 2,807,800

ue
20 ;
%

2; 59,580.00 ;
18.4%

.6%
 Adela Mountain Resort
Pinehill Lodge

2019 Monthly Rentals

Room January February March April May June
Welkom 30 29 27 20 15 11
Richards Bay 30 29 28 14 10 9
Mahikeng 30 29 30 12 9 13
Polokwane 30 29 25 14 7 11
Hermanus 30 29 27 15 10 11

2019 Revenue per Month

Room January February March April May June
Welkom R 67,800 R 65,540 R 61,020 R 36,980 R 27,735 R 20,339
Richards Bay 71,910.00 69,513.00 67,116.00 25,886.00 18,490.00 16,641.00
Mahikeng 71,910.00 69,513.00 71,910.00 22,188.00 16,641.00 24,037.00
Polokwane 71,910.00 69,513.00 59,925.00 27,804.00 13,902.00 21,846.00
Hermanus 82,170.00 79,431.00 73,953.00 35,955.00 23,970.00 26,367.00
Total R 365,700 R 353,510 R 333,924 R 148,813 R 100,738 R 109,230
ela Mountain Resort
Pinehill Lodge

2019 Monthly Rentals

July August September October November December Total
10 9 18 31 30 31 261
7 7 19 25 30 31 239
9 9 28 24 30 31 254
11 11 16 26 30 31 241
10 10 16 29 30 31 248

019 Revenue per Month

July August September October November December Total
R 18,490 R 16,641 R 33,282 R 70,060 R 67,800 R 70,060 R 555,747
12,943.00 12,943.00 35,131.00 59,925.00 71,910.00 74,307.00 536,715.00
16,641.00 16,641.00 51,772.00 57,528.00 71,910.00 74,307.00 564,998.00
21,846.00 21,846.00 31,776.00 62,322.00 71,910.00 74,307.00 548,907.00
23,970.00 23,970.00 38,352.00 79,431.00 82,170.00 84,909.00 654,648.00
R 93,890 R 92,041 R 190,313 R 329,266 R 365,700 R 377,890 R 2,861,015
 Pinehill Lodge

2019 Monthly Rentals

Room January February March April May June

2019 Revenue per Month

Room January February March April May June
R - R - R - R - R - R -
- - - - - -
- - - - - -
- - - - - -
- - - - - -
Total R - R - R - R - R - R -
 Pinehill Lodge

2019 Monthly Rentals

July August September October November December Total
0
0
0
0
0

019 Revenue per Month

July August September October November December Total
R - R - R - R - R - R - R -
- - - - - - -
- - - - - - -
- - - - - - -
- - - - - - -
R - R - R - R - R - R - R -
 Monthly Revenue
R1,200,000

R1,000,000

January
February
R800,000 March
April
May
June
July
Revenue

R600,000
August
September
October
November
R400,000 December

R200,000

R-
January February March April May June July August September October November December
 Adela Mountain Resort
All Locations
1/21/2024

2019 Monthly Rentals

Room January February March April May June July August September October November December Total
1 88 86 86 73 63 49 48 47 66 90 88 90 874
2 88 86 84 62 53 45 42 43 68 81 88 90 830
3 88 86 89 58 51 53 46 47 86 79 88 90 861
4 88 86 82 62 47 49 50 51 62 84 88 90 839
5 88 86 85 64 53 50 46 49 62 88 88 90 849

2019 Monthly Revenue

Room January February March April May June July August September October November December Total
1 R 198,880 R 194,360 R 194,360 R 134,977 R 116,487 R 90,601 R 88,752 R 86,903 R 122,034 R 203,400 R 198,880 R 203,400 R 1,833,034
2 210,936 206,142 201,348 114,638 97,997 83,205 77,658 79,507 125,732 194,157 210,936 215,730 1,817,986
3 210,936 206,142 213,333 107,242 94,299 97,997 85,054 86,903 159,014 189,363 210,936 215,730 1,876,949
4 210,936 206,142 196,554 123,132 93,342 97,314 99,300 101,286 123,132 201,348 210,936 215,730 1,879,152
5 241,032 235,554 232,815 153,408 127,041 119,850 110,262 117,453 148,614 241,032 241,032 246,510 2,214,603
Total R 1,072,720 R 1,048,340 R 1,038,410 R 633,397 R 529,166 R 488,967 R 461,026 R 472,052 R 678,526 R 1,029,300 R 1,072,720 R 1,097,100 R 9,621,724
 Adela Mountain Resort
Cabin Rentals

Criteria Range
Cabin ID Last Name First Name Start Date Bedrooms Bathrooms Sleeps

Cabin Rentals
Cabin ID Last Name First Name Start Date Bedrooms Bathrooms Sleeps
103 Alkana Antonio 1/6/2019 2 2 6
104 April Lusapho 1/6/2019 3 2 6
110 Bruintjies Henricho 1/7/2019 2 2 4
112 Gelant Elroy 1/8/2019 2 2 6
115 Gongga Lungile 1/9/2019 2 1 4
120 Hamman Le Roux 1/10/2019 3 3 8
122 Hanekom Lindsay 1/11/2019 1 1 3
125 Leotlela Tlotliso 1/11/2019 1 1 2
130 Jobodwana Anaso 1/12/2019 3 3 10
131 Mokoka Stephen 1/12/2019 3 2 8

Extract Area
Cabin ID Last Name First Name Start Date Bedrooms Bathrooms Sleeps
ountain Resort
abin Rentals

Kitchen Mountain View Weekly Rate

Rental Lookup
Kitchen Mountain View Weekly Rate Cabin ID 125
YES NO 27475 Renter Leotlela
YES YES 31596 Weeks Rented 2
NO YES 24728 Revenue R 40,526
YES NO 23354
YES YES 24041
YES YES 38465
NO YES 20606
YES NO 20263
YES NO 34344
YES YES 34344

Kitchen Mountain View Weekly Rate