Microsoft Office Tutorials and References
In Depth Information
Chapter 9
Solver, Scenarios, and Goal Seek Tools
Contents
9.1 Introduction ................................. 303
9.2 Solver—Constrained Optimization ...................... 305
9.3 Example—York River Archaeology Budgeting ................. 306
9.3.1 Formulation .............................. 308
9.3.2 Formulation of YRA Problem ...................... 310
9.3.3 Preparing a Solver Worksheet ...................... 310
9.3.4 Using Solver ............................. 314
9.3.5 Solver Reports ............................. 315
9.3.6 Some Questions for YRA ........................ 319
9.4 Scenarios .................................. 323
9.4.1 Example 1—Mortgage Interest Calculations ............... 324
9.4.2 Example 2—An Income Statement Analysis ............... 328
9.5 Goal Seek .................................. 329
9.5.1 Example 1—Goal Seek Applied to the PMT Cell ............. 330
9.5.2 Example 2—Goal Seek Applied to the CUMIPMT Cell .......... 331
9.6 Summary .................................. 334
Key Terms .................................... 334
Problems and Exercises .............................. 335
9.1 Introduction
Chapters 1 through 8 have introduced us to some very powerful tools for formulat-
ing, solving, and analyzing complex problems. From the seemingly endless array of
cell functions, to the sorting, ﬁltering, and querying, to PivotTable and PivotChart
reports, to the Data Analysis tools, there seems to be a tool for almost every analyt-
ical need. Yet, still there are a number of tools we have not exercised; in particular,
the optimization tool Solver. Solver permits us to utilize a problem structure known
as constrained optimization. Decision makers can search for a best solution for
H. Guerrero, Excel Data Analysis , DOI 10.1007/978-3-642-10835-8_9,
C
303
Springer-Verlag Berlin Heidelberg 2010
Search JabSto ::

Custom Search