Question regarding variance for dollars and percentage

[bthumble]

I am calculating the variance between two month in dollars (July - June) and percentage (July - June)/June. The problem I have encountered is that sometimes the variance in dollars is positive and the variance in percent is negative.

Example

June (1,321.66)
July (43.67)
Variance in dollars = 1,277.99
Variance in Percent = -97%

My boss wants the percentage variance to be the same sign as the dollar variance.

Any suggestions?

Thanks

Jerry

 

[wigi]

For instance:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">-1321,66</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">-43,67</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1277,99</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">97%</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A3</th><td style="text-align:left">=A2-A1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A4</th><td style="text-align:left">=A3/A1*IF(<font color="Blue">A1>A2,1,-1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[xenou]

June (1,321.66)
July (43.67)
Variance in dollars = 1,277.99
Variance in Percent = -97%

What do these negative numbers mean to you? -43.67 is in relative terms a positive change from -1,321.66 (although neither are positive numbers). Why do you consider this a negative change (-97%)?

I didn't sleep enough last night and my question may be the thing that doesn't make sense, I figure I'd throw it out ... unless Wigi's solution is what you needed, since indeed it is in the same sign as the dollar variance).

 

[bthumble]

The June and July numbers represent expenses. Expenses are normally shown as negative, because it is a reduction to income.

wigi - your formula works except if both numbers are positive. For example if June is 1912 and july is 2203, the variance is 292 and 15%. Using your formula above changes it to -15% and causing another difference in signs.

Thanks

 

[xenou]

Percent change is:
[New - Old] / Old

This should work in all cases, as shown:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">a</td><td style="text-align: right;;">b</td><td style="text-align: right;;">c</td><td style="text-align: right;;">d</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">June</td><td style="text-align: right;;">-12</td><td style="text-align: right;;">-8</td><td style="text-align: right;;">12</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">July</td><td style="text-align: right;;">-8</td><td style="text-align: right;;">-12</td><td style="text-align: right;;">8</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Change</td><td style="text-align: right;;">4</td><td style="text-align: right;;">-4</td><td style="text-align: right;;">-4</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">-33.33%</td><td style="text-align: right;;">50.00%</td><td style="text-align: right;;">-33.33%</td><td style="text-align: right;;">50.00%</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">a:</td><td style=";">July expenses are less than June (33.33% decrease in expenses)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">b:</td><td style=";">July expenses are more than June (50% increase in expenses)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">c:</td><td style=";">July expenses (credit) is less than June (33.33% less credit)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">d:</td><td style=";">July expenses (credit) is more than June (50% more credit)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=B4-B3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=C4-C3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=D4-D3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=E4-E3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=(<font color="Blue">B4-B3</font>)/B3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=(<font color="Blue">C4-C3</font>)/C3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=(<font color="Blue">D4-D3</font>)/D3</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E6</th><td style="text-align:left">=(<font color="Blue">E4-E3</font>)/E3</td></tr></tbody></table></td></tr></table><br />

Why do bosses have trouble with data?

 

[wigi]

For instance:

=A3/A1*IF(SIGN(A3)=SIGN(A3/A1),1,-1)

 

[xenou]

I think you're doomed because if the numbers are negative a negative dollar changes in an increase in expense and a positive change (expenses up). If the numbers are positive a negative dollar change is also an increase in expense (expenses up, in the form of less credit to expenses).

So both negative and positive numbers can mean the same change in expense relative to the prior month: a positive increase in the amount of expense.

I think you have to let the percentages be what they are. A positive % is an increase in expenses, and a negative percent is a decrease in expenses. This has nothing to do with whether the dollars are positive or negative as dollar totals, because it's not measuring the same thing.

Your boss may be incapable of understanding this. you can always coerce the percent sign to be whatever the dollar sign is. But that reduces the meaning of the change as a ratio to basically garbage.

Though as I said - I'm too tired today. Think it through and don't take my word for it. I think it's useful to construct a small table and test the various outcomes. Not hard.