Data drives decision-making; there is no doubt about it. But how do CFOs and controllers wrap their arms (and heads) around large, internally generated data sets? Accounting software packages typically have some capabilities for ad-hoc reporting but what if those are not enough? Or what if there is a desire to use existing data sets to project the future of the organization? And what if there are going to be changes in the future?
Using Microsoft© Excel to create bespoke financial models is an executive’s ticket to getting the most out of their data. Not all models are considered equal and what may work for one organization may not work for another. Preparing a customized model is the best way to analyze the data of an organization and generate outputs that are meaningful to management teams.
Building a financial model begins with setting the goals of the outputs. What is important to the entity and what will drive better decision making? One base-line output includes periodic reporting. Models can also drill down to a product or customer level and provide relevant information on the profitability of each. Calculation layers are included and add value to the data, such as the allocation of overhead to customer groups, that may not always be available in software packages.
Beyond historical data, good models also allow for future analysis. With a clear set of input assumptions, financial teams can deliver management with a projection of what is expected to happen in the future. And that projection can be adjusted for various scenarios.
CFOs often question: what would happen if we increased sales by 5% and shaved 1% off of cost of goods sold? Margin would increase on larger sales and drive more profit to the bottom line. But what do the numbers look like on a monetary and common-size basis? The model will display that information with a couple of key strokes. By seeing what the financial future might look like given a certain set of changes to the existing operations, financial teams can disseminate goals and targets throughout the organization and be focused on working toward those goals while monitoring progress on a periodic basis through the model.
A high-performing financial model has at its core a standard data set. In any model the user should be able to pull a standard data extract (typically the general ledger) from their accounting system that can be “dropped” into the model on a periodic basis (monthly, for example). The data set is then coded to be searchable for various data points (date, customer, other criteria). Formulas can then be written to search the standard data set based on selected inputs.
Beyond historical analysis and future projections, models can be built to handle a wide variety of other tasks that may be more specific to certain entities. For instance, clients that have multiple locations may want to use certain industry benchmarks to rank the locations categorically. For example, a restaurant with 10 locations may want to compare revenue between the locations several different ways such as: revenue per employee, revenue per square foot and revenue per seat. The rankings may be different for each category but with enough analysis the better-performing locations emerge as well as the poor performers. Furthermore, top locations may have a category or two (such as percentage of sales discounts to revenue) that, if focused on, could make them even better performers.
Data analysis is a powerful tool that can be handled through the use of a powerful financial model. Models should use a standard data set to drive meaningful analysis that is relevant to an organization and will assist in financial and operational decision making. When designed and used properly, financial modeling of data results in a competitive advantage you do not want to miss.
Need help making sense of your company’s financial data? RKL’s Business Consulting Services Group provides financial modeling and data analysis services that will assist management in making informed decisions.
Contributed by James M. Spencer, CPA, MBA, CVA, a manager in RKL’s Business Consulting Services Group. He provides business valuation, financial modeling and analysis including projections and forecasts, project feasibility analysis and assistance with acquisition and sales of closely-held businesses.