Microsoft Excel - Stock Sheet

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.

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...

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