A number of our clients face a common problem: How to effectively run scenarios across a portfolio of investments in multiple Excel models and consolidate the data?

To effectively perform large-scale scenario modeling and analysis in Excel, scalable financial modeling techniques are necessary. Such techniques can help you streamline your processes and make better informed decisions.

Recently, CohnReznick’s newly expanded Financial Modeling & Decision Analytics team hosted a webinar to explore this problem and the techniques to address it. Anton Cohen, Charles Bocquentin, and Michael Ruth shared some key takeaways and best practices, and reviewed an illustrative client case study, which we’ve summarized below. 
 

Key takeaways and best practices

1. Control Panel: Use a separate file to summarize all your scenarios and assumptions across all models in a centralized “Control Panel” table. You do not need to build scenario logic into each file and can instead run all desired scenarios from the Control Panel.
2. VBA for pushing scenarios: Leverage VBA to automatically loop through the Control Panel and generate a new file with the relevant assumptions for each investment model and scenario. VBA is a scripting that can run any local macros and refresh calculations while generating each scenario. It is best practice to use a Source Folder with all the investment models that VBA will open, then save each scenario file in a separate Scenario Folder.
3. Power Query for data consolidation: Use Power Query to consolidate the desired financial data from the various scenario files located in the Scenario Folder. Power Query is an ETL (Extract, Transform, and Load) tool that is included within Microsoft Excel and Power BI. It enables powerful data consolidation in a scalable, automated, and auditable manner. Once a process is defined, you can simply add files to the target folder, hit refresh, and the data will be consolidated to the extent that it follows a consistent structure.
4. Power Pivot for interactive Excel dashboard: Use Power Pivot to enable powerful and highly interactive visualization of your data in Excel, leveraging slicers, pivot tables, and pivot charts. You can sum all the data together or break it down by fund and assets with the ability to toggle between scenarios or present them side by side, and the ability to present the data monthly, quarterly, or annually.
5. CUBEVALUE for best practice modeling: Use CUBEVALUE() formulas to integrate the consolidated data into your financial models. They essentially enable you to pull the right data into your model straight from the data model, which is best practice instead of manually copy-pasting data or linking your model to Pivot Tables.
6. Power BI for online dashboard: Use Power BI for standardized online dashboards that can be shared with key stakeholders and updated easily while enabling a great level of control.
7. Data planning and pattern recognition: Define the key metrics you wish to consolidate and consider what types of data can lead to strategic decisions, such as benchmarking performance across the portfolio to identify top/bottom performers. Pattern recognition is key to successful automation. Determine if you need to add a dedicated standardized Export tab to extract it or if you can leverage consistent patterns (cell address, labeling, etc.). Exception handlers may be needed.
8. System integration: Power BI and Power Query can be used to automate processes and import data from various sources such as ERP, accounting, CRM, etc. They can be particularly helpful to automate the quarterly/monthly update of historical data in your models, or to perform data reconciliation across multiple sources.
   
   

Illustrative case study

The problem

In our case study, the illustrative client was dealing with the following situation:

• They have invested in 13 assets through four funds.
• They maintain financials for each fund in separate Excel models, capturing historical and project financial performance of all assets on a monthly basis.
• They wanted to run various scenarios (low case, base case, high case) across all assets.
• They wanted this to be done in a scalable and automated way, so they could easily change assumptions and refresh the analysis for their quarterly board meetings.

The solution

We built a best-practice tool in Excel: In a couple clicks, it enables to automatically generate the desired scenarios across all asset models, consolidate the data, and visualize it.

• We created a “Control Panel” file summarizing key assumptions under each scenario across all assets.
• We used VBA to automatically push these assumptions into each Excel model, refresh local macros and calculations, and saved resulting financials.
• We used Power Query to extract key financial data to be consolidated.
• We used Power Pivot to visualize this data in Excel in a very interactive way – either summed all together or broken down by project – with the ability to toggle between scenarios or present them side by side and the ability to present the data monthly, quarterly, or annually.
• We used CUBEVALUE() formulas to incorporate this data into a best practice consolidated financial model.
• We used Power BI to visualize this data in an online and interactive dashboard that can easily be shared with relevant stakeholders in a highly controlled, yet compelling, manner.
• We then used Power BI to blend this data with a fundraising model to understand the company’s cashflow forecast for each of the scenarios.

All this is highly scalable and automated, the analysis can be refreshed in a couple clicks, and additional files can be added with minor updates required. You can also update the Control Panel table summarizing assumptions for the additional assets, and possibly add an Export tab in the models if they do not follow the same pattern as the others.

Beyond this specific case study, this approach enables you to make batch automated updates across a large number of Excel files, and/or consolidate data from a large number of Excel files. For example, we recently helped a client consolidate 40 years of projected financials across 200+ Excel files.

To view the recording of our webinar, please go here.