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
This is Part 3 of 6 on Financial Modeling using ExcelIn 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,
- Introduction to Financial Modeling
- Building a layout for Project Evaluation Model – Best practices
- Building Inputs and Assumptions Sheet
- Building Projections for Project Evaluation
- Modeling the Cash Flow Statement and Projections
- Putting it all together – Final Project Evaluation Model
- 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)
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.
17 Responses to “Building Inputs & Assumptions Sheets – Excel Financial Modeling [Part 3 of 6]”
Great article, but I'd like to point out that there are scenarios in which deprecation can have a substantial effect on your valuation, particularly in the world of real estate transactions. When modeling real estate, or any business with substantial physical assets, it's important to remember that depreciation and amortization will have a direct effect on the project/company's tax liability, and therefore it's after-tax cash flow.
Looking forward to the next article in the series!
A valid point RE_analyst. From my experience I have developed financial models where the depreciation and amortision rates have been requested to have two versions (different rates) being internal depreciation rates for management reporting. And a a different rate for external reproting that actually impacts cash flow from an income tax perspective to cashflow that does not relate to the internal management reports. Dont ask me why but this has been requested of me by clients.
Cheers
Hi Paramdeep and Chandoo
You did it again! This is such an awesome third serving. All the tips you posted are right on. Again, I'm looking forward to the rest of the series. Great job you guys!
@RE_analyst,
Yes I agree, I assume Paramdeep has never done a U.S. project so he did not know in the U.S. we tax corporate profits. The joys doing cash flow analysis in a nation with a corporate double taxation system. Pretty much any expenses per GAAP that is not expensed in the month the cash is paid needs to taken into consideration. Typical one can ignore Net 30 and 60 day payment terms because it has very little effect on the cash flow. That is unless you are using different tax rates depending on the fiscal year. Then you may need to take that into consideration because it is a large enough diffrence to matter. Multi-year leases are another expense one needs to be careful of when doing cash flow analysis (the payments over the life of the lease are straight line amortized, leases which due not have even payments over its life are quite common.) There are other expense I do not know about which one needs to look at closely, but I don’t think they are as significant as leases and deprecation.
The same is true with revenue on long term contracts. For tax purposes revenue does not equal cash inflow and it is very significant in many cases, multiple year construction contracts is one example. Government Contracting is even more complicating for cash flow if you have a Cost + or T&M contract. The goverment is rebursing you according to their rules. That is you have two books one for taxes and one for the goverment that they use to pay you.
@ Pristine
"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!"
Are you talking that modeling(aka the calculations) themselves are that simple. Then I agree with the most statement but most is not correct with many jobs, but very true with others. However, I read this first as the answer is that simple. If you a referring to the answers and what should be done then you are clueless and more bad decisions because manager think the answer is straight forward and simple so they read to much into it. Even at that level of information they need to know it is not simple and there is a ton of unknown and risk associated with the answer.
Oh that is when I am working for people who actually want to know a good answer. Adjusting the assumption or model so it is skewed towards the decision they want, then you are right the decision is simple. Just tell me want you want the numbers to say and I can back into them.
Very good point RE_Analyst & Brian.
@Brian:
We are trying to show how one can look at an investment decisions using basic math. By no means we are advocating that all financial decision making is simple. In fact, it is exactly the opposite. But the underlying math is much more simple and straight-forward than it made out to be (unless you are talking about weirdo stuff like option pricing. That one got me a D in valuation course back in school.. 😀 )
@Drew: You are welcome 🙂
[...] Building Inputs and Assumptions Sheet [...]
[...] Building Inputs and Assumptions Sheet [...]
@all: Just read the comments while going through the previous posts. Somehow the notify on comment was not updated!
@Drew: Thanks
@RE_Analyst, @Brian: As I had said earlier the calculations are obscure and complex, the decision is simple! 🙂
I agree that long gestation projects (typically RE, Roads, Infra, etc.) have a complex tax calculation mechanism and thus the cash flow calculation itself becomes complex. Even in India, tax is major headache in any such project. Apart from Depreciation (and thus deferred taxes) you have MAT (Min. alternate tax) and the MAT credit period given to companies who implement these projects. Correspondingly you can work on optimizing your period of MAT credit so that you get the best return.
The tutorial was meant to give a gist of how valuation works. It in no way is a complete primer on valuing complex structures and transactions! 🙂 We can connect (separately or on this group) to discuss some of these intricacies and how they can be modeled in Excel!
BTW, we have conducted separate trainings for RE and Infra groups of large Banks and PE on Financial Modeling in Excel, where we helped them create complete integrated valuation models for these industries! 🙂
[...] Building Inputs and Assumptions Sheet [...]
[...] Building Inputs and Assumptions Sheet [...]
How opportunity cost, growth in fixed expenses, and Weighted average cost of capital were calculated. Although I have read the case several times, I have no idea how those figures came out.
I would appreciate it if you would send me the reason through an email.
Many thanks!
@Yeong: Actually in the case, the assumptions about the growth in revenues are made in the section on "Real Potential in my Venture". The assumption about the alternate opportunity cost is probably not given in the model.
Usually in the models you would make these assumptions and can then check their impact on the valuation/ IRR through a scenario analysis.
Thank you very much for your answers.
Paramdeep,
Not sure if WACC could be 10%. As per my calculations, it came out to be 7.5%. If I am missing any step then please let me know.
WACC = E/V * Re + D/V * Rd * (1 - Tc)
where Re = cost of equity
Rd = cost of debt
E = market value of the firm's equity
D = market value of the firm's debt
V = E + D
E/V = percentage of financing that is equity
D/V = percentage of financing that is debt
Tc = corporate tax rate
As per the case, E = 50% of total financing and D = 50% of total financing so E/V and D/V = 0.5
and Re is 10% and Rd is 7%. Tax rate is 30%.
Thus, WACC came out to be 7.5%
@Ansh: I am taking the cost of debt as post tax cost of debt (That is why you dont see the factor or (1-t) there. Apart from that there are two switches (Cell D22), where you can change whether the Re you want to take through CAPM model or direct expectation of investor.
Hope this helps
Any tips for determining opportunity cost in a project evaluation?
Attractive section of content. I just stumbled upon your web site and in accession capital
to assert that I acquire in fact enjoyed account your blog posts.
Anyway I will be subscribing to your feeds and even I achievement you access consistently
rapidly.