Building Inputs & Assumptions Sheets – Excel Financial Modeling [Part 3 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 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)

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

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

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

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

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.

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)