Corporate Model A-Z On-Line Course

After I published my exciting novel on corporate and project finance modelling, I made a lot of videos to go along with the videos. I did not do this in a professional manner — too much dog barking in the videos and too much swearing. I think the videos did were often difficult to follow, they did not have any corporate finance theory as to why you are following various procedures and they did not have examples of what not to do. After various comments about background music, quality, lack of professionalism etc. I have began to make a set of revised videos. These videos and the associated excel sheets and the associated power point slides will be presented below. I have included advanced issues as well as basic issues in these videos.

If you are starting out in modelling and have not yet made a model, the files and videos below are intended to allow you to see the model structure, the importance of a historic timing switch, the essential nature of separating operating cash flows from financing cash flows and other issues including different financing issues. The set of videos and files also introduce you to some difficult financing issues including circularity associated with interest expense and holding a constant capital structure. Files associated with the videos are shown first followed by video links that walk through the various lessons.

If you fill in all of the exercises and send me the files along with a small fee, I will publish your name on my website so you can show it to your boss or your future employer. I will also get you an official badge. An illustration of how the models work is shown below. The yellow tabs in the excerpt show the items to fill in. The completed sheets are next to the yellow tabs (I hope I did not make mistakes).

Power Point Slides that Describe the Corporate Finance Theory and Modelling Including Multiples and Cost of Capital

 

Building a Standard Corporate Finance Model

If you are starting out in modelling and have not yet made a financial model, the files and videos below are intended to allow you to see the general structure and design of a corporate model. The essential element of corporate modelling is presentation of history alongside the forecast, smooth transition from history to forecast and development of alternative structures for the assumptions. Difficult issues in corporate modelling involve depreciation, setting target capital structures, circularity from interest expense and interest income. The exercises begin with a simple case that does not have any history and then demonstrate how to add history to the model.

 

Very Basic Corporate Model with Macros to Fill in Exercise

If you have never built any kind of model before and are getting started with a basic balance sheet, income statement and cash flow analysis, you can try the exercise in the file below.  I made this file many years ago for some basic corporate modelling courses.  You can press a button to clear the equations and then fill in the equations.

 

Corporate Model Exercise with Macros that Fill In Data Using Simple Analysis and Balance Sheet to Start

 

 

Corporate Modelling Exercise for Complete Model without History and Only Starting Balance Sheet

 

Corporate Model Exercise with Cash Flow Plug

A very very long time ago when I first saw financial models, the issue of making sure the three financial statements reconciled with a cash flow plug was a big issue.  I hope it is a lot less of an issue now.  The key in my opinion is to set up some kind of net cash balance or debt plus cash balance before the cash flow statement.  In the exercise below, a minimum cash balance is established as well as a cash balance and you have to be a little careful with the minimum function.

 

 

Corporate Model Exercise: Using Cash and Debt to Reconcile Cash Flow with Balance Sheet (Plug)

 

 

Corporate Model Exercise on Computing Depreciation Expense and Completing Income Statement and Balance Sheet

 

Excel File with Corporate Model: File for Exercies on Depreciation and Completing the Balance Sheet

 

 

 

 

 

 

Objectives and Introduction to Corporate Models

The McKinsey Book uses examples of transport, food and retail companies like Fed Ex, UPS, Heiniken, Disney, Walgreens and General Mills.  These companies could be described as old fashioned companies that need to make investments to generate returns.  In addition these companies have a long and stable history along with growth related to the overall economy.  I have included a number of examples of relatively stable corporate analyses.  An example of this is Carlsberg that you can download below.

 

Corporate Model of Carlsberg with Analysis of Terminal Value Using Alternative Models and Stable Relationships

 

Building a Basic Corporate Model

 

Corporate Model Exercise on Establishing Minimum Cash Balance and Working Capital

 

Corporate Model Exercises: Exercise on Modelling Working Capital and Minimum Cash in Corporate Model

 

.

 

 

 

Corporate Model with Cash Flow Waterfall to Evaluate Surplus and Deficit Cash Flow

 

Corporate Model Exercise with Cash Flow Waterfall to Evaluate Minimum Cash, Surplus Cash and Debt

 

.

Corporate Model on Resolving Circular References with Average Debt Balance

Most of my discussion on circular references involves project finance where circular references can really mess things up.  In a corporate model circular references can arise because of computing interest expense and/or interest income on the average debt balance.

 

Corporate Finance Model Exercise: Exercise on Resolving Circular References from Interest on Average Balance

 

Corporate Model and Establishing Target Capital Structure

 

Corporate Model Exercies: Exercise on Establishing Target Capital Structure in Model with Solver

 

 

Corporate Model Exercise: Incorporating Historic Financial Statements in Corporate Model

 

Model with Capacity Analysis

 

Corporate Model Exercise: Model that is Capacity Driven with Retirements and Capacity Additions

 

Classic Company

  • Mature Company with capital expenditures used to make investment.
  • No large write-offs, re-structuring’s or asset sales.
  • Multiples are similar across companies in the industry and over time.
  • Value to Investment (price to book) ratios are stable and can be used to evaluate company management and cost of capital.
  • Return earned on assets invested in the past is similar to return on investment on new assets.

Tricky Situations

  • Investment in the form of research, operating losses and software.
  • Balance sheet affected by gains on asset sales, write-offs, re-structuring charges.
  • Multiples change a lot over time for individual companies and across a cross-section of companies.
  • Value to investment have little meaning as historic investment was very small compared to new investment.
  • Return on historic investments change as plants age and conditions change in an industry.

 

Corporate Model Structure: Exercise with Simple Model

 

Model Structure Exercise:

  • Historical Financials
  • Set-up of Assumptions
  • Connection of Financial Statements
  • Free Cash Flow and Equity Cash Flow
  • ROE and ROIC

 

Corporate Model Structure:

 

 

 

The remainder of the Corporate Model is Arranged as Follows:

  • General Ideas and Objectives of Corporate Modelling
  • Excel Functions and Techniques for Modelling including Interpolate Function
  • Structure of Corporate Models and Simple Exercise
  • Acquiring Data from Internet and From PDF files and Presentation of History and ROIC Analysis
  • Evaluating Fundamental Risks of Operating Cash Flow
  • Problems with Depreciation in Corporate Models
  • Circular Reference Issues in Corporate Models

The corporate modelling that is explained in this section applies a few novel concepts. Some of these ideas in modelling described below include:

  • Use of Historic Switch to Make Incorporation of New Financial Statements
  • Evaluation of ROIC and Invested Capital Using Switches and SUMPRODUCT
  • Development of INTERPOLATE Function to Evaluate Assumptions
  • Automation of Scenario Analysis with Scenario Reporter
  • Effective Automation of Historic Data Graphs with Flexible Spinner Box
  • Resolution of Circular References Related to Interest Expense and Taxes
  • Deprecation Techniques that Account for Changing Growth and Implied Retirements
  • Development of Techniques to Automate Constant Capital Structure in Financial Models
  • Dynamic Goal Seek Functions for Evaluation of Cost of Capital Using P/E Ratios
  • User Defined Functions for Computing Stable Capital Expenditures to Depreciation and Other Items

The modelling innovations are supported by a series of different methods to acquire data.  Some of the novel data techniques include:

  • Creation of techniques to download stock price data, financial statement data and economic data
  • Stock price database that allows you to evaluate IRR’s, volatility and beta for stocks, stock price indices, economic series and commodity prices.
  • Financial Database that allows you to extract and evaluate financial data, financial ratios, and cost of capital across companies.
  • Extraction of Data that Enables you to have Historic Basis for Creating Financial Models.
  • Interest Rate, Exchange Rate and Commodity Price Databases that Include Historic Evaluation of Term Structures, Volatility and Other Statistics.
  • Comprehensive Country by Country Database to Evaluate Growth and Risks Across the World.

 

 

 

Video Explanations for Lesson 1 – Building Fundamental Corporate Finance

The videos below walk you through building a basic corporate model and are associated with my text book. If you are starting with corporate models I suggest that you begin with this lesson set. When you are trying to work through the videos I suggest you open the file and try to fill in the blank part of the files that have the exercises. A key behind structuring a corporate model is incorporating history and the ability to model alternative cash flow reconciliations. Alternative methods for this are shown in the various exercises. One of the basic issues in corporate modelling can be establishing a minimum cash balance.

 

Subject Excel Exercise File Video Chapter Reference
Overview of Corporate Model Issues Exercise 1: Building Basic Corporate Model
Exercise 1: Building Basic Corporate Model Chapter 4
Exercise 4: Cash Flow Plug Chapter 10
Part 1 of Model Exercise with History, Depreciation Analysis and Balance Sheet Exercise 1a – Corporate Model – Depreciation Chapter 11
Part 2 of Model with Financing and Minimum Cash Balance Exercise 1b – Financing Min Cash Chapter 7
Working Capital and Fixed Debt Exercise Overview Exercise 2: WC Corporate Exercise Chapter 9
Working Capital and Fixed Debt Exercise Exercise Exercise 2: WC Corporate Exercise Chapter 9
Coprorate Model with History – Overview Exercise 3: Corporate Model with History https://www.youtube.com/watch?v=jl2I8DK-UnI Chapter 7
Model with History – Developing Assumptions using Historic Switch Exercise 3: Corporate Model with History https://www.youtube.com/watch?v=xF0pZygR0j4 Chapter 6
Model with History – Working Analysis of Revenues, Expenses and Capital Expenditures Exercise 3: Corporate Model with History https://www.youtube.com/watch?v=WhjgwFncTKQ Chapter 8
Model with History – Graphing History Exercise 3: Corporate Model with History https://www.youtube.com/watch?v=WhjgwFncTKQ Chapter 4
Minimum Cash Balance – Using Cash Flow Waterfall Concepts to Model Min Cash Exercise 7: Minimum Cash Balance https://www.youtube.com/watch?v=OHpw3HOLbss Chapter 10

 

Corporate Model Exercise Files Associated with the Lesson 1:

One of the products that I am offering — again for a great bargain — is a service to your company. If you have listened to and understood most of what is in the videos, you really and truly are one of the young people who will progress and eventually be a success in one way or another. Your boss should give you some kind of credit for making an effort to listen to the videos and mess around with some of the files. To test that you have really listened to the videos and understood the main points, I am including some evaluation files for each lesson set. If your organisation has subscribed to my service and will give you credit for your work, you have to demonstrate that you have seen the videos. After filling out the evaluation, I will put your name on the website and send an e-mail to you boss verifying that you have indeed understood the point and made this impressive effort to improve yourself.

The files below include blank exercises as well as the completed equations. Unlike some of the newer lessons, I have included separate files for various elements of making a model including use of historic switches, cash flow analysis, incorporating minimum cash flow constraints in the model, and using risk analysis. Exercise that works through how to begin with demand and then work through variable costs and fixed costs in creating a financial model from different pages. Most files include a blank page that you are supposed to fill out that is coloured in yellow. If you want credit for your work, you should fill out the things in yellow.