Two New Excel Functions

Microsoft has just announced two new Excel functions to be included in the Microsoft Office 2007 suite, Service Pack 2 (SP2) expected to be released 04/01/2009. I'm sure they will prove to be indispensable tools to accountants, particularly those involved in budgeting, financial reporting or financial modeling for charities and public corporations.

In these trying economic times, closing the gap between revenues and expenses can be challenging to say the least. Also, with consumer confidence ebbing and the price of overseas parts and raw materials mounting, it is increasingly difficult for companies to maintain their profit margins. That's where the first function, HOPE comes in. It will calculate the gap or margin that you will hopefully fill when your forecasted transactions are realized. Here's an excerpt from the documentation:

__________________________________________

HOPE

HOPE returns a specific dollar amount or percentage, based on the total revenue and total expense/cost cells you specify.

Syntax

HOPE(result,tot_revenue,tot_cost)

Result is either "%" for a margin calculation or "$" for a dollar amount.

Tot_revenue is the total revenue amount or the location of the cell containing the revenue amount.

Tot_cost is the total expense or cost amount or the location of the cell containing the cost amount.

Remarks

• If tot_revenue is greater than tot_cost, HOPE is unnecessary.
• If the margin or dollar amount calculation is too high, HOPE returns the #VALUE! error value.

__________________________________________

I should comment that in testing the beta version of this function I came across the #VALUE! error and discussed it with one of the developers. He said that the function is based on econometrics. There is a certain point where the gap between revenues and expenses/costs is so great that the value of HOPE required to balance the equation approaches infinity (i.e. an undefined value). In order to prevent the function from going into an infinite loop, it aborts and displays the error message.

To overcome this issue, Microsoft has introduced a second function, but you should employ it cautiously as it uses an enormous amount of system resources. The syntax is similar to HOPE, but the function is called PRAY.