Financial modeling

It struck me as peculiar that my accounting degree did not include any financial modeling. It was in the real world that I learned, initially as a CFO, and then in finance.

As a working capital financier, you see plenty of cash flow projections. Working capital finance is usually needed because people have made mistakes with their modeling at the outset. Financiers have to have own models into which they plug the numbers. It’s better than trusting the sanguine figures that are usually presented.

So that’s what we did.

As the spreadsheet guru, I was tasked with creating a model that integrated the balance sheet, income statement and cash flow projections.

Getting all three to balance is tricky, but crucial. Errors in assumptions become evident as the ratios go out of kilter, and that let’s you know if the numbers can be trusted.

Once the model is working, the starting point is to enter the last three months figures. If the assumptions and calculations are right, the most recent balance sheet will pop out from the input. If it doesn’t there are errors. Fix them, and you’re on your way.

The assumptions are key. They will guide the strategic planning, and the executive that has a grip on them is likely to succeed.

One of the best tools for getting a feel for the key factors in a business is also one of the simplest: break-even analysis.

To the uninitiated, break-even analysis is like a magic trick. Sitting in a pub, you can scribble down the variable expenses, then the fixed expenses, calculate the contribution, and tell a desperate friend why his business is struggling, and how long it will take to fix.

The financial model must include the results of a proper break-even analysis. Then, slotting in past figures will immediately reveal any misallocation of fixed and variable expenses.

The exercise of entering past figures has another benefit.

It provides the executives with confidence that the model works, and the understanding that by continuing to enter results they will know in advance whether something is out of line.

They become proactive.

They look good.

They are good.

The Excel mistake that changed the world

In 2010, two leading Harvard economists, Carmen Reinhart and Kenneth Rogoff, published a paper. They had found that debt levels exceeding 90% of GDP led to a decline in economic growth. Governments took notice, and policies were implemented to reduce debt, with sometimes draconian austerity measures.

George Osborne quoted the paper as he introduced his measures to repair Britain’s economy. We are still living with the consequences.

The problem is that their results were wrong.

The Harvard professors had used Excel, and had accidentally only included 15 of the 20 countries under analysis in their key calculation. It made a big difference to their results. Their finding of a decline in GDP of 0.1% was really a increase of 2.2%.

As their data had grown, they had failed to change the formula.

Excel is a great tool. It’s intuitive, easy to work with, and presents results well with graphs and pivot tables.

It’s the best tool for creating and checking complex algorithms, doing financial projections, and quick and dirty calculations.

It falls down when handling big data, data that grows over time, or there are multiple users making updates.

It’s easy to end up with multiple versions of the data, and then have to decide which is correct.

Excel makes entering data easy. But it’s also easy to make mistakes. Validations to guard against bad data have to be programmed in. Garbage in – garbage out.

Ray Panko, a professor at the University of Hawaii and the elder statesman of small coterie of research academics who study Excel errors, claims that 84 percent of spreadsheets contain some kind of materially significant error. Other economists agree.

Databases are not visual, not in the way that Excel is. The terminology and design can seem arcane. You can’t just open a database and start entering data. They’re not intuitive. They are also not as complex as they seem.

So, which should you use? – The answer: both.

It’s a matter of deciding which is the right tool for the job. It’s not necessary to decide up front. Using Excel to become familiar with the data is a great way to start a database project. When you get going, importing the initial data into the database is easy. so nothing’s lost.

Having an existing spreadsheet that should be a database is an excellent way to learn. The best choice for a project is one where you’re struggling to tie up the relationships in the data. What’s difficult in Excel is easy in a database.

Great, now the data is in a database, but now you need to present the result of your analysis. Simple. Export the summaries into Excel and do it there. It’s easy to do, and it’s the best tool for the job.

What about the cost? Unlike Excel, many of the best databases are open source and free.

So, how do you get started. Next time.