Paul Michaels*
Paul Michaels* is a consultant who helps his clients forecast revenues, manage expenses as well as transform their cost structures. He has led dozens of such engagements in retail, manufacturing, media, and consumer products. When we first spoke to Paul about working with Trefis, he and his team were using a complex model to support their recommendations.
A “Bear” of a Model
“The spreadsheet we built was a bear,” Paul told us. “It’s about 3 dozen tabs wide and thousands of rows deep. This model was developed to help us advise our retail clients with revenue forecasts.”
In fact, the model Paul describes has more than 1000 active rows and 33 tabs “It includes the key elements of a large five-year corporate forecast such as complete Profit/Loss, Income statement, Balance Sheet and Cash Flows as well as outputs for economic margin and valuations ” he continued. “We also incorporate data feeds from industry sources to provide performance benchmarks,” Michaels adds.
Trefis Calculates a Complexity Score
Trefis automatically calculates a complexity score for each model a user uploads. Our customer success team uses this diagnostic score to help assign models to the right team member when a user calls for assistance. Paul’s “bear” had a complexity score of 11.
Paul’s forecast model was not unlike the hundreds of thousands of other models Trefis customers upload each years, some of which are less complex, some of which are more. The “Financial Model” we use as part of our alpha test (also a five year model) has a complexity score of less than 1. One of our client models tracks an entire industry sector; that one has a score of 42. In fact, Trefis’ platform has transformed models with complexity scores in the thousands.
Using Complex Spreadsheets for Scenario Analysis Poses Several Obstacles
Before using Trefis, Paul and his team used this complex model to create “what-if” scenarios. It wasn’t easy.
“Our guys wrote complex macros, added “flex” variables and created multiple versions of the model in Excel so our team could test different scenarios,” says Michaels. “We can change growth rates and and other assumptions over multiple periods and compare year-over-year changes by manipulating any of the inputs.”
Creating this model took many people many hours spread out over the course of many weeks. While the model works, it’s not without challenges.
“Only one or two people know how the thing really works,” Michaels says, “so when we want to examine different scenarios, those people need to be available. Even then, when we create a new scenario to, say increase Revenues, the figure will update in its cell but we don’t get a real sense of what the real drivers are or the impact on related outputs like margin or EBITDA. Those appear on other tabs.
Finally, the model includes tabs to save two scenarios. “Therefore, if we want to do more than a couple of scenarios,” Michaels reports, “we have to save multiple versions.”
The need to build in this scenario capability required continued refinement. Indeed when Paul came to us, his team had created hundreds of versions.
Using Trefis Saves HUNDREDS OF HOURS
Trefis transformed his model. Paul was astounded. He told us, “If we would have had this capability at the beginning of the year, we would have saved hundreds of hours not just in creating scenarios but in building the model itself.”
Indeed, the ability to effortlessly change any variable and run any number of scenarios across multiple time periods is core to the Trefis platform. “We could use the Trefis platform for all of our scenario and comparison needs and wouldn’t have needed to incorporate all of this development in the model. The entire model would have been simpler, and easier for everyone to understand.”
In addition, Paul noted that Trefis is “flexible and intuitive and has robust ability to do all of the scenario creation, analysis, and impact assessment. With Trefis we can focus on the core findings as expressed in the base case, create scenarios faster and more efficiently.”
*Note: For the purposes of confidentiality, we are using a pseudonym “Paul Michaels” for our client. The model linked to above is an obfuscated and streamlined version of a client model.
