The History of Spreadsheets & Financial Modelling

8 October 2019 written by Dave Theron, Colin Human and Lance Rubin

Introduction to the co-authors

Colin Human

My first introduction into financial modelling was in 1978 when I was the Group CEO of an aluminium company in SA. We were expanding the business both geographically and across the product range. This resulted in internal cash generation being insufficient to fund the growth.

Our banker (Conrad Strauss subsequently Chairman of Standard Bank) suggested that we needed to do some longer-term financial planning. Enter my friend Dave Theron.

Dave and his partner Dieter had formed a business called Decision Information (1973) to build financial models. In those years, there was no Lotus 123, or Excel and I seem to recall the use of products such as Quatro Pro and VisiCalc. Subsequently Dave embraced Lotus 123, and I think became the Lotus distributor in SA.

In 1978, I convinced him to become the Group CFO for Kool Aluminium with the specific goal of building a model. Patently at that time, I knew nothing about modelling, and he was the model builder.

We used an ICL programming language called Prosper – and punch cards – and ran the model on a big mainframe.

So, my participation was to supply him with all the key drivers of the business case, which he converted into the model.

Obviously given my involvement in the model build, I did learn a little about Lotus and spreadsheets.

In 1980 I resigned and started my own company utilising some of my fledgling modelling skills.

To the best of my memory, Excel was launched in South Africa in 1985 and initially was nowhere near as good as Lotus. By this time, my new company had taken over Kool Aluminium and I was once again at the helm. My business focus was sales and marketing and operations – more so than finance and modelling.

Dave had moved on, and finally in 1989 I sold the company and retired @ 46.

I then got deeply involved in other companies, ceramics and granite and some private equity and capital raising from 1989 to 1998.

I was using Excel for my own purposes to build strategic planning and budgeting models for these companies.

Only in 1999 did I deliver my first financial modelling training course to the Kuwait Investment Authority at Dave’s behest.

My involvement in financial modelling and training courses was quite irregular in the late 90’s and early 2000’s – basically filling in for Dave when needed.

All course material was his creation as where the Excel files and training models

Dave Theron

In 1971 I was employed by a company called Illings (Mazda) as their “Profit Planning Accountant”.

On the day I took up my post my boss, the Financial Director, and the Managing Director set off for a month’s stint in Japan. As he was leaving, my boss handed me a manual from ICL computers called Prosper with the remark that he really did not know what it was about, but it might help me.

Having no real clue as to what a “Profit Planning Accountant” did, I spent many hours in the CA library doing research (no Google in those days) and came across the concept of financial modelling. Only then did it begin to dawn on me that the book I had been handed was ICL’s manual on a punch card-based financial modelling language which they called Prosper.

There was only one person in South Africa who knew anything about Prosper (but nothing about accounting or financial modelling), and that was a computer geek employed by ICL by name of Barry Strong. With his help I built my first financial model for Illings (from production floor to balance sheet and cash flow) and presented it to my bosses on their return from Japan. This made quite an impression, and my status was upgraded to “Corporate Planning Manager”, with an increase in salary and a Mazda RX2 at my disposal.

Three years later my boss was headhunted by BMW as their financial director in South Africa and he took me along with him as BMW’s Financial Controller. I built BMW (SA)’s first financial model.

After a year or so (and still being very much in contact with Barry Strong) I decided to leave the big corporate life. Barry Strong, his colleague Dieter Schaeffer, and I formed a company called “Decision Information” to exploit this thing called financial modelling.

This was hard going at this time as we first had to sell the concept and then our services

The term “financial modelling” was unknown at the time, though, in fact, accountants had been preparing financial models (in the form of budgets) for years. Most companies prepared their budgets every year, by hand with pencil & paper, and it was a huge task.

Very few companies also projected their balance sheets into the future, (let alone their cash flows) as this was a very time-consuming task.

Whilst the budget was an attempt to represent a company’s income statement projected into the future (and thus a type of model), because everything had to be done manually, making “what-if” type of changes (e.g. what if there is a change in the foreign exchange rate), or interrogating the budget, was just not practical.

Technology just did not exist to turn these pencil & paper budgets into real models where assumptions could easily be changed and the effects on the company’s bottom line be measured in an instant, thus tempting one to test the effect of various changes.

During the 3 years we were active in Decision Information we were lucky enough to meet three (what I called) enlightened managers who gave Decision Information their first contracts.

The first was one Malcolm Macdonald who had been seconded to a company called Ferrovorm by the IDC (Industrial Development Corporation) as their financial director. (Malcolm went on to become the financial director of the IDC, and later Iscor). The second was Colin Human, the CEO of Kool Aluminium, who quickly grasped the concept of modelling and attended our very first “Prosper” modelling course. The third was Rob Jeffrey, the CEO of Irvin Chapman (later the MD of Econometrix).

After 3 years the three partners decided to mothball Decision Information, and I joined Colin Human as the financial director of Kool Aluminium (Tvl). I was with him for another 3 years and then left to exploit financial modelling on my own. During this period, I developed courses on financial modelling and at the same time teamed up with Chris Leal as a shareholder of Spartan Computers with the intention of using financial modelling as a vehicle for selling Apple and IBM PCs.

After twelve years of this I again headed out on my own (first taking a 3-month “vacation” to sail across the Med from England to Greece with my wife and friend and his wife in his 37 foot Moody yacht that he had just had built for himself in Plymouth, England.

On my return to South Africa in 1992 (now running courses in financial modelling as well as building models) I worked very closely with the two professors of accounting at Wits university. It was here that I became of the opinion that success in business is a combination of grasped opportunities and luck, as, at this time, a company specialising in conferences (AIC) contacted the professors and asked them if they knew anyone who knew anything about financial modelling. They responded that they did and referred AIC to me. Then, whilst running the first financial modelling course for AIC, their Singapore office contacted them and asked them if they knew anyone who knew anything about financial modelling. And so, began a journey for me that took me all around the world running courses in financial modelling for many years.

Probably the biggest challenge for me at this time was keeping the courses up to date with technology changes from the punch card based Prosper to program-based EPS and then spreadsheets Lotus 1-2-3 and Excel.

Let’s look a little closer at this history (by Theron)

Around 1971, ICL (International Computers Limited, the huge British computer company) introduced a piece of software to South Africa called Prosper.

This was in fact a high-level language designed to run on the large ICL computers (in those days a large computer had a memory of 64k!), and, I believe, originated during world war II to control Britain’s radar installations. Its purpose was to enable end-users to “build” models.

Prosper was designed to accept inputs, process them with user-input formulas, and report on the results.

Thus, the system required one to construct the model in 3 sections – a section containing “Inputs”, a section containing the calculations (the “Logic”), and a section containing the output the “Report”.

In those days one communicated with a computer via punched cards.

A punched card would typically consist of 80 columns and 10 rows (the first row being row 0)

A Prosper model would basically consist of three categories of cards:

1.      “Input cards”: a “format 4” card (meaning that the very first hole punched into the card would be in character position 1 in row 4)

2.      “Logic” cards: a “format 5” card (meaning that the very first hole punched into the card would be in character position 1 in row 5). These cards contained the formulas that were required to process the Inputs and create results.

3.      “Report” cards: a “format 9” card (meaning that the very first hole punched into the card would be in character position 1 in row 9).

Doing a “What-if” required one to finger one’s way through all the format 4 cards (which formed the bulk of the model), identify the one you wish to change, remove it, punch a new card, replace the card you removed, and feed them all back into the computer.

In the early days one had to learn to read the punched holes to identify the card, but later with the advent of the print punch, the details would be typed along the top of the card, making identification much easier.

The sequence of the format 5 and format 9 cards was critical. If the cards were inadvertently dropped it could take hours to get them back in sequence before feeding them into the computer.

One of my first Prosper models was a departmental budget prepared for Ferrovorm (an IDC company), and consisted of 40,000 cards, 30,000 of which were the untyped input (format 4) cards. Doing a “what-if” was a real mission just to find the relevant card! It then took 3 hours to load the cards, and another 3 hours to calculate the model. 6 hours for 1 run!

Around the middle of the 70s saw the introduction of terminal-based modelling systems, meaning that communication with the computer was done via a keyboard on a terminal. This was a huge advance as one no longer needed to load thousands of cards into the computer and calculation times were significantly improved – minutes rather than hours. The terminals could either be on-site or remote. The first remote terminal I used was a “Silent 700” manufactured by Texas Instruments in the USA and could communicate over the normal telephone lines (provided there was no “crackle” on the line or thunderstorms in the area). One would dial up the mainframe computer on your telephone and when hearing the signal, quickly plug it into the rubber cups on Silent 700.

Silent 700

Spreadsheets were still unheard of at that time, so the modelling systems were high-level programming languages. The two available at that time were FCS (later changing its name to EPS) and available on the IBM mainframe computer, and IFPS, available on the ICL mainframe computer. The main difference between the two was that FCS calculated row-by-row, and IFPS column-by-column.

Models built in these languages needed big mainframe power, only available in computer bureaux or large companies. For us at that time it meant buying computer time from companies like Old Mutual and Currie Motors from midnight to 8am (if we weren’t working through a computer bureau which was expensive). One run of the model I built for the JD Group would cost R100,000! (But still they felt it was worth it).

Then around 1980 along came the Apple II computer, with memory upgradeable to 64k. Around the same time a modelling language called Micromodeller made its appearance.

To my amazement the Micromodeller “language” was the same high-level language as EPS – but it ran on an Apple! A lot slower, but so what! The Apple computer and the Micromodeller software sold for a once-off sum of around R6500!

With the advent of the Apple came the very first spreadsheet – Visicalc – comprising 8 columns and 64 rows and only available on the Apple. At this stage it really was only a “Visible Calculator” but this heralded the advent of the spreadsheet.

Microsoft tried to persuade the authors of Visicalc to make the software available on its operating software – PCDOS (becoming MSDOS) – but they did not or could not. This opened the way for Lotus 1-2-3 to jump into the gap.

The first Lotus 1-2-3 spreadsheet was still only a one-sheet system, but with 64 columns and around 1024 rows, significantly enhanced from Visicalc. (It was created by a former employee of Visicalc).

Lotus 1-2-3 became the de-facto modelling spreadsheet of the time followed several other rival spreadsheet programs, e.g. Framework, Multiplan, Supercalc. Excel, and Quattro-Pro (Lotus’ main competitor).

Then IBM (who had acquired Lotus) made the decision to move away from desktop applications and concentrate on its communications software (Lotus Notes), and this opened the door for Excel.

Today Excel is the de facto modelling system worldwide.

How have people evolved (or not) using Excel? Are things getting better or worse as the proliferation of spreadsheets grows with the work force?

According to Human, “Depending on the level of tertiary education, whether it was financial in nature, their post study experience, profession and their activity in their current employment – whether financial or not, we find quite a significant variance in modelling knowledge.”

Typically, most trainee delegates overestimate their Excel knowledge and capability – miss construe their knowledge of Excel (data management and data manipulation) – and often are deficient in those specific functions applicable to financial modelling.

The knowledge of basic accounting, financial statements and corporate finance theory varies wildly dependent on tertiary education, profession, and involvement in matters of corporate finance.

Given the (sad) lack of education pertaining to financial modelling at our universities and in the profession (SAICA, CIMA, CFA) etc., it is quite understandable the area of least knowledge and competence is financial modelling best practice methodology.

Hence the creation of certification institutes like the Financial Modelling Institute.

Human concludes, “Provided that aspiring modellers have been on a quality training course, I think that they are getting better at modelling and the application of best practice modelling methodology.”

How has functionality improved over that time (Excel and other tools) to support Financial Modelling?

Both Lotus 1-2-3 and Excel were originally 1-sheet spreadsheet systems and were competing with the more powerful and flexible financial modelling program-type systems (EPS, IFPS & Micromodeller).

Spreadsheets, however, were easier to understand, and their popularity increased, urging Lotus 1-2-3 (at that point the most popular package) and Excel (nibbling at Lotus’ heels) to enhance their systems. Lotus’ response was to bring out multi-sheet spreadsheets (now calling them workbooks), thus increasing the dimension of the spreadsheet from 2-dimensional to 3-dimensional. Excel followed suit, improving on Lotus’ offering dramatically. (In Lotus it was initially not possible to group the sheets and insert formulas into all the grouped sheets at once. If you did group the sheets, all the sheets in the group took on the characteristics of the 1st sheet in the group, often resulting in an unholy mess).

Then came the facility to link workbooks by formula – a major advance in spreadsheet technology.

Around 2003 Lotus 1-2-3 emerged with its own macro (programming) language as part of its spreadsheet offering. This improved its functionality, and it became a real contender to the program-type of modelling system. Excel shortly followed suit with its macro language. This original macro language (now known as “Excel 4 macro language”) still exists in Excel but is no longer supported by Microsoft – so no help screens available. A pity, in my opinion, as this macro language is far easier to understand than VBA. (I still use it in preference to VBA).

As the memory in PCs increased, so did the size of the spreadsheets. Today Excel spreadsheets are limited only by the memory of the computer.

The ability to link Excel spreadsheets to other Microsoft applications (like MSWord) further enhanced their functionality.

What's the single thing that remains critical as the rise in demand for Financial Modelling increases?

According to Theron, “As far as building good models is concerned, knowledge of model structure is still a limiting factor, as people still tend to do things like hard-coding data, do not use drop-down lists to minimise errors of input, do not display warning messages, etc.”

According to Human, “Of course, the very first step in building a successful model is to really understand and comprehend the business case.”

There are four main building blocks for successful financial modelling, namely: -

a. Essential Excel for financial modelling

b. Basic accounting and financial statements

c. Corporate Financial metrics and evaluation processes

d. Financial modelling best practice methodology”

The structure of the model and the need to understand the implications of changing the input data appears to be the most critical aspect of understanding Financial Modelling.

Where are your good places (links) to find out more on the topic

Given the wildly ranging skills in financial modelling, Colin’s financial modelling training company Goalfix Financial Modellers - introduced the “Course Selector” on their website.

It is simply a method to assist delegates to understand “where they are” when it comes to modelling.

There are also numerous other training providers on the Financial Modeling Institute (FMI) website which includes Model Citizn, Plum Solutions amongst others in Australia and around the globe.

There are also a fair number of financial modelling templates and example models both paid and free on the Eloquens website which help with showing what a good model looks like.

If you want to get a bit more technical in the financial modelling space download the Financial Modelling Code or wait for the upcoming series with Kenny Whitelaw-Jones on Financial Modelling standards.

How important do you think the skill of Financial Modelling is in today’s business world (by Human)?

In our (early) days, there were no rules, no conventions, no guidelines – it was all homespun.

Today there are a number of recognised financial modelling standards and codes of best practice namely F1F9 (FAST Standard), Financial Modelling Code (ICAEW), Corality (SMART) and Best Practice Spreadsheet Modelling Standards (BPSMS) and a plethora of approved training providers per the Financial Modeling Institute website .

All the trainers employ a best practice methodology and many of whom publish free guidelines available on the internet. Additionally, you can purchase model templates from Eloquens, eFinancial Models and CFI to name a few as well as getting access to free templates.

Understandably, if the “modeller” has never seen a properly built model and doesn’t work with “proper” models built by colleagues, the process is something new and not always intuitive.

Self-education is available to all in the modern world – and serious practitioners of modelling have numerous sources of information and aids available.

Of course, too much, too easy doesn’t always make for success. You need to ride the bicycle to get good at it.

There is becoming a much greater awareness about financial modelling globally and the benefits of properly built, flexible financial models.

350 people attended Human’s presentation at the Finance Indaba in South Africa, the most he had seen in a long time wanting to learn more.

Human supports the FMI and the different levels AFM (L1), CFM (L2) and MFM (L3) that they offer, given its importance and believes financial modelling should be a professional qualification, like the CFA qualification for investment and banking professionals.

Financial modelling should become a corner stone skill for all accounting and finance professionals heading into the digital era.

How does all this disruption, AI and automation talk impact this topic

Despite the development of automation in many areas, including financial modelling, the skill of understanding Excel, Accounting, Finance, Business and Problem Solving is still very much human led.

Design thinking and customer centric problem solving will not go away anytime soon (both human focused) and the fact remains there is no AI that can build an integrated 3-way financial statement model means that humans will still be required in building and using financial models.

Whilst technology will enable us to build models quicker and visualise them in ways we have never done before using software like Modeler and PowerBI if anything humans with the skills of financial modelling are still very much in demand in today’s digital world with all the data we have access to.

The inter-connectedness of data via APIs will even further accelerate this demand for a combination of data science and financial modelling where these skills start to converge.

If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App

If you want to find more information on financial modelling and content visit the Model Citizn website.