You are the financial manager for Excel-Lent Fitness Centre Ltd (EFC), a registered VAT vendor.
EFC has an agreement with Ubuhle Medical Aid Ltd whereby EFC provides a discount to all Gold and Platinum Ubuhle members. In return Ubuhle will invest 40% of the yearly loss in fitness membership fees on EFC’s behalf as Ubuhle can invest money at an interest rate much higher than that of EFC’s current investment return rate.
This agreement has been in place for 5 months EFC currently has 35 454 members of which 3 215 are Ubuhle members on the Gold and Platinum levels.
Due to your extensive Excel skills, the CFO has requested you to prepare a projection of the estimated loss in membership fees for a year as well as the projected value of the amount to be invested on EFC’s behalf at the end of the three-year term.
You created the following spreadsheet:
(BEVS HINT: PLEASE TAKE NOTE, IF YOU ARE USING AN ACTUAL PHOTOCOPY OF THE EXAM, THEN THIS SPREADSHEET HAD SOME ERRORS ON IT)
THE AMOUNTS PUT IN BY THE UNISA LECTURER FOR COLUMN G – Discounted monthly fee Incl VAT – WERE IN FACT INCORRECT.
THEREFORE THE TOTAL OF THE COLUMN WAS ALSO INCORRECT, IT IS R1593.90, BUT FOR THE SAKE OF THE FOLLOWING FORMULAS I HAVE PUT IN THEIR INCORRECT TOTALLING FIGURE OF R1643.40.
OPEN PDF HERE
You have the following information:
a) The Value Added Tax (VAT) percentage is 15% (refer to cell C4)
b) Ubuhle only provided you with their Gold and Platinum members’ data for the Zola Island region who are also members of EFC.
c) Ubuhle’s Gold and Platinum members qualify for membership fee discounts as follows:
- Gold (GO) members receive 12% discount on the normal membership fee (refer to cell G5)
- Platinum (PL) members receive 22% discount on the normal membership fee (refer to cell G6)
d) The Ubuhle membership number contains a member’s town of residence and membership level and the membership number structure is as follows:
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
|---|---|---|---|---|---|---|---|
| X | X | 1 | 1 | 1 | 1 | Y | Y |
Characters 1-2 XX The membership level (GO = Gold and PL = Platinum)
Characters 3-6 1111 (First four digits of the member’s Identity number)
Characters 7-8 YY (A short code which identifies the member’s town of residence)
e) The normal monthly membership fee is R330 including VAT.
f) The loss in membership fees is the difference between the original monthly membership fee and the discounted monthly membership fee.
g) The financial year starts on the 1st of January of each year.
h) The first five (5) months’ actual loss in membership fees excluding VAT for Zola Island was R1 358 (refer to cell F29)
i) You were instructed to assume that for the remaining part of the year no new Ubuhle members will take up membership with EFC Each remaining months loss in membership fees excluding VAT can therefore be assumed to be equal to May’s loss in membership fees excluding VAT.
j) Ubuhle will invest 40% of EFC’s yearly loss in membership fees excluding VAT at the end of each year.
k) The investment as referred to in j) will be invested for a period of 3 years (refer to cell C5) and will earn interest at a yearly interest rate of 17% (refer to cell C6) Interest is compounded monthly at the end of each month.
Use the provided information and answer the following questions
Note: Where it is indicated that your formula will be copied to other cells, your formula must take absolute and relative addresses Into account, but only where necessary!