As part of an ongoing quest to discover exactly what a financial model really is, we grabbed a sample of modelling experts, quizzing them on their approach to their work in Excel. What we found was a wide variety of modelling styles, with each analysis oriented towards solving a particular commercial objective.
If you’d like to think about where your own analysis fits and put your own Excel work in some kind of box, after talking to our modelling experts we developed two graphics (attached). The first graphic starts by separating the date crunchers from the financial modellers and allows you to find where you might fit. The second graphic tries to convince you that, no matter what financial modelling you’re doing at the moment, without realising it you may be on a journey working towards Excel enlightenment: integrated financial statement modelling.
The data crunchers
The Excel data crunchers probably aren’t really modeling at all. Perhaps they’re focussing on historic or forecast key performance indicators for a business. An example would be the funds analyst crunching thousands of lines in Excel around investment positions and returns.
Perhaps the data-cruncher’s Excel spreadsheets are a patch for inadequate internal management systems and they’re facing particular issues with workbook size. They may want to set calculation to manual (in Excel 2010 and later file > options > formulas > calculation options > manual) and use the F9 keyboard shortcut to make the spreadsheet to calculate. They will want to avoid applying formulas across the whole spreadsheet forcing Excel to work through spreadsheet-wide calculations. For those people =Sum(A:A) is a bad formula. They may want to make sure they’re using the “ctrl > shift > down arrow” keyboard shortcut to get straight to the bottom of their 10,000 rows. If they’re into using VBA macros to simplify some of the tasks they’re performing again and again over a huge data set of indeterminate size, they’re going to want to turn Excel’s macro recorder on and grab hold of the code that results when they use that shortcut. Combining that tiny piece of code together with a macro loop will probably be the most powerful trick the data cruncher will play in VBA. Beyond that maybe they’re going to want to talk to their boss about whether this job is really done best in Excel or whether the work should be transferred to a database that’s not going to skip a beat when thrown thousands of lines of data.
The Excel modellers
As a result of our discussions with our pet modelling experts, in our schema we’re defining an Excel model as something that produces some kind of profit forecast. But even then, any modeller needs to think about what kind of work they really need to do in Excel.
The profit forecasters
Someone wanting to create a budget forecast for a business unit may forecast down to gross or operating profit (earnings before interest and tax or EBIT) and happily stop there. Why? They’re going to want to think carefully about likely volumes, prices, gross margins and admin costs specific to their business unit. If the exercise has been about producing targets and holding managers accountable, an EBIT model and target may be enough. At EBIT, the business unit head has reached his ‘point of accountability’. He can’t influence subsequent line items around business-wide overheads, depreciation policies, gearing and interest levels. From the business unit head’s perspective, as long as he delivers enough operating profit, those line items should take care of themselves. A fuller financial model is not required.
The straight-to-cash-flow modellers
Cash is king. So anyone wanting a complete picture is going to want to include the difference between profits and cash flows. Project models or investment appraisal models typically step to cash flow quickly. They’re concentrating on the principal differences between profits and cash flow say around working capital, but they don’t feel obliged to forecast a full balance sheet.
The integrated modellers
These guys are modelling a full profit and loss, balance sheet and cash flow statement. Perhaps their project has moved on and is being developed as a real business now. Perhaps someone around the project or deal such as a bank or investor wants to see a forecast for full financial statements and calculate some of the key ratios they are interested in. Perhaps the modeller realises that cash flow depends on balance sheet interactions so it’s really not much extra trouble to produce the three financial statements, including a robust balance sheet check on the model’s internal integrity.
As shown in the graphic below, the cash flow modellers and the integrated financial statement modellers should be doing the same thing. Perhaps the cash flow modeller believes that they are saving a few lines in their model. Perhaps the integrated modeller has a bit of an accounting hat on, appreciates that the two modelling approaches have more similarities than differences, realises that there may be bankers and investors who would like to see the shape of the key line items in the suite of financial statements, both types need the same inputs, and that it really should be no extra trouble to produce a full set of p&l, balance sheet and cash flow statements.
Here’s to integrated financial modelling?
Have we convinced you that cash flow modelling and integrated financial statement modelling are really the same thing because both require you to think about interactions on the balance sheet? Have we converted you to the integrated approach yet? Perhaps not. But the discussions we’ve had reinforce our belief that there are many different styles of modelling out there. If you’re just looking to track and forecast key operating data e.g. around volumes you don’t need a full set of financial statements for that. But as soon as you’re modelling cash flows and thinking about balance sheet interactions (customers not paying, suppliers waiting to be paid) then we’re sure that pretty soon you’ll be thinking about whether you might as well start to forecast a set of financial statements.
Chris Sheasby, CFO at Ark Schools
Chris is chief financial officer at Ark Schools. Ark is a charity that runs 34 schools across the UK. Ark’s ambition is to transform young lives through education. Chris, and Ark, believe in the power of people.
34 schools translates to around £150m of income, more than 2,000 staff, a great number of ambitious students and huge data flows.
We talked to Chris about how numbers flow through Ark and Excel’s role in that.
The role of Excel at Ark
With high data volumes Chris told us that Ark has recently implemented a proprietary management information system. Add in a finance specialist focussed on running numbers for each of the 34 schools and that system helps ensure consistency.
Still there’s a big role for Excel. For looking at ‘what if’ scenarios across Ark’s schools Chris will ask his team to use Excel. And, inevitably, there are still a few finance managers at the schools who maintain a lingering love affair with their old Excel spreadsheets
Modelling style: integrated financial statements with full balance sheet
In the ‘old days’ local authorities would have taken care of balance sheet issues for schools, leaving individual schools worrying about their operations
In contrast, Ark’s forecast models include a full balance sheet, starting from the individual school and consolidating up. According to Chris, modelling the balance sheet at the operating unit level is essential:
“It’s your check. It provides the link to cash flow.”
Plus individual schools need to keep a close eye on unique balance sheet items such as funding provided and set aside for items like capital expenditure.
Hannah Robertson, strategy analyst
Hannah and Renewable Energy Systems (RES) are intent on making the world a better place with clean renewable energy. Internationally RES has developed a renewable electricity generation portfolio with capacity of around 10GW, equivalent to approximately 15% of the UK’s total. But don’t talk to Hannah about wind mills. They’re not windmills. Windmills are something you grind flour in. You can talk to her about wind turbines or wind farms. That’s OK.
Joining RES after an engineering degree from Oxford, RES obviously regard Hannah as one of their bright young things, regularly transporting her between her home in the UK and RES’s Coloarado office. Hannah works in RES’s strategy team looking at how new technologies could be applied in new markets – so it seems they’ve given her the challenge of taking over America. As you would expect, RES are taking Hannah’s initiatives very seriously, with her team reporting its findings direct to RES’s main board.
Modelling style: data cruncher and cash flow modeller
Hannah is another modeller who sometimes is a data cruncher and sometimes is a cash flow modeller.
Hannah spends part of her time evaluating new technologies and new markets, looking at data for electricity prices and technology costs. In those periods she’s a data cruncher.
At other times Hannah is a cash flow modeller, for example when she’s evaluating whether a new technology can be applied into a new market.
Which modelling style is right for you?
It’s clear, from talking to our experts about what they’re doing, not all models are the same. You want the right model for the job. Whichever style of modelling you adopt though, we’d love to believe that you stopped and thought about what kind of model you needed before banging entries into Excel: before sending your modelling in the wrong direction or doing more work than required. We just like the idea that you’re really thinking about the kind of modelling you’re doing.
Read more stories
You’re reading a version of an article written by Financial Training Associates for original publication by ACCA (The Association of Chartered Certified Accountants): the world’s largest professional association for accountants
Phillip Jarman, venture capital fund manager
Sitting down and talking to Phillip Jarman of Anglo African Enterprises quickly leaves you with the impression that his life moves at a pace that’s different than the rest of ours.
Armed with a fresh MBA and a background with investment bank UBS, Phillip has teamed with pan-Africa data services company “Anglo African” to establish an in-house venture capital firm. In three months he has:
- Surrounded himself with a stellar board;
- Crunched through his market research and established a pipeline of potential investments;
- Partnered with Barclays to establish the first in a chain of start-up incubators for Africa;
- Cut a deal with his first start-up business and made sure he is on track to cut his second imminently.
As well as being in an enormous hurry, Phillip strikes you as someone who, quietly and confidently, is totally determined to get somewhere. That explains why Anglo African have backed him into his own private equity fund and why he’s managed to surround himself with such a strong team.
We talked to Phillip about his work in Excel.
Modelling style: a data cruncher one minute, a cash flow modeller the next
Phillip has been using Excel to crunch through market data and establish a pipeline of deal opportunities for his private equity business. By our reckoning that makes him a “data cruncher” (A) some of the time. But he’s also developed a cash flow model for the private equity business, so some days he’s a cash flow modeller (C).