MASTER CORE EXCEL 2010 TOOLS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS!
Excel expert Paul McFedries shows how to use Excel 2010's core features to solve problems and get the answers you need! Using real-world examples, McFedries helps you get the absolute most out of breakthrough Excel 2010 improvements-from Sparklines to the brand-new version of Solver. Along the way, you'll discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.Becoming an Excel expert has never been easier! You'll find crystal-clear instructions... insider insights... even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more.
* Create more powerful formulas
* Use conditional formatting to instantly reveal anomalies, problems, or opportunities
* Analyze your data with standard tables and PivotTables
* Use complex criteria to filter data in lists
* Understand correlations between data
* Perform sophisticated what-if analyses
* Use regression to track trends and make forecasts
* Build loan, investment, and discount formulas
* Troubleshoot problems with formulas, ranges, and functions
About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excelskills, and presents focused tasks and examples for performing them rapidly and effectively. Selectedby Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will:
* Dramatically increase your productivity-saving you 50 hours a year, or more
* Present proven, creative strategies for solving real-world problems
* Show you how to get great results, no matter how much data you have
* Help you avoid critical mistakes that even experienced users make
Paul McFedries, president of Logophilia Limited, is a Microsoft Office expert and full-time technical writer. Paul has been authoring computer books since 1991 and has more than 70 books to his credit, which combined have sold more than three million copies worldwide. His recent titles include Using the Microsoft Office Web Apps, and Microsoft Windows 7 Unleashed. He is also proprietor of Word Spy (www.wordspy.com), a website that tracks new words and phrases as they enter the English language.
Sprache
Verlagsort
Verlagsgruppe
Zielgruppe
Illustrationen
Maße
Höhe: 178 mm
Breite: 231 mm
Dicke: 27 mm
Gewicht
ISBN-13
978-0-7897-4306-0 (9780789743060)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Klassifikation
Paul McFedries, president of Logophilia Limited, is a full-time technical writer, passionate computer tinkerer, and Windows expert. He has authored 60+ computer books that have sold 3,000,000+ copies. His recent titles include Microsoft Windows 7 Unleashed, Microsoft Home Server Unleashed, Tweak It and Freak It: A Killer Guide to Making Windows Run Your Way, Formulas and Functions with Microsoft Excel 2007; andTricks of the Microsoft Office 2007 Gurus. He is also proprietor of Word Spy (www.wordspy.com) a website that tracks new words and phrases as they enter the English language.
Editor:
Bill Jelen has made more than 75 guest appearances on TechTV with Leo Laporte and was voted guest of the year on the Computer America radio show. He has produced more than 600 episodes of his daily video Learn Excel podcast. Before founding MrExcel.com in 1998, Jelen spent 12 years "in the trenches" as a financial analyst for the accounting, finance, marketing, and operations departments of a publicly held company. Since then, his company has automated Excel reports for hundreds of clients around the world. The website answers more than 30,000 questions a year-for free-for readers all over the world.
Introduction ............................................................................................................................................................................................ 1
What's in the Book ............................................................................................................................................................................. 2
This Book's Special Features ......................................................................................................................................................... 2
I MASTERING EXCEL RANGES AND FORMULAS
1 Getting the Most Out of Ranges ............................................................................................................................... 5
Advanced Range-Selection Techniques................................................................................................................................. 5
Mouse Range-Selection Tricks ............................................................................................................................................ 6
Keyboard Range-Selection Tricks ..................................................................................................................................... 7
Working with 3D Ranges ....................................................................................................................................................... 7
Selecting a Range Using Go To ........................................................................................................................................... 8
Using the Go To Special Dialog Box ................................................................................................................................. 9
Data Entry in a Range .................................................................................................................................................................... 13
Filling a Range ................................................................................................................................................................................... 14
Using the Fill Handle ...................................................................................................................................................................... 14
Using AutoFill to Create Text and Numeric Series ................................................................................................ 14
Creating a Custom AutoFill List ....................................................................................................................................... 16
Filling a Range........................................................................................................................................................................... 17
Creating a Series ............................................................................................................................................................................... 17
Advanced Range Copying ........................................................................................................................................................... 18
Copying Selected Cell Attributes ............................................................................................................................................. 19
Combining the Source and Destination Arithmetically ..................................................................................... 20
Transposing Rows and Columns .................................................................................................................................... 21
Clearing a Range ............................................................................................................................................................................... 22
Applying Conditional Formatting to a Range .................................................................................................................. 22
Creating Highlight Cells Rules ......................................................................................................................................... 22
Creating Top/Bottom Rules ............................................................................................................................................... 24
Adding Data Bars ..................................................................................................................................................................... 26
Adding Color Scales ................................................................................................................................................................ 28
Adding Icon Sets ...................................................................................................................................................................... 31
From Here .................................................................................................................................................................................... 32
2 Using Range Names ............................................................................................................................................................ 33
Defining a Range Name ............................................................................................................................................................... 34
Working with the Name Box ............................................................................................................................................ 34
Using the New Name Dialog Box ................................................................................................................................... 35
Changing the Scope to Define Sheet-Level Names ............................................................................................. 37
Using Worksheet Text to Define Names ..................................................................................................................... 37
Naming Constants .................................................................................................................................................................. 39
Working with Range Names ..................................................................................................................................................... 41
Referring to a Range Name ............................................................................................................................................... 41
Working with Name AutoComplete............................................................................................................................. 43
Navigating Using Range Names ..................................................................................................................................... 43
Pasting a List of Range Names in a Worksheet...................................................................................................... 44
Displaying the Name Manager ....................................................................................................................................... 44
Filtering Names ........................................................................................................................................................................ 44
Editing a Range Name's Coordinates ........................................................................................................................... 45
Adjusting Range Name Coordinates Automatically ............................................................................................ 45
Changing a Range Name .................................................................................................................................................... 47
Deleting a Range Name ....................................................................................................................................................... 47
Using Names with the Intersection Operator.......................................................................................................... 47
From Here .................................................................................................................................................................................... 49
3 Building Basic Formulas................................................................................................................................................. 51
Understanding Formula Basics ................................................................................................................................................ 51
Formula Limits in Excel 2007 and Excel 2010 ......................................................................................................... 52
Entering and Editing Formulas ....................................................................................................................................... 52
Using Arithmetic Formulas ................................................................................................................................................ 53
Using Comparison Formulas ............................................................................................................................................. 54
Using Text Formulas .............................................................................................................................................................. 54
Using Reference Formulas ................................................................................................................................................. 55
Understanding Operator Precedence ................................................................................................................................... 55
The Order of Precedence ..................................................................................................................................................... 55
Controlling the Order of Precedence ............................................................................................................................ 56
Controlling Worksheet Calculation ........................................................................................................................................ 58
Copying and Moving Formulas ................................................................................................................................................ 59
Understanding Relative Reference Format............................................................................................................... 60
Understanding Absolute Reference Format ............................................................................................................. 62
Copying a Formula Without Adjusting Relative References .......................................................................... 63
Displaying Worksheet Formulas ............................................................................................................................................. 63
Converting a Formula to a Value ............................................................................................................................................ 63
Working with Range Names in Formulas ........................................................................................................................... 64
Pasting a Name into a Formula ...................................................................................................................................... 64
Applying Names to Formulas ........................................................................................................................................... 65
Naming Formulas .................................................................................................................................................................... 68
Working with Links in Formulas ............................................................................................................................................. 69
Understanding External References .............................................................................................................................. 69
Updating Links .......................................................................................................................................................................... 71
Changing the Link Source .................................................................................................................................................. 72
Formatting Numbers, Dates, and Times ............................................................................................................................. 72
Numeric Display Formats .................................................................................................................................................... 72
Date and Time Display Formats ...................................................................................................................................... 80
Deleting Custom Formats ................................................................................................................................................... 83
From Here .................................................................................................................................................................................... 83
4 Creating Advanced Formulas ................................................................................................................................... 85
Working with Arrays ...................................................................................................................................................................... 85
Using Array Formulas ............................................................................................................................................................ 86
Understanding Array Formulas ................................................................................................................................................ 87
Array Formulas That Operate on Multiple Ranges ............................................................................................... 88
Using Array Constants ................................................................................................................................................................... 89
Functions That Use or Return Arrays ............................................................................................................................ 90
Using Iteration and Circular References .............................................................................................................................. 91
Consolidating Multisheet Data ................................................................................................................................................ 93
Consolidating by Position ................................................................................................................................................... 93
Consolidating by Category ................................................................................................................................................. 97
Applying Data-Validation Rules to Cells .............................................................................................................................. 98
Using Dialog Box Controls on a Worksheet .................................................................................................................... 101
Displaying the Developer Tab ....................................................................................................................................... 101
Using the Form Controls .................................................................................................................................................. 101
Adding a Control to a Worksheet ............................................................................................................................... 101
Linking a Control to a Cell Value ................................................................................................................................. 102
Understanding the Worksheet Controls .................................................................................................................. 103
From Here ................................................................................................................................................................................. 108
5 Troubleshooting Formulas ...................................................................................................................................... 109
Understanding Excel's Error Values .................................................................................................................................... 110
#DIV/0! ................................................................................................................................................................................... 110
#N/A ............................................................................................................................................................................................ 111
#NAME? ...................................................................................................................................................................................... 111
Case Study: Avoiding #NAME? Errors When Deleting Range Names ..................................................... 112
#NULL! ...................................................................................................................................................................................... 113
#NUM! ......................................................................................................................................................................................... 113
#REF! ......................................................................................................................................................................................... 113
#VALUE! ................................................................................................................................................................................... 114
Fixing Other Formula Errors .................................................................................................................................................... 114
Missing or Mismatched Parentheses ......................................................................................................................... 114
Erroneous Formula Results ............................................................................................................................................. 115
Fixing Circular References ............................................................................................................................................... 116
Handling Formula Errors with IFERROR() ................................................................................................................... 117
Using the Formula Error Checker ......................................................................................................................................... 118
Choosing an Error Action ................................................................................................................................................. 119
Setting Error Checker Options ....................................................................................................................................... 119
Auditing a Worksheet................................................................................................................................................................. 122
Understanding Auditing .................................................................................................................................................. 123
Tracing Cell Precedents ..................................................................................................................................................... 123
Tracing Cell Dependents .................................................................................................................................................. 124
Tracing Cell Errors ................................................................................................................................................................. 124
Removing Tracer Arrows .................................................................................................................................................. 124
Evaluating Formulas ........................................................................................................................................................... 124
Watching Cell Values.......................................................................................................................................................... 125
From Here ................................................................................................................................................................................. 126
II HARNESSING THE POWER OF FUNCTIONS
6 Understanding Functions ......................................................................................................................................... 127
About Excel's Functions ............................................................................................................................................................. 128
The Structure of a Function ..................................................................................................................................................... 128
Typing a Function into a Formula ....................................................................................................................................... 130
Using the Insert Function Feature ...................................................................................................................................... 131
Loading the Analysis ToolPak ................................................................................................................................................ 134
From Here ................................................................................................................................................................................. 134
7 Working with Text Functions ............................................................................................................................... 137
Excel's Text Functions ................................................................................................................................................................. 137
Working with Characters and Codes ................................................................................................................................. 137
The CHAR() Function ........................................................................................................................................................ 139
The CODE() Function ........................................................................................................................................................ 141
Converting Text .............................................................................................................................................................................. 142
The LOWER() Function ..................................................................................................................................................... 142
The UPPER() Function ..................................................................................................................................................... 143
The PROPER() Function.................................................................................................................................................. 143
Formatting Text ............................................................................................................................................................................. 143
The DOLLAR() Function.................................................................................................................................................. 144
The FIXED() Function ..................................................................................................................................................... 144
The TEXT() Function ........................................................................................................................................................ 145
Displaying When a Workbook Was Last Updated ............................................................................................. 145
Manipulating Text ........................................................................................................................................................................ 146
Removing Unwanted Characters from a String ........................................................................................................... 146
The TRIM() Function ........................................................................................................................................................ 146
The CLEAN() Function ..................................................................................................................................................... 147
The REPT() Function: Repeating a Character .................................................................................................... 147
Padding a Cell ......................................................................................................................................................................... 147
Building Text Charts............................................................................................................................................................ 148
Extracting a Substring ................................................................................................................................................................ 149
The LEFT() Function ........................................................................................................................................................ 149
The RIGHT() Function ..................................................................................................................................................... 150
The MID() Function ........................................................................................................................................................... 150
Converting Text to Sentence Case ............................................................................................................................... 150
A Date-Conversion Formula ........................................................................................................................................... 151
Searching for Substrings ........................................................................................................................................................... 151
The FIND() and SEARCH() Functions ................................................................................................................... 151
Case Study: Generating Account Numbers ......................................................................................................................152
Extracting a First Name or Last Name ...................................................................................................................... 153
Extracting First Name, Last Name, and Middle Initial .................................................................................... 154
Determining the Column Letter .................................................................................................................................. 154
Substituting One Substring for Another.......................................................................................................................... 155
The REPLACE() Function .............................................................................................................................................. 155
The SUBSTITUTE() Function ..................................................................................................................................... 156
Removing a Character from a String ......................................................................................................................... 156
Removing Two Different Characters from a String ........................................................................................... 157
Case Study: Generating Account Numbers, Part 2 ..................................................................................................... 157
Removing Line Feeds ......................................................................................................................................................... 158
From Here ................................................................................................................................................................................. 158
8 Working with Logical and Information Functions......................................................................... 159
Adding Intelligence with Logical Functions ................................................................................................................... 159
Using the IF() Function ................................................................................................................................................. 160
Performing Multiple Logical Tests .............................................................................................................................. 163
Combining Logical Functions with Arrays .............................................................................................................. 168
Case Study: Building an Accounts Receivable Aging Worksheet ...................................................................... 173
Getting Data with Information Functions ....................................................................................................................... 176
The CELL() Function ........................................................................................................................................................ 176
The ERROR.TYPE() Function ..................................................................................................................................... 179
The INFO() Function ........................................................................................................................................................ 180
The IS Functions .................................................................................................................................................................... 181
From Here ................................................................................................................................................................................. 183
9 Working with Lookup Functions ........................................................................................................................ 185
Understanding Lookup Tables .............................................................................................................................................. 186
The CHOOSE() Function .......................................................................................................................................................... 187
Determining the Name of the Day of the Week ................................................................................................. 187
Determining the Month of the Fiscal Year ............................................................................................................. 188
Calculating Weighted Questionnaire Results ....................................................................................................... 189
Integrating CHOOSE() and Worksheet Option Buttons ............................................................................... 189
Looking Up Values in Tables ................................................................................................................................................... 190
The VLOOKUP() Function .............................................................................................................................................. 190
The HLOOKUP() Function .............................................................................................................................................. 191
Returning a Customer Discount Rate with a Range Lookup ....................................................................... 192
Returning a Tax Rate with a Range Lookup .......................................................................................................... 193
Finding Exact Matches ...................................................................................................................................................... 193
Advanced Lookup Operations ....................................................................................................................................... 195
From Here ................................................................................................................................................................................. 200
10 Working with Date and Time Functions.................................................................................................... 201
How Excel Deals with Dates and Times ............................................................................................................................ 201
Entering Dates and Times ............................................................................................................................................... 202
Excel and Two-Digit Years .............................................................................................................................................. 203
Using Excel's Date Functions .................................................................................................................................................. 204
Returning a Date ................................................................................................................................................................... 205
Returning Parts of a Date ................................................................................................................................................ 207
Calculating the Difference Between Two Dates ................................................................................................. 216
Using Excel's Time Functions ................................................................................................................................................. 220
Returning a Time .................................................................................................................................................................. 220
Returning Parts of a Time ............................................................................................................................................... 221
Calculating the Difference Between Two Times ................................................................................................. 224
Case Study: Building an Employee Time Sheer ............................................................................................................ 224
From Here ................................................................................................................................................................................. 228
11 Working with Math Functions ............................................................................................................................. 229
Understanding Excel's Rounding Functions ................................................................................................................... 232
ROUND() Function .............................................................................................................................................................. 232
MROUND() Function ........................................................................................................................................................... 233
ROUNDDOWN() and ROUNDUP() Functions .......................................................................................................... 233
CEILING() and FLOOR() Functions ...................................................................................................................... 234
Determining the Fiscal Quarter in Which a Date Falls .................................................................................... 235
Calculating Easter Dates ................................................................................................................................................... 235
EVEN() and ODD() Functions..................................................................................................................................... 236
INT() and TRUNC() Functions ................................................................................................................................. 236
Using Rounding to Prevent Calculation Errors..................................................................................................... 237
Setting Price Points ............................................................................................................................................................. 237
Case Study: Rounding Billable Time .................................................................................................................................. 238
Summing Values ............................................................................................................................................................................ 238
SUM() Function .................................................................................................................................................................... 238
Calculating Cumulative Totals ...................................................................................................................................... 239
Summing Only the Positive or Negative Values in a Range ........................................................................ 240
MOD() Function ............................................................................................................................................................................. 240
Better Formula for Time Differences ......................................................................................................................... 241
Summing Every nth Row ................................................................................................................................................ 241
Determining Whether a Year Is a Leap Year ......................................................................................................... 242
Creating Ledger Shading ................................................................................................................................................. 242
Generating Random Numbers............................................................................................................................................... 244
RAND() Function ................................................................................................................................................................. 244
RANDBETWEEN() Function............................................................................................................................................ 246
From Here ................................................................................................................................................................................. 247
12 Working with Statistical Functions ................................................................................................................ 249
Understanding Descriptive Statistics ................................................................................................................................ 249
Counting Items with the COUNT() Function ................................................................................................................ 252
Calculating Averages ................................................................................................................................................................... 253
AVERAGE() Function ........................................................................................................................................................ 253
MEDIAN() Function ........................................................................................................................................................... 253
MODE() Function ................................................................................................................................................................. 254
Calculating the Weighted Mean ................................................................................................................................. 254
Calculating Extreme Values .................................................................................................................................................... 256
MAX() and MIN() Functions ........................................................................................................................................ 256
LARGE() and SMALL() Functions ............................................................................................................................ 256
Performing Calculations on the Top k Values ...................................................................................................... 258
Performing Calculations on the Bottom k Values ............................................................................................. 258
Calculating Measures of Variation ...................................................................................................................................... 258
Calculating the Range ....................................................................................................................................................... 258
Calculating the Variance .................................................................................................................................................. 259
Calculating the Standard Deviation ........................................................................................................................... 260
Working with Frequency Distributions ............................................................................................................................. 261
FREQUENCY() Function .................................................................................................................................................. 262
Understanding the Normal Distribution and the NORMDIST() Function ............................................. 263
Shape of the Curve I: The SKEW() Function ......................................................................................................... 264
Shape of the Curve II: The KURT() Function ....................................................................................................... 265
Using the Analysis ToolPak Statistical Tools .................................................................................................................. 267
Using the Descriptive Statistics Tool .......................................................................................................................... 270
Determining the Correlation Between Data ......................................................................................................... 272
Working with Histograms ............................................................................................................................................... 274
Using the Random Number Generation Tool ....................................................................................................... 276
Working with Rank and Percentile ............................................................................................................................. 279
From Here ................................................................................................................................................................................. 281
IIIBUILDING BUSINESS MODELS
13 Analyzing Data with Tables ................................................................................................................................... 283
Converting a Range to a Table .............................................................................................................................................. 285
Basic Table Operations ............................................................................................................................................................... 286
Sorting a Table ................................................................................................................................................................................ 287
Performing a More Complex Sort ............................................................................................................................... 288
Sorting a Table in Natural Order ................................................................................................................................. 289
Sorting on Part of a Field ................................................................................................................................................. 290
Sorting Without Articles ................................................................................................................................................... 291
Filtering Table Data...................................................................................................................................................................... 292
Using Filter Lists to Filter a Table ................................................................................................................................ 292
Using Complex Criteria to Filter a Table .................................................................................................................. 296
Entering Computed Criteria ........................................................................................................................................... 299
Copying Filtered Data to a Different Range .......................................................................................................... 300
Referencing Tables in Formulas ........................................................................................................................................... 301
Using Table Specifiers ........................................................................................................................................................ 301
Entering Table Formulas .................................................................................................................................................. 303
Excel's Table Functions .............................................................................................................................................................. 305
About Table Functions ...................................................................................................................................................... 305
Table Functions That Don't Require a Criteria Range ...................................................................................... 305
Table Functions That Accept Multiple Criteria ..................................................................................................... 307
Table Functions That Require a Criteria Range ................................................................................................... 309
Case Study: Applying Statistical Table Functions to a Defects Database ..................................................... 313
From Here ................................................................................................................................................................................. 314
14 Analyzing Data with PivotTables..................................................................................................................... 315
What Are PivotTables? ............................................................................................................................................................... 315
How PivotTables Work ...................................................................................................................................................... 316
PivotTable Terms .................................................................................................................................................................. 317
Building PivotTables .................................................................................................................................................................... 318
Building a PivotTable from a Table or Range ....................................................................................................... 319
Building a PivotTable from an External Database ............................................................................................. 322
Working with and Customizing a PivotTable ....................................................................................................... 323
Working with PivotTable Subtotals ................................................................................................................................... 323
Hiding PivotTable Grand Totals ................................................................................................................................... 324
Hiding PivotTable Subtotals .......................................................................................................................................... 324
Customizing the Subtotal Calculation ...................................................................................................................... 324
Changing the Data Field Summary Calculation ........................................................................................................... 325
Using a Difference Summary Calculation................................................................................................................ 326
Using a Percentage Summary Calculation ............................................................................................................. 327
Using a Running Total Summary Calculation ....................................................................................................... 330
Using an Index Summary Calculation ....................................................................................................................... 331
Creating Custom PivotTable Calculations ........................................................................................................................ 332
Creating a Calculated Field ............................................................................................................................................. 334
Creating a Calculated Item ............................................................................................................................................. 335
Case Study: Budgeting with Calculated Items.............................................................................................................. 337
Using PivotTable Results in a Worksheet Formula .................................................................................................... 339
From Here ................................................................................................................................................................................. 340
15 Using Excel's Business-Modeling Tools ....................................................................................................... 341
Using What-If Analysis .............................................................................................................................................................. 341
Setting Up a One-Input Data Table ............................................................................................................................ 342
Adding More Formulas to the Input Table ............................................................................................................. 344
Setting Up a Two-Input Table ...................................................................................................................................... 345
Editing a Data Table ............................................................................................................................................................ 346
Working with Goal Seek............................................................................................................................................................ 347
How Does Goal Seek Work? ........................................................................................................................................... 347
Running Goal Seek .............................................................................................................................................................. 347
Optimizing Product Margin ............................................................................................................................................ 349
Note About Goal Seek's Approximations ................................................................................................................ 351
Performing a Break-Even Analysis .............................................................................................................................. 352
Solving Algebraic Equations .......................................................................................................................................... 352
Working with Scenarios ............................................................................................................................................................ 354
Understanding Scenarios ................................................................................................................................................. 354
Setting Up Your Worksheet for Scenarios .............................................................................................................. 355
Adding a Scenario ................................................................................................................................................................ 355
Displaying a Scenario ......................................................................................................................................................... 357
Editing a Scenario ................................................................................................................................................................ 358
Merging Scenarios ............................................................................................................................................................... 358
Generating a Summary Report .................................................................................................................................... 359
Deleting a Scenario ............................................................................................................................................................. 360
From Here ................................................................................................................................................................................. 361
16 Using Regression to Track Trends and Make Forecasts ............................................................ 363
Setting Up and Performing a Find ...................................................................................................................................... 363
Choosing a Regression Method ............................................................................................................................................ 364
Using Simple Regression on Linear Data ......................................................................................................................... 364
Analyzing Trends Using Best-Fit Lines ..................................................................................................................... 365
Making Forecasts .................................................................................................................................................................. 372
Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model ..................................................... 377
Using Simple Regression on Nonlinear Data ................................................................................................................. 384
Working with an Exponential Trend .......................................................................................................................... 384
Working with a Logarithmic Trend ............................................................................................................................ 388
Working with a Power Trend ........................................................................................................................................ 391
Using Polynomial Regression Analysis ..................................................................................................................... 394
Using Multiple Regression Analysis ................................................................................................................................... 396
From Here ................................................................................................................................................................................. 399
17 Solving Complex Problems with Solver ..................................................................................................... 401
Some Background on Solver .................................................................................................................................................. 401
The Advantages of Solver ................................................................................................................................................ 402
When Do You Use Solver? ............................................................................................................................................... 402
Loading Solver ................................................................................................................................................................................ 403
Using Solver ...................................................................................................................................................................................... 403
Adding Constraints ....................................................................................................................................................................... 406
Saving a Solution as a Scenario ............................................................................................................................................ 408
Setting Other Solver Options .................................................................................................................................................. 408
Selecting the Method Solver Uses ............................................................................................................................... 409
Controlling How Solver Works...................................................................................................................................... 409
Working with Solver Models ......................................................................................................................................... 412
Making Sense of Solver's Messages ................................................................................................................................... 413
Case Study: Solving the Transportation Problem ....................................................................................................... 415
Displaying Solver's Reports ..................................................................................................................................................... 417
The Answer Report .............................................................................................................................................................. 417
The Sensitivity Report ........................................................................................................................................................ 418
The Limits Report ................................................................................................................................................................. 420
From Here ................................................................................................................................................................................. 420
IV BUILDING FINANCIAL FORMULAS
18 Building Loan Formulas ............................................................................................................................................. 421
Understanding the Time Value of Money ....................................................................................................................... 421
Calculating the Loan Payment .............................................................................................................................................. 422
Loan Payment Analysis ..................................................................................................................................................... 423
Working with a Balloon Loan ....................................................................................................................................... 424
Calculating Interest Costs, Part 1 ................................................................................................................................ 424
Calculating the Principal and Interest ...................................................................................................................... 425
Calculating Interest Costs, Part 2 ................................................................................................................................ 426
Calculating Cumulative Principal and Interest .................................................................................................... 426
Building a Loan Amortization Schedule ........................................................................................................................... 428
Building a Fixed-Rate Amortization Schedule ..................................................................................................... 428
Building a Dynamic Amortization Schedule ......................................................................................................... 429
Calculating the Term of the Loan ........................................................................................................................................ 431
Calculating the Interest Rate Required for a Loan ..................................................................................................... 433
Calculating How Much You Can Borrow ........................................................................................................................... 434
Case Study: Working with Mortgages ............................................................................................................................... 435
From Here ................................................................................................................................................................................. 438
19 Building Investment Formulas ........................................................................................................................... 439
Working with Interest Rates .................................................................................................................................................. 439
Understanding Compound Interest ........................................................................................................................... 440
Nominal Versus Effective Interest ............................................................................................................................... 440
Converting Between the Nominal Rate and the Effective Rate ................................................................ 441
Calculating the Future Value .................................................................................................................................................. 442
The Future Value of a Lump Sum ............................................................................................................................... 442
The Future Value of a Series of Deposits ................................................................................................................. 443
The Future Value of a Lump Sum Plus Deposits ................................................................................................. 444
Working Toward an Investment Goal ............................................................................................................................... 444
Calculating the Required Interest Rate .................................................................................................................... 444
Calculating the Required Number of Periods ....................................................................................................... 445
Calculating the Required Regular Deposit ............................................................................................................. 446
Calculating the Required Initial Deposit .................................................................................................................. 447
Calculating the Future Value with Varying Interest Rates ........................................................................... 448
Case Study: Building an Investment Schedule ............................................................................................................. 449
From Here ................................................................................................................................................................................. 451
20 Building Discount Formulas .................................................................................................................................. 453
Calculating the Present Value ............................................................................................................................................... 454
Taking Inflation into Account ....................................................................................................................................... 454
Calculating Present Value Using PV() ..................................................................................................................... 455
Income Investing Versus Purchasing a Rental Property ................................................................................ 456
Buying Versus Leasing ...................................................................................................................................................... 457
Discounting Cash Flows ............................................................................................................................................................. 458
Calculating the Net Present Value .............................................................................................................................. 459
Calculating Net Present Value Using NPV() ........................................................................................................ 460
Net Present Value with Varying Cash Flows ......................................................................................................... 462
Net Present Value with Nonperiodic Cash Flows ............................................................................................... 463
Calculating the Payback Period ............................................................................................................................................ 464
Simple Undiscounted Payback Period ...................................................................................................................... 464
Exact Undiscounted Payback Point ............................................................................................................................ 465
Calculating the Internal Rate of Return ................................................................................................................... 466
Using the IRR() Function.............................................................................................................................................. 467
Calculating the Internal Rate of Return for Nonperiodic Cash Flows ..................................................... 468
Calculating Multiple Internal Rates of Return ..................................................................................................... 468
Case Study: Publishing a Book .............................................................................................................................................. 469
From Here ................................................................................................................................................................................. 473
TOC, 9780789743060, 4/13/10
