There is no fixed form for a financial model, as it is always tailored for the type of business, and various models serve different purposes. However, a good design that is easy to follow and allows smooth data flow has a big impact on the model’s scalability and efficiency of use.
An integrated model that contains BS, PL and CFS is usually desirable for forecasting, as it reflects a more complete picture of the business. I’d like to share a few ideas in structuring this type of model. There is no right or wrong—the best structure is defined by the business and how good users feel about it.
Keep it neat and simple. Try to minimize the number of tabs, as long as the source data and inputs are categorized organically to support the final output. Navigating through too many tabs will make it hard to find your way back. Keeping similar information in the same tab can enhance efficiency and accuracy. I’ve seen models break output financials into BS, PL and CFS by month, quarter and year, which ends up creating nine tabs for similar information. Instead, data can be sorted into two tabs: a master sheet with BS, PL and CFS by month, which serves as a data pool; and a reporting tab customized to the layout you wish to see, such as financials by quarter, by year or both, with ratios and so on. You may also find that consolidating BS, PL and CFS into one tab will enhance visibility of data links.
Place assumptions in applicable worksheets. You may often see modelers put all assumptions in one tab in an effort to keep things looking neat and tidy. However, when the model grows larger and more complex, the artificially created inter-sheet links will require extra time to navigate and extra effort to scale or revise. For example, in most cases, you will have an operating expenses tab that contains numerous line items that don’t share the same assumption, input and projection method. Creating assumption columns next to these items will give you a straight look of how the projections are made. Be sure to shade the cells so you don’t miss the input areas.
Set up a warning system to trap errors. Ensure all data tie out and reconcile. It is critical to set up cross-check, total tie-out rules to ensure accuracy when flowing through tabs. Conditionally format any unreconciled data or imbalance in red so you won’t miss it. Since BS often sees imbalance, placing these rules is essential to identify errors at each step and correct them so you don’t walk into a disaster.
Ultimately, the goal is to achieve simplicity in structure so people can follow it easily—to make it look like a book and read like a book. By doing so, you will help users—the CEO, CFO, COO and other top executives—see a much clearer future picture of the business and be able to make better decisions.
