Max and Min formulas

When Excel modelling, Max and Min functions (as well as TRUE/ FALSE checks) can be used as a neater and shorter alternative to Excel’s “If” formula.  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.

Max and min functions as an alternative to If statements

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”.

Financial modelling with Excel’s Max and Min functions

Experienced 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 Max/ Min functions, which demonstrates how these functions have been used to solve the problem outlined above.

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 the alternatives described above because they can sometimes solve a financial modelling problem more directly than Excel’s standard IF function.

About this free online material

You’re looking at free online material on Excel functions extracted from our financial modelling course training.



...join the conversation...

Your email address will not be published. Required fields are marked *