Carl Seidman, of Seidman Financial discusses how CFOs can architect and construct better financial models. We also chat about tips to becoming a better reviewer of financial models prepared by others.

Conversation to focus around three areas:

i. You are responsible for the creation of a financial model of some sort for your company that will involve inputs from several departments. What are best practices to go from “Carl we need you to model this out” to a completed, published model?

● Carl: “Where people can often go wrong is they see the creation and management of the financial model as the goal. But instead, they should be viewing the financial model as a way to facilitate conversations and better decision-making. I often see companies where finance is in control of the model as well as the planning and forecasting process, but this traditional approach brings about inefficiency in planning, a fractured system of accountability, and a poorer buy-in from non-financial groups. In contrast, finance should not be responsible for the planning process – only the financial planning elements of the process – at least in the beginning phases. As finance people, we’re not best suited to understand the intricacies outside of our function – the departments heads are. Instead, department leaders should each be responsible for their own planning but with direct coordination and collaboration with finance which may ultimately manage the model. Once ‘modeled out’, finance can further inquire and nudge other departments and become deeper entrenched in ops.

● Companies are often surprised by my approach to financial modeling and planning in that I spend a lot of time upfront conducting diligence, drafting and plan, and building a process even before I start modeling. How often does a financial modeler say “you know, I built this model months ago, and if I’d known we’d be moving in this direction today, I would’ve built this model differently.” The goal is to never have to say that. To do your research and planning upfront, build a flexible dynamic model, so that it can grow and evolve with the company rather than retire and replace it every few iterations.

ii. You have just started a new position as CFO of a company. Your head of FP&A hands you a financial forecast model that he has prepared. How should a CFO approach the review of a model that they may or may not be intimately familiar with and get to a point of reasonable confidence that the model is working as intended?

● I constantly remind (humorously) the groups and companies I work with that their models are going to be wrong. It takes some of the seriousness and aspiration for perfection away from the mandate. But that light-heartedness and attitude doesn’t mean that we don’t take modeling and forecasting very seriously. Forecast models are used by almost all mid-sized and large organizations for very important decisions. But those decisions aren’t just based off of precision – they’re based off of data, expectations, predictions, guesses, and most importantly…assumptions. We can make conjectures about the future and be reasonably correct and fairly accurate, while knowing our actual numbers will be incorrect. This is the mindset a CFO should adopt. Rather than obsess about accuracy, obsess about assumptions and the related confidence and risk.

● Model integrity comes in two forms: One, mechanics; and two, intelligence. Mechanics is all about the quality of the model build, from layout, to formulas, to easy of use, data importing, and synthesis. A model without strong mechanics is inefficient. But a model without intelligence is worthless. You can have a model that’s clunky but contemplates the right intelligence and it can be leveraged. Whereas if you have a model that’s laid out perfectly, has all the bells and whistles, but doesn’t represent reasonable economic assumptions, it’s worse than useless – it’s dangerous.

● If we’re going to get to a point of reasonable confidence, we need to test the model to see if it gives us the outputs we would expect to see when we push it. As smart finance people, we can often perform back of the napkin calculations quickly and we can vet those assumptions with the refined analysis that the model can provide.

iii. In your mind, what are some general model best practices and landmines that you have seen in model design and preparation?

● Best Practices - A point I raise to all of my clients and groups is: a model is never yours and it’s not built for you. In FP&A, sometimes our models are MANAGED by us, but they’re almost always built for the benefit of others. If you’re going to build a model, don’t do so in the same way and style you’ve always done it and expect others to come around to you. Ask what it’s going to be used for, who it’s going to be used by, what are future uses? How does the model need to be built to support this?

● I’m typically very finicky about laying my models out in such a way that even the most unfamiliar modelers can make sense of it. An advanced modeler shouldn’t need to spend an hour understanding how it’s built, how it’s managed, and where drivers connect. It should be intuitive and sometimes it’s harder to make something clean and intuitive than complicated and self-serving.

● I typically try to use summaries and graphs to illustrate points rather than financial outputs that take time to more deeply analyze. I tend to use various font colors to bring attention to inputs/drivers vs. outputs/formulas.

● If I’m using Excel or Google Sheets, I tend to add in dynamic functionality which allows my models to grow with minimal or no formula updating.

● Landmines – Some of the biggest landmines in modeling (at least in Excel and Google Sheets) is making the model too complex unnecessarily by using bad practices. Bad practices that cause the most problems are having too many links, too many manual processes, and too much data importing/updating.

● To be most specific, I’d caution people to avoid linking cells across multiple worksheets, forming physical links that are easily breakable, or using approaches that require hours of work to update and are easily exposed to errors.

● I’ve worked with Fortune 500 companies that run multi-billion dollar books of business from Excel models and the models are so clunky because of these bad practices. Again, it should be as simple, clean, and flexible as possible. You don’t need to be a super advanced modeler to be able to do this, but sometimes you have to be advanced to be able to think so simply.

iv. If I myself w...