Proto Software

Hi,

I have a simple LBO model in Excel that I would like to run for a list of companies in the different sectors. I can get the relevant fundamental data for each company into Excel from Capital IQ, but then I want to go through that list and run the numbers through the LBO model. At the end I want to be able to look at the companies based on this as a custom indicator in a module I saw called Fundamental Data Comparative Analysis. Is this possible using the Excel interactivity provided I get the data from CIQ?

I attached the Excel model to the post for reference…

Attached File

Easy LBO.xls

107 KB

Good idea. We'll take a look.

From what I can tell this should be possible. It will work something like this:
1. Get a table of data from Cap IQ where there is a row with the relevant fundamental data for each Ticker
2. Go through each row and push the values through this spreadsheet model.
3. At each step, take the IRR (N60) or Multiple of Investment (N59) and save this with the other fundamental data.

At the end of this processing you'll have a set of fundamental data for each Ticker with the additional column, "LBO IRR" or "LBO MOI"

Let's tackle the step of pushing data through the model first. Once we get a table of data w/ the new indicator, it should be pretty easy to get this table into the Fundamental Data Comparative Analysis tool (for reference: http://protosw.com/mods/view/79)

To start, I tried to identify all the relevant inputs to the spreadsheet that affect the IRR. It looks to me like they are:
Last Historical Year
Acquisition / Exit Multiple
Projected Sales Growth
Equity (used in financing)
Blended Cost of Debt
Effective Tax Rate
Revenue (LTM)
EBITDA (LTM)
EBIT (LTM)
Capital Expenditures (LTM)

Some of these look like they'll come from you and some will come from CIQ, right? Can you confirm that CIQ is only providing the LTM numbers?

For simplicity to start, I'll assume that the user-set variables are entered in the spreadsheet. We can set these up to be changed from Proto, or saved, etc. later depending on the need. But for now, the inputs to the model for each Ticker are:
Revenue (LTM)
EBITDA (LTM)
EBIT (LTM)
Capital Expenditures (LTM)

I've reposted the workbook you attached with a "Proto" sheet in it which is tied into the model you made (the cells in the model sheet that link up to the Proto sheet are green). The reason I use a Proto sheet is so that if you change the model (add rows, columns, etc.) the range that Proto writes to still stays the same. Excel will manage the link internally, but if Proto writes to E4 and that cell becomes D5, the interaction will break. Anyway, I've found this to be a good practice.

If the model looks correct, the next step will be to get some data that looks like:
Ticker, Revenue (LTM), EBITDA (LTM), EBIT (LTM), Capital Expenditures (LTM)

Can you do the following?
1. Confirm that entering the LTM numbers in the Proto Sheet gives the correct IRR / MOI number back in the Proto Sheet under output? I chose the IRR output, but you can change this if you want.
2. Can you provie data for a couple Tickers? If not I can make some up, but it will be more interesting with real data. I added a "Data" sheet to the workbook with the columns as above.

Finally, if I've missed anything or am on the wrong track please let me know. Thanks for posting -
Byron

Attached File

Easy LBO.xls

110 KB

I confirm that the drivers in your new spreadsheet do work for the IRR/Multiple of Investment Calculation. As well, I have updated a spreadsheet with CIQ links that can be used with any ticker and some companies I found when doing a quick screen. For simplicity, assume that the tax rate for all companies is 40% (as is in the model) and that the first projected year (slight variation from the previous model) is 2006. Also, I added a line on your data spreadsheet called TEV / LTM EBITDA - since these are public companies with trading values, one could assume the average take-out multiple for buying a public company (15%-25% historically) and apply it to the company's current TEV / LTM EBITDA to drive the valuation more accurately. I built this into the model so that it can be used. In addition, the historical financials can be excluded for this exersice and added later (also attached). Things that would be interesting to flex (assuming all else is constant) would be financing (equity / debt split), premium paid (15% - 25%), and sales growth - just food for later thought.

Attached File

Easy LBO (2).xls

115 KB

Ok – check out the attached module.

A couple notes on opening and loading it:
1. The Excel file, “Easy LBO.xls” is now bundled in with the Proto module, so it will launch automatically when you open the Proto file. It includes a couple edits since "Easy LBO (2).xls".
2. Just a reminder that when you open the module, it will be Read Only. To save it to your repository, select “File>Save”. This will import the Proto file to your repository and will copy the Excel resource to the repository’s “Shared Resources” folder.
3. The CIQ plug-in is acting very sporadically for me. My best luck so far is to

- close Excel

- open Excel and Disable the plug-in

- open the Proto file and accept the error

- then enable the CIQ plug-in and F9 the sheet.

I’m trying to figure out a set of standard steps to work around this and will post back if I lock down the process on my end.

Easy LBO for All Tickers
- The module allows you to pick a set of Excel data to read (with the same columns as defined). Right now it defaults to the range in the Data worksheet and will auto-grow down if you add more Tickers.
- There is a force reread from Excel incase the data doesn’t automatically come in (which it should) or you type over some values to experiment.
- As a sanity check the data is shown in a preview table. You can also change values in this table to do “what if” on the fundamental data.
- The “Processed Data” table shows the output sorted by IRR of running the iterations through Excel. You should be able to see the Excel workbook cycle through calculating each row if you line up the windows right wiht Excel in the background (to test this you can click the “Recalculate with new Parameters” button).
- You can change the assumptions for Equity used, premium paid, and sales growth using the sliders. Click the button “Recalculate with new Parameters” to reprocess the data.

Let me know how it goes...

Attached File

Easy_LBO_for_All_Tickers.proto

187.8 KB

This model is terrific! Is there a way to create a viewer that plots IRRs vs. Implied Purchase Multiple (Implied Purchase Multiple = Premium x LTM EBITDA Multiple)? If so, you could use this to look at a bunch of data and pick the companies for which you paid less than 10x LTM EBITDA and returned 20+%...

thanks.

I made a couple upgrades as per your suggestions.
The data is now plotted in a scatter plot so you can see the companies comparatively. You can pick what to plot versus IRR (including a new "Implied Purchase Multiple" attribute which is calculated in proto), and you can click and drag to select companies on the graph to see what the Tickers are.

I also made a fix to the Excel sheet. The TEV/ LTM EBITDA was still using the 8x hardcoded in from the model before we incorporated the premium. I realized this when I started adjusting the assumptions as well as testing different values for imaginary companies and changing the TEV/ LTM EBITDA input didn't change anything. Since most of the companies in the sample data have very similar TEV / LTM EBITDA, the results probably didn't look too crazy. However, one company now has a >100% IRR! Let me know if you target them... wouldn't that be cool.

When you open the module and try to save, it will probably alert you of a resource conflict since you already will have an "Easy LBO.xls" in your shared resources. You can either rename that one, delete it, or Proto will let you change the name of the one you're importing. Likewise, you might have to 'save as' with the module to get it in your repository. Let me know if it gives you any trouble.

I put the module up on the Gallery this time so you can see it / download it there:
http://protosw.com/mods/view/86

take a look and let me know... thanks for all the feedback and the idea.

This is really terrific!! Thanks again for putting this together.

The http://protosw.com/mods/view/86 link is no good...

Use this: http://protosw.com/mods/app/view/86 to view the LBO analysis.
thanks,
Byron