Corporate Model Basic A-Z On-Line Course

This page describes some of the basic ideas about creating a corporate financial model.  Creating a financial model with three statements (profit and loss, cash flow and balance sheet).  This page is made for people who have done little financial modelling and includes some very simple examples. After I published my exciting novel on corporate and project finance modelling a few years ago, 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 and examples. These videos and the associated excel sheets and the associated power point slides are presented in the next page. 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.

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

 

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

 

 

Very Basic Corporate Model with Where you Can Fill in Equations

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 that is in the file attached to the button below.  I made this file many years ago as a pre-course exercise for people to practice for some basic corporate modelling courses.  In working through this exercise, you can press a button to clear the equations.  Then you can fill in the equations and check your work.  The videos below the button explains how to work through the model and the central idea that the corporate model starts with an existing balance sheet.  The screenshot below the button shows what the exercise looks like when you open the file.  There is documentation for every formula and you can check your work by clicking on the complete button (I made this before discovery of the historic switch).

 

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.  The cash flow plug is the way the balance sheet connects with the profit and loss statement. I think the cash flow plug it is a lot less of an issue now and I hope that you would never force the balance sheet to balance.  The key in my opinion is to set up some kind of net cash balance or debt plus cash balance that should be laid out before the profit and loss statement and the cash flow statement.  The net cash balance is the balance of the cash less the debt.  You set up an account where the closing balance of this net balance account comes from the balance sheet. When you move forward to the forecast, the net cash flow comes from the bottom of the cash flow.  Once you have the forecast net cash, you can separate this into debt and cash using the MAX(net cash,0).  For the cash use the MAX function with a positive sign.  For the debt balance use the MAX function with a negative sign.  The first screenshot shows how the plug works in the basic model.  The second screenshot shows what appears when you open the file.  As with the simple file, you can press a button to complete the file.  This is shown on the second screenshot below.

 

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

The examples above are so simple that they do not even have depreciation expense and capital expenditures.  In modelling, a central issue is the amount of capital expenditures and/or working capital investment that are necessary to support continued EBITDA and cash flow. The continued simple exercises address the fundamental mechanics of how to include depreciation and capital expenditures.  This example that is attached to the button below does not touch the difficult theory and practice of depreciation that arises from retirements and prospective nominal growth that is different from historic growth.  You can find much more advanced discussion of depreciation on the depreciation page.  To set up depreciation you need to set-up and move forward with some kind of net plant balance or gross plant balance and accumulated depreciation account. The first screenshot below illustrates how to model depreciation using a net depreciation rate and net plant balance (this is not part of the file that is attached to the button).  The second screenshot show how to compute depreciation with the gross plant balance and retirements.  The hard part about this is deriving retirements on existing assets which cannot be found anywhere in the financial statements.

 

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

 

 

 

 

Corporate Model Exercise on Establishing Minimum Cash Balance and Working Capital

The above example with the cash flow plug is not very realistic because if the net cash balance falls to a negative number, the MAX(-net cash,0) operates to move the cash balance to zero.  But of course, companies do need some liquidity and need some cash on the balance sheet. Instead, you can define some kind of minimum cash balance in your model. This may defined as something like 2% of revenues or it could be derived from an historic analysis of cash balances and revenues. The file attached to the button below also includes analysis of working capital.  In computing working capital you can start with the balances from calculations like A/R to Sales multiplied by sales (you get the A/R to Sales from the historic balance sheet). The screenshots below illustrate how to incorporate multiple debt issues and minimum cash balances.  The first screenshot illustrates how to put debt issues that have a fixed interest rate and a fixed repayment in the model.  The second screenshot illustrates how you can adjust the MIN function for debt in the model when there is a minimum cash balance.  You can use the function: MIN(-net cash + min cash,0).  For the cash balance, the formula is simpler. The function is simply MAX(net cash, min cash).  This assures that the cash balance will be at least the minimum cash balance.

 

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

An alternative way to compute the cash flow plug and the cash flow connection is to set-up a cash flow waterfall.  The cash flow waterfall does one thing if cash flow is negative and another thing when cash flow is positive.  The first use of positive cash flow is to re-pay the debt.  But you cannot re-pay debt when the cash flow falls below the minimum cash balance.  So, the first thing to do as shown in the screenshot below is to subtract enough cash to meet the minimum cash flow level.  When cash flow after that is left over, if the cash flow is positive and there is debt outstanding, you repay the debt.  After the debt is paid off you then use the positive cash flow to build up cash.  When the cash flow is negative, you use the cash balance to repay the debt.  When all of the cash except the minimum cash is used up, then you can go and add to the debt.  This is all done with the MIN and MAX functions.  The MAX function is used to test whether the cash flow is positive or negative.  The MIN function is used to text the opening balances.  For example, when the cash flow is positive and you are computing the debt repayment, the formula would be MIN(opening debt balance, MAX(cash flow, 0)).  When you are testing the negative cash flow to see if there is any cash available, the formula is MIN(opening cash balance, MAX(-cash flow,0)).

 

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

 

 

 

 

.

Incorporating History in Corporate Models and Importance of the Historic Switch

When developing just about any financial model, the idea of developing a time line is important.  For a corporate model, it is important to be able to add new history to your model without re-creating your model.  The last thing you want to do is to start your model all over.  When making a time line, you can enter the last period of the historic financial statements somewhere.  Then you can create a TRUE/FALSE switch that compares the time period with the last period.  When the time period is less  than or equal to the final financial statement data, something that I call the historic switch is true.  For the subsequent periods, the historic switch is true.  You can use this historic switch for a whole bunch of stuff including development of assumptions, adjusting closing balances, and testing out the model.

 

Excel File that Illustrates How to Use the Historic Switch in a Corporate Model for Assumptions and Cork Screws

 

 

 

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

 

Excel File that Illustrates Use of Solver in Computing Target Capital Sturture in Corporate Model

Excel File that Illustrates Stable Ratios for Depreciation and Deferred Tax that Depend on Growth and Depreciation Rate

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.

 

Model Structure Exercise:

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

 

 

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				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.