Home
Search results “Investment models excel”
Value-Add Hotel Investment Model in Excel
 
22:46
To download this Hotel Investment Model, to here: http://www.adventuresincre.com/hotel-valuation-model/ For more information about the author, see: http://www.spencerburton.org
Views: 9399 Spencer Burton
Financial Modeling Quick Lesson: Building a Discounted Cash Flow (DCF) Model - Part 1
 
11:07
Learn the building blocks of a simple one-page discounted cash flow (DCF) model consistent with the best practices you would find in investment banking. If you are preparing for investment banking interviews, know that the DCF is the source of a TON of investment banking interview questions. To download the backup Excel file, go to www.wallstreetprep.com/blog/financial-modeling-quick-lesson-building-a-discounted-cash-flow-dcf-model-part-1/ The DCF modeled here is a simplified version of a fully-integrated DCF model. For a deeper dive into DCF modeling in Excel, please visit www.wallstreetprep.com.
Views: 327257 Wall Street Prep
Excel Shortcuts Investment Banking: Quick Tips
 
10:11
You’ll get a quick, but very powerful, tip on how to optimize your Excel setup with the Quick Access Toolbar (QAT) and custom shortcuts in this tutorial. These tips will save you a ton of time when creating valuations, organizing data, and doing any formatting exercise. http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers" Shortcuts Introduced: These are all BUILT-IN shortcuts: Alt, T, O: Options Menu Alt, H, FC: Font Color Alt, H, FS: Font Size Alt, H, H: Fill Color Alt, H, A, C: Center Alt, H, B: Borders Alt, H, O, I: AutoFit Column Width Alt, H, O, W: Column Width Alt, H, 0: Increase Decimal Places Alt, H, 9: Decrease Decimal Places These are the NEW shortcuts you can create via the Quick Access Toolbar: Alt, 1: Font Color Alt, 2: Font Size Alt, 3: Fill Color Alt, 4: Center Alt, 5: Borders Alt, 6: AutoFit Column Width Alt, 7: Column Width Alt, 8: Increase Decimal Places Alt, 9: Decrease Decimal Places Lesson Outline: Many Excel shortcuts that you use repeatedly when creating valuations, models and when formatting data are cumbersome to enter. Something as simple as changing the font color takes 4 keystrokes – Alt, H, F, C – if you use the built-in method for it. Other common commands such as alignment, fill colors, borders, and column widths also take 3-4 keystrokes. A more efficient alternative is to set up the Quick Access Toolbar (QAT) so that you can access the most common commands with shortcuts like Alt, 1 instead. You can either import our file (see the link below under RESOURCES) or go to the Options menu (Alt, T, O) and then the Quick Access Toolbar tab, and create the menu yourself. We recommend setting “Font Color” in position #1, followed by Font Size, Fill Color, Center, Borders, AutoFit Column Width, Column Width, and Increase and Decrease Decimal places. These are some of the most frequently used commands in Excel, and you’ll save a ton of time with the new, shorter versions. A command like AutoFit Column Width that used to take 4 keystrokes now takes only 2 (Alt, 6) with this approach. You might realize 30-40% time savings when working in Excel if you use this full set of shortcuts. They’re especially useful for formatting and analyzing data and doing the initial setup in financial models. RESOURCES: https://youtube-breakingintowallstreet-com.s3.amazonaws.com/Excel-QAT-Export.exportedUI https://youtube-breakingintowallstreet-com.s3.amazonaws.com/Excel-Shortcuts-Investment-Banking-Slides.pdf
How to Build a Financial Model in Excel - Tutorial | Corporate Finance Institute
 
22:48
How to Build a Financial Model in Excel - Tutorial | Corporate Finance Institute Learn how to build a financial model in Excel with our video course (part 1). Enroll in the FULL course to earn your certificate and advance your degree: http://www.corporatefinanceinstitute.com In this course you will learn to build a financial model from scratch by working in Excel and following along with the video. Upon successfully completing the course and all quizzes you will obtain your Financial Modeling Certificate from the Corporate Finance Institute. -- FREE COURSES & CERTIFICATES -- Enroll in our FREE online courses and earn industry-recognized certificates to advance your career: ► Introduction to Corporate Finance: https://courses.corporatefinanceinstitute.com/courses/introduction-to-corporate-finance ► Excel Crash Course: https://courses.corporatefinanceinstitute.com/courses/free-excel-crash-course-for-finance ► Accounting Fundamentals: https://courses.corporatefinanceinstitute.com/courses/learn-accounting-fundamentals-corporate-finance ► Reading Financial Statements: https://courses.corporatefinanceinstitute.com/courses/learn-to-read-financial-statements-free-course ► Fixed Income Fundamentals: https://courses.corporatefinanceinstitute.com/courses/introduction-to-fixed-income -- ABOUT CORPORATE FINANCE INSTITUTE -- CFI is a leading global provider of online financial modeling and valuation courses for financial analysts. Our programs and certifications have been delivered to thousands of individuals at the top universities, investment banks, accounting firms and operating companies in the world. By taking our courses you can expect to learn industry-leading best practices from professional Wall Street trainers. Our courses are extremely practical with step-by-step instructions to help you become a first class financial analyst. Explore CFI courses: https://courses.corporatefinanceinstitute.com/collections -- JOIN US ON SOCIAL MEDIA -- LinkedIn: https://www.linkedin.com/company/corporate-finance-institute-cfi- Facebook: https://www.facebook.com/corporatefinanceinstitute.cfi Instagram: https://www.instagram.com/corporatefinanceinstitute Google+: https://plus.google.com/+Corporatefinanceinstitute-CFI YouTube: https://www.youtube.com/c/Corporatefinanceinstitute-CFI
Investment Banking 101: Operating Model
 
01:18:05
In this episode of the financial modelling series Sergey, ex-Goldman Sachs investment banker, will describe how to build an operating model for a start-up company with the step-by-step financial modelling guide. 00:47 income statement assumptions total revenue cost of goods sold research & development sales & marketing general & administrative expenses other expenses 26:00 balance sheet assumptions operating assets operating liabilities capex & depreciation schedule other assumptions 37:35 income statement revenue gross profit operating expenses operating income ( EBIT) prfoit before taxes net income 49:10 balance sheet assets liabilities & shareholders equity 01:04:08 cash flow statement cash flow from operating activities Original Excel file with financial model can be found here: https://goo.gl/QScJgJ Our Investment Banking preparation course https://youtu.be/bBMmN8Cmq3g WANT TO GET INTO INVESTMENT BANKING? Join Sergey's course on Investment Banking Interview Prep https://edu.fless.pro/investment-banking-interview-prep-course SUBSCRIBE AND STAY WITH US! FLESS https://fless.pro Instagram https://www.instagram.com/flesspro Facebook https://www.facebook.com/flesspro VK https://vk.com/flesspro Telegram https://t.me/flesspro
Views: 4865 Fless
Single Family Residential Investment Model - Rental or Flip
 
24:29
This Excel spreadsheet is for performing financial analysis on single family investments, such as flipping homes or homes for rent. To download the model, visit: http://www.adventuresincre.com/single-family-residential-investment-analysis-model/ For more Excel real estate models, visit: http://www.adventuresincre.com/category/re-modeling/excel-models/ To contact Spencer Burton, visit: http://www.spencerburton.org
Views: 4877 Spencer Burton
Excel Tutorial - How to Color Code Financial Models
 
12:21
This will cover how to color code your financial models so that hard-coded numbers and constants are in blue, formulas and text are in black. By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers" You will also learn how to make the links to other worksheets show up in green. In addition to a simple method using the F5 shortcut key to jump to different cell types, you'll also learn how to use VBA code to accomplish this in a more elegant way that checks for different cases. Here's an outline of what we cover in the lesson, along with the shortcuts used and the VBA code that you can use in your own models and spreadsheets: Why is Color Coding Important? Makes it easy to see at a glance what is a formula vs. constant vs. text vs. link to other workbook or worksheet. Standards vary between different groups and firms, but you almost always see the following: Blue = Constants / hard-coded numbers. Black = Formulas and text. Green = Links to other worksheets. Blue w/ yellow background and border = Input cell. (This one is less important) Excel Shortcuts for Finding Different Cell Types: F5 Jump to Cell (PC / Mac) F5, Alt + S + O + X Highlight Constants (PC) F5, Alt + S + F + X Highlight Formulas (PC) F5, ⌘ + S, ⌘ + O Highlight Constants (Mac) F5, ⌘ + S, ⌘ + F Highlight Formulas (Mac) Alt + H + FC Font Color Alt + H + H Cell Fill Color On the Mac, you'll have to use CMD + 1 to access font and fill colors instead. Ctrl + F Find (PC) CMD + F Find (Mac) F2 Edit Cell (PC) Ctrl + U Edit Cell (Mac) How to Find Links to Other Worksheets / Workbooks: Highlight formulas and... Search for "!" but not 100% accurate, since hard-coded text might also have it. And sometimes, a formula might have "!" but is NOT just a direct link elsewhere - it might just use a link from another worksheet IN a formula instead. Better Solution - If You're Comfortable with VBA: Please see the Excel file at this link below - YouTube does not allow us to upload VBA code because of some of the characters included in the code. So you can get the full macro and the VBA code from the Excel file below: http://youtube-breakingintowallstreet-com.s3.amazonaws.com/Excel-Color-Code-Financial-Models.xlsm
Ch04-01 Investment Problem Excel Model
 
14:43
This video is part of a lecture series available at https://www.youtube.com/channel/UCMvO2umWRQtlUeoibC8fp8Q
Views: 6406 Decision Making 101
Real Estate Equity Waterfall Model in Excel (IRR Hurdles)
 
08:20
Note: A few errors were identified (you may see in the video) that have since been fixed in the most recent version of this model. An option to promote off of equity multiple hurdles has also been added. To download the most recent version of this model: https://www.adventuresincre.com/real-estate-equity-waterfall-model/ This is a real estate equity waterfall model for inclusion in your real estate investment models. It is NOT standalone, as it requires you to have already modeled your property-level, levered cash flows. This video will walk you through how this model works, and will help you incorporate the module into your own models.. Visit our entire collection of free Excel real estate financial analysis models at http://www.adventuresincre.com To learn more Spencer, visit: http://www.spencerburton.org
Views: 49102 Spencer Burton
Building a Three Statement Financial Model: Part 1
 
52:25
In this video I show how to create a quick & dirty 3 statement model using Toys R Us as an example company. Sorry about the abrupt ending; please let me know if you have any questions! (Quick note:at the end that current liabilities should be added to Total Liabilities in row 26, K through N).
Views: 109744 josephlaurent
Basic Monte Carlo Simulation of a Stock Portfolio in Excel
 
11:30
https://alphabench.com/data/monte-carlo-simulation-tutorial.html Demonstration of a simple Monte Carlo simulation technique or Monte Carlo method that utilizes the Excel Data Table feature to replicate iterations. This tutorial models annual investments in an S&P 500-like environment. No add-ins are used; 100% pure Excel. Monte Carlo simulation in Excel typically makes use of add-in software for Excel like Palisades Decision Suite or Oracle's Crystal Ball, but we can do a reasonable job modeling Monte Carlo Simulation with Excel, using just Excel. Monte Carlo Simulation is one of the most highly used and important numerical techniques used in finance. A dynamic histogram can be added to further characterize a return profile. See my video on the topic if interested: https://youtu.be/WsQH3AtJqxY For a Monte Carlo simulation to approximate individual stock price movement see: https://youtu.be/1ot7HOI3wQE
Views: 191557 Matt Macarty
How to Build a Forecasting Model in Excel - Tutorial | Corporate Finance Institute
 
19:24
How to Build a Forecasting Model in Excel - Tutorial | Corporate Finance Institute Enroll in the Full course to earn your certificate and advance your career: http://courses.corporatefinanceinstitute.com/courses/fpa-rolling-12-month-cash-flow-forecast-course Master the art of building a rolling 12-month cash flow forecast model in our Financial Planning & Analysis (FP&A) course. In this course you will learn to build a cash flow model from scratch complete with assumptions, financials, supporting schedules and charts. -- FREE COURSES & CERTIFICATES -- Enroll in our FREE online courses and earn industry-recognized certificates to advance your career: ► Introduction to Corporate Finance: https://courses.corporatefinanceinstitute.com/courses/introduction-to-corporate-finance ► Excel Crash Course: https://courses.corporatefinanceinstitute.com/courses/free-excel-crash-course-for-finance ► Accounting Fundamentals: https://courses.corporatefinanceinstitute.com/courses/learn-accounting-fundamentals-corporate-finance ► Reading Financial Statements: https://courses.corporatefinanceinstitute.com/courses/learn-to-read-financial-statements-free-course ► Fixed Income Fundamentals: https://courses.corporatefinanceinstitute.com/courses/introduction-to-fixed-income -- ABOUT CORPORATE FINANCE INSTITUTE -- CFI is a leading global provider of online financial modeling and valuation courses for financial analysts. Our programs and certifications have been delivered to thousands of individuals at the top universities, investment banks, accounting firms and operating companies in the world. By taking our courses you can expect to learn industry-leading best practices from professional Wall Street trainers. Our courses are extremely practical with step-by-step instructions to help you become a first class financial analyst. Explore CFI courses: https://courses.corporatefinanceinstitute.com/collections -- JOIN US ON SOCIAL MEDIA -- LinkedIn: https://www.linkedin.com/company/corporate-finance-institute-cfi- Facebook: https://www.facebook.com/corporatefinanceinstitute.cfi Instagram: https://www.instagram.com/corporatefinanceinstitute Google+: https://plus.google.com/+Corporatefinanceinstitute-CFI YouTube: https://www.youtube.com/c/Corporatefinanceinstitute-CFI
Linear Programming Investment Problem
 
07:21
Using Solver to determine the maximum return on multiple investment
Views: 10259 Roberta Aronoff
Top 5 Excel Features for Financial Modellers
 
07:50
In this video, we look at 5 Excel features that most financial analysts aren't even aware of. Find out what they are how to use them. (Video 8 of 8 for the course on Financial Modeling using Excel) To get CPD or CPE for this course, visit the Proformative Academy at www.proformative.com and use the COOK10 discount code to get a discount on your subscription. Learn more and become student at EF University for FREE - http://executivefinance.teachable.com/ Like us Facebook- https://www.facebook.com/exfinance/ Linkedin- https://www.linkedin.com/company/executive-finance Twitter- https://twitter.com/exfinance
Views: 43328 Executive Finance
How to Build a Basic Financial Model in Excel
 
10:10
Here is a quick lesson on how to build a basic financial model in Excel 2016. In this video we focus on the P&L, or income statement. Course discount: https://www.udemy.com/excel-power-user/?couponCode=YOU-TUBE ________ We learn how to build a basic financial model, specifically an income statement. We'll review how to apply industry formats to assumptions and formulas to model clearly. All financial models in Excel begin with the income statement - or P&L. First we will build a set of assumptions, and then we will build a financial model based on our initial assumptions. If you'd like to learn more, I built a business-focused Excel course on Udemy that is stripped down to the core functions for being an analyst. The objective is to get people to the power-user level quickly. Check it out in the link above! The course is about 2 hours and includes things like: -completely abandoning the mouse to increase speed 3X -data manipulation & visualization -conditional statements & pivot tables For additional tutorials about Excel, check out my Quora profile: https://www.quora.com/profile/Eric-Andrews-5 For additional information about the course, check out my website: http://excelpoweruser.weebly.com/
Views: 61648 Eric Andrews
How to Create Financial Scenarios in Excel
 
15:39
This tutorial analyzes advanced financial modeling tools provided by Excel 2010 to create multiple financial scenarios.
Views: 173619 edutechional
Financial Modeling Quick Lesson: Accretion / Dilution - Part 1
 
08:52
To download the model template that goes with this lesson, please go to http://www.wallstreetprep.com/blog/financial-modeling-quick-lesson-accretion-dilution-model/. In this lesson, we'll learn how to build an accretion/dilution model. One of the core models investment banking analysts and associates have to build when analyzing an acquisition is the accretion/dilution model. The underlying purpose of such an analysis is to assess the impact of an acquisition on the acquirer's expected future earnings per share (EPS). It is also a frequent topic of investment banking interview questions.
Views: 23659 Wall Street Prep
Capital Budgeting in Excel Example
 
16:54
Description
Views: 32703 financecanbefun
WST: 4.1 Investment Banking Training - Basic Valuation Methodologies
 
04:44
Wall St. Training Self-Study Instructor, Hamilton Lin, CFA reviews the basic valuation methodologies utilized by investment bankers and professionals to value companies, ranging from trading comps to deal comps to DCF to break-up analysis and other metrics. For more information of the video courses previewed here, go to: http://www.wstselfstudy.com/modules.html Over 80 hours of online, interactive Self-Study Videos! ***YOUTUBE VISITORS ONLY*** 10% off any online course, use Discount code: youtube http://www.wstselfstudy.com Wall St. Training Self-Study provides online, video-based, self-study financial modeling training solutions to Wall Street. Our interactive course modules are Excel-based and specialize in advanced and complex financial modeling, valuation modeling, investment banking, mergers & acquisitions and leveraged buyout training topics. Enhance your skills and master the content required by Wall Street investment banks, M&A, research, asset management, credit, and private equity firms.
Views: 54097 wstss
Watch Me Build a Multifamily Real Estate Model
 
38:40
Watching others model is a great way to learn new tricks, and improve your own modeling abilities. I turned on my screen recorder and built a basic multifamily real estate model. Download the completed model here: https://www.adventuresincre.com/watch-build-multifamily-real-estate-model/ Learn more about the author: http://www.spencerburton.org
Views: 41230 Spencer Burton
Excel Crash Course for Finance Professionals - FREE | Corporate Finance Institute
 
01:15:58
Excel Crash Course for Finance Professionals - FREE | Corporate Finance Institute Enroll in the FREE full course to earn your certification and advance your career: http://courses.corporatefinanceinstitute.com/courses/excel-crash-course-for-finance The ultimate Excel crash course for finance professionals. Learn all the Excel tips, tricks, shortcuts, formulas and functions you need for financial modeling in this free online course. Key concepts include: formatting, ribbon shortcuts, if statements, eomonth, year, paste special, fill right, fill down, auto sum, sumproduct, iferror, today(), concatenate, special numbers, vlookup, index, match, xirr, xnpv, yearfrac, and much more. -- FREE COURSES & CERTIFICATES -- Enroll in our FREE online courses and earn industry-recognized certificates to advance your career: ► Introduction to Corporate Finance: https://courses.corporatefinanceinstitute.com/courses/introduction-to-corporate-finance ► Excel Crash Course: https://courses.corporatefinanceinstitute.com/courses/free-excel-crash-course-for-finance ► Accounting Fundamentals: https://courses.corporatefinanceinstitute.com/courses/learn-accounting-fundamentals-corporate-finance ► Reading Financial Statements: https://courses.corporatefinanceinstitute.com/courses/learn-to-read-financial-statements-free-course ► Fixed Income Fundamentals: https://courses.corporatefinanceinstitute.com/courses/introduction-to-fixed-income -- ABOUT CORPORATE FINANCE INSTITUTE -- CFI is a leading global provider of online financial modeling and valuation courses for financial analysts. Our programs and certifications have been delivered to thousands of individuals at the top universities, investment banks, accounting firms and operating companies in the world. By taking our courses you can expect to learn industry-leading best practices from professional Wall Street trainers. Our courses are extremely practical with step-by-step instructions to help you become a first class financial analyst. Explore CFI courses: https://courses.corporatefinanceinstitute.com/collections -- JOIN US ON SOCIAL MEDIA -- LinkedIn: https://www.linkedin.com/company/corporate-finance-institute-cfi- Facebook: https://www.facebook.com/corporatefinanceinstitute.cfi Instagram: https://www.instagram.com/corporatefinanceinstitute Google+: https://plus.google.com/+Corporatefinanceinstitute-CFI YouTube: https://www.youtube.com/c/Corporatefinanceinstitute-CFI
Create an Investment Schedule in Excel
 
07:59
Video shows how to create an investment schedule in Excel
Views: 1610 Ronald Sweet
Simple LBO Model - Case Study and Tutorial
 
13:25
In this LBO Model tutorial, you'll learn how to build a very simple LBO model "on paper" that you can use to answer quick questions in PE (and other) interviews. By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers" This matters because in many cases, they'll ask you to calculate numbers such as IRR and multiple of invested capital very quickly and will not actually ask you to build a more complex model until later in the process. You should always START this exercise by looking at the actual question or set of questions they are asking you: "Calculate the purchase price required for ABC Capital to obtain a 3.0x multiple of invested capital (MOIC) if it plans to sell OpCo after five years at an EV / EBITDA multiple of 6.0x." So they're giving you the exit multiple and the return on investment that the PE firm is targeting, and you have to figure out the initial purchase price by "working backwards." Here's how we interpret each line in this case study and use it in the model: "OpCo currently has EBITDA of $250mm, and ABC believes that the new management team could keep EBITDA flat for the next 5 years." This tells you to make the initial EBITDA $250mm and keep it at that level for 5 years - skip revenue, COGS, OpEx, and everything else because none of that matters if this is all they give you. "ABC Capital has obtained debt financing of $750mm at 10% interest, and OpCo expects working capital to be a source of funds at $6mm per year." The initial debt balance is $750mm and there's a 10% interest rate, so the interest expense will be $75mm per year. In the "Cash Flow Statement Adjustments", since Working Capital is a SOURCE of funds it will add $6mm to cash flow each year. "OpCo requires capital expenditures of $35mm per year, and it has a tax rate of 40%. Assume no transaction fees, zero minimum cash required, and that PP&E on the balance sheet remains constant for the next 5 years." Also in the CFS section, CapEx = $35mm per year, and Depreciation also equals $35mm per year since the PP&E balance does not change at all. So you can also fill in the Depreciation figure on the Income Statement. No transaction fees and no minimum cash requirement simplify the purchase price and debt repayment - although we don't even have debt repayment here. "Assume that excess cash is NOT used to repay debt, and instead simply accumulates on the Balance Sheet." This makes the final numbers easier to calculate, since interest expense will never change and you can simply add up cash generated to get to the final cash number at the end. PROCESS: 1. Start with the Income Statement - EBITDA is $250mm per year. Subtract Depreciation of $35mm per year, and interest of $75mm per year. So EBIT = $140mm. Taxes = $140mm * 40%, so Net Income = $140mm - $56mm = $84mm. 2. On the simplified CFS, Net Income = $84mm, Depreciation = $35mm, Change in Working Capital = $6mm, CapEx = ($35mm), so Cash Generated per year = $90mm. 3. EBITDA Exit Multiple = 6.0x, and final year EBITDA = $250mm, so Exit EV = $1.5B. Subtract the outstanding debt of $750mm and add the cash generated in this period of $450mm, so Equity Proceeds = $1.2B. 4. Targeted MOIC = 3.0x so the PE firm would have to invest $400mm in the beginning. $400mm equity + $750mm debt = $1.150B, so the purchase multiple is $1,150 / $250 = 4.6x. Further Resources http://youtube-breakingintowallstreet-com.s3.amazonaws.com/109-04-Simple-LBO-Model.pdf http://youtube-breakingintowallstreet-com.s3.amazonaws.com/109-04-Simple-LBO-Model.xlsx
Apartment Valuation Tutorial Using the Excel Real Estate Acquisition Model
 
15:37
A short video showing you how to use my real estate multifamily acquisition model to price out an actual deal from Loopnet. (Disclaimer: this is only a tutorial. The resulting value is not necessarily representative of the property's actual market value.) To download the free model, visit: http://www.adventuresincre.com/multifamily-apartment-acquisition-model-in-excel/ To learn more about the author, visit: http://www.spencerburton.org
Views: 16697 Spencer Burton
Financial Modeling Quick Lesson: Cash Flow Statement (Part 1)
 
12:39
Learn the building blocks of a financial model. In this video, we'll build a cash flow statement given an income statement and balance sheet in Excel. To download the Excel template that goes with this video, go to http://www.wallstreetprep.com/blog/financial-modeling-quick-lesson-cash-flow-statement-part-1/ The accounting here is a simplified presentation of how the three major financial statements are inter-related and lays the foundation of financial statement models in investment banking. Many accounting questions that we see time and again in finance interviews are designed to test the understanding explained in this exercise.
Views: 351311 Wall Street Prep
Valuation and Discounted Cash Flow Analysis (DCF)
 
01:26:16
Here's a quick overview on Valuation. We also construct an entire discounted cash flow analysis on WalMart in conjunction with my book Financial Modeling and Valuation: A Practical Guide to Investment Banking and Private Equity http://www.amazon.com/Financial-Modeling-Valuation-Practical-Investment/dp/1118558766/ref=sr_1_8?ie=UTF8&qid=1422553204&sr=8-8&keywords=valuation
Views: 80038 Paul Pignataro
NPV and IRR in Excel 2010
 
09:00
Description: How to calculate net present value (NPV) and internal rate of return (IRR) in excel with a simple example. Download the excel file here: https://codible.myshopify.com/products/npv-and-irr-in-excel-2010-excel-files Some good books on Excel and Finance: Financial Modeling - by Benninga: http://amzn.to/2tByGQ2 Principles of Finance with Excel - by Benninga: http://amzn.to/2uaCyo6
Views: 766865 Codible
Build an IRR Matrix for Real Estate in Excel
 
15:23
How to build a simple tool in Excel - the IRR Matrix - to analyze the internal rate of return of a real estate investment in each year of the projected hold period. The IRR Matrix is one tool for helping to assess the ideal hold period for an investment. To download the Excel Workbook used in this video, visit: https://www.adventuresincre.com/using-an-irr-matrix-to-analyze-hold-period To learn more about the author, visit: http://www.spencerburton.org
Views: 18133 Spencer Burton
Watch Me Model a Real Estate Private Equity Technical Interview Exercise in Excel
 
01:15:55
Warning: This video is long and boring! But, if you're preparing for a real estate technical interview, you'll likely find value in it. Watch me complete what is an actual real estate private equity modeling test. Download the Excel file and follow along (see notes below for edits). To download the Excel file that goes along with this video (both the finished version as well as a blank tab to follow along), visit: https://www.adventuresincre.com/watch-me-model-real-estate-technical-interview/ To learn more about the author: http://www.SpencerBurton.org A Couple of Notes/Corrections: At 21:20, the formula for the Loan Cash Flow should be Total Project Costs (row 22) minus Equity (row 26), not row 20 minus row 26. This error was fixed around the 26:00 mark, but was not made clear. The formula in C21 that sums all Construction Interest for the entire development period [=SUM(G21:CR21)] got zeroed out somehow. I didn't realize this had happened until the 1:00:00 minute mark, when I saw my Levered Cash Flow line had equity contributions beyond when I'd expect. Adding the formula in C21 back, corrected the issue.
Views: 15902 Spencer Burton
Watch Me Build a Basic Real Estate Equity Waterfall Model with IRR Hurdles
 
31:02
In this video, I build a basic, 3-tier real estate equity waterfall model with IRR hurdles. Download blank worksheet together with the completed version, and follow along to learn one way I model real estate partnership cash flows in Excel. To download the models that go along with this video, visit: https://www.adventuresincre.com/watch-build-equity-waterfall/ To learn more about or to contact the author, visit: http://www.SpencerBurton.org
Views: 10905 Spencer Burton
Pay Off Debt Now or Invest: Excel Decision Calculator
 
18:24
Get this model: http://www.smarthelping.com/2018/06/financial-model-to-help-decide-to.html Explore all of smarthelping's models: http://www.smarthelping.com/p/excel.html Should you pay off all your loans now or invest that money instead? This is a really interesting financial model to help the user see a comparison in total investment value when trying to the decide to invest more money now or pay off debt now and invest less. It all comes down to a race of which scenario has a higher investment value by the time all the debt is paid off. It will depend on your specific loan terms and annual % return of the investment you plan on having. There are some assumptions in the model so that it ensures an apples to apples comparison. For example, in the scenario of paying off the loan now, you will be putting the debt service cash flows that you would have been using to pay off the debt over time to work each month while in the pay off over time scenario you are paying off the debt so the investment is greater at the start but doesn't grow as fast depending on how much debt service there is and the loan terms. As loans gets paid off, the remaining amount will then also be invested in the loan. Once all debt is paid off, both scenarios stop the contribution of theoretical debt service. Disclaimer: I am not a financial advisor and you should use this at your own risk of loss/gain.
Views: 40 smarthelping
Dividend Discount Model - Commercial Bank Valuation (FIG)
 
18:31
Why the Dividend Discount Model (DDM) is used to value commercial banks instead of the traditional Discounted Cash Flow (DCF) analysis. By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers" There are 3 main reasons why the DCF and the concept of Free Cash Flow (FCF) do not apply to commercial banks: 1. You can't separate operating vs. investing vs. financing activities - the lines are very blurry for a bank, since items like debt are more operationally-related and fund the bank's lending activities. 2. CapEx doesn't represent re-investment in the business, as it does for a normal company - for a bank,"re-investment" means hiring people, doing more lending, etc. 3. Working Capital represents something much different for a bank - the standard definition of Current Assets Excl. Cash Minus Current Liabilities Excl. Debt makes no sense, because for banks that includes tons of investments, securities, other borrowings, etc. so you could see massive swings... What You Do Instead - Use Dividends as a Proxy for Free Cash Flow Why? Because banks are CONSTRAINED by capital requirements - according to the Basel accords (I, II, III), they must maintain a certain "buffer" at all times to cover unexpected losses on their loans... So just like CapEx requirements, Net Income growth, and Working Capital constrain FCF for normal companies, the Tier 1 Capital / Tangible Common Equity / Total Capital requirements constrain dividends for banks. So we'll project a bank's regulatory capital, its asset growth, and its net income, and use those to project its dividends - then, discount, and sum up the dividends and discount and add the NPV of its terminal value. How to Set Up a Dividend Discount Model (DDM) 1. Make assumptions for Total Assets, Asset Growth, targeted Tier 1 (or other) Ratios, Risk-Weighted Assets, Return on Assets (ROA) or Return on Equity (ROE), and Cost of Equity. 2. Next, project Assets and Risk-Weighted Assets. 3. Then, project Net Income based on ROA or ROE. 4. Then, project Shareholders' Equity (AKA Tier 1 Capital) based on targeted capital ratio... 5. And BACK INTO dividends! Different from a normal company's DDM! Set dividends such that the minimum capital ratio is maintained, based on starting Shareholders' Equity and Net Income that year. 6. Flesh out the rest of the model - stats, growth rates, other metrics. 7. Discount and sum up dividends. 8. Calculate, discount, and add Terminal Value so that NPV = NPV of Terminal Value + NPV of All Dividends. 9. Calculate the Implied Share Price and compare to actual Share Price. Is the bank undervalued? Overvalued? What are the clues so far? What Next? Try it with a real company, using its historical financial information. Add more complex / realistic assumptions, based on industry research, channel checks, the bank's own strengths/weaknesses, etc. Add more advanced features - other ways to calculate Terminal Value, more accurate regulatory capital, mid-year discount and/or stub periods, stock issuances / repurchases, multiple growth stages, and so on.
Estimate CAPM Beta in Excel
 
09:04
This is a supplement to the investment courses I teach.
Finance Lesson 4 - IBM Model Valuation
 
01:59:50
MODELS AND HELPFUL FILES LINK HERE: https://drive.google.com/folderview?id=0B7KHNVOBFX8qMEs1LWlKamZsRE0&usp=sharing
Views: 80103 Martin Shkreli
A 60 Minute Recipe for creating a Simple Project Finance Model - Part 1
 
14:27
http://www.videofinancialmodelling.com/ Part 1 of a tutorial developing a simple acquisition financial model for a toll road. If you want more free tutorials and spreadsheets (including access to this tutorials accompanying e-book) go to http://www.videofinancialmodelling.com/subscribe-to-newsletter/ and subscribe. Subscription is free.
Jensen's alpha definition for investment modeling
 
04:31
The definition, visualization and demonstration of a calculation of the Jensen's alpha in Excel. We discuss the two types of performance analysis: returns-based and holdings-based. For investment and financial modeling of stocks and portfolios. We discuss the use of the =AVERAGE function for returns and =STDEV.P for standard deviation, plus =INTERCEPT for the alpha measure. https://factorpad.com/fin/glossary/jensens-alpha.html Please note, here we use the method to source Jensen's Alpha from the regression. Many times the regression outputs are a given in the problem. If inputs are given, you can isolate the alpha term from the CAPM formula: Jensen's Alpha = (Portfolio Return - Risk Free) - Portfolio Beta * (Market Return - Risk Free) Topics covered in our investment glossary: Excel tutorial, Python examples, portfolio theory, portfolio return, portfolio risk, correlation, regression, linear algebra, alpha signal, risk models, performance attribution. Glossary: https://factorpad.com/fin/glossary/index.html Innovators: https://factorpad.com/fin/innovators/index.html https://factorpad.com
Views: 2183 FactorPad
Excel Finance Class 89: Sensitivity Analysis For Cash Flow & NPV Calculations
 
10:54
Download Excel workbook http://people.highline.edu/mgirvin/ExcelIsFun.htm See how to do Sensitivity Analysis and adjust a single variable for a NPV calculation. See the NPV and SLOPE function and TRANSPOSE array Functions and how to create a X Y Scatter chart.
Views: 78011 ExcelIsFun
Financial Modeling Quick Lesson: OFFSET / MATCH and Data Validation (Part 1)
 
17:53
Note: To download the Excel template that goes with this video, go to http://www.wallstreetprep.com/blog/financial-modeling-quick-lesson-2-offset-match-and-data-validation-part-1/ In this video, I'll show you how to integrate scenarios into financial models. We'll do this by building a drop down menu in Excel using data validation and connecting the drop down menu to the scenario analysis using the OFFSET / MATCH function.
Views: 95493 Wall Street Prep
Sensitivity Analysis for Financial Modeling Course | Corporate Finance Institute
 
34:17
Sensitivity Analysis for Financial Modeling Course | Corporate Finance Institute Enroll in the full course to earn a certificate and advance your career: http://courses.corporatefinanceinstitute.com/courses/sensitivity-analysis-financial-modeling This advanced financial modeling course will take a deep dive into sensitivity analysis with focus on practical applications for professionals working in investment banking, equity research, financial planning & analysis (FP&A), and finance functions. Course agenda includes: Introduction Why perform sensitivity analysis? Model integration - Direct and Indirect methods Analyzing results Gravity sort table Tornado charts Presenting results By the end of this course, you will have a thorough grasp of how to build a robust sensitivity analysis system into your financial model. Form and function are both critical to ensure you can handle quick changes and information requests when you're working on a live transaction.
Index Fund Portfolio Asset Allocation Model
 
07:40
This video presents a spreadsheet I built to construct a portfolio of Vanguard index funds that is more evenly distributed across sectors, market capitalization, and geographic location. Minimization of the portfolio's expense ratio and turnover rate were also critical factors that determined fund allocation. This spreadsheet allowed me to create a model portfolio on Google's finance page whose data is then reintroduced to update the spreadsheet. Re-balancing the portfolio is considered quarterly, if necessary. Contact Info [email protected] LinkedIn: linkedin.com/in/bwbuchmann YouTube: youtube.com/c/BeauBuchmann Twitter: @bwbuchmann Facebook: https://www.facebook.com/bwbuchmann/
Views: 5889 Beau Buchmann
Link the 3 Financial Statements in Excel - Tutorial | Corporate Finance Institute
 
41:43
Link the 3 Financial Statements in Excel - Tutorial | Corporate Finance Institute Download Excel template: https://corporatefinanceinstitute.com/resources/knowledge/modeling/link-the-3-financial-statements-cfi-webinar/ The 3 financial statements are all linked and dependent on each other. In financial modeling, your first job is to link all three statements together in Excel, so it’s critical to understand how they’re connected. This is also a common question for investment banking interviews, FP&A interviews, and equity research interviews. -- FREE COURSES & CERTIFICATES -- Enroll in our FREE online courses and earn industry-recognized certificates to advance your career: ► Introduction to Corporate Finance: https://courses.corporatefinanceinstitute.com/courses/introduction-to-corporate-finance ► Excel Crash Course: https://courses.corporatefinanceinstitute.com/courses/free-excel-crash-course-for-finance ► Accounting Fundamentals: https://courses.corporatefinanceinstitute.com/courses/learn-accounting-fundamentals-corporate-finance ► Reading Financial Statements: https://courses.corporatefinanceinstitute.com/courses/learn-to-read-financial-statements-free-course ► Fixed Income Fundamentals: https://courses.corporatefinanceinstitute.com/courses/introduction-to-fixed-income -- ABOUT CORPORATE FINANCE INSTITUTE -- CFI is a leading global provider of online financial modeling and valuation courses for financial analysts. Our programs and certifications have been delivered to thousands of individuals at the top universities, investment banks, accounting firms and operating companies in the world. By taking our courses you can expect to learn industry-leading best practices from professional Wall Street trainers. Our courses are extremely practical with step-by-step instructions to help you become a first class financial analyst. Explore CFI courses: https://courses.corporatefinanceinstitute.com/collections -- JOIN US ON SOCIAL MEDIA -- LinkedIn: https://www.linkedin.com/company/corporate-finance-institute-cfi- Facebook: https://www.facebook.com/corporatefinanceinstitute.cfi Instagram: https://www.instagram.com/corporatefinanceinstitute Google+: https://plus.google.com/+Corporatefinanceinstitute-CFI YouTube: https://www.youtube.com/c/Corporatefinanceinstitute-CFI
NAV Model (Oil & Gas): Production Decline Curve
 
24:28
When you're valuing an E&P (Exploration & Production) company, the Net Asset Value (NAV) Model is the key methodology. By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers" UNLIKE in a DCF, where cash flow growth is assumed into infinity, in a NAV model you assume the company's cash flows go to $0 eventually as it completely produces all of its reserves and has nothing left. A granular NAV model is complex, but it comes down to a 2-step process: Step 1: Model the company's existing production from wells it already has... and assume a decline rate for the annual production each year, also assuming commodity prices to determine revenue, and linking operating expenses to production and calculating cash flow like that. Step 2: Assume the company drills new wells in its PUD (Proved Undeveloped), PROB (Probable), and POSS (Possible) reserves. The second step involves dozens of sub-steps and assumptions, but here we're just going to focus on ONE small part of this process: estimating the decline rate of a new well the company drills. It starts off at a very high production rate, but then declines quickly within even the first year of its useful life - and we need to estimate the decline rates each year to build the rest of the model. You COULD do lots of complicated math, try fitting hyperbolic or exponential functions, run a regression analysis, etc., but we suggest a much simpler approach here: if the company doesn't disclose data on its decline rates for individual wells, find data from another company operating in the same region and fit it to your company's "average" wells. How to Do That: Step 1: Find the company's key data, such as the EUR per well and IP rate per well in the region you're looking at. Step 2: Now, see if the company discloses data on its own decline rates... if so, you're set! If not, or if it's not enough, find another company operating in the region that discloses more data (EQT here), and go to that company's investor presentations to get the numbers. Step 3: In the first year, assume that production is some % of 365 * IP Rate per Well... because there is a huge drop-off in daily production from Month 1 to Month 12 in that first year. EQT's data shows 45%; we assume 60% here since UPL has a slightly flatter decline curve. Step 4: Copy and paste the other company's decline rates into each year of your decline curve. Step 5: Enter the correct formula for calculating annual production each year AFTER the initial year... here: =MIN(AU129*(1+AT130),$AT$126-SUM(AU$129:AU129)) Want to take either Last Year Production * (1 + Decline Rate) (the first part), or the total remaining reserves in this well (the second part). Step 6: Set up Subtotal / Remainder / Total math and ensure that everything is produced. Step 7: "Fit the data" using Goal Seek and the Factor - multiply each decline rate by a certain factor and use Goal Seek (Alt + A + W + G) to solve for the factor that makes the Subtotal equal to the EUR. Step 8: Build in support for a different EUR by scaling the production up or down in the "Total" column. Step 9: Allocate the production to oil vs. gas. vs. NGLs. Step 10: Complete the Subtotal / Remainder / Total math at the bottom. What Next? Next, we'd complete this process for all the wells the company drills in every region, estimate revenue, expenses, and cash flow for each one, and then aggregate the discounted cash flow values in every region across all reserve types... Which brings us closer to the implied NAV per share, which is what the NAV model is really all about. Stay tuned for more!
Revenue Models for Consumer Retail Companies
 
18:18
In this Revenue Models lesson, you'll learn how to build a revenue model for a consumer retail company. By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers" Chuck E. Cheese, a kids' restaurant chain that was acquired by Apollo for $1.3 billion, is used in this example since their data is readily available and easy to use Table of Contents: 0:39 Why Revenue Models Are Important 2:19 How to Set Up Revenue Models - Units Sold and Market Size Methods 3:39 How You Build a Revenue Model - Examples for Different Industries 5:03 Step 1 - Finding Historical Data 5:59 Step 2 - Assumptions for Stores Opened and Closed 8:02 Step 3 - Assumptions for Sales per Store Growth 9:03 Step 4 - Calculating Ending Stores per Year 10:30 Step 5 - Toggle Calculations for Sales per Store 11:08 Step 6 - Splitting Revenue Into Segments 14:20 Step 7 - How to Review and Tweak the Numbers 15:18 Recap and Summary Why Do Revenue Models Matter? It's a very common topic in case studies and interviews in IB, PE, HFs, and anything else in finance. Revenue models can come up in LBO case studies, 3-statement modeling case studies, normal interview questions, and, of course, on the job. Often, you have enough data to make MORE than just a simple % growth rate assumption for revenue... but not enough data to do the same on the expense side. Theoretically, you could just say 2%, 3%, 4%, etc. growth each year and project revenue like that. BUT it's much more credible to say, "We have 50 stores each generating $2 million in annual sales, on average, and we plan to open 5 new stores per year for the next 5 years -- based on that, revenue is expected to be..." rather than "We're assuming 4% revenue growth per year." The numbers you get will NOT necessarily be different or "more accurate" -- you're still predicting the future! But at least your numbers will have more real-world support behind them... What is a Revenue Model? It can be done many different ways, but most revenue models boil down to Units Sold * Average Selling Price, or Total Market Size * % Market Share. The best method depends on the available data, the work and research you've done, and what the company discloses. For this consumer/retail example, it makes the most sense to use a variation on Units Sold * Average Selling Price, since "market share" is almost impossible to establish for a large and fragmented market like restaurants. How Do You Build a Revenue Model? For retailers, you can divide revenue into into existing stores vs. new stores and assume a figure for average Sales per Square Foot/Meter, or Sales per Store, and then make assumptions for new stores opened, stores closed, and how the sales per store figures change over time. Here's what we cover in this example for Chuck E. Cheese: Step 1: Get the historical data you need -- in this case, the # of stores opened and closed in prior years, and the average sales per store type. These are all taken from the company's filings. Step 2: Make assumptions for the # of stores opened and closed each year -- companies often disclose their plans in their filings, or you can extrapolate from historical data. In this case, CEC told us directly how many stores it planned to open over the next 4 years. Step 3: Assume a growth rate in Sales per Comparable (Existing) Store, and Sales per New Store. Step 4: Calculate Ending Stores each year, with support for the sensitivity toggles built in so that we can easily modify the assumptions. Step 5: Now, make similar "post-toggle" calculations for Sales per New Store and Sales per Existing Store. Step 6: Now, divide the revenue into segments, if applicable... it is very much applicable here! There are different margins for entertainment vs. food and beverages, and there's a clear trend in one direction (away from food and beverages). Step 7: Now, go back and check your numbers, fill in the miscellaneous and smaller items, and see how equity research estimates (and other sources) compare to what you've come up with. Go back and tweak your numbers as necessary. What Next? Pick a company you're interested in, in an industry that's relatively easy to analyze, and project revenue based on what's in their filings. It doesn't have to be super-complicated -- for most companies, revenue comes down to less than 5 key drivers. Avoid conglomerates, companies with tons of business lines, or industries that are more complex, such as oil & gas, commercial banking, etc. Suggestions: Airlines, technology, consumer/retail, industrials/manufacturing, healthcare is iffy because it can get very complex to model a company with a huge drug portfolio. Further Resources http://youtube-breakingintowallstreet-com.s3.amazonaws.com/CEC-Revenue-Model.xlsx
Investment Property Spreadsheet Real Estate Excel ROI Income NOI Template
 
08:25
Get the spreadsheet at https://mspreadsheets.myshopify.com/
Views: 54053 notmfb
351-8 How to Build a Portfolio in Excel
 
19:29
Walkthrough for Assignment 8 for FINA 351. We'll be building and comparing 11 portfolios in Excel. The portfolios will have weights ranging from 100% Stock A to 100% stock B and combinations in between. Comment or message me with any questions.
Views: 53891 TimevalueVideos
How to Create a Cash Flow Forecast using Microsoft Excel - Basic Cashflow Forecast
 
14:34
Create a basic cash flow forecast using excel. If you need help get in contact. www.bpfs-online.com Support this channel https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=FHGCUQ8GU9VB6 Take our Online Sage training course http://www.bpfs-online.com/p/online-sage-training-course.html Create a bookkeeping spreadsheet using Microsoft Excel http://youtu.be/LlWADbkGdac Sage Accounts Bookkeeping Tutorial/Training Learn more at www.bpfs-online.com
Views: 544831 BookkeepingMaster
Waterfall Returns Distribution in an LBO Model
 
19:19
What is a "Waterfall Returns" Schedule? CONCEPT: In a leveraged buyout or any deal where an investment firm acquires another company, they'll often own close to 100% of it... By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers" Table of Contents: 1:04: Example of Management Promotes / Waterfall Returns 3:29: Rationale for Management Promotes and Giving Away Ownership 4:25: Step-by-Step Modeling Process for Waterfall Returns 6:35: Excel Setup 7:12: Level 1 IRR Calculations 10:05: Level 2 IRR Calculations 12:38: Level 3 IRR Calculations 13:55: Level 4 IRR Calculations 14:23: How the Waterfall Distribution Affects IRRs to Everyone 17:35: Recap and Summary What is a "Waterfall Returns" Schedule? CONCEPT: In a leveraged buyout or any deal where an investment firm acquires another company, they'll often own close to 100% of it... But sometimes management will retain a small portion, or another investor group might retain a certain portion. Sometimes it ends there - but sometimes, that smaller group gets ADDITIONAL ownership and a higher stake upon exit if the investment performs well. This is called a "management promote" (if it's the management team that receives this as an incentive). EXAMPLE: A new leveraged buyout takes place, and the PE firm structures the deal to heavily incentivize the management team: For an IRR up to 10%, PE firm gets 95% and management team gets 5% of the proceeds. Then, for the portion of the IRR between 10% and 15%, the PE firm gets 90% and the management team gets 10%. For the portion of IRR between 15% and 20%, the PE firm gets 85% and the management team gets 15%. Then for the IRR above 20%, the PE firm gets 80% and the management team gets 20%. A PE firm might do this to create a "win win" scenario - yes, it loses some of its IRR by giving up a % to the management team... but if all goes well, the team should outperform and help the PE firm achieve a higher overall IRR. How Do You Model This Scenario? 1) Make assumptions for the initial investment and proceeds upon exit, plus the ownership percentages. 2) Make assumptions for how the proceeds split changes at different IRR levels. 3) For each "tier" of IRR, take the initial investment and calculate the amount of net proceeds upon exit that would correspond to that IRR. Example: $1,000 initial investment, and 10% IRR tier - multiply by (1 + 10%), then multiply that number by (1 + 10%), and so on until the exit year. 4) Determine the split of proceeds within that tier. If the actual proceeds are $1,500, for example, and $1,611 would correspond to a 10% IRR, you're done - just split the $1,500 between the PE firm and management team in a 95% / 5% split. But if it goes beyond that $1,611, you just split up the $1,611 according to those numbers and then save the rest for the next tier. 5) Determine the proceeds to distribute in the next tiers. For $3,000, for example, you'd distribute $1,611 and save ($3,000 - $1,611) for the next tiers. If you're at the 10% level and you get something below $1,611, you'd set the "proceeds for the next tiers" number to $0 (use a MAX function for this). 6) Keep doing this for each tier of IRRs until the end. The formulas get trickier as you move up because you need to use MIN and MAX to ensure that you don't get negative or nonsensical values. In Level 2, for example, the "Amount to Distribute and Split" is: =MIN(Net Proceeds That Correspond to 15% IRR in Year 5 minus Net Proceeds That Correspond to 10% IRR in Year 5, MAX(Total Net Proceeds minus Net Proceeds That Correspond to 10% IRR in Year 5, 0)) So you're taking the lesser of the proceeds between 10% and 15% IRRs, or the total remaining amount that can be distributed AFTER the Level 1 distributions. And that same type of logic continues as you move down, until the last tier. RESOURCES: http://youtube-breakingintowallstreet-com.s3.amazonaws.com/109-03-Simplified-Waterfall-Distribution-Before.xlsx http://youtube-breakingintowallstreet-com.s3.amazonaws.com/109-03-Simplified-Waterfall-Distribution-After.xlsx
REIT Valuation: Crash Course
 
22:18
In this tutorial, you’ll learn how REITs operate, how to create simple 3-statement projection models for them, how to extend the projections into a DCF analysis, and how to complete a Net Asset Value (NAV) model and use Public Comps to value a REIT. https://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers" Table of Contents: 2:14 Part #1: Basic Characteristics of REITs and U.S. GAAP vs. IFRS 6:17 Part #2: Simple Projection Model for a REIT 12:00 Part #3: Extension of the Projection Model into a DCF for a REIT 16:03 Part #4: Net Asset Value (NAV) for REITs and Public Comps 19:40 Recap and Summary Resources: https://youtube-breakingintowallstreet-com.s3.amazonaws.com/REIT-Valuation-Slides.pdf https://youtube-breakingintowallstreet-com.s3.amazonaws.com/REIT-Valuation-Example.xlsx Lesson Outline: To value REITs simply and effectively, you must understand how they operate, their special requirements, and the differences between U.S. GAAP-based and IFRS-based REITs. REITs buy, sell, develop, and operate properties or other real estate assets. They must distribute a high percentage of Net Income in the form of Dividends (90% in the U.S.), and high percentages of their revenue and assets must come from real estate. In exchange, they pay no corporate income taxes (or greatly reduced corporate taxes). REITs are always maintaining, acquiring, developing, renovating, and selling properties, and since they distribute so much Cash, they constantly need to raise Debt and Equity to operate. The Gains and Losses on property sales make Net Income fluctuate, so you look at alternative metrics, such as Funds from Operations (FFO) or EPRA Earnings, when analyzing REITs. Funds from Operations (FFO) = Net Income + RE Depreciation & Amortization + Losses / (Gains) + Impairments. Under U.S. GAAP, REITs depreciate properties and record a huge Depreciation expense on the IS; under IFRS, they revalue properties constantly and record huge Fair Value Gains and Losses instead. Also as a result of that, Book Value is important and meaningful for IFRS-based REITs but must be adjusted significantly for U.S.-based REITs. To project a REIT’s statements, you start by projecting its “same-store” (existing) properties by assuming rental growth and margins. Then, assume acquisition, development/redevelopment spending, a yield on spending, and margins there, and assume something for dispositions and the lost revenue and operating income. Add up all the property-level revenue and expenses, and then project corporate items such as Depreciation, Maintenance CapEx, and SG&A with traditional percentage approaches. Make Dividends a % of FFO, AFFO, or EPRA Earnings, and assume Debt and Equity issued based on the REIT’s Cash before financing vs. its minimum Cash balance. To value a REIT with a DCF, extend these projections, factor in all CapEx and Asset Sales, as well as Stock Issued, and project revenue, margins, D&A, CapEx, and Asset Sales through a 10-year period. Calculate and discount Terminal Value the normal way, discount and sum up the Free Cash Flows, back into the Implied Equity Value and divide by the share count (current + future shares to be issued) to get the Implied Share Price. The DDM is similar, but you use Cost of Equity instead of WACC, Equity Value-based multiples for the Terminal Value, and you discount and sum up Dividends rather than Unlevered FCF. To calculate NAV for U.S.-based REITs, project the 12-month forward Net Operating Income from properties, divide it by an appropriate Cap Rate or Yield (based on similar transactions or companies in the market), and then take the market value of the other assets and add them up. Then, adjust the Liabilities, and subtract them from the market value of Assets to determine Net Asset Value; divide by the share count to get NAV per Share and compare it to the Current Share Price. Public Comps are similar, but the screening criteria are usually Real Estate Assets, Geography, and Sub-Industry. You can use traditional metrics and multiples like EBITDA and EV / EBITDA, but you’ll also use alternative ones such as FFO, P / FFO, NAV, and P / NAV, and, for IFRS-based REITs, Book Value and P / BV. To find the data, you can use “Related Companies” on Google Finance, get the assumed growth rates for the projections from sources like Yahoo Finance, and go from there.
Merger Model: Cash, Debt, and Stock Mix
 
19:59
In this merger model lesson, you'll learn how a company might decide what mix of cash, debt, and stock it might use to fund... By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers" ... might use to fund a merger or an acquisition - and you'll understand how to determine the appropriate amount of each one in a deal. 2:24 General Order of Funding for M&A Deals 4:49 Cash - How Much Can You Use? 9:56 Debt - How Much Can You Use? 14:08 Stock - How Much Can You Use? 16:32 Exceptions 18:03 Recap and Summary How Do You Determine the Cash / Stock / Debt Mix in an M&A Deal? Very common interview question, and you also need to know it for what you do on the job. 3 ways to fund a company, and to fund acquisitions of other companies: use cash on-hand, borrow the money from other entities (debt), or issue equity (stock) to new investors. But how does a buyer in an M&A deal decide whether it should use… 50% debt and 50% stock vs. 33% debt, 33% stock, and 33% cash vs. 50% cash and 50% debt vs…. And the list goes on. Easiest: Think about the "cost" of each method, start with the cheapest method, use the most of THAT method that you can, and then move to the next cheapest method, and continue like that. GENERALLY: Cheapest: Cash, since interest rates on cash are lower than interest rates on debt, and tend to be low in general. Next Cheapest: Debt, since it is still cheaper than equity and since interest paid on debt is tax-deductible. Most Expensive: Stock, since the Cost of Equity tends to exceed the Cost of Debt… in theory and in practice. To Compare Them: Look at the "After-Tax Yields"… for debt and cash, just take the Interest Rate and multiply by (1 - Buyer's Tax Rate). Stock: Take the buyer's Net Income and divide by its Equity Value (or "flip" its P / E multiple). SO: Always start with cash, use the most you can, then move to debt, use the most you can, and finish up with stock. Cash - How Much is "The Most You Can?" Easy: Company has minimal cash and can't use anything, or it has a huge cash balance and can use all of it. More Common Case: Look at the company's "minimum" cash balance and use the excess cash above that to fund the deal. EX: Company has $500 million in cash right now, but its minimum cash balance to keep operating is $200 million… So it can use $300 million of its cash to fund the deal. How to Determine: Can be tough, but sometimes companies disclose it… ...or you can look back at historical cash balances and make a guesstimate based on that (what was its lowest cash balance in past years?). Debt - How Much Can You Use? So let's say you've now used $300 million of cash to fund the deal… but it's a deal for $1 billion total. How much debt can you use to fund the remainder? $700 million? $300 million? $500 million? Easiest Method: Calculate the key credit stats and ratios for the combined company - for example: Total Debt / EBITDA Net Debt / EBITDA EBITDA / Interest Expense And see what amount of debt makes these look "reasonable", in line with historical figures and also figures for comparable companies. EX: Let's say that if the company uses $500 million of debt, its Debt / EBITDA is 4x. Historically, it has been around 2-3x, and no peer company is levered at more than 3.5x. If that's the case, we'd say that 3.5x - 4.0x is probably the "maximum" (whatever amount of debt that means). Here: We have the Debt / EBITDA and other ratios for the Men's Wearhouse / Jos. A. Bank peer companies. Stock - Now What? Often used as the "method of last resort" because: A) It tends to be the most expensive method for most companies. B) Most acquirers don't like giving up ownership and diluting existing shareholders unless absolutely necessary. So in this example, if we've used $300 million of cash and $500 million of debt, we're still not quite at $1 billion... need an extra $200 million, which we can get by issuing stock. # of Shares = $200 million / Buyer's Share Price. Technically, there's no real "limit," but it would be very odd for a company to give up more than, say, 50% ownership to another company… unless they're very close in size. Exceptions: Buyer has an exceptionally high P / E multiple (Amazon) - stock might be the cheapest! Buyer wants to do a tax-free deal (Google / YouTube) and it's much bigger anyway, so won't make a difference. Companies are similarly sized - stock might always be necessary because cash/debt are implausible (mergers of equals). Summary Which purchase method do you use? MOST relevant when companies are closer in size… doesn't make much difference when the buyer is 100x or 1000x bigger than the seller. Order: 1. Cash - Any excess cash above the company's minimum cash balance. 2. Debt - To the upper range of the Debt / EBITDA of comparables (and other metrics). 3. Stock - For any remaining funding that's required; ideally give up well under 50% ownership.
Implementing the DDM in Excel
 
09:24
This video is part of a series of videos discussing stock valuation. This video focuses on how implement the Dividend Discount Model in an Excel spreadsheet. This video accompanies others on this channel related to theoretical and practical applications of stock valuation. In particular, this video uses the same numerical examples as used in Stock Valuation Theory - Dividend Discount Model (http://www.youtube.com/watch?v=2O4PdCgvsaQ). This video was created primarily as a self-paced learning reference for students who participate in the Saluki Student Investment Fund (SSIF) at Southern Illinois. While others are allowed to view this video, it should be noted that it is provided for educational purposes only. I hope that you find these videos helpful. If you do, I would appreciate hearing from you. I also appreciate constructive criticism and tips on how to improve these videos. The music is "Gnomone a Piacere" by MAT64 (http://www.mat64.org/).
Views: 35626 Jason Greene