Advanced financial modelling course: max & min functions

MAX and MIN functions can be used as a neater and shorter alternative to Excel's "IF" function.  This example shows how MAX and MIN functions can be used in financial modelling as a business moves from positive to negative cash balances and back again.

The financial modelling problem

In a financial model, if the business concerned is stressed, and as cash flows become negative, cash balances dwindle.  At some point the business will need to draw on a short term overdraft facility or revolving credit facility (RCF or "revolver").  In the model, as the business's fortunes revive and cash flows become positive again, the company will reduce its overdraft/ revolver until cash balances become positive.  The problem is how to adequately model the swing between cash and overdraft/ RCF.  In the stress case in a financial model, as the cash balance reduces below zero, we don't want negative cash appearing under current assets on the balance sheet.  That wouldn't make sense.  As the cash balance reduces below zero, what we want is a new liability item appearing on the balance sheet, reflecting a draw down on the overdraft/ RCF.

Using the IF function

Excel's IF function could adequately solve this problem for us.  IF the cash balance is positive, we want to show a positive cash balance on the cash balance sheet.  However, IF the cash balance has become negative, we want to show a new liability item on the balance sheet under "overdraft" or "RCF".

Advanced financial modelling with Excel's MAX and MIN functions

Advanced financial modellers know that the MAX and MIN functions can sometimes result in a shorter, neater solution to a problem like the one described above.  The MAX function examines a series of numbers and returns the largest in the series.  The MIN function returns the smallest in a series.  Please download this simple Excel spreadsheet advanced financial modelling course max and min functions, which demonstrates how these functions have been used to solve the problem outlined above.

Advanced financial modelling course tips

If you can solve a financial modelling problem using shorter rather than a longer functions, a new user will find it easier to discover how a model works.  Shorter, neater functions make it easier for a new user to follow links through a model.  Short functions make a model easier to check and therefore less prone to error.  Expert modellers enjoy using MAX and MIN functions because they can sometimes solve a financial modelling problem more directly than Excel's standard IF function.

Return to the advanced financial modelling training course extract

More advanced modelling training course material is available.  If you would like more information regarding our taught program, please see the full outline for our advanced modelling course.