The benefits of a "split role" approach to financial modelling

Posted by John Dimberline

Split_role

We’ve been working with a client over the last few weeks recreating their in-house financial model, and this process has reinforced my views on the benefits of a ‘split role’ approach to financial modelling.

The problem in most cases, is that it’s just not realistic for staff to obtain and apply the required level of skill to building models, alongside their already full day-to-day workload. In the majority of cases their time would be better employed focussing on the business and not wrestling with the technicalities of spreadsheet modelling.

I think to many the prospect of bringing in an outsider to solve this problem can look equally problematic.  The key word we would use to allay their concerns, as simply as we can, is most definitely ‘collaboration’. 

Split role modelling absolutely necessitates efficient collaborative working - the experts in the business must seamlessly combine with the modellers, with each focussing on their strengths and avoiding their weaknesses. There is a clear loss from applying the ‘split’ but a greater gain from people who know how to deal with this interface and from being able to apply people to what they are trained to do.

We’ll be presenting and handing over our model very shortly to the client’s management team (which should be more of a refresher than a crash course because of the split role process).  This will present another opportunity to get client feedback on how a process that’s new to them has worked out, and this must be one of the critical metrics of success for the split role process.

  Download the Business Analysis Lifecycle ebook  

 

Comment on this post...

Sign Convention - Positive vs. Negative

Posted by Kenny Whitelaw-Jones

Signs

This post first appeared on the financial modelling handbook website.

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 handbook guide.

The whole idea of the financial modelling handbook 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.

input_outflow

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?

sea_of_negatives_1

Or the single negative among the positives?

sea_of_negatives_2

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.

FAST_approach

How sign switching is done in FAST models

sign_switching

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?

 

To join in this discussion, visit the financial modelling handbook website.

 

Comment on this post...

The advantages of the OFFSET function in Excel...

Posted by Andrew Berkley

advantages2

Stuck waiting for a delayed flight, I thought I'd write a blog about the advantages of the OFFSET function in Excel.

I had a coffee.  Then I had another one.  I had an Indian head massage (that was very effective).  I then reviewed a model to see how easy it was to play with currency conversions where one currency (AED) was pegged to another (USD).  Answer: it threw up a few interesting issues.  I then checked Facebook and F1F9's cloud based communication system (we use something called Jive).

I talk about OFFSET in our training courses because it is used so frequently in non FAST models.  OFFSET is Excel's satellite navigation system (I explain): it returns a value with reference to a fixed point.  So it shows the contents of a cell that is a number of rows and columns away from the fixed point.

My flight is being called and I still cannot think of a single advantage to using OFFSET: it is difficult to explain and review, it is not immediately understandable, it has poor transparency, dreadful audit trails and it is all too easy for it to bring up silly answers and present them as valid. 

Perhaps its only merit is it helps me reach an immediate and strong opinion about the risks associated with models that do make use of it.

Comment on this post...

Financial modelling: start with the end in mind

Posted by Stephen Daniels

Laptop-Model-2
When building financial models, it is helpful to conceptualise the results that the financial model needs to produce and to work your way backwards from there. The following provides brief pointers on why this technique is useful and how it works. 

According to the world-renowned leadership adviser, Dr Stephen R. Covey, an effective person always approaches a task or project by starting with the end in mind.

He says that having a crystal clear picture of what we want to achieve before we take the first steps will make it easier for us to determine what we need to do in order to reach that goal. In his international best-seller The 7 Habits of Highly Effective People, Dr Covey adopts the analogy of a building, pointing out that construction is only possible once the blueprints have been drawn up.

The habit of beginning a process of construction with a clear understanding of the end product can be highly beneficial when building financial models. When we can conceptualise how the model should function and appear, we can work our way backwards to see what steps are needed to achieve that result.

Here are some guidelines on how this approach can be applied to financial modelling and why it is beneficial:

Make sure that the steps are clearly defined. When we begin with the expected output, each task that precedes it must be broken down into steps and arranged logically. These clearly defined steps tell us how far we are from the required result and, therefore, the time required to achieve that result.

Avoid unused calculations. Calculations which do not add to the required output should be avoided completely. This approach is very helpful in models where we need to replicate. Start with the financial statements and then work backwards to find the inputs required to calculate the line items in the financial statements.

Don't start with the inputs – as tempting as this is. This is important as we won’t know what data is needed unless we start with our outputs and figure out how to calculate each line item.

As with any new habit, adjusting your approach to building a financial model can take time. Starting with the end in mind may be difficult at first, but by persevering with this method and using it each time you need to build a model, you will soon find that it is an efficient and logical approach. 

Download the Agile modelling ebook

 

Comment on this post...

Wow, howl or shocked silence: What is your response to a new spreadsheet model?

Posted by Andrew Berkley

iStock_000026685715Medium

There is a critical point in a spreadsheet's development when that spreadsheet is first shared with someone that has never seen it before.

If the new user has a stake in the spreadsheet, then their response might be one of three: wow; howl; or shocked silence.

One reason that we work with the FAST standard is to maximise the chances of a wow response.  But, from experience, we have learned that often the wow response is a slow burn.  F1F9 clients build up a sense of wow over time.  As their requirements change and they get used to the FAST model upon which we collaborate, so they appreciate the flexibility, structure and transparency of the model with which we all work.

A howl is more likely from a client that has a non FAST spreadsheet model that they offer up for rebuild.  We understand their pain.  However much our clients accept the long term benefits of FAST modelling, there is a leap of faith required in the short term.  An existing model into which much time has been put must be offered up for dissection, analysis and reconstruction.  That can be a painful experience. 

So one thing we do to ease the pain is something called Interim FAST modelling.  Interim FAST modelling is FAST modelling but undertaken in an existing non FAST model. 

There are three elements of Interim FAST modelling.

1. Make the existing model easier to review

Short formulae are easier to read than long formulae.  One reason for long formulae is long worksheet names.  So the first thing we might do is to shorten each of the worksheet names on each worksheet tab (Excel 2003: Alt O H R; Excel 2007/10/13: Alt H O R).  The trick is to come up with something meaningful in three letters or fewer.

We will also identify the last column used in a worksheet and hide columns to the right of that column (Excel 2003: Alt O C H).  We will also put in a placeholder label to mark the last row that is used on a worksheet.  It is a simple psychological advantage to a reviewer if they know the boundaries of each worksheet - latitude and longitude.

2. Replicate existing model logic using FAST

Taking a particular section, we will replicate the model logic immediately beneath the original.  We will go as far as we can to show how FAST modelling could work in the existing model: colour conventions, row anchored links and calculation blocks with one calculation at the end.  We will also have a check to prove that the outputs of our interim modelling match the original outputs.

This is a powerful step for a client that is new to FAST modelling.  They see their model logic made transparent; they begin to understand the use of flags to answer the question "when?"; they begin to understand why FAST models are so easy to review.

They will also see how long formulae are shortened and how few rows become many.  In a recent training course, I took 3 lines of existing code and built an interim FAST model.  The interim FAST model - built using FAST modelling techniques - used 30 rows.  But it was far quicker and easier to review the 30 rows than tease out the model logic in the original 3.

3. Transfer the interim FAST model to a FAST model

Non FAST models tend to use columns in a different way to FAST models.  So the final step of interim FAST modelling is to take the interim FAST coding and drop it in a FAST model.  This tidies up the whole process: headings and sub headings can be set up; units and row totals can be slotted in and reviewed; quick graphs can be charted.  This tidies up the whole process: headings and sub headings can be set up; units and row totals can be slotted in and reviewed; quick graphs can be charted.

From our client's point of view, the process of rebuilding a non FAST model is made clear.  They can see, understand and challenge the journey that their original model is taking in becoming a FAST model.  For us in F1F9, it is an effective means to achieve collaborative modelling and build an effective ongoing modelling relationship with a new client.


free financial modelling course 31 days to better financial modelling

 

Comment on this post...

Do financial modelling standards inhibit innovation?

Posted by Kenny Whitelaw-Jones

FAST Standard and innovation
This is one of several questions that is asked regularly about our adoption of the FAST standard.

We think that standards are more likely to promote innovation than inhibit it.

In his book, "The World is Flat", Thomas Friedman quotes Joel Cawley, Head of IBM''s strategic planning unit:

"Standards don't eliminate innovation, they just allow you to focus it. They allow you to focus on where the real value lies, which is usually everything you can add above and around the standard"

In software development the code is never an end in itself. The end point is the application created by the code and the problem it solves. Standardisation in coding facilitates innovation in application development: better software solves more problems.

Spreadsheet models are never an end in themselves. They are created to answer a business question. Knowing what question to ask - and knowing what to do with the answer - is where the real value lies. This is what our clients add above and around the standard.

As one of F1F9's Private Equity clients puts it:

"Every investment we make starts with a hypothesis. The financial model is the environment in which we conduct experiments to test that hypothesis. Our focus is on formulating the right hypothesis, and then understanding what the experiment tells us. FAST helps us by reducing the time it takes us to understand the spreadsheet itself, so that we have more time to think about and act on what it is telling us"

The greater the level of financial innovation, the greater the requirement for clarity and transparency in the modelling. 

Another F1F9 client put it like this:

"The application of standards allows a team based approach to modelling and reduces our risk. This gives people the confidence to try out new and innovative solutions to client problems, knowing that their analysis will be understood by their colleagues and by the client."

Software standards capture the distilled wisdom of thousands of developers who, through decades of trying things out, have come to understand what works and what fails to work.

As an independent standards body, the FAST Standard Organisation is now doing the same for financial modelling.

free financial modelling course 31 days to better financial modelling  
 
 
Comment on this post...

Why living with your model is like living with a dog

Posted by Kevin Gate

dog_blogIn 1978, Clarissa Baldwin, Chief Executive of the Dogs Trust, created the slogan “A Dog is for life, not just for Christmas”. After 25 years in financial modelling and a fair few as a dog owner, I have come to realise that some dogs are models and some models are dogs so I hope Clarissa will forgive me for hijacking her well-meant slogan.

Of course there are many kinds of model and in coining this phrase I am not thinking of that casual affair with a spreadsheet that is redundant as soon as you have finished it. If you are going to live with a model (or a dog for that matter) it’s probably worth spending a little time making sure it is compatible with your way of life.

So, what makes model easy to live with?

Given the tendency for models to be looked at in some detail, appearance can be important. However stunning beauty might be a distraction and may conceal a darker side. In practice the model needs to be easy on the eye so loads of garish colour and multiple fonts or random blocks of code scattered around the sheets are not easy to live with. It may be ‘in the eye of the beholder’ but good looks can certainly help.

Unless you enjoy life on the edge, behaving badly is pretty tedious. A certain predictability in behaviour and a consistent response to your questions makes the relationship far more sustainable. However taken to extremes, this can become boring, so the occasional surprise can be worth its weight in gold. Given the complexity of some models a surprise outcome can be truly enlightening, providing you know your model is behaving properly.         

At some stage you will probably want to introduce other people to your model. Knowing your model has good manners makes this a lot less risky. There is nothing more embarrassing than a model that flies of the handle at the slightest provocation, crashes out or simply takes so long to answer that everyone goes to sleep half way through the conversation. Good manners are learnt at a young age so make sure your model has good manners from the outset.

George Bernard Shaw could have been talking about financial models when he wrote. “The single biggest problem in communication is the illusion that it has taken place.”

In the end the ability to communicate is probably the most important quality for a long term relationship with your model but communication between you and your model is just the beginning. A model that can communicate with a wide audience has real value so make sure you model speaks the same language as the intended audience and can be clearly understood.

“That’s a lot to ask of your model” I hear you say as you stare at a blank spreadsheet wondering how to work out if the company is going to run out of cash next month. You are not alone!

By adopting FAST as a standard F1F9 have been building corporate models that last a lifetime, behave themselves, have good manners, communicate brilliantly and even look good. We also train others so they can have a happy life with their models.

 


free financial modelling course 31 days to better financial modelling
Comment on this post...

Model Optimisation – Turning theory into practice

Posted by Andrew Berkley

TIP2

Coming to accountancy studies late in life, I found myself in the classroom for my 30th birthday. We were on a light industrial estate on the outskirts of Cambridge and the topics for the day - taught by a tutor who is still a legend in the world of financial reporting - were deferred tax and accounting for pensions.

I've spent better birthdays - but at least this one was memorable.

The other memorable topic from my accounting studies that has served me well through my thirties and my forties has been Modigliani and Miller. I like the highly theoretical world in which they live: no taxes; no transaction costs; perfect information; infinite debt availability; and infinite projects with a positive NPV.

But like the best theories, I find that you can take Modigliani and Miller into the real world. Their theory of capital structure, for example, lies at the heart of model optimisation.

Their starting point is that capital structure is irrelevant - how you raise money has no impact on value. So we can conclude that there is no point in optimising models.

And that is not good enough when you start to consider taxes. Bring tax into the equation and debt finance becomes advantageous. Borrow as much as you possibly can, we conclude. So an optimised model would by default go for maximum gearing.

And that is not good enough when you take real debt investors into account. Or risks of bankruptcy. Or credit rating agencies. Or the particular features of debt markets: right here, right now.

So Modigliani and Miller tell us how far we need to travel from an idealised world to enter the real world. And as we start to breathe in the polluting atmosphere and swim the muddy waters, so models begin to have a role - describing the real world in some imperfect but nonetheless appropriate and useful way. 

FAST financial models, with transparency at their core, are designed to satisfy key stakeholders quickly and easily that their objectives are being met. For debt and equity investors, it is all about Modligliani and Miller - finding the optimal mix of debt and equity at levels that satisfy appetites for risk. And a price conscious project sponsor will seek a price point such that the project generates sufficient cash to generate a minimum return for investors but not so much cash as to be uncompetitive.

Model Optimisation eBook Download
Comment on this post...

New ebook: Business Analysis Lifecycle

Posted by Andrew Berkley

Business-Analysis-LifeCycle_EBook

The Business Analysis Lifecycle framework was developed by Tom Grossman. Like all works of great insight – it now seems obvious. However, when we first came across it, it was a real moment of revelation. It makes clear and explicit what had been unclear and implicit previously.

This framework has helped us to explain what it is that we do (“spreadsheet engineering”) and what we rely on our clients to provide us with (“conceptual models”).

In our training business, it has helped us as we explain to our students the different skills that they will need if they are to add value to their modelling assignments. We are deeply grateful to Tom.

The Business Analysis Lifecycle model divides the world in two – the real world, and the model world.

business-analysis-lifecycle

Analysts and financial modellers in particular, live in the model world. It is a well-ordered world where A leads to B, and where businesses can be neatly represented by calculation blocks and inputs.

Most people do not live in the model world. Communicating with people in the real world can be a challenge for those who live in the model world.

It is important for analysts to do the work of turning model insights into business insights. Managerial insights are aimed at people who are familiar with the business, and the industry in which the business operates, but who are not familiar with the model.

A key skill that allows an analyst to progress to more senior appointments is the ability to communicate insights about the business based on analysis of the model.

Our latest ebook “Business Analysis Lifecycle – From business concept to the financial model” gives practical advice on how you can effectively communicate and deliver these management insights.

Download the Business Analysis Lifecycle ebook
 
Comment on this post...

Why labels matter in modelling - Interest on a loan

Posted by Andrew Berkley

Labels

Once you have been working with the FAST standard for a while, you begin to realise that one of the most difficult things to get right are labels. 

Labels - often in column E - should be: descriptive (yes), concise (yes, yes), short, punchy and accurate (yes, yes, yes).  And in order to make the single source calculation structure work, they should be unique.  We work with unique labels as a matter of course: avoid naming identical things with different labels; avoid calling different things by the same label.

Unique labels can also help to bust jargon.  And here is an example of where things get full of jargon: interest on a loan.

Capitalised interest

Imagine that you are modelling a project where the first two years are spent building an asset.  It is known as a construction period.  The thing about a construction period is that - more often than not - there is no revenue.  So costs cannot be written off to the income statement because there is no revenue.  An income statement relies on revenue since it is a basic accounting principle that costs should be matched with revenue.  If you have no revenue, then you have no income statement.  You can show no costs.

So costs that are awaiting revenue (against which they may be matched) are capitalised.  They appear on the balance sheet as an asset.  So interest that is charged during construction by a lender is capitalised.  It sits on the balance sheet as an asset and is subject to depreciation once the revenue starts to come in.

If you model interest during construction as an asset, then what you have determined is a particular accounting treatment for interest.  When should this treatment be adopted?  Answer: interest should be capitalised when there is no revenue against which to match it.

Interest expense

Now let us assume that there is plenty of revenue.  There is no requirement for capitalised interest and so interest is taken to the income statement in the period to which it relates.  We call this "interest expense due" or "interest payable".

Most of the time our model period ends are aligned with interest setting dates.  It is one of the facts of modelling that the lender's terms will often determine whether the model is monthly, semi-annual or annual.  It makes sense for the modeller to get those dates aligned since interest charged for a particular period can then be slotted into its equivalent model period.

One exception would be where the lender has offered zero coupon debt.  Zero coupon debt requires no payment of interest until the final redemption period.  At this point, all the debt is paid back in one single sum covering principal and interest.  Even though no interest is charged until the end, accountants would still accrue for interest in every period.  And so should modellers.

Interest paid

So what is the difference between interest expense due and interest paid?  Often we assume no difference: interest is calculated for a particular period and immediately paid.  The same calculation appears in both the income statement and the cash flow statement.  And the label reflects this assumption: "interest expense due & paid" or "interest payable & paid".

And if there is a lag in payment then a cork screw for an "interest payable" balance would be deployed.  What makes the balance go up: interest payable (as charged to the income statement).  What makes the balance go down: interest paid (as shown in the cash flow statement).

What is important here is that the interest must be paid.  There is a contractual obligation that - if ignored - could lead to default and the lender's right to insist on immediate repayment of the loan.

Rolled up interest

But what if there is insufficient cash to pay the interest?  One option is for the borrower to draw down more principal on the loan facility to cover the additional interest.  The interest is still paid but the amount required to repay the loan increases by the extra amount borrowed to pay the interest.  We call this "rolled up interest".

Rolled up interest describes a particular funding solution for the interest paid.  It does not describe an accounting treatment.  The interest is paid by drawing down further on the loan facility that generates the interest in the first place.

Accounting treatment vs. financing solution

What this boils down to is clear thinking about interest.  There are two questions to be answered:

(a) How should I account for interest?  In the income statement (cost) or in the balance sheet (asset); and

(b) How is my interest paid financed?

Once you have these two questions clear, you begin to realise that there might be various combinations to model.  And each combination has a different risk profile.  Understanding the different risk profiles is key to understanding a lender's negotiating position.

And it all starts with good labels.

Download "The Devil's Guide to Spreadsheet Creation" for a light hearted look at how not to create models.


The Devil's Guide to Spreadsheet Creation
Comment on this post...
     |      All posts      |     
31-Days_Blue-1

Start your free course now

In around 10 minutes per day for 1 month, this FREE online financial modelling course will teach you the basics of the FAST modelling standard, and some practical and highly effective modelling skills.

Subscribe to the F1F9 Blog

Recent Posts