Manual Excel

Excel 2003 Advanced II

University of Nevada, Las Vegas

Excel 2003 Advanced II
Table of Contents
Introduction...................................................................................................................................... 2
What If Analysis...............................................................................................................................3
Conditional Formatting................................................................................................................... 3
Exercise—Conditional Formatting ....................................................................................... 3
Pmt Function.................................................................................................................................... 4
Exercise—Loan Payment Schedule……………………………………………………….4-7
Data Tables....................................................................................................................................... 8
Exercise—Data Tables ......................................................................................................... 9
GoalSeeker....................................................................................................................................... 10
Exercise—Goal Seeker…………………………………………………………………..10-11
Scenario Manager ............................................................................................................................ 12
Exercise—Scenario Manager…………………………………………………………….13-14
Solver.............................................................................................................................................. 15
Exercise—Solver………………………………………………………………………...15-17

2

Computing Resource Center

Excel 2003 Advanced II
Introduction
Welcome to the Computing Resource Center’s Microsoft Excel XP Advanced II: What If Analysis workshop. This workshop is centered on providing users with themeans to determine or
predict how changes in values with affect the outcome of formulas on a Worksheet. Even
though this workshop is intended as an advanced course for users of Microsoft Excel, certain
assumptions are made. Users should be able to:
• Determine which version of the Office product(s) is currently installed on their workstation.
• Distinguish between left-clicking andright-clicking the mouse.
• Highlight multiple cells using the mouse or keyboard.
• Enter and modify cell values.
• Format cells.
• Navigate between Worksheets.
• Understand order of operations.
• Enter and modify formulas using functions.
• Modify cell names.
• Open, save, and remember the location of files on their workstation.
Operate a standard keyboard and understand the functions of the variouskeys.
In order to follow along with this manual, it’s recommended you download the corresponding
class file(s). The class file(s) may be found on the CRC web site at http://ccs.unlv.edu/cns/
training. Simply select the appropriate topic, locate the appropriate workshop, and then click on
the class files corresponding with your version of the Office suite.
If you have any questions regardingthis manual, please contact the Computing Resource Center by phone at 895-0777 or by email at crchelp@ccmail.nevada.edu

University of Nevada, Las Vegas

3

Excel 2003 Advanced II
What If Analysis
If one could read the future, there would be no need to create budgets for upcoming years. We
would know exactly how much we would need every year down to the last cent. Unfortunately,
this isnot the case. No matter how well we prepare, there’s always the potential for unforeseen
factors causing great strain on even the most well planned budget. To help cope with this, Excel has the ability to provide varying types of analysis for best-case, worst-case, and in-between
scenarios. These tools can provide great help in better preparing for the unexpected by being
able to
• Provide...