A Microsoft Excel spreadsheet is laid out in a grid of columns and rows. The columns are labeled with letters: A$,$ B$,$ C$...$ while rows are labeled with numbers: $1,2,3...$ and so on$.$ A cell is where a column and row intersect. For example, where column B intersects with row $4,$ the cell is labeled B$4.$ You can think of a cell as an address on a map.

The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below.

Open spreadsheet

Relative Cell References

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$ MP$3$ album $811\mathrm{.}99?$

$3$ Concert t$-$shirt $1727\mathrm{.}99?$

$4$ Large soft drink $114\mathrm{.}99?$

We can tell from this table that there are $8$ orders for the MP$3$ album and that value is in cell C$2.$

A B C D E

$1$ Item Order Quantity Unit Cost $($$$)$ Total Cost

$2$ MP$3$ album $811\mathrm{.}99?$

$3$ Concert t$-$shirt $1727\mathrm{.}99?$

$4$ Large soft drink $114\mathrm{.}99?$

Next, we would like to calculate the Total Cost for all MP$3$ orders. To do that we would multiply the Order Quantity by the Unit Cost; $8*11\mathrm{.}99.$ That can be done in cell E$2.$

A B C D E

$1$ Item Order Quantity Unit Cost $($$$)$ Total Cost

$2$ MP$3$ album $811\mathrm{.}99=$C$2*$D$2$

$3$ Concert t$-$shirt $1727\mathrm{.}99?$

$4$ Large soft drink $114\mathrm{.}99?$

Finally, we can select cell E$2,$ grab the drag handle, and then drag down to E$4$ 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 D E

$1$ Item Order Quantity Unit Cost $($$$)$ Total Cost

$2$ MP$3$ album $811\mathrm{.}99=$C$2*$D$2$

$3$ Concert t$-$shirt $1727\mathrm{.}99=$C$3*$D$3$

$4$ Large soft drink $114\mathrm{.}99=$C$4*$D$4$

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 D$4$ by multiplying its value in Year $0$ by the quantity $(1+$ Growth rate$).$ Then drag cell D$4$ down to D$6$ to get the other two investment values after one year given different growth rates and place those values below:

Investment A: $fill in the blank $2$

$($to the nearest dollar$)$

Investment B: $fill in the blank $3$

$($to the nearest dollar$)$

Investment C: $fill in the blank $4$

$($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 B$13.$ First, calculate the value in Year $1($D$13)$ using the same technique in Part A$.$

If you try to drag D$13$ to the right to fill in the remaining years, you will get some very strange numbers! That is because the growth rate cell B$13$ is changing as you drag. However, you need that cell to say fixed in place for all the formulas as you drag to fill E$13$ through M$13.$ The way to fix cell B$13$ in place is using an absolute cell reference. Instead of B$13$ 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$ Year $4$ Year $5$ Year $6$ Year $7$ Year $8$ Year $9$ Year $10$

$fill in the blank $5$

$fill in the blank $6$

$fill in the blank $7$

$fill in the blank $8$

$fill in the blank $9$

$fill in the blank $10$

$fill in the blank $11$

$fill in the blank $12$

$fill in the blank $13$

$fill in the blank $14$

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 D$22$ that is like the formula in D$13.$ 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 D$22,$ then drag across to M$22$ to obtain the values for Investment A$.$

Finally, select all cells from D$22$ to M$22,$ 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 D$22$:M$24.$ What is the value of each investment in year $10?$

Investment A: $fill in the blank $15$

$($to the nearest dollar$)$

Investment B: $fill in the blank $16$

$($to the nearest dollar$)$

Investment C: $fill in the blank $17$

$($to the nearest dollar$)$