2013 S02 P02 EXCEL

You are the financial manager for Fit & Healthy Gym Ltd (FHG), a registered VAT vendor. FHG has an agreement with Wecare Medical Aid Ltd whereby FHG gives a discount to the top two levels of Wecare members. In return, Wecare will invest 65% of the yearly loss in gym membership fees on FHG’s behalf as Wecare can invest money at a rate far superior to that of FHG’s current investment return rate.

This agreement has been in place for 4 months. FHG currently has 24 515 members of which 2 412 are Wecare 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 invested on FHG’s behalf at the end of the three-year term.

You wanted to ensure your projection spreadsheet is correct before you apply it to the whole data set. You have, therefore, created a draft spreadsheet with data from only one town in the Karoo.

You have the following information:

a) The Value Added Tax (VAT) percentage is 14% (refer to cell X4)

b) Wecare only provided you with their Gold and Platinum members’ data who are also members of FHG.

c) Wecare’s Gold and Platinum members qualify for membership fee discounts as follows:

- Gold (GO) members receive 15% discount on the normal membership fee (refer to cell C5)
- Platinum (PL) members receive 25% discount on the normal membership fee (refer to cell C6)

d) The Wecare membership number contains a member’s town of residence and membership level.

The Wecare membership number structure is as follows:

1 2 3 4 5 6 7 8
Y Y 1 1 1 1 X X

Characters 1-2 : YY (A short code which identifies the member’s town of residence)
Characters 3-6 : 1111 (Random letters)
Characters 7-8 : The membership level (GO = Gold and PL = Platinum)

e) The normal monthly membership fee is R150 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 four (4) months’ actual loss in membership fees excluding VAT for Fraserburg was R870 (refer to cell F29).

i) You were instructed to assume that for the remaining part of the year, no new Wecare members will take up membership with FHG. Each remaining months’ loss in membership fees excluding VAT can, therefore, be assumed to be equal to April’s loss in membership fees excluding VAT.

j) Wecare will invest 65% of FHG’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 G4) and will earn interest at a yearly interest rate of 16% (refer to cell G5). Interest is compounded monthly at the end of each month.

You created the spreadsheet :

OPEN PDF HERE

Please login to get access to this quiz
s2Member®