Excel 2013 is pretty nice. The interface is clean and crisp and Microsoft seem to have gotten rid of most of the bugs that made Excel 2007 such a disaster.

However the “sweeping” animation on the cursor, and the slot machine style animations when numbers update make me want to hurt somebody. If you’re having the same reaction, you can keep your colleagues safe from harm by disabling the animation:

1. Open the control panel and choose “Ease of access”.

2. Select “Optimise visual display”.

3 Then scroll down and tick “Turn off all unnecessary animations (when possible)”.

There. All unnecessary animation now turned off. Your colleagues are now safe from harm.

Written by Hedieh Kianyfard

“ Errors using inadequate data are much less than those using no data at all.” – Charles Babbage

It is common to hear people complaining about excel being prone to errors. Even if we take a very systematic and methodical approach to using excel modeling, we won’t eliminate errors . The benefits of using excel however, far outweigh the risks it potentially poses.

Instead of wasting time and resources building overly complex financial models, a good financial modeller can create a Flexible, Appropriate, Structured and Transparent (FAST) model – one that is “as simple as possible, but no simpler”. That way they can focus on what goes into the model as inputs and avoid the garbage in, garbage out problem.

One way to help reduce errors is to include error and integrity checks in your models. The FAST Standard advocates a Control Sheet that collects all the individual checks throughout the model into one sheet.  This allows the model builder to check that the model, or section of the model, works correctly.

All these checks are then summed and a master error check and alert indicator can be created and included in the freeze pane in each worksheet within the model.

Here are a few examples of common checks to be included in your models:

If the sources and uses of funds do not match, then the flags for Uses and Sources Cross Check will be highlighted in red and the Master Error Check will also be automatically formatted in red background.

This list is just an example of how you might structure your control sheet and some of the questions you could ask.

I’d like to make this into a modelling guide for inclusion into the handbook. For this, I need your help.

What methods do you use? What are the most fundamental checks that should be made? How many checks is enough, is it possible to have too many?

I‘d love to know how you do this, please leave a comment and discuss, I’ll then collect everyone’s thoughts and ideas and create a modelling guide for this topic.

This week Neha from the F1F9 modelling team has put together a guide on using placeholders. This is such a simple technique but one that we use ALL THE TIME in in our modelling.

Thank you for all the comments over the last week – I appreciate them all, especially those suggestions for improvements. If I haven’t gotten to all of them please bear with me – I will reply to them all as soon as I can. . . . you know what it’s like – the day job gets in the way of projects like this!

I hope you find this week’s guide useful. As usual please leave a comment with your insight and ideas.

To view the latest financial modelling handbook guide click here.

When should numbers in a financial model be positive, and when should they be negative?

This question is one that modellers often feel quite strongly about. Although the FAST Standard has a well established position on this, I want to open this up for discussion ahead of writing the guide. The whole idea of this collaborative, “publish as we go” model is that we can explore topics iteratively, and hopefully all gain from perspectives we hadn’t considered previously.

The reality is that we can’t escape sign switching. It’s going to happen somewhere in our model. The question is therefore not “whether to sign switch” but rather “how to sign switch and where”.

Before we get into what the FAST Standard says about sign convention, and why it takes the position it does, we’ll look at the advantages and disadvantages of two approaches: inflow / outflow and positive as normal.

1. Inflow / outflow

In this approach all values which represent inflows to the business are positive numbers. All values that represent outflows from the business are negative numbers.

Advantage: Inherent readability of financial statements

Users expect to see financial statements presented according to the inflow / flow convention, with inflows represented as positive numbers, and outflows represented as negative numbers.

Advantage: Simpler logic in arithmetic expressions

When inflows are expressed as positive numbers, and outflows as negative numbers, arithmetic expressions can be more simple. i.e. a column of numbers can just be added up, without worrying which are being added and which subtracted from the total.

Weakness: Sign switching of inputs

Sometimes modelling assumptions are provided as positive numbers. They therefore have to either be sign switched on input, or sign switched within calculations. It’s often the case that values have to be sign switched numerous times to accommodate the requirements of functions and presentation. This increases the risk of error.

However, the flip side of this is that forecasts are often driven off “actuals” which are provided on inflow / outflow convention. More on this below.

Weakness: Mid calc sign switching often required

Let’s take an example. When calculating say, the balance of non current assets, one will need to know the amount of capex and the amount of depreciation (ignoring asset disposals for the moment). Under inflow / outflow convention capex is a (cash) outflow, depreciation is a (non-cash) outflow. Yet capex increases the balance of non-current assets, whereas depreciation reduces that balance. There will need to be some kind of sign switching going on in the middle of this, very often buried within the calculation.

Weakness: Sea of negatives

On occasion in a model a value will become unintentionally negative when it should be positive, or positive when it should be negative.

Which is more like to be spotted . . .

This single positive among the negatives?

Or the single negative among the positives?

 2. “Positive as normal”

In this convention all numbers are positive, and the “direction of flow” is indicated by the label. Inflow numbers will include labels like Revenue, Income, Receipts, Drawdown, Borrowings. Outflow line items will have labels like Expenses, Costs, Payments, Expenditure, Repayments, Distribution.

Advantage: How assumptions are often provided

Assumptions are often provided as positive numbers. Sign switching is not required on input or within calculations and in many cases logic can be simpler as a result. However this is not universally true, especially where forecasts pick up from a last set of actuals.

Advantage: Negatives stand out as unusual

See the side by side comparison above. I suspect that different people will have different views about which is easier to spot. Please leave a comment with your thoughts on this.

Weakness: Can’t just “add up”

In the inflow / outflow convention we can usually just add up the numbers and let the sign convention take care of itself. In “positive as normal” whether a line is being added or subtracted has to be written into the formula.

Weakness: Not appropriate for financial statements

Most users will be used to seeing financial statements expressed using “inflow / outflow” convention and will expect to see the model’s financial statement outputs presented in this way.

Note however that there are regional variations about how the balance sheet is presented. Sometimes both the asset and liability balances are presented as positive numbers. Sometimes only the asset values are expressed as positives, with the liability balances expressed as negatives.

 3. What FAST recommends

FAST recommends a “mixed economy” of sign convention. “Positive as normal” in the calculation engine / working sheets of a model, and inflow / outflow on the presentation / financial statements. The reason for this can be hopefully seen from the diagram below: the advantages of each of the two conventions apply to specific parts of the model.

How sign switching is done in FAST models

Only line items that are going to flow into the financial statements are sign switched. The suffix “POS” is added to the positive version in order to maintain distinction between the line items, and thus maintain consistency and integrity about row labels being unique. The example above, “Fuel costs” are being exported to the financial statements, and are therefore give “export” line item formatting.

4. Issues with this approach

The mixed economy of “positive as normal” in the calculation sheets and “inflow / outflow” on the financial statements works really well, especially in “bottom up” models where all line items are built up from provided assumptions. This is typical of project finance and infra modelling.

It’s less typical in Corporate Finance and FP&A where we’re often starting from a set of actuals, expressed in inflow / outflow convention. FAST is not currently sufficiently clear on this.

In this regard are three possibilities:

1. Adopt FAST positive as normal in calculations and sign switch the actuals prior to input

Benefits:

• avoids a lot of additional sign switch calcs.
• The calculations are all positives which avoid mid calc sign switching and is simpler.

Weaknesses:

• The actuals inputs don’t match the actuals outputs – this gets horribly confusing when trying to ensure alignment of outputs.
• The sign switching is done outside the model and is not transparent. When the actuals are updated this could cause confusion.

2. Adopt FAST positive as normal in calculations and explicitly sign switch the actuals before using them

Benefits:

• The actuals inputs match the actuals outputs.
• The calculations are all positives which avoid mid calc sign switching and is simpler.
• The sign switching is explicit.

Weaknesses:

• More sign switch calcs – switching “pre calc” as well as the normal “post calc”

3. Adopt inflow / outflow throughout the model

Benefits:

• Avoids having to worry about what to do with the inflow / outflow actuals

Weaknesses:

• We get into sign switching in the middle of calculations e.g. capex / depreciation issue.
• Have to deal with all the other weaknesses of inflow / outflow

At F1F9 we’ve been following the second approach in our Corporate Finance and FP&A modelling.

• How have you approached this problem in your models?

• Have I missed anything on the “advantages” and “weaknesses” of each approach?

• Do you have any recommendations for a better approach?