2014 S01 P02 EXCEL

You and your friend are the shareholders of GerKu Properties Ltd (GerKu), a registered VAT vendor.
GerKu rents outbuildings containing office space (commercial properties).

Due to your extensive Excel skills, your friend asked you to calculate the total rental amount and the new monthly rental amount based on an 8% increase, for each property. You also want the change the current property code by including the square meters of each rental property in the property code.

You have the following information:

a) The Value Added Tax (VAT) percentage is 14% (refer to cell B3) Commercial rental income is subject to VAT.
b) The base rental amount ("cost price") is the sum of the monthly payment and the monthly property tax. The total rental amount is GerKu's selling price.
c) Properties with a 1 and 2-year rental agreement term have a mark-up margin of 20% (refer to cell B4) Properties with a 3-year rental agreement term has a mark-up margin of 15% (refer to cell B5).
d) The new rental amount assumes an 8% (refer to cell B6) increase in the total rental amount.
e) The current property code contains the rental agreement term, the area code, and a sequence number.

The current property code structure is as follows :

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

Characters 1 and 2 24 (The rental agreement term in months)
Characters 3 and 4 XX (Area Code)
Characters 5 and 6 01 (Sequence number)

f) The new property code should contain the rental agreement term, the area code, sequence number, a hyphen and the square meters (refer to row 13)

QUESTION 6 (Continued)

The new property code structure is as follows :

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

Characters 1
Characters 3 and 4 XX (Area code)
Characters 5 and 6 01 (Sequence number)
Character 7 - (Hyphen)
Characters 8 to 10 999 (Square meters)

g) Property tax is based on the value of the property ie. property with a value of R 710 000 will attract property tax of R 690 (Provided in the range D3:F9)

h) For each property, the property value is equal to the current value of the property's finance loan. All finance loans monthly payments (row 14) are made at the beginning of each month and each property has its own specific annual interest rate (row 15) and terms in months (row 16). All finance loans are fully repayable


Please login to get access to this quiz