All Posts by Andrew Berkley:

What if my existing model isn't FAST?

Posted by Andrew Berkley

BannerSpread

It's one of the most common challenges in the classroom: "Andrew - what you are teaching us is all very well for new models.  But I work with models that have not been built to a standard.  What should I do with them?"

Nobody likes my answer - which is to rebuild the model using a modelling standard.

So I was pleased when I got this feedback from a recent course delegate who works for a real estate investment company:

"I just wanted to let you know that last week I was asked to review a business model received from an external party. It was not that good, so I decided to remodel the model using the FAST rules we learnt during our course, in the Excel template provided.  And I must say: my colleagues and I were quite surprised with the result. It gave a clear and easy-to-understand model with an accurate result. 

"We are definitely going further with this in the future I think."

And it's not only this company that is going further with standardised models.  The Institute of Chartered Accountants in England & Wales is launching its Twenty Principles for Good Spreadsheet Practice on June 17th 2014.

High on the list of principles is: adopt a standard - and stick to it.

free financial modelling course 31 days to better financial modelling

 

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

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

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

The Excel modelling guru

Posted by Andrew Berkley

Laptop-Model-BW

Let me start with a stereotype: the Excel modelling guru (“EMG”). 

The EMG is a perfectionist.  No solution is perfect; all can be improved.  Their coding and use of Excel functions takes on the quality of a Japanese haiku; the neatness of their syntax is a work of art. 

EMGs find innovative ways of using the lesser known and more complex Excel functions - such as INDIRECT().  Their work carries a distinct character and style - like a fingerprint.  It does not occur to them that people may not be able to read their spreadsheets: a simple solution is not nearly as good as a clever solution.  They work in a world that is fascinating and impenetrable to other spreadsheet users

Like most stereotypes, the Excel modelling guru is an amalgam of different people that we all recognise.  It is useful nonetheless because it highlights the risks that we deal with when we leave the modelling to the EMG.  For starters:

  • Decisions are made on the basis of spreadsheets that only the EMG understands;
  • The organisation suffers when the EMG is on holiday or – even worse – leaves to join another organisation; and
  • The organisation starts to accept that with complexity comes greater accuracy

I suggest that the last point is most dangerous of the three since it has such a long term impact.  Such received wisdom is handed down unchallenged through the generations.  It is why “senior management buy in” has become such a watch word for change management programmes.  The boss needs to be on board if you are to unlearn old habits.

Through the use and promotion of the FAST standard, we at F1F9 do our best to challenge the EMG.  So a spreadsheet that only the modeller understands is a bad spreadsheet.  Just as a technical drawing that only the architect understands is a bad technical drawing.

Likewise, we encourage all in the modelling community to collaborate so that EMGs are able to go on holiday.  The FAST standards are designed to make collaboration easy and second nature – within organisations and across difference organisations.

And we do our best to build simple spreadsheets.  Why?  Because with simplicity comes transparency.  Transparency breeds good analysis and judgment.  Good judgment breeds good decision making.

It is too easy to demonize the EMG as something external, over there or not invented here.  The EMG exists in all of us that work with Excel to help our organisations’ decision making.

Collaborative modelling is the smartest way for organisations to get what they need from the spreadsheets they rely on.

 

 

Comment on this post...

What can financial modellers learn from car manufacturers?

Posted by Andrew Berkley

Car_Blocks2

A recent article in the Financial Times highlights how the motor industry has adopted a modular approach to building cars.

Volkswagen believes that by standardising elements of the production line, and by using standard modules in vehicle construction, they can grow to become the world’s biggest car manufacturer.

This approach has won them awards for innovation and has shown other manufacturers and industries that taking a modular approach can increase efficiencies, save time and improve output.

 

How can this approach be applied to the world of financial modelling?

At F1F9 we take the same approach to building financial models. We have found that modularity is the key to modelling productivity, and standards are the key to modularity. Although no two modelling assignments are the same, there are plenty of elements in common. This means that a “plug and play” approach to modelling is possible – and beneficial. This makes FAST modelling very efficient. 

For example, in a FAST modelling assignment, we at F1F9 can be confident of spending 80 per cent of the time on 20 per cent of the model – those parts of the model that really need our attention because they are highly specialised, innovative or particularly risky.

 

 

How is this efficiency possible? 

Because FAST financial models are built with standards in mind. Models built with the FAST standard incorporate standard modules with ease – just like Volkswagen’s production line.

FAST models use links and calculation blocks to take standard modules and make the coding specific.  That coding, in turn, is understandable and reusable elsewhere in the model.  A modeller within F1F9 may expect to undertake very little blue sky modelling.  Instead, there is a focus on recycling information and coding that has been used before.  That leads to productivity gains and efficiency.

And it is all made possible through standards.

 

 

How do I find out more?

 

To get a better understanding of how the FAST standard makes it possible to build reusable, modular code in financial models, please take a look at our without obligation free course 31 days to better financial modelling. – This course will guide you through some key FAST principles and offer excellent tips that will help you improve your modelling immediately.

To learn more, our FAST financial modelling, and FAST project finance modelling courses both make extensive use of modular code sections that enable you construct models more easily, in less time, with fewer errors and with greater confidence.

I look forward to hearing your comments.

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

Financial modelling is a team sport

Posted by Andrew Berkley

blog-agile-1
Financial modelling is, or at least should be considered in terms of, a team sport - the talents of an individual modeller, no matter how fantastic, are nothing if they cannot work together within a collaborative team to achieve their goals.

Michael Jordan, legendary NBA basketball player said this about teamwork:

There are plenty of teams in every sport that have great players and win titles. Most of the time, those players aren't willing to sacrifice for the greater good of the team. The funny thing is, in the end, their unwillingness to sacrifice only makes individual goals more difficult to achieve. One thing I believe to the fullest is that if you think and achieve as a team, the individual accolades will take care of themselves. Talent wins games, but teamwork and intelligence win championships.

Take cricket players Shane Warne and Sachin Tendulkar. In their finest hour, the performances of their careers meant nothing because their team, as a whole, did not perform well.

In the past, financial modelling was a one-man show with everything depending on the "modelling guru" and it didn't seem to matter that no one else - least of all the client - could understand the model. With the evolution the FAST standard, a common language for collaborative modelling, this is no longer the case.

Model building can now be shared among the team, leading to parallel working and great efficiency.

Absence of team work can lead to problems, among them:
- Work is duplicated causing inefficiency
- Deadlines are missed
- Models are more error prone

Consider the equestrian. Although in the ring there is just one person competing for the ribbon, behind that person there is the horse, the horse’s handler, the trainer, even the saddle - there are many facets involved in making the equestrian a champion.

Similarly, in financial modelling, the collaboration of a cohesive team is essential to the completion of a successful model build.

 

free financial modelling course 31 days to better financial modelling

 

Comment on this post...

Financial modelling: to model is to understand

Posted by Andrew Berkley

financial modelling to model is to understand
I’ve just finished writing some teaching material that focuses on foreign exchange, for our online FAST Financial modelling course. It reminded me of how little I understood when I became an accountant.
Take inventory for example. In the first two weeks of accountancy training, we did bookkeeping. We learned about inventory. We learned that inventory was valued at the lower of cost and net realisable value. We were tested on inventory. Could we do the double entry bookkeeping? Could we prove that we should retain our training contracts with our big important firms by showing we knew what to do with inventory? In those early days, I knew what to do with inventory. But I did not understand it. It was only when I came to model inventory that I got it.

What modelling gets you to realise is a simple fact that most accountants just don’t think about: the whole point of inventory is that it excludes any profit margin. The lower of cost or net realisable value means no profit. Not a thing.
On countless occasions – and once at Wolverhampton Wanderers – I have stood in front of an audience of accountants and told them about cork screws in financial modelling. “What makes inventory go up?”, I ask. “Purchases”, they reply. “Quite right”, I say. “Now: what makes inventory go down?”. “Sales”, they reply. Every time. Without fail.

Of course it is not sales – it is cost of sales (since cost of sales excludes profit margin). And every accountant when they stop to think about it knows that it is cost of sales. Financial modelling forces you to think about it.


financial modelling courses





Comment on this post...
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