| |
| |
| Foreword | |
| |
| |
| Preface | |
| |
| |
| Acknowledgments | |
| |
| |
| |
| Using Excel Efficiently | |
| |
| |
| |
| AutoFill | |
| |
| |
| AutoFill Options | |
| |
| |
| Right-Drag AutoFill | |
| |
| |
| Appendix: AutoFill in Excel 2003 and Excel Mac 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Selecting Efficiently in Excel | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Formulas, Functions, and Relative and Absolute Addressing | |
| |
| |
| Relative and Absolute Addressing | |
| |
| |
| Other Functions | |
| |
| |
| Appendix: Doing It in Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Naming Ceils and Ranges | |
| |
| |
| Naming a Single Cell | |
| |
| |
| Naming a Range of Cells | |
| |
| |
| Using the Name Menu to Create Names | |
| |
| |
| Appendix: Using the Names Menu in Excel 2003 and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Conditional and Advanced Conditional Formatting in Excel | |
| |
| |
| Simple Conditional Formatting; Adding a Rule | |
| |
| |
| New Conditional Formatting Features | |
| |
| |
| Advanced Conditional Formatting | |
| |
| |
| Appendix: Using the Conditional Formatting in Excel 2003 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Excel Charts | |
| |
| |
| Quick/Instant Chart | |
| |
| |
| Creating a Chart Using the Menu | |
| |
| |
| Adding More Data to an Existing Chart | |
| |
| |
| More about Charts | |
| |
| |
| Three-Dimensional Charts-Column and Pie | |
| |
| |
| Pie Charts | |
| |
| |
| Appendix: Generating Charts in Excel 2003 and Mac Excel 2011 | |
| |
| |
| |
| Sparklines and Advanced Topics in Excel Charts | |
| |
| |
| Sparklines | |
| |
| |
| More about Charts | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| IF Functions and Text Manipulations | |
| |
| |
| |
| IF Functions | |
| |
| |
| Simple IF Functions | |
| |
| |
| Nested IF Functions | |
| |
| |
| Nested IF-Payroll Example | |
| |
| |
| Appendix: Using the LF Function in Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Text Manipulation | |
| |
| |
| Text to Columns | |
| |
| |
| Appendix: Using Text to Column in Excel 2003 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Statistical Tools | |
| |
| |
| |
| Descriptive Statistics | |
| |
| |
| Descriptive Statistics | |
| |
| |
| Appendix: Descriptive Statistics with the Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Frequency Distributions | |
| |
| |
| One More Example | |
| |
| |
| Appendix: Frequency Distributions with Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Statistical Regression | |
| |
| |
| Using the Scatter Chart in Excel | |
| |
| |
| Linear Regression-Using Excel Functions | |
| |
| |
| Appendix: Using the Chart Feature to Create a Trend Line in Excel 2003 and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Data Analysis-The Excel Easy to Use Statistics Add-In | |
| |
| |
| Descriptive Statistics | |
| |
| |
| Frequency Distribution Using Histogram | |
| |
| |
| Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Data Analysis-Multi-Regression | |
| |
| |
| Simple or Single Variable Regression | |
| |
| |
| Multi-Regression | |
| |
| |
| Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| What-If Analysis | |
| |
| |
| |
| Naming Cells-For Meaningful Decision Making and Modeling | |
| |
| |
| One More Example-If You Have a Ready Model | |
| |
| |
| Appendix: Create and Apply Names in Excel 2003 and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| What If Analysis and Goal Seek | |
| |
| |
| Goal Seek | |
| |
| |
| Appendix: Goal Seek in Excel 2003 and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Sensitivity Analysis-One- and Two-Way Data Tables | |
| |
| |
| Two-Way Table | |
| |
| |
| Data Tables-One More Example | |
| |
| |
| Break-Even Point (BEP) Chart | |
| |
| |
| Appendix: Sensitivity Analysis-One- and Two-Way Data Tables for Excel 2003 and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Using Scroll Bars for Sensitivity Analysis | |
| |
| |
| Scroll Bar Limitations | |
| |
| |
| Appendix: Adding a Scroll Bar in Excel 2003, 2007, and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Multi-Page Systems and Lookups | |
| |
| |
| |
| Multi-Page Budgets-Going to the Third Dimension | |
| |
| |
| A Payroll Example | |
| |
| |
| A Second Example-Bakers' Supplies | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Lookup Tables | |
| |
| |
| Range-Approximate Match Lookup | |
| |
| |
| An Exact Match Lookup | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| The Data Menu and Ribbon | |
| |
| |
| |
| Sorting Data | |
| |
| |
| Sorting by Multiple Parameters | |
| |
| |
| Appendix: Sorting in Excel 2003 and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| AutoFilter | |
| |
| |
| Dates | |
| |
| |
| Below and Above Average | |
| |
| |
| Filter by Color | |
| |
| |
| Appendix: AutoFilter in Excel 2003 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Data Forms and Features Eliminated in Excel 2007 and 2010 | |
| |
| |
| Appendix: The Data Form in Excel 2003 or Earlier versions and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Group and Outline Data | |
| |
| |
| Appendix: Group and Outline Data Excel 2003 and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Excel Subtotals | |
| |
| |
| One More Example-Function Applied to Multiple Categories | |
| |
| |
| Copying Grouped Data | |
| |
| |
| Second Example-Larger Database | |
| |
| |
| Appendix: Creating Subtotals in Excel 2003 and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Pivot Tables | |
| |
| |
| PivotTable Example | |
| |
| |
| Appendix: The Pivot Table in Excel 2003 and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Data Mining Using Pivot Tables | |
| |
| |
| Appendix: Advanced PivotTable Techniques in Excel 2003 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Using Slicers to niter Pivot Tables | |
| |
| |
| Format a Slicer | |
| |
| |
| Sharing Slicers for More Than One Pivot Table | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Excel Financial Tools | |
| |
| |
| |
| NPV and IRR-Evaluating Capital Investments | |
| |
| |
| The Time Value of Money | |
| |
| |
| IRR-Internal Rate of Return | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Unconventional Financial Functions: XNPV and HJRR | |
| |
| |
| Excel XNPV-The Net Present Value Function for Uneven Intervals | |
| |
| |
| Excel XLRR-The Internal Rate of Return Function for Uneven Intervals | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Frequently Used Financial Functions | |
| |
| |
| Similar Excel Functions | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Amortization Tables | |
| |
| |
| Amortization Example | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Accounting Depreciation Functions | |
| |
| |
| SLD Straight Line Depreciation | |
| |
| |
| SYD Sum of the Years Digits | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| Using the Solver Add-In | |
| |
| |
| |
| Beyond the Goal Seek-More Than One Changing Cell? Use the Salver | |
| |
| |
| Example-Break-Even Point | |
| |
| |
| Using the Solver | |
| |
| |
| Appendix: Using the Solver in Excel 2003 and Mac Excel 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| |
| The Solver Add-In-Optimizer | |
| |
| |
| Solver Example | |
| |
| |
| Nonlinear Example Using the Solver | |
| |
| |
| Appendix: Using the Solver in Excel 2003 and Mac 2011 | |
| |
| |
| Review Questions | |
| |
| |
| Answers | |
| |
| |
| Appendix Summary Case Study-Supply Chain Management Example | |
| |
| |
| About the Author | |
| |
| |
| Index | |