One of the main qualities of a financial model is to be flexible and robust enough to be used as a tool during negotiations of different contracts.
Usually in contract negotiations there are specific working sessions on the financial model. That’s where different parties first agree and approve the model mechanics and then agree on the inputs and basically define a base case. So when you are preparing for these kind of modelling sessions, you need to make sure that your financial model is flexible enough to run different scenarios and present the results during the session.
Here I want to list different tools and techniques that would help you for better presentation during these type of modelling sessions:
Present your inputs in the form of scenario
It’s very practical to design the layout of the “Inputs” sheet in such a way to enable you to run multiple scenarios. For example in the below snapshot, the Base case assumptions are defined in column J in “Inputs”, Column K for Worst case and so on and there’s a dropdown menu that enables you to switch in between different scenarios.
So when you start the meeting you can define a new column and call it the revised case or put the date and start changing the inputs during the sessions as the discussions progresses. For a complete model having this structure check my solar PV model template.
Know the sources of your data
When you go through the inputs, people will start asking you about the sources of the inputs. So get ready to have a convincing explanation for every single data input that you are presenting. It’s a good practice to either put the source of data next to input itself or to have a dedicated sheet listing all the sources. For an example please check my suggested assumption book.
Make sure that you have the right metrics on your summary sheet
Once you are done presenting your inputs, you’ll go directly to a summary sheet where you should present all the relevant metrics. You don’t want to go to different sheets and find the different metrics. It’s a complete waste of time so before the meeting, think about the questions that people might ask and the metrics that will answer their questions. For example in a typical project finance deal, you want to have the summary sources and uses, debt metrics (DSCRs), equity return and also present key model inputs like debt terms, capex and price assumptions.
Add dynamic charts
You can easily graph all the items in the financial statements and in one graph and have a dropdown menu to switch in between cash flow, P&L and balance sheet items. For example if during the meeting someone asks you about the dividend distributions through time, you simply go to the chart and switch to dividends in the cash flow statements and show the flow of dividends. Have a look at my template on how to use Index and match to create this dynamic chart.
Add the scenario Reporter
Most probably when people start negotiating during the meeting you’ll need to run multiple scenarios and then compare it with another scenario. For example lenders might be interested to see the impact of extending the construction period on their debt ratios and at the same time sponsor might want to show to lenders that their request to increase the interest rate by 1% is not reasonable and is hurting the project. I always use the scenario tab designed by Edward Bodmer. This is a simple but useful tool that enables me to define different metrics and changing inputs and saving the results in this sheet. Here’s a snapshot of how this worksheet looks like. You can download it from Edward’s site.
Add the Q&A tab
During any model discussions most probably people will raise concern or questions that you want to keep track. It’s very practical if you attach a Q&A sheet with the model and as you go through the model and people raise questions, you want to write them down live during the meeting inside the model. This way people in the meeting will also have some kind of comfort that their request, comment or question is documented and will be treated. It also helps you to keep track of the critical issues raised during the session. So when you send the model after the meeting, it will contain a Q & A sheet with maybe also a column explaining the next steps for each of the items raised during the session. Download an example of a Q&A and also check my video.
I want to finish this post by saying that the best person to run the negotiations is the one who knows the data best.
