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.
Monday, December 15, 2008
Thursday, November 6, 2008
Developing a Basic Financial Model – Part I: The Income Statement
Excel is probably the most popular spreadsheet in use today, and certainly a mainstay of investment banks, private equity firms and hedge funds. It offers a tremendous amount of flexibility to develop a wide array of financial computations, ranging from simple, static calculations to complex, dynamic analyses. In order to effectively develop financial models for use in valuation analyses or forecasting, it is important to understand how companies show their information. This article 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.
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.
Friday, October 3, 2008
Time Value Formulas in Microsoft Excel – Part II
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.
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.
Friday, September 26, 2008
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.
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.
Wednesday, September 10, 2008
IS Formulas in Microsoft Excel – Part II
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.
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
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."
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."
Tuesday, September 2, 2008
Weighted Average Calculations in Microsoft Excel
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.
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.
Sunday, August 17, 2008
MATCH Function in Microsoft Excel
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.
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.
Sunday, August 10, 2008
SEARCH and FIND Functions in Microsoft Excel
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.
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.
Thursday, July 31, 2008
COUNTIF and SUMIF Formulas in Microsoft Excel
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.
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.
Monday, July 21, 2008
LOOKUP Function in Microsoft Excel
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.
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.
Friday, July 18, 2008
VLOOKUP and HLOOKUP Functions in Microsoft Excel
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.
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.
Monday, July 14, 2008
IF-THEN Statements with Microsoft Excel
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.
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.
Tuesday, July 8, 2008
Microsoft Excel – Overview of the Financial Modeling Process
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.
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.
Monday, July 7, 2008
Introduction to Microsoft Excel
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.
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.
Subscribe to:
Posts (Atom)
Posts
