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


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.






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

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









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
IMAG0184.jpg
Google Drive: e-mail to edwardbodmer@gmail.com


Related Corporate Finance Model Pages on the Site:

Exercises for adding DCF to Corporate Models
Other Featured Models
Template ModelsFeatured Corporate Finance Models
Scenario and Senstivity Analysis
Generic Macros
Auditing Files

Corporate Model Exercise Basic.JPG
Watch Video
Corporate Model Exercise Minimum Cash Balance.JPG
Watch Video