Chapter 11: IF Statements

Overview

• If I order up to 500 units of a product, I pay $3.00 per unit. If I order from 501 through 1200 units, I pay $2.70 per unit. If I order from 1201 through 2000 units, I pay $2.30 per unit. If I order more than 2000 units, I pay $2.00 per unit. How can I write a formula that expresses the purchase cost as a function of the number of units purchased?

• I’ve just purchased 100 shares of stock at a cost of $55 per share. To hedge the risk that the stock might decline in value, I purchased 60 six-month European put options. Each option has an exercise price of $45 and costs $5. How can I develop a worksheet that indicates the six-month percentage return on my portfolio for a variety of possible future prices?

• Many stock market analysts believe that moving-average trading rules can outperform the market. A commonly suggested moving-average trading rule is to buy a stock when the stock’s price moves above the average of the last 15 months, and to sell a stock when the stock’s price moves below the average of the last 15 months. How would this trading rule have performed against the Standard & Poor’s 500 Stock Index (S&P)?

• In the game of craps, two dice are tossed. If the total of the dice on the first roll is 2, 3, or 12, you lose. If the total of the dice on the first roll is 7 or 11, you win. Otherwise, the game keeps going. How can I write a formula to determine the status of the game after the first roll?

• In most pro forma financial statements, cash is used as the plug to make assets and liabilities balance. I know that using debt as the plug would be more realistic. How can I set up a pro forma statement having debt as the plug?

• When I copy a VLOOKUP formula to determine salaries of individual employees, I get a lot of #NA errors. Then when I average the employee salaries, I cannot get a numerical answer because of the #NA errors. Can I easily replace the #NA errors with a blank space so I can compute average salary?

• My worksheet contains quarterly revenues for Wal-Mart. Can I easily compute the revenue for each year and place it in the row containing the first quarter’s sales for that year?

• IF statements can get rather large. How many IF statements can I nest in a cell? What is the maximum number of characters allowed in an Excel formula?

The eight situations listed above seem to have little, if anything, in common. However, setting up Microsoft Office Excel 2007 models for each of these situations requires the use of an IF statement. I believe that the IF formula is the single most useful formula in Excel. IF formulas let you conduct conditional tests on values and formulas, mimicking (to a limited degree) the conditional logic provided by computing languages such as C, C++, and Java.

An IF formula begins with a condition such as A1>10. If the condition is true, the formula returns the first value listed in the formula; otherwise, we move on within the formula and repeat the process. The easiest way to show you the power and utility of IF formulas is to use them to help answer each of our eight questions.

• If I order up to 500 units of a product, I pay $3.00 per unit. If I order from 501 through 1200 units, I pay $2.70 per unit. If I order from 1201 through 2000 units, I pay $2.30 per unit. If I order more than 2000 units, I pay $2.00 per unit. How can I write a formula that expresses the purchase cost as a function of the number of units purchased?

• You can find the solution to this question on the Quantity Discount worksheet in the file Ifstatement.xlsx. The worksheet is shown in Figure 11-1.

  
  Figure 11-1: You can use an IF formula to model quantity discounts.

• Suppose cell A9 contains our order quantity. We can compute an order’s cost as a function of the order quantity by implementing the following logic:

  • If A9 is less than or equal to 500, the cost is 3*A9.

  • If A9 is from 501 through 1200, the cost is 2.70*A9.

  • If A9 is from 1201 through 2000, the cost is 2.30*A9.

  • If A9 is more than 2000, the cost is 2*A9.

• We begin by linking the range names in A2:A4 to cells B2:B4, and linking the range names in cells D2:D5 to cells C2:C5. Then we implement this logic in cell B9 with the following formula:

   IF(A9<=_cut1,price1*A9,IF(A9<=_cut2,price2*A9,IF(A9<=_cut3,price3*A9,price4*A9)))

• To understand how Excel computes a value from this formula, recall that IF statements are evaluated from left to right. If the order quantity is less than or equal to 500 (cut1), the cost is given by price1*A9. If the order quantity is not less than or equal to 500, the formula checks to see whether the order quantity is less than or equal to 1200. If this is the case, the order quantity is from 501 through 1200, and the formula computes a cost of price2*A9. Next, we check whether the order quantity is less than or equal to 2000. If this is true, the order quantity is from 1201 through 2000, and our formula computes a cost of price3*A9. Finally, if the order cost has not yet been computed, our formula defaults to the value price4*A9. In each case, the IF formula returns the correct order cost. Note that I typed in three more order quantities in cells A10:A12 and copied our cost formula to B10:B12. For each order quantity, our formula returns the correct total cost.

• An IF formula containing more than one IF statement is called a nested IF formula.

• I’ve just purchased 100 shares of stock at a cost of $55 per share. To hedge the risk that the stock might decline in value, I purchased 60 six-month European put options. Each option has an exercise price of $45 and costs $5. How can I develop a worksheet that indicates the six-month percentage return on my portfolio for a variety of possible future prices?

• Before tackling this problem, I want to review some basic concepts from the world of finance. A European put option allows you to sell at a given time in the future (in this case, six months) a share of a stock for the exercise price (in this case, $45). If our stock’s price in six months is $45 or higher, the option has no value. Suppose, however, that the price of the stock in 6 months is below $45. Then you can make money by buying a share and immediately reselling the stock for $45. For example, if in 6 months our stock is selling for $37, you can make a profit of $45–$37, or $8 per share, by buying a share for $37 and then using the put to resell the share for $45. You can see that put options protect you against downward moves in a stock price. In this case, whenever the stock’s price in six months is below $45, the puts start kicking in some value. This cushions a portfolio against a decrease in value of the shares it owns. Note also that the percentage return on a portfolio (we will assume that no dividends are paid by the stocks we own) is computed by taking the change in the portfolio’s value (final portfolio value–initial portfolio value) and dividing that number by the portfolio’s initial value.

• With this background, let’s look at how the six-month percentage return on our portfolio, consisting of 60 puts and 100 shares of our stock, varies as the share price varies between $20 and $65. You can find this solution on the Hedging worksheet in the file Ifstatement.xlsx. The worksheet is shown in Figure 11-2 on the next page.

  
  Figure 11-2: Hedging example that uses IF statements

• The labels in A2:A7 are linked to cells B2:B7. The initial portfolio value is equal to 100($55)+60($5)=$5,800, shown in cell B7. By copying from B9 to B10:B18 the formula IF(A9<exprice,exprice–A9,0)*Nputs, we compute the final value of our puts. If the six-month price is less than our exercise price, we value each put as exercise price–six-month price. Otherwise, each put will in six months have a value of $0. Copying from C9 to C10:C18 the formula Nshares*A9, we compute the final value of our shares. Copying from D9 to D10:D18 the formula ((C9+B9)–startvalue)/startvalue) computes the percentage return on our hedged portfolio. Copying from E9 to E10:E18 the formula (C9–Nshares*pricenow)/(Nshares*pricenow) computes the percentage return on our portfolio if we are unhedged (that is, buy no puts).

• In Figure 11-2, you can see that if the stock price drops below $45, our hedged portfolio has a larger expected return than our unhedged portfolio. Also note that if the stock price does not decrease, the unhedged portfolio has a larger expected return. This is why the purchase of puts is often referred to as portfolio insurance.

• Many stock market analysts believe that moving-average trading rules can outperform the market. A commonly suggested moving-average trading rule is to buy a stock when the stock’s price moves above the average of the previous 15 months and to sell a stock when the stock’s price moves below the average of the previous 15 months’ price. How would this trading rule have performed against the Standard & Poor’s 500 Index?

• In this example, we’ll compare the performance of the moving-average trading rule (in the absence of transaction costs for buying and selling stock) to a buy-and-hold strategy. The strength of a moving-average trading rule is that it helps you follow market trends. A moving-average trading rule lets you ride up with a bull market and sell before a bear market destroys you. Our data set contains the monthly value of the S&P 500 Index for the time period January 1871 through October 2002. To track the performance of our moving-average trading strategy, we need to track the following information each month:

  • What is the average of the S&P 500 Index over the last 15 months?

  • Do we own stock at the beginning of each month?

  • Do we buy stock during the month?

  • Do we sell stock during the month?

  • What is our cash flow for the month (positive if we sell stock, negative if we buy stock, and 0 otherwise)?

• Our worksheet for this situation requires us to scroll down many rows. We would like to keep columns A and B as well as the headings in row 8 visible as we scroll down. To do this, in the file Matrade.xlsx, we move the cursor to cell C9, click View on the Ribbon, and then click Freeze Panes. This presents us with the choices shown in Figure 11-3.

  
  Figure 11-3: Freeze Panes options

• We chose the Freeze Panes option. This allows us to keep columns A and B and rows 6–8 visible as we scroll through the worksheet. The Freeze Top Row option (new in Excel 2007) lets us keep just the top row visible while scrolling through the rest of the worksheet. For example, if the top visible row is row 6, then we will see row 6 no matter how far down we scroll. If we choose the Freeze First Column option (also new in Excel 2007), we will always see the leftmost column as we scroll through the worksheet. Selecting Unfreeze Panes from the menu returns us to normal worksheet view.

• The file Matradingrule.xlsx, shown in Figure 11-4, includes the formulas needed to track the effectiveness of a moving-average strategy. Tackling this problem requires several IF formulas, and some of the IF formulas will require an AND operator. For example, we’ll buy the stock during a month if and only if we don’t own the stock at the beginning of the month and the current month’s price is larger than the 15-month moving average for the stock’s price. The first month for which we can compute a 15-month moving average is April 1872, so we begin our calculations in row 24.

  
  Figure 11-4: Moving-average trading rule beats buy and hold!

• Let’s assume we first owned the stock in April 1872, so we entered Yes in cell C24.

  • By copying from D24 to D25:D1590 the formula AVERAGE(B9:B23), we compute the 15-month moving average for each month.

    Note 

    An easy way to copy the formula from D24 to D25:D1590 is to point at the lower right corner of cell D24 (the pointer is displayed as a crosshair) and then double-click the left mouse button. Double-clicking copies the formula in a column to match the number of filled rows in the column to the left of the current column. This trick can also be used to copy formulas in multiple columns.

  • By copying from E24 to E25:E1590 the formula IF(AND(C24="No",B24>D24),"yes", "no"), we determine for each month whether our S&P share is purchased during the month. Remember that we purchase the share only if we did not own the stock at the beginning of the month and the current value of the S&P exceeds its 15-month moving average. Notice the AND portion of the formula. It contains two conditions (more than two are allowed) separated by a comma. If both conditions are satisfied, the formula returns Yes; otherwise, it returns No. For an IF formula to recognize text, you need to place quotation marks (" ") around the text.

  • By copying from F24 to F25:F1590 the formula IF(AND(C24="Yes",B24<D24),"yes", "no"), we determine for each month whether our S&P share is sold. The stock is sold if and only if we owned the S&P share at the beginning of the month and the current value of the S&P share is below the 15-month moving average. April 1873 is the first month in which we sell our S&P stock.

  • During any month before October 2002, if we buy a share of the S&P during the month, our cash flow is negative the value of the S&P share we bought. If we sell a share of the S&P during the month, our cash flow equals the value of the S&P. Otherwise, the cash flow is 0. During October 2002, we sell any S&P share we own to get credit for its value. Therefore, by copying from G24 to G25:G1589 the formula IF(E24="yes",–B24,IF(F24="yes",B24,0)), we record our cash flow for all months before October 2002. Entering in cell G1590 the formula IF(C1590="yes", B1590,0) gives us credit for selling any stock we own at the beginning of the last month.

  • In cell G6, we compute our total profit from the moving-average trading strategy with the formula SUM(G24:G1590). We find the 15-month moving average strategy earns a profit of $1,319.75.

  • The profit from buying and holding shares is simply the October 2002 S&P value minus the April 1872 S&P value. We compute the profit from the buy-and-hold strategy in cell G7 with the formula B1590–B24. We find that the buy and hold profit of $849.45 is far worse than the profit from the moving-average trading rule. Of course, we ignored the transaction costs incurred in buying and selling stocks. If transaction costs are large, then this might wipe out the excess profits earned by the moving-average trading strategy.

• In the game of craps, two dice are tossed. If the total of the dice on the first roll is 2, 3, or 12, you lose. If the total of the dice on the first roll is 7 or 11, you win. Otherwise, the game keeps going. How can I write a formula to determine the status of the game after the first roll?

• The fact that you lose in craps if you throw a 2, 3, or 12 can be conveniently modeled by placing an OR formula within an IF formula. In cell B5 of the Craps worksheet, shown in Figure 11-5 and found in the file Ifstatement.xlsx, we enter the formula IF(OR(A5=2, A5=3,A5=12),"lose",IF(OR(A5=7,A5=11),"win","keep going")). This formula is then copied from B5 to B6:B7. The formula displays lose if a 2, 3, or 12 is entered in cell A5. It displays win if a 7 or 11 is entered, and it displays keep going for any other value.

  
  Figure 11-5: Using IF statements to model the first roll in craps

• In most pro forma financial statements, cash is used as the plug to make assets and liabilities balance. I know that using debt as the plug would be more realistic. How can I set up a pro forma statement having debt as the plug?

• A pro forma is basically a prediction of a company’s financial future. A pro forma requires construction of a company’s future balance sheets and income statements. The balance sheet provides a snapshot of the company’s assets and liabilities at any point in time. An income statement tells us how the company’s financial status is changing at any point in time. Pro forma statements can help a company determine its future needs for debt and are also key parts of valuation models that stock analysts use to determine whether a stock is properly valued. In the file Proforma.xlsx, I’ve generated the free cash flows (FCFs) for a company for the next four years. Figure 11-6 on the next page shows the balance sheet, and Figure 11-7 on the next page shows the income statement.

  
  Figure 11-6: Pro forma assumptions and balance sheet

  
  Figure 11-7: Pro forma income statement

• Column D contains information about the company’s current status (during year 0). Our basic assumptions are as follows:

  • Sales growth (SG) is 2 percent per year.

  • Initial sales are $1,000.

  • Interest rate on debt is 10 percent.

  • Dividend payout is 5 percent of net income.

  • The tax rate is 53 percent.

  • Cost of goods sold (COGS) are 75 percent of sales.

  • Depreciation is 10 percent of gross fixed assets.

  • Liquid assets earn 9 percent.

  • Current assets are 15 percent of sales.

  • Current liabilities are 7 percent of sales.

  • Net fixed assets are 60 percent of sales.

• I’ve assigned the names in the cell range C3:C10 to the cells in the range D3:D10. Then, during each Year t, basic finance and accounting imply the following relationships, which are then implemented in a series of formulas:

  • Formula 11.1: Year t+1 sales=(Year t sales)*(1+SG). I’ve computed sales during each year by copying from E28 to F28:H28 the formula D28*(1+SG).

  • Formula 11.2: Year t COGS=COGS*(Year t sales). Each year’s COGS are computed by copying from E29 to F29:H29 the formula COGS*E28.

  • Formula 11.3: If Year t assets>Year t liabilities, Year t debt must be set equal to Year t total assets–Year t current liabilities–Year t equity. Otherwise, Year t debt=0. I’ve computed each year’s debt in E21:H21 with the formula IF((E18>E20+E24,E18–E20– E24,0). If Year t total assets are greater than Year t total liabilities, this formula sets Year t debt to Year t total assets–Year t current liabilities–Year t equity. This equalizes, or balances, assets and liabilities. Otherwise, we set Year t debt equal to 0. In this case, Year t cash and marketable securities will be used to balance assets and liabilities.

  • Formula 11.4: Year t current liabilities=(CL/Sales ratio)*(Year t sales). In E20:H20, we use the formula $H$4*E28 to compute current liabilities for each year (copying this formula from E20 to F20:H20).

  • Formula 11.5: Year t equity=Year t stock+Year t retained earnings. In E24:H24, we compute equity by copying from E24 to F24:H24 the formula SUM(E22:E23).

  • Formula 11.6: If Year t debt is greater than 0, Year t cash and marketable securities equals 0. Otherwise, Year t cash and marketable securities equals MAX(0,Year t total liabilities–Year t current assets–Year t net fixed assets). In E13:H13, I compute cash and marketable securities for each year by copying from E13 to F13:H13 the formula IF(E21>0,0,MAX(0,E25–E14–E17)). If Year t debt is greater than 0, we need not use Year t cash and marketable securities to balance assets and liabilities.

    In this case, we set Year t cash and marketable securities equal to 0. Otherwise, we set Year t cash and marketable securities equal to Year t total assets–Year t current liabilities–Year t equity. This balances assets and liabilities if Year t assets (without cash and marketable securities) are less than Year t liabilities. If debt does not balance assets and liabilities, this creates liquid assets as the plug that does balance assets and liabilities.

  • Formula 11.7: Year t interest expense=(Year t Debt)*IRD. In E33, I compute interest expense by using the formula IRD*E21, copying this formula again to F33:H33.

  • Formula 11.8: Year t interest income=(Year t cash and marketable securities)*LAIR. In E32:H32, I compute interest income by copying from E32 to F32:H32 the formula LAIR*E13.

  • Formula 11.9: Year t operating income=Year t sales–Year t COGS–Year t depreciation. In E31:H31, operating income is computed by copying from E31 to F31:H31 the formula E28–E29–E30.

  • Formula 11.10: Year t dividends=(Year t net income)*DIV. In E39:H39, I copy from E39 to F39:H39 the formula E36*DIV to compute dividends for each year.

  • Formula 11.11: Year t+1 beginning retained earnings=Year t ending retained earnings. I compute beginning retained earnings each year in F38:H38, copying from F38 to G38:H38 the formula E40.

  • Formula 11.12: Year t end of year retained earnings=Year t beginning retained earnings+Year t net income–Year t dividends. In E40:H40, I compute each year’s ending retained earnings by copying from E40 to F40:H40 the formula E38+E36–E39.

  • Formula 11.13: Year t income before taxes=Year t operating income–Year t interest expense+Year t cash income. I compute income before taxes by copying from E34 to F34:H34 the formula E31–E33+E32.

  • Formula 11.14: Year t taxes=(Year t income before taxes)*TR. I compute each year’s taxes in E35:H35 by copying from E35 to F35:H35 the formula TR*E34.

  • Formula 11.15: Year t net income after taxes=(Year t income before taxes)–(Year t taxes). In E36:H36, I compute each year’s net income by copying from E36 to F36:H36 the formula E34–E35.

  • Formula 11.16: Year t net fixed assets=(Year t sales)*(NFA/Sales). In E17:H17, I compute each year’s net fixed assets by copying the formula $H$5*E28 from E17 to F17:H17.

  • Formula 11.17: Year t gross fixed assets=Year t net fixed assets+Year t accumulated depreciation. In cells E15:H15, I compute gross fixed assets for each year by copying the formula E17+E16.

  • Formula 11.18: Year t depreciation=(Year t net fixed assets)*DEP. Each year, I use the formula DEP*E15 to compute depreciation, copying the formula from E30 to F30:H30.

  • Formula 11.19: Year t accumulated depreciation=Year t–1 accumulated depreciation +Year t depreciation. Each year, I use the formula D16+E30 to compute accumulated depreciation by copying the formula from E16 to F16:H16.

  • Formula 11.20: Year t net fixed assets=Year t gross fixed assets–Year t accumulated depreciation. In row 17, to compute net fixed assets, I copy from E16 to F16:H16 the formula D15–D16.

  • Formula 11.21: Year t total assets=Year t liquid assets+Year t net fixed assets+Year t cash and marketable securities. By adding liquid assets, current assets, and net fixed assets, I compute our total assets by copying from E18 to F18:H18 the formula SUM(E13,E14,E17).

  • Formula 11.22: Year t total liabilities=Year t current liabilities+Year t debt+Year t equity. By copying from E25 to F25:H25 the formula SUM(E20,E21,E24), I compute total liabilities for each period. Each year will balance because of our debt and liquid asset statements.

• Formulas 11.3 and 11.6 require the use of IF statements. This worksheet will also contain circular references. (For more information about solving circular references, see Chapter 10, “Circular References.”) For example, the following relationships create a circular reference:

  • Year t cash affects Year t total assets.

  • Year t total assets affect Year t debt.

  • Year t debt affects Year t cash.

• Because our worksheet contains circular references, we need to select the Microsoft Office Button followed by Excel options. Then choose Formulas and check the Enable Iterative Calculations box. As explained in Chapter 10, “Circular References,” this will enable Excel to resolve our circular references. Note that for each Year t, total assets in row 18 equal total liabilities in row 25. This shows the power of IF formulas and circular references.

• When I copy a VLOOKUP formula to determine salaries of individual employees, I get a lot of #N/A errors. Then when I average the employee salaries I cannot get a numerical answer because of the #N/A errors. Can I easily replace the #N/A errors with a blank space so I can compute average salary?

• The file Errortrap.xlsx (see Figure 11-8 on the next page) contains salaries and names of 5 employees in the cell range D3:E7. In D11:D15 we have a list of 5 people and we compute their salary by copying from E11 to E12:E15 the formula =VLOOKUP(D11, $D3:$E$7,2,False). Unfortunately in cells E13 and E14 we see an #N/A error. NA is short for “not available.” Excel returns an #N/A error value when a formula cannot return an appropriate result. Because JR and Josh have no listed salary the VLOOKUP cannot return a salary value for them. Thus when we compute average salary in E16 with formula =AVERAGE(E11:E15) we get an #N/A error. Many people I have taught go through and manually replace the #N/A errors with spaces so their average formula will calculate properly (by ignoring the spaces). The IFERROR function (new in Excel 2007) makes replacing errors by a desired character (such as space or 0) a snap. The syntax of IFERROR is IFERROR(value,value_if_error).

  
  Figure 11-8: Error trapping formulas

• The first argument is the formula you want calculated, and the second argument is the value inserted in the cell if your formula returns an error value (other common error values are #DIV/0, #NAME, #NUM, #REF, #VALUE; more on these later in the section). Therefore copying from F11 to F12:F15 the formula IFERROR(VLOOKUP(D11, $D$3:$E3:$E7,2,False)," ") computes salary correctly for each actual employee and enters a blank space for people who are not actual employees. The formula =AVERAGE(F11:F15) now correctly computes the average salary for all listed employees.

• The file Errortypes.xlsx, shown in Figure 11-9, contains examples of other common error values.

  
  Figure 11-9: Examples of Excel error values

  • In cell D3, the formula =C3/B3 yields a #DIV/0! value because we are dividing by 0.

  • In cell D6, the formula =C6+D6 yields a #VALUE! error because Jack is not the appropriate type of data for the entered formula. (Jack is text.)

  • In cell D7, the formula =SUM(Sales) returns a #NAME? error indicating that the range name Sales referred to in the formula is not defined.

  • In cell D8, the formula =SQRT(–1) results in a #NUM! error. The #NUM error results when you enter an unacceptable argument in a function. Because negative numbers do not have square roots, we receive the #NUM! error.

  • In cell C9, we entered the formula SUM(A1:A3) and then deleted column A. This results in a #REF! error because the cells that we referred to in our formula (cells A1:A3) are no longer in the worksheet.

  • The IFERROR function can be used to replace any of these error values by any desired number or text string,

• My worksheet contains quarterly revenues for Wal-Mart. Can I easily compute the revenue for each year and place it in the row containing the first quarter’s sales for that year?

• The file Walmartrev.xlsx contains quarterly revenues (in millions of dollars) for Wal-Mart. (See Figure 11-10.) Rows 6, 10, 14, and so on contain the revenues for the first quarter of each year. In each of these rows, we would like to compute total revenues for the year in column E. In other rows, column E should be blank. We could enter in cell E6 the formula SUM(D6:D9) and copy this formula to E10, then E14, then E18, and so on. There must be a better way. Using an IF statement with two neat Excel functions, (ROW() and MOD()), gives us an easy way to enter our formula once and then copy the formula. The function ROW(cell reference) yields the row of reference. The function =ROW(A6) would yield a 6, whereas if we are in row 6 the =ROW() function would also yield a 6. The function MOD(number,divisor) yields the remainder when number is divided by divisor. For example, MOD(9,4) yields 1, whereas MOD(6,3) yields 0. Note that we want our formula to work only in rows that leave a remainder of 2 when divided by 4. Therefore, copying from E6 to E7:E57 the formula =IF(MOD(ROW(),4)=2,SUM(D6:D9)," ") will ensure that we add up revenues for the current year only in rows that leave a remainder of 2 when divided by 4. This means that we compute annual revenues only in the first quarter of each year, as desired.

  
  Figure 11-10

• IF statements can get rather large. How many IF statements can I nest in a cell? What is the maximum number of characters allowed in an Excel formula?

• In Excel 2007, you can nest up to 64 IF statements in a cell. In previous versions of Excel, you could nest a maximum of 7 IF statements. In Excel 2007, a cell can contain up to 32,000 characters. Previous versions of Excel allowed only 1000 characters in a cell.