formula for calculating budget variances

[anaguiu]

I am trying to develop a formula that will calculate favorable /(unfavorable) variances between actual and budget, but both my income and expense amounts are positive. The following formula works for income items, but expenses should show unfavorable variances and they come out positive.

=IF(AND(B3>0,C3>0),B3-C3,(IF(AND(B3<0,C3<0),B3-C3,(IF(OR(B3>0,C3>0),C3-B3)))))

column B is actual and column C is budget

I know I could modify the formulas for each line item, but I'm trying to use the same formula that will work for all line items regardless of whether they are income or expense.

Anyone have any ideas? Is there possibly VBA code that will do this?

I wish Excel had built in Accounting functions, but it only has Financial functions ... Here's a clue Mr. Gates, accountants use Excel too!

Thanks in advance for your help.

 

[RalphA]

If you but both income and expenses in the same column, and both are positive, then you must provide Excel a means of recognizing which is which. How do you do that? Note that if expenses were in a Debit column, and income in a Credit column, as I thought was the norm for accountants, that would be the way to solve your problem!

 

[anaguiu]

well ... my spreadsheet is not in ledger format...it's in P&L Statement format, so no...the numbers are not in debit and credit columns. But it would be nice if you could somehow tag certain line items in the spreadsheet as expense items and then have a function that would recognize that and calculate the correct variance.

 

[RalphA]

Sure, but not without knowing how that "tagging" is done. I am sending you a PM.

 

[Smitty]

Can you post an example?

Smitty

 

[gwkenny]

anaguiu:

Without some additional information, you cannot use the same formula for income and expenses because the computer will not know how to differentiate between the two.

What you have to do is insert another column. Identify each line item as an Income or Expense. So in this column you could put an "I" or an "E".

In the column that you determine the differential between actual and budget, you can then put in your formula to test if this is an I or an E and multiply the answer by 1 or -1 accordingly.

That'll get you the correct sign on your differential and then you can sum that up to get your overall net income differential.

 

[richarddec]

a little late from the post date, but I used this formula with my i/e identifier in column A, Actuals in Column B, and Budget in Column C:


=IF(AND($A1="I",$C1>$B1),($B1-$C1),IF(AND($A1="I",$C1<$B1),($B1-$C1),IF(AND($A1="E",$C1>$B1),($B1-$C1)*(-1),IF(AND($A1="E",$C1<$B1),$C1-$B1,IF(ROUND($C1,2)=ROUND($B1,2),"0","")))))

Hopefully this helps future users.