This corporate model webpage includes excel files and videos that show you how to build various aspects of a corporate model and a DCF valuation on a step by step basis. By working through the different corporate model lessons you should quickly become comfortable with basic and more advanced concepts in corporate models including incorporation of historic figures with the ability to update, simulation of target capital structures and alternative financing, working through some tricky financing and tax issues including NOLs and efficiently reading historic data into a model from either the internet or from PDF files.

This page is something like the page I created in my old wikispaces page. It is probably better to go to the individual sections that are somewhat more organised.  If you are beginning with corporate finance it may be good to first review some theory.  I have put some of the corporate finance theory in the slides below.

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

Some of the exercises and videos listed on the table below deal with fundamental structural issues associated with corporate models that include projection of financial statements. Other lesson sets address more complex corporate modelling issues associated with depreciation expense, stable capital expenditures, new equity offerings, circular references, normalised cash flow, valuation issues and model presentation for credit analysis. These other corporate modelling issues are also addressed in other pages of this website. If you are beginning in corporate modelling, I suggest starting with the first lesson set.

As with the other pages, you can get credit for completing the lesson set and demonstrating to your boss or your new employer that you are a highly skilled financial analyst who understands both the equations for the model and all of the excel tools for making the model efficient. As I say a lot elsewhere, I am convinced that courses with personal contract are far better than on-line videos as a way to learn corporate finance ideas and modelling.

The corporate finance model exercises are arranged to correspond to the structure of a model where financial statements are entered and reviewed first, followed by analysis of operating assumptions that include capital expenditures, revenues and operating expenditures. The files and videos for corporate finance modelling are arranged by different subjects and different levels of complexity. Separate sets of videos and analyses are presented for:

(1) Building a corporate finance model A-Z;
(2) Reading multiple historic years to a corporate model with the Union Function and Read PDF file;
(3) Dealing with circularity and other issues that can arise from target capital structure and interest expense in a corporate finance model;
(4) Using a corporate model in valuation;
(5) Using a corporate model in credit analysis;

For each of the above subjects, video links and files that you can use in working through the issues are listed on this page.

The on-line lessons are free which may be a really bad idea for me. I have been told that if I somehow charged money for the courses you would think they are more valuable. This is complete rubbish and I expect that you can get a lot more out of these exercises than other companies that spend a lot of money on marketing and being flashy. But, if you have completed exercises and you want me to verify that you really are a good modeller I will do this for a modest fee. If you want to be registered as a modelling expert that will be published in the website and verified for your employment, send me an e-mail to edwardbodmer@gmail.com.

Building a Standard Corporate Finance Model

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, unprofessionalism 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).

Lesson Set 2: Leaning from Corporate Finance Models that are not Flexible, Not Accurate, Not Structured and Not Transparent

When using case studies to study risks and valuation, I think you can learn a lot from cases that were originally thought to be good business strategy or well structured and became dismal failures. Examples are First Solar and Constellation. Similarly, a different way to learn corporate finance model techniques is to see what not to do.
This may be somewhat less boring than working through each of the detailed exercises. Looking at a badly structured model and fixing the model is demonstrated in the videos and the files below.

 

 

 

Bad Model Example.xlsm

Bad Models.xlsx

Corporate Model Example.xlsm

 

Lesson Set 1: Building a Corporate Finance Model

If you are starting out in modelling and have not yet made a project finance 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.

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 https://www.youtube.com/watch?v=-THY2VW8nSw Chapter 4
Building a Basic Corporate Model Exercise 1: Building Basic Corporate Model https://www.youtube.com/watch?v=03_bH8gdbnc Chapter 4
Cash Flow Plug Exercise including Simple Minimum Cash Balance Exercise 4: Cash Flow Plug https://www.youtube.com/watch?v=0Zpe5n0ap3s Chapter 10
Part 1 of Model Exericse with History, Depreciation Analysis and Balance Sheet Exercise 1a – Corporate Model – Depreciation https://www.youtube.com/watch?v=XmkTCI8TkGw Chapter 11
Part 2 of Model with Financing and Minimum Cash Balance Exercise 1b – Financing Min Cash https://www.youtube.com/watch?v=h5kB92TGqxU Chapter 7
Working Capital and Fixed Debt Exercise Overview Exercise 2: WC Corporate Exercise https://www.youtube.com/watch?v=lZqYPxu62y4 Chapter 9
Working Capital and Fixed Debt Exercise Exercise Exercise 2: WC Corporate Exercise https://www.youtube.com/watch?v=RMO_comm-JM 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

 

Resolving Circularity in Models and Establishing Capital Structures

Many corporate models make the unrealistic assumption that capital structure changes depending on the performance of a company. For example, if a company has high returns the cash builds up. This negates the return on equity, equity cash flow and earnings per share forecasts. Instead you can compute the cash flows with equity issues to maintain a given capital structure.

Video Explanations for Lesson 3 – Financing with Target Capital Structure and Cash Sweep

 

Subject Excel Exercise File Video Chapter Reference
Equity Issues and Target Capital Structure – Book Basis with Solver Exercise 10: Target Capital Structure – Book https://www.youtube.com/watch?v=yb6JOPj7cwE Chapter 11
Equity Issues and Target Capital Structure – Market Basis Exercise 11: Target Capital Structure – Market Chapter 11
Monthly Corporate Model with Annual Summation Exercise 12: Cash Sweep for Min Cash https://www.youtube.com/watch?v=whofBIlnVXw Chapter 8
Corporate Model with Forward Looking Sweep and Dividend Exercise 12: Cash Sweep for Min Cash https://www.youtube.com/watch?v=whofBIlnVXw Chhapter 37
Corporate Model with Function to Look Forward Exercise 12: Cash Sweep for Min Cash https://www.youtube.com/watch?v=6Hy2pg6dOts Chhapter 37
Setting-up Up Corporate Model with Depreciation Corporate Model with Target Capital Structure
Circular Reference and Target Capital Structure in Corporate Model Target Capital Structure Exercise https://www.youtube.com/watch?v=vtYnkvyoeaw
Part 3 of Model with Resolution of Circular Reference for Interest Exercise 1c – Corporate Model – Circular Reference https://www.youtube.com/watch?v=2WIf30VUCSE Chapter 12
Part 4 of Model with Taxes, NOL and Target Capital Structure Exercise 1d – Corporate Model – Target Cap Str https://www.youtube.com/watch?v=vtYnkvyoeaw Chapter 12

As with other video sets, if you review this set and you can resolve a circularity in a corporate model with a user defined function, you should receive credit in one way or another. If are doing these things independently you can send in the fee and then I will send you an evaluation exercise. If you organisation has subscribed to my service and your boss will have the evaluation file. If you complete the evaluation file and send it to me, I will verify that you really understand some of these complex things and give you credit that proves you are really smart.

Exercise Files Associated Target Capital Structure and Circularity

Exercise that demonstrates how to use the solver and algebra to find dividends or equity issues in a financial model. Exercise that works through how to add equity issues and the number of shares to a model and make a sensitivity analysis of earnings per share. Exercise that uses corporate model and works through somewhat complex issues associated with free cash flow including splitting free cash flow into different items, evaluating the value of the interest tax shield, evaluating stock options and minority interest, reconciling problems with WACC. (Issues associates with computing cash flow are discussed in Appendix 1 to Chapter 2.)

 

Exercise 1c – Corporate Model Circular Reference.xlsm

 

Exercise 1d – Corporate Model – Target Cap Str.xlsm

 

Exercise 6 – Supply and Demand Simulation.xlsx

Exercise 7 – Corporate Exercise with Minimum.xlsm

 

Exercise 8 – Target Capital Structure with Solver.xls

 

Exercise 10 – Target Capital Structure with Function.xlsm

Exercise 12 – Cash Sweep for Min Cash a.xlsm

 

Exercise 13 – Forward Looking Revolving Credit and Dividends.xlsx

Lesson Set 4: Complex NOL Tax Issues and Timing Issues in Corporate Models

Some of the tricky issues in making a corporate model is computing the net operating loss for tax purposes with a time limit on the time period of the losses. Another problem is presenting a corporate monthly basis and showing the twelve months next to an annual amount. Other important and tricky issues involve how to simulate risk issues associated with surplus capacity in an industry.

Video Explanations for Lesson 3 – Financing with Target Capital Structure and Cash Sweep

 

Subject Excel Exercise File Video Chapter Reference
Monthlly Corporate Model Structure Exercise 13: Monthly Model and Revolver https://www.youtube.com/watch?v=D5-PjSolLGY Chapter 8
Revolving Credit with Commitment from Forward Cash Flow Exercise 13: Monthly Model and Revolver https://www.youtube.com/watch?v=VD9Cig_H2IA Chapter 46
Creating Flexible Assumptions with Historic Switch Meralco Model https://www.youtube.com/watch?v=zFZzBEmL0vU Chapter 6
Taxes and Net Operating Loss (NOL) in Simple Case Exercise 8: NOL Exercise in Corporate Model Chapter 12
Taxes and Net Operating Loss (NOL) in Complex Case with Expiration Exercise 9: NOL Exercise in Corporate Model Chapter 12
Model with Industry Capacity – Overview Exercise 5: Model with Industry Capacity https://www.youtube.com/watch?v=Fxq1OjBZxg0 Chapter 6
Model with Industry Capacity – Basic Case of Incorporating Surplus Capacity Exercise 5: Model with Industry Capacity https://www.youtube.com/watch?v=s3eeNAmxLUU Chapter 6
Model with Industry Capacity – Comprehensive Case Exercise 5: Model with Industry Capacity https://www.youtube.com/watch?v=yLHGRWPxmdQ Chapter 6
Model with Working Analysis – Connecting Capital Expenditures to Demand Exercise 6: Model with Working Analysis https://www.youtube.com/watch?v=XvxTEGpVwPY Chapter 6

 

Exercise Files Associated with Monthly Revolver in Corporate Modelling:

The files below are associated with tricky issues in corporate finance.

General Comments About Corporate Models

The tricky part about corporate models involves how to incorporate moving from historic to projected periods in a smooth way and how to model terminal value.
The model below illustrates how to:
1. use macros to read from PDF files into excel2. put history and forecasts together in an effective way that allows you to add history in the future
3. compute stable ratios of working capital, depreciation, capital expenditures and deferred tax that depend on the terminal growth
4. create flexible valuation periods