Making an Effective DCF Model

Posted by on Jul 5, 2018 in Financial Modeling | No Comments

An effective and easy-to-use DCF model could start as a blank Excel spreadsheet or be developed by leveraging past spreadsheets, but do you know what elements help shape a DCF model into a tool that provides meaningful insights and is easy to use by others?

In this video, we’ll walk through a great layout of a customized financial model, or a DCF model.

DCF Model Cover Page

First, every DCF model we develop begins with a cover page, which also serves as a disclaimer page. If you’re building the model for external stakeholders, you’ll need a disclaimer, but if you’re building the model for internal stakeholders, you’ll use the cover page as a getting started page that includes a Table of Contents, just like you’d find in a book, so that new stakeholders understand how to use the model.

Include a Table of Contents

The Table of Contents is basically a DCF Model User Manual and starts with the modeler’s contact information and the objective of the model. On this page, you’ll include the structure of your model, an influence diagram of how the information flows throughout the model, and the names of all the worksheets, what type of worksheet, if the worksheet is a Read Me page, and if the worksheet is static or dynamic.

Another thing to consider including in your Table of Contents is a note if there are a lot of calculations on a particular page or any further descriptions that the page requires.

An excellent model uses color and formatting to improve usability. You should include a description of what each cell and font color means for your model in the Table of Contents. Color-coding will help users quickly understand which cells they can change, which provide information only, and which contain calculations.

DCF Model Inputs/Outputs Page

This page is typically the most used page out of the entire DCF model. On this tab, you’ll include key inputs and outputs, and make them clear and dynamic enough so that someone who is about to go into negotiations can quickly run a what-if scenario. A successful DCF model will have stakeholder engagement throughout the development process in order to make sure this page is clear, succinct, and delivers the necessary outputs.

Typical inputs are general assumptions, discount rate, inflation, tax rates, and any other conversion factors that are important depending on what industry or what sector you’re in. If your project deals with commodity prices, you’ll want to include the key prices of your project and build in different price scenarios.

Tip: Use drop down menus to make this page easy to use.

On this page, color-coding is key. In Darby Finance DCF Models, white cells are dynamic inputs and yellow cells are the locked calculations.

Time Series Inputs

Time Series Inputs are multi-year inputs that may change over a period of time but do not need frequent updates. Most times, these inputs do not need to be changed in order to run different scenarios. If you’re looking at a commodity-based valuation, you can include different commodity price curves on this ‘Time Series Inputs’ tab and include a drop down menu on your frequently used ‘Inputs/Outputs’ page to change the selected price curve to run different scenarios.

Using Charts in Your DCF Model

If you’re creating an investment presentation or a pitch book for investors, or if you’re populating a white paper or a business case, you can put all your key charts on a separate page, which will make it easy to populate for later presentations.

An Effective Layout for your DCF Model

An effective DCF Model will lay all data out on spreadsheets from North to South (top to bottom) and West to East (left to right). Your calculations should not jump all over the page.

Tip: Use the zoom feature to zoom out of a spreadsheet, below 25%, to ensure you have no extemporaneous calculations off to the side. Your model will be prone to more errors if there are random calculations found on spreadsheets throughout the model.

You should strive use a very clear layout and architecture for your DCF model.

Most Importantly, Test and Debug

And last but not least, one of the most important pages you could possibly have in your model is a ‘checks’ page. Your checks page helps to ensure you have no decision-changing errors in your DCF model. Financial models can become very complex very quickly. You want to have checks, and you want to make sure you have no busts in your model, especially when millions, or billions, of dollars are on the line.

An Effective DCF Model

In laying out an effective DCF model, keep it user-friendly, concentrate on great architecture, don’t lose sight of the objective, and always check for errors.

If you’d like to see how Darby Finance can develop an effective DCF model for your asset or project, contact us today!


About Darby Finance – A Leading Financial Consulting Firm

Darby Finance helps our 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.

How can we support your projects?

Let's Talk