.
This webpage discusses issues associated with corporate finance modelling and corporate finance theory. I begin with some provocative ideas the critique the academics, the McKinsey Valuation Book and accounts among other things using review of financial data, stock price analysis and simple excel exercises. I assure that you can compute some basic valuation formulas. The correspondence between corporate finance and project finance is documented and various corporate finance issues are addressed in the context of a corporate valuation case.
I discuss corproate finance along with M&A modelling in the context of a couple of case studies. I put the most recent case study of Orsted first and then I discuss the HBS case study called Burton Sensors (a fake company with a horrible model). The Orsted case works through what happened that caused the stock price to decline from 1243 to 263 per share (in Dkk). One can argue that a lot of this decline came about because there was no project finance discipline and because of the aggressive strategy of the company that did not account for the venture capital characteristics of the company.
I have used a Harvard Business School (HBS) case named Burton Sensors to illustrate the construction of a financial model. The model below illustrates some issues in setting up timelines and constructing the correspondence between history and forecasting in a model. I believe this case is used in many MBA programs and is probably taught in one class.
Orsted Case Study
A description of the Orsted case study along with a video is included in the powerpoint slides below. These slides include a financial model with a M&A case.
.
Power Point Slides on Case Studies for Renewable Energy with Orsted Case and With M&A Model Case
.
.
.
.
Partner Files to So You Do Not Waste Time on Formatting and Using the Paint Brush
I have included two files that are used to make the excel exercises work more smoothly. The first is a file called Read PDF which allows you to grab data from PDF files and then convert the data to excel files. To run this file you copy stuff from the pdf file and then operate the macro with SHIFT, CNTL, A. When you copy data from a PDF to excel, make sure that you copy and paste special as UNICODE text. There are different formats that you can use to resolve the PDF. If you are reading from the Lazard LCOE stuff you can use the first green box. If you are reading from the PVGIS you can use the second green box.
The second file is a file that has a whole lot of macros to prevent you from wasting time on formatting and copying formulas to the right. This file is called GENERIC MACROS. I have revised the generic macro file in the link below so that you are not prevented to open it because of something called the auto open — a macro that operates when you open the file and excel considers dangerous. When working through the exercises, it would help a lot if you have this file open and enable the macros. The big things that this GENERIC MACRO allows are to press SHIFT, CNTL, R to copy to the right and also CNTL, ALT, C to open the window that has a whole lot of formatting options. There are a lot more utility macros in the GENERIC MACROS file. You can go to https://edbodmer.com/excel-utilities-and-backpack/generic-macros-file/ and see some of the other stuff including a few user defined functions.
.
.
.
.
Database Files for the Course
The files below include the power point slides we will use for the course as well as data files that include long term history and stock prices for individual companies. The files with data provide an introduction to valuation issues in the course that are driven by the project rate of return and the cost of capital. Using this data you can get some background on the difficult issues of (1) how to evaluate the cost of capital; (2) how to consider the valuation ratios such as P/E and EV/EBITDA ratios; (3) how to evaluate terminal valuation and long-term growth prospects. I have put some different samples attached to the various links below.
.
Outline for Corporate Valuation Theory and Corporate Modelling Course using Excel Analysis
.
Power Point Slides with the Theory of Corporate Valuation and Discussion of Corporate Models
.
.
The files below come from the new financial database that I have recently created. This procedure to get financial data into a database I think has a much better presentation and it has much more historic financial data.
.
.
.
US Utility Companies with Historic Analysis of Financial Data and Stock Prices from Yahoo Finance
.
Saudi Companies with Historic Analysis of Financial Data and Stock Prices from Yahoo Finance
.
.
Article from Credit Suisse by Dimson, Staunton, Marsh on Equity Risk Premium with 3% Estimate
.
.
Growth and Return
.
I have putt some selected examples below.
.
Note in how the price to book ratio and the return is different for different types of companies. The graphs below shows the variation in return with the COVID.
.
In my opinion the Burton Sensors case includes many issues and cannot be dealt with in one class session. I go further and suggest the case can be used as the centerpiece of corporate finance and investments by addressing financial modelling, bank financing, equity financing, capital budgeting, valuation, use of multiples in corporate finance and merger and acquisition issues. Specific issues in the case include:
- Development of an effective financial model is essential in addressing corporate finance issues. The financial model should evaluate historic trends and provide understanding of the potential to earn economic profit. The fundamental issues involve calculation of the economic return on invested capital and the prospects for earning future returns. The model provide in the case is does not come close to allowing you to consider essential issues for valuation, M&A assessment, capital budgeting or financing issues. It also includes the three statement financial modelling.
- The financing component of the case involves interpretation of financial ratios and financing with short-term debt, long-term debt and common equity. The first issue that can be demonstrated is the issue of continued growth. To evaluate this issue you can make a model with different growth scenarios and see if you run into problems with the limits on the short-term debt financing. I suggest that instead of ratios like accounts receivable to debt, you compute more classic debt to capital ratios and debt to EBITDA ratios.
- The case involves an investment valuation components where something called Thermowell machines are considered. In the real world these machines should be assessed with capital budgeting analysis and IRR or NPV. I suggest that this part of the case on assessment of the Thermowell machines can be assessed with a project finance model. In the discussion below I present an exercise like one of the exercises that is used in an interview exam with inputs.
- When discussing the issuance of a private equity placement, valuation is the central and this means that a valuation of the company should be made to assess whether amount suggested in the case is a reasonable price. To do this you deal with the painful issue of terminal value. You can try using terminal growth rate and/or EV/EBITDA ratios. You also can incorporate different scenarios in the analysis and evaluate what you think is a reasonable return on invested capital over the long-run.
.
Iris, Communication and MBAs
I was first introduced to the case by somebody who was studying for an MBA degree. I was able to see how the case was used in a well know MBA program. The emphasis in the class was on un-levering and re-levering cash flows in computing the cost of capital and valuing the company. The discussion also involved understanding how deferred taxes work. In reading the case I think there are much more important and fundamental issues of financial statement analysis, financing constraints, investment evaluation, corporate valuation, assessment of multiples and evaluation of the costs and benefits of merger and acquisition. After seeing how the case was used in the class, I thought about how to re-do the case with a proper financial model and proper financial analysis.
This is where Iris comes in. She was crazy enough to want to keep going in some virtual classes and had a remarkable attitude in wanting to learn both financial modelling and financial analysis. You will see her attitude and development of both finance knowledge and finanal modelling in the videos below. I cannot give her an MBA degree, but I suggest that if you follow our along with the modelling in this case like Iris, you will gain essential skills in corporate finance and project finance. If you follow the videos I made with Iris and try the exercises that she worked through, you should also become proficient in both corporate and project financial modelling.
.
.
HBS Secret Police
A long time ago, I accidently (maybe not) allowed you to download a Harvard Business School (HBS) case on this website. Well I received emails and very nasty threats of being sued if I did not take it down from everywhere. So whilst I am using the HBS Burton sensors case, I will not allow you to download the case here in case the Stasi from HBS will put me in a gulag. Instead, you can go and pay for it and alternatively I will put some screenshots to introduce you to the case. The screenshot shows that I have purchased the HBS case. I have also provided spreadsheets provided with the case which I think is ok. The spreadsheet associated with the case is attached to the button below.
.
.
.
As shown in the introduction to the case, the case includes a fundamental investment decision (thermowell machines), a question of raising capital with a private placement and an analysis of an acquisition. If you can properly evaluate these things I suggest that you have a very strong background in corporate finance. You will need to make some financial statement analysis, you will need to evaluate borrowing under a committed short-term loan facility, you will need to construct, analyze and interpret a comprehensive financial model.
.
I hope you can see that these three issues go to the core of corporate finance:
- The question of purchasing new machines involves investment decisions, assessment of risk and the cost of capital, NPV versus IRR, computing IRR on the new machines with a project model, incorporating the new capital expenditures into the corporate finance model.
- The financing question in relation to private placement involves valuation of a corporation with DCF and multiples analysis, evaluation of different real world financing strategy and incorporation of the private placement in the financial model. As with the capital budgeting this requires estimates of the cost of capital. In addition, the question of terminal value arises in developing the DCF.
- The M&A portion of the case involves understanding EV/EBITDA ratios and P/E ratios in the context of an acquisition. As part of the acquisition analysis you can also see how the acquistion with a share exchange involves an implicit equity issuance and you can evaluate returns on the new company relative to an existing company.
.
In reviewing this case we have made and applied various financial models and I use the case in describing various issues in my new book. The best thing to do would be to work through the models and create them yourself. Alternatively you can consider what you would do to evaluate the issues such as how you would measure the ability to finance operations; evaluate the costs and benefits of the capital expenditure; compute corporate value with the DCF model; evaluate the EV/EBITDA and the P/E ratios for Burton Sensors and the acquisition canidate named Electro-Engineering; and evaluate the financial performance of the consolidated company with and without the acquisition.
.
.
.
Most of the HBS cases are for real companies but this company seems to be an artifical company. There is a company in Indiana that sells sensors. A picture of some of these sensors is shown below.
.
.
.
.
.
Part 1: Basics of Building a Corporate Financial Model
The simple model provided in the case is shown below. This model provided with the case demonstrates what not to do in making a model and is the starting point of the case. In the next videos Iris and I work through how to make a proper three statement model that can incorporate different financing including use of a short-term debt facility with a limit on the commitment, funding with a private placement to support capital expenditures, financing an acquistion and of course balancing the balance sheet. If you are studying this case in an MBA class, I suggest that you download the model completed by Iris.
Before assuring that we can make a three statement model, we show you something which is more important. That is to structure the model beginning with the three historic financial statements and then to use these statements in working through operating cash flow. One of the crucial parts of this is to use an historic switch.
.
.
The restrutured model with the historic switch and the alternative assumptions layout is attached to the button below.
.
.
.
Part 2: Incorporation of Financing in a Financial Model
.
.
.
- How in the heck do you know the industry would experience a shakeout – are companies earning high returns; is there impending surplus capacity in the industry; has the pricing in the industry began to plumet.
- The idea that pricing will increase seems to be inconsistent with the notion of an industry shakeout.
- How would you measure the debt to equity of 1 to 1 and how does this relate to the short-term debt.
.
Part 3: Inclusion of Minimum Cash and Evaluation of Ability to Re-finance
.
.
.
Part 4: Evaluation of New Investment with Simple Project Finance Model
.
.
.
Task 1: Set-up model with the following assumptions:
- Capital Expenditure: 4 x 600,000 = 2,400,000
- Savings (Model as Revenues): 1,400,000 per year
- Operating Expenses: 170,000 + 780,000 = 950,000
- Working Capital in Capital Expenditure Year: 650,000
- Economic Life: 7 Years
- Include 2% Inflation on Revenues and Operating Expenses
Tasks with Project Finance Model
- Compute Pre-tax IRR
- Use the tax rate and assume straight line depreciation
- Compute After-tax IRR
.
Include Financing Assumptions in Project Finance Model
Interest Rate of 5.5%
DSCR of 1.7
Tenor of 7 Years
Sculpt Debt on Pre-tax Basis
Compute the Following:
Debt Size
Equity IRR
.
Comment on the economics of the investment
Comment on the risk of the investment
.
Incorporation of Scenario in Corporate Model:
- Reduce the Cost of Goods Sold by 1,400,000
- Increase the Other Operating Expenses to 950,00
- Include the Capital Expenditures
- Don’t Worry about Working Capital
- Don’t Change Sales Growth Assumptions from Base Case
.
.
Part 5: Incorporation of New Scenario in Model with Increase in Capital Expenditures and Sales
.
.
.
Part 6: Valuation of Company with New Capital Expenditures using DCF Model
.
.
.
.
Part 7: Valuation of Target Company Using Multiples and Simple DCF Model
.
.
.
.
Part 8: Incorporation of M&A in Corporate Model
.
.
.
.