FNCE 3415 Valuation and Financial Modeling

Videos

I will post videos to this page throughout the semester. The videos will mostly be solutions to homework or in-class Excel exercises; the videos are not a replacement for my lectures. Videos should not be used in lieu of coming to class. Excel can be difficult to learn therefore I make these videos in case you get lost in class and need to revisit a topic. Please use them as an additional resource to help learn valuation and modeling. Disclaimer: Values may be different depending on when video was made and when you are watching.

Basic Excel Videos

Formatting Functions Homework 1 Practice Exercises Practice Problems 1

Financial Statement Videos

Homework 2 Lemonade Stand Exercise Practice Problem 2

Financial Statement Analysis Videos

Calculate and Interpret (EW) Calculate and Interpret (SFIX)

Financial Statement Modeling Videos

Gentex Unlevered Free Cash Flow Stitch Fix Unlevered Free Cash Flow FCFF Alternative Template Pro-forma Financial Statements Pro-forma Extended Practice Problem

DCF Valuation Videos

Gentex Terminal Value and WACC Alaska Airlines Terminal Value and WACC Stryker Beta Estimation Homework 6 Stitch Fix Terminal Value and WACC
Homework 7 Gentex: Dealing with Equity Compensation Gentex: Sensitivity Analysis Stitch Fix: Dealing with Equity Compensation Stitch Fix: Sensitivity Analysis

Relative Valuation Videos

LTM Example Relative Valuation: Gentex Relative Valuation: Stitch Fix


Football Field Chart

Football Field Chart: Gentex Football Field Chart: Stitch Fix

Advanced Excel Videos

Advanced Excel Additional Practice

Video #1: Basic Excel Formatting

This video goes over basic formatting and introduces students to shortcut keys in Excel. Note this video is a summary of the material covered in WSP Excel Crash Course.

Watch on Vimeo Back to Video List

Video #2: Functions

This video goes over the Class Worksheet from Basic Excel. It covers functions related to time value of money, statistical function, logical functions, lookup functions, and custom formatting.

Template Solution Watch on Vimeo Back to Video List

Video #3: Homework 1

This video goes over Homework 1.

Template Solution Watch on Vimeo Back to Video List

Video #4: Practice Exercises

This video goes over the Excel practice exercises from Basic Excel Modeling.

Template Watch on Vimeo Back to Video List

Video #5: Practice Problems 1

This video goes over practice problems.

Template Watch on Vimeo Back to Video List

Video #6: Homework 2

This video works through Homework 2, building an income statement and balance sheet for a fictional Lemonade stand company.

Handout Solution Watch on Screencast Watch on Vimeo Back to Video List

Video 7: Lemonade Stand Exercise

This video works through the in-class exercise on our fictional lemonade stand. It is an extension of Homework 2; we add more complex transactions and build out a cash flow statement. The solution file is the same from above.

Handout Solution Watch on Screencast Watch on Vimeo Back to Video List

Video 8: Practice Problem 2

This video works through practice problem 2 in which we build financial statements for a fictional donut shop.

Handout Template Solution Watch on Screencast Back to Video List

Video #9: FSA: Calculate and Interpret: Edwards Lifesciences (EW)

This video goes over financial statement analysis for a real world company. It covers calculating a common-size balance sheet and income statement for Edwards Lifesciences (EW). We also cover a range of ratio calculations. Lastly we briefly discuss interpreting the numbers.

Template Solution Watch on Vimeo Back to Video List

Video #10: FSA: Calculate and Interpret: Stitch Fix (SFIX)

This video goes over financial statement analysis for a real world company. It covers calculating a common-size balance sheet and income statement for Stitch Fix (SFIX). We also cover a range of ratio calculations. Lastly we briefly discuss interpreting the numbers.

Template Solution Watch on Screencast Watch on Vimeo Back to Video List

Video #11: Gentex Unlevered Free Cash Flow

This video goes over calculating the projected unlevered free cash flows for Gentex Corp. Topics include pulling in historical data dynamically, estimating sales growth and other important inputs using the 10K, analyst reports, and earnings call transcript, calculating NWC, and proper modeling formatting.

Template Material Solution Watch on Screencast Watch on Vimeo Back to Video List

Video #12: Stitch Fix Unlevered Free Cash Flow

This video completes a financial statement model for Stitch Fix (SFIX). Topics include pulling in historical data dynamically, estimating sales growth and other important inputs using the 10K, analyst reports, and earnings call transcript, calculating NWC, and proper modeling formatting.

Material Watch on Screencast Back to Video List

Video #13: FCFF Alternative Template

This video is additional practice calculating unlevered cash flows for Alaska Air. The video focuses mainly on the calculation and alternative approaches to those used in the Gentex example.

Template Solution Watch on Vimeo Back to Video List

Video #14: Pro-forma Financial Statements

This video goes over creating pro forma financial statements using cash as the plug and calculating unlevered cash flows.

Template Solution Watch on Vimeo Back to Video List

Video #15: Pro-forma Extended

This video extends the pro-forma example to require external financing. Specifically, how to use both debt and cash as the plugs, i.e., use debt financing if cash is no longer available.

Template Solution Watch on Vimeo Back to Video List

Video #16: Practice Problems 4

This videos covers the practice problems for the Financial Statement Modeling module. Topics include forecasting and calculation of unlevered cash flows.

Practice Problems Watch on Screencast Back to Video List

Video #17: Gentex TV and WACC

This video works through the Gentex template. We discuss how to calculate the Terminal value using perpetuity growth and the exit multiple approaches. We also work through the calculation of the weighted average of capital. Lastly we finish by calculating the enterprise value and the associated equity value of Gentex. Note: Do not use average debt for WACC calculation of capital structure weights; only done in video to show a non-zero debt value. Please note, the numbers in solution will be different at time of viewing. Also this is forecasting 2019 forward, see class session videos for updated values.

Template Solution Watch on Screencast Watch on Vimeo Back to Video List

Video #18: Alaska TV and WACC

This video works through the Alaska Airlines template. We discuss how to calculate the Terminal value using perpetuity growth and the exit multiple approaches. We also work through the calculation of the weighted average of capital. Lastly we finish by calculating the enterprise value and the associated equity value of Alaska Airlines.

Template Solution Watch on Screencast Watch on Vimeo Back to Video List

Video #19: Stryker Beta Estimation

This video works through the estimation of beta using both the regression and bottom up approach.

Template Solution Watch on Screencast Watch on Vimeo Back to Video List

Video #20: Homework 6: Calculations

This video works through Homework 6.

Homework 6 Solution Watch on Screencast Watch on Vimeo Back to Video List

Video #21: Stitch Fix TV and WACC

This video works the estimation of the terminal value and weighted average cost of capital for Stitch Fix (SFIX). We also calculate the equity value. Please note, the numbers in solution will be different at time of viewing.

Template Solution Watch on Screencast Back to Video List

Video #22: Homework 7

This video works through Homework 7, a bottom-up approach to forecasting and a DCF valuation.

Handout Template Solution Watch on Screencast Watch on Vimeo Back to Video List
-->

Video #23: Gentex: Dealing with Equity Compensation

This video works through the a few different approaches to accounting for equity grants to employees in our valuations. Specifically how to deal with past option awards through either a treasury stock approach or directly valuing the options using Black Scholes.

Template Solution Watch on Vimeo Back to Video List

Video #24: Gentex: Sensitivity Analysis

This video works through a sensitivity analysis of our Gentex Valuation. Discussion includes the use of Data Tables and Scenario Manager in Excel. I also recap our valuation from start to finish. Note please use the Solution file from Video #23 as your starting point.

Solution Watch on Vimeo Back to Video List

Video #25: Stitch Fix: Dealing with Equity Compensation

This video works through the a few different approaches to accounting for equity grants to employees in our valuations (both options and restricted stock). Specifically how to deal with past option awards through either a treasury stock approach or directly valuing the options using Black Scholes.

Template Solution Watch on Screencast Back to Video List

Video #26: SFIX: Sensitivity Analysis

This video works through a sensitivity analysis of our Stitch Fix (SFIX) Valuation. Discussion includes the use of Data Tables and Scenario Manager in Excel. I also recap our valuation from start to finish. Note please use the Solution file from Video #25 as your starting point.

Solution Watch on Screencast Back to Video List

Video #27: LTM Example

This video walks through a Last twelve month calculation for Microsoft, Best Buy, and Walt Disney as of November 2019.

Template Solution Watch on Vimeo Back to Video List

Video #28: Relative Valuation: Gentex

This video walks through a relative valuation using public trading comparables. Please note there is an (obvious) error around 31:30 mark, I copied over the value in Cell F24 (with F25), you should do the opposite and drag the F24 formula down to F25. The error is corrected in the attached solution. Also did not add 28 (Operating Lease, current) when calculating Net Debt for VC.

Template Solution Watch on Screencast Watch on Vimeo Back to Video List

Video #29: Relative Valuation: Stitch Fix

This video walks through a relative valuation using public trading comparables. We discuss selecting comps, pulling in the required data, calculating and applying multiples, and lastly estimating excess cash.

Template Solution SFIX Industry Peers Watch on Screencast Back to Video List

Video #30: Football Field Chart: Gentex

This video goes over the steps to building a football field chart for our Gentex valuation.

Template Solution Watch on Screencast Watch on Vimeo Back to Video List

Video #31: Football Field Chart: Stitch Fix

This video goes over the steps to building a football field chart for our Stitch Fix valuation.

Template Solution Watch on Screencast Back to Video List

Video #32: Advanced Excel

This video covers miscellaneous Excel skills including Pivot Tables, conditional formatting, basic graphing skills, concatenating, INDIRECT function, the group of TEXT based functions, and goal seek.

Template Solution Watch on Screencast Watch on Vimeo Back to Video List

Video #33: Additional Practice

This video goes through additional practice exercises.

Template Solution Watch on Screencast Watch on Vimeo Back to Video List