More on the IRR Function in Excel

This article is an expansion to a prior article covering the time value of money. Because many financial modelers require information yielding returns, it is important to understand better the IRR functions, not only from an Excel perspective, but also the underlying financial theory behind the functions.

The IRR function is used in Excel to calculate the internal rate of return. What is the internal rate of return? The internal rate of return (or, of course, IRR), is the rate earned on an investment over a defined period of time. In other words, let’s say that there is an investment of $10,000 at the end of the year, and in each of the five subsequent years, the payments are $500, $600, $700, $800, $900 plus the original $10,000 returned at the end of year five. The IRR would be 6.9% and represents the yield earned on the original investment based on the periodic payments and the return of the original investment.

As one may be able to discern, in the prior example the periodic payments are not the same. Thus, one cannot use the RATE function, which assumes that payments are consistent over some timeframe. So the first thing learned from this is that when there are inconsistent periodic payments, one must use the IRR function. The second concept learned is that the payments MUST BE periodic for the IRR function. In other words, cash flows need to be every year, end of quarter or end of month. The third item that needs to be explained is that there can only be on change in sign for any list of payments, e.g., the initial investment would be entered as -$10,000 (cash outflow) with the subsequent payments having a positive sign (cash inflows).

For a bit of background, an underlying assumption of the IRR is that all cash inflows (or outflows) are reinvested at the IRR. Confused? Think of it this way, if the answer to an IRR question is 8.0%, it is assumed that the periodic cash flows are being reinvested at the 8.0% rate. So, even in reality if the flows were invested at varying rates, it is implicit by the calculation that they were invested at 8.0%. For most analyses and financial modeling, and in the absence of reinvestment rate specific, the IRR will yield a result that can help to provide a solution to the project (particularly when used in conjunction with the NPV function). To avoid unnecessary complication, the IRR can be thought of as an iterative process, which thanks to computing power, is done much more quickly than we can do by hand.

If there is information on a different reinvestment rate, then one would use the modified internal rate of return function, or MIRR (=MIRR(values, finance_rate,reinvest_rate)). This function assumes that you can take your periodic cash flows and invest at some rate other than the IRR, and incorporates a finance rate, which is the rate on the initial investment. Said more simply, if the finance rate equals the reinvestment rate, you would only need the IRR function. If, for example, we revisit the original example and set the finance rate equal to the original IRR, but change the reinvestment rate to 5.0%, the overall return is reduced to 6.68% (clearly, lower reinvestment rate than the original IRR). If the reinvestment rate is 8.0%, the overall return is 6.99%. While this is a function that is likely not be used frequently, it may arise and it is helpful to know that there is a formula to handle a situation like this.

Finally, there are instances in which the cash flows are not periodic, and the IRR function will not provide an accurate result. Excel provides the XIRR function, which takes into consideration irregular payments. For example, referring to the original example, assume that those payments occurred as follows: original investment 12/31/04, subsequent payments on 3/31/05, 4/30/06, 6/30/07, 8/31/08 and final payment and return of investment on 12/15/09. Using the XIRR function, the overall return would be 7.12%. As one can see, the timing of the payments impacts the returns, a fundamental truism of the time value of money. Most of the time, the IRR function will be used, but when there is an instance of specific irregular payments, the XIRR function should be used.

Understanding the Big Picture in Financial Modeling

It is time to revisit a basic but fundamental aspect of financial modeling: The Big Picture. What exactly is The Big Picture ("TBP")? TBP refers to the overall goal of the financial model and what answer the model is specifically being developed to answer.

Many times in my career I have been asked questions about financial modeling in Excel, questions that really indicated that TBP was not yet established. This is not a knock on anyone's intellectual capacity but rather, it confirms what I have always believed: people cannot sit down in front of a computer and miraculously create a financial model that provides the answers to relevant questions without first addressing the overall scope and purpose of the exercise.

Let us assume, for the moment, that you are a budding young financial analyst at Toyota and you have assigned to analyze domestic (U.S.) cars sales. If we assume that Toyota, being a global entity, has all of the reporting and data readily available, there are no information glitches in the exercise and it is all up to you, the financial analyst, to develop this model. So, how would you begin?

If the corporate office was interested in number of Camrys versus Tauruses sold in the state of Michigan, the exercise is straightforward. You could easily pull information on total car sales by make and model and have the answer. If the analysis is, however, to determine how much of an advertising campaign is required to increase market share by 3.0%, there will be more thought required to create the model. In this case, TBP can be thought of in a somewhat formulaic relationship: TBP = advertising impact on market share. This becomes the basis of the model.

Given the way that the formula is presented, this may be an opportunity to use a data analysis function incorporated in Excel like the regression analysis. Whenever an analyst is trying to determine relationships, taking historical advertising expenditures and running that against changes in market share may provide the appropriate regression equation from which the forecast can be created. For example, you may take 5 years of monthly data and determine that increases in advertising translates directly into increases in market share (all other variables held constant), and this will allow you to understand what the future market share over the next year might be based on those relationships. If the model displays profit margins, return on invested capital or reductions in debt, than TBP was not addressed. Those items reflect supporting data, but remember TBP: impact of advertising on market share.

The purpose of this brief article is really to beat into your head the need to focus on TBP. Part of being an effective analyst, consultant or advisor is listening to the problem and providing the relevant solution. If you need 10 apples for a pie, finding 12 oranges is not helpful. Focus on the purpose of the model before you start creating spreadsheets and you will find your efficiency in financial model development increasing significantly.

Sensitivity and Scenario Analyses in Financial Modeling

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.

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.

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.

The INDIRECT Function in Microsoft Excel

The INDIRECT function in Excel is used when you want to reference a cell to get input related to another cell. Sounds strange at first, but it is a clever tool that can provide some flexibility in your financial modeling but only in the rarest of occasions. This formula can provide the financial modeler with the ability to more easily interpret changing data all in one cell, without the need to develop a very long string of formulas and references in a cell, but for most financial model practitioners, you may never need to use it.

The form of the INDIRECT function is =INDIRECT(ref,a1), where "ref" is the cell to which you are referring and "a1" represents a true or false option – if false, the function will interpret the data on an RiCi-style basis and if true or omitted, the data is interpreted as a letter-number style (also known as A1 style).

For example, let's assume that the ultimate data the model is seeking is contained in the area defined by G3:G7 and that these are numbers range from 1 to 100. Further assume that the headings in the area defined by F1:F7 are Mon, Tues, Wed, Thurs and Fri. So this could be unit sales per day, lost items per day, ending inventory or whatever specific analysis is warranted. If you wanted to know how many sales took place on Friday, you would enter =INDIRECT(H7), with H7 containing "G7" and it would go to the value in G7. This is pretty cool, but not entirely useful. There are many ways to accomplish this without using INDIRECT, but this is merely an example of how to incorporate the formula.

I can tell you that I have probably used the INDIRECT formula once or twice in my entire career. That does not mean that it is totally useless, it just means that for most financial modeling tasks there is no need to incorporate it. In particular, most lookup or searching situations can be accomplished by the more familiar formulas including LOOKUP, VLOOKUP, HLOOKUP, SEARCH or FIND. This is not to say that you will never use it, for there may be that rare opportunity to do so. The point to this story is that you will probably never need to incorporate this formula in your normal, day-to-day financial modeling tasks.

Developing a Basic Financial Model - Part VII: Long-Term Liabilities and Equity

This is the final installment in the introductory series of fundamental concepts of financial modeling. It is important that the reader has some familiarity of the three major financial statements (income statement, balance sheet and cash flow statement) that I covered in the prior three articles. If not, please read those first prior to continuing.

Long-Term Liabilities

The most common long-term liabilities are debt and capital lease obligations, deferred taxes, and pension and postretirement benefits. For financial modeling purposes deferred taxes are often left alone or changed according to specific information for forecast horizon. A similar concept may be used for pension and postretirement benefits. Pension accounting rules are fairly complex and rely upon the use of an actuary, and for the average financial modeler, the incremental logic required to correctly forecast changes in those accounts will not necessarily yield a substantially more robust model. Simple assumptions like keeping that category as a percentage of revenue can be used under the logic that people drive revenue and more revenue means additional hires in many cases (and vice versa). The complexities of forecasting deferred taxes often call for this simpler approach. Note that if you have the accounting training you can certain be more precise, but my experience has shown me that unless there is a specific reason for doing so, standard assumptions should drive these categories.

Debt and capital leases, on the other hand, are items that can be modeled correctly. Capital leases have a principal and interest component to them, and Excel has formulas for both (PPMT and IPMT). With the stated interest rate and time period, those calculations are straightforward. For debt that has a fixed rate and specified maturity (like a term loan), those calculations are also basic. If it is a security like a mortgage, the same PPMT and IMPT calculations could be done. What is more common, however, is to look at the balances of the debt at a given period and apply the interest rate to determine the amount of interest shown on the income statement. If there are mandated principal payments, those would show up on the cash flow statement, and you would use them to reduce the balance of debt shown on the balance sheet.

A common form of debt is the revolver or senior line of credit. Many companies turn to this form of debt to finance working capital or seasonal swings in the business. Normally, as long as a company has sufficient accounts receivable or inventory, management can draw down on this line of credit, pay it down when there is excess cash or do nothing until it matures. From a modeling perspective, the revolver is important in managing minimum cash balances, maintaining working capital levels or covering any one-time purchases. This is basically overdraft protection for companies. Your model should be flexible enough to determine what level of balance sheet cash is necessary, reduce the revolver to the extent there is sufficient cash flow, or even repay other forms of debt if necessary. This type of modeling is best done under Excel's iteration option under Tools, Options, Calculation. The rationale for this will become more evident as you do the models, but in short, the iteration provides the real-life dynamic interpretation of how a company will perform versus static sets of assumptions. Required retirement of debt, interest calculations and cash flow sweeps (always utilizing the excess cash flow generated to paydown debt) are all part of this component of the model. All increases and decreases in debt totals will flow through the cash flow statement in the financing section.

Equity

In financial modeling, the equity account provided few problems. Within this account, whether a c corporation, an s corporation, an LLC or any other entity, the equity account represents the value in a firm after all obligations are paid. You may see this listed as stockholders' equity, stakeholders' equity, members' interests or other similarly-named account, but they all mean the same thing. If you are modeling a c corporation, you are likely to have common stock, paid-in capital and retained earnings listed. Occasionally there may also be a preferred stock entry, a treasury stock entry or even a line for net income or distributions (as seen in QuickBooks for many small businesses).

To keep it simple, the major things that affect the equity account are the issuance or retirement of equity, dividends or distributions and net income. When you think about your financial model, know that net income will increase the equity account (specifically retained earnings) and net losses decrease the account. Dividends or distributions also decrease the account because cash is leaving the business. If you issues new equity, the overall equity account grows, and if you repurchase equity, the opposite holds true. Any dividends or equity issuances are captured on the cash flow statement in the financing section, and you will get the net income number on the income statement or cash flow statement under operations.

This concludes the introduction to financial modeling series, as future articles will get back to the focus of specific Excel formulas and delve more into intermediate and advanced financial modeling techniques. For now, with these few articles as a backdrop, you should begin to feel a bit more comfortable in developing a basic financial model. At the end of the day, the only way you will begin to be a competent financial modeler is to start modeling. Books, articles and other guides are helpful in addressing specific points, but nothing will be as helpful in learning to develop financial models as sitting down in front of your computer and doing them.

Developing a Basic Financial Model - Part VI: Long-Term Assets

In continuing in our series of fundamental concepts of financial modeling, and after taking a quick break to discuss the cash conversion cycle, I will now turn to another initial step of understanding how to forecast financial information. It is important that the reader has some familiarity of the three major financial statements (income statement, balance sheet and cash flow statement) that I covered in the prior three articles. If not, please read those first prior to continuing.

Long-Term Assets

The most common long-term asset for many industrial or manufacturing companies is property, plant and equipment ("PP&E"), also referred to in certain cases as fixtures, furniture, fixtures and equipment. PP&E is a category on the balance sheet that typically captures large pieces of equipment used to generate products. For example, a car manufacture would include all of the assembly line equipment like conveyor belts, robotic arms, power drills and lifts, etc., in this category. Also computers, desks, chairs, leasehold improvements, land and buildings would be included in PP&E. In most financial statements, a company lists both gross PP&E and net PP&E. The gross amount is the actual totally dollar amount a company paid for all of its equipment and the net amount represents the book value of those same items after depreciation is included.

What is depreciation? Depreciation is a means to try to establish useful lives for various assets based on both accounting standards and the tax code, which have different approaches. For example, a computer may have a five-year asset life for both accounting and tax purposes, but a company car might be depreciated over 10 years for accounting and five years for tax purposes. It is not uncommon to have assets classes with disparate timeframes between GAAP and tax methods. The Financial Accounting Standards Board regulates GAAP, which constitutes the rules for accounting methods and the IRS is the regulatory agency behind the tax code. These two entities have different rules for governing depreciation methods and a general understanding of the differences is important prior to developing a financial model. Further, some analyses may get into very complex tax code understanding, so if your project calls for a deep dive into the tax impact of decisions, you should have a resource to address those questions. In many instances of simple financial modeling, however, the book method and the tax method are left the same and much of the aforementioned differences become moot.

To keep everything simple, financial modelers will take the entire net PP&E amount and use what is called "straight line" depreciation, or subtract the same depreciation amount from PP&E each year. For example, if the beginning total was $100,000,000 and accounting rules dictate that the assets are depreciable over a 20-year period, the depreciation would be $5,000,000 per year, if there is no "residual value" (residual value, or salvage value, refers to the amount one thinks an asset would be worth at the end of the useful life to a company and this value does not exist for tax purposes). If there is a residual value of $20,000,000, you would depreciate $80,000,000 over a 20-year period, or $4,000,000 depreciation expense per year. From a modeling perspective, it is easy to do an explicit depreciation calculation based on the accounting timeframes.

Companies build up the PP&E category through capital expenditures ("CapEx"). CapEx can either be improvements to existing equipment or new equipment purchases. To determine the amount of forecasted CapEx, there are two methods: explicit time horizon or ratio. Under the explicit time horizon, the financial modeler would have specific information on the spending needs of a company. For example, assume that the management team must spend $30 million equally over the next three years to upgrade existing equipment. In this case, you know that $10 million per year will be spent. If you do not know the exact amount, you would use a ratio to determine total CapEx, like a percentage of revenue. Let's assume that over the past few years a company has spend 5% of total sales in CapEx. Barring some specific news of the future, you might assume that the 5% ratio would hold for the foreseeable future. Another way some financial modelers will forecast CapEx, particularly for a company in the mature stage of business, is to have CapEx equal depreciation. This way, the net PP&E will stay the same over the forecasted horizon. Whichever method you choose to use should just make sense from an historical performance analysis as well as incorporating future expectations.

Another common long-term asset is goodwill, which is an intangible asset. Goodwill arises when one company buys another company for a value that is in excess of the net asset value. This "extra" value is, presumably, related to the positive intangible aspects of running a successful business, and the amount is placed on the balance sheet at goodwill. The current accounting rules for goodwill dictate that the total amount be evaluated periodically for potential decreases in value. If there is a determination that the goodwill account is higher than it should be, the goodwill is considered impaired and a write-down is required. For financial models with a short forecast horizon (three to five years), the goodwill is rarely adjusted. Other intangible assets include patents, trademarks, copyrights, etc. and there are specific time periods by which these categories are amortized (amortization is depreciation but for intangible assets). Patents are generally amortized over their legal life, trademarks, while technically indefinite, are amortized over their useful lives, and copyrights are amortized over a time period reflective of the costs to obtain such copyright. From a financial modeling perspective, there accounts are very straightforward and require little to no adjustment over the forecast horizon.

There are other long-term assets, like deferred taxes, long-term investments and various prepaid rights. The category most spend time getting right, however, is PP&E. It is of paramount importance that you have a basic understanding of depreciation methodologies and CapEx rationale in order to correctly forecast PP&E. The vast majority of the other long-term assets are much easier to model and once the PP&E calculations are conquered, the rest of the long-term assets will seem like child's play.

Developing a Basic Financial Model – Part V: More on the Cash Conversion Cycle

In continuing in our series of fundamental concepts of financial modeling, I would like to spend a bit more drilling down into some detail on the cash conversion cycle, or cash cycle ("CC") prior to heading on to long-term assets and long-term liabilities. This relatively straightforward concept can provide some insights into determining whether or not a business has become more efficient or is headed towards insolvency. This has impact on a financial modeler because historical CC values should not differ substantially from the forecast unless the financial analyst has specific, relevant information to suggest that there will be a shift in the operations of a business in the future.

As a quick refresher, the CC is equal to days receivables outstanding PLUS days inventory outstanding MINUS days payables outstanding, or DSO + DIO - DPO. This determines how quickly a company receives cash when it sells a product from inventory. Depending on the industry, the cycle can be very quick or very slow. For example, assume that you are a manufacturer of large, custom made home furniture, like armoires. A typical process would be a customer placing an order on day one, and at that time, you need to begin the process of making the furniture.

The first step would be to call a supplier of raw materials and begin making the product. Assume that it takes 60 days from the time of the order from a customer to complete the work. Further assume that your supplier, while a close relationship, only extends terms to 30 days. This means that you have to finance the payment to the supplier, or the payable, until you receive cash from the customer. If you do not offer terms and 100% of the balance is due at the time of completion, then you are only financing for 30 days. If you operation is on a commercial scale in that you manufacture custom furniture for office spaces or industrial facilities, you would probably offer some sort of terms to your customers. This will increase the time you need to finance the payables. If it is 30 days, then the CC = 30 + 60 - 30, or 60 days until you receive the cash. This means that working capital is "tied up" for 60 days, or said another way, you are financing the time period of 60 days until you receive the cash for your work.

In the prior example, a company might have enough cash on hand to finance the 60-day process, but if it doesn't, another form of financing is needed, like a bank line or other external financing form. This is not free and the interest charged over the process of the borrowing constitutes part of the total cost to a company for running its business. This is not an insignificant component and needs to be considered when modeling the business operations.

The CC is more often used for manufacturing entities, or what I would call old line businesses - the bricks and mortar operations. What if you have a service business? In this case, there is likely to be little to no inventory, so that component is no longer part of the equation, leaving DSO and DPO. In many instances, say a consulting business, you bill be the hour and provide an invoice on a monthly basis. There are likely to be terms, so you would have a DSO number that is greater than zero. As for DPO, there may not be much involved in that category. To keep it very simple, if there is a consulting firm with four employees working out of a small office, the major expenses you will have are the salaries of those employees. While there may be some payables for things like office equipment or supplies, those are not related to providing a service in the similar fashion as buying materials to make saleable inventory products for a manufacturing business. Therefore, when analyzing a services business, the DSO may be the only component of the CC. In this scenario, there is little value added in understanding the CC.

In summary, I think that understanding the CC for entities who need to invest in inventory and who are more likely to be concerned with managing payables and receivables is important. You have to understand how cash it tied up and the costs associated with managing working capital. While this concept is more relevant to some companies more than others, understanding the process from delivery of goods to receiving cash is a fundamental building block of financial modeling.

Developing a Basic Financial Model - Part IV: Working Capital Historical Relationships

In continuing in our series of fundamental concepts of financial modeling, I will now turn to the initial steps of understanding how to forecast financial information. It is important that the reader has some familiarity of the three major financial statements (income statement, balance sheet and cash flow statement) that I covered in the prior three articles. If not, please read those first prior to continuing.

Working Capital Historical Relationships

We often see in disclaimer language that "past performance is not indicative of future results" or other types of language similar to that to let the reader know not to put too much stock in the historical outcomes of a particular situation. This is commonly seen in relation to stock price performance or asset manager historical returns. In the case of forecasting financial information of specific companies, this is not necessarily true. In fact, in investment banking or private equity, historical relationships help to drive the expectations of future performance.

For example, let us assume that there is a company in a relatively mature stage of growth, meaning that the future growth is likely to be more along the lines of a bit above the inflation rate for the next several years. The last two years have showed that the days outstanding for accounts receivable ("DSO") was 32 (the days from the point of booking a sales turns to cash). In the absence of specific information related to the company, whether a macro event or industry overhaul, there is likely no reason to assume that there would be a significant change from that.

DSO is calculated by taking the average accounts receivable over a time period, like annual, and dividing the result by the total daily sales value for the most recent period. In other word, if annual, the denominator would be the sales figure divided by 365 and the numerator would be the average of the accounts receivable calculated for the current year and the prior year. If the total accounts receivables for the past two years was $50 and $65, and the total sales for the most recent period was $1,050, the DSO would be approximately 20, or on average, accounts receivable is outstanding 20 days before coming to the company as cash.

The same concept would apply to days payables outstanding ("DPO") or days inventory outstanding ("DIO" – although, a more common concept is inventory turns). DPO is calculated similarly to DSO, except the accounts payable becomes the numerator and the denominator is cost of sales divided by 365. You can easily see the similarities between the DSO and DPO because one is a method to track when cash comes in (DSO) and the other tracks when you have to pay (DPO). These are two components of the cash cycle (also known as the cash conversion cycle).

The cash cycle is a way companies can track how quickly cash comes into the company, and when an analyst is forecasting results for a company, the historical information is very important to understand. In simple terms, the cash cycle follows the purchase of raw materials (often building up payables) to creating a saleable product to the collection of receivables. The cash cycle is = days inventory + days receivable – days payable or DIO + DSO – DPO. The DIO is calculated similarly to DPO in using the cost of sales as the denominator with the average inventory as the numerator (the inventory turns is calculated by taking the total costs of sales as the numerator and the average inventory as the denominator and the result yields how often a company is "turning" its inventory, or selling its products).

To continue with our prior example of DSO = 20 days, if we assume that average inventories was $28, average accounts payable was $37 and the cost of sales in the most recent period was $750, then DIO = 14 and DPO = 18. This translates to a cash conversion cycle of 16 days (20 + 14 – 18). This means that a company is getting cash every 16 days from the current operations. What this really tells you is that the time period of 16 days is what a company must finance prior to getting cash. As a slight tangent, there are instances, although rare, where companies have been able to "self-finance," or not rely on a credit facility or other source of funding to finance its working capital. If you change the numbers above and have DSO or 11, DIO of 10 and DPO of 22, you can see that the cash cycle is approximately 0 (rounded to -1). This is an instance where an entity is developing inventory to sell faster and receiving cash from accounts receivable faster than that company is paying to its vendors. That is a great position, but it is not a common situation.

Other current assets include prepaid assets, which many financial analysts will see as a percentage of total sales. Other current liabilities include accrued expenses or other accrued liabilities, which financial analysts may view as a percentage of total cost of sales. For example, if prepaid expenses at the end of the year have been on average 3% of total sales, in the absence of specific information, any deviation from that going forward is probably not warranted. The similar logic holds for current liabilities.

I want to make two quick points before ending this section. The first is that for companies who are new and rapidly growing, it is acceptable to look at all working capital relationships as a percentage of total sales. In many instances, if you attempt to forecast receivables, inventory or payables using historical information for a company only in business for one or two years, you will get misleading data. To better understand this, think of a new company who will be building up inventory ahead of a marketing launch and who may be offering better receivables terms to purchasers in order to generate sales. Additionally, a new company is likely to have more stringent terms on payables so you will be double penalized from a cash cycle perspective. As a company goes from upstart to established entities, the various ratios will reach equilibrium and then using the DIO, DPO or DSO analyses is more prudent.

The second point is that some companies will include short-term borrowings or current portion of short-term debt in the current liabilities section. For working capital analyses, these should not be included. The only counter-argument to this is that is there is a specific working capital line that is associated with financing working capital (not just based on the balances but actually used to finance working capital) some would choose to include that in certain working capital calculations. In my experience in financial forecasting, I have separated that component of debt and I do not view it as a current liability but as part of a company's capital structure (i.e., long-term financing and not a current liability like payables or accrued expenses). But, as long as the methodology is clearly defined, the financial modeler can choose either path, as the results should not differ.

The next article will cover long-term assets and long-term liabilities and how a financial modeler can use some historical perspective to forecasting these items.

Developing a Basic Financial Model – Part III: The Cash Flow Statement

Excel is probably the most popular spreadsheet in use today, and certainly a mainstay of investment banks, private equity firms and hedge funds. It offers a tremendous amount of flexibility to develop a wide array of financial computations, ranging from simple, static calculations to complex, dynamic analyses. In order to effectively develop financial models for use in valuation analyses or forecasting, it is important to understand how companies show their information. This article continues the overview of the link between the basic components of a full financial spreadsheet by discussing the last of the three main components: the cash flow statement. Because these financial statements are based on accounting rules, there will be some accounting theory used in this article but only very high level, basic elements to allow the reader to follow along.

The Cash Flow Statement

The cash flow statement (or statement of cash flows) provides an accounting (in the literal and financial sense) of how a company generates cash. Since generally accepted accounting principals (also known as "GAAP") are based on accruing revenues and expenses, understanding how a company earned the cash recorded on its books at the end of a reporting period would be very difficult. The following numerical example will shed some light on this issue.

For the sake of simplicity, let us assume that the only components on a company's balance sheet at December 31, 2007 is cash of $100, accounts receivable of $200, accounts payable of $100, and equity of $200. At the end of December 31, 2008, the company shows accounts receivable of $350, accounts payable of $150 and equity remained $200. What would the cash balance be? First, you look at the change in accounts receivable, and if that balance increases, that is a use of cash (and vice versa for a decrease in the balance). So, given the information above, it is clear that there was a use of cash of $150, meaning that the cash from the balance from the year prior would be decreased by that amount. Why does this happen?

GAAP requires companies to record sales of products or services but the company will usually offer terms, say 30 days for the purchaser to pay for those products or services. During this period, a company does not have the cash from the sales and will not get the cash until the purchasers pay. During this period, the company is effectively lending money to the purchaser, or tying up the company cash. This is why some companies will get bank lines or other credit facilities to finance receivables so the cash in the business does not get used. In short, building receivables (or other assets, like inventories) uses cash.

A similar process occurs for payables, except in an opposite process. The accounts payable have increased by $50, so that increases the cash amount. Think of this as deferring a payment due today until some time in the future, and in keeping with the financing discussion above, a third party is providing financing for you, and thus, this becomes a source of cash. In this example, the $150 increase in accounts receivable offset against the $50 increase in accounts payable nets to a cash use of $100. With equity remaining the same, cash from the prior period would be reduced by $100. In short, cash balance would be zero at December 31, 2008.

The cash flow statement will include all changes in assets and liabilities, including the aforementioned receivables and payables. There will also be expenditures for building up the physical property of a company, changes in bank borrowings and changes in the shareholder equity account (like dividends paid or issuance of new stock). Just like its name, the cash flow statement provides a way to track how cash is generated for a business by "unwinding" the accrual methods of accounting. In conjunction with the income statement and balance sheet, the cash flow statement provides a way to analyze the operations of any company and show how business generate or lose cash.

This is the final part of the basic understanding of financial statements. It is now time to talk a bit more in detail about setting up these statements to do financial modeling. The next several articles will cover a series of steps to walk through building a financial forecast and how to use the historical data to provide guidance to projected information.