GOOD INFORMATION IS CRITICAL FOR ALL GOOD BUSINESS DECISIONS. MICROSOFT EXCEL IS ONE OF THE MOST COMMONLY USED TOOLS TO AID IN PROVIDING GOOD INFORMATION TO DECISION MAKERS. HOWEVER, STUDIES SHOW THAT CLOSE TO 90% OF ALL EXCEL MODELS CONTAIN ERRORS. A CUSTOM-BUILT EXCEL MODEL CRAFTED BY SOMEONE WHO KNOWS WHAT THEY ARE DOING CAN REDUCE THE RISK OF MAKING DECISIONS BASED ON INCORRECT DATA AND PROVIDES SEVERAL OTHER BENEFITS TO COMPANIES AT ANY STAGE OF DEVELOPMENT.
In today’s fast-paced business environment, leaders need the ability to quickly see the financial results associated with a variety of business decisions. Excel acts as an excellent blank canvas that can be used to model a wide variety of situations in a clear way. Ultimately, a custom model will allow the user to adjust inputs and see the financial impact of a variety of scenarios in a clean, easy to interpret dashboard, thus assisting the user in making better business decisions. Well-built models maximize simplicity and transparency as well as minimize the risk of errors. A custom-built financial model can provide the basis for any important decision by ensuring accuracy and interpretability of data.
BUILDING CUSTOM EXCEL MODELS
Most of the work that goes into creating a custom Excel model should take place before the actual physical building of the model. Before building the model, it is important to consider what decisions need to be made based on the model outputs and what the model outputs need to look like. Understanding the desired output greatly assists in architecting a model that is straight forward and easy to follow. A good model should be fully created mentally before it is created physically. The following are 5 key considerations when building a clear and easy to use custom Excel model:
COMPLEXITY ≠ ACCURACY
Complexity does not equal accuracy. Simplicity is important in modeling. Find a way to make the assumptions driving the model easy to understand and adjust. Hundreds of inputs, in most cases, will cause the model to be more confusing and less likely to be used. Finding the right balance between simplicity and complexity is difficult. One question to ask while modeling is whether added granularity will have a material effect in the outputs of the model. According to Albert Einstein, “The definition of genius is taking the complex and making it simple.”
STRUCTURE
Make the model so clear that new users can understand it without much explanation. Follow a pattern of having all assumptions on one tab, laid out in a logical and organized manner. Color code input cells, calculation cells, call up cells, etc. and make sure to have a legend outlining what each cell type looks like. Place check figures to ensure data is flowing appropriately and calculations are working as intended.
LOGIC
When writing complex formulae, break up formulae into several steps to ensure the logic is clearly presented and easy to follow. Doing as much as possible in a single cell is not necessary and ends up causing more confusion than the theoretical “efficiency” is worth. If a boss/coworker/client were to open the model, he or she should be able to understand the basic logic of the model quickly. There are over a million rows in a worksheet – do not be afraid to use more to make a calculation easier to follow.
TEST SENSITIVITIES
Once the model is built, take time to test the sensitivity of the assumptions. Understanding how sensitive the output can be to certain inputs will assist in understanding the limitations of the model and the needed accuracy of the inputs. No model is perfect, after all it is merely an attempt to predict the future. Being aware of assumption sensitivities will prevent outlandish outputs and hone in on more realistic results.
DATA INTEGRITY
Poor historical data will often lead to poor results or unreliable assumptions/drivers. Although history does not predict the future, often it is the best starting point available. Review historical data, ensuring it makes sense and is accurate. Confirm that the way the historical data flowing into the model is accurate. Poor data going into the model and poor assumptions used in calculating the outputs will lead to inaccurate results.
KEY TAKE-AWAY
Custom Excel models do not need to be overly complicated. Design the structure, logic, and layout to be simple and easy to follow. Good Excel models are easily used and updated. With clean and accurate historical data and appropriately granular assumptions and logic, the Excel model will fulfill its use as a guide in making informed, data-driven decisions.