Data Manipulation

In January 2013, Prophecy delivered a training course in Kuala Lumpur titled “Advanced Financial Modelling and Data Manipulation in Excel”. The course title and content sat very well with Prophecy employees given our experience and recent discussions around the essence of financial modelling. Oftentimes, when asking or telling stories about financial modelling, people will mention valuations, cash flow modelling and integrated financial statements. However, the reality of financial modelling is often a focus on another area of expertise – data manipulation.

Financial models can have a variety of outputs – financial statements, valuations, charts and/or budgets etc. But what is more common among the models Prophecy builds is the requirement to manipulate data prior to developing these outputs. Manipulating data can involve anything from transposing it, summarising it and filtering it, to exporting it, importing it and cutting it and we are asked to do this in almost every model we build – far more often than we are asked for integrated financial statements. This led us to ask internally – what is financial modelling all about and what should we teach attendees in a financial modelling course.

The answer is that increasingly our focus has moved towards data manipulation skills. Excel functionality such as filters and advanced filters (as well as sometimes using pivot tables as a check or output) is combined with Excel functions, such as SUMIF, SUMPRODUCT, INDEX and MATCH. We discuss reconciliations, checks and cleansing data as well as methodologies for linking workbooks, a common practice prone to causing errors. And when we return from our training courses, our day to day client projects continue to see a majority of our development time spent on cleansing, manipulating and summarising data, before building any financial statements or developing valuation tools. In essence, we are practicing what we are preaching in training.

No comments yet.

Leave a Reply


1 + four =