Financial Modelling Sign Conventions

City Financial Models • 2 September 2019

Scope for confusion

The sign convention used in the outputs of a financial model will generally be determined by individual company practice. These can differ in the way liabilities are reported in balance sheet for example. Some companies will show them as negatives, while others will show them as positive.

Regardless of the output, in the inputs and calculation sections of the model, there are two main sign convention options.

• Inputs and calculations are positive by default.

• A mixed approach where assets, revenues and cash inflows are positive while liabilities, expenses and cash outflows are negative.

Naturally, to avoid confusion it is best to explicitly clarify which convention is to be used and to use that consistently.

The ICAEW financial modelling code doesn’t recommend one method over the other, just that the sign convention be clear and consistent. It also recommends not to use pure accounting convention.

In general we would recommend using the positive by default approach as this highlights any erroneous negative values in the model and is also more easily related to by non financial staff.

< Older Post Newer Post >

Spill Error - Why doesn’t my Index Match work in Excel 365.

by City Financial Models • 27 May 2020

This something we are starting to see as we work across a number of different versions of Excel. Recently, Microsoft rolled out something called dynamic array formulas in Excel 365, which is a substantial change from previous versions. Dynamic array formulas automatically spill in to neighbouring blank cells. They have also added some very powerful new functions such as FILTER, UNIQUE, XLOOKUP and XMATCH. Even better there is no need to enter array formulas with control + shift + enter (CSE) as in other versions. However, because of the way dynamic arrays work, there will be instances where models built in other versions will have formulas which unintentionally now try to spill or populate into neighbouring cells when opened in Excel 365. Hence the Spill error. To overcome this, the @ character is added automatically to stop formulas unintentionally spilling multiple results into neighbouring cells. Because dynamic arrays are only available in the subscription version at the moment and not in the other versions, these powerful new functions may cause some compatibility issues. In general, the @ character should overcome the compatibility issues with models build in other versions to work in Excel 365, however the dynamic array functions at present will not work in non-subscription versions so should be used with caution if you aren’t sure of your final audience.