If you ever had to deal with 2 separate financial models for one deal, you know how hard it is to aligne those two models. I not talking about different versions of the same model but two completely different models with different model mechanics.

I had to deal with this issue once in my life back in 2013. I was working as part of the appraisal team for lenders for a mega project. The project was at a quite advanced stage and was almost about to close. The one remaining issue was to come up with a Minimum Revenue Guarabntee mechanism, and we needed to run some MonteCarlo simulations to come up with that mechanism. The existing model which was also edited and accepted by all parties could not be used for running Monte Carlo simulation. It had different blocks using copy and paste macro. Doing a simple change and running the macro was taking 4 to 5 minutes to run. So imagine running at least 500 simulations to get a reasonably well defined distribution was impossible.

So as lenders we decided to build our own version of the model. I had to first build a standard model while minimizing the copy and paste macro to one for financing only. Then after rebuilding the model, I had to do a line by line comparison of my model with the original model to make sure that we are getting the same results under the base case. Any time we receiving a new model from sponsors, I had to repeat the same process and understand what were the changes and try again align the two model. So since then, at least when I am building a model, first of all, I make sure that the model is compliant with best practices and also can be easily used by different stakeholders without a needs to have a seperate model for each party involved in the deal.

If I want to summarize the ideas and tricks that enables me to have this quality in my model, it will in 3 steps:

Step 1: Present Inputs in form of Scenarios

• The layout of the “Inputs” sheet should be designed in such a way to enable the user to run multiple scenarios.
• For example, differentiate between Sponsor, lenders base case and dedicate column J for sponsor base case assumptions and column K for lenders base case.

Step 2: Hybrid sheets

• Hybrid sheet is a single sheet that contains inputs, calculations and output
• It’s not a stand-alone sheet and it is linked to the model but you can just remove from the model and it will not have any impact on other sheets within your model.

Step 3: Use Ed’s Scenario reported to swiftly generate a report comparing different scenarios

The scenario reporter created and shared by Edward Bodmer allows you to record multiple scenarios in a sheet in an automatic fashion.

Download Ed’s tool from here.

What are the benefits of One Model Approach

Now let’s look at 2 practical example using the One Model Approach

Case 1: EPC Bid evaluation

Let’s say you received 4 offers from 4 different EPC contractors and here’s the summary of the EPC offers:

As part of financial evaluation of the EPC offers, you are asked to plug in the above parameters into your financial model and report to your management what are the total project cost, Equity IRR and minimum DSCR under each of the above offers.

If you don’t use the one model approach, you need to plug in the Bidder 1 offer in the model and save it under another name, repeat the same for teh other bidder so you’ll have 4 model version. You can avoid this by simply using the one model approach.

So following the step, you first need to present your inputs in form of scenario analysis and make sure you have at least 4 columns.

Once you have the set up for the inputs you simply input the parameters as per the table for each bidder.

Then use the scenario reporter (edbodmer.com) to record different scenarios.

Then you have a solid base to take to your management for discussion on the selection of the EPC.

Case 2: Lender versus sponsor base case

Let’s say that you are working as a financial modeller for lenders and you have received the following table from your task manager asking you to change the following parameters and present it as the lenders base case and compare the main project metrics under sponsor versus lenders case.

Of course you can save the model under another name and change the inputs. However for comparison sake, it is easier to build the Inputs in form of scenarios and have both scenarios together within the same file.

Hopefully the model you have received a standard model and you have dedicated sheet of all inputs going into the model. If the inputs are not presented in form of scenarios, you simply build it in the model by adding columns and using the index/match function to pick up the selected scenario.

Now using Ed’s scenario reported, you can easily generate the comparison table.

I hope you found this post useful. Also make sure you check out my two related YouTube videos and if you like what I do, please consider subscribing to both my YouTube Channel and on my blog. This way we’ll be in touch.

In the meantime, happy modeling.

Hedieh