NEXT GENERATION EXCEL, Second Edition +Website – Modeling in Microsoft Excel for Analysts and MBAS (for Windows and Mac OS)

<p>Foreword xv</p>
<p>Preface xvii</p>
<p>Acknowledgments xxi</p>
<p>PART ONE Using Excel Efficiently 1</p>
<p>CHAPTER 1 AutoFill 3</p>
<p>AutoFill Options 8</p>
<p>Right–Drag AutoFill 8</p>
<p>Appendix: AutoFill in Excel 2003 and Excel Mac 2011 10</p>
<p>Review Questions 13</p>
<p>Answers 14</p>
<p>CHAPTER 2 Selecting Efficiently in Excel 15</p>
<p>Review Questions 18</p>
<p>Answers 19</p>
<p>CHAPTER 3 Formulas, Functions, and Relative and Absolute Addressing 21</p>
<p>Relative and Absolute Addressing 22</p>
<p>Other Functions 25</p>
<p>Appendix: Doing It in Mac Excel 2011 28</p>
<p>Review Questions 29</p>
<p>Answers 30</p>
<p>CHAPTER 4 Naming Cells and Ranges 31</p>
<p>Naming a Single Cell 31</p>
<p>Naming a Range of Cells 33</p>
<p>Using the Name Menu to Create Names 34</p>
<p>Appendix: Using the Names Menu in Excel 2003 and Mac Excel 2011 38</p>
<p>Review Questions 41</p>
<p>Answers 42</p>
<p>CHAPTER 5 Conditional and Advanced Conditional Formatting in Excel 45</p>
<p>Simple Conditional Formatting; Adding a Rule 45</p>
<p>New Conditional Formatting Features 46</p>
<p>Advanced Conditional Formatting 49</p>
<p>Appendix: Using the Conditional Formatting in Excel 2003 52</p>
<p>Review Questions 53</p>
<p>Answers 55</p>
<p>CHAPTER 6 Excel Charts 57</p>
<p>Quick/Instant Chart 57</p>
<p>Creating a Chart Using the Menu 57</p>
<p>Adding More Data to an Existing Chart 61</p>
<p>More about Charts 64</p>
<p>Three–Dimensional Charts Column and Pie 66</p>
<p>Pie Charts 70</p>
<p>Appendix: Generating Charts in Excel 2003 and Mac Excel 2011 73</p>
<p>CHAPTER 7 Sparklines and Advanced Topics in Excel Charts 81</p>
<p>Sparklines 81</p>
<p>More about Charts 83</p>
<p>Review Questions 90</p>
<p>Answers 91</p>
<p>PART TWO IF Functions and Text Manipulations 93</p>
<p>CHAPTER 8 IF Functions 95</p>
<p>Simple IF Functions 95</p>
<p>Nested IF Functions 98</p>
<p>Nested IF Payroll Example 99</p>
<p>Appendix: Using the IF Function in Mac Excel 2011 101</p>
<p>Review Questions 102</p>
<p>Answers 102</p>
<p>CHAPTER 9 Text Manipulation 103</p>
<p>Text to Columns 103</p>
<p>Appendix: Using Text to Column in Excel 2003 108</p>
<p>Review Questions 111</p>
<p>Answers 112</p>
<p>PART THREE Statistical Tools 113</p>
<p>CHAPTER 10 Descriptive Statistics 115</p>
<p>Descriptive Statistics 115</p>
<p>Appendix: Descriptive Statistics with the Mac Excel 2011 117</p>
<p>Review Questions 119</p>
<p>Answers 119</p>
<p>CHAPTER 11 Frequency Distributions 121</p>
<p>One More Example 126</p>
<p>Appendix: Frequency Distributions with Mac Excel 2011 129</p>
<p>Review Questions 129</p>
<p>Answers 130</p>
<p>CHAPTER 12 Statistical Regression 131</p>
<p>Using the Scatter Chart in Excel 131</p>
<p>Linear Regression Using Excel Functions 133</p>
<p>Appendix: Using the Chart Feature to Create a</p>
<p>Trend Line in Excel 2003 and Mac Excel 2011 137</p>
<p>Review Questions 140</p>
<p>Answers 140</p>
<p>CHAPTER 13 Data Analysis The Excel Easy to Use Statistics Add–In 141</p>
<p>Descriptive Statistics 141</p>
<p>Frequency Distribution Using Histogram 143</p>
<p>Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003 148</p>
<p>Review Questions 148</p>
<p>Answers 149</p>
<p>CHAPTER 14 Data Analysis Multi–Regression 151</p>
<p>Simple or Single Variable Regression 151</p>
<p>Multi–Regression 152</p>
<p>Appendix: Using Data Analysis in Excel Mac 2011 and Excel 2003 160</p>
<p>Review Questions 160</p>
<p>Answers 161</p>
<p>PART FOUR What–If Analysis 165</p>
<p>CHAPTER 15 Naming Cells For Meaningful Decision Making and Modeling 167</p>
<p>One More Example If You Have a Ready Model 168</p>
<p>Appendix: Create and Apply Names in Excel 2003 and Mac Excel 2011 170</p>
<p>Review Questions 175</p>
<p>Answers 175</p>
<p>CHAPTER 16 What–If Analysis and Goal Seek 177</p>
<p>Goal Seek 178</p>
<p>Appendix: Goal Seek in Excel 2003 and Mac Excel 2011 182</p>
<p>Review Questions 183</p>
<p>Answers 183</p>
<p>CHAPTER 17 Sensitivity Analysis One– and Two–Way Data Tables 185</p>
<p>Two–Way Table 187</p>
<p>Data Tables One More Example 189</p>
<p>Break–Even Point (BEP) Chart 190</p>
<p>Appendix: Sensitivity Analysis One– and Two–Way Data Tables for Excel 2003 and Mac Excel 2011 192</p>
<p>Review Questions 193</p>
<p>Answers 195</p>
<p>CHAPTER 18 Using Scroll Bars for Sensitivity Analysis 199</p>
<p>Scroll Bar Limitations 203</p>
<p>Appendix: Adding a Scroll Bar in Excel 2003, 2007, and Mac Excel 2011 205</p>
<p>Review Questions 207</p>
<p>Answers 208</p>
<p>PART FIVE Multi–Page Systems and Lookups 211</p>
<p>CHAPTER 19 Multi–Page Budgets Going to the Third Dimension 213</p>
<p>A Payroll Example 213</p>
<p>A Second Example Bakers Supplies 217</p>
<p>Review Questions 219</p>
<p>Answers 220</p>
<p>CHAPTER 20 Lookup Tables 221</p>
<p>Range Approximate Match Lookup 221</p>
<p>An Exact Match Lookup 224</p>
<p>Review Questions 227</p>
<p>Answers 228</p>
<p>PART SIX The Data Menu and Ribbon 229</p>
<p>CHAPTER 21 Sorting Data 231</p>
<p>Sorting by Multiple Parameters 233</p>
<p>Appendix: Sorting in Excel 2003 and Mac Excel 2011 236</p>
<p>Review Questions 238</p>
<p>Answers 239</p>
<p>CHAPTER 22 AutoFilter 241</p>
<p>Dates 241</p>
<p>Below and Above Average 243</p>
<p>Filter by Color 243</p>
<p>Appendix: AutoFilter in Excel 2003 244</p>
<p>Review Questions 246</p>
<p>Answers 247</p>
<p>CHAPTER 23 Data Forms and Features Eliminated in Excel 2007 and 2010 249</p>
<p>Appendix: The Data Form in Excel 2003 or Earlier versions and Mac Excel 2011 251</p>
<p>Review Questions 252</p>
<p>Answers 253</p>
<p>CHAPTER 24 Group and Outline Data 255</p>
<p>Appendix: Group and Outline Data Excel 2003 and Mac Excel 2011 258</p>
<p>Review Questions 260</p>
<p>Answers 261</p>
<p>CHAPTER 25 Excel Subtotals 263</p>
<p>One More Example Function Applied to Multiple Categories 265</p>
<p>Copying Grouped Data 265</p>
<p>Second Example Larger Database 269</p>
<p>Appendix: Creating Subtotals in Excel 2003 and Mac Excel 2011 269</p>
<p>Review Questions 273</p>
<p>Answers 273</p>
<p>CHAPTER 26 Pivot Tables 275</p>
<p>PivotTable Example 275</p>
<p>Appendix: The Pivot Table in Excel 2003 and Mac Excel 2011 282</p>
<p>Review Questions 286</p>
<p>Answers 286</p>
<p>CHAPTER 27 Data Mining Using Pivot Tables 289</p>
<p>Appendix: Advanced PivotTable Techniques in Excel 2003 296</p>
<p>Review Questions 297</p>
<p>Answers 298</p>
<p>CHAPTER 28 Using Slicers to Filter Pivot Tables 301</p>
<p>Format a Slicer 302</p>
<p>Sharing Slicers for More Than One Pivot Table 304</p>
<p>Review Questions 305</p>
<p>Answers 306</p>
<p>PART SEVEN Excel Financial Tools 307</p>
<p>CHAPTER 29 NPV and IRR Evaluating Capital Investments 309</p>
<p>The Time Value of Money 309</p>
<p>IRR Internal Rate of Return 311</p>
<p>Review Questions 313</p>
<p>Answers 313</p>
<p>CHAPTER 30 Unconventional Financial Functions: XNPV and XIRR 315</p>
<p>Excel XNPV The Net Present Value Function for Uneven Intervals 315</p>
<p>Excel XIRR The Internal Rate of Return Function for Uneven Intervals 316</p>
<p>Review Questions 318</p>
<p>Answers 318</p>
<p>CHAPTER 31 Frequently Used Financial Functions 319</p>
<p>Similar Excel Functions 320</p>
<p>Review Questions 323</p>
<p>Answers 323</p>
<p>CHAPTER 32 Amortization Tables 325</p>
<p>Amortization Example 325</p>
<p>Review Questions 334</p>
<p>Answers 334</p>
<p>CHAPTER 33 Accounting Depreciation Functions 337</p>
<p>SLD Straight Line Depreciation 337</p>
<p>SYD Sum of the Years Digits 337</p>
<p>Review Questions 339</p>
<p>Answers 339</p>
<p>PART EIGHT Using the Solver Add–In 341</p>
<p>CHAPTER 34 Beyond the Goal Seek More Than One Changing Cell? Use the Solver 343</p>
<p>Example Break–Even Point 343</p>
<p>Using the Solver 343</p>
<p>Appendix: Using the Solver in Excel 2003 and Mac Excel 2011 349</p>
<p>Review Questions 350</p>
<p>Answers 352</p>
<p>CHAPTER 35 The Solver Add–In Optimizer 353</p>
<p>Solver Example 353</p>
<p>Nonlinear Example Using the Solver 355</p>
<p>Appendix: Using the Solver in Excel 2003 and Mac 2011 360</p>
<p>Review Questions 360</p>
<p>Answers 360</p>
<p>Appendix Summary Case Study Supply Chain Management Example 365</p>
<p>About the Author 385</p>
<p>Index 387</p>