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 create a graph. 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.

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.

A. First move the curser to cell C18 and type:
B. Move to cell C20 and enter
C. Add your name in row 22 and the date in row 24 to finish the 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. $3000 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
2 Part II
3 Problem A    
4 Principal 3000  
5 Rate/yr 0.06  
6 term =1/12  
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) give the amount of interest and the amount to be repaid if the term of the loan to for 2 months. 18 months. (Change the input to find the answers.)

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. Click the INSERT tab of the Excel Ribbon at the top. The TextBox icon is located in the Text section of the Insert Tab. Click the TextBox icon , then use the mouse curser to draw a box for the text of the size desired where you want to place it. Don't worry if it is too small or too big. You can resize or reposition later. In fact, you should normally reposition after viewing Print Preview to the page looks good. Place your discussion in the Text Box. (Holding 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 Excel Ribbon, select the Data tab, then Solver from the Analysis section.
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.
There you have it! The annual rate that produced the salary increase is in cell B5.

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 create a graph using the Chart options from the Insert section. Start with a new page and enter the data below.

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 selecting Chart properties.

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 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.

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. Select the Insert tab, then the little arrow below Scatter icon in the Charts section. A group of predefined scatter types should appear.

3. Click the subtype with Markers only then the graph should appear. Very often the values on the x-axis are not correct.
Here is how to Edit the Horizontal (Category) axis Labels.

Step 1.Right click the Horizontal Axis and select Format Data Series from the pop-up menu.
Step 2.Choose the Select Data Source option. A dialog box should pop-up with a Horizontal Axis Labels section on the right.
Step 3.Click Edit, add the proper labels, and click 'OK'.

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: Construct a column graph by slightly modifying the steps above. Hint: start by only highlighting cell range B2..B8; then, use 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.