Microsoft Excel allows great flexibility when developing financial models, which is important given the level of subjectivity involved with determining variable inputs. Whenever a financial modeler prepares a set of projections or is doing an analysis that requires several inputs that are estimated, that value of the analysis becomes based on subjective values used to drive the model. In these situations, there are methods to provide ranges of possible outcomes. These ranges are crucial given the subjectivity of the inputs. The two main approaches are sensitivity analysis and scenario analysis.
Sensitivity analysis can be thought of as taking a particular variable, like price per pound, number of units sold or interest rate, and changing it to see the impact on the overall analysis. For example, assume that an analyst forecast assumes that there will be a 5% increase in the unit prices and a 5% increase in overall sales demand. One way to determine the impact of each assumption, one could keep unit prices constant while leaving the overall demand the same, or vice versa. Under this method, the analyst can see what impact changing one variable has on the overall assessment. This can be applied to any number of variables used in the model, and in general, this is referred to as “stress testing” the model. Under a worst case scenario analysis, one would take a variable like sales growth and assume 0% or negative, or take a cost assumption and increases it significantly. By taking this approach, one can see what might happen if the current product or service provided by a company was suddenly significantly altered one variable at a time and represents the ultimate stress impact.
Scenario analysis can be thought of as multiple sensitivities performed simultaneously. For example, an automobile manufacture has several things that affect the ability to sell cars and make a profit, like foreign competition, union wages and escalations, increasing costs of inputs and assumptions regarding postretirement benefits and pensions. Instead of assuming merely a 0% sales growth or increasing costs, managers may want to know what the combination of several effects might be and label different scenarios. One might assume 1) an increase in foreign car presence that would decrease domestic demand for cars; 2) higher union wages driven my contracts approaching expiration; and 3) recessionary impact on disposable incomes. Changes in these three variables might constitute on scenario, and the multiple variable changes can be assessed.
Normally, using data tables in Excel is an excellent method by which to assess stressing of variables. Of course, data tables at most can provide up to two variables simultaneously. Because of this, it is common to have three or four data tables in a row to get an overall assessment of changing more than two variables at a time. An example of this would be calculation of investor returns. One could change the exit multiples and year of exit in one, the exit multiple and sales growth in another, the amount of total debt and year of exit in another, and so on. The series of these analyses provides an overall picture of the changes in variables that may be very helpful in determining the factors that affect the outcome of the financial model the most.
Friday, July 3, 2009
Wednesday, May 13, 2009
A Simple Model Concept to Assess Leverage Capabilities in Microsoft Excel
In this article, I break away a bit from Excel specific information to address a fundamental concept related to corporate finance when companies want to issue debt or a private equity firm wants to acquire a company and use leverage. Even in the current environment, there are still some willing traditional and alternative lenders, but it is certainly not as robust as it had been 5 years ago. In any event, the method by which to quickly assess whether or not a company can handle debt is useful in determining which ideas can be acted upon and which ideas should be shelved.
Many years ago in my early days of investment banking, I developed a Quick Look Model for refinancing for our group to assess the capability of companies to issue debt. It was a simple model that required few inputs but could quickly evaluate whether a debt transaction was viable. Beginning with the latest calculated EBITDA, I constructed a forecast based on EBITDA change for a five-year period. Once you have depreciation, capital expenditures, non-cash working capital changes and a tax rate, the model can be constructed.
Staring with the recent history, grow EBITDA by whatever amount is necessary that fits with the latest news and/or understanding of a company's prospects. For example, if EBITDA for the last fiscal year was $100 and the business prospects are steady but not spectacular, you may assume a simple 5% growth rate per annum, so $105 in year 1, $110 in year 2, $116 in year 3 and so on. The next step is to understand depreciation, along with capital expenditures. In the simplest of models, and without detailed information on expenditure plans, analysts will sometimes have depreciation and capital expenditures as offsetting items. In other words, a company will spend enough each year merely to replace the amount of asset depreciation. In this fashion, the net plant, property and equipment will stay the same for the forecasting period. Finally, you need to make some assumption abut working capital uses. For a quickly growing company, it is very likely that cash will be tied up in working capital as inventories and accounts receivables build. For more mature companies, working capital may be consistent over time as the established entities tend to have long-term relationships with suppliers and customers and the swings in net working capital may be less volatile. Whether you use a percentage of revenue approach or other method, this is another subjective input requiring thought by the financial modeler.
One thing to keep in mind is the iteration concept in Excel. In my opinion, it is more appropriate to use the iteration style of modeling for financial purposes to more accurately reflect the inflows and outflows of cash. For example, you could make an assumption regarding the payment of debt that allows the beginning cash balance (say January 1, 2009) to determine the total amount of interest an entity would pay for the calendar year 2009. This is only appropriate if the type of debt is either a non-principal paying loan or one in which only year-end amortizations occur. In the real world, companies borrow from institutions that may impose quarterly or monthly interest and principal payments. By allowing for iteration in your model, you capture interest paid over the course of the year by taking the beginning and ending debt balances and averaging the numbers. You could increase the realism by using monthly or quarterly forecasting periods, but for the most part, the simple average with iteration approach will provide enough of a first cut to make a decision to do further research on a potential project or not.
Whereas in a standard valuation model in which EBITDA is reduced by depreciation to arrive at NOPAT (net operating profit after taxes, or sometimes used interchangeably with EBIT, which is not exactly correct) and then calculate free cash flow, we are looking at this from a debt perspective. As such, the way to quickly determine how much cash is left to repay borrowing, one must subtract interest expense. Again, we are not calculating the value of the firm, but rather, we are analyzing a company's ability to repay debt. EBITDA minus depreciation minus interest expense provides a pretax number that is then taxed at a specified corporate rate. To reiterate, this interest expense will be based on average balances and thus, the iteration box must be clicked in Excel.
Thus, top of your quick look model (the mini income statement information) will have a net income number plus depreciation minus capital expenditures and changes in non-cash working capital to arrive at cash available to retire debt. The bottom should have the debt balances and cash balances, which will adjust over time depending on the forecasted performance of the company. The change in debt balance will be based on the cash available to retire debt and may or may not include a minimum cash balance constraint or other minor adjustments a financial modeler may incorporate. Once the assumptions are completed, you can quickly see if a company can support debt. Some additional calculations might include coverage ratios (EBITDA/interest, EBIT/ interest) and leverage ratios (total debt/EBITDA, total debt/(EBITDA – capex)). These are the types of ratios that lender will study before determining whether or not a company can support leverage.
The key here is that you can make it as simple or a bit more detailed as you would like, but this would likely take an intermediate financial modeler no more than an hour to setup, quickly research and analyze the merits or drawbacks of a potential project. Sometimes, the most effective approach is a simple one.
Many years ago in my early days of investment banking, I developed a Quick Look Model for refinancing for our group to assess the capability of companies to issue debt. It was a simple model that required few inputs but could quickly evaluate whether a debt transaction was viable. Beginning with the latest calculated EBITDA, I constructed a forecast based on EBITDA change for a five-year period. Once you have depreciation, capital expenditures, non-cash working capital changes and a tax rate, the model can be constructed.
Staring with the recent history, grow EBITDA by whatever amount is necessary that fits with the latest news and/or understanding of a company's prospects. For example, if EBITDA for the last fiscal year was $100 and the business prospects are steady but not spectacular, you may assume a simple 5% growth rate per annum, so $105 in year 1, $110 in year 2, $116 in year 3 and so on. The next step is to understand depreciation, along with capital expenditures. In the simplest of models, and without detailed information on expenditure plans, analysts will sometimes have depreciation and capital expenditures as offsetting items. In other words, a company will spend enough each year merely to replace the amount of asset depreciation. In this fashion, the net plant, property and equipment will stay the same for the forecasting period. Finally, you need to make some assumption abut working capital uses. For a quickly growing company, it is very likely that cash will be tied up in working capital as inventories and accounts receivables build. For more mature companies, working capital may be consistent over time as the established entities tend to have long-term relationships with suppliers and customers and the swings in net working capital may be less volatile. Whether you use a percentage of revenue approach or other method, this is another subjective input requiring thought by the financial modeler.
One thing to keep in mind is the iteration concept in Excel. In my opinion, it is more appropriate to use the iteration style of modeling for financial purposes to more accurately reflect the inflows and outflows of cash. For example, you could make an assumption regarding the payment of debt that allows the beginning cash balance (say January 1, 2009) to determine the total amount of interest an entity would pay for the calendar year 2009. This is only appropriate if the type of debt is either a non-principal paying loan or one in which only year-end amortizations occur. In the real world, companies borrow from institutions that may impose quarterly or monthly interest and principal payments. By allowing for iteration in your model, you capture interest paid over the course of the year by taking the beginning and ending debt balances and averaging the numbers. You could increase the realism by using monthly or quarterly forecasting periods, but for the most part, the simple average with iteration approach will provide enough of a first cut to make a decision to do further research on a potential project or not.
Whereas in a standard valuation model in which EBITDA is reduced by depreciation to arrive at NOPAT (net operating profit after taxes, or sometimes used interchangeably with EBIT, which is not exactly correct) and then calculate free cash flow, we are looking at this from a debt perspective. As such, the way to quickly determine how much cash is left to repay borrowing, one must subtract interest expense. Again, we are not calculating the value of the firm, but rather, we are analyzing a company's ability to repay debt. EBITDA minus depreciation minus interest expense provides a pretax number that is then taxed at a specified corporate rate. To reiterate, this interest expense will be based on average balances and thus, the iteration box must be clicked in Excel.
Thus, top of your quick look model (the mini income statement information) will have a net income number plus depreciation minus capital expenditures and changes in non-cash working capital to arrive at cash available to retire debt. The bottom should have the debt balances and cash balances, which will adjust over time depending on the forecasted performance of the company. The change in debt balance will be based on the cash available to retire debt and may or may not include a minimum cash balance constraint or other minor adjustments a financial modeler may incorporate. Once the assumptions are completed, you can quickly see if a company can support debt. Some additional calculations might include coverage ratios (EBITDA/interest, EBIT/ interest) and leverage ratios (total debt/EBITDA, total debt/(EBITDA – capex)). These are the types of ratios that lender will study before determining whether or not a company can support leverage.
The key here is that you can make it as simple or a bit more detailed as you would like, but this would likely take an intermediate financial modeler no more than an hour to setup, quickly research and analyze the merits or drawbacks of a potential project. Sometimes, the most effective approach is a simple one.
Friday, March 20, 2009
Microsoft Excel: Understanding Pivot Tables
Pivot tables can be a great time saver for presenting custom charts or tables that relate to a lot of data. In fact, Microsoft has made the process relatively easy and straightforward to set them up, but there are some tricks that can be employed after a table is constructed that can help the financial modeler develop an array of summary tables. Simply put, pivot tables allow you to display a variety of data in a summary table format in a process that takes only a few steps.
To begin, a financial modeler would merely click on PivotTable and PivotChart Report under the Data menu or PivotTable Wizard underneath the PivotTable menu found on the PivotTable toolbar (I normally do that latter because I have the toolbar open most of the time). In either case, Microsoft gives you a choice of where the data is located that you want in the pivot table analysis. To keep it simple, the default setting is likely to be the one you will use (Microsoft Office Excel list or database). The second step is to highlight the appropriate range of cells for inclusion in the analysis. You can either grab the database with mouse/keyboard, or you can define the range and just type in its name (it is important to note that creating a range with titles in the first row is a requirement to using pivot tables). Finally, you can place the resulting table in an existing sheet or specify a new sheet. For a basic pivot table, that is it. There is no real mystery or difficulty in setting this up, because, as stated earlier, Microsoft did a good job of making it easy to do.
Let us look at a situation where maybe there are two data points that require capturing. In a simple pivot table, you might want to know which companies sold the most widgets. You have the companies in the left-hand columns and in the right-hand column you have the total number sold (if you have a long list of repeating names in the database, pivot table automatically consolidate, one of the great things about it). But what if you wanted to know not only the sum of widgets sold, but the dollar value associated with them?
In your existing pivot table, right mouse click and then click on the wizard again. You will see a box called "Layout" that you will click. This is where you can drag multiple items into the "data" box of the pivot table, like number sold and total sales. After clicking the "Finish" button, you will now see a pivot table that has two pieces of information for each company. In addition, you can drag information into the column area and have data broken down by company name and, perhaps, day of the week I which the sales were made. This can be done by merely dragging and dropping – it doesn’t get much easier than that.
For those of you who like formulas and want to do some customer table building, there is a basic formula for use in conjunction with pivot table:
=GETPIVOTDATA(data_field, pivot_table, [field1], [item1], [field2], [item2],…).
In this instance, up to 14 field/item references can be used. The data_field refers to the sum or count of something, like number of widgets, and the pivot_table is, obviously, a reference to the pivot table you are using (is the top left corner of the pivot table and the cell should be absolute referenced, i.e., dollar signs). The field/item combinations refer to the label of the information you are seeking and then the specific search item. For example, if you had a pivot table with care sales by color, the field might be "cars" and the item might be "black" and, assuming your data_field was "sum of sales price" you would get the sum of all black cars sold that exists in your database.
This is meant to be an introduction to pivot tables. Honestly, I found out more about utilizing the various features (that would be another article) by just playing around with different format and data combinations. If you take a few hours just to break a pivot table down, understand the layout button and work with the GETPIVOTDATA formula, you will have a base understanding. Once you accomplish this, you will understand how a bunch of data in a spreadsheet easily can be broken down and conveyed in simple, yet effective, summary tables.
To begin, a financial modeler would merely click on PivotTable and PivotChart Report under the Data menu or PivotTable Wizard underneath the PivotTable menu found on the PivotTable toolbar (I normally do that latter because I have the toolbar open most of the time). In either case, Microsoft gives you a choice of where the data is located that you want in the pivot table analysis. To keep it simple, the default setting is likely to be the one you will use (Microsoft Office Excel list or database). The second step is to highlight the appropriate range of cells for inclusion in the analysis. You can either grab the database with mouse/keyboard, or you can define the range and just type in its name (it is important to note that creating a range with titles in the first row is a requirement to using pivot tables). Finally, you can place the resulting table in an existing sheet or specify a new sheet. For a basic pivot table, that is it. There is no real mystery or difficulty in setting this up, because, as stated earlier, Microsoft did a good job of making it easy to do.
Let us look at a situation where maybe there are two data points that require capturing. In a simple pivot table, you might want to know which companies sold the most widgets. You have the companies in the left-hand columns and in the right-hand column you have the total number sold (if you have a long list of repeating names in the database, pivot table automatically consolidate, one of the great things about it). But what if you wanted to know not only the sum of widgets sold, but the dollar value associated with them?
In your existing pivot table, right mouse click and then click on the wizard again. You will see a box called "Layout" that you will click. This is where you can drag multiple items into the "data" box of the pivot table, like number sold and total sales. After clicking the "Finish" button, you will now see a pivot table that has two pieces of information for each company. In addition, you can drag information into the column area and have data broken down by company name and, perhaps, day of the week I which the sales were made. This can be done by merely dragging and dropping – it doesn’t get much easier than that.
For those of you who like formulas and want to do some customer table building, there is a basic formula for use in conjunction with pivot table:
=GETPIVOTDATA(data_field, pivot_table, [field1], [item1], [field2], [item2],…).
In this instance, up to 14 field/item references can be used. The data_field refers to the sum or count of something, like number of widgets, and the pivot_table is, obviously, a reference to the pivot table you are using (is the top left corner of the pivot table and the cell should be absolute referenced, i.e., dollar signs). The field/item combinations refer to the label of the information you are seeking and then the specific search item. For example, if you had a pivot table with care sales by color, the field might be "cars" and the item might be "black" and, assuming your data_field was "sum of sales price" you would get the sum of all black cars sold that exists in your database.
This is meant to be an introduction to pivot tables. Honestly, I found out more about utilizing the various features (that would be another article) by just playing around with different format and data combinations. If you take a few hours just to break a pivot table down, understand the layout button and work with the GETPIVOTDATA formula, you will have a base understanding. Once you accomplish this, you will understand how a bunch of data in a spreadsheet easily can be broken down and conveyed in simple, yet effective, summary tables.
Subscribe to:
Posts (Atom)
Posts
