With a great and effective DCF model, you can start with a blank Excel spreadsheet or you can leverage past spreadsheets, but what all should be included in a great DCF model layout?
This video breaks down the key elements of a financial model including layout, key tabs and pages, color coding, important charts and checking for quality. These are elements that the financial modeling professionals at Darby Finance consistently utilize to provide financial modeling solutions including asset based financial valuation, equity valuation, financial scenario analysis. These techniques can be utilized for conducting financial analysis for all types of companies and scenarios such as oil and gas asset valuations. Extended purposes include Investor Relations Marketing
About Darby Finance
Darby Finance, a financial consulting services firm, helps clients make better financial decisions by transforming complex data into a compelling and easy-to-understand financial story. We work alongside our clients to develop customized financial models, valuations, and sophisticated investor presentations while utilizing more than 40 years of experience in finance and investor relations. Visit us: www.DarbyFinance.com Contact us: www.DarbyFinance.com/Contact-us/
I’d like to walk you through a great layout of a customized financial model. With a customized financial model, you have no limitations. You can start with a blank excel spreadsheet or you can leverage past spreadsheets and cut out previous sections that’s been useful. But one of the things I’d like to do is just walk you through what’s a great layout and why it’s a great layout.
First, we typically have a cover page. It’s also our disclaimer page, so if you’re building a model for someone else, you’ll want to include a disclaimer; but if you’re building it internally, the first thing you want to start with is a “Getting Started” or “Read me” page.
You want people that’s first looking at the financial model that’s never looked at it before to have a place to go first. Same thing with a book that you buy at Barnes and Noble. You want to have a Table of Contents. You want the reader to understand how to operate it, how to use it. It’s basically your user manual.
Typically, a great financial model will start with who to contact if something goes wrong with it or if you have questions, what’s your contact information and what’s the objective of the financial model? If you are building a financial model for a key asset or valuation, which most of the time a custom financial model (also referred to as Custom Economic Models) is for a particular project or investment opportunity, you’ll want to state what the objective is.
You also want to lay out the table of contents which should include every single tab that you have in your model. The reader should be able to read:
- What is the name of the worksheet?
- What type of worksheet is it?
- Is it a read me page?
- Is it static?
- Is it dynamic?
- Are there lots of calculations on that page?
Some of the common descriptions that we have are disclaimers, instructions, input information, where you have calculations, outputs such as charts and any notes. You also want to have a color code to help your users understand what are the different cells and what they mean. It’s important to be consistent in use of color in the layout throughout the workbook. So someone trying to use your model, whether it be for a quick analysis or maybe inherit your project once you’ve moved onto a different project, you want them to be able to know how to use your model. And then a diagram of how the information flows throughout your customized financial model. That’s important as well.
The next tab that’s really the most important; it’s the face of your model. It’s where you want 80 percent of all users of your model to stay. If you have to jump out into the calculations of the model, you’ve done something wrong as a developer of a customized financial model.
So, on this page, you want to have key inputs and outputs so if your particular project or investment that you’re looking at, and someone’s negotiating it, this is a page where they would be.
If you are getting ready to run negotiations and you’re up at 11:00 at night like we’ve all been before, you want the model user to be able to stay on this page and to be able to run quick scenarios and quick analysis. And part of that is making sure that you have lots of stakeholder engagement with the people who will be using your financial model.
So here we lay out the different assets.
You’ve got general assumptions, your discount rate, inflation, maybe tax rates, any conversion factors that’s important depending on what industry or what sector you’re in. Key prices, if you’re looking at an appraisal of a project that has commodity prices, you may have different scenarios.
You want to be able to build in different price scenarios, make it easy to use with drop down menus in the inputs that go on this page.
But they’re all the key inputs that are easy to change on and off. And here you have all the key outputs. What’s everything that you or your negotiator or the owner may want to have at their fingertips?
Because again, 80 percent of the people using a financial model should never leave this Input/output page and it’s also color coded.
You can see that white cells are things you can change, here the yellow cells are the calculator or the calculated cells, and oftentimes you’ll lock these so people cannot change the calculations or make mistakes.
The next ones are time series input. These are the inputs that are a time series, they’re over a period of time or they’re also input that may not change very frequently. Again, people don’t need to change them oftentimes to run different scenarios. If you’re looking at a commodity based valuation, you can put time series here and have different options, so if you have a drop down option, maybe price deck one, two, three, or four, you can have the drop down on your input output sheet, but then have the different scenarios here and this is where the inputs would go.
Another key output are charts. If you’re creating an investment presentation or if you’re populating a white paper justification paper, a business case, you can put all your key charts here, that’s going to populate those presentations later. Again, it’ll help you be efficient.
This will also help you test and debug your customized financial model. The assets and the calculations are on, you can either have them on one particular worksheet or you can make them identical on different worksheets.
If you’re looking at four assets and they all have identical key value drivers, you can create them efficiently by highlighting all the tabs at one time and laying out the calculations, but that’s for another book. But for this one we’ll talk about the layout.
You want to do everything north to south. You don’t want calculations jumping all over the page.
If you zoom out of a page, you want to make sure you don’t see any extemporaneous calculations
off to the side. A great customized financial model will always develop north to south and left to right, and then you can also be able to follow them by opening them up. It’s all a very clear layout of your financial model and then if you’re looking at multiple assets or portfolio, you can have a roll up tab as well. That’s very easy to look at and that can populate your charts.
And last but not least, one of the most important pages you could possibly have is your checks page. Your checks page is making sure you have no decision changing errors in your customized
financial model. Custom Financial Models, also referred to as Custom Economic Models, can become very complex very quickly. If you’re looking at a billion dollar project or maybe it’s just a couple million dollars, but those couple million dollars is 90 percent of your portfolio, then it’s a very significant model.
Whether you’re conducting capital intensive project modeling for construction project valuation such as pipeline valuations, renewable energy valuation, valuing E&P companies or Oil & Gas Asset Valuations (including Oil & Gas Company Valuations, Upstream Company Valuations, Upstream Oil & Gas Valuation), you want to have checks, you want to make sure you have no busts in your model, especially as you want key sensitivities. So in laying out a great customized financial model, one, it’s user friendly and two, it’s developed very well. It’s got great architecture, but you also test and debug as well.
I hope this video blog was helpful. If you’d like to have more content on a great model layout of your financial model, please check out more blogs at www.darbyfnance.com or visit our YouTube Channel at https://www.youtube.com/channel/UCp_9RfcyqiyZam1rmcHhdhw
About Darby Finance
Darby Finance helps clients make better financial decisions by transforming complex data into a compelling and easy-to-understand financial story. We work alongside our clients to develop customized financial models, valuations, and sophisticated investor presentations while utilizing more than 40 years of experience in energy, finance and investor relations.
- financial modeling solutions
- asset based financial valuation
- equity valuation
- financial scenario analysis
- asset valuations
- custom financial models
- custom economic model
- Litigation Support (Valuations)