Valuation Modeling: Excel as a tool
ฝัง
- เผยแพร่เมื่อ 14 ก.พ. 2024
- Excel is a powerful tool, but in our zeal to test out its many powers, we often overuse, and spend far too much time thinking about modeling and too little about business and valuation first principles. I do have a base valuation spreadsheet that I use to value almost every non-financial service company, and over time I have added some bells and whistles. That said, at its core it remains a simple spreadsheet, with no macros or Excel functions. In this session, I provide a link to the spreadsheet and how to use in valuing company, going input cell by input cell. (I am sorry if my voice sounds ragged, but I have a scratchy throat (nope.. Not COVID) and just got off a long flight.)
Spreadsheet: pages.stern.nyu.edu/~adamodar...
Professor Aswath is the best! He is an unsung hero in these times! Everyone trying to charge for every thing they can and he just shares his knowledge for free in humility..
True he is so generous
He is the real scholar.
Yes you are right! But he isn't unsung. He is well appreciated worldwide!
wake up babe, new damodaran video just dropped
lmao
Thank you professor! I'm not a finance guy, just a computer science nerd and I listen to your new lectures when they come out :)
Thank you sir, others have you pay for such spreadsheets on their patreon. Truly a godsend
No words are enough to thank you for your great support in serving the world and liberating the knowledge sharing. Many thanks professor❤❤
Still the best channel in TH-cam, thanks you so much professor
What a time to be alive. I can’t believe we can get this for free! Thank you professor.
I really appreciate the passion that you have for valuation and for all the effort you make in producing these videos and explaining your process. It's really great of you to do this.
What a great guy. Giving away his knowledge just to help others. No ego no charges. Thanks Aswath!
Prof Ashwath, From the time you'd featured my earlier company CommerceOne, I've been a keen follower. Thank you for the service you render in spreading knowledge. 🙏🙏
Very generous of you to provide these. Thank you, Professor.
I found the most underrated channel on TH-cam. Excellent Sir.
Thanks to you professor, its an incredibile opportunity to have all of this material to study 🙏
Really thanks for the video and keeping knowledge free at these crucial times.
professser,
thank you so much for your efforts, no words can express my gratitude towards you.
TREMENDOUSLY HELPFUL.
THANK YOU Prof.🙏
Aswath is a national treasure. All his NYU content is available here FREE... that's crazy. The Ginzu knife commercials were the best!!!! They were great at doing everything!
Aswath you are the second best to no one !
You are a true educationist Professor!. Thank You🙏
I have smiled throughout the entirety of this--gold. If I choose to get an MBA, it will certainly be at Stern because of you, professor.
Sure, if you have an extra $180k to spare.
Thanks a lot professor for share your classes. I do appreciate it. Thanks!
Awesome! knowledge philanthropy. bowing down in reverence. Hats off Sir..
I like this, knowledge philanthropy!
Un privilegio ver estos videos. Thank you
Always very helpful.. Just remarkable in checking all necessary elements of valuation!!!
I valued UBS post merger before Q2 results with this sheet and bought a call option, which helped me incorporate my startup. I value your words more than anyone my uValue Ninja.
by "helping me incorporate my startup" what do you mean? You bought the call option, made a return and the profit helped pay for the incorporation of the company or was the trade the first actionable idea for your (trading) startup? Thank you
This is Gold, thank you Professor!
7:30 Iterative calculations on PC: File (top left button) -> Options (near bottom of left bar) -> Formulas -> Calculation options (should be top right)
Thanks Aswath, This is worth more than gold.. I hope everyone watches this video before investing in the stock market. God Bless
From Argentina, thank you very much.!!!!! I will use it with WMT tomorrow.
Thank you. You truly are a great professor.
Great Video Prof. Much appreciated, thanks!
Professor, you are the man! Thank you!
This is really helpful and informative. Thank you .
Thanks for sharing the work you are doing.... great work!!.... small comment - Ireland marginal tax rate is 15% for large firms from 1st Jan 2024 (after adopting BEPS Pillar 2).
Thank you from Argentina Professor !!!!
Thank you prof! Using this for my next Seeking Alpha article!
Thank you very much as always Professor!!!
thank you sir, that was a gold mine of information.
thank you for sharing this guide. I welcome this video...
2023 was a hell of a year, in the health profession i have made $250,000 before taxes as the sole breadwinner and head of household. This is a great starting point and I’m very aware how blessed we are to be in this position, but I’m always looking ahead on how to improve.
You want to work with a money coach or financial planner, although a wealth manager will concentrate on long term, a money coach will help you with paying off all of your debts, maximize your cash flow and help you create systems and processes to direct your money proactively.
I have thought about this, but what is the major difference between a money coach & financial advsior?
A financial planner/wealth managercouls help too to create a more holistic plan for your money. They can assist you in the creation of both short and long-term goal and then help you by giving guidance on the financial decisions and opportunities you are presented with.
Yeah get you an invaluable adviser, everyone has blind spots when it comes to their finances. I began consulting with mine in NY from WA. I have a plan in place now and began to love spreadsheets making exactly 702 thousand in the past 8 months from barely 400 grand makes me have high hopes for 2023.
Who is this person you work with? I have tried getting around me and it is practically impossible to get one that doesn’t charge exorbitantly. Can you also share what the fee structure is like?
This was a great video. I would love to see a video or model on financial services companies. All of your videos are outstanding, though!
Thanks Prof Damodaran
THE BEST PROF ASWATH DAMODRAN.
Thank you, Professor!
very very useful spreadsheet sir, having all the info in a simple spreadsheet
Are you able to see spreadsheet? I am not able to open it why?
You are amazing! Thank you, thank you, thank you!
Spreading this knowledge for free ..❤
thanks for sharing your knowledge.
Hero in disguise!
Great video!
I learnt about you through my FR teacher Kalpesh Mehta who teach FR in JKSC CA Final Batch. He claimed Aswath is the best in valuation
Thank you professor!
Dear Professor, great video sir and your knowledge sharing enlightened me so much. some times I face challenges to estimate the effective tax rate. Any guidance on this?
Holy moly, thank you so much.
Thank you, you are amazing!
perfect , Thanks Professor
may god bless you sir
Thanks for this information video! I learn a lot from it. One question I still have after this video is that: what number should I put into "Book value of debt" for a bank. A bank record all various things as interest bearing liabilities. I feel it's not quite right to put $0 here, but don't know which reporting number to look for.
Thank you Prof!
Hi, many thanks for the excel model, it's very useful! Just a question, how should I consider in the file dividend payments?
Thank you for this wonderful video & material - a question on R&D Conversion. If a company releases R&D figures where part of the total has already been capitalized, and the proportion is known, should only the OpEx R&D figures go into the converter, as the capitalized portion will already been assumed as part of the other inputs, or does this skew the R&D data without ever considering the already-capitalized expenses? Thanks
thank you sir for your openess.....
Hello, thanks a lot for all of this.
About the R&D converter page, if I'm not mistaken the table with estimates for the différents industries doesn't appear on the file I downloaded today (16/02/2024).
Regards
Fixed it.
Thank you so much 🙏
Thank you sir!
Very appreciated.
thank you professor
Hi Aswath! Are you planning to revisit CART? They had two earning calls after you covered their IPO.
thanks prof!
Can you please make a video elaborating on the reinvestment part in the 'valuation output' sheet? thank you, sir!
Prof Aswath, thanks for your updated excel which I have been using extensively.
Notice that the formula of Reinvestment in Valuation Output has changed due to the new insertion of 'year lag between reinvesting and generating growth from that reinvestment' in cell B56 of Input cell. In previous version, the reinvestment used the revenue difference between next and current year to calculate. However, this updated excel uses revenue difference between previous and current year instead as default (when input 'No' in B56); and between next and current year when year lag is 0 year.
May I know whether there is some variable error or it is the correct formula that rectify previous version bug? Should 0 year lag and default assumption (when stated 'No') be using the same revenue numerator variables instead?
Tks for your kind advice.
Could you share some more information on the sales to capital ratio? I’m valuing a growing p&c insurer and the standard 1.5 ratio is giving me a negative FCFF. Not too sure what it means and I’d like to understand it better. Thanks!
Hi professor thank you so much for this! How does one value a pre-revenue clinical development and asset biotech? Your breakdown of that would be most appreciated!
If there's a way to connect with you on this, would be greatly appreciated
I love it
Great video! Just curious as to how you all come up with your sales to capital ratios? I often feel like I’m just pulling a number out of a hat based on this year and previous years.
It is the weakest link, since it is purely empirical. That is why I have computed all of the variants of sales to capital ratio (average and marginal) by industry group and for the company.
@profaswath ... i would like to ask if it is possible to use this valuation Excel tool to evaluate a business unit or country business entity ? if yes, would it be possible to explain/illustrate how it might be done ? it would be much appreciated.
❤ thank you
Thank you
Thank you sir
I love the spreadsheet but there are 2 things I am uncertain about. Is there no need to add capex and depreciation & amortization because of the sales to capital ratio? Because I don’t see depreciation and amortization added to my fcff‘s, and I don’t see an option to add capex.
Also, does the beta in the detailed coc variation account for the debt/equity ratio of the company?
Hello Professor Damodaran, I noticed that the cells in the lookup table for Approach 3 on the Cost of Capital Worksheeet is hard coded with various percentages that are added to the risk-free rate. Can you tell us where to find the source data for those percentages? I assume they will change over time. Thank you!
They are based upon the histogram of the costs of capital of all publicly traded firms in the world that I update at the start of every year. You should be able to see the most recent year’s update in the data updates at the start of the year.
Kindly do one for financial services companies like banks please.
Shouldn't we add D&A to the FCF in the Valuation Output tab? Or I am missing something? Thanks.
Question / comment: Cell B31 Sales to capital ratio for 1-5 years points to years 1 to 4 instead of 1 to 5 on the Valuation output sheet, otherwise great spreadsheet :-)
Fixed. Thank you!
If I capitalize r&d, will the spreadsheet account for that with the operating profit? Because if I use the operating profit or ebit from the 10k, r&d is already subtracted, so I don’t want to double count it.
Great sir
Sorry to ask sir could you come up with an Indian multi business and different business model because I am not getting the industry average for India
wait, if I'm doing this in November like you said and I'm using the 3 most recent quarters of 2024 and the last quarter of 2023 as my last 12 months, then the last 10k would be the 2023 annual report? which also includes the last quarter of 2023?
Hi! in cell B21 i have to insert: Implied Shares Outstanding ? Thank you very much!
Thank you very much! Can I use this spreadsheet for a private company valuation and what should I input for stock price if we have no idea with the company owner what is the current valuation and at what price we should price our business at?
Enter the book value of equity per share (or input one share in the share count and the aggregate book equity) in the share price cell. The spreadsheet uses the stock price only if you use the detailed cost of capital approach. Go the cost of capital options, and choose one of the other approaches, and it should work.
@@AswathDamodaranonValuation Thank you very much! You are the best )))
I am confused with the reinvestment calculation in Terminal Value / period. Why Growth Rate is divided by ROC to get reinvestment rate ?
😊 #zomato is still cheap.
Your Valuation Video on it made my Conviction Strong.
Thanks 😊
Did he valuated again Zomato in recent year
24:53 "Well again, I'm a full service operation"
Classic lol. Thanks for the laughs professor.
Thanks so much Professor Damodaran, really practical and insightful as ever. For some reason, however, when I fill out Input Sheet cell B28 with a number for Compounded annual revenue growth rate - years 2-5 above a certain value it actually reduces the resulting Valuation Output, even down to a minus. I would have thought that the higher the revenue CAGR the higher the company should be valued. Can you assist with what I'm doing wrong? Many thanks!
Growth can add, destroy or do nothing for value. The good side of growth, which is that it makes your revenue higher has to weighed against the cost of delivering that growth, in the reinvestment. In your firm, the bad side is winning out.
@@AswathDamodaranonValuation Thanks so much! What a pleasure to be able to learn from you.
I am doing something wrong. I downloaded the template and ran it without changes, but i get a sub$100 value for the value of AMZN. Any ideas or things to check?
Has anyone got this to work in Google Sheets? One of the first things I noticed was the drop down on the 'Input' sheet for 'Industry' does not pull the data from the 'Industry Averages' sheet because the sheet is trying to pull data from a 'Master data' sheet which I can not locate. Ive opend this file in the free version of Excel but I was not able to find the 'Master data' sheet.
Any help or clarification would be appreciated.
Thank you, professor, and NYU! it has been a long time since college I am now investing, and using your Valuation spreadsheet, only I am having problems with the Book value of Debt? using your video and Amazon and the 2024 10-K only I'm not sure how you got the numbers as mine do not match. I am adding long term lease, long term Debt, and Other Long term? also I cannot for my life find the cross holdings and non-Operating assets? I have the same balance sheet as you professor? not sure how you get 2,954.00 and 2,815.00
Here's how to get the cross holdings
Page 47, fair value of financial instruments
Equity warrants of $2100 million in 2022 and $2200 million in 2023
Page 49 investments
Carrying value of investment in 2022 was $715 Mil and in 2023 was $754 Mil
Adding them together
2022 - $2815 Mil
2023 - $2954 Mil
Hope this helps
Grazie
Hi I think that there is a formula that is not working properly in the sheet "Cost of capital worksheet" B27 when in B25 "country of incorporation" is selected: the vlookup in the sheet "Country equity risk premiums" should be extended from row 181 to row 195
One semester teaching in a 45 min session. 🎊 🎉
Thank you so much. and I need to know how to get Book Value of Debt?
Yes, have you found out?