Problems

1. The file C20p1.xlsx contains data about unit sales for 11 products during the years 1999–2003. Write a formula using the MATCH and OFFSET functions that determines the sales of a given product during a given year. Can you think of another way to solve this problem without using the MATCH and OFFSET functions?

2. A commonly suggested moving average trading rule is to buy a stock when its price moves above the average of the last D months and to sell it when its price moves below the average of the last D months. In Chapter 11, “IF Statements,” we showed that for D=15, this trading rule outperformed the Standard &Poor’s 500 by a substantial amount. By combining a one-way data table with the OFFSET function, determine the value of D that maximizes trading profit (excluding transactions costs). You can find pertinent data in the file Matradingrule.xlsx.

3. A commonly suggested moving average trading rule is to buy a stock when its price moves above the average of the last B months and to sell it when its price moves below the average of the last S months. In Chapter 12, “Time and Time Functions,” we showed that for B=S=15, this trading rule outperformed the Standard & Poor's 500 by a substantial amount. By combining a two-way data table with the OFFSET function, determine the values of B and S that maximize trading profit (excluding transactions costs). You’ll find data for this problem in the file Matradingrule.xlsx.

4. The file Lagged.xlsx contains data about the number of magazine ads placed by U.S. Army Recruiting during each of 60 consecutive months. For each month, we define the k-month lagged number of ads to equal the number of ads placed k months ago. For months 7–60, we would like to compute the 1-month lagged, 2-month lagged through 6-month lagged values of the number of ads. Use the OFFSET function to efficiently compute these lagged values.

5. The file Verizondata.xlsx gives sales of 4 different Verizon phones in 5 regions. Determine an efficient method to enter for each of the 20 region-product combinations the region, type of phone, and sales of each phone into one row.

6. This is a difficult one!! The file Agingdata.xlsx gives the number of insurance claims projected to be received daily and the number of insurance company workers available. Each day, a worker can process up to 30 claims. Workers process the oldest claims in the system first. Cells H6:AL6 contain the number of claims already in the system on January 1, before new claims arrive. Set up a worksheet to track the “aging” of the claims. That is, for each day, how many 1-day old , 2-day old, … 30-day old and over 30-day old claims will be in the system.

7. Each row of the file Tapesales.xlsx contains monthly sales of a video tape. Write a formula to determine sales for each tape during its first six months on the market.

8. To obtain a golfer’s handicap, we average the 10 lowest of the golfer’s last 20 rounds. Then we subtract 80 and round to the nearest integer to obtain the handicap. Thus, if the 10 lowest of last 20 rounds added up to 864, the handicap would be 6. The file Golf-data.xlsx contains a golfer’s scores. Beginning in row 24, compute the golfer’s handicap after each round. We will assume that if the 10th best score in the last 20 rounds occurs more than once, then all rounds including that score will be included in the handicap calculation. Note that the Excel function =ROUND(x,0) will round x to the nearest integer.

9. Each row of the file Carsumdata.xlsx contains sales data for a product (car, train, or plane) from January thru July. Suppose we enter a month and a product into our worksheet. Write a formula that will give the total sales of that product during the given month.

10. The file Verizon.xlsx contains monthly returns on Verizon stock. Use the OFFSET function to extract all the January returns to one column, all the February returns to one column, and so on.

11. The file Casesensitive.xlsx contains product codes and product prices. Note that the product codes are case sensitive. For example, DAG32 is not the same product as dag32. Write a formula that gives the product price for any product code. Hint: You might need to use the EXACT function. The formula EXACT(cell1,cell2) yields TRUE if cell1 and cell2 have exactly the same contents. EXACT differentiates between uppercase and lowercase letters.