Section 2.1 Introduction to Spreadsheets
Objectives: Section 2.1 Introduction to Spreadsheets
Students will be able to:
Perform basic calculations on a spreadsheet
Use cell references and the fill-down feature


Subsection 2.1.1 Basic Calculations
To do a calculation on a spreadsheet, type an equal sign before the operation. This lets the program know that you want it to calculate the result. When you press enter, you will see the result.Example 2.1.1.
To add 3 + 4, enter
=3+4
To subtract 100-76, enter
=100-76
To multiply 4 times 18, enter
=4*18
To divide 0.05 by 12, enter
=0.05/12
To calculate 525,525, enter
=5^25

Example 2.1.2.
Your bill at a restaurant is $35.75 and you want to leave an 18% tip. How much would you add to the bill?
To work with a percentage, we need to convert it into a decimal first, and then multiply it by the base amount.
In a spreadsheet we would type
=0.18*35.75
and get a result of $6.44, rounded to the nearest cent. You would leave a tip of $6.44.
Subsection 2.1.2 Cell References
One of the powerful things about spreadsheets is using a cell reference, such as C5 in a calculation. When you use a cell reference, the values will automatically update if any of the referenced values change. Letβs make a spreadsheet for the percentage tip example above. We calculated an 18% tip on a bill of $35.75. We might want to tip 18% in general, but our bill will change values. We labeled the first column Bill Amount and the second column Tip. The amount of $35.75 is entered in cell A2. Then when we write our formula in B2, we want to calculate 18% of A2. That way if the number in A2 changes, our tip will automatically update.
=0.18*A2is entered in B2 which gives a result of $6.44 when you hit enter.

Subsection 2.1.3 Cell Formatting
We can also format cells A1 and B1 to show dollar signs by clicking on the dollar sign in the number formatting menu.Subsection 2.1.4 Fill-Down Feature
The fill-down feature is very useful for making tables. This allows us to copy values or formulas to save time. Letβs make a tipping reference table with values from $10, to$100, in increments of $10. First, we will enter two values in column A to establish the pattern. Then select those two cells and you will see a small square in the lower right corner. Drag that square down until you get to $100.



Subsection 2.1.5 Formulas
Spreadsheets have many useful built-in formulas. We will introduce some of the financial formulas in this chapter. Here are some of the formulas we will use:=FV
to calculate the future values of an investment=PV
to calculate the deposit needed for a desired future balance=PMT
to calculate a loan or savings plan payment=EFFECT
to calculate the effective rate of an account and compare accounts
Exercises 2.1.6 Exercises
1.
Convert 4/74/7 to a decimal
=4/7
which gives approximately 0.571429
2.
Convert 16% to a decimal
=16%
which gives 0.16
3.
Add 8 and 19
=8+19
which gives 27
4.
Find the difference of 230 and 78
=230-78
which gives 152
5.
Multiply 12 and 9
=12*9
which gives 108
6.
Divide 0.09 by 52
=0.09/52
which gives approximately 0.001731
7.
Calculate 8383
=8^3
which gives 512
8.
Your bill at a restaurant is $55.75 and you want to leave a 20% tip. How much would you add to the bill?
=55.75*20%
which gives 11.15 or eleven dollars and fifteen cents
9.
You leave a tip for $7.50 for a bill at a restaurant that is $44.50. What percent tip did you leave?
=7.50/44.50
which gives approximately 0.168539, or approximately 16.8539%
10.
In Column A use the fill down feature to build a spreadsheet starting with $5 and ending at $125, in increments of $5. In Column B write a formula with a cell reference to calculate a 15.5% tip on the amount in Column A. Use the fill down feature to complete your table.
See the table below:
Note, the entry in cell B1 is =15%*A1
. (Columns A and B are given dollar formatting)

11.
Imagine a certain savings account started out with a balance of $5250.00 on day-one, and today has a current balance of $5780.23
Exactly how much more money does the account have today, compared with day-one?
Rounding to the nearest tenth of a percent: By what percentage amount has the account balance grown?
If instead, the bank balance today was exactly double the starting balance, then by what exact percentage amount would the bank balance have grown?
If the bank balance today had instead grown by 15.5% since day-one, then what would be the exact amount of todayβs balance?
=5780.23-5250
which gives 530.23 dollars=530.23/5250
which gives approximately 0.100996, or approximately 10.1%Exactly 200%
=5250*115.5%
which gives exactly 6063.75 dollars
12.
Imagine that at the start of a certain month, you will make an opening deposit of $500 into a savings account, and you will then leave the account alone (meaning you will make no further deposits or withdrawals). Also, for this account: Every month after the opening deposit, the amount in the account will grow to be 101% of its previous monthβs balance.
Use a spreadsheet to enter 500 in cell A1. Using a formula and a cell-reference: Compute in cell A2, the amount in the account after one month has passed. Then using the fill down feature, continue the pattern for another eleven full months (you should end at cell A13). Format all the cells to show dollar signs. What is the amount in the account after one year?
Now continue the pattern in column A of your spreadsheet to extend for a second full year (you should end at cell A25). What is the amount in the account after two years?
What overall percentage growth occurred in the account between the opening deposit and one year later? (Compute using a formula and cell references)
What overall percentage growth occurred in the account between the end of year one, and the end of year two? (Compute using a formula and cell references)
(Challenge) The annual percentage growth that you found in part (d) for the second year, should be identical to the annual percentage growth that you found in part (c) for the first year. Can you mathematically explain why this is true? Do you think this pattern of identical overall annual percentage growth would continue, if you extend the pattern for even more years?
-
See the table at the bottom for part a and part b.
After 1 year, the account holds $563.41
-
See the table at the bottom for part a and part b.
After 2 years, the account holds $634.87
=A13/A1
which gives \(\approx 112.6825\%\) growth=A25/A13
which gives \(\approx 112.6825\%\) growth (same)Each starting value increases mathematically by a factor of \((1.01)^{12}\) each year, which is \(\approx 112.6825\%\text{.}\) So yes, this pattern must continue indefinitely into future years.

13.
Imagine that at the start of a certain year, you will deposit $1000.00 into a savings account, and then you will leave the account alone. Each year after the opening deposit, the amount in the account will grow to be 103% of its previous yearβs balance.
After two years, the account balance will have experienced two growth amounts of 103%. You can find this account balance amount here, with the spreadsheet computation
= 1000 * (103%) * (103%)
. Perform this computation in a spreadsheet and write the balance that you find.Now enter the spreadsheet computation
= 1000 * (103%)^2
. Notice that the result here, which involves using a power, gives the same answer as you found in part (a). Comparing the two spreadsheet computations: Explain why they give the same result.Using the pattern in part (b) above, and carefully choosing the power: Compute the balance that will be in the account fifteen full years after the account was originally opened. (Round to the nearest cent)
(Challenge) Make a spreadsheet that shows the account balance each individual year for 30 years. From the date of the opening deposit: What minimum number of full years will you have to wait, until the balance finally exceeds twice its opening deposit amount? (Use cell references, the fill down feature, and dollar formatting)
(Challenge) Imagine the opening balance of the account was $5000.00 instead of $1000.00 (and everything else about the account stays the same). Make a similar spreadsheet as you did in part (d), and using this spreadsheet, find the minimum number of full years you will have to wait this time, until the balance finally exceeds twice its opening deposit amount. How does this answer compare with your answer in part (d)? Do you think your answer would be the same here, for any positive opening balance you may choose for the account?
=1000*103%*103%
which gives 1060.90 dollars.=1000*(103%)^2
gives the same result of 1060.90 dollars, because raising 103% to the second power means the same as multiplying 103% by itself two times.=1000*(103%)^15
which gives 1557.97 dollars (to the nearest cent).-
Refer to the table at the bottom for part d and part e.
Note the entry in cell B3 here is
= B2*103%
and the remaining cells are computed using the fill down feature.You will have to wait a minimum of 24 full years, in each case, in order for the balance to finally exceed twice the opening deposit amount.
Since \((103\%)^{23} \lt 2 \lt (103\%)^{24}\text{,}\) the minimum number of full years until the opening deposit doubles must be the same here, for any positive opening balance that we may choose for this account.
