Building a Layout for Project Evaluation Model & Best Practices [Part 2 of 6]

This is a guest post written by Paramdeep from Pristine. Chandoo.org is partnering with Pristine to bring an excel financial modeling online training program for you.

This is Part 2 of 6 on Financial Modeling using Excel

In this tutorial we are going to learn how to build a financial model to do project evaluation using Excel. The 6 parts of this tutorial are,

1. Introduction to Financial Modeling
2. Building a layout for Project Evaluation Model – Best practices
3. Building Inputs and Assumptions Sheet
4. Building Projections for Project Evaluation
5. Modeling the Cash Flow Statement and Projections
6. Putting it all together – Final Project Evaluation Model
7. Join our Financial Modeling Classes

Building a Layout for a Project Evaluation Model

If you know basic referencing and formula framework in excel, you can write a model that calculates numbers. Good vs Bad financial model would be decided by the layout of the model.

Before we start discussing on how to build a good layout, lets see the layout that I have in mind. The layout is based on my experience of working as an investment banker and as a consultant and I find it neat.

What is so awesome about this model layout?

• Structured in logical modules (Separate sections/ sheets for separate units like Assumptions, P&L, Cash, Balance Sheet, calculations and conclusion. It makes navigation and understanding very clear
• Formatting of the sheet
  • Inputs different from calculations (Typically you would find inputs in blue font and calculations in black)
  • No grid lines
  • Numbers with similar decimal points, etc.
• Proper indenting of model with comments
• Ease of navigation in the model

Start building the model template

Follow these steps to create a simple layout to perform project evaluations.

1. Disable Grid lines for the sheets.
2. Decrease the size of the first 2 columns and increase the size of the third column.
3. Use the columns after that to set the required years.

Benefits of such a template

Since the size of columns A and B is small, if we use column A for a main heading, then we can use column B for a sub-heading. This would act as an indenting for the document (Similar to what you see in table of content in any book). This also acts as a bookmark.

Lets say, if I have 3 main headings – Assumptions, calculations and valuation, then we put them in column A and all the subheadings like, Investment assumptions, P&L Assumptions, Working Capital, Cash etc. in subheadings.

If you remember the Ctrl + arrow shortcut to navigate fast, you can use it to navigate from one section to the other in a fast manner. The columns act as a bookmark!

The other thing to note is the Column mapping to the year is sacrosanct. For example, in my model FY 10 is aligned to column E. Now whenever anybody wants to do any calculation for FY 10, she would remember to do it in column E. If there is any mention of FY 10 in columns different from E, it raises an alarm!!

One more trick that I generally use in the model is to freeze panes, so that I can always see for a particular year, what is the item that I am using. (tip # 12 on 15 excel tips)

Single sheet or multiple sheet model

If you are developing a mini model (Like the one we are doing here), you can use a single sheet and use the bookmarking methodology to separate the logical sections. If the information is large then one sheet might become too large. If you are using multiple sheets, then REMEMBER – always use the same template and you should maintain the matrix integrity (In our case column E corresponding to FY 10). Just copy the sheet to create other logical units. When you are using multiple sheet models, remember Ctrl + PgUp/ PgDn is your best friend to navigate across sheets.

Best Practices While Designing Models:

[added by Chandoo]

I hate to interrupt Paramdeep’s flow. But I want to list down few best practices I have seen across various models.

• Keep your formats consistent: As observed above, use one color for input cells, one color labels, one for outputs etc. Also, follow same formats  for charts and all other visual elements of your model. Consistency is very important to keep your model simple and easy to maintain. (tip: use cell styles)
• Separate calculations from outputs: While simple models can have all the calculations done on output sheet, ideally you should have a separate worksheet for doing all the calculations (formulas). This will make your model elegant and simple.
• Do not hard code values, instead use “assumptions” sheet: If you need to use inflation rate of say 6%, never hard code that in to formulas like =A1*(106%)^A3, instead keep all the assumptions in a separate worksheet (call it as assumptions like above) and fetch the values from that. This will help you revise your financial model easily when your assumptions change.
• Use named ranges: Financial models can be quite complicated beings. A formula like =Assumptions!C23*(1+Calculations!A21)/Inputs!B13 can be quite confusing and useless. When you need to debug or modify your model you are totally clueless as to what these references mean. Instead define names for all important values and use them in formulas.
• Use helper columns without shame: While most of us would get a nice ego boost to write one big formula to solve all the problems, it may not be a nice thing to do in modeling. Instead split your problem in to small, meaningful chunks and use helper columns to solve it. This will make your model easy to maintain.
• Use conditional formatting to highlight relevant stuff: If you are going to demonstrate scenarios in your model, you can use conditional formatting to highlight scenarios. You can also use CF to bring key values / areas to user’s focus. (tip: become a conditional formatting rockstar)
• Use data validation: Getting invalid data in to your model can be quite frustrating. Instead of addressing invalid data thru formulas, you should try to avoid them by using data validation for input / assumption worksheets. (tip: allow only a list of values in a cell)
• Know Excel Better: Learn a few keyboard shortcuts, understand how features like tables, formulas & formatting work. This will save precious amount of time when you are busy modeling. (100s of tips on keyboard shortcuts, excel tables, formulas, charting & formatting)

Most importantly,

• Know your industry better: Nothing beats industry knowledge. Even if you know nothing about excel and modeling, you can learn them easily thru sites like chandoo.org and pristine. But industry knowledge is something that is hard to pick up. Thorough understanding of your company, your market, your customers, your products and your processes is very important to define and prepare a good model.

All the best.

Download Project Evaluation Model Layouts

I have created part templates and you can download the single sheet model and the multiple sheet model to take a look at it. I would suggest that you look at it and try to create a template of your own (Even if that means just trying to create an exactly same template).

Download zip file containing the model layouts | Download the case-study [pdf]

What Next?

In the next installment, we would see, how we can fill in the assumptions part of the model. It would mean reading through the case and filling in the assumptions part of the model. For maximum benefit from the series, please try to fill it on your own and fill in the other parts of the model as well.

Read first part of this series – Introduction to Financial Modeling

What Best Practices & Layout ideas you follow in Modeling?

We are very eager to learn from you about ideas and best practices you follow when building financial models. Please share using comments.

Join our Financial Modeling Classes

We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.

Please click here to learn more about the program & sign-up.

Added by Chandoo:

Thank you Paramdeep & Pristine:

Many thanks to Paramdeep and Pristine for making this happen. I am really enjoying this series and learning a lot of valuable tricks about financial modeling.

If you like this series, say thanks to Paramdeep. I am sure he can take any amount of appreciation without choking.

Join our Newsletter

If you are new here, consider joining my newsletter, because I can send you updates when new articles are posted (plus you get a cool e-book with 95 excel tips, FREE)

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.