hi driller
the formulas a
G5 is =B5-F5 or "Budget"-"Actual"
H5 is =min(200%,G5/E5) or "Diff Budget:Actual"/Savings Target"
Hope this helps and again THANK you for trying to help.

have a great day
gma

"driller" wrote:

gma,

Also pls. include again your new draft formula for G5 & H5.
B5 C5 D5 E5 F5 G5 H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 105 5.67 113%
100 5.00% 95 5 90 4.73 95%

without a draft formula to support your input on columns G & H, it will be
very hard for both of us to guess the relative formulas along every
additional lines.

you're welcome, hop this will help and have a great day too.
+regards+

"gma" wrote:

hi driller
i've inputted the number as you requested below plus i added additional
lines showing if the actual number drops below 90. i dont know what the
formulas are suppose to be in G5/H5, that my problem. those are the two
formulas that will calculate the numbers i'm trying to achieve.

Thanks for taking the time to help. Hope this information helps. Have a
great day.
gma


"driller" wrote:

gma,

others. as the actual number G5 <94 falls below the goal <95 i want the dif
budget/act number to have a minus sign, thus making the gain/loss to also
have a minus sign.

it will help if you place your new desired results on few "?" below where
the supposed *minus sign* on H5 is applicable. Also pls. include again your
new draft formula for G5 & H5.

B5 C5 D5 E5 F5 G5 H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 95 5 100%
100 5.00% 95 5 105 5.67 113%
100 5.00% 95 5 90 4.73 95%
100 5.00% 95 5 100 5.37 107%
100 5.00% 95 5 94 4.97 99%
100 5.00% 95 5 89 -1 -20%
100 5.00% 95 5 88 -2 -40%




hop this will help,
-regards-


"gma" wrote:

hi driller
i am satisfied except i want this line of the budget to be oppsite of the
others. as the actual number G5 <94 falls below the goal <95 i want the dif
budget/act number to have a minus sign, thus making the gain/loss to also
have a minus sign.
subsequently if the actual number is above the goal then the oppsite happens
the number increase. again the formulas are for the goal/s to go down. i need
this line to increse the numbers. thanks for being patient with me. i'm not
very good with excel.
gma


"driller" wrote:

gma,

it seems like your already satisfied with your enumerated formulas,
especially along the G5 & H5, based from the sample.
B5 C5 D5 E5 F5 G5 H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 103 13 130%

Or are there anything more you need to explain furthermore ?
Hop this may help.

regards,
"gma" wrote:

driller
hey its not your fault...i'm not explaining very well. the formula is set up
like this.
"budget-B5" is a fixed number <"100" i come up with from yearly budget.
"target savings %-C5" <5%is a fixed number also, and it represents further
savings of the budget and becomes the "goal-D5" or "95". The formula in
D5-goal is =abs((B5*C5)-B5) the "savings target-E5" formula is simply =B5-D5
or "budget-goal". The "diff budget/act-G5" formula is =B5-F5. As long as this
number equals the "savings target" number then you've met your "goal" and the
"gain or -loss" number is 100%. The "gain or -loss-H5" formula is
=min(200%,G5/E5). I have the min set because i have capped the gain. All the
other lines I have for this bonus works fine because the lower the number in
the "actual" the "gain or -loss" increases which is what i want. This
particular line, as the "actual" number goes up i need the "gain or -loss" to
go up or as it goes down the "gain or -loss" goes down. You asked if the
"-loss%" represents a negative number, it can. If the "actual" number falls
below the "budget" number then it put the "gain or -loss" to a negative
number. This is why i put in this formula a cap at 200% because the "actual"
could make the "gain" number go indefinite. Like its hard to explain, i'm
putting the formulas below. maybe if you put them in a spreadsheet you can
see what i'm tring to do.
CELL B5-BUDGET Fixed number "100"
CELL C5-TARGET SAVINGS % Fixed number "5%"
CELL D5-GOAL =ABS((b5*c5)-b5)
CELL E5- SAVINGS TARGET =B5-D5
CELL F5- ACTUAL Data input from P & L for the quarter
CELL G5- DIFF BUDGET/ACT =B5-F5
CELL H5F- GAIN OR -LOSS =MIN(200%,G5/E5)

Lots of stuff but i hope this helps. I REALLY appreciate you helping me.
thanks
gma

"driller" wrote:

gma....sorry for not understanding your point very well, your bonuses plan
have in H5 "gain or-Loss" column, does the "-Loss" means a negative % ? How
to know its a -Loss ?

For G5 : is there a proportionating formula or this is a "series of count
+/-" from *5* ?

lastly please fill in your value at the extended last 2 rows in the table
with "?" below

B5 C5 D5 E5 F5 G5 H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 95 *5* 100%
100 5.00% 95 5 98 6 120%
100 5.00% 95 5 93 4 80%
100 5.00% 95 5 105 7 140%
100 5.00% 95 5 103 13 130%
100 5.00% 95 5 94 4 82%

regards


"gma" wrote:

driller
as you can see any thing less than "95", decreases the "budget/act" and the
"gain/loss", but or the other hand any thing more than "95" will increase
those same numbers. i know i'm not explaining myself very well but this
spreadsheet is hard to explain. its a bonus plan for my managers. i wish i
could just email you a copy of the forumlas and you could see better what i'm
trying to do, or maybe what your asking below will help. hope this helps

"driller" wrote:

Hi gma,

pls fill values on "?" as sample explanation for a quicker approach.

B5 C5 D5 E5 F5 G5
H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 95 5 100%
100 5.00% 95 5 98 6 120%
100 5.00% 95 5 93 4 80%
100 5.00% 95 5 105 7 140%

regards

"gma" wrote:

Hi driller
i'm not explaining myself very well.
B5 C5 D5 E5 F5 G5
H5
Budget %savings Goal Target Actual Budget/Act Gain or -Loss
100 5.00% 95 5 95 5 100%
the way this works is: there is a budget number,B5 100, thats fixed or a
number we have set as the yearly budget ,and a goal
number,D5=abs((b5*c5)-b5), thats driven from the "% savings" C5 5%. the %
savings number is also fixed that we have set as a target savings of the
budget or in other words, we have a budget number but i'm asking that we also
try and beat the budget number by 5%, thus the target E5 =B5-D5 becomes the
number 5. the actual number is from the average of the quarter say 95. as the
actual number goes up i need G5 to increase or if the actual number decreases
i need G5 to decrease. Again not sure if i'm explaining myself very well.





"driller" wrote:

gma,

interpreting A1 as the index for your target f1, try with the following +/-
formula as fallows;

d1 = "input as-actual value"

Assumed as a respective input,
"When d1 is changed by either a smaller or larger value then i need e1 to
increase or decrease".
thus,
e1= (b1-d1)+c1
and where c1 is the goal for savings
f1=e1/c1 *100

so if actual/d1 = 98 ; then diff/e1 = 2 and gain/loss = 40.


--
regards,
driller

"gma" wrote:

i need to increase or decrease a cell. A1 is the consent value or base. B1 is
the goal which is a smaller value and d1 is the actual value. When d1 is
changed by either a smaller or larger value then i need e1 to increase or
decrease.
base goal savings actual diff gain/loss
A1 b1 c1 d1 e1 f1
100 95 5 95 5 100
fixed =a1-b1 moving =d1/a1 =e1/c1
if i change actual/d1 to say 98 i need the diff/e1 to increase or if i
change it to say 93 i need it to decrease. help!!