Cornell Beginning Excel What-If Data Analysis Tools

Getting Started with Goal Seek, Data Tables, Scenarios, and Solver
1. ed
ISBN: 978-1-4302-0098-7
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 192 Seiten

* The only book on the market dedicated to the What-If functions and tools built into Excel, which are key to many business analysis scenarios. * Focused and to the point - where other Excel books force the reader to wade through 100's of pages of related information and commit hours of reading, this book gets straight to the topic of teaching the reader about What-If scenario tools in Excel. * Offers the deepest tutorial treatment of the Solver add-in, the Goal Seeker and the other What-If scenario analysis tools, in a practical, hands-on exercises approach to using Excel to get business results.

Paul Cornell works at Microsoft on the documentation team for Microsoft Visual Studio Tools for the Microsoft Office system. He worked as an editor, writer, and columnist on the MSDN Office Developer Center and edited the Microsoft Office Visual Basic Language Reference. Cornell also founded the Power User Corner, on Microsoft Office Online, where he was a frequent contributor.

Autoren/Hrsg.

Cornell, Paul

Inhaltsverzeichnis

1;Contents at a Glance;4
2;Contents;6
3;Preface;12
4;About the Author;14
5;About the Technical Reviewer;16
6;Acknowledgments;18
7;Introduction;20
8;CHAPTER 1 Goal Seek;24
8.1;What Is Goal Seeking?;24
8.2;When Would I Use Goal Seek?;24
8.3;How Do I Use Goal Seek?;25
8.4;Try It: Use Goal Seek to Solve Simple Math Problems;27
8.4.1;Speed,Time, and Distance Math Problems;27
8.4.2;Circle Radius, Diameter, Circumference, and Area Math Problems;28
8.4.3;Algebraic Equation Math Problem;30
8.5;Try It: Use Goal Seek to Forecast Interest Rates;32
8.5.1;Home Mortgage Interest Rate;33
8.5.2;Car Loan Interest Rate;34
8.5.3;Savings Account Interest Rate;36
8.6;Try It: Use Goal Seek to Determine Optimal Ticket Prices;37
8.6.1;Number of Tickets Sold;38
8.6.2;Ticket Prices;40
8.7;Troubleshooting Goal Seek;41
8.8;Summary;42
9;CHAPTER 2 Data Tables;44
9.1;What Are Data Tables?;44
9.2;When Would I Use Data Tables?;45
9.3;How Do I Create Data Tables?;47
9.3.1;Working with One-Variable Data Tables;47
9.3.2;Working with Two-Variable Data Tables;49
9.3.3;Clearing Data Tables;50
9.3.4;Converting Data Tables;50
9.3.5;Adjusting Data Table Calculation Options;51
9.4;Try It: Use Data Tables to Forecast Savings Account Details;51
9.4.1;One-Variable Data Table to Forecast Savings Account Details;52
9.4.2;Two-Variable Data Table to Forecast Savings Account Details;53
9.5;Try It: Use Data Tables to Determine Royalty Payments;54
9.5.1;One-Variable Data Table to Determine Royalty Payments;55
9.5.2;Two-Variable Data Table to Determine Royalty Payments;56
9.6;Try It: Use Data Tables to Calculate Stock Dividend Payments;58
9.6.1;One-Variable Data Table to Calculate Stock Dividend Payments;58
9.6.2;Two-Variable Data Table to Calculate Stock Dividend Payments;59
9.7;Troubleshooting Data Tables;60
9.8;Summary;61
10;CHAPTER 3 Scenarios;62
10.1;What Are Scenarios?;62
10.2;When Would I Use Scenarios?;63
10.3;How Do I Use Scenarios?;64
10.3.1;Creating a New Scenario;65
10.3.2;Displaying a Scenario;66
10.3.3;Editing an Existing Scenario;67
10.3.4;Deleting a Scenario;67
10.3.5;Creating a Scenario Summary Report;67
10.3.6;Merging Scenarios from Another Worksheet;68
10.3.7;Preventing Changes to a Scenario;70
10.4;Try It: Use Scenarios to Forecast Development Costs;71
10.4.1;Worst-Case Scenario;71
10.4.2;Best-Case Scenario;72
10.4.3;Scenario Results;73
10.5;Try It: Use Scenarios to Forecast Sales;74
10.5.1;Summer Scenario;75
10.5.2;Winter Scenario;75
10.5.3;Scenario Results;76
10.6;Try It: Use Scenarios to Forecast Rental Volumes;77
10.6.1;Blockbuster Week Scenario;77
10.6.2;Regular Week Scenario;78
10.6.3;Scenario Results;79
10.7;Troubleshooting Scenarios;80
10.8;Summary;81
11;CHAPTER 4 Solver;82
11.1;What Is Solver?;82
11.2;When Would I Use Solver?;83
11.3;How Do I Use Solver?;84
11.3.1;Installing Solver;86
11.3.2;Setting Solver Parameters;86
11.3.3;Adding and Changing Constraints;88
11.3.4;Setting Solver Options;89
11.3.5;Saving and Loading Solver Models;92
11.3.6;Working with the Solver Results;94
11.3.7;Working with the Show Trial Solution Dialog Box;96
11.3.8;Creating Solver Reports;96
11.4;Try It: Use Solver to Solve Math Problems;100
11.4.1;Cube Volume Problem;100
11.4.2;Object Velocity Problem;101
11.5;Try It: Use Solver to Forecast Auction Prices;102
11.5.1;Average Daily Bid Increase for One Item;103
11.5.2;Average Daily Auction Bid Increase for All Items;104
11.6;Try It: Use Solver to Determine a Home Sales Price;106
11.7;Try It: Use Solver to Forecast the Weather;108
11.7.1;Minimum Yearly Precipitation Total for Seattle;109
11.7.2;Average December Precipitation Total for All Cities;110
11.8;Try It: Experiment with the Default Solver Samples;112
11.8.1;Quick Tour;112
11.8.2;Product Mix;114
11.8.3;Shipping Routes;115
11.8.4;Staff Scheduling;117
11.8.5;Maximizing Income;119
11.8.6;Portfolio of Securities;122
11.8.7;Engineering Design;123
11.9;Troubleshooting Solver;125
11.9.1;General Excel Error Messages;125
11.9.2;Solver Dialog Box Error Messages;126
11.9.3;General Troubleshooting Tips;130
11.10;Summary;130
12;CHAPTER 5 Case Study: Using Excel What-If Tools;132
12.1;About the Ridge Running Cooperative;132
12.2;Use Goal Seek to Forecast Membership Dues;133
12.2.1;New Lifetime Family Club Membership Dues;134
12.2.2;New Annual Family Club Memberships;135
12.3;Use Data Tables to Forecast Race Paces;136
12.3.1;Time for a Single Race Pace;136
12.3.2;Time for Multiple Race Paces;137
12.4;Use Scenarios to Forecast Race-Day Cash Flow;139
12.4.1;Cash Flow for a Rainy Weather Race Day;141
12.4.2;Cash Flow for a Normal Weather Race Day;142
12.4.3;Cash Flow for a Perfect Weather Race Day;143
12.4.4;Report to Display Race-Day Cash-Flow Forecasts Side by Side;144
12.4.5;Report to Display Race-Day Cash-Flow Forecasts in PivotTable Format;145
12.5;Use Solver to Forecast Race-Day Finish Times;146
12.5.1;Race-Day Finish Times with Distance and Target Pace;148
12.5.2;Race-Day Finish Times with Distance and Elapsed Time;149
12.5.3;Race-Day Finish Times with a Pacer;150
12.6;Use Solver to Pair Up Race Relay Teams;151
12.7;Summary;153
13;APPENDIX A Excel What-If Tools Quick Start;154
13.1;Using Goal Seek;154
13.1.1;Goal Seek Procedure;154
13.1.2;Goal Seek Example;154
13.2;Using Data Tables;155
13.2.1;Data Table Procedure;155
13.2.2;Data Table Examples;156
13.3;Using Scenarios;158
13.3.1;Scenario Procedure;158
13.3.2;Scenario Example;158
13.4;Using Solver;159
13.4.1;Solver Procedure;159
13.4.2;Solver Example;160
14;APPENDIX B Summary of Other Helpful Excel Data Analysis Tools;162
14.1;Subtotaling and Outlining Data;162
14.2;Consolidating Data;163
14.2.1;Consolidating Using 3-D References in Formulas;163
14.2.2;Consolidating Data by Position or Category;164
14.3;Sorting Data;165
14.3.1;Sorting in Ascending or Descending Order;165
14.3.2;Sorting by Multiple Columns;165
14.3.3;Sorting by Months or Weekdays;165
14.3.4;Sorting in Custom Order;166
14.3.5;Sorting by Rows;166
14.4;Filtering Data;167
14.4.1;Filtering Data with the AutoFilter Feature;167
14.4.2;Filtering Data with the Advanced Filter Feature;168
14.5;Using Conditional Cell Formatting;169
14.6;Working with OLAP Data;170
14.7;Working with PivotTables and PivotCharts;170
15;APPENDIX C Summary of Common Excel Data Analysis Functions;172
15.1;Statistical Functions;172
15.2;Mathematical Functions;174
15.3;Financial Functions;175
16;APPENDIX D Additional Excel Data Analysis Resources;178
16.1;Books;178
16.2;Periodicals;178
16.3;Web Sites;178
16.4;Newsgroups;179
17;Index;180