Growth Rate (Expected) Year 0 Year 1 Formulas Investment A 3% $1,400 NA 5 Investment B 12% $1,400 6 Investment C 11% $1,400 INIA INA RELATIVE CELL REFERENCES "Take each value in column C and multiply it by its adjacent growth rate in column B (which is 1 plus the percentage expected growth) ABSOLUTE CELL REFERENCES 13 Investment A Growth Rate (Expected) Year 0 3% $1,400 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 15 Formulas INIA INIA ANA INA INA IN/A INA NA 16 12 Start in column D, then move across allowing the column to change, and multiply the preceding value by its FIXED growth rate in cell $8513 (which is 1 plus the percentage expected growth) to get the cument value." FIXED COLUMN/RELATIVE ROW CELL REFERENCES 19 20 Growth Rate 21 22 Investment A 23 Investment B 24 Investment C 25 (Expected) Year 0 3% $1,400 12% $1,400 11% $1,400 Year 1 Year 2 Year 3 26 Formulas Year 4 Year 5 Year 6 Year 7 Year Year 9 Year 10 ANA NA ANA NA AN/A N/A NA ANA NA A NA ANA NA NA ANA NIA N/A ANA N/A ANA NA #NIA ANIA "Start in column D, then move across, and multiply the preceding value by its growth rate in cell $822 (which is 1 plus the percentage expected growth) to get the current value." By changing the growth rate cell from 10322, e to $822, the row of the growth rate is allowed to change yet remain in column B while filing down to the other two investments 444 NA ANA N/A One of the most powerful features in Excel is the ability to place a formula in one cell and then drag the contents of that cell down or across to repeat that same formula on other values. For example, let's say you have this following simple layout in Excel: A B C D E 1 Item Order Quantity Unit Cost (S) Total Cost 2 MP3 album 8 11.99 ? 3 Concert t-shirt 17 4 Large soft drink 11 27.99 4.99 We can tell from this table that there are 8 orders for the MP3 album and that value is in cell C2. ? ? A B 1 Item Order Quantity Unit Cost ($) Total Cost 2 MP3 album 8 11.99 ? 3 Concert t-shirt 17 27.99 4.99 ? ? 4 Large soft drink 11 Next, we would like to calculate the Total Cost for all MP3 orders. To do that we would multiply the Order Quantity by the Unit Cost; 8 11.99. That can be done in cell E2. A B 1 Item Order Quantity Unit Cost (S) Total Cost 2 MP3 album 8 11.99 =C202 3 Concert t-shirt 17 4 Large soft drink 11 27.99 4.99 ? Finally, we can select cell E2, grab the drag handle, and then drag down to E4 to obtain the other two Total Cost values. This technique uses relative cell references, meaning the values can change as you drag downward. A B C 1 Item Order Quantity 2 MP3 album 8 Unit Cost ($) 11.99 Total Cost -C2 D2 3 Concert t-shirt 17 27.99 -C3 D3 4 Large soft drink 11 4.99 -C4 D4 Investment A: $ (to the nearest dollar) Investment B: $ (to the nearest dollar) Investment C: $ (to the nearest dollar) Absolute Cell References By placing a dollar sign ($) in front of the row and/or column of a cell you can "lock down" either the row, column, or both so no change occurs when you drag to fill other cells. In row 13, we would like to calculate the value of Investment A over a period of ten years assuming the constant growth rate in cell B13. First, calculate the value in Year 1 (D13) using the same technique in Part A. If you try to drag D13 to the right to fill in the remaining years, you will get some very strange numbers! That is because the growth rate cell B13 is changing as you drag. However, you need that cell to say fixed in place for all the formulas as you drag to fill E13 through M13. The way to fix cell B13 in place is using an absolute cell reference. Instead of 813 in the formula change it to $8513 and then drag to the right to fill Year 2 through Year 10. Enter those values below (to the nearest dollar). Do not use a dollar sign ($), just the value. Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Fixed Column / Relative Row Cell References Now you would like to track the value of all three investments over the same 10-year period in rows 22, 23, and 24. Start by creating a formula in D22 that is like the formula in D13. However, there is one change to make. Knowing that you will want to drag the entire 10-year span in row 22 down to rows 23 and 24, the row reference in the (1+ growth rate) calculation must be able to change as you drag. That is done by removing the dollar sign ($) in front of the row number in the growth calculation. Make sure that is done in cell D22, then drag across to M22 to obtain the values for Investment A. Finally, select all cells from D22 to M22, and then drag down to fill the next two rows. Now you have the values for Investment B and Investment C. Notice the format of the formulas in cells D22:M24. What is the value of each investment in year 10? Investment A: S (to the nearest dollar) Investment B: S (to the nearest dollar) Investment C: S (to the nearest dollar)