469,645 Members | 1,984 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

Help with formula

I need some help on computing this formula especially the best way to capture the data that is whether to use excel or ms access, then with the data compute the formula. The excercise is for bonus scheme calculation which is depended on various performance indicators.

Global Incentive (GI)
General Formula

GI = {(By-1) * (P/N)} + {X * (COMa-COMm) * (WRpa+UFWpa+CEpa+MRpa+TApa)

GI = Global Incentive that relate to the entire company
By-1 = The base given by the average bonus for the previous year
P/N = Aggregate performance on the various performance indicators. The performance is discrete, taking 1 if the actual performance has been achieved relative to the minimum performance standard or zero otherwise
X = Maximum incentive fee payable in a given month and is a certain percentage (e.g 30%) of the difference between the actual and the minimum cash operating margin
COMa and COMm = Respectively, the actual and minimum cash operating margin given by the difference between revenue collections (excluding deposits and grants) and operations expenditure on accrual basis
WR = Working ratio given as % of total operating expenses as a proportion of actual billing
UFW = Unaccounted for water given as the difference between water produced and water accounted (either sold or otherwise)
CE = Connection efficiency given as a ratio of active connections as a proportion of total connections
MR = Meter reading given as a ratio of meters read as a proportion of total connections
TA = Total Arrears
Pa = percentage increamental achievement given as

Pa = Ia –Im/It-Im

Where

Im = is the indicator’s minimum performance standard given as the average performance for the previous year
It =is the indicator’s target performance for a given month
Ia = is the indicator’s actual performance for a given month

Thank you
Patrick
Sep 21 '08 #1
2 1398
youmike
69
I think that as a general rule, the most flexible solution in this sort of situation is to make sure that the spreadsheet contains normalized data without calculations and that all calculations are done in Access, using VBA, with results output to a table which you can then export to a separate spreadsheet. What this does is to encourage you to normalize the input data, which you will find a boon subsequently.
Sep 22 '08 #2
Thats a good suggestion. Will try it out.
Thanks
Sep 22 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Josh | last post: by
2 posts views Thread by alex | last post: by
4 posts views Thread by Tony | last post: by
reply views Thread by =?Utf-8?B?TGV0emRvXzF0?= | last post: by
2 posts views Thread by dreamer1963 | last post: by
3 posts views Thread by alaurent71 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.