Introduction:

In this lab we will learn more uses for Microsoft Excel, and learn about its chartmaking capabilities. For this, you will be creating a new Investment spreadsheet that demonstrates these abilities. In addition to this, you will also be modifying the Toy store spreadsheet introduced in the previous exercise.

New Topics:

Hand In:

Instructions:

In order to get started, we first introduce two new Excel utilities: Autoincrement, and Chartmaking, which are explained below in turn.

If you are still in need of further instruction on these two topics (Auto-increment and Chartmaking) or any of the previous basic Excel utilities, refer back to the Excel Intro page.

Here is a small file of spreadsheet data on Sports Company Sales to be used as an in-class charting exercise.

Exercise:

Part I

You will now be asked you to create a new worksheet for an investment account. Your sheet should tally a growing deposit with interest over an undetermined number of months. You should do the following: deposit a set amount each month until the amount in the account reaches a predetermined target amount. At that point, you may just let the interest accumulate.

First, create a worksheet to calculate how the investment account will perform. You should be able to enter data for the monthly deposit amount, the interest rate, and a threshold value. The worksheet should have the following columns:

  1. The payment number (1-24). (There should be 24 rows, covering 2 years).
  2. The date of the payment. (Assume it starts Jan. 1996).
  3. The old balance. (The new balance from the previous line, starting from 0).
  4. The interest gained. (This is O*I(1/12), where O = old balance, and I = interest).
  5. The new balance. (This is the old balance + the interest gained + a monthly payment, if the old balance is below the threshold value. Otherwise, it is just the old balance + the interest gained).

Print out the worksheet with the following values:

Part II

For the second part of this exercise, you will be working with the worksheet you created in Exercise 1. First, add 1 to the number of Puppies sold, and also to the number of Yo-Yos sold. Then create a chart displaying how much each item contributes to the Net Profit. You should choose an appropriate type of chart to display this kind of data.

Print out the altered Toy store worksheet and the chart.

Additional Exercises

Return to 1001 Home Page