Spreadsheets

Unit 3: Advanced Spreadsheet Techniques

Objectives

At the end of this unit you will be able to:

• Create an amortization table.
• Sort columns and rows in a spreadsheet.
• Describe how a spreadsheet can be used to perform what-if analysis and explain how that can help you make better decisions.

Lecture

Advanced Spreadsheet Techniques

Skills

The skills covered here are superset of the skills covered on pages IP1-1 through IP1-24 in Toliver and Johnson (Integrated project 1).

• Lesson 8: Planning a Large Spreadsheet
• How do you figure out what to do first, second, etc.?
• Lesson 9: Using Spreadsheets in Decision Making
• How do you use a spreadsheet in decision making?
• How do you sort rows of data in a spreadsheet?
• How do you use the Choose and Vlookup functions to create lookup or reference tables?
• How do you split the spreadsheet window to view two different sections of the spreadsheet?
• Lesson 10: A Useful Example – A Loan Amortization Table
• How do you create an amortization table using the Pmt function?
• Lesson 11: Integrating Word and Excel
• How do you copy Excel data into a Word document?
• How do you link Excel data to a Word document?

Concepts

An understanding of certain concepts is necessary to succeed in the lessons. The following concepts are defined where applicable in the lesson text. You may also click on any concept listed here to see it defined.

1. What is a "what if" model?
2. What kinds of "what if" models are there?
3. How is a "what if" model different from a regular spreadsheet?
4. Why does the computer sort some of the data but not the rest of it?
5. How is the Choose function different from Vlookup?
6. What is an amortization table?
7. What are the most useful financial functions?
8. What is the difference between copying Excel data into a Word document, and linking Excel data into a Word document?

Self-Check

Do you know what an amortization table is? The following self-check will test your understanding of this and other concepts:

• SS Self-Check #3

Assignments

You must complete the following assignment associated with the lessons above to receive credit for this unit:

• Spreadsheet Assignment 3

Copyright 1997 by the Curators of the University of Missouri