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 ($) Total Cost 2 MP3 album 8 11.99 ? 3 Concert t-shirt 17 27.99 ? 4 Large soft drink 11 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 C D E 1 Item Order Quantity 2 MP3 album 8 Unit Cost ($) 11.99 Total Cost ? 3 Concert t-shirt 17 27.99 ? 4 Large soft drink 11 4.99 ? 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 C D E 1 Item Order Quantity 2 MP3 album 8 Unit Cost ($) 11.99 Total Cost 3 Concert t-shirt 17 27.99 4 Large soft drink 11 4.99 =C2*D2 ? ? 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. D E A B C 1 Item Order Quantity 2 MP3 album 8 3 Concert t-shirt 17 Unit Cost ($) 11.99 27.99 Total Cost 4 Large soft drink 11 4.99 =C2*D2 =C3*D3 =C4*D4 In the given spreadsheet, three investments are listed in rows 4-6. Column B contains the expected growth rate for each investment as a percentage. Calculate the expected value of Investment A in cell D4 by multiplying its value in Year 0 by the quantity (1 + Growth rate). Then drag cell D4 down to D6 to get the other two investment values after one year given different growth rates and place those values below: Investment A: $ (to the nearest dollar) Investment B: $ (to the nearest dollar) Investment C: $ (to the nearest dollar) D E F G H RELATIVE CELL REFERENCES J K L M N A B 1234567 Growth Rate (Expected) Year 0 Year 1 Formulas 4 Investment A 12% $2,200 #N/A 5 Investment B 6 Investment C 1% $2,200 3% $2,200 #N/A #N/A Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 8 "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)." 9 10 ABSOLUTE CELL REFERENCES 11 12 Growth Rate (Expected) 13 Investment A 12% Year 0 $2,200 Year 1 Year 2 Year 3 Year 4 14 15 Formulas #N/A #N/A #N/A #N/A #N/A 16 #N/A #N/A #N/A #N/A 17 "Start in column D, then move across allowing the column to change, and multiply the preceding value by its FIXED growth rate in cell $B$13 (which is 1 plus the percentage expected growth) to get the current value." #N/A 18 19 FIXED COLUMN / RELATIVE ROW CELL REFERENCES 20 Growth Rate 21 (Expected) Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 22 Investment A 23 Investment B 24 Investment C 12% 1% 3% $2,200 $2,200 $2,200 25 26 Formulas #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 27 28 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 29 30 "Start in column D, then move across, and multiply the preceding value by its growth rate in cell $B22 (which is 1 plus the percentage expected growth) to get the current value." 31 "By changing the growth rate cell from $B$22, etc. to $B22, the row of the growth rate is allowed to change yet remain in column B while filling down to the other two Investments." 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 0 P 0 R S T U V W

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 ($) Total Cost 2 MP3 album 8 11.99 ? 3 Concert t-shirt 17 27.99 ? 4 Large soft drink 11 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 C D E 1 Item Order Quantity 2 MP3 album 8 Unit Cost ($) 11.99 Total Cost ? 3 Concert t-shirt 17 27.99 ? 4 Large soft drink 11 4.99 ? 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 C D E 1 Item Order Quantity 2 MP3 album 8 Unit Cost ($) 11.99 Total Cost 3 Concert t-shirt 17 27.99 4 Large soft drink 11 4.99 =C2D2 ? ? 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. D E A B C 1 Item Order Quantity 2 MP3 album 8 3 Concert t-shirt 17 Unit Cost ($) 11.99 27.99 Total Cost 4 Large soft drink 11 4.99 =C2D2 =C3D3 =C4D4 In the given spreadsheet, three investments are listed in rows 4-6. Column B contains the expected growth rate for each investment as a percentage. Calculate the expected value of Investment A in cell D4 by multiplying its value in Year 0 by the quantity (1 + Growth rate). Then drag cell D4 down to D6 to get the other two investment values after one year given different growth rates and place those values below: Investment A: $ (to the nearest dollar) Investment B: $ (to the nearest dollar) Investment C: $ (to the nearest dollar) D E F G H RELATIVE CELL REFERENCES J K L M N A B 1234567 Growth Rate (Expected) Year 0 Year 1 Formulas 4 Investment A 12% $2,200 #N/A 5 Investment B 6 Investment C 1% $2,200 3% $2,200 #N/A #N/A Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 8 "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)." 9 10 ABSOLUTE CELL REFERENCES 11 12 Growth Rate (Expected) 13 Investment A 12% Year 0 $2,200 Year 1 Year 2 Year 3 Year 4 14 15 Formulas #N/A #N/A #N/A #N/A #N/A 16 #N/A #N/A #N/A #N/A 17 "Start in column D, then move across allowing the column to change, and multiply the preceding value by its FIXED growth rate in cell $B$13 (which is 1 plus the percentage expected growth) to get the current value." #N/A 18 19 FIXED COLUMN / RELATIVE ROW CELL REFERENCES 20 Growth Rate 21 (Expected) Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 22 Investment A 23 Investment B 24 Investment C 12% 1% 3% $2,200 $2,200 $2,200 25 26 Formulas #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 27 28 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 29 30 "Start in column D, then move across, and multiply the preceding value by its growth rate in cell $B22 (which is 1 plus the percentage expected growth) to get the current value." 31 "By changing the growth rate cell from $B$22, etc. to $B22, the row of the growth rate is allowed to change yet remain in column B while filling down to the other two Investments." 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 0 P 0 R S T U V W

Financial Management: Theory & Practice

16th Edition

ISBN:9781337909730

Author:Brigham

Publisher:Brigham

Chapter7: Corporate Valuation And Stock Valuation

Section: Chapter Questions

Problem 26SP

See similar textbooks