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).
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 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 on Computing Depreciation Expense and Completing Income Statement and 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.
Building a Basic Corporate Model
Corporate Model Exercise on Establishing Minimum Cash Balance and Working Capital
Corporate Model with Cash Flow Waterfall to Evaluate Surplus and Deficit Cash Flow
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 Model and Establishing Target Capital Structure
Model with Capacity Analysis
- 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.
- 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
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.
Exercise 1 – Building a Basic Corporate Model.xlsm
Exercise 1b – Corporate Model – Financing and Minimum Cash.xlsm
Exercise 2 – Simple Corporate Model.xls
Exercise 2 – Corporate Model -WC, Min Cash.xlsm
Exercise 3 – Corporate Model with History.xlsx
Exercise 4 – Cash Flow Plug.xlsm
Exercise 5 – Model Exercise with Capacity.xlsm
Exercise 6 – Model with Working Analysis.xlsm
Exercise 7 – Corporate Exercise with Minimum.xlsm
Exercise 3 – Basic Model with WC.xls
Exercise 5 – One Way Data Tables from Macros.xlsm
Exercise 1b – Corporate Model – Financing and Minimum Cash.xlsm