Computer Science and Computer Engineering 115
Solve it with the Computer!
Spring 2000

Lab 6: Due Tuesday, March 28, 2000

15 points

You may not have enough time during the organized lab time to finish this exercise. You will need to go to the U.C. user room at another time to finish it.

Goal:

Charts are often useful for displaying data. Learn the basics of building charts with a single data series in Excel. The lab also works with financial functions.

What to do before the lab:

Read through page 144. Different types of charts are shown on pages 135-139. Figure 4.4, page 141, shows two ways a chart can be added to a workbook: (a) an embedded chart is on the same page as the data and (b) a chart that is on its own chart sheet. The easiest way to create a chart is using the chart wizard. (It is the third tool left of the zoom box in the standard toolbar.) The 4 dialog boxes needed to create a chart are shown in Figure 4.5 on pages 142-143. The picture on page 144 shows two optional tool bars that can be used to enhance a chart.

The exercise (what to do during the lab):

Part I: Making charts

Complete the Hands-On Exercise 1 on pages 145-153. Print the page with the embedded chart BEFORE and AFTER step 5 and the chart page after step 8. When you have questions about the meaning of the different types of charts or about the steps using the chart wizard, refer back to the first part of the chapter.

Part II. Who wants to be become a millionaire?

You would like to be a millionaire when you retire. Unfortunately winning the million on TV or being willed it by Bill Gates is unlikely so you decide to set up a retirement fund. You will deposit a fixed amount every month starting at age 20, 25, 30, ..., or 60. Create a spreadsheet that calculates the monthly payment needed to have an investment worth $1,000,000 when you retire at age 65 assuming you earn 9% on your investment. The user should be able to change the interest rate and amount desired at retirement. Then create an embedded line chart showing the monthly payment vs the starting age (chart 1). Also create a bar chart (chart 2) showing the same information but on its own chart page.

The charts should have chart, category and value titles. Because there is only one data series, legends are unneeded. Make sure spreadsheet includes "Prepared by: Your name". Add a text box to the separate chart page with the same information.

Print the resulting page include the table and chart 1. Then change the data to show the monthly payments needed to save a $500,000 if the investments earn 10%. You should not have to change any formulas. Print copies of the formatted sheet, chart 2 and of the formulas.

Hints:
Beginning
age		  
 Investment
years		Monthly
payments
2045$135.05
2540$213.61

  1. Create a table with the following columns: "Beginning age", "Investment years", and "Monthly payments".
  2. The row labels are numbers. To simplify chart creation, make the cell at the intersection of the row labels and column label blank.
  3. The first few lines of the table might look like that shown on the right.

Optional Extra Credit

Create an embedded XY scatter chart (chart 3) for the data in part II. This time show the monthly payment vs. the number of investment years.

What to hand in:

Turn in your output: It includes 2 formatted sheets and one chart sheet from part I and two formatted sheets, one chart sheet and one formula sheet from part II. Make sure you have labeled each page.

Lab06.html,2/9/2000