Building Inputs & Assumptions Sheets – Excel Financial Modeling [Part 3 of 6]

Share

Facebook
Twitter
LinkedIn

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.

Building Inputs & Assumptions Sheets - Excel Financial Modeling Part 3 of 6This is Part 3 of 6 on Financial Modeling using Excel

In this tutorial we are going to learn how to build assumptions & input sheets in our excel financial model. 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

First a story: Charlie and Chocolate Factory

Would you invest in my chocolate factory? It is just a $1,000 investment (And I would give you all the chocolate that you want for free!!)! I am going to produce 10,000 chocolates per year, which costs me $ 1,000 and I would be able to sell them for $ 1,010 in the market. Would you invest your money in my project?

You can just do a back of the envelop calculation to figure out that it would take me a hundred years to return back your money (Even if you were to charge no interest).

Believe me – Most of the decisions in finance are as simple as that! 😉 Its just that the numbers are obscure and figuring out the right numbers from the client takes a lot of time!

The first step for any meaningful evaluation is assimilating the facts CORRECTLY. In the world of finance (especially investment banking, equity research, etc.), for most of the analysis work you don’t need to be PHD material or a (ironically!!) researcher. Typically the work that is required to be done (as far as modeling in concerned) is quite routine. The skill that is highly in demand is consistency and an eye for detail.

As a banker your task starts by questioning each and every number that your client is giving you and recording it correctly. For comparisons, you start with ball park (industry) numbers and do some back of the envelop calculations to ascertain the feasibility of the project or the valuation numbers.

Figuring out relevant information

In finance there are two cardinal rules:

  • Cash is the king
  • Cash today is more important than cash tomorrow

Whatever affects the above two is going to have an impact on the valuation of the firm. So while evaluating any business proposal (or company), you should record all the facts that affect the cash and its timing.

For example, if we were to just pick an instance from the case (download here), which reads as:

I have done a thorough analysis and found out that the minimum initial investment needed in starting such kind of factory would be around USD 400 Mn (which includes the cost of machinery which has depreciation @ 20% every year and would also have a salvage value of 20mn after 10 years) and a starting working capital of $100mn which is 40% of revenue).

Then what is important from valuation perspective is the USD 400 Mn that goes out as investment (If it is all cash). Since it goes out on day 1, it is all the more important.

The depreciation is just an accounting concept (allocation of the huge cash that you invested to different accounting periods). It should have NO impact on the valuation of the project (as it does not affect the cash and also does not affect its timing). But when accountants create P&L, they deduct the depreciation from your EBITDA. So when you start the valuation, you should put that back in the cash available (and hence to record the information on depreciation).

Layout for Assumptions & Inputs

Creating a layout which can help you record the cash and its timing in a structured manner can help you eliminate the possibility of errors in your model. Typically you can categorize the recording of information in the following heads:

  • Initial investment that would be required to start the project (Investment decision)

Investment Assumptions - Financial Modeling using Excel

  • How much of money is locked in the business apart from the plant and machinery (Working capital as investment)

Working Capital Assumptions - Financial Modeling using Excel

  • How are you going to get your cash back (The operations)

Profit & Loss - Operations Assumptions - Financial Modeling using Excel

  • How are you going to mix your equity and debt and what are the costs you pay for each

Valuation Assumptions - Financial Modeling using Excel

Once you have recorded the relevant information, you should draw out the timing of cash as well to figure out the valuation.

Templates to download

I have created a template for you, where the subheadings are given. You have to read the business case (here) and figure out which numbers go where. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

Download the blank assumptions sheet

Assumptions & Inputs Sheet - Blank - Financial Modeling using Excel

Also you can download this filled template and check, if the information you recorded, matches mine or not!

Download the completed assumptions sheet.

I am just doing that for the single sheet model and recommend that you do the same for multi-sheet model as a homework problem. If you face any issue, post your excel with the exact problem and we can discuss the way to move forward.
Financial Modeling using Excel - Online Classes by Chandoo.org & Pristine

Next Steps

In the next installment, we would see, how we can calculate the cash that the project is going to earn in each financial period. It would mean using the recorded information and finding the items that contribute to cash change (and that don’t) and also the exact financial period, when that cash would flow in. 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 previous part of this series – Building a Layout for Valuation – Best Practices

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.

How do you prepare assumptions sheet?

We are very eager to learn from. Tell us how you go about building assumptions sheet and how you switch between various assumption scenarios. Please share using comments.

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)

This article is written by Pristine. The author can be contacted on paramdeep@edupristine.com.
Pristine is an awesome training institute for CFA, PRIMA, GARP etc. They have trained folks at HSBC, BoA etc. Chandoo.org is partnering with Pristine to bring an excel financial modeling online training program for you.
Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

4 Responses to “Best of Chandoo.org – 2013”

  1. Kushal K Shah says:

    sir i want your autograph

  2. Maxim Manuel says:

    How many times during the year did I click on most of the pages there to learn something new? Thank you Chandoo!

  3. Cad says:

    =TEXTSPLIT(jobs[Job title],{" - "," ("," /"})

  4. Cad says:

    =TEXTSPLIT(jobs[Job title],{" - "," ("," /"})

    =CHOOSECOLS(TEXTSPLIT([@[Job title]],{" - "," ("," /"}),1) -- for tables

Leave a Reply