I A 2 B 4 Investment A 5 Investment B 5 Investment C 7 B 3...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
I A 2 B 4 Investment A 5 Investment B 5 Investment C 7 B 3 _0 _1 2 3 Investment A B _0 1 2 Investment A 3 Investment B 4 Investment C с 5 6 Formulas 7 Growth Rate (Expected) Year 0 11% $3,000 6% $3,000 4% $3,000 Growth Rate (Expected) Year 0 11% $3,000 D Growth Rate (Expected) Year 0 11% $3,000 6% $3,000 4% $3,000 Year 1 ✓ "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 Year 1 #N/A E Year 1 Formulas #N/A #N/A #N/A #N/A #N/A #N/A Year 2 #N/A Year 2 F #N/A #N/A #N/A ▼ Year 3 #N/A H TNEEAITVE VLLE TNEI ETNLINVE 4 5 Formulas 6 7 "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 valu 8 9 Year 3 G #N/A #N/A #N/A ✓ Year 4 #N/A Year 5 Year 4 FIXED COLUMN / RELATIVE ROW CELL REFERENCES #N/A #N/A #N/A #N/A Year 6 Year 5 #N/A #N/A #N/A #N/A Year 6 J #N/A #N/A #N/A Year 7 #N/A Year 7 K #N/A #N/A #N/A Year 8 #N/A Year 8 #N/A #N/A #N/A L 8 9 O "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." 1 "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." 2 -3 Year 9 #N/A Year 9 #N/A #N/A #N/A M ✓ Year 10 #N/A Year 10 #N/A #N/A #N/A N O P We can tell from this table that there are 8 orders for the MP3 album and that value is in cell C2. A C Order Quantity 1 2 8 3 Concert t-shirt 17 4 Large soft drink 11 A 1 2 3 4 B Item MP3 album A 1 2 3 4 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. B Item MP3 album Concert t-shirt Large soft drink B Item MP3 album C Order Quantity 8 17 11 Concert t-shirt Large soft drink D Unit Cost ($) 11.99 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. C Order Quantity 8 17 11 D Unit Cost ($) 11.99 27.99 4.99 E Total Cost ? ? ? D Unit Cost ($) 11.99 27.99 4.99 E Total Cost =C2*D2 ? ? E Total Cost =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 O 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) (to the nearest dollar) (to the nearest dollar) Investment B: $ Investment C: $ 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 B13 in the formula change it to $B$13 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 Fixed Column / Relative Row Cell References $ Year 4 $ Year 5 $ Year 6 $ Year 7 $ Year 8 $ Year 9 $ Year 10 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. 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 B13 in the formula change it to $B$13 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 Fixed Column / Relative Row Cell References Year 3 Investment C: $ Check My Work $ Year 4 $ Reset Problem Year 5 $ Year 6 $ Year 7 $ Year 8 $ Year 9 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. Year 10 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: $ (to the nearest dollar) Investment B: $ (to the nearest dollar) (to the nearest dollar) I A 2 B 4 Investment A 5 Investment B 5 Investment C 7 B 3 _0 _1 2 3 Investment A B _0 1 2 Investment A 3 Investment B 4 Investment C с 5 6 Formulas 7 Growth Rate (Expected) Year 0 11% $3,000 6% $3,000 4% $3,000 Growth Rate (Expected) Year 0 11% $3,000 D Growth Rate (Expected) Year 0 11% $3,000 6% $3,000 4% $3,000 Year 1 ✓ "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 Year 1 #N/A E Year 1 Formulas #N/A #N/A #N/A #N/A #N/A #N/A Year 2 #N/A Year 2 F #N/A #N/A #N/A ▼ Year 3 #N/A H TNEEAITVE VLLE TNEI ETNLINVE 4 5 Formulas 6 7 "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 valu 8 9 Year 3 G #N/A #N/A #N/A ✓ Year 4 #N/A Year 5 Year 4 FIXED COLUMN / RELATIVE ROW CELL REFERENCES #N/A #N/A #N/A #N/A Year 6 Year 5 #N/A #N/A #N/A #N/A Year 6 J #N/A #N/A #N/A Year 7 #N/A Year 7 K #N/A #N/A #N/A Year 8 #N/A Year 8 #N/A #N/A #N/A L 8 9 O "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." 1 "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." 2 -3 Year 9 #N/A Year 9 #N/A #N/A #N/A M ✓ Year 10 #N/A Year 10 #N/A #N/A #N/A N O P We can tell from this table that there are 8 orders for the MP3 album and that value is in cell C2. A C Order Quantity 1 2 8 3 Concert t-shirt 17 4 Large soft drink 11 A 1 2 3 4 B Item MP3 album A 1 2 3 4 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. B Item MP3 album Concert t-shirt Large soft drink B Item MP3 album C Order Quantity 8 17 11 Concert t-shirt Large soft drink D Unit Cost ($) 11.99 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. C Order Quantity 8 17 11 D Unit Cost ($) 11.99 27.99 4.99 E Total Cost ? ? ? D Unit Cost ($) 11.99 27.99 4.99 E Total Cost =C2*D2 ? ? E Total Cost =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 O 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) (to the nearest dollar) (to the nearest dollar) Investment B: $ Investment C: $ 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 B13 in the formula change it to $B$13 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 Fixed Column / Relative Row Cell References $ Year 4 $ Year 5 $ Year 6 $ Year 7 $ Year 8 $ Year 9 $ Year 10 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. 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 B13 in the formula change it to $B$13 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 Fixed Column / Relative Row Cell References Year 3 Investment C: $ Check My Work $ Year 4 $ Reset Problem Year 5 $ Year 6 $ Year 7 $ Year 8 $ Year 9 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. Year 10 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: $ (to the nearest dollar) Investment B: $ (to the nearest dollar) (to the nearest dollar)
Expert Answer:
Answer rating: 100% (QA)
ANSWER GR Year 0 Year 1 Investment A 6 2800 2968 Investment ... View the full answer
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these finance questions
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-6. On December 12, Irene purchased the building where her store is located. She paid...
-
1. Find the perimeter of each figure. 2. 52 feet 40 feet 52 feet 72 feet 11 kilometers 20 kilometers 35 kilometers
-
During 2016, John was the chief executive officer and a shareholder of Maze, Inc. He owned 60% of the outstanding stock of Maze. In 2013, John and Maze, as co-borrowers, obtained a $100,000 loan from...
-
Explain in what circumstances a decrease in the rate of non-current asset turnover might be a positive indicator.
-
Consider the following cash flow profile and assume MARR is 10 percent/year. a. What does Descartes' rule of signs tell us about the IRR(s) of this project? b. What does Norstrom's criterion tell us...
-
BONDS ISSUED AT FACE VALUE Ito Co. issued the following bonds: Date of issue and sale: ........April 1, 20-1 Principal amount: ..........$300,000 Sale price of bonds: .........100 Denomination of...
-
In June 2022, the Federal Reserved imposed its first 0.75% interest rate hike -- the largest increase since 1994. Starting in January 2023, they dropped back to lower rate increases, but the rate...
-
What is the level of measurement for each of the following variables? a. Student IQ (intelligence quotient) ratings. b. Distances students travel to class. c. Student scores on the first statistics...
-
There are four standard classes of column. Name them.
-
To provide incentives, the NWC made the supervisors in Montreal and at the winter trading posts partners. A negative aspect of this organizational form was Group of answer choices the NWC was cash...
-
Although achieving horizontal and vertical fits are important for high-performance work systems, what do you consider more critical and why?
-
Think of the human resoruces organization that you are part of daily. Discuss how you communicate within this organization from a systems perspective. Describe the human resources organization and...
-
What are some examples of things you can't do on company computers and why?
-
Contrast the differences between straight piecework, differential piece rate, and standard hour plans. Explain where each plan might best be used. What are the reasons behind the different payment...
-
Figure Form Assess 8 shows a longitudinal section of a pressurised flow system (point 1 to point 2). The cross-section changes within that longitudinal section of the pressurised flow system. The...
-
Pappa's Appliances uses the periodic inventory system. Details regarding the inventory of appliances at January 1, purchases invoices during the year, and the inventory count at December 31 are...
-
Jenny earns $34,500 in 2012. Calculate the FICA tax that must be paid by: Jenny: ..............................Soc,Sec. ..................$______________...
-
Brian and Kim have a 12-year-old child, Stan. For 2012, Brian and Kim have taxable income of $52,000, and Stan has nonqualifying dividend income of $4,500 and investment expenses of $250. No election...
-
Leslie is a single taxpayer who is under age 65 and in good health. For 2012, she has a salary of $23,000 and itemized deductions of $1,000. Leslie is entitled to one exemption on her tax return. a....
-
A child receives \($100\),000 as a gift, which is deposited in a bank account earning 6 percent compounded semiannually. If \($5\),000 is withdrawn at the end of each half year, how long will the...
-
The plan was to leave $5,000 on deposit in a savings account for 15 years at 6.5 percent interest compounded annually. It became necessary to withdraw $1,500 at the end of the fifth year. How much...
-
A deposit of $3,000 is made in a savings account that pays 7.5 percent interest compounded annually. How much money will be available to the depositor at the end of 16 years? a. $8,877 b. $10,258 c....
Study smarter with the SolutionInn App