2018 S01 P02 EXCEL

Refer to the detailed spreadsheet for the rest of the question.

A family member (Sow-so) has approached you to co-establish a property leasing company (a property leasing company that buys buildings then rents them out to clients to make rental income). You both agreed to name the company “You & /” Properties Lid (U&I) and registered the company as a VAT vendor. U&I rents outbuildings containing office space (commercial properties).
Sow-so is aware that you have gained extensive experience in Microsoft excel and has asked for your assistance in calculating the monthly rental income amount.

You have the following information:

a) The Value Added Tax (VAT) percentage is 14% (refer to cell B3 in the detailed spreadsheet provided on the previous page) Commercial rental income is subject to VAT.

b) The property code contains the sequence number, the area code, and the square meter’s as follows

1 2 3 4 5 6 7
0 1 X X X 0 0

Characters 1 and 2 01 (Sequence number)
Characters 3 and 4 XX (Area code)
Characters 5 and 6 300 (The square meters)

c) Property Loans were given on full property costs (refer to row 14). Ali monthly property loan repayments (refer to row 18) are paid at the beginning of each month and each property has its own specific annual interest rate (refer to row 16) and term in months (refer to row 17). All property loans will be fully repaid by the end of the loan term.

d) Monthly property tax is based on the cost of the property ie. property with a cost of R 520 000 will attract a monthly property tax of R580 (refer to range D3:F9).
e) Each rental contract has a unique contract number. The rental contract number contains» die property code, a hyphen and the rental agreement term as follows:

1 2 3 4 5 6 7 8 9 10
0 1 X X 3 0 0 3 6

Characters 1 to 7 01XX300 (property code)
Characters – (Hyphen)
Characters 9 to 10 38 (The rental agreement term in months)

f) The base rental cost (’cost price’) is the sum of the monthly property loan re-payment and the monthly property tax including VAT. The monthly rental income amount is U&l’s selling price.

g) Properties with a one (1) and two (2) year rental agreement terms have a mark-up margin of 23% on base rental cost (refer to cell B4 on the detailed spreadsheet provided). Properties with a three (3) year rental agreement term have a mark-up margin of 18% on base rental cost (refer to cell B5 on the detailed spreadsheet provided).

h) U&l’s plan to renovate and improve the condition of all five properties/buildings. Due to the renovations, U&l will now be able to charge a higher rental amount for the properties. The monthly rental amount will be increased by 7% (refer to cell B6)

Please login to get access to this quiz