Excel Tips and Tricks

The INDIRECT Function For Microsoft Excel Revisited

2010-02-08T20:41:00.000-08:00

In a previous article, I discussed some of the limitations of the INDIRECT function in Microsoft Excel and the relative limited scope of use. I would like to revisit this function, however, as there may be some useful situations in which this function can work well. It will not make the function the most useful one, but there are ways to use it to accomplish some simple goals.

To remind my audience, the INDIRECT function is a method to interpret the result from the formula in a cell, which will be in the form of a cell reference, and return the value found in that particular cell. In other words, if your INDIRECT formula returns "C10", the formula goes to cell C10 and shows the value in that cell.

The form of the function is =INDIRECT(cell_reference). The "cell_reference" need to be in quotes if you are directly referring to a cell, as in =INDIRECT("B4"). This will result in displaying whatever value is in the cell B4. So, if 90 is the value in cell B4, 90 will be displayed. There is an interesting twist here to keep in mind, because if you want to return the reference of a reference, then you do not need the quotes. For example, assume that in the cell D4 you have as a value "E4" and in the cell E4 you have the value of 80. The formula =INDIRECT("D4") will return a value of E4, whereas the formula =INDIRECT(D4) will return a value of 90.

While this is not necessarily mindblowing on its own, you can also use the INDIRECT function for a more useful situation, such as returning values on other tabs. Take the example of a company with multiple regional branches that sell similar products. As the CFO or COO, one may want to track the sales per week or month by each branch of a variety of products and have a summary page for quickly determining trends. To keep this simple, let’s assume there are 25 branches and the management team wants to see annual summaries of sales. As long as each tab is setup in a similar fashion, with the names of the products and the resulting sales in the same rows and columns, the INDIRECT function can allow the financial modeler to do some quick copying and pasting to create a summary.

The general formula for finding something on another sheet is =INDIRECT(Sheetx!RC) (as long as the tab has no spaces, no single quotes are necessary, otherwise the formula is =INDIRECT(‘Sheet x’!RC)). Assume that the model is constructed with the branch names across the top in one row and the years are listed in the one column. If the tabs in the master model are named after the respective branch (i.e, West1, West2, Midwest, South1, South2, MidAtlantic, etc.), then the general formula for obtaining the information is =INDIRECT(R1C1&"!R2C2"). More specifically, if the summary page has headers in row 4 beginning in column J, and each of the tabs has the data in cell E4 (on each sheet) that is required, you would have the formula =INDIRECT(J$4&"!E4") and copy across each column (for each branch).

There are some additional ways to use the INDIRECT function, but as was the case in the original article, there are specific instances under which it can be used. For the most part, the average Excel modeler will not need to use this function, but for those with intermediate to advanced knowledge of Excel, I will concede that there actually are a few instances in which this function can be useful.

As a final note, if one needs to incorporate R1C1-style references, the formula is =INDIRECT(cell_reference, false). The second part omitted or true is the normal A1-style reference. In addition, Microsoft Excel 2007 ignores some previous returns that would yield a #REF!, like external references to workbooks not open. The most current version of Excel will yield an answer, so just be careful to note that there are some subtleties not captured in earlier versions of Excel.

More on the IRR Function in Excel

2009-11-24T12:10:00.000-08:00

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

2009-08-05T12:26:00.000-07:00

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

2009-07-03T13:02:00.001-07:00

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

2009-05-13T08:09:00.000-07:00

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

2009-03-20T10:59:00.000-07:00

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

2009-02-05T06:41:00.001-08:00

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

2009-01-27T12:57:00.000-08:00

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

2009-01-20T13:42:00.001-08:00

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

2009-01-16T07:16:00.001-08:00

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

2009-01-09T08:43:00.000-08:00

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

2009-01-01T15:36:00.000-08:00

Developing a Basic Financial Model – Part II: The Balance Sheet

2008-12-15T08:27:00.000-08:00

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 balance sheet and its related components. 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 Balance Sheet

The balance sheet provides a snapshot at a particular moment in time of a company's assets, liabilities and equity. For public company's trading on an exchange in the United States, the Securities and Exchange Commission requires data to be filed on a quarterly basis. Other global exchanges require semiannual filings for public companies. Most businesses worldwide would have an accounting system to track balance sheet information on a much more regular basis (likely daily) for monthly reporting purposes.

A balance sheet is an indication of an entity's health and the simple accounting relationship is:

Assets = Liabilities + Equity.

Given the formula, assets are often referred to as being on the left side of the balance sheet with liabilities and equity representing the left side (one reason for this may be the fact that when a full balance sheet is presented on one page, the assets are first, and thus, on the left side of the page).

Assets. An asset is something of value to the company and comprises items that will, in theory, provide cash to the business. Typical assets listed on a balance sheet include cash and short-term investments, accounts receivables, inventories, prepaid expenses, and property, plant and equipment ("P,P&E"). If you are a manufacturing business, you likely use your P,P&E to make products to sell, which means that buy raw materials from suppliers and buildup your inventory balance, and one a product is finalized and sold, you have an increase in accounts receivable. Once you collect on the accounts receivable (the customer pays you for the product you made), you have an increase in cash. While other types of businesses may have more or less of specific assets, the fundamental flow described above can be applied across many industries. Items like cash and accounts receivable reside in a category called current assets, whereas P,P&E is considered a long-term asset.

Liabilities. A liability is something that a company incurs as a means of operating the basic business. Typical liabilities listed include accounts payable, accrued expenses, taxes payable, current portion of long-term debt, long-term debt and other long-term liabilities. To continue with the prior manufacturing example, when that company purchases raw materials from suppliers, many times the business will get terms, or a payment plan for such raw materials. For example, it is common to allow a purchaser 30 days by which to pay for materials purchased. A supplier may incentivize the buyer by offering a discount to the total purchase if paid within 10 days, and expects full payment by the 30th day from the purchase. These purchases show up on the manufacturer's books as accounts payable or obligations to a supplier or suppliers. Items including taxes payable and accounts payable reside in a category call current liabilities, with other liabilities being considered long-term. A major long-term liability is debt. Now, debt can be long-term or short-term (the current portion of long-term debt is contained in current liabilities), with short-term debt often a credit line from a bank or bridge financing from a third party institution. Many companies, including cable, telecom, auto manufacturing and retail, finance their long-term plans through the issuance of debt. When companies do this, the debt appears on the left side of the balance sheet.

Equity. The equity account (also called shareholders' equity, stockholders' equity or members' equity) is the numerical difference between assets and liabilities when everything is properly accounted. The equity account changes when a company generates net income, pays a dividend or raises capital. The equity account is important because other individuals or companies who are looking to buy businesses often look to the equity account as a sign of the overall health of a company and its historical performance. For example, the retained earnings ("RE") category is a component of the equity account and is a culmination of the historical net income and dividend payments. If you are analyzing a business and you see that RE is $10,000,000 and last year's net income was $1,000,000, you might infer that this business has been steadily profitable for the past 10 years ($10,000,000/$1,000,000). If, on the other hand, the prior year's net income was $10,000,000, there is either a case of a newly started business or some uncertainly about year-over-year performance, and this might be a warning flag. In the most obvious case, if the prior year's net income was a loss of $20,000,000, in the absence of a turnaround in the business, next year's retained earnings may go negative, indicating a deficit. This deficit indicates that the book value of the liabilities is greater than the book value of the assets, and that is indeed a warning sign. In this instance, a company would like have to raise additional outside equity, which would increase the overall equity account and possibly offset the deficit of the RE account.

In summary, this section attempted to outline some very basic items related to a balance sheet to setup the next article with will cover the cash flow statement. It is the cash flow statement that tracks the changes in balance sheet items and ties together the income statement and balance sheet. In addition, there are theories that can be more easily explained when the concepts of how the three statements work together are established. For now, just understand that the accounting rules for balance sheet information is designed to gauge the health and viability of a business and shed light on the ability to generate profits in the future.

Developing a Basic Financial Model – Part I: The Income Statement

2008-11-06T12:59:00.000-08:00

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 is an overview of the link between the basic components of a full financial spreadsheet: income statement, balance sheet and cash flow. 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 Income Statement

The income statement includes items that give an indication of how much a company sells (called revenue, sales or net sales) and what it costs to run the business (cost of sales, operating expenses, taxes). If you took sales and subtracted all of the expenses, what is left is the net income of a company. An example income statement will look like the following:

Net Sales
- Cost of Sales
= Gross Profit

- Operating Expenses
= Operating Profit

- Interest Expense
+ Interest Income
+/- Other Expense/Income
= Pretax Income

- Income Taxes
= Net Income

Net sales represents what a company has sold, whether a physical product (box, toy, car, etc.) or a service. The cost of sales represents what expenses a company incurred to provide the physical product or service. For example, if a company sold $100 worth of shoes and the machine usage to make those shoes was $40, than $40 is your cost of sales, and thus, the gross profit is $60. In general, a manufacturing business has costs related to the running of machinery to make a product plus a depreciation value associated with the economic wear and tear, which is usually included in cost of sales.

Operating expenses relate to items including the administrative parts of running a business, including payroll, rent or lease payments, advertising and marketing, depreciation and amortization on office fixtures and other general and administrative items. This category is usually reserved for the non-activity aspects of business. Some companies, like pharmaceutical or electronics entities, will also have research and development expenses, and these are normally listed under this section of the income statement. After summing all of these expenses and subtracting from gross profit, you are left with operating income (or operating loss).

Operating income is an important figure in business because that is what a company generates before any financing decisions are taken into account. This number is also referred to as EBIT (earnings before interest and taxes), and when you add back all depreciation, amortization and other "non-cash" charges, you arrive at EBITDA (earnings before interest, taxes, depreciation and amortization). EBITDA is and important calculation in the financial analysis world because it represents the cash income earned for running a business. This is the figure used in many industries to determine valuation, as well as how much debt a company can handle.

After operating income, you will include items involved in the financing of a company, like interest expense for a company who has debt. In addition, you would include interest earned from excess cash balances or short-term and long-term investments. If the company is a financial institution, this interest expense and interest income lines are actually part of revenue and cost of revenue, so you would see that information near the top, but for most other industries, it constitutes a place below operating income. Finally, if there are any other non-operating sources of income, like gains on sales of assets, it would be included in this area, too. The sum of all of these entries is normally called "Other Expenses, Net."

Once the other expenses are subtracted from operating income, a company has pretax income, or income before income tax provision. This is the accounting figure used to determine how much tax an entity is required to pay to the government. Normally, companies pay around a 35% federal income tax, and they may have to pay a state and local tax amount based on specific tax rules. There are also other items that impact how much tax must be paid, including any operating losses from a prior year, tax credits used to offset taxable income, and certain accounting methods that can increase or decrease the amounts owed during a particular time period. Once the total tax is determined, the net income can then be calculated.

Without getting too much into accounting methods and tax law, the net income number is merely the result of accounting methodologies and does not necessarily reflect the cash generation of the firm. As stated earlier, EBITDA is a better metric of a company's ability to generate cash. As will be discussed later, there are certain items to be deducted from EBITDA to determine true free cash flow, but for now, just note that cash generation and accounting regulations create a different set of results. It is important to keep that in mind when you develop your financial models because understanding the components of the income statement is the first step in putting it all together.

Time Value Formulas in Microsoft Excel – Part II

2008-10-03T14:49:00.000-07:00

In the first article, I discussed the PV and FV functions in Excel for calculating the time value of money. In this article, I will discuss the NPV and IRR functions in Excel. In the real world, these two formulas are critical in capital budgeting alternatives, as the results determined by NPV and IRR dictate whether or not to commence projects.

NPV and IRR are inexorably linked, as you will find out, but in order to provide the proper reference, some background of the financial theory is appropriate.

NPV is net present value and it is used to calculate what the present value of a stream of future cash flows is based on a specified discount rate. The discount rate is a measure of risk in the cash flows. As a business, you would use the weighted average cost of capital ("WACC", see other article on weighted averages) as the discount rate, as the WACC is the cost of doing business. If you are an investor, you are likely to use as a discount rate your returns threshold over the period of this project. A simple way to think of discount rate is to consider it the minimal acceptable return for doing a deal.

The form of the NPV function is =NPV(rate,values), where rate is the discount rate and values are either nonadjacent specific numbers (A1,C1,E5,G7) or a set of values in rows or columns that are adjacent (A1:N1). As an example, if you make an investment at December 31, 2008 of $50,000, and you are going to receive $9,000 at the end of each year for the next ten years, the NPV of that stream of cash flows would be $4,819 using a 10.0% discount rate. If your minimal acceptable return is 12.0%, the NPV is $761 and if the minimum acceptable return is 15.0%, the NPV is -$4,201.

For positive NPV values, you would accept the project because it is adding value. In the instance of a negative NPV, you would not do the transaction. In general, you DO NOT accept projects with negative NPVs as that is an indication of value erosion (the return on the project is less than your required minimum so you are subtracting value over the time period of the project).

IRR is the internal rate of return and is used to calculate the compounded rate of return over a series of cash flows. In finance, the IRR assumes that each of your cash flows is reinvested at the calculated IRR. In other words, if your IRR result is 4.0%, each of your cash flows is assumed to have been reinvested at that rate to get the proper IRR. A more important feature of the IRR is its relationships with NPV: the IRR of a project is the rate at which the NPV equals zero. Thus, you should begin to see how these two functions are interrelated.

The form of this function is =IRR(values). In this case, the values must be adjacent or you will get an error message. Sticking with our prior example with the $50,000 investment and $9,000 cash payments, the IRR on that stream of cash flow is 12.4%. Based on what was stated earlier, you should have begun to suspect that the IRR was close to 12.0% because the NPV at a 12.0% discount rate is $761, or closer to zero than a 10.0% discount rate. In short, the IRR provides the bottom return at which you would be able to accept any project.

The problem with the IRR function and IRR in general, is that there can only be one change in sign of the cash flows. This is commonly achieved by an investment (negative sign) with payments returning to the investor (positive sign). If there are two changes in the signs of cash flows, you may get an incorrect IRR as there can be more than one. Additionally, the IRR formula assumes that cash flows come in at the same time over each period (end of each year, end of each month, etc.). If this is not the case, you will have to use the XIRR function, which considers different times for payments over the project period. Finally, as was mentioned earlier, the IRR assumes that the cash flows are reinvested at the IRR. If you know that reinvestment rates will be different over the time horizon of the project, you should use the MIRR function, which allows for you to input a specific reinvestment rate.

As a final thought, NPV and IRR are good tools for determining whether or not to pursue a project, but what if you are evaluating two mutually exclusive projects (you can only choose one) and you get conflicting answers? This can happen when Project A has a higher IRR and lower NPV than Project B. If you can only choose one project, you will always choose the project with the higher NPV, and in this case that is Project B. Why is this? The fundamental premise in finance is maximization of value. A higher NPV means that more value is being created for the shareholders and that is the project you want to choose.

2008-09-26T14:47:00.001-07:00

Excel offers a few simple formulas for determining the value of projects over time. In this article, I will cover PV and FV. For most financial applications, some subset of the formulas listed above will be more than enough to determine the value or rate of return of a particular project.

The PV and FV formulas are very similar in construct, but give the opposite answers. PV, which is present value, will tell you what the value of a string of future payments is today. Conversely, FV, which is future value, tells you what the value of your current holdings will be in the future.

As a brief primer, the time value of money is an important concept in finance. The simplest way to explain this concept is to understand that if you had $1 dollar today, it is worth more then $1 in a year for any interest rate greater than 0%. Let us assume that the current annual interest rate is 3%. If you invested the $1 you have today, it would be worth $1.03 at the end of the year (annual payment, no continuous compounding). Clearly, that is more than merely $1.00. Now, if you were to take this example to the next level and assume that the interest rate of 3% holds for five periods, the future value is now $1.16. Notice in this simple example, the future value is $1.16 and not $1.15, indicating the power of compounding. For an example that has a much larger difference, consider $10,000 today with an annual interest rate over 10 years of 8%. The future value is $22,589.25 versus the non-compounded total of $18,000.00 – a difference of $3,589.25. This is a bit of a digression and will be explained in further articles, but the summary of all of this is that the time value of money is a fundamental concept in financial theory and practice, and Excel makes it easy to calculate.

The formula for PV is =PV(rate,nper,pmt,[fv],[type]), where rate is the interest rate per period, nper is the total number of period in the calculation, pmt is the periodic payment, if any, future value of the project (optional) and type of payment stream (optional). For type of payment stream, use 1 for beginning of period payments and 0 or blank for end of period payments. In virtually all financial applications I have done, I have dealt with end of period payments, so the "type" is left blank. The reason the "fv" is optional is that for a project producing periodic payments, the fv may be zero and the calculation is just for determining the present value of periodic payments over some time period. For example, if there is an annual interest rate of 6%, periodic payments of $10.00 over 30 periods, the present value is $137.65. When there is no specific future value identified, fv = 0.

Similar to PV, the formula for FV is =FV(rate,nper,pmt,[pv],[type]). The same explanations hold for FV as in PV, and PV in this formula is optional. For example, in the case where there is no identifiable present value, pv is equal to zero. Assume that the pv equals zero, the annual interest rate is 6%, there are 30 periods and the payment is $10.00 per period. If you plug this into an Excel formula, you will get a future value equal to ($790.58). Why would you have a negative future value? To understand the answer to this question, it is important to consider what you are trying to determine. Present value calculations return negative values (under normal circumstances) because that is the "cost" of achieving a stated future value. The cost, in this case, is really the investment needed to generate the future value calculated using the formulas. If you think back to the first example, investing $1.00 is what it takes to get to $1.03 one year later. You do not have access to that dollar once it is invested, so from your perspective, it is a cash outflow, or a negative cash event to you. Coming back to future value, when you have positive payments, the formula interprets that as you paying out that amount, not receiving, over the defined period of years (this should be reasonably intuitive, since you must enter a present value, if known, as a negative number for purposes of calculating future value). When you input ($10.00) for the payment, the future value is correctly calculated as $790.58.

In summary, the PV and FV formulas save time and effort when using spreadsheets that require many calculations for present and future values. In the next article, I will discuss NPV and IRR, two formulas critical in evaluating projects. For now, just remember that cash in your hands today trumps the same amount of cash in the future because you can take the money now and invest.

IS Formulas in Microsoft Excel – Part II

2008-09-10T15:08:00.000-07:00

This is continuation of a prior article covering several similar formulas within the Information category that allows a modeler to extract TRUE or FALSE information, based on the design of the model. This article covers the functions ISEVEN, ISODD, ISLOGICAL and ISBLANK. These formulas, too, can play a big role in simple financial models and I think that many individuals who are normally familiar with Excel do not know how to incorporate them in a way that may make the modeling process easier.

ISEVEN, ISODD

These are simple functions to determine whether or not a reference is an even number odd number. The form of the functions are =ISEVEN(value) or =ISODD(value) and the formulas return a TRUE statement if the value is even for ISEVEN or odd for ISODD. These are the types of formulas that could be used for storing products in a warehouse (like addresses, even on one side and odd on the other) or developing a spreadsheet to determine prime numbers (after number 2, since it is the only even prime number).

ISLOGICAL

This formula merely checks to see if the reference is TRUE or FALSE, and its form is =ISLOGICAL(value). At this point in the article, you have seen how these IS statements work, and this one is straightforward. If you are interested in taking a total tally of all of the TRUE or FALSE statements, you can use this formulas for each of the individual cells and then use =COUNTIF(Range_of_Data,FALSE) to count all of the FALSE references. That is one example of a scenario in which you might actually utilize in your own model.

ISBLANK

This formula checks the reference cell to see if there is any data in it, and its form is =ISBLANK(value). This is a formula that I have used many times and while a bit clunky on more complex spreadsheets, its simplicity makes it easy to incorporate. For example, when you are modeling anything with a waterfall structure, this formula comes in handy. You can create a series of formulas that will only kick in after a specific circumstance is reached. So, instead of having to manually adjust formulas across rows or columns, incorporating the ISBLANK language allows you to copy and past across the time horizon of your model. Let us say that you have a model where an investor has invested $10 in a company, and you want to calculate the return based on a specific sale date. Further, if a sale does not occur at the end of a period, some interim cash flow to an investor is paid. So, if the sale date is the variable, you may have a formula that looks to see if there was any activity in the prior cell to determine if any action should take place. Using the $10 investment, assume that you have years across the top and years in the column on the left. If the years across the top represent when cash flow will take place, the years on the left indicate when the sale of the investment occurs. Of course, of the year in the left column is greater then a year in the row (across the top), there would be no activity. This would be an example of when to use ISBLANK.

(As an aside, a waterfall is something that has a payoff structure that resembles water falling over a cliff. You would see this if you need to determine the returns to an investor over a period of years. If there was a sale of an investment in the first year, the investor might be getting only one payment – the initial sale. If the sale of the investment occurred in year 5, there may be smaller annual payments with a final larger payment at the end of year 5, thus the payoff stream would look like a waterfall when you model it. This will be explained in better detail in the financial articles to come.)

In summary, these simplistic formulas may not seem like terrific methods by which to establish your financial model, but a subset, particularly the ISBLANK function, can be very helpful in making a more complex situation easier to manage.

IS Formulas in Microsoft Excel – Part I

2008-09-10T15:07:00.000-07:00

Excel offers several similar formulas within the Information category that allows a modeler to extract TRUE or FALSE information, based on the design of the model. This article will briefly describe the various functions, including: ISERR, ISERROR, ISNA, ISNONTEXT, ISNUMBER, ISREF, and ISTEXT. These formulas can play a big role in simple financial models and I think that many individuals who are normally familiar with Excel do not know how to incorporate them in a way that may make the modeling process easier.

ISERR, ISERROR

These two formulas will check to see if the reference is an answer or an error. The form for each is simply =ISERR(value) or =ISERROR(value) (Note that in each of these the form includes a "value" component, which should be thought of as the cell reference you are targeting). The only difference between these two formulas is that ISERROR can include the error of #N/A, and if the reference is #N/A, the result of TRUE is returned. For ISERR, the value of FALSE is returned for #N/A because it does not recognize that particular error. For that reason, I use the ISERROR only. This formula can come in handy when evaluating a formula designed to lookup data in an area (like VLOOKUP or HLOOKUP). It can act as the MATCH formula in this capacity, treating an error as a non-match and thereby allowing the modeler to have a means by which to check if the required data is actually contained in the data range. For example, if you have a list of inventory products and you need to know the amount sold on a specific day, you could do the following to get the exact amount or return an error message:

=IF(ISERROR(VLOOKUP(Product,Range,Number,False)),"No Data", VLOOKUP(Product,Range,Number,False)).

ISNONTEXT, ISTEXT, ISNUMBER

These functions are fairly straightforward, with one determining if a reference is text, number or not text at all. The short of this section is that ISNONTEXT and ISTEXT are opposite, not surprisingly, and ISNONTEXT and ISNUMBER are the same EXCEPT FOR a blank cell, which ISNONTEXT states TRUE and ISNUMBER states FALSE. There is no real mystery in these formulas, whose form takes the place of =ISNONTEXT(value), =ISTEXT(value) or =ISNUMBER(value). You could use these to check the names of employees in a long list, addresses in a database or some combination that requires confirmation of text status.

ISNA, ISREF

These two formulas are designed to specifically determine is there is an #N/A or #REF! in a reference cell. The form is either =ISNA(value) or =ISREF(value). I have not found a good time to use either of these formulas, and you may never find a good time either. Having said that, if you have a long list of data and want to do a quick copy and paste of a simple formula to find the bad data, one of these two Excel formulas could do the trick. Let us say that you have 4,000 entries of products sold last year and you need to know how much was sold each day of the week. If you were checking to see if any results were #N/A (indicating no sales on that particular day of the week), you could use =ISNA(Range_of_Friday_Sales) for each cell next to the product to see if it is truly #N/A. Frankly, that is cumbersome and I doubt you would ever use something like that, but Excel has made the formula available to use, so if you are in a bind, use it. The same would apply for use with ISREF, but instead of finding #N/A, you would be searching for bad formulas. Let us say that you have a model with a lot of data in a row and you begin to delete some of those rows. Of each row was dependent upon the next to provide the right answer, you will end up generating a #REF! That is when you can use ISREF, but once again, I cannot see a real life scenario where the ISREF will trump another formula you create in your financial model.

Additional Information formulas are covered in the next article entitled "IS Formulas in Microsoft Excel – Part II."

Weighted Average Calculations in Microsoft Excel

2008-09-02T15:25:00.000-07:00

There are many analyses you will undertake that will require the need to calculate a weighted average instead of a simple average, and there are a few different methods by which to do so. This article will first define the concept and then illustrate the methods used to calculate the correct answer. Once you understand the shortcut that Excel provides in determining the result, you will likely not return to the longer, more cumbersome approach.

First, what is a weighted average ("WAVG")? The WAVG uses a parameter, like market capitalization or shares traded, to modify a simple average calculation. As an example, let's assume that you have five data points that are stock prices: $3.00, $4.00, $4.50, $5.00 and $5.50. If these are the closing prices over the last five trading days, the average price over this period would be $4.40. But, if we assume that these prices were intraday prices, we could say that the average price for the day was $4.40. This is misleading, however, as it does not consider the volume of shares at each trade. The volume at the time of each trade provides a clearer picture of what the market thinks of valuation. The calculation used to weight these trades is the volume weighted average price ("VWAP") and is a common practice in private capital transactions involving equity or equity-linked securities, like convertible debt (note that VWAP and WAVG are calculated in the same fashion, and I use VWAP to illustrate a real life application).

In keeping with the prior example, let us assume that we have volume information associated with the prices as follows: 1,000 shares, 1,500 shares, 1,000 shares, 10,000 shares and 500 shares. You can see from the data that many more shares traded at $5.00 – 71.4% of the total daily volume to be exact. How does one incorporate that information to determine the VWAP? There are two approaches for doing the calculation: the step-by-step method or the SUMPRODUCT method.

Assume that in the first column of your spreadsheet contains the stock prices and the next column contains the shares traded at each price. The step-by-step approach would dictate that you create two new columns: weighting and contribution. The weighting column would contain the result of that day's shares divided by the total shares traded for the day for each of the data points. For example, the weight for the $4.00 price would be 10.7% (1,500 divided by the total shares traded that day of 14,000 shares). Once you have the weightings completed, you can do the contribution column, which would be the weighting value multiplied by the actual stock price. In the $4.00 example, you would take the 10.7% and multiply by $4.00, yielding $0.43 – the contribution to the total VWAP. Summing the contribution column yields the VWAP, which, in this case, is $4.73, higher than the simple average of $4.40. The result would indicate that the actual value of this stock is closer to $4.73 than $4.40.

To avoid the need for two additional columns, Excel provides the SUMPRODUCT function. The form of this function is =SUMPRODUCT(array1,[array2],[array3]….). This formula takes an array (row or column) and multiplies it by one or more other arrays of the same size. To calculate the VWAP from our prior example, all you would need is =SUMPRODUCT(prices,volume)/total volume. This would result in the same answer as above, $4.73. The answer is calculated without the additional columns. When you look at the formula and break it down, you can see that it takes the form of (A x B)/C, which can be rewritten as A x (B/C). The B/C component is the same as the "weighting" column described in the step-by-step approach, with A being the stock price used in determining the "contribution" column in the former method.

Finally, you can also utilize an array method to calculate the VWAP using the form of ={SUM((prices)*(volume))/total volume}, which is entered using ctrl+shift+enter. You can clearly se that 1) this is not much of a time saver and 2) SUMPRODUCT is similar to this form. I only show this to illustrate that there are several ways to accomplish the WAVG calculation, and depending upon space, modeling skill or other personal factors, you can use whichever works for you. I recommend minimization of superfluous data and err towards using the functionality contained in the Excel formulas, and would use SUMPRODUCT over the lengthier method.

MATCH Function in Microsoft Excel

2008-08-17T16:46:00.000-07:00

Excel offers a function for finding specific data in column or row called MATCH. This function will allow you to find the location within an array, versus the LOOKUP functions that provide information related to the data you are seeking but in another column or row (you can use the LOOKUP function to return values in the same column or row, but the MATCH function shortens it a bit).

The format of the MATCH function is:

=MATCH(lookup_value,lookup_array,match_type).

The lookup_value is the item you want to find within the lookup_array, like finding "apple" in a range of fruits or "green" in a list of colors. The match_type can be 1, 0 or 1. If the match_type is left blank, then it is assumed to equal 1. By using 1, MATCH will return the exact value or the value that is the largest value less than the specific item for which you are searching. In this case, the array must be in ascending order. Conversely, using -1 will provide he value that is immediately larger than the item for which you are searching, if no exact value exists. If you use -1, the array must be in descending order. If the match_type is 0, only an exact match is accepted, and if no exact match is available, #N/A! is returned. With match_type set to 0, the array can be in any order.

The order is important, because the 1 or -1 is dependent on how the data is presented. If, for example, you have a range of data in a non-sequential order, you will get the wrong answer. Let's assume that the array contains the following data:

1, 4, 7, 2, 21, 14, 5, 32, 6.

If you enter the formula =MATCH(6,{1, 4, 7, 2, 21, 14, 5, 32, 6}), you will get the answer 2. Clearly there is a number 6 at the end of the array, but because it is not an exact search and the data is not in order, the MATCH formula produces an answer you do not want. If you modify the formula to read =MATCH(6,{1, 4, 7, 2, 21, 14, 5, 32, 6},0), the answer will be 9, which is the correct location. So remember that for a NON-exact search, the order is imperative.

Once I learned to better incorporate the lookup functions, the MATCH function lost its relevance for many applications. In addition, when counting and summing, the array multiplication formulas work much better, and I will be discussing that in later articles. The point is that MATCH is good for a very limited set of basic inquiries, but ultimately, it is likely easily replaced by LOOKUP or other means. For now, it is a decent way to familiarize with the methodology Excel uses.

Finally, another formula used to determine the exact match that the MATCH formula uses, is, not surprisingly, EXACT. The format for this formula is = EXACT(text1,text2). This formula is not case sensitive, and straightforward in construct. This is another formula that is not entirely useful in most instances, because the double equal sign (= =) accomplishes the same result. For example, =EXACT(A1,B1) is the same as the formula =A1= = B1. Thus, the latter is as easy to use as the actual EXACT formula. The only rationale for mentioning this formula is to remind you that there are a myriad of formulas Excel provides, but some that may never be used or are easily replaced by better, more flexible formulas. The more experience you have in financial modeling, the more easily you will understand which formulas are the appropriate to use under the specific circumstances of your model.

SEARCH and FIND Functions in Microsoft Excel

2008-08-10T14:12:00.000-07:00

There are two very similar functions in Excel to look for data inside of cells matching parameters that you dictate: SEARCH and FIND. There are so similar, in fact, that one wonders why have two separate functions that perform virtually the identical results and are identical in the construct of the formula. This article will discuss he one, basic difference.

SEARCH Introduction

The SEARCH function is a way to find a character or string within another cell, and it will return the value associated with the starting place. In other words, if you are trying to figure out where a character is within the cell that contains a word, sentence or other type of information, you could use the SEARCH function. The format for this function is:

=SEARCH("find_text","within_text",start_num).

If, for example, the word "alphabet" was in cell C2, and your model needed the location of the letter "a" in that cell, you would use the formula =SEARCH("a",C2,1), and the result would be 1. To continue this simplistic example, if you were seeking the location of "b" in the word, the formula would be =SEARCH("b",C2,1), and the result would be 6. You can also use search on strings of characters. If, for example, cell F2 contains 1023-#555-A123, the formula =SEARCH("A12",F2,1) would yield the 11 as an answer.

FIND Introduction

The FIND function is another way to find a character or string within another cell, and it will return the value associated with the starting place, just like the SEARCH function. The format for this function is:

=FIND("find_text","within_text",start_num).

Using the same example as before, the location of the letter "a" in cell C2 would be discovered using =FIND("a",C2,1), and the result would be 1. Looking for "b" in cell C2 would be accomplished be =FIND("b",C2,1), resulting in the number 6. Finally, continuing on the similarity path, if cell F2 contains 1023-#555-A123 (as before), the formula =FIND("A12",F2,1) would yield the 11 as an answer. As you can see, up to this point, both methods would give you the same results.

Note: You probably quickly recognized that there are two a's in the word located in cell C2. By stating the starting point in each of the formulas as 1, we will pick up the first instance of the letter "a". If we needed to choose the next instance, we could merely have the "start_num" part of the formula to be 2, thus skipping the first instance of the letter and resulting in an answer of 5.

Main Differences

The main difference between the SEARCH function and the FIND function is that FIND is case sensitive and SEARCH is not. Thus, if you used the formula =SEARCH("A",C2,1) (note the capital "A"), the result would still be 1, as in the case before. If you were to use the formula =FIND("A",C2,1), you would get #VALUE!. FIND is case sensitive and there is no "A" in the word "alphabet".

Another difference is that SEARCH allows for the use of wildcards whereas FIND does not. In this context, a question mark will look for an exact phrase or series of characters in a cell, and an asterisk will look for the beginning of the series of characters right before the asterisk. For example, the formula =SEARCH("a?p",C2,1) in our alphabet example would yield an answer of 1, as it is looking for an exact grouping of the letter "a" with anything next to it with a "p" immediately after. As this is in the beginning of the word, the value returned is 1. Continuing with the alphabet example, the formula =SEARCH("h*t",C2,1) would yield a value of 4. In this instance, the wildcard "*" can represent any number of characters in between the "h" and the "t" as long as there is a string beginning and ending with the two letters you use in the formula. If the formula was =SEARCH("h*q",C2,1), you would get #VALUE!.

In short, these two formulas are very similar, and unless you need confirmation of an exact character or string of characters, you would likely err on the side of using SEARCH. Instances where this may not be the case might involve searches involving specific SKUs or names of employees. In my experience, SEARCH has been more helpful in specific financial modeling exercises, but it is helpful to understand the differences in usage and results as you work through your own modeling projects.

COUNTIF and SUMIF Formulas in Microsoft Excel

2008-07-31T09:05:00.000-07:00

Excel offers two simple built-in formulas to tackle counting and summing in rows or columns based on criteria that you establish. The formulas discussed in this article are COUNTIF and SUMIF.

COUNTIF

This formula will analyze data in a range consisting of single row or column, or adjoining rows or columns. For example, you could analyze data in the column defined by A1:A20, or you could be looking at A1:B20. Either range is acceptable to use for the COUNTIF function. The form of the formula is:

=COUNTIF(Range,Criteria).

Let us say, for example, that you are interested in determining the total number of sales in a region, with regions being defined as North, South, East and West, and you have daily sales for each region for a five-day period.

The first step might be to determine how many sales over the five-day period exceeded 30 units per day. Further assume that our region names are in the range C6:C9 with corresponding unit data in D6:H9. The formula to find unit sales over 30 would be =COUNTIF(D6:D9,">30"). This will give you the correct answer. Note that in this function, the criteria portion needs to be in quotes. You could also have that linked to a cell, such that the formula could be =COUNTIF(D6:D9,A6), where the cell A6 would have =">30" in it. Sometime, linking to another cell provides more flexibility to a financial model, especially is you are doing a quick sensitivity analysis.

The COUNTIF function has some flexibility to count based on wildcards, like creating criteria that is "*py" to find all words that end in "py" or containing "py" would be "*py*" and so on. There are advanced criteria that will be covered in my blog, but for now, we want to keep it simple.

The major drawback to the COUNTIF function is that is can only take a single criterion to analyze. So, if you wanted to determine all of the unit sales over 30 AND within the North region, you would have to do a formula like the following:

=SUM(IF(C6:C9="North",(IF(D6:H9>30,1,0),0)).

In this case, you would have to use Ctrl+Shift+Enter to get the right answer (this is array formula entry, which is more advanced than this article intended, but is necessary for your understanding). This formula will tell you how many days in the five-day analysis in which unit sales exceeded 30 in the North region.

SUMIF

The SUMIF formula is similar in its constraints to the COUNTIF formula, only being able to assess one particular criterion. The form of this function is:
=SUMIF(Range,Criteria,Sum_Range).

Let us assume that you need to know the total of all unit sales in the five-day period for days in which the unit sales exceeded 30. The formula would be:

=SUMIF(D6:H6,">30",D6:H6).

This literally breaks down into looking at the range defined by D6:H6, finding all values greater than 30, and then summing those values within the range of D6:H6. This seemingly simple example is only meant to convey the context by which you would use this formula. A more likely scenario could involve a long list of salesforce member names, defined by the range of A1:A200, with the results of last year's sales by product in B1:B200. If you wanted to know the total sales of all products by name, the formula would be:

=SUMIF(A1:A200,"Name of Key Employee",B1:B200).

This is the more likely use of the SUMIF formula. Once again, it bears mentioning that the shortcomings of this formula minimize its overall usefulness. In simple database settings, it will work fine, but if you have a large database of information such as sales, profits, units sold and inventory, all by salespersons, you are likely to want to better understand the array formula entry as it will be more flexible and easier to manipulate to provide a variety of the desired results.

LOOKUP Function in Microsoft Excel

2008-07-21T14:59:00.000-07:00

In contrast to the VLOOKUP and HLOOKUP functions in Excel, the LOOKUP function has two forms: vector and array. The following is a brief description of the differences in the two approaches.

Vector Form

Under the vector method, the formula you create will look for the cell you target, within a range of information (a single row or column) with the result you seek in some other range of information (another single row or column of same size as the first). Let us say hypothetically that you have a list of fruits whose sales prices vary by month, and you are curious as to the price of bananas in the month or March. We will assume that your list of fruit names is in column A beginning in row 3 with data through August (column I).

Thus, the entire range of data is within A3:I9. We would like to get the data for March, which, in this case is in column D. This means that the LOOKUP formula would be:

=LOOKUP("Banana",A3:A9,D3:D9), to yield the price per pound of bananas in March.

This would tell Excel to find the word "Banana" in the range of fruit, and whatever placement number within the column results, that same placement number will be used in the range for March to return the desired result. Similarly, if you wanted to find lychee values for August, the formula would be:

=LOOKUP("Lychee",A3:A9,I3:I9), to yield the price per pound of lychee in August.

To increase the efficiency of using this method, particularly if you have a lot of columns representing data for multiple years, is naming the columns. For example, if you name A3:A9 "Fruit_Range" and I3:I9 "August_Data" then the formula for the lychee example we just reviewed becomes:

=LOOKUP("Lychee",Fruit_Range,August_Data)

or

=LOOKUP(A8,Fruit_Range,August_Data), where the cell A8 contains the word lychee.

Why is this important? If you have a situation that requires looking up 1,000 fruit names across 36 months of data, the copying and pasting becomes more efficient when you need to create summary reports. This will be covered down the line with other articles, but just keep in mind that when you develop spreadsheets, efficiency and flexibility should be main drivers behind how you construct a financial model.

Array Form

The array form is very straightforward, in that you can either setup a long formula with specific references or highlight a set of data that comprises the range of information you are using. A simple example would be:

=LOOKUP("k",{"a","d","k","z";3,6,9,12}), which would find "k" in the array defined by "a","d","k" and "z" and return the appropriate value from the array defined as 3,6,9,12, and in this case, the answer would be 9. You could also have =LOOKUP("kangaroo",{"a","d","k","z";3,6,9,12}) and the result would still be nine. The point here is that if it is not a specific match, similar to VLOOKUP and HLOOKUP, Excel looks for the largest datapoint smaller than or equal to the reference value ("kangaroo", in this case). Finally, you could write this equation in the equivalent form of =LOOKUP("kangaroo",{"a",3;"d",6;"k",9;"z",12}). Note that the arrays could be flipped, to have Excel search a number lookup and return a letter or lookup a number to return another number or any assortment of combinations. As long as the arrays have the same number of values, you will not get an error message.

The other method I referenced relates to grabbing a range of data. For example, if you have colors listed in column A with unit sales data for four days in the subsequent columns, the entire range of data is comprised in the area defined by A2:E6.

If you wanted to know how many units were sold on day 4 of oranges (with "orange" in cell A6), you would enter the following formula:

=LOOKUP("orange",A2:E6) or =LOOKUP(A6,A2:E6), and the correct result would be displayed.

I have not used this form of the LOOKUP function, and it seems easily replaced by VLOOKUP. In addition, another drawback of this method is that if your column inputs (first column) are greater than the number of total columns you have in the data range, you will get a false answer (you can test this by creating a simple table as outlined above and adding a column "F" with data, changing the range to A2:F6 and you will get the answer "orange" instead of a number").

Another drawback to this method is a drawback of the vector form, too. There is no option to tell this formula to find the exact data point as in the case of VLOOKUP or HLOOKUP. There are ways around that, but for now, this standalone function will always find the closest value only.

Finally, either form requires the first column data to be in ascending order or the results may be correct. Based on the prior paragraph, you must make certain that the data is in ascending order or you will need to rely on another method to do the lookup you need.

While I do not use either of these functions extensively, the vector form is my preference if I have to use one or the other. Once you make the range reference absolute (the dollar signs in front of the letter and the number, like $A$2:$E$6), you can insert rows or cells and the values will maintain their correctness. The feature is something that I always consider when developing financial models.

VLOOKUP and HLOOKUP Functions in Microsoft Excel

2008-07-18T09:53:00.000-07:00

One of the more efficient and simple methods to reference an existing database or collection of data is through the VLOOKUP, HLOOKUP or LOOKUP functions. The VLOOKUP and HLOOKUP functions are similar and, therefore, have the same strengths and weaknesses. The LOOKUP function has two forms (vector and array), and should be used under different circumstances. I will cover the LOOKUP functions in the next post.

VLOOKUP and HLOOKUP

The VLOOKUP function can be used when referencing a particular name in the first column of a set of data, to pull information a specific number of columns away. For example, you could have the names of all of your salespersons in the first column and you would like to see how many sales that person made on the ninth day of the past month. The formula would look something like the following:

=VLOOKUP("John",A1:M20,9), where the range A1:M20 contains all of the data.

In this case, VLOOKUP will look for "John" in the first column of the cells covered by the range A1:M20, which is your range for all of the names and data, with the information you want in the ninth column INCLUDING the first column (that includes the various names of the salesforce). The VLOOKUP function searches A1:A20 for the name and will then look over the columns you have dictated to retrieve your data. If there is no exact match, Excel will return the value that is immediately less than what you are seeking. So if you had no "John" but there was a "Joe" and a "Josh," Excel would retrieve the value of "Joe" for you input.

Similar in construct to the VLOOKUP function, the HLOOKUP function is a way of finding information based on rows instead of columns. This would be useful in a case that has a series of dates across the top (like monthly or quarterly data) and the desired information is related to total sales for a particular period. One could setup the following to find such data:

=HLOOKUP("Q1 2008", D1:Q20,3), where the range A1:M20 contains all of the data.

In this case, HLOOKUP will search for the time period "Q1 2008" in the first row of the range D1:Q20 and return the information 3 rows down, including the first row, which is where the relevant information you are seeking is located. In this case, HLOOKUP searches D1:Q1 for "Q1 2008" and returns the value the number of rows away that you specified in the formula. As in the case of VLOOKUP, if there is not an exact match, the results will be from the row immediately less than what you are trying to find.

Note that in these two functions assume that the ordering of the first column (VLOOKUP) or row (HLOOKUP) is in alphabetical order. If that is not the case, neither of these functions will work properly. To get around this, we can setup the function to find the exact match we need by adding an additional command in the formula bar. Using our salesperson example again, let us assume that the names are in some other order than alphabetical, like descending based on last year's total sales or by birth date. We would do the following to adjust for such order:

=VLOOKUP("John",A1:M20,9,false).

The inclusion of the "false" at the end of the formula means that Excel must find the exact match you have targeted. If there is not exact match, the formula will return #N/A. You would use this same format for HLOOKUP when you want to find the exact match.

One major drawback of the VLOOKUP function is that if you insert or delete a column, the function will not compensate. In other words, if you delete a column and the formula you created is referencing column number nine, it will still reference number 9, which would now be the next column over. You would have to go back to the VLOOKUP formula and change the column reference number. The same effect occurs if you change the number of rows when using the HLOOKUP function. You need to keep cognizant of that as you start to modify the data range you are targeting, and make sure that the formula is correctly providing the information you require.

In summary, VLOOKUP and HLOOKUP functions are good for spreadsheets that are likely not to shift in construction (inserting or deleting columns or rows). It is a good basic formula for less dynamic spreadsheets, and is easily implemented by newcomers to financial modeling.

IF-THEN Statements with Microsoft Excel

2008-07-14T17:34:00.000-07:00

One of the basic statements for financial modeling is the IF-THEN function in Excel. While this may not be considered as robust as other approaches when doing actual computer programming (C++, Java, VB), in Excel it is rather straightforward and ubiquitous.

There are a couple of things to remember when using the IF-THEN approach:

• For basic results like "if this cell equals dog put a bone in the cell next to it" or "if the cell on my right equals rain display an umbrella sign in the cell below" or something like that. IF-THEN works well for binary situations, and is a method that the basic financial modeler can use and understand readily easily. The beauty of the function at its most basic level is the simplicity.

• IF-THEN can also be used for non-binary situations, meaning that one can "nest" several sub IF-THEN statements within the cell. For example, let's assume that you had four triggering events: apple, orange, grape and banana. Depending upon the word that was in a cell, there would be one of four results. This would look like (note that the extra spaces are in there for formatting purposes of this blog - you would not have spaces after the commas):

=IF(A1="Apple", "Watch for worm", IF(A1="Orange", "Citrus", IF(A1="Grape", "Soon to be a raisin", IF(A1="Banana", "Don't slip on the peel", "Do nothing")))).

You will notice that the last bit of this text includes "do nothing", which captures any text that is not one of the specified amounts you are seeking. If there were only four options (that somehow the input cell was blocked to prevent anything other than those four choices), the code would look like this:

=IF(A1="Apple", "Watch for worm", IF(A1="Orange", "Citrus", IF(A1="Grape", "Soon to be a raisin", "Don't slip on the peel"))).

By definition, only four choices will yield three IFs – think about the fourth IF as silent to help you see why this is the case.

• The financial modeler can also link IF-THEN statements to apply a mathematical operator to two or more statements in the cell. Thus, you could create a formula like =IF(A1="Pie", 1, 0) + IF(B1="Soup", 2, 0) + IF(C1="Water", 3, 0). You can do this with any operator and is useful if there are discrete components to the model you are developing. As you ultimately get further into advanced modeling, this can be used for triggers, or cells that create an influx of additional information when turned to a true state. Further discussion on that will occur as we move to intermediate level issues, but just remember that there is flexibility in the IF-THEN statement from this particular perspective.

• One shortcoming of the IF-THEN statement usage is that you can only have up to seven nested statements EVER in any function in Excel. For examples, the following is a maxed out version of the IF-THEN statement:

=IF(C5=1, 1, IF(C5=2, 2,IF(C5=3, 3, IF(C5=4, 4, IF(C5=5, 5, IF(C5=6, 6, IF(C5=7, 7, IF(C5=8, 8, "No")))))))) – if you try to add another nested IF statement, you will get an error message. Part of the rationale behind that is because evaluating the IF-THEN in the standard Excel environment (as well as computer language in general) takes some time to evaluate. Granted, it is not a lot of time when thinking about a machine calculating something faster than us, but each of the evaluation steps requires computing speed and memory. If you can imagine a nested 20-step IF-THEN statement in your mind and pretend that you must evaluate that statement in order until the last part of it, you will begin to see why there needs to be extra time for the computer to do analyze it. Thus, seven is the cutoff point, but in reality, you should never need that many nested IF-THEN statements anyway.

In summary, the IF-THEN statement can be a very useful, simple approach to binary and certain non-binary situations. It is not perfect for larger database inquiries or very large spreadsheets wherein the answer to the IF-THEN drives the model, but it can suffice for everyday simple financial modeling applications, and it easy to implement and interpret.

Microsoft Excel – Overview of the Financial Modeling Process

2008-07-08T14:36:00.000-07:00

For the novice financial modeler, knowing where to start a model can be a difficult and somewhat daunting task. Forget thinking about formulas, structure, flow and results, but how does one even begin the process? The good part about this problem is that when you have worked on or developed hundreds of models, the starting point for model creation much more simplified in one's mind. This, of course, it similar to needing experience to get a job, but requiring a job for experience - thus, where to start?

In general, I have found that the map for creating a successful financial model follows these steps:

• Identify the problem to be solved or purpose of the model

Why do I need a model? This could be something like simple calculations to see what car payments may be like over a fiver-year period or the creation of family budget. It may be a more advanced model that calculates changes in interest rates based on a debt refinancing or the impact to a company's earnings from an acquisition. These are reasons why a financial model may be required.

• Identify the variables or assumptions needed to populate the model

What factors impact the analysis I am trying to perform? Do I need to consider macro economic factors like interest rates and foreign currency exchanges? Are there commodity pricing issues that will impact my analysis? If modeling a company's forecast, what sorts of growth expectations exist and what level of profitability in the foreseeable future? It is important at this point to make these mental assumptions prior to trying to create the model. The clearer one is in thinking through the steps, the easier the development will be.

• Think about the results (the answer you are seeking)

This may seem odd to think about what the answer to the question is prior to developing the model, but this helps form how the model is to be developed. For example, if you are curious to see what net income a company will generate based on a series of assumptions, you are likely to lean toward a standard income statement development with some for of summary table. If you are more interested in creating a Black-Scholes options pricing model, than the assumptions are relatively straightforward, and the answer you are seeking is the value of a call option or put option. If you are trying to determine which salesforce sells the most of widget A in the first week of each month for the past five years, the resulting model needs to have a summary that is easy to read expressing that exact answer. As you think about the purpose of the model and the assumptions affecting the output, you should begin to visualize the model in your head.

• Start building

Once you have the first three components determined, it is time to begin constructing the base of the model. For example, a typical leveraged buyout or acquisition model will have an income statement, balance sheet and cash flow as the primary driver of results. These are all linked together and require some basic concepts of accounting to be understood. From this, it is common to incorporate debt repayment schedules, various financial ratios covering cash flow and interest, and value to the buyers. For you novices, this will become more evident over time as you further hone your modeling skills, so just have patience.

• Double Check

In my career in investment banking, nothing causes more angst (other than losing a deal) than receiving an analysis from the deal team and quickly noticing that there are obvious errors. I learned from the old school methods of modeling, which dictated printing out the entire model and double checking the formulas with my trusty HP and a pencil. It is not necessary to check every single cell, but you need to provide the model with a sanity check. This will also help you build more robust models, as you will eventually start double checking the materials as you are developing the model, thus saving time.

These five steps will allow you to become a more efficient model builder, and allow you to progress from novice to intermediate in no time. Subsequent postings will include very simple examples of spreadsheets to help the reader better understand the process of financial modeling. For now, please just try to visualize the five steps above and I will be back in the next post with some basic beginning building blocks.

Introduction to Microsoft Excel

2008-07-07T09:19:00.000-07:00

This is the beginning of my blog, Excel Tips and Tricks. I know that there are many resources out there for spreadsheet tips and financial modeling guides, but this blog will be a tutorial based on my fifteen years of financial model development. I will provide step by step information in the posts to come on practical spreadsheet development.

My steps will range from basic model input to some more advanced approaches that incorporate some of the built-in functions that are rarely used. In addition, the later posts will delve into basic VBA coding to simply certain functions that you may require on a regular basis in your own programming.

The other element to this blog is to illustrate features that can be performed by others for the benefit of small businesses that lack a true CFO or financial person. Having worked with small business in the past (business plan writing, strategy assessment, financial modeling), I know that focusing on the day-to-day operations is the critical element, and some of the necessary financial planning takes a back seat at time. Financial models can be a powerful tool for assessing the prospects and strategy of a business, and is a function that is best outsourced for small businesses.

In any event, I hope that you, the reader, will find this tutorial helpful and allow you to become a better financial modeler in the upcoming weeks and months. Developing spreadsheets may not be fund for everyone, but the final product should, at a minimum, accomplish your specific goals and provide some sense of reward to the creator.