Do You Model Off against the Masters of Financial Modelling?
This article is co-written by Lance Rubin and Anders Liu-Lindberg
There’s a saying when it comes to forecasting and budgeting, it is only as good as your financial model. Or at least there should be such a saying because a well-constructed financial model will determine how flexible, accurate and therefore valuable your forecasting and budgeting could become. A well-built financial model is as much of an art as it is a science.
In this installment of the Finance Master series we look at budgeting and forecasting and more importantly the financial model that supports them. For this I have armed myself with a true master of financial modelling, Lance Rubin. Lance is an ex-investment banker, virtual CFO for a fintech start-up Banjo and works in the KPMG Financial Modelling team.
He has spoken at twoModelOff (Financial Modelling World Championships) Global Training Camps in HK and Sydney, hosted webinars for CCH (an accounting and tax publication company) and delivered numerous workshops to Finance professionals on behalf of The Outperformer, whilst also running his own consultancy firm called MODEL CITIZN. His firm builds models, advises and trains people in this highly sought after skill. Lance has also written many blogs on the topic.
Why do weeven need budgeting and forecasting anyway?
For this article lets first clarify a few of the words used to avoid confusion and provide clarity of terminology. People often use different terms interchangeably and whilst fundamentally the process to derive the numbers might be the same, the nature of their use is very different. In chronological order, they are:
1. Planning or the plan – this is often the strategic plan over many years (often 3 – 5 years, anything longer is somewhat meaningless, unless you are building an infrastructure PPP financial model).
2. Budget – this is an annual subset of the plan or one that is approved by a board at the start of the year to guide execution and target setting in terms of revenues and costs mainly.
3. Forecasting – this is a more dynamic assessment of future performance based on actuals to date and a best endeavor “estimate” of the forward view (might be to the end of that financial year or the entire plan).
Forecasting often includes the assessment of actuals against the budget and plan to see whether adjustments need to be made in terms of the business logic or assumptions that were baked into the budget that are proving to be incorrect (either in a good way or a bad way). It may even result in revisiting of the strategic plan and potentially the entire business model if proven to be unachievable.
Let’s start by taking a step back and discuss why companies need a budget and forecasting the first place? For as long as businesses have been operating they have been preparing an annual budget which also facilitates target setting and resource allocation which sets up the execution of the longer-term plan for the entire company to follow for the years ahead.
Staff and business performance needs to be measured in order track how well it is being achieved against strategic goals. What gets measured gets done and the budget makes sure that all staff in the company are on the same page.
In more recent times, however, change is occurring so fast that any budget that is set at the start of the year is quickly less relevant and in some cases completely out of sync with reality. Ultimately nobody can predict the future in absolute clarity, however without measurement and foresight things can quickly get out of control or worse head towards insolvency.
The budget, and perhaps even the plan, simply becomes redundant quicker today than it used to be. This is resulting in the increased demand for a more agile performance measurement and more regular forecasting supported by financial modelling. It's primarily why companies are now moving to rolling forecasts or more simply put reforecasting when significant assumptions change. The nature of budgeting and forecasting is evolving. It is also not possible to truly understand the relativities of one decision against another (including making no decision) without some form of predictive analysis or financial modelling.
True masters might even do forecasting on a more regular basis (e.g. monthly) or potentially real-time meaning the model is significantly more dynamic based on various drivers that are updated with actual data feeds enabling a highly interactive forecast. This helps companies budget what resources are needed to deliver the forecast and allows them to act far quicker in the event the previous forecast suddenly doesn’t look so bright when compared to reality or the unrealistic budgets and plans set at the start of the year.
In simple terms, it’s all about planning for the future by looking at where you are today and ahead as close to real-time as possible. Without forecasting, companies would simply steer blind (think Titanic and iceberg) and more likely than not to make the wrong choices too often.
Importantly, the process of reforecasting needs to be efficient as it's now required to be done more frequently. The financial model therefore needs to be robust and effective at representing the changing business conditions in a streamlined manner with fast turnaround time to maximise the value of the decision the moment it is required to be made.
How to build a master financial model?
First, you need to know all the important functions and formulas available to you in Excel, including shortcut keys. Typically, you start building in Excel but if you don’t know some of the key formulas (and shortcut keys) your ability to build a state of the art dynamic financial model quickly, your efforts will be in vain. Hardcoding when things get complex, compared to finding a dynamic formula or an "=if" statement, is the first sign of failure in building a master financial model as it will be inflexible with hardcoding
If you only ever use VLOOKUPS and have never heard of INDEX and MATCH or don’t know how to use OFFSET, then you still have some studying to do. If you have never tried one of the many Modeloff past questions, perhaps it's time. Lance wrote a solution to Precise Debt Modeling (one of the Modeloff past questions). Here is the free link to his solution and you can find this question amongst others on the Modeloff website.
Next step you need to know which programs to use to accelerate your modelling like Modano and Modeler as shown in this YouTube channel. There are of course larger systems that do forecasting outside of Excel, but be careful when giving up flexibility for perceived process efficiency and cost.
Sacrificing the flexibility to change business logic easily can be detrimental to the dynamic process, forcing you back to Excel as the expensive ERP system doesn't do what you need it to,without an expensive consultant doing it for you. Lance has been able to reduce 75% of tasks related to forecasting and planning whilst not losing flexibility and without the big price tag.
If you’re stuck using only basic elements of Excel, you won’t become a master either. In addition, to knowing what tools to use, you also need to consider what modelling standards to apply. There are three recognised standards currently Best Practice Spreadsheet Modelling, FAST and SMART. The Best Practice Spreadsheet Modelling Standards are the most comprehensive and contain practical design concepts for building models. There is a lot of consistency between the standards (as you might expect).
Lastly here is a simple yet comprehensive three step checklist for becoming a financial modelling master.
1. Know your Excel and other software applications that enhance it like Modano and Modeler for 2-way dynamic visualisations.
2. Know how to integrate business logic into your Excel model without hardcoding in formulae.
3. Know your financial modelling standards (hint...if you use Modano this is done for you as Modano builds models compliant with the BPSM standards).
All of the above requires training and discipline but that’s like everything else you have to do to become a Finance Master!
What do the true financial modelling masters do?
The above will only get you started. Practical application of the above in the real business world as well as completing past questions and perhaps competing in ModelOff will take your foundation of skills to another level.
A further option is also becoming accredited or certified Modano practitioner and an advanced financial modeler from the Financial Modelling Institute.
Lance is A+ certified on Modano and an Approved Training Provider for FMI in addition to having built models for many large infrastructure projects in public and private partnerships with governments and IPOs
If you want to learn more from a master click on his blogs on his website which contains links to more content including other models.
In the words of Carol Dweck it's not I CAN'T (“do financial modelling”), it's not YET. Take a growth mindset and you too can become a master at planning, budgeting and forecasting.
Conclusion
If you want to learn more in 2019 and also understand how you can upskill in FM be sure to reach out to us or stay tuned.
I encourage you to also take a peak at my past/present articles on financial modeling which is the foundation of business decision making, planning and forecasting.
We have also redesigned our website to help you along your journey of levelling up in this space.
We will continue to discuss this topic and you can click to follow me on Twitter or LinkedIn or subscribe to our short but sweet newsletter.
Be sure to check out our pods and video page.
Here are also some past/present blogs that might be of interest.
Forecasting skills doesn't guarantee good modeling skills
How Technology Will Change FP&A Forever
Why so many unemployed accountants are already struggling..and how I can help?
Father battles cancer, son battles career extinction
Why being a 1-man/women VCFO business is a really bad idea?
Why Vlookup (vs Index(match) shows your lack of embracing change, innovation and better Excel skills
Financial Modeling Innovation: Predictive analytics vs Financial Modeling
Financial Modeling: Diamonds in the rough
Why Monte Carlo will change the way you make financial decisions and think about scenarios?
Driver Based Planning & Forecasting in the context of FP&A
Inside the mind of a spreadsheeter vs a financial modeler
Virtual CFO the Good, Bad and the Ugly
NEED HELP searching for the best advisory tool in the market?
Part 2 of Finance Innovation - Data Analytics / Big Data is not Financial Modeling
Why I disrupted my own role and you should do the same?
Consulting, Advisory, Finance and the art of influencing is about to change forever.
Your Financial Model can save lives !
The Machines Are Not Coming… they are here!
The Demand for Financial Modeling Skills Reaches new Heights
Lance Rubin is the Founder of Model Citizn, partner of theOutperformer, approved training provider to the Financial Modeling Institute and Group CFO for SequelCFO.
I have more than 20 years of combined experience working in model audit, investment banking, corporate finance, finance business partner and Fintech CFO.
Organisations I have worked with include PwC, KPMG, National Australia Bank, Investec Bank and Banjo small business lender.
I have a YouTube channel dedicates to the Future of Financial Modeling and also provide access to Models via Eloquens with thousands of viewers and downloads.