Corporate+Finance+Model+Exercises

=Home|| Book || Featured PF Models || Featured Corp Models || Featured Acq Models || PF Exercises || Corp Exercises || Download to Excel || Scenario Analysis || Tornado Diagrams || Monte Carlo || Flexible Graphs || Real Estate || Acq Exercises || Macros || Reading PDF || Waterfall Graphs || Circular Ref || Depreciation || Financial Ratio || Value Driver || Renewable || Testimony || Pricing Case Studies ||=

= This website is now obsolete because of being extinguished by horrible wikispaces. Please go to http://edbodmer.com for a much improved website. =

This corporate model page 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. 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.

= Corporate Finance Model Exercises and Video Courses =

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.


 * Revised Lesson Set 1: 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.

media type="youtube" key="CLnsjatsnLc" width="462" height="275" align="left" media type="youtube" key="J1SxL8a9Vvk" width="474" height="280"media type="youtube" key="Dk-N_I5BVeA" width="486" height="285"



Lesson Set 2: Practical Problems in Acquiring Historic Data from PDF Files and SEC Reports
One of the real world challenges in creating a financial model is to acquire historic accounting data in an efficient and flexible manner. I am convinced that it is is very instructive to put together as many historic years as possible to evaluate trends, volatility of returns and develop downside cases. There are a few ways to get historic data these days. One way is to go the an internet site like marketwatch.com and put together the data. If your company must register with the U.S. securities and exchange commission, you can get data back through 1994.

This lesson set walks you through acquiring data from PDF files and putting together many years of data in a consistent format. In addition to acquiring data from PDF files, the lesson set demonstrates how to acquire data from companies that make filings to the Securities and Exchange Commission in the U.S. You can receive credit for this lesson set simply by sending in a completed set of historical financial statements along with the original source of the data. If you do this and send me the files I will put your name on the page of the website that lists the star financial analysts who have completed a lesson. Here are the steps for getting a nice set of financial statements put together:

1. Find the financial statements and get them with Crome (it works better than Firefox) 2. Copy the data into excel and arrange statements together (e.g. all income statements in first set of sheets, followed by balance sheet etc.) 3. Use the Read PDF excel file to format the financial statements. 4. Put all of the statements together -- put all income statements in one file; balance sheets in second file etc. 5. Get the UNION function and the INDMAT function into your file from the Read PDF file. 6. Arrange sections of each statement with a few lines and have a lot of blanks. eg. operating section of income statement, current assets of balance sheet. 7. Use the UNION function to put together a common set of titles. 8. Use the INDMAT function to put together data for different years with common titles. 9. Copy and paste as values in new file for analysis.

= Videos Associated with Lesson Set 2: Reading Financial Statements Course: =

Videos with this lesson set are boring and have some sound quality problems and I admit this, sorry. I have left them on the website anyway. Perhaps the idea of reading in financial statements from PDF files and from the SEC is not really interesting. However, efficient use of: (1) the READ PDF file to get data from the PDF's on the internet and get the data as quickly as possible into excel is a very practical issue; (2) putting financial statements together from different years when the titles change is a very real world pain and is solved with the important UNION function; (3) quickly arranging the data itself after putting together the titles with the UNION function is very helpful and is accomplished with the INDMAT function. These three things are all made possible with functions and macros that are included in the READ PDF file.

Even with all of these tools, putting together financial statements is a pain. But it is a very real problem and anything to help this problem is I think useful. If you go through the videos and then try an example I think you can really improve your efficiency.


 * Downloading Data from Alternative Sources ||  || ReadPdf.xlsm ||   || https://www.youtube.com/watch?v=fPcC8gbQIKA ||
 * Converting to Excel with Read Pfd File ||  || ReadPdf.xlsm ||   || https://www.youtube.com/watch?v=TauokBNVwG4 ||
 * Arranging Data with ALT E, M Shortcut ||  || Carlseburg.xlsm ||   || https://www.youtube.com/watch?v=RT0QpDF1bkI ||
 * Using the Important UNION Function ||  || Carlseburg.xlsm ||   || https://www.youtube.com/watch?v=_NE-f0CIe48 ||
 * Cleaning Up files ||  || Carlseburg.xlsm ||   || https://www.youtube.com/watch?v=Oubt5y-kKts ||
 * Graphing Financial Data ||  || Carlseburg.xlsm ||   || https://www.youtube.com/watch?v=cGHSwU8mzuU ||
 * Financial Ratio Analysis ||  || Carlseburg.xlsm ||   || https://www.youtube.com/watch?v=XyLxzwQtY2M ||

= Files Association with Reading Financial Statements Course - Parts 1 to 7: =



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.

media type="youtube" key="OHpw3HOLbss" width="560" height="315"


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



Lesson Set 3: 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 ||
 * 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.)



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

media type="youtube" key="VD9Cig_H2IA" width="560" height="315"

=**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 excel 2. 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

=**Related Corporate Finance Model Pages on the Site:**=
 * Exercises for adding DCF to Corporate Models **
 * Other Featured Models **
 * Template Models **** Featured Corporate Finance Models **
 * Scenario and Senstivity Analysis **
 * Generic Macros **
 * Auditing Files **