Creating a visually appealing and compelling story can be challenging. Following standards is often easier when building financial models but can be far more subjective when it comes to impactful visualization and storytelling. Three main factors to keep top of mind when designing great visuals and dashboards are:
1. Design with a purpose (explore vs. explain)
2. Less is often more (eliminate clutter)
3. Tell a clear story (reading order, pre-attentive attributes, Gestalt principles)
VBA is a key component of automation & transforms tasks from hours/days into seconds. Although many tasks previously performed in VBA can now be performed with Power Query, Python & other languages, VBA still has an important place, generally a lot simpler, for financial modellers who are not hard-core coders.
A good article that provides a nice simple set of principles for folks to follow & links to numerous terrific websites for people to learn more.
Monte Carlo analysis is all about managing uncertainty. This is a great article by Kerry and Lance using really good practical real life examples regarding the electricity industry and the Christchurch (NZ) earthquake. It's important to give decision makers the best view of possible future outcomes with the range and associated confidence levels ... compared to just the standard High, Mid, Low forecasts that so many do. Monte Carlo simulation is becoming more important than ever along with the other fundamental skills of Financial Modelling.
Scenario planning and analysis is about describing a future that doesn’t yet exist based upon what we believe will be true. It's about uncertainty and the possibilities that might arise & therefore the need for agile decision making. Scenario management encourages us to use many different assumptions (running them in isolation or together) and track the results against each other and reality. As our assumptions change over time, we can easily pivot from one scenario to another that we believe will be more reflective of reality.
Planning to pursue a career in financial modelling? There's no shortage of free resources to help you improve your skills & hundreds of training courses to choose from. Some come with a Certification (e.g. FMI, CFI & PFI). You should consider what you want to achieve, how you best learn, how long it will take, the associated costs and benefits of the respective qualifications/certifications. This article is a helpful start along that journey.
Sensitivities are about seeing how changes in an identified variable impact certain outputs and outcomes. When probabilities and elements of uncertainty are overlaid onto our analysis, sensitivities can tell a whole lot about our business environment and not just about the numbers. When we thoroughly understand how financial and non-financial variables impact each other, we can more effectively manage our business.
"What gets measured gets done" & "You can’t manage what you don’t measure" & we humans like to “game” the system. Financial modelling depends upon the integration of measures that drive business results, so it's essential to invest the time to identify KPIs that capture developing trends so the insights they offer are made visible to decision makers across the business.
Dashboards often used to show / track KPIs.
When you see Dynamic Array/s (“DA”) functions, they'll blow your mind as you start to think about all the implications for modelling, the countless hours they can save you and the ease with which your models can be extended and adapted. DA formulas are self-replicating and can reference all the values in a table throughout the entire workbook.
It’s never been more important to have a well built, flexible, robust and user-friendly financial model to use to predict outcomes. It’s times like these that a financial model really proves its worth. With a global pandemic unfolding and changing daily, having a model that can you can quickly and easily update is going to make it so much easier to make the quick decisions.
People that have worked with spreadsheets are not immune to the possibility of errors due to its incredible flexibility (a blessing & a curse).
A common statistic is that >90% of spreadsheets contain serious errors yet >90% of spreadsheet users are convinced that their models are error-free. This article contains some great tips for error trapping & detection.
This article provides guidance on use of hyperlinks & Workbook structure as follows:
1. Inputs (data and assumptions);
2. Workings (line by line logic/calculations);
3. Outputs (raw answers coming out);
4. Analysis (e.g. trends & ratios);
5. Application (value?, deal?, raise debt?, IPO?, buy or sell company?)
Data sourcing is the process of gathering the data necessary to build a financial model. Key parts include: sourcing data; data quality; interpreting & using the right data; automated data extraction; & a data dictionary. They're all necessary to develop a holistic approach to understanding & sourcing the appropriate or best data.
Corporate finance theory is very broad, but we believe theory and application of strategy, planning, M&A, finance, valuations and financial modelling are all connected and need to function coherently if you want to be successful.
Making financial models User-friendly should be a key focus for model builders. Always beginning with the end user in mind & applying a few standard concepts are key (e.g. design unique Inputs & Outputs, ensure the model meets client's expectations in a way that abides by published standards, ensure flexible key value drivers & assumptions are easy to find & update).
The Development stage is a crucial part of building a financial model, which should be built using one of the recognised standards or methodologies (e.g. Best Practice Modelling (BPM) Standards). A detailed scope should be developed & the model built optimising the trade-offs and balance of Robustness, Flexibility & User-friendliness. The model should be developed & constructed with various end-users in mind, building in quality & accuracy (e.g. error checks, alerts) from the start.
Scoping a model for business valuation purposes is about assessing the work that needs to be done & data that needs to be collected for a valuation to be undertaken. We need to understand the reason for building the financial model and how large and complex the business operations are. Another critical element of Scope is time: the time series (e.g. monthly -v- annual, actual -v- forecast); and the time frame that things need to happen (including the date the valuation relates to).
The fundamentals of 3-way cash flow modelling is essential to helping businesses achieve their goals and is a key tool needed to forecast the future. It refers to modelling the 3 financial statements (P&L, BS and CF) that are integrated through formulas and assumptions. They provide a significantly higher level of accuracy into the net changes in the cash position period on period. A 3-way cash flow model allows us to forecast & analyse changes in the P&L, BS & CF Statements.
Project Finance models are complicated models to build with many inputs from across the project. What happens when a project reaches Financial Close? The model maybe converted into an Operational Financial Model. It's not practical to go back and re-create the model from scratch, but there are some useful steps to make a hybrid PF-Operational Model easier/faster to work with.
Underlying nearly every deal is a Financial Model that holds the complexity, logic & structure. It is often said that if you understand the financial model, you understand the deal.
Project Finance is arguable one of the most complex forms of financial modelling, but the outcomes most beneficial to the public.
It's not until we have to create financial models for someone else that we realise we need disciplines, e.g. labelling, structure, simplicity, data validation, error checks, protection & flexibility to grow & accommodate a variety of situations. A new Excel feature helps address several of these better than anything else: Tables. Tables can add a ‘physical’ structural element that facilitates organising a model’s ‘logical’ structure with dynamic ranges that automate formula documentation & consistency. Think 'Kind Vertical Lists'!
This article seeks to define the differences between Deal & Operational Models (as they are designed to meet two different purposes), when & how each should be used. A Deal model is used specifically for a transaction (i.e. once), whereas an Operational model is used on a regular basis (e.g. monthly, quarterly or annually) by a business to manage its ongoing operations making tactical & strategic business decisions.
The difference between a Spreadsheet & a Model for FP&A is that a Spreadsheet is usually quite static and used to manipulate & report data, whereas a Model is used to test assumptions, predict & analyse future outcomes. A model provides the reader with useful information to guide decisions that either mimic the actual business or predict what the business would look like if certain key financial decisions were made).
This is a great piece on the history of spreadsheets & financial modelling from the 1970's (e.g. Visicalc) through to today (e.g. Excel). According to the authors, even after all this time, model structure is still a limiting factor in building good financial models, as people still tend to use hard code data in formulae and don't use drop-down lists or display warning messages, etc., and the first step in building a successful model is to understand and comprehend the business case.
A successful financial modeller needs to be able to listen to someone’s conceptual vision, extract the important elements and model the relationship between the key drivers and desired outcomes.
This means working with incomplete data which causes grey areas to emerge requiring gaps to be filled & assumptions to be made. So to get more comfortable with the grey, modellers need to focus on Structure, Sensitivities, Scenarios and Sense checking financial models.
Lance has been leading the charge on the development and publication of a series of articles "From Spreadsheets to Financial Modelling". The most recent articles are republished here for your convenience.
If you want to find out more or review the article series be sure to download the Financial Modelling App.
If you want to find more about Lance, visit the Model Citizn Website at https://www.modelcitizn.com
Thompson Group Holdings Pty Ltd
Copyright © 20 Thompson Group Holdings Pty Ltd - All Rights Reserved.
Liability limited by a scheme approved under Professional Standards Legislation.
Powered by GoDaddy Website Builder