2015 S02 P02 EXCEL


PPRE is a Limpopo-based real estate agency that specialises in selling both residential and commercial properties on behalf of property owners. There are two types of residential properties i.e. Affordable House-Properties (AH) and Up-Market Properties (UP). The agency prides itself on a number of highly motivated, passionate, and award-winning agents driving its operations.
PPRE was recently identified for a VAT audit by the Revenue Authorities (RA). Immediately after the audit, Thabo Moeng (the Managing Director) showed one of his senior estate agents (Ms. Sam Greed) the door following the below audit findings from the RA:

Ms. Sam Greed only represents non-VAT vendors and should, therefore, not charge VAT on any of her property deals

Ms. Sam Greed’s property portfolio only attracts transfer duty

Transfer duty was correctly calculated and paid to the RA on all of Ms. Sam Greed’s previous deals
The “Transfer duty table” as reflected in Ms. Sam Greed’s property portfolio is valid and accurate
Ms. Sam Greed has been unlawfully charging VAT on all of her property deals and secretly transferring the collected funds to her partner’s personal account

Thabo conceded that as a “star performer”, Sam was allowed to perform her duties with minimum assistance from him. Sam’s unforeseen departure left him in the doldrums and his immediate challenge is to make sense of Sam’s property portfolio (see next page) and start managing the portfolio himself. Knowing and trusting your Excel skills, Thabo needs your help in understanding Sam’s portfolio.


Thabo provided you with the following additional information:

a) Your advice should take into account the audit findings identified by Revenue Authorities.
b) All amounts on the spreadsheet for Sam’s Property portfolio were formatted to display zero decimal digits and all percentages to display two decimal digits.
c) Where applicable the Value Added Tax (VAT) rate is 14% (refer to cell B2).
d) The markup margin is 27,5% (refer to cell B4).
e) As per the Deposit percentage table, all property buyers are required to pay a non-negotiable cash deposit (refer to point n) per below:
• 5% of selling price excluding VAT for all affordable houses (AH) (refer to cell C10), or
• 15% of selling price excluding VAT for all up-market properties (UP) (refer to cell C11).
f) Properties in Sam’s portfolio are situated country-wide and across different provinces. The location table, starting in cell D2, indicates the province code and the corresponding province name.
g) The transfer duty table, starting in cell A13, indicates the rates used to calculate transfer duties (refer to point o). Transfer duties are based on the property’s selling price excluding VAT. For example, a property with a selling price of R600 000 excluding VAT will attract transfer duty at a rate of 7,5% as it falls within the R500 0001 to R1 500 000 bracket.


h) The property code is a combination of alpha-numeric characters making up a unique identifying code for each property in the portfolio (refer to row 21).
The property code structure is as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
U P - E R F 0 0 1 6 - 1 2 0 0 G P
Property Type Stand Number Property size in square metres (m2) Province Code

Characters 1-2: Residential property type (also refer to point k) below)
Character 3: - (hyphen/dash)
Characters 4-10: Stand number of the property
Character 11: - (hyphen/dash)
Characters 12-15: Property size of the property in square meters (m2)
Characters 16-17: Province code of the property (also refer to point f) above)

i) The property size in square meters (m2) (refer to row 24 and point h).
j) “Prop-Man” code (refer to row 25) is a unique code used in PPRE’s accounting package to track and identify properties within the accounting records.

The code is structured as follows:

1 2 3 4 5 6 7
G P - 1 2 0 0
Province code Property size in square metres (m2)

Characters 1-2 : Province code
Character 3: - (hyphen/dash)
Characters 4-7 : Property size in square meters (m2)

k) The property type code (refer to row 26) indicates if a property is either an affordable house (AH) or an up-market property (UP).


l) The property selling price including VAT (refer to row 28).
m) The property selling price excluding VAT (refer to row 29).
n) The deposit amount (refer to row 30) is calculated using the applicable rate (refer to point e) above).
o) The transfer duty rate (refer to row 31) is retrieved from the transfer duty table (refer to point g) above).
p) The transfer duty amount (refer to row 32) is calculated as the transfer duty rate multiplied by the selling price excluding VAT.
q) The financed amount excluding VAT (refer to row 33) is calculated by deducting the deposit amount from the selling price (excluding VAT) and then adding the transfer duty amount.
r) The agency cost excluding VAT (refer to row 34) relates to the cost incurred by PPRE in executing the property sale on behalf of the seller.
s) The agency income excluding VAT (refer to row 35) is PPRE’s income earned based on the agency cost (refer to point r) above) and the markup-margin (refer to point d) and cell B4).


t) A mortgage bond will be registered at a value equal to the “Financed amount excluding VAT” of the property being financed.
u) Monthly installment (refer to row 39) refers to the monthly installment payable on the mortgage bond raised, taking into account the following terms:
• Financed amount excluding VAT (refer to row 33 and point q) above).
• Finance term in years applicable to the specific property (refer to row 37).
• The annual interest rate applicable to the specific property (refer to row 38).
• All installments are paid at the end of each month.
• At the end of the finance term, all mortgage bonds will be fully repaid.


v) Price per square meter excluding VAT (refer to row 41) is calculated as selling price excluding VAT dividend by property size in square meters (m2) (refer to point t) above).
w) The lowest price per square meter excluding VAT (refer to cell B42) is based on the price per square meter excluding VAT for all the properties in Sam’s portfolio.
x) Average finance term (years) (refer to cell B43) is based on finance terms in years for all the properties in Sam’s portfolio.

Please login to get access to this quiz