Cover image for MBA's guide to Microsoft Excel 2000 : the essential Excel reference for business professionals
MBA's guide to Microsoft Excel 2000 : the essential Excel reference for business professionals
Nelson, Stephen L., 1959-
Personal Author:
Publication Information:
Redmond, WA : Redmond Technology Press, [1999]

Physical Description:
xxv, 463 pages : illustrations ; 24 cm + 1 computer optical disc (4 3/4 in.)
General Note:
Disc containers starter and sample workbooks.

Includes index.
Format :



Call Number
Material Type
Home Location
Item Holds
HF5548.4.M523 N45275 1999 Book and Software Set Central Closed Stacks

On Order



How to master Excel 2000 tools for better statistical analysis, financial calculations, and optimization modeling.

Table of Contents

Acknowledgmentsp. vii
Chapter 1 Introductionp. 1
Why This Bookp. 1
What's in This Bookp. 2
What's Not in This Bookp. 3
Part 1 QuickPrimersp. 5
Chapter 2 QuickPrimer on Using Excelp. 7
Workbook Basicsp. 7
Starting Excelp. 8
Working with the Excel Windowsp. 8
Creating a Simple Workbookp. 12
Saving and Opening Workbooksp. 15
Using File Propertiesp. 18
Exiting the Programp. 18
Using Formulas and Functionsp. 18
Entering Formulasp. 18
Using Functionsp. 22
Naming Cells and Rangesp. 24
Editing Workbook Datap. 25
Erasing Cell Contentsp. 25
Undoing Mistakesp. 26
Copying, Cutting, and Pastingp. 26
Inserting and Deleting Cells, Rows, Columns, and Worksheetsp. 30
Using Find and Replacep. 32
Checking Your Spellingp. 33
Formating Workbooksp. 33
Using a Predesigned Formatp. 34
Formatting Manuallyp. 35
Printing Workbooksp. 42
Chapter 3 QuickPrimer on Chartingp. 47
Understanding Excel's Charting Termsp. 47
How Excel Sees Chart Datap. 47
Components of Excel Chartsp. 49
Presenting Data with Chartsp. 51
Using the Chart Wizardp. 51
Choosing the Right Chart Typep. 56
Customizing Your Chartsp. 67
Mapping Geographic Datap. 73
Adding the Map Button to the Toolbarp. 73
Creating a Data Mapp. 74
Customizing Your Data Mapsp. 76
Part 2 Using Excel in Businessp. 81
Chapter 4 Statistical Analysisp. 83
EasyRefresher: Basic Business Statisticsp. 83
Statistical Formulasp. 85
Average Absolute Deviation from the Mean (AVEDEV)p. 86
Beta Probability Densityp. 87
Binomial Probability Distributionp. 87
Chi-Square Distributionp. 89
Confidence Intervals for Population Means (CONFIDENCE)p. 91
Correlationp. 92
Counting Cellsp. 94
Covariance (COVAR)p. 95
Exponential Probability Distribution (EXPONDIST)p. 96
Exponential Regressionp. 97
F Probability Distributionp. 97
Fisher Transformationp. 98
Frequency (FREQUENCY)p. 98
Gamma Probability Distributionp. 99
Geometric Mean (GEOMEAN)p. 100
Harmonic Mean (HARMEAN)p. 100
Hypergeometric Distribution (HYPGEOMDIST)p. 100
Kurtosis (KURT)p. 101
Linear Regressionp. 101
Lognormal Distribution Functionp. 104
Maximums and Minimumsp. 105
Meanp. 105
Median (MEDIAN)p. 106
Mode (MODE)p. 107
Normal Probability Distributionp. 107
Permutations (PERMUT)p. 108
Poisson Random Variables (POISSON)p. 109
Probability That Values Are Between Upper and Lower Limits (PROB)p. 109
Rank and Percentilep. 110
Skewness (SKEW)p. 111
Standard Normal Probability Distributionp. 112
Sum of Squares of Deviations from Mean (DEVSQ)p. 113
Standard Deviationp. 113
t Distributionp. 115
Trimming to the Mean (TRIMMEAN)p. 116
Variancep. 116
Weibull Distribution (WEIBULL)p. 117
Z-Test (ZTEST)p. 118
Data Analysis Toolsp. 118
Analysis of Variance (ANOVA)p. 118
Correlationp. 119
Covariancep. 121
Descriptive Statisticsp. 122
Exponential Smoothingp. 124
F-Testp. 125
Fourier Analysisp. 125
Histogramp. 126
Moving Averagesp. 128
Random Number Generationp. 129
Rank and Percentilep. 130
Regressionp. 131
Samplingp. 132
T-Testp. 133
Z-Testp. 133
Chapter 5 Financial Calculationsp. 137
EasyRefresher: Applying Time Value of Money Conceptsp. 138
Analyzing Borrowingp. 138
Analyzing Investmentsp. 139
Dealing with Inflationp. 140
Using the Standard Financial Functionsp. 141
Using the Depreciation Functionsp. 142
Using the Payment Functionsp. 146
Using the Present Value, Future Value, and Interest Rate Functionsp. 151
Using the Add-In Financial Functionsp. 159
Using the Accrued Interest Add-In Functionsp. 159
Using the Bond Duration Add-In Functionsp. 161
Using the Capital Budgeting Add-In Functionsp. 163
Using the Coupon Dates Add-In Functionsp. 165
Using the Cumulative Interest and Principal Add-In Functionsp. 169
Using the Dollar Pricing Add-In Functionsp. 170
Using the French Depreciation Add-In Functionsp. 171
Using the Future Value Add-In Functionsp. 173
Using the Interest Rate Add-In Functionsp. 174
Using the Price and Yield Add-In Functionsp. 177
Using the Treasury Bill Add-In Functionsp. 183
Chapter 6 Business Modelingp. 185
What-If Analysis with Data Tablesp. 185
Working with One-Variable Data Tablesp. 186
Working with Two-Variable Data Tablesp. 188
What-If Analysis with Scenario Managerp. 191
Creating a Scenariop. 192
Using a Scenariop. 193
Editing a Scenariop. 194
Summarizing Scenariosp. 195
Merging Scenarios from Other Workbooksp. 196
Simple Modeling with Goal Seekp. 197
Optimization Modeling with Solverp. 199
EasyRefresher: How Optimization Modeling Worksp. 199
Solving an Optimization Problemp. 201
Reviewing Solver Reportsp. 206
Customizing Solver's Operationp. 210
Save Model and Load Modelp. 212
Understanding Solver Error Messagesp. 212
Chapter 7 Sharing Workbooksp. 215
Using OLE with Excelp. 215
How OLE Worksp. 215
Creating an Embedded OLE Objectp. 216
Creating a Linked OLE Objectp. 219
Editing OLE Objectsp. 220
Inserting OLE Objects in Excel Workbooksp. 220
Sharing Workbook Filesp. 222
Sharing Excel Workbooks with Other Programsp. 222
Sharing Excel Workbooks Over a Networkp. 224
Sharing Excel Workbooks with E-Mailp. 227
Workbook Sharing with E-Mailp. 230
Using E-Mail Routing Slipsp. 232
Receiving a Routed Workbookp. 233
Sharing Excel Data Over the Webp. 234
Creating a Web Page Version of an Excel Workbookp. 234
Creating an Interactive Spreadsheet Componentp. 236
Retrieving External Data with Excelp. 240
Importing Textual Data into Excelp. 240
Using the Get External Data Commandsp. 243
Chapter 8 PivotTables and PivotChartsp. 251
Using the PivotTable Wizardp. 251
Specifying PivotTable Layoutp. 253
Editing PivotTablesp. 256
Pivotingp. 256
Filtering Items in a Fieldp. 257
Separating Data Between Pagesp. 257
Grouping PivotTable Datap. 258
Creating PivotChartsp. 258
Creating a PivotChart from an Existing PivotTablep. 259
Creating a PivotChart Directly from a Databasep. 260
Chapter 9 Small Business Financial Managerp. 261
Installing and Starting the Small Business Financial Managerp. 261
Importing Financial Datap. 262
Importing Data for the First Timep. 262
Working with the Report Wizardp. 264
Reviewing the Report Wizard Reportsp. 264
Using the Report Wizardp. 265
Using the Report Wizard's Reportsp. 267
Working with the Financial Analysis Toolsp. 268
Using the Business Comparison Report Toolp. 268
Using the Buy Vs. Lease Toolp. 270
Using the Create Projection Wizard Toolp. 272
Using the Projection Reports Toolp. 274
Using the What-If Analysis Toolp. 276
Using the Chart Wizardp. 280
Refreshing Imported Datap. 281
Rearranging and Modifying Imported Datap. 282
Part 3 Using the Starter Workbooksp. 283
Chapter 10 Building a Business Planning Workbookp. 285
EasyRefresher: Financial Statements and Ratiosp. 286
Using the Business Planning Starter Workbookp. 287
Understanding the Starter Workbook's Calculationsp. 292
Forecasting Inputsp. 293
Balance Sheetp. 293
Common Size Balance Sheetp. 300
Income Statementp. 301
Common Size Income Statementp. 305
Cash Flow Statementp. 305
Financial Ratios Tablep. 312
Customizing the Starter Workbookp. 316
Changing the Number of Periodsp. 316
Ratio Analysis on Existing Financial Statementsp. 316
Calculating Taxes for a Current Net Loss Before Taxesp. 317
Combining This Workbook with Other Workbooksp. 317
Chapter 11 Building a Profit Volume and Break-Even Analysis Workbookp. 319
EasyRefresher: Profit Volume and Break-Even Analysisp. 320
Using the Profit Volume and Break-Even Analysis Starter Workbookp. 323
Understanding the Starter Workbook's Calculationsp. 326
Break-Even Analysis Forecastp. 326
Profit Volume Forecastp. 329
Common Size Profit Volume Forecastp. 335
Interpreting the Profit Volume Charts and Chart Datap. 336
Customizing the Starter Workbookp. 338
Changing the Number of Volumes Testedp. 338
Removing Forecasts from the Starter Workbookp. 339
Adding Minimums and Maximums to the Profit Volume Forecastp. 339
Charting Profit Volume Analysis Datap. 340
Using the Profit Volume Area Chartp. 340
Using the Break-Even Line Chartp. 341
Chapter 12 Forecasting Sales and Cost of Salesp. 343
EasyRefresher: Sales and Cost of Sales Forecastingp. 343
Using the Sales Forecasting Starter Workbookp. 345
Understanding the Starter Workbook's Calculationsp. 347
Sales Forecast Schedulep. 347
Cost Totals and Statisticsp. 347
Sales and Gross Margin Forecastp. 350
Inventory Forecastp. 351
Customizing the Starter Workbookp. 353
Chapter 13 Building a Capital Budgeting Workbookp. 355
EasyRefresher: Cash Flow Forecasting and Analysisp. 355
Using the Cash Flow Forecast and Analysis Starter Workbookp. 357
Understanding the Starter Workbook's Calculationsp. 362
Cash Flow Forecasting Inputsp. 362
Profit and Loss Statementp. 363
Gain and Loss Statementp. 366
Operating Cash Flow Statementp. 368
Liquidation Cash Flow Statementp. 370
Cash Flow Analysisp. 371
Pretax Cash Flow Scenariosp. 378
After-Tax Cash Flow Scenariosp. 381
Customizing the Starter Workbookp. 382
Changing the Number of Forecasting Periodsp. 382
Removing the Pretax Profitability and Liquidity Measuresp. 383
Removing the After-Tax Profitability and Liquidity Measuresp. 384
Combining This Workbook with Other Workbooksp. 384
Chapter 14 Building Amortization Schedulesp. 385
EasyRefresher: Amortizing Debtp. 386
Using the Debt Amortization Starter Workbooksp. 387
Understanding the Fixed Rate, Ordinary Annuity Amortization Starter Workbookp. 390
Fixed Interest Rate Amortization Inputsp. 390
Fixed Interest Rate Amortization Schedulep. 390
Balloon Payment Schedulep. 392
Understanding the Fixed Rate, Annuity Due Amortization Starter Workbookp. 394
Fixed Interest Rate, Annuity Due Amortization Inputsp. 394
Fixed Interest Rate, Annuity Due Amortization Schedulep. 394
Balloon Payment Schedulep. 397
Understanding the Variable Rate, Ordinary Annuity Amortization Starter Workbookp. 398
Variable Interest Rate Amortization Inputsp. 398
Variable Interest Rate Amortization Schedulep. 399
Balloon Payment Schedulep. 402
Understanding the Variable Rate, Annuity Due Amortization Starter Workbookp. 403
Variable Interest Rate, Annuity Due Amortization Inputsp. 403
Variable Interest Rate, Annuity Due Amortization Schedulep. 403
Balloon Payment Schedulep. 407
Customizing the Debt Amortization Starter Workbooksp. 408
Changing the Number of Periodsp. 408
Removing the Balloon Payment Schedulep. 409
Adding Data Valuesp. 409
Chapter 15 Building Asset Depreciation Schedulesp. 411
EasyRefresher: Asset Depreciationp. 412
Using the Asset Depreciation Starter Workbooksp. 415
Understanding the Straight-Line Depreciation Starter Workbookp. 417
Straight-Line Depreciation Calculation Inputsp. 417
Straight-Line Depreciation Schedulep. 418
Understanding the Declining Balance Depreciation Starter Workbookp. 419
Declining Balance Depreciation Calculation Inputsp. 421
Declining Balance Depreciation Schedulep. 421
Understanding the Sum-of-the-Years'-Digits Depreciation Starter Workbookp. 423
Sum-of-the-Years'-Digits Calculation Inputsp. 424
Sum-of-the-Years'-Digits Depreciation Schedulep. 424
Straight-Line Depreciation Schedulep. 426
Excess Accelerated Depreciation Schedulep. 427
Understanding the Annuity or Sinking Fund Depreciation Starter Workbookp. 428
Annuity or Sinking Fund Depreciation Calculation Inputsp. 429
Annuity or Sinking Fund Depreciation Schedulep. 429
Understanding the Activity Depreciation Starter Workbookp. 432
Activity Depreciation Calculation Inputsp. 433
Activity Depreciation Schedulep. 433
Customizing the Asset Depreciation Starter Workbooksp. 435
Changing the Number of Periodsp. 436
Indexp. 437