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:
- Worksheets
- Autoincrement
- Chart making
Hand In:
- A print out of the Investment worksheet.
- A print out of the modified Toy store worksheet.
- A chart for the Toy store worksheet, showing Net Profit by item
(i.e., the chart should show the net profit for every item in the toy
store).
Instructions:
In order to get started, we first introduce two new
Excel utilities: Autoincrement, and Chartmaking,
which are explained below in turn.
- Auto-increment: This is an offshoot of the generalized copy
function command. You can copy a function by selecting the cell you wish
to copy from, and then moving the mouse to the little box in the lower
right hand corner of the border surrounding the cell. When the mouse
changes to a crosshairs, you can drag the contents of the cell to cover
a range of cells adjacent to the original cell. In addition to
functions, this technique can be used to quickly create a series of
data, by entering the first two terms, and then copying the second term.
This technique can also be used to copy the contents of blocks of cells.
Experiment with selecting several cells, and see what happens when you
drag/copy them.
- Chartmaking: To create a chart of your data, select the
data you wish to chart. Then click on the Chart Wizard button
(the little button at the top of the window that looks like a chart). You
can then use the mouse to select the area to put the chart into. The
Chart Wizard will open up five menus to help you customize the
chart, described in order below:
- This menu is used to select the
data you wish to chart. The default value will be the areas
that you had previously selected (assuming you have already done so).
You can also type in new values and select different regions with the
mouse. Note that you can select multiple non-adjacent regions by holding
down the [Alt] button while you select them.
- This menu helps you customize which type of
chart you wish to use. Each button represents a different
way to chart the data. It is possible to go back later if you don't like
how the chart turns out.
- This menu also customizes a chart
type.
- This menu will let you customize how the
data is displayed by the chart. You can change things such
as how the computer labels the axis, and how the data is arranged before
it is charted. Note that sometimes to get the data just right, it will be
necessary to go back to the first window and change how the data is
selected. It may also even be necessary to get out of the Chart
Wizard application in order to sort the data in the worksheet, if
that is desired.
- This menu allows you to customize the
labels on the chart. You can do things like add a title
and/or change the labels for the X- and Y-axis.
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.
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:
- The payment number (1-24). (There should be 24 rows, covering 2 years).
- The date of the payment. (Assume it starts Jan. 1996).
- The old balance. (The new balance from the previous line, starting
from 0).
- The interest gained. (This is O*I(1/12), where O = old
balance, and I = interest).
- 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:
- Deposit: $100
- Interest: 10%
- Threshold value: $1000
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