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 firstname.lastname@example.org.
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
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.
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|
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.
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.
|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 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.)
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.
|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|
The files below are associated with tricky issues in corporate finance.
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