MATH 105 Lab -- Spreadsheet basics: algebra and graphing
This lab covers some basic algebra and graphing skills. You will enter formulas, create Text Boxes, use the Solver, and use the Chart Wizard. In Part I you will create a cover page to use for your labs. In Part II you will build simple spreadsheet templates and use a Text Box. The Solver will be used in Part III and in Parts IV and V you will build a graph and find the trend line for the data. The important lesson of this lab is learning to use these spreadsheet features.
Requirements |
Place name in upper right corner of each page. (cell G1 is fine) Save your work frequently - you never know when the system might freeze and unsaved work lost. BRING your OWN disk to each lab. It would also be smart to ALWAYS carry a spare diskette. |
Part I: Create a title page.
Save this worksheet.
NOTE: By changing the date you can reuse it to make a
cover sheet for each of the labs.
Print the cover page for Lab #1.
Part II: Spreadsheet algebra
In this part you are to use the basic arithmetic operations (+, -, *, /, and ^) to build two spreadsheet models. Start with a new worksheet.
|
NOTE: Review the order of operations and use parentheses liberally. The most common sources of error in using calculators or computers is failure to place parentheses properly. |
Problem A. $2000 was borrowed for 1 month at an annual simple interest rate of 6%. How much interest is to be paid and how much is owed after 1 month?
The formulas are I=Prt and A=P(1+rt) where I is the interest, A is the repayment amount, P is the loan principal, r is the annual interest rate, and t is the term of the loan (in years or fractions thereof). The strategy for building a template to model the problem is to place the independent variables (P, r, and t) in one "area" of the spreadsheet and place the dependent variables (I and A) that are calculated in another area.
In the template below the variables are identified by the labels in column A and the specified values of the independent variables are supplied in column B.
| A | B | C | |
| 1 | |||
| 2 | Part II | ||
| 3 | Problem A | ||
| 4 | Principal | 2000 | |
| 5 | Rate/yr | 0.06 | |
| 6 | term | =1/12 | |
| 7 | |||
| 8 | Interest | =B4*B5*B6 | |
| 9 | Amt Due | ||
Notes: In cell B6, 1 month is represented by 1/12 because the term is in years. Since the numerical value is needed the expression must start with an "=" sign. On your own, enter the appropriate formula for A (the Amount to be repaid) in cell B9.
In rows 12-16 place a Text Box (see instructions below) with the following information:
(i) state the amount of interest and the amount to be repaid for the loan,
(ii) change the term of the loan to 18 months and give the amount of interest and the
amount to be repaid for this term.
TEXT BOXES.
To enter text in a spreadsheet you could just type sentences into cells,
but that's like using a typewriter instead of a wordprocessor. And it
is not easy to move around or reposition. A better solution is to
use a Text Box. Here's how. If the Drawing toolbar is not open,
click on the DRAWING icon 
on the Shortcut toolbar.
This will open it. Click the TextBox button 
in the Drawing Toolbar
then use the mouse curser to draw a box for the text of the size desired
wherever you want to place it. Don't worry if it is too small or too
big. You can resize later. Place your discussion in the Text Box. (Clicking
the right mouse button when the Text Box is selected and selecting Format
brings up properties (like Font size) of the text box that you can change.)
Problem B. $1000 is deposited in a savings account that pays an annual rate of 6%, compounded monthly. Determine the value of the account in 2 1/2 years (= 30 months).
The formula is A=P(1+r/k)n where the independent variables are the deposit P, the annual rate r, the compounding frequency k times per year, and the total number n of compounding periods. The final Account balance A is the dependent variable.
Build a template (similar to the one in Problem A) to calculate the final account balance and to give the total interest the bank pays to the account . Write your answers in a text box. [Note: Be careful with parentheses and recall that "^" computes powers.]
Print the worksheet containing Part II of the lab. Be sure to include the templates for both Problems A and B as well as the text boxes. Be sure to save these templates for use in Part III.
Part III: Using the Solver
We want to use the Solver tool to find an independent variable when the value of the dependent variable is given. To keep from messing up Part II copy the templates from Part II to a new page of the spreadsheet. For convenience place them in the same cell locations.
Problem III.A. Joan's annual salary increased from $25,207 to $27,311. What percentage increase was this?
Here is the solution process. Use the (Copy of) the template from Problem A, Part II since this is really a simple interest problem. [You might want to change some of the labels for the variables.] Enter values for the INDEPENDENT variables that you know; namely, enter 25207 in cell B4 and =1 (for one year) in cell B6. Place a guess in cell B5 (the annual rate). Now, let's use the Solver Tool to find the value of B5 to solve the problem.
From the Menu bar, select Tools, then Solver.There you have it! The annual rate that produced the salary increase is in cell B5.
In the Solver Parameters dialog box set the following values:Set Target Cell: B9
Equal to: select "Value of:" radio button and enter 27311 in box
By Changing Cells: B5
Click Solve and then Ok in the Results box to Keep Solver Solution.
|
Note: For best success using the Solver use an initial guess that is not too far from the solution. |
On your own solve the following problem using the template for compounding
created for Problem B, Part II.
Problem III.B. Suppose $1000 is deposited in a savings account that pays an annual rate
of 6%, compounded monthly. How long will it take for the value to double?
Write a statement in a text box on the worksheet that gives the number of
years and months it takes for the account to double.
Print the worksheet for Part III.
Part IV: Graphing
The goal of this Part is to use the Chart Wizard to graph data. Start with a new page.
Data: The revenues (in millions) at Goodwill of Lower SC since 1997 are as follows: 1997 (4.3), 1998 (6.5), 1999 (9.8), 2000 (11.2), 2001 (12.5), 2002 (14.7), and 2003 (17.1).There are two steps in constructing a graph: building an x vs y table and using the Chart Wizard.
Building an x vs y Table of values
To form a table of values we place the independent variable (for the Domain)
in column A, then add the corresponding revenue (the dependent variable) in
column B.
Enter the labels and values in the cells indicated below to get started.
| A | B | |
| 1 | Year | Revenue |
| 2 | 1997 | 4.3 |
| 3 | 1998 | 6.5 |
| 4 | 1999 | 9.8 |
| 5 | 2000 | 11.2 |
| 6 | 2001 | 12.5 |
| 7 | 2002 | 14.7 |
| 8 | 2003 | 17.1 |
Note: sometimes you may need to use a formula to compute the values that are placed in column B.
Graphing an XY table
We walk through the steps of using the
ChartWizard to form a scatter diagram for our XY table. These instructions assume the
X-values are in the cells A2..A8 and the Y-values are in the cells B2..B8. Consult the online
Graph Tutorial
if needed.
1. Highlight the X-Y table of values you want to graph, i.e., A2..B8. (Remember you can do this by placing the curser in cell A2, holding down the Shift key, and using the arrow keys to move to cell B8.)
2. Click the ChartWizard icon 
on the Shortcut toolbar. The
ChartWizard dialog box should appear.
3. Complete the following 4 steps of the ChartWizard. Click "Next" to go on and "Back" to change a previous step.
| Step 1. | Select XY (Scatter) as the Chart Type. For the SubType choose the subtype in the 1st row: Scatter, Compares pairs of values. |
| Step 2. | Verify the Data Range is A2:B8 |
| Step 3. | Options such as creating titles are set in this step. Select Legend and uncheck the Show Legend box. (We have only one series and this marker takes up space.) |
| Step 4. | In Chart Place check As Object in Sheet 1 (or whatever the worksheet is named). This will allow the graph to be embedded alongside the rest of the data in the worksheet. Click Finish now. |
Well, that's it! If you are not happy with the graph placement, select the chart and move it. You can also resize by placing the curser over a side or corner, holding down the left mouse button, and moving the curser.
Now, on your own: modify the steps above to construct a
column graph. Hint: start by only highlighting cell range B2..B8; then,
in step 2, after checking that B2..B8 is the data range, go to the Series
tab and add the cell range A2..A8 as the Category (X) axis labels.
When you save the worksheet the graph is automatically saved with
it. To print the graph and data be sure the graph is not selected.
Print the Part IV worksheet now.
Part V: Adding a Trendline
In Part IV we constructed a scatter diagram for Goodwill's annual revenue since 1997. To use this data to make projections we need to approximate it by an equation. Below we find the "best fitting line".
The Procedure: Right click on one of the data points in the scatter diagram. A menu of Chart Options pops up. Select "Add Trendline..." from the list. On the Type tab choose Linear as the "Trend/Regression Type". Now, select the Option tab and check the box "Display equation on chart". Click "OK" to add the trendline. You should select the equation on the chart and move it to a place where it is easy to read.
Now that you have obtained the equation for the line of best-fit
use it to estimate the revenues for Goodwill of SC for 2004.
Hint: place the independent variable, 2004, in a cell and enter
the formula for predicting the revenue in an adjacent cell.
Place your answer in a text box, print Part V and save the lab.