Microsoft Excel - Stock Sheet - Asked By Wil Theron on 05-Mar-12 09:44 AM

Hello again,

Wally can possible answer my question.  Same Stock Sheet but I have simplified it a bit. My predicament is as follows:
In this company employees receive a yearly allowance for corporate clothing, however not all employees receive the same amount.  I need to include in the spreadsheet (maybe on the employee sheet a column that will automatically pull through the correct amount).  However, the company have 4 divisions being Africa, Products, Acuity and Holdings.
  • all employees receive the same amount if they purchase cycling clothes i.e. R250
  • Products, Acuity and Holdings employees receive R680 yearly
  • Africa employees who are situated at the client premises receive a once off R500 amount and then a yearly amount of R500.  These employees have a Cost Centre Reference that starts with @client (Employee Sheet, Column D)
  • Africa employees who works at Head Office also receive R680.
The above information should ideally !!somewhere!! show how much money an employee have available, if he used up the company contribution etc. which would then influence the price that he pays for an item.

Thank you for your time

WillemienTransaction Record - March 2012A.zip


wally eye replied to Wil Theron on 05-Mar-12 11:38 AM
I would start off by creating a cross-reference table, either from Cost Centre or Office Location (or both) to a division and the initial/annual allowance for the division.  I tried to do that myself, but I could only recognize JHB and CPT as African cities, I expect there are more.

Cost Centre
@Client
@Client - Manuf/Util
@Client - Mining
@Client - Shell Commercial
@Client - Shell Retail
@Pragma
100 - Corporate Services
110 - Human Resources
120 - Marketing and Communications
130 - HSSE and Office Management 
140 - ICT
150 - Finance
200 - Products Management
210 - Products SW Development
220 - Products ACC Pack Development
230 - Products Partner Support
240 - Products ACC Tools
300 - Acuity Management
310 - Acuity Consulting
320 - Acuity Operations
400 - Africa Management
410 - Business Development 
420 - Academy
440 - National Projects
450 - Cape Region
451 - Northern Region
452 - KZN Region
453 - Eastern Cape Region
Office Management - CPT
Office Management - JHB
Office Management - KZN

Once the cross-reference table is set up, probably on the Lookups sheet, then it should be fairly easy to determine in your Employee Sheet the Amount Available for each employee. 

It might be difficult to determine the difference between your one-time allowance and annual allowance, are you planning on keeping all transactions in Transaction List, or will some age out?  Also, because it is an annual allowance, you will need a field to either track the period or date of the transaction.  You have the Month field, that might be what you need.

Cycling clothes, is that R250 annually as well?  If an employee purchase both cycling and non-cycling, would their total allowance be R680 or R930?

This seems like it might be getting a bit complicated, perhaps a database might be a better fit...
Wil Theron replied to wally eye on 05-Mar-12 03:02 PM
Hello Wally,

Thanks you for the reply.  Long day today and only checked my mail now.  Will try this out tomorrow and give you some feedback.

Willemien