Designing Models In Excel
A well designed model in Excel makes following someone else’s work much easier.
Excel is the most used and abused piece of software in the world. One of the most important skills everyone should learn using excel is how to build a model using good design principles.
Most companies, and most Excel training programs do a poor job of teaching design principles when it comes to building business and financial models in Excel. Learning to follow a set of standards and principles is critical to creating user-friendly and well-structured models instead of “Frankein Model’s. Frankein Models are those models that are pieced together and violate many of the good design principles and after spending some time with the model we start over because it is easier to do that then to try and understand what the end user has built.
Today’s blog provides a list of resources that teach good financial modeling design principles. The list of resources includes:
3 formal modeling standards that can be followed to build well-structured financial models
List of websites/blogs that provide good design principles when building, robust, well-structured financial models
Videos that discuss good Excel spreadsheet design and financial modeling principles
Books that teach how to build models in Excel using good design principles.
Formal Modeling Standards
Three formal modeling standards one can use when building financial models include:
FAST - Flexible, Appropriate, Structure, Transparent
The Smart Methodology
BPMS - The Best Practice Spreadsheet Modeling Standards
FAST - The FAST is a formal modeling standard that some professional financial modelers when building a model. The full standards can be downloaded from the FAST website: FAST Modeling Standards Document
The FAST acronym stands for:
Flexible - To be effective, the structure and style of model require flexibility for both immediate usage and the long term.
Appropriate - Models must reflect key business assumptions directly and faithfully without being cluttered in unnecessary detail.
Structured - Rigorous consistency in layout and organisation is essential in retaining the model’s logical integrity over time, particularly as a model’s author may change.
Transparent - Effective models are founded upon simple, clear formulas that can be understood by other modellers and non-modellers alike.
SMART - The Smart methodology is another formal modeling standard used by professional financial modelers. This methodlogy provides 10 guiding principles that help ensure your model is following best practices and increases the chance your model will be successful. You can download the guide at the following link: Download 10 SMART Guidelines for financial modelling (corality.com)
The Ten principles covered in the guide are:
Adopt guidelines, not rules
Think about the end user
Focus on transparency
Keep it simple
Use an intuitive structure
Communicate a story
Incorporate powerful analysis
Build for the future
Keep the model neat
Make it easy to find errors
BPMS- The third standard is from the Spreadsheet Standards Review Board. The SSRB developed the Best Practice Spreadsheet Modeling Standards or BPMS. This model was designed by former investment bankers who felt modellers needed a standards guide to ensure everyone had a guiding set of principles to use when building models. The BPMS has three general areas and provides multiple sections to guide us within each of these three areas. You can download the standards at the following link: Download | Spreadsheet Standards Review Board (ssrb.org). The three general sections are:
General Concepts -Covers areas such as workbook purpose, sheet content, assumptions.
Workbook Structure - Covers cover sheet, work book sections, table of content, section structure, and workbook navigation
Sheet Structure - Covers areas such as sheet titles, sheet type consistency, grouping rows or columns
Websites Discussing Good Modeling Design Principles
Next are a few websites that teach good principles for using Excel.
The first site on my list is PERFECTXL as they provide a lot of great Excel resources including Excel Add-ins, training, etc. They also have a guide available for download that covers principles for good Excel use. Principles of good Excel use // Excel Advanced Training // PerfectXL
The Guide lists 36 principles to follow and talks about three high level principles we always need to keep in mind:
Keep it simple
Keep it safe
Keep it going
The second website on the list is Spreadsheetweb.com and it is a very simple guide that focuses on the four main areas of a financial model and how to build a model in a clean simple manner. Financial Modeling in Excel (spreadsheetweb.com) The four main building blocks of a Financial Model are
Inputs
Data
Calculations
Outputs(Cells, charts, pivot tables)
The third website is wallstreetprep.com and the site is designed for those interested in Investment Banking. They provide a very lengthy guide that provides a set of Financial Modeling Best Practices and details on how to structure your model. The site discusses the different type of models that are commonly used on Wall Street and the best practices to use when building these models.
Financial Modeling: Excel Best Practices - Wall Street Prep
The fourth website is from the Corporate Finance Institute (CFI) and they have a blog called A complete Guide to Financial Modeling.
Complete Financial Modeling Guide - Step by Step Best Practices (corporatefinanceinstitute.com)
In addition to offering a guide on Financial Modeling the Corporate Finance Institute offers multiple training courses designed to teach one how to build a financial model.
The CFI guide covers the following:
Why build a financial model
Excel tips and tricks
Modeling best practices
Building the forecast
Linking the statements
Further analysis (DCF, sensitivity, M&A, and more)
Presenting results
Video resources for learning Excel design principles
The first video is from the Corporate Finance Institute (CFI)
The video is easy to follow and is a great overview of discussing why we build financial modeling In the video we will be given a list of Excel Tips and Tricks, and a list of the 10 best practices for structuring a model. The guide also discusses some free training resources that the CFI makes available to all of us.
The second video comes from Excel MVP and master trainer Leila Gharani
In this video Leila covers 8 handy Excel practices to follow when deigning a spreadsheet. These are:
Input separate sheets to output
Define a purpose for each sheet
Have a control worksheet
Add workbook instructions
Keep file backups
Prepare for print
Consistent color coding
Format for appreciation
The third and final video is a webinar present to the CFA Society of Nigeria and is presented by the Financial Modeling Institute focused on Financial Modeling Best Practices.
The Financial Modeling Institute (FMI) is a great resource for financial modelers and they offer one of the best certifications available focused on building an integrated 3-way financial model in Excel. This webinar covers the following topics:
The Discipline of Financial Modeling
Top 10 Modeling Best Practices
Key Modeling Tips
Skills to check a Financial Model
Great Modeling Technical Skills
Books Teaching Excel Design Principles
The list below provides two books that do a good job of covering principles for designing robust well-structured models in Excel. Many books exist that focus more on financial models and building the models in Excel. I choose these books as they have high customer ratings and cover a lot of great material for modeling.
The first book is Using Excel for Business and Financial Modeling - Danielle Stein Fairhurst. I own this book and have found it to be a good resource on financial modeling. It covers a lot of great content on build models in Excel. The book contains a chapter that focuses on providing practical advice for designing your financial models and gives two lists one for the simpler project, and one for the more complex model that will have multiple end users.
The second book is Building Financial Models - John S. Tija. This book is another great resource for one to won that discusses building financial models in Excel. This book provides detailed guidance on using spreadsheet software to build financial models.
Conclusion
Building financial models requires learning and using good design principles. Many resources exist but all follow some basic principles. If I had to summarize what I believe are some of the key principles everyone should follow they would include:
Keep it simple
Never hardcode in your formulas
Make sure to build in checks and balances to the model
Keep assumptions, inputs, outputs, and data on separate tabs
Use color coding
I hope you found this list of resources available and please make sure next time you build a model in Excel to find a set of principles that work for you and follow them. Your model and your co-workers will thank you.