Mini Course

Computers: Beyond the Basics

© Morris Firebaugh



Introduction

A. Disclaimers & Survey
B. Review Basics: The Desktop
C. More MS-Word Features
D. Spreadsheet Fundamentals
E. Spreadsheet Formatting
F. Real-world Example - Loan Schedule




A. Disclaimers & Survey

Disclaimers:

Final Disclaimer:




B. Review Basics: The Desktop

We need to understand just two terms in time and space:

Examples: Storage capacities of

And one characteristic of memory and storage devices is important:

In Windows OSs, the Desktop contains Icons representing computer objects. Let's look at the desktop of my G4 computer.

So, what objects are represented by Icons?

What actions can you perform on these objects?

So how can we organize 51,264 Folders and 271, 402 documents (Data Files) on my G4 hard drive?

Answer: A Hierarchical Filing System (HFS)

So, let's practice working with files and folders.

Finally, we need to become familiar with Window manipulation:

Practice these four functions:



C. More MS-Word Features

The beauty of MS-Word is the enormous range of its capabilities. Consider, for example, the exercises suggested in Word Tutorial 2

Another useful feature is the large library of prototype documents.

  1. Open MS-Word by double clicking the Word Icon,
  2. Select "Letters - Envelopes" from Project Gallery
  3. Select "Cover Letter - Modern" from Letters Gallery
  4. Click O.K. button

Carry out the following exercises:




D. Spreadsheet Fundamentals

History

Consider the basic concept of a spreadsheet worksheet

Worksheet is a rectangular array, or matrix, of cells

Cells may contain information of the following types:

Example: Gradebook for a small class

The fundamental paradigm (mental model or image):

Any cell can be any function of any other cell(s).

Example 1: Gradebook shown above

  1. Open MS-Excel by double clicking
  2. From the Project Gallery, click "OK" to select a Blank Document
  3. Reduce the size of the Worksheet and position it on the right hand side of the screen
  4. Scroll to the gradebook example in Mini2.html and position it on the left hand side
  5. Enter the Labels as shown (alphabetic information)
  6. Enter the raw data values from B3 - E7 (numeric grades)
  7. In cell F3, enter the formula "=SUM(B3:E3)" (without the quotes)
  8. Select cells F3 to F7 by dragging
  9. Copy the formula to cells F4 to F7 by simply typing <control> + D
  10. In cell B9, enter the formula "=AVERAGE(B3:B7)" (without the quotes)
  11. Select cells B9 to F9 by dragging
  12. Copy the formula to cells C9 to F9 by simply typing <control> + R

Example of "What if" capability of spread sheets

What if, at the end of the semester, Brent came to your office and showed you that he had really gotten a "25" on Quiz 3 instead of the "19" you had recorded. Make the correction by entering 25 in cell D3, and watch the corrections ripple through the spreadsheet.

Conclude: Spread sheets are very powerful tools for tabulating and analyzing numeric data.





E. Spreadsheet Formatting

Features to control formatting and enhance the appearance of spreadsheets





E. Real-world Example - Loan Schedule

Write an EXCEL program to take three input parameters for an auto loan, the Amount of the loan, the Annual Interest, and the Period of the loan, and then compute the monthly payment and the repayment schedule indicating the Interest, Principle, Total Interest, and Remaining Principle. Below is a suggested format for the spreadsheet, but please show all $ values to the nearest cent;. Please use the input parameters shown in B4..B6.

[Remember: Type in your cell labels (Amount of loan, etc.) and contents in the same exact location as shown on the image--if you don't, your formulas will not work!]

Cell D5 should contain =PMT(B5/12,B6*12,-B4)--This formula must be in this exact order

Cell A9 should contain 1

Cell B9 should contain =B4*B5/12 (First month's interest = amount of loan * annual intest rate / 12)

Cell C9 should contain =D5-B9 (Principle = Monthly payment - Interest)

Cell D9 should contain =B9

Cell E9 should contain =B4-C9 (Amount of loan - Principle)

Cell A10 should contain =A9+1 (This will automatically number the months for you)

Cell B10 should contain =E9*$B$5/12 (Second month's interest = previous month's remaining principle * annual interest rate / 12)

Cell C10 should contain =$D$5-B10 (Principle = annual interest rate - interest)

where $B$5 and $D$5 are "absolute addresses"]

Cell D10 should contain =D9+B10

Cell E10 should contain =E9-C10

Copy the contents of cells B10..E10 down to the 36th. month on your sheet.

Note:

This is a very realistic and practical application of spreadsheets to monitor your finances and compute alternative investment scenarios.




Updated February 26, 2004