Spreadsheet software Books

Book SynopsisDaniil Maslyuk is an independent business intelligence consultant, trainer, and speaker who specializes in Microsoft Power BI. Daniil is the author of all official Microsoft Power BI exam reference books. For his community contributions, Daniil has been recognized as a Microsoft MVP. Daniil blogs at xxlbi.com and posts on X as @DMaslyuk. Gil Raviv is a seasoned Data & AI expert, founder of DataChant Consulting, and the creator of BI Pixie, an enterprise solution that measures the engagement, adoption, and user satisfaction of Microsoft Power BI audiences. Gil started his Power Query journey as a senior program manager on the Microsoft Excel Product team, where he led the design and integration of Power Query as the next-generation Get Data and data-wrangling technology in Excel 2016. Since then, Gil has been a devoted Power Query practitioner. With the knowledge of Power Query at his disposal, Gil moved to consulting, and over the course of s

Read more less

Book SynopsisTable of ContentsPart I The Excel interface Chapter 1 What's new in Microsoft 365 Excel Chapter 2 Using the Excel interface Chapter 3 Customizing Excel Chapter 4 Keyboard shortcuts Part II Calculating with Excel Chapter 5 Understanding formulas Chapter 6 Controlling formulas Chapter 7 Understanding functions Chapter 8 Using everyday functions: math, date and time, and text functions Chapter 9 Using powerful functions: logical, lookup, and database functions Chapter 10 Using names, LET, LAMBDA, and Data Types in Excel Chapter 11 Connecting worksheets and workbooks Chapter 12 Dynamic array formulas and names in Excel Part III Data analysis with Excel Chapter 13 Transforming data with Power Query Chapter 14 Summarizing data using subtotals or filter Chapter 15 Using pivot tables to analyze data Chapter 16 Using slicers and filtering a pivot table Chapter 17 Mashing up data with Power Pivot Chapter 18 Using What-If, Scenario Manager, Goal Seek, and Solver Chapter 19 Automating repetitive functions using VBA macros Chapter 20 More tips and tricks for Excel Part IV Excel visuals Chapter 21 Formatting worksheets Chapter 22 Using data visualizations and conditional formatting Chapter 23 Graphing data using Excel charts Chapter 24 Using 3D Maps Chapter 25 Using sparklines Chapter 26 Formatting spreadsheets for presentation Chapter 27 Printing Chapter 28 Collaborating in Excel Part V Appendixes Appendix A Excel functions Appendix B Excel functions with DAX or Power Query equivalents Appendix C Adding functionality to Excel with add-ins Index

Read more less

Book SynopsisJoan Lambert has worked closely with Microsoft technologies since 1986, and in the training and certification industry since 1997, guiding the translation of technical information and requirements into useful, relevant, and measurable resources for people seeking certification of their computer skills or who simply want to get things done efficiently. She has written more than 50 books about Windows, Office, and SharePoint technologies, including dozens of Step by Step books and five generations of Microsoft Office Specialist certification study guides. Students who use the GO! with Microsoft Office textbook products may overhear her cheerfully demonstrating Office features in the videos that accompany the series. Table of ContentsIntroduction Part 1: Create and format workbooks 1 Set up a workbook 2 Work with data and Excel tables 3 Perform calculations on data 4 Change workbook appearance Part 2: Analyze and present data 5 Manage worksheet data 6 Reorder and summarize data 7 Combine data from multiple sources 8 Analyze alternative data sets 9 Create charts and graphics. 207 10 Create PivotTables and PivotCharts Part 3: Collaborate and share in Excel 11 Print worksheets and charts 12 Automate tasks and input 13 Work with other Microsoft 365 apps 14 Collaborate with colleagues Part 4: Perform advanced analysis 15 Perform business intelligence analysis 16 Create forecasts and visualizations

Read more less

Book SynopsisGet more out of Microsoft Excel and save time by learning Excel VBA (Visual Basic for Applications). Excel VBA in easy steps, 4th edition demonstrates VBA macro programming in a clear and concise manner, and will take your Excel proficiency to the next level:Write your own Excel macros so that you can do a series of tasks at the click of a button.Use VBA to automate repetitive tasks, and for functions and commands beyond Excel. Create dialog boxes for user interactions and then define processes to handle user input. Develop and share your Excel apps with colleagues they'll be impressed! Whether you're upgrading to Excel in Office 365 or new to macro programming, use this guide to master Excel VBA essentials in easy steps! Comes with free downloadable code to help you get started with Excel VBA right away!

Read more less

Book SynopsisExcel Functions and Formulas in easy steps shows you how to use the power of Excel functions and formulas to take full advantage of this popular spreadsheet application. Learn how to: Save time by utilizing Excel functions to carry out often-used operations, for error checking and tracing cells.Build powerful spreadsheets using the features of Excel formulas. Explore all the categories of Excel functions including financial, logical, mathematical, lookup and other data-related functions. Review business-oriented scenarios and examples of functions in each of the groups. Create your own simple macros, and more. Covering all recent versions of Excel including Excel in Office 365, this book will guide you through a myriad of Excel functions and formulas clearly, concisely and in easy steps!

Read more less

Book SynopsisTable of ContentsIntroduction 1 About This Book 1 How to Use This Book 2 What You Can Safely Ignore 2 Foolish Assumptions 2 How This Book Is Organized 3 Part 1: Getting Started with Excel 2019 3 Part 2: Editing Without Tears 4 Part 3: Getting Organized and Staying That Way 4 Part 4: Digging Data Analysis 4 Part 5: Life Beyond the Spreadsheet 4 Part 6: The Part of Tens 5 Conventions Used in This Book 5 Selecting Ribbon commands 5 Icons Used in This Book 6 Beyond the Book 7 Where to Go from Here 7 Part 1: Getting Started with Excel 2019 9 Chapter 1: The Excel 2019 User Experience 11 Excel’s Ribbon User Interface 12 Going Backstage 14 Using the Excel Ribbon 15 Customizing the Quick Access toolbar 21 Having fun with the Formula bar 24 What to do in the Worksheet area 26 Showing off the Status bar 34 Launching and Quitting Excel 35 Starting Excel from the Windows 10 Start menu 35 Starting Excel from the Windows 10 Search text box 36 Starting Excel from the Windows 10 Metro view in Tablet mode 36 Exiting Excel 37 Help Is on the Way 38 Using the Tell Me help feature 39 Chapter 2: Creating a Spreadsheet from Scratch 41 So What Ya Gonna Put in That New Workbook of Yours? 42 The ins and outs of data entry 44 You must remember this 45 Doing the Data-Entry Thing 45 It Takes All Types 48 The telltale signs of text 48 How Excel evaluates its values 49 Fabricating those fabulous formulas! 56 If you want it, just point it out 59 Altering the natural order of operations 59 Formula flub-ups 60 Fixing Those Data Entry Flub-Ups 62 You really AutoCorrect that for me 62 Cell editing etiquette 63 Taking the Drudgery Out of Data Entry 65 I’m just not complete without you 65 Fill ’er up with AutoFill 66 Fill it in a flash 74 Inserting special symbols 76 Entries all around the block 76 Data entry express 77 How to Make Your Formulas Function Even Better 78 Inserting a function into a formula with the Insert Function button 79 Editing a function with the Insert Function button 82 I’d be totally lost without AutoSum 82 Sums via Quick Analysis Totals 85 Making Sure That the Data Is Safe and Sound 86 Changing the default file location 89 The difference between the XLSX and XLS file formats 90 Saving the Workbook as a PDF File 91 Document Recovery to the Rescue 92 Part 2: Editing Without Tears 95 Chapter 3: Making It All Look Pretty 97 Choosing a Select Group of Cells 98 Point-and-click cell selections 99 Keyboard cell selections 104 Using the Format as Table Gallery 106 Customizing table formats 109 Creating a new custom Table Style 110 Cell Formatting from the Home Tab 112 Formatting Cells Close to the Source with the Mini-bar 114 Using the Format Cells Dialog Box 115 Understanding the number formats 116 The values behind the formatting 121 Make it a date! 123 Ogling some of the other number formats 123 Calibrating Columns 124 Rambling rows 125 Now you see it, now you don’t 126 Futzing with the Fonts 127 Altering the Alignment 129 Intent on indents 130 From top to bottom 130 Tampering with how the text wraps 131 Reorienting cell entries 132 Shrink to fit 134 Bring on the borders! 134 Applying fill colors, patterns, and gradient effects to cells 136 Doing It in Styles 137 Creating a new style for the gallery 137 Copying custom styles from one workbook into another 138 Fooling Around with the Format Painter 139 Conditional Formatting 140 Formatting with scales and markers 141 Highlighting cells ranges 142 Formatting via the Quick Analysis tool 143 Chapter 4: Going Through Changes 145 Opening Your Workbooks for Editing 146 Opening files in the Open screen 147 Operating the Open dialog box 148 Changing the Recent files settings 150 Opening multiple workbooks 151 Find workbook files 151 Using the Open file options 152 Much Ado about Undo 152 Undo is Redo the second time around 153 What to do when you can’t Undo? 153 Doing the Old Drag-and-Drop Thing 154 Copies, drag-and-drop style 156 Insertions courtesy of drag and drop 157 Copying Formulas with AutoFill 158 Relatively speaking 159 Some things are absolutes! 160 Cut and Paste, Digital Style 162 Paste it again, Sam 163 Keeping pace with Paste Options 164 Paste it from the Clipboard task pane 166 So what’s so special about Paste Special? 167 Let’s Be Clear About Deleting Stuff 169 Sounding the all clear! 170 Get these cells outta here! 170 Staying in Step with Insert 171 Stamping Out Your Spelling Errors 172 Eliminating Errors with Text to Speech 174 Chapter 5: Printing the Masterpiece 177 Previewing Pages in Page Layout View 178 Using the Backstage Print Screen 180 Printing the Current Worksheet 183 My Page Was Set Up! 184 Using the buttons in the Page Setup group 185 Using the buttons in the Scale to Fit group 190 Using the Print buttons in the Sheet Options group 192 From Header to Footer 192 Adding an Auto Header and Footer 193 Creating a custom header or footer 194 Solving Page Break Problems 199 Letting Your Formulas All Hang Out 201 Part 3: Getting Organized and Staying That Way 203 Chapter 6: Maintaining the Worksheet 205 Zooming In and Out 206 Splitting the Worksheet into Window Panes 209 Fixed Headings with Freeze Panes 210 Electronic Sticky Notes 213 Adding a comment to a cell 214 Comments in review 215 Editing comments in a worksheet 216 Getting your comments in print 217 The Range Name Game 217 If I only had a name 217 Name that formula! 220 Naming constants 221 Seek and Ye Shall Find 222 Replacing Cell Entries 225 Doing Your Research with Smart Lookup 227 Controlling Recalculation 228 Putting on the Protection 229 Chapter 7: Maintaining Multiple Worksheets 233 Juggling Multiple Worksheets 234 Sliding between the sheets 235 Editing en masse 237 Don’t Short-Sheet Me! 238 A worksheet by any other name 239 A sheet tab by any other color 240 Getting your sheets in order 241 Opening Windows on Your Worksheets 242 Comparing Worksheets Side by Side 247 Shifting Sheets to Other Workbooks 248 Summing Stuff on Different Worksheets 252 Part 4: Digging Data Analysis 255 Chapter 8: Doing What-If Analysis 257 Playing What-If with Data Tables 258 Creating a one-variable data table 258 Creating a two-variable data table 261 Playing What-If with Goal Seeking 264 Making the Case with Scenario Manager 266 Setting up the various scenarios 266 Producing a summary report 269 Chapter 9: Playing with Pivot Tables 271 Data Analysis with Pivot Tables 272 Pivot tables via the Quick Analysis tool 272 Pivot tables by recommendation 274 Manually producing pivot tables 276 Formatting Pivot Tables 279 Refining the Pivot Table style 279 Formatting values in the pivot table 280 Sorting and Filtering Pivot Table Data 280 Filtering the report 281 Filtering column and row fields 281 Filtering with slicers 282 Filtering with timelines 283 Sorting the pivot table 284 Modifying Pivot Tables 285 Modifying the pivot table fields 286 Pivoting the table’s fields 286 Modifying the table’s summary function 287 Creating Pivot Charts 288 Moving pivot charts to separate sheets 289 Filtering pivot charts 291 Formatting pivot charts 291 Part 5: Life Beyond the Spreadsheet 293 Chapter 10: Charming Charts and Gorgeous Graphics 295 Making Professional-Looking Charts 296 Charts thanks to Recommendation 296 Charts from the Ribbon 297 Charts via the Quick Analysis tool 299 Charts on their own chart sheets 301 Moving and resizing embedded charts 301 Moving embedded charts to chart sheets 303 Customizing charts from the Design tab 303 Customizing chart elements 304 Editing the generic titles in a chart 307 Adding Great-Looking Graphics 310 Sparking up the data with sparklines 310 Telling all with a text box 312 Inserting online images 315 Inserting 3-D images 318 Inserting local images 319 Editing inserted pictures 319 Formatting inserted 2-D images 320 Formatting inserted 3-D images 322 Adding graphic shapes 323 Adding Icons 324 Working with WordArt 325 Make mine SmartArt 326 Screenshots, anyone? 329 Theme for a day 330 Controlling How Graphic Objects Overlap 330 Reordering the layering of graphic objects 331 Grouping graphic objects 332 Hiding graphic objects 333 Printing Just the Charts 334 Chapter 11: Getting on the Data List 335 Creating Data Lists 335 Adding records to data lists 336 Moving through records in the data form 342 Finding records with the data form 342 Sorting Data Lists 344 Sorting on a single field 345 Sorting on multiple fields 346 Filtering Data Lists 349 Using ready-made number filters 350 Using ready-made date filters 351 Using custom filters 352 Importing External Data 355 Querying Access database tables 355 Performing web queries 357 Chapter 12: Linking, Automating, and Sharing Spreadsheets 359 Using Office Add-ins 360 Using Excel Add-Ins 362 Adding Hyperlinks to a Worksheet 363 Automating Commands with Macros 366 Recording new macros 367 Running macros 371 Assigning macros to the Ribbon and the Quick Access toolbar 372 Sharing Your Worksheets 374 Sharing workbooks saved on OneDrive 374 Getting a sharing link 378 E-mailing workbooks 378 Editing Worksheets in Excel Online 379 Part 6: The Part of Tens 383 Chapter 13: Top Ten Beginner Basics 385 Chapter 14: The Ten Commandments of Excel 2019 387 Chapter 15: Top Ten Ways to Manage Your Data 389 Chapter 16: Top Ten Ways to Analyze Your Data 395 Index 399

Read more less

Book SynopsisRenowned Excel experts Bill Jelen (MrExcel) and Tracy Syrstad explain how to build more powerful, reliable, and efficient Excel spreadsheets. Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable; capture data from anywhere, and use it anywhere; and automate the best new features in Excel 2019 and Excel in Office 365. You’ll find simple, step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and complete, easy-to-adapt solutions. By reading this book, you will: Quickly master Excel macro development Work more efficiently with ranges, cells, and formulas Generate automated reports and quickly adapt them for new requirements Learn to automate pivot tables to summarize, analyze, explore, and present data Use custom dialog boxes to collect data from others using Excel Improve the reliability and resiliency of your macros Integrate data from the internet, Access databases, and other sources Automatically generate charts, visualizations, sparklines, and Word documents Create powerful solutions with classes, collections, and custom functions Solve sophisticated business analysis problems more rapidly About This Book For everyone who wants to get more done with Microsoft Excel in less time For business and financial professionals, entrepreneurs, students, and others who need to efficiently manage and analyze data Table of ContentsIntroduction Unleash the Power of Excel with VBA This Sounds Like BASIC, So Why Doesn't It Look Familiar? Referring to Ranges Looping and Flow Control R1C1-style Formulas Creating and manipulating Names in VBA Event Programming Arrays Creating Classes and Collections Userforms: An Introduction Data Mining with Advanced Filter Using VBA to Create Pivot Tables Excel Power Sample User-Defined Functions Creating Charts Data Visualizations and Conditional Formatting Dashboarding with Sparklines Reading from and Writing to the Web Text File Processing Automating Word Using Access as a Back End to Enhance Multiuser Access to Data Advanced Userform Techniques Windows Application Programming Interface (API) Handling Errors Customizing the Ribbon to Run Macros Creating Add-Ins An Introduction to Creating Office Add-ins What's New in Excel 2019 and What's Changed

Read more less

Book SynopsisRenowned Excel experts Bill Jelen (MrExcel) and Michael Alexander help you crunch data from any source with Excel 2019 pivot tables. Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365. Drawing on more than 45 combined years of Excel experience, Bill Jelen and Michael Alexander offer practical “recipes” for solving real business problems, help you avoid common mistakes, and present tips and tricks you’ll find nowhere else. By reading this book, you will: • Master easy, powerful ways to create, customize, change, and control pivot tables • Control all future pivot tables using new pivot table defaults • Transform huge data sets into clear summary reports • Instantly highlight your most profitable customers, products, or regions • Use Power Query to quickly import, clean, shape, and analyze disparate data sources • Build geographical pivot tables with 3D Map • Construct and share state-of-the-art dynamic dashboards • Revamp analyses on the fly by dragging and dropping fields • Build dynamic self-service reporting systems • Share your pivot tables with colleagues • Create data mashups using the full Power Pivot capabilities of Excel 2019 and Excel in Office 365 • Automate pivot tables with macros and VBA • Save time by adapting reports with GetPivotData • Discover today’s most useful pivot table tips and shortcuts Table of ContentsIntroduction Pivot Table Fundamentals Creating a Basic Pivot Table Customizing a Pivot Table Grouping, Sorting, and Filtering Pivot Data Performing Calculations in Pivot Tables Using Pivot Charts and Other Visualizations Analyzing Disparate Data Sources with Pivot Tables Sharing Dashboards with Power BI Using Cube Formulas with the Data Model or OLAP Data Unlocking Features with the Data Model and Power Pivot Analyzing Geographic Data with 3D Map Enhancing Pivot Table reports with Macros Using VBA to Create Pivot Tables Advanced Pivot Table Tips and Techniques Dr. Jekyll and Mr. GetPivotData

Read more less

Book SynopsisA practical guide to building fully operational financial cash flow models for structured finance transactions Structured finance and securitization deals are becoming more commonplace on Wall Street. Up until now, however, market participants have had to create their own models to analyze these deals, and new entrants have had to learn as they go. Modeling Structured Finance Cash Flows with Microsoft Excel provides readers with the information they need to build a cash flow model for structured finance and securitization deals. Financial professional Keith Allman explains individual functions and formulas, while also explaining the theory behind the spreadsheets. Each chapter begins with a discussion of theory, followed by a section called Model Builder, in which Allman translates the theory into functions and formulas. In addition, the companion website features all of the modeling exercises, as well as a final version of the model that is created in the text. Table of ContentsPreface xi Acknowledgments xiii About the Author xv Introduction 1 The Three Basic Elements of a Cash Flow Model 3 Inputs 3 Cash Flow Structure 4 Outputs 5 The Process of Building a Cash Flow Model 5 Plan and Design 5 Obtain All Necessary Information 6 Construct Basic Framework 6 Develop Advanced Structure 6 Validate Assumptions 6 Test Model 7 How This Book Is Designed 7 Chapter 1 Dates and Timing 9 Time Progression 9 Dates and Timing on the Inputs Sheet 10 Day-Count Systems: 30/360 versus Actual/360 versus Actual/ 365 11 Model Builder 1.1: Inputs Sheet—Dates and Timing 12 Dates and Timing on the Cash Flow Sheet 14 Model Builder 1.2: Cash Flow Sheet—Dates and Timing 15 Toolbox 18 Naming Cells and Ranges 18 Data Validation Lists 19 EDATE 21 Chapter 2 Asset Cash Flow Generation 23 Loan Level versus Representative Line Amortization 23 How Asset Generation Is Demonstrated in Model Builder 27 Asset Generation on the Inputs Sheet 27 Fixed Rate Amortization Inputs 28 Floating Rate Amortization Inputs 28 Model Builder 2.1: Inputs Sheet Asset Assumptions and the Vectors Sheet 29 Asset Generation on the Cash Flow Sheet 33 Model Builder 2.2: Notional Asset Amortization on the Cash Flow Sheet 33 TOOLBOX 40 OFFSET 40 MATCH 40 MOD 41 PMT 41 Chapter 3 Prepayments 43 How Prepayments Are Tracked 43 SMM: Single Monthly Mortality 44 CPR: Conditional Prepayment Rate 44 PSA: Public Securities Association 44 ABS: Absolute Prepayment Speed 45 Historical Prepayment Data Formats 46 Building Prepayment Curves 46 Prepayment Curves in Project Model Builder 47 The Effect of Prepayments on Structured Transactions 48 Model Builder 3.1: Historical Prepayment Analysis and Creating a Projected Prepayment Curve 48 Model Builder 3.2: Integrating Projected Prepayments in Asset Amortization 53 Toolbox 56 Weighted Averages Using SUMPRODUCT and SUM 56 Chapter 4 Delinquency, Default, and Loss Analysis 59 Delinquencies versus Defaults versus Loss 59 The Importance of Analyzing Delinquency 60 Model Builder 4.1: Building Historical Delinquency Curves 62 Deriving Historical Loss Curves 64 Model Builder 4.2: Building Historical and Projected Loss Curves 67 Analyzing Historical Loss Curves 69 Model Builder 4.2 Continued 69 Projecting Loss Curves 70 Model Builder 4.2 Continued 71 Integrating Loss Projections 73 The Effects of Seasoning and Default Timing 75 Model Builder 4.3: Integrating Defaults in Asset Amortization 76 Chapter 5 Recoveries 83 Model Builder 5.1: Historical Recovery Analysis 85 Projecting Recoveries in a Cash Flow Model 86 Model Builder 5.2: Integrating Recoveries into Project Model Builder 87 Final Points Regarding Recoveries 88 Chapter 6 Liabilities and the Cash Flow Waterfall 89 Priority of Payments and the Cash Flow Waterfall 89 The Movement of Cash for an Individual Liability 90 Types of Liabilities 91 Fees 91 Model Builder 6.1: Calculating Fees in the Waterfall 91 Interest 94 Model Builder 6.2: Calculating Interest in the Waterfall 95 Principal 100 Model Builder 6.3: Calculating Principal in the Waterfall 100 Understanding Basic Asset and Liability Interactions 105 Chapter 7 Advanced Liability Structures: Triggers, Interest Rate Swaps, and Reserve Accounts 107 Triggers and Their Affect on the Liability Structure 107 Model Builder 7.1: Incorporating Triggers 108 Swaps 113 Model Builder 7.2: Incorporating a Basic Interest Rate Swap 114 Final Notes on Swaps 117 Reserve Accounts 117 Model Builder 7.3: Incorporating a Cash-Funded Reserve Account 118 Conclusion of the Cash Flow Waterfall 122 Toolbox 123 AND and OR 123 Chapter 8 Analytics and Output Reporting 125 Internal Testing 125 Cash In versus Cash Out 125 Model Builder 8.1: Cash In versus Cash Out Test 126 Balances at Maturity 128 Model Builder 8.2: Balances at Maturity Tests 128 Asset Principal Check 129 Model Builder 8.3: Asset Principal Check Test 129 Performance Analytics 130 Monthly Yield 130 Model Builder 8.4: Calculating Monthly Yield 130 Calculating the Monthly Yield 132 Bond-Equivalent Yield 133 Model Builder 8.5: Calculating Bond-Equivalent Yield 133 Modified Duration 133 Model Builder 8.6: Calculating Modified Duration 134 Output Reporting 135 Model Builder 8.7: Creating the Output Report 136 The Importance of Testing and Output 140 Toolbox 140 Conditional Formatting 140 Goal Seek 141 Array Formulas 142 Chapter 9 Understanding the Model 145 The Complete Model in Review 145 Understanding the Effects of Increased Loss 147 Varying Principal Allocation Methodologies 150 Varying Prepayment Rates 151 Varying Loss Timing 152 Varying Recovery Rate and Lag 152 The Value of a Swap 153 Additional Testing 153 Chapter 10 Automation Using Visual Basic Applications (VBA) 155 Conventions of This Chapter 155 The Visual Basic Editor 156 The Menu Bar 156 The Project Explorer and the Properties Window 157 VBA Code 157 Simple Automation for Printing and Goal Seek 158 Model Builder 10.1: Automating Print Procedures 158 Model Builder 10.2: Automating Goal Seek to Optimize Advance Rates 161 Understanding Looping to Automate the Analytics Sheet 164 Model Builder 10.3: Automating Goal Seek to Perform Transaction Analytics 164 Automated Scenario Generation 167 Model Builder 10.4: Creating a Transaction Scenario Generator 167 Working with Macros in Excel 173 Chapter 11 Conclusion 175 The Investment Banker’s Perspective 175 The Investor’s Perspective 176 The Issuer’s Perspective 176 The Financial Guarantor’s Perspective 177 The Big Picture Perspective 177 Appendix: Using This Book with Excel 2007 179 About the CD-ROM 189 Index 193

Read more less

Book SynopsisThe advanced tools accountants need to build automated, reliable, and scalable reports using Excel Learn about the functions that work together to automate many of the processes involved in Management Reporting. See how to take advantage of the many new features of Excel 2007 and 2010. Find out how to build validation structures into your spreadsheet reports. Discover how to identify missing or new codes, either in the creation process or in the day-to-day running of the reports. Do it all with Advanced Excel Reporting for Management Accountants. Explore the structures that simplify the report creation process and make the reports more maintainable Learn techniques to cleanse data so that it is ready for use in Pivot Tables and formula-based reports Find out the tips and tricks that can make the creation process quicker and easier Discover all you need to know about Excel''s summing functions and how versatile they can be Table of ContentsPreface xiii Acknowledgments xv Introduction 1 Chapter 1 Management Accounting and Excel 3 Assumptions 3 The Goal of Reporting 5 Why Use Excel? 5 The Goal of This Book 6 Monthly Management Reports 7 Macro Policy 7 Chapter 2 Building Reporting Models 9 Needs Analysis 10 Scope Definition 10 Design 11 Construction 11 Testing 12 Operation 12 Maintenance 12 Time, Effort, and Cost 12 Practical Considerations 13 Chapter 3 Building Tips 15 Display Tips 16 Keyboard Shortcuts 26 Mouse Shortcuts 33 Keyboard and Mouse Shortcuts 42 General Tips 48 Chapter 4 Design and Structure 57 Structure = Flexibility 57 Modular Sheet Design 58 Standardised Report Layout 60 Table-Based Systems 62 Spreadsheet Best Practices 63 Chapter 5 Setting the Foundation 67 Terminology 68 Data Rules 68 Data Structures 69 Format as Table 70 Data Cleansing Techniques 74 External Data 80 Chapter 6 Pivot Tables (Do-It-Yourself Reporting) 85 The Pros and Cons of a Pivot Table 85 Creating a Pivot Table 88 PowerPivot 108 Chapter 7 Tools of the Trade: Summing Functions 111 Range Names 112 Using Cells and Ranges in Formulas 112 The Humble SUM Function 116 Advanced SUM and 3D Formulas 117 Subtotaling 120 The SUBTOTAL Function 121 The AGGREGATE Function 125 Function Wizard 127 Conditional Summing 127 The SUMIF Function 131 SUMIF Uses 135 Helper Cells 135 The SUMIFS Function 136 The SUMPRODUCT Function 138 Chapter 8 Accessories: Other Reporting Functions and Features 153 Helper Cells 153 Logic Functions 155 The IF Function 157 The AND and OR Functions 161 Lookup Functions 164 The VLOOKUP Function 164 The HLOOKUP Function 168 An Alternative to VLOOKUP 170 The INDEX and MATCH Functions 170 The MATCH Function 170 The INDEX Function 172 The INDEX-MATCH Combination 174 Error Handling Functions 175 The IFERROR Function 175 Handling Specific Errors 177 Text-Based Functions 180 The TEXT Function 181 LEFT and RIGHT Functions 183 The MID Function 184 Flexible Text Manipulations 185 The SEARCH Function 185 The LEN Function 187 Flexible Splitting 187 The SUBSTITUTE Function 188 Converting Text to Numbers 190 Date Functions 190 The DATE Function 191 Other Useful Functions 192 Array Formulas 201 Chapter 9 Range Names 209 Advantages 210 Disadvantages 210 Creating a Range Name 211 Using Range Names 213 Name Manager 216 Naming a Range 218 Creating Names Automatically 222 Name Intersections 227 Dynamic Range Names 228 Using Structure in Range Names 233 INDIRECT and Range Names 236 Listing Range Names 237 Chapter 10 Maintenance Issues 239 Maintenance Instructions 239 The Advantages of Using Tables 240 Common Issues 241 Rolling the Year 241 Working with Days 242 Simplifying the Interface by Using Controls 244 Chapter 11 Choosing the Right Format 255 Colour Blindness 255 Format Painter 256 Less Is More 256 Fonts 257 Clear and Start Again 257 The Format Cells Dialog Box 257 Styles 270 Conditional Formatting 272 Printing Issues 293 Chapter 12 Picture Perfect: Charting Techniques 299 Chart versus Graph 300 Chart Basics 300 Charts for Reports 302 Automating Charts 302 Mixing Chart Types 307 Dual-Axis Charts 308 Handling Missing Data 311 Labeling Highs and Lows 313 Trendlines and Moving Averages 315 Plotting the Variance 316 Dashboard Techniques 317 Text in a Chart 331 The Data Series Formula 332 Before and After Charts 333 Chapter 13 Quality Control: Report Validation 337 Identifying Errors 337 Validations 338 Error Tracking 340 Identifying New Codes 346 Conditional Formatting 347 Suggested Validation Structure 347 Reasonableness Checks 349 Chapter 14 Case Study One: Month and Year-to-Date Reporting 351 Scenario 351 Data Requirements 352 Processes 352 Structure 354 Design 354 Report Layout 355 The Creation Process 355 The Reports 363 Chapter 15 Case Study Two: 12-Month Reporting 379 Scenario 379 Data Requirements 380 Processes 381 Structure 381 Design 382 The Creation Process 382 The Reports 387 Chapter 16 Final Thoughts 407 Feedback 408 Last Words 408 About the Author 409 About the Companion Website 411 Index 413

Read more less

Book SynopsisCurtis Frye is the author of more than 30 books, including  Microsoft Excel 2013 Step by Step for Microsoft Press and  Brilliant Excel VBA Programming for Pearson, UK. He has also created and recorded more than three dozen courses for lynda.com, including  Excel for Mac 2016 Essential Training and  Excel 2013: PivotTables in Depth. In addition to his work as a writer, Curt is a popular conference speaker and performer, both as a solo presenter and as part of the Portland, Oregon ComedySportz improvisational comedy troupe. He lives in Portland with his wife and three cats.Table of ContentsIntroduction xiii Who this book is for xiii The Step by Step approach xiii Download the practice files xiv Sidebar: Adapt exercise steps xviii Ebook edition xx Get support and give feedback xx Errata and support xx We want to hear from you xx Stay in touch xx Part 1: Create and format workbooks Chapter 1: Set up a workbook 3 Explore the editions of Excel 2016 4 Excel 2016 4 Excel Online 4 Excel Mobile Apps 5 Become familiar with new features in Excel 2016 6 Create workbooks 7 Modify workbooks 13 Modify worksheets 18 Merge and unmerge cells 23 Customize the Excel 2016 app window 26 Zoom in on a worksheet 26 Arrange multiple workbook windows 28 Add buttons to the Quick Access Toolbar 30 Customize the ribbon 33 Skills review 39 Practice tasks 40 Chapter 2: Work with data and Excel tables 43 Enter and revise data 44 Manage data by using Flash Fill 48 Move data within a workbook 51 Find and replace data 56 Correct and expand upon data 60 Define Excel tables 65 Skills review 69 Practice tasks 70 Chapter 3: Perform calculations on data 73 Name groups of data 74 Create formulas to calculate values 77 Sidebar: Operators and Precedence 88 Summarize data that meets specific conditions 90 Set iterative calculation options and enable or disable automatic calculation 96 Use array formulas 98 Find and correct errors in calculations 100 Skills review 106 Practice tasks 107 Chapter 4: Change workbook appearance 111 Format cells 112 Define styles 118 Apply workbook themes and Excel table styles 121 Make numbers easier to read 127 Change the appearance of data based on its value 130 Add images to worksheets 138 Skills review 142 Practice tasks 143 Part 2: Analyze and present dataChapter 5: Manage worksheet data 147 Limit data that appears on your screen 148 Manipulate worksheet data 153 Sidebar: Select list rows at random 154 Summarize data in worksheets that have hidden and filtered rows 155 Find unique values within a data set 161 Define valid sets of values for ranges of cells 163 Skills review 165 Practice tasks 166 Chapter 6: Reorder and summarize data 169 Sort worksheet data 170 Sort data by using custom lists 176 Organize data into levels 179 Look up information in a worksheet 184 Skills review 187 Practice tasks 188 Chapter 7: Combine data from multiple sources 191 Use workbooks as templates for other workbooks 192 Link to data in other worksheets and workbooks 197 Consolidate multiple sets of data into a single workbook 202 Skills review 205 Practice tasks 206 Chapter 8: Analyze alternative data sets 209 Examine data by using the Quick Analysis Lens 210 Define an alternative data set 212 Define multiple alternative data sets 216 Analyze data by using data tables 218 Vary your data to get a specific result by using Goal Seek 221 Find optimal solutions by using Solver 223 Analyze data by using descriptive statistics 229 Skills review 231 Practice tasks 232 Chapter 9: Create charts and graphics 237 Create charts 238 Create new types of charts 248 Customize chart appearance 252 Find trends in your data 260 Create dual-axis charts 263 Summarize your data by using sparklines 264 Create diagrams by using SmartArt 266 Create shapes and mathematical equations 271 Skills review 277 Practice tasks 278 Chapter 10: Create dynamic worksheets by using PivotTables 281 Analyze data dynamically by using PivotTables 282 Filter, show, and hide PivotTable data 290 Edit PivotTables 301 Format PivotTables 305 Create PivotTables from external data 309 Create dynamic charts by using PivotCharts 313 Skills review 316 Practice tasks 317 Part 3: Collaborate and share in Excel Chapter 11: Print worksheets and charts 323 Add headers and footers to printed pages 324 Prepare worksheets for printing 330 Fit your worksheet contents to the printed page 331 Change page breaks in a worksheet 334 Change the page printing order for worksheets 335 Print worksheets 337 Print parts of worksheets 339 Print charts 343 Skills review 345 Practice tasks 346 Chapter 12: Automate repetitive tasks by using macros 349 Enable and examine macros 350 Set macro security levels in Excel 2016 350 Examine macros 353 Create and modify macros 356 Run macros when you click a button 357 Run a macro when you open a workbook 361 Insert form controls into a worksheet 362 Skills review 369 Practice tasks 370 Chapter 13: Work with other Microsoft Office apps 373 Include Office documents in workbooks and other files 374 Link Office documents to Excel workbooks 375 Embed files in Excel and other Office apps 377 Create hyperlinks 379 Paste charts into documents 384 Skills review 385 Practice tasks 386 Chapter 14: Collaborate with colleagues 389 Share workbooks 390 Save workbooks for electronic distribution 394 Manage comments 395 Track and manage colleagues’ changes 398 Add protection to workbooks and worksheets 401 Finalize workbooks 408 Authenticate workbooks 410 Save workbooks for the web 412 Import and export XML data 414 Work with OneDrive and Excel Online 418 Skills review 424 Practice tasks 425 Part 4: Perform advanced analysis Chapter 15: Perform business intelligence analysis 431 Enable the Data Analysis add-ins 432 Define relationships between tables 434 Analyze data by using Power Pivot 438 View data by using timelines 442 Bring in external data by using Power Query 446 Skills review 451 Practice tasks 452 Chapter 16: Create forecasts and visualizations 455 Create Forecast Worksheets 456 Define and manage measures 461 Define and display Key Performance Indicators 464 Create 3D maps 468 Skills review 475 Practice tasks 476 Keyboard shortcuts 479 Glossary 89 Index 95 About the author 521

Read more less

Book SynopsisTable of ContentsIntroduction 1 Part 1: Holy Macro Batman! 7 Chapter 1: Macro Fundamentals 9 Chapter 2: Getting Cozy with the Visual Basic Editor 29 Chapter 3: The Anatomy of Macros 43 Part 2: Making Short Work of Workbook Tasks 57 Chapter 4: Working with Workbooks 59 Chapter 5: Working with Worksheets 81 Part 3: One-Touch Data Manipulation 107 Chapter 6: Feeling at Home on the Range 109 Chapter 7: Manipulating Data with Macros 129 Part 4: Macro-Charging Reports and Emails 163 Chapter 8: Automating Common Reporting Tasks 165 Chapter 9: Sending Emails from Excel 199 Chapter 10: Wrangling External Data with Macros 217 Part 5: Part of Tens 235 Chapter 11: Ten Handy Visual Basic Editor Tips 237 Chapter 12: Ten Places to Turn for Macro Help 247 Chapter 13: Ten Ways to Speed Up Your Macros 253 Index 263

Read more less

Book SynopsisA serious source of information for those looking to reverse engineer business deals It's clear from the current turbulence on Wall Street that the inner workings of its most complex transactions are poorly understood. Wall Street deals parse risk using intricate legal terminology that is difficult to translate into an analytical model. Reverse Engineering Deals on Wall Street: A Step-By-Step Guide takes readers through a detailed methodology of deconstructing the public deal documentation of a modern Wall Street transaction and applying the deconstructed elements to create a fully dynamic model that can be used for risk and investment analysis. Appropriate for the current market climate, an actual residential mortgage backed security (RMBS) transaction is taken from prospectus to model by the end of the book. Step by step, Allman walks the reader through the reversing process with textual excerpts from the prospectus and discussions on how it directly transferTable of ContentsPreface. Acknowledgments. About the Author. CHAPTER 1: Introduction. The Transaction. The Documents. The Process. How This Book Works. CHAPTER 2: Determining Dates and Setting Up Timing. Differences in Timing Approaches. A First Look at the Prospectus. Important Dates. Transforming Dates and Timing from Words to a Model. Model Builder 2.1: Reversing Dates and Timing. Conclusion of Dates and Timing. CHAPTER 3: Creating Asset Cash Flow from Prospectus Data. It’s All in the Prospectus Supplement. The Basics of Amortization. Performance and the Prospectus Supplement. Delinquency. Loss. Prepayment. Recovery. Creating Cash Flow. A Complex Implementation. Model Builder 3.1: Entering in the Raw Asset Information. Model Builder 3.2: Entering in the Default and Prepayment Assumptions. Model Builder 3.3: Interest Rates and Additional Asset Amortization Inputs. Model Builder 3.4: Introducing VBA and Moving Data In and Out of the Model. Model Builder 3.5: Loading Loan Performance Assumptions into VBA. Model Builder 3.6: Global Functions. Model Builder 3.7: Loan-Level Asset Amortization. CHAPTER 4: Setting Up Liability Assumptions, Paying Fees, and Distributing Interest. Identifying the Offered Securities. Model Builder 4.1: Transferring the Liability Information to a Consolidated Sheet. The Liability Waterfall: A System of Priority. Model Builder 4.2: Starting the Waterfall with Fees. Interest: No Financing Is Free. Model Builder 4.3: Continuing the Waterfall with Interest Paid to the Certificate Holders. More on Waterfalls and Wall Street’s Risk Parsing. Model Builder 4.4: Mezzanine Interest. Continuing the Waterfall: It Only Gets More Complicated. CHAPTER 5: Principal Repayment and the Shifting Nature of a Wall Street Deal. Model Builder 5.1: The Deal State and Senior Principal. Mezzanine Principal Returns. Model Builder 5.2: The Mezzanine Certificates’ Priority of Payments. Number Games or Risk Parsing? CHAPTER 6: Credit Enhancement Mechanisms to Mitigate Loss. Model Builder 6.1: Excess Spread, Overcollateralization, and Credit Enhancement. CHAPTER 7: Auditing the Model. Model Builder 7.1. CHAPTER 8: Conclusion of Example Transaction and Final Thoughts on Reverse Engineering. Mortgage Insurance and Servicer Advances. Reverse Engineering in the Current and Future Market. Appendix. Automatic Range Naming. About the CD-ROM. Index.

Read more less

Book Synopsis Joan Lambert has worked closely with Microsoft technologies since 1986, and in the training and certification industry since 1997, guiding the translation of technical information and requirements into useful, relevant, and measurable resources for people who are seeking certification of their computer skills or who simply want to get things done efficiently. Joan is the author or coauthor of more than four dozen books about Windows and Office (for Windows, Mac, and iPad), six generations of Microsoft Office Specialist certification study guides, video-based training courses for SharePoint and OneNote, QuickStudy guides for Windows 10 and Office 2016, and GO! series books for Outlook. Joan is a Microsoft Certified Professional, Microsoft Office Specialist Master (for all versions of Office since Office 2003), Microsoft Certified Technology Specialist (for Windows and Windows Server), Microsoft Certified Technology Associate (for Windows), Microsoft DyTable of Contents 1 Manage worksheets and workbooks Objective 1.1: Import data into workbooks Objective 1.2: Navigate within workbooks Objective 1.3: Format worksheets and workbooks Objective 1.4: Customize options and views Objective 1.5: Configure content for collaboration 2 Manage data cells and ranges Objective 2.1: Manipulate data in worksheets Objective 2.2: Format cells and ranges Objective 2.3: Define and reference named ranges Objective 2.4: Summarize data visually 3 Manage tables and table data Objective 3.1: Create and format tables Objective 3.2: Modify tables Objective 3.3: Filter and sort table data 4 Perform operations by using formulas and functions Objective 4.1: Insert references in formulas Objective 4.2: Calculate and transform data by using functions Objective 4.3: Format and modify text by using functions 5 Manage charts Objective 5.1: Create charts Objective 5.2: Modify charts Objective 5.3: Format charts

Read more less

Book SynopsisExcel-erate your Excel 365 knowledge with the celebrated Teach Yourself VISUALLY series In Teach Yourself VISUALLY: Excel 365, veteran tech educator and writer Paul McFedries delivers a practical, step-by-step guide to using the latest version of the world's most popular spreadsheet app. The book is filled with easy two-page lessons containing large-size screenshots and walkthroughs demonstrating common and useful tasks in Excel 365. You'll learn how to present, analyze, visualize, and organize your data, as well as how to: Create and fill spreadsheets, charts, tables, and PivotTablesFilter and sort your data, use and create formulas, and organize your informationVisualize your data with a variety of effective charts, graphs, and moreA can't-miss handbook perfect for anyone looking for straightforward and easy-to-follow tutorials on basic and advanced Excel techniques, Teach Yourself VISUALLY: Excel 365 offers users of previous Excel versionsand complete Excel newbiesa clear roadmap toTable of ContentsChapter 1 Getting Started with Excel Getting to Know Excel 4 Start Excel on the Desktop 6 Navigate to Excel for the Web 7 Tour the Excel Window 8 Work with the Excel Ribbon 9 Chapter 2 Entering Data Learning the Layout of a Worksheet 12 Understanding the Types of Data You Can Use 13 Enter Text into a Cell 14 Enter a Number into a Cell 16 Enter a Date or Time into a Cell 18 Edit Cell Data 20 Delete Data from a Cell 22 Chapter 3 Working with Ranges Select a Range 26 Fill a Range with the Same Data 28 Fill a Range with a Series of Values 30 Flash Fill a Range 32 Move or Copy a Range 34 Insert a Row or Column 36 Insert a Cell or Range 38 Delete Data from a Range 40 Delete a Range 42 Hide a Row or Column 44 Freeze Rows or Columns 46 Merge Two or More Cells 48 Chapter 4 Working with Range Names Understanding the Benefits of Using Range Names 52 Define a Range Name 54 Using Worksheet Text to Define a Range Name 56 Navigate a Workbook Using Range Names 58 Change a Range Name 60 Delete a Range Name 62 Chapter 5 Formatting Excel Ranges Change the Font and Font Size 66 Apply Font Effects 68 Change the Font Color 70 Align Text Within a Cell 72 Center Text Across Multiple Columns 74 Rotate Text Within a Cell 76 Add a Background Color to a Range 78 Apply a Number Format 80 Change the Number of Decimal Places Displayed 82 Apply a Conditional Format to a Range 84 Apply a Style to a Range 86 Change the Column Width 88 Change the Row Height 90 Wrap Text Within a Cell 92 Add Borders to a Range 94 Copy Formatting from One Cell to Another 96 Chapter 6 Building Formulas Understanding Excel Formulas 100 Build a Formula 102 Understanding Excel Functions 104 Add a Function to a Formula 106 Add a Range of Numbers 108 Build an AutoSum Formula 110 Add a Range Name to a Formula 112 Reference Another Worksheet Range in a Formula 114 Move or Copy a Formula 116 Switch to Absolute Cell References 118 Troubleshoot Formula Errors 120 Chapter 7 Manipulating Worksheets Navigate a Worksheet 124 Rename a Worksheet 125 Create a New Worksheet 126 Move a Worksheet 128 Copy a Worksheet 130 Delete a Worksheet 132 Zoom In on or Out of a Worksheet 134 Split a Worksheet into Two Panes 136 Hide and Unhide a Worksheet 138 Chapter 8 Dealing with Workbooks Create a New Blank Workbook 142 Create a New Workbook from a Template 144 Save a Workbook 146 Open a Workbook 147 Find Text in a Workbook 148 Replace Text in a Workbook 150 Chapter 9 Formatting Workbooks Modify the Workbook Colors 154 Set the Workbook Fonts 156 Choose Workbook Effects 158 Apply a Workbook Theme 160 Add a Workbook Header 162 Add a Workbook Footer 164 Chapter 10 Importing Data into Excel Understanding External Data 168 Import Data from an Access Table 170 Import Data from a Word Table 172 Import Data from a Text File 174 Import Data from a Web Page 176 Import Data from an XML File 178 Refresh Imported Data 180 Separate Cell Text into Columns 182 Chapter 11 Working with Tables Understanding Tables 186 Get to Know Table Features 187 Convert a Range to a Table 188 Select Table Data 190 Insert a Table Row 192 Insert a Table Column 193 Delete a Table Row 194 Delete a Table Column 195 Add a Column Subtotal 196 Convert a Table to a Range 198 Apply a Table Style 199 Chapter 12 Analyzing with PivotTables Understanding PivotTables 202 Explore PivotTable Features 203 Build a PivotTable from an Excel Range or Table 204 Refresh PivotTable Data 206 Add Multiple Fields to the Row or Column Area 208 Add Multiple Fields to the Data Area 210 Move a Field to a Different Area 212 Group PivotTable Values 214 Apply a PivotTable Filter 216 Chapter 13 Analyzing Data Sort a Range or Table 220 Filter a Range or Table 222 Set Data Validation Rules 224 Create a Data Table 226 Summarize Data with Subtotals 228 Group Related Data 230 Analyze Data with Goal Seek 232 Highlight Cells That Meet Some Criteria 234 Highlight the Top or Bottom Values in a Range 236 Analyze Cell Values with Data Bars 238 Analyze Cell Values with Color Scales 240 Analyze Cell Values with Icon Sets 242 Chapter 14 Visualizing Data with Charts Examine Chart Elements 246 Understanding Chart Types 247 Create a Chart 248 Create a Recommended Chart 250 Add Chart Titles 252 Add Data Labels 253 Position the Chart Legend 254 Display Chart Gridlines 255 Display a Data Table 256 Change the Chart Layout and Style 257 Select a Different Chart Type 258 Change the Chart Source Data 260 Move or Resize a Chart 262 Chapter 15 Adding Worksheet Graphics Draw a Shape 266 Insert an Online Image 268 Insert a Photo from Your PC 270 Insert a SmartArt Graphic 272 Move or Resize a Graphic 274 Format a Picture 276 Chapter 16 Collaborating with Others Add a Comment to a Cell 280 Protect a Worksheet’s Data 282 Protect a Workbook’s Structure 284 Save a Workbook to Your OneDrive 286 Send a Workbook as an Email Attachment 288 Save Excel Data as a Web Page 290 Make a Workbook Compatible with Earlier Versions of Excel 292 Mark Up a Worksheet with Digital Ink 294 Collaborate on a Workbook Online 296 Index 298

Read more less

Book SynopsisExcel tips to skyrocket white-collar productivity. Originally designed for Bill Jelen's live Power Excel seminars, the target audience already uses Excel 40 hours a week. These tips are the "aha" tips that uncover secret methods in Excel. The book covers general Excel functions, pivot tables, and formulas such as VLOOKUP and the new XLOOKUP. It introduces elements of modern Excel such as the Power Pivot Data Model and cleaning data with Power Query. Updated annually, this edition for 2021 adds information on LET and LAMBDA functions, amazing new data types, dynamic array formulas, and more.Table of Contents1 - Fast Formula Copy2 - Break Apart Data3 - Convert text #'s4 - Filter by Selection5 - Total Visible Rows6 - The Fill Handle 1, 2, 3… 7 - Fast Worksheet Copy8 - Defaults Future WB9 - Recover Unsaved Workbooks10 - Search in File, Open11 - Show Changes 60 Days12 - Collab13 - Sheet View14 - Threaded Comments 15 - Perfect 1-Click Charts16 - Paste New Data on a Chart17 - Create Interactive Charts18 - Chart 2 Orders of Magnitude19 - Waterfall Charts20 - Funnel Charts21 - Map Charts22 - Bell Curve23 - Plot People on a Bell Curve24 - Data Visualizations25 - Cutout People26 - Semi-Transparent Image27 - Save any object as image28 - List Format29 - Sort Using a Custom List30 - Sort Left to Right31 - Sort Subtotals32 - Sort & Filter by Color / Icon33 - Consolidate Qtr Worksheets34 - Create Your First Pivot Table35 - PT Year-over-Year36 - Change PT Calculation37 - True Top 5 in a PT38 - Defaults for All Future PT39 - PT Expandable Using Ctrl+T40 - Replicate a PT for Each Rep41 - Use a PT to Compare Lists42 - Sparklines and Slicers43 - GETPIVOTDATA use44 - Ask a Question45 - Data Model to Eliminate XLOOKUP46 - Budg v. Act'l via Power Pivot47 - PT Slicers from 2 Data Sets48 - F4 for $A$1 or Repeat49 - Formulas to Values50 - See All Formulas51 - Audit With Inquire52 - New Fx by Using fx 53 - Fx Args Nested Fx54 - Nonstandard Work Weeks55 - Sideways with a Formula56 - Multiple Conditions IF57 - Troubleshoot VLOOKUP58 - Wildcard in Lookup59 - 12 Benefits of XLOOKUP60 - Preview Remove Duplicates61 - Nested IFs v. Lookup62 - Suppress Errors with IFERROR63 - Plural Conditions w/ SUMIFS64 - Geo, XE & Stock Data Types 65 - STOCKHISTORY66 - Wolfram Alpha67 - Historical Weather for City68 - Create Data Types using PQ69 - Which language in Bilingual70 - Dynamic Arrays Can Spill71 - Sorting with a Formula72 - Filter with a Formula73 - Formula for Unique or Distinct74 - Fx Can Accept Arrays75 - 1 Hit Wonders with UNIQUE76 - SEQUENCE inside IPMT77 - Random #'s in Excel78 - PT with 3 DA79 - Dependent Validation DA80 - Complex Validation Formula81 - Stack Arrays82 - Drop, Take, Choose from Array83 - Reshaping Arrays84 - UNIQUE of Rectangular Range85 - Shuffle & Deal Cards86 - A2:INDEX() non-Volatile OFFSET87 - Microsoft 365 features88 - Perf Improvements89 - Unhide Multiple Worksheets90 - Action Pen91 - Take Panes Collapse92 - Feedback to the Excel Team93 - Date Tricks94 - LET95 - LAMBDA96 - Lamda helper functions97 - MAXIFS98 - TEXTBEFORE or AFTER99 - TEXTSPLIT100 - Less CSV Nagging+Autocomplete101 - Protect All Formula Cells102 - Goal Seek103 - Do 60 What-If Analyses104 - Solver105 - Interpolate106 - Improve Macro Recording107 - Clean Data Power Query108 - Fuzzy Match in PQ109 - PT on a Map 3D Maps110 - Forecast Seasonality111 - Sentiment Analysis112 - Visio Flowcharts113 - Fill in a Flash114 - Format as a Faç ade115 - Taskbar all open workbooks116 - Paste to Other Computer117 - Surveys & Forms in Excel118 - Windows Magnifier119 - Scrolling Tricks120 - Word for Excellers121 - Speak Cells122 - Customize QAT123 - QAT VBA Macros124 - Fav Keyb Shortcuts125 - Ctrl+Click to Unselect126 - Collapse the Search Box127 - More Excel Tips128 - Excel Stories129 - Quick Fx Reference

Read more less

Book SynopsisTable of ContentsExcel Module 1: Creating a Worksheet and a Chart. Excel Module 2: Formulas, Functions, and Formatting. Excel Module 3: Working with Large Worksheets, Charting, and What-If Analysis. Excel Module 4: Financial Functions, Data Tables, and Amortization Schedules. Excel Module 5: Working with Multiple Worksheets and Workbooks. Excel Module 6: Creating, Sorting, and Querying a Table. Excel Module 7: Creating Templates, Importing Data, and Working with SmartArt, Images, and Screenshots. Excel Module 8: Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers. Excel Module 9: Formula Auditing, Data Validation, and Complex Problem Solving. Excel Module 10: Data Analysis with Power Tools and Creating Macros. Excel Module 11: User Interfaces, Visual Basic for Applications (VBA), and Collaboration Features in Excel.

Read more less

Book SynopsisMicrosoft Excel in easy steps guides you through the essential functions of Microsoft Excel whether you are new to Excel and the spreadsheet concept, or just upgrading from an older version. This edition is illustrated using the most up-to-date version of Excel in Microsoft 365 at the time of printing. Microsoft Excel in easy steps starts with the basics then reveals the key clever features on offer all in easy steps! Areas covered include:Creating, editing, and manipulating worksheets.Formulas, functions, tables and importing data. Handy templates to give you a head start. Macros for everyday tasks to save time. Charts to get an overview of your data. What-if, Goal Seek and Optimization. Using the Excel Online app to share and collaborate on spreadsheets and workbooks. Adding images and illustrations to your spreadsheets to impress your colleagues! Whether you're new to Microsoft Excel or new to spreadsheets, use this guide to get more out of Microsoft Excel, and manage your finances and data effectively both at home and at work!

Read more less

Book SynopsisConrad Carlberg is a nationally recognized expert on quantitative analysis, data analysis, and management applications such as Microsoft Excel, SAS, and Oracle. He holds a Ph.D. in statistics from the University of Colorado and is a many-time recipient of Microsoft's Excel MVP designation. He is the author of many books, including Business Analysis with Microsoft Excel, Fifth Edition, Statistical Analysis: Microsoft Excel 2016, Regression Analysis Microsoft Excel, and R for Microsoft Excel Users. Carlberg is a Southern California native. After college he moved to Colorado, where he worked for a succession of startups and attended graduate school. He spent two years in the Middle East, teaching computer science and dodging surly camels. After finishing graduate school, Carlberg worked at US West (a Baby Bell) in product management and at Motorola. In 1995 he started a small consulting business (www.conradcarlberg.com)Table of ContentsPrefaceChapter 1 Bayesian Analysis and R: An Overview Bayes Comes Back About Structuring Priors Watching the Jargon Priors, Likelihoods, and Posteriors The Prior The Likelihood Contrasting a Frequentist Analysis with a Bayesian The Frequentist Approach The Bayesian Approach SummaryChapter 2 Generating Posterior Distributions with the Binomial Distribution Understanding the Binomial Distribution Understanding Some Related Functions Working with R's Binomial Functions Using R's dbinom Function Using R's pbinom Function Using R's qbinom Function Using R's rbinom Function Grappling with the Math SummaryChapter 3 Understanding the Beta Distribution Establishing the Beta Distribution in Excel Comparing the Beta Distribution with the Binomial Distribution Decoding Excel's Help Documentation for BETA.DIST Replicating the Analysis in R Understanding dbeta Understanding pbeta Understanding qbeta About Confidence Intervals Applying qbeta to Confidence Intervals Applying BETA.INV to Confidence Intervals SummaryChapter 4 Grid Approximation and the Beta Distribution More on Grid Approximation Setting the Prior Using the Results of the Beta Function Tracking the Shape and Location of the Distribution Inventorying the Necessary Functions Looking Behind the Curtains Moving from the Underlying Formulas to the Functions Comparing Built-in Functions with Underlying Formulas Understanding Conjugate Priors SummaryChapter 5 Grid Approximation with Multiple Parameters Setting the Stage Global Options Local Variables Specifying the Order of Execution Normal Curves, Mu and Sigma Visualizing the Arrays Combining Mu and Sigma Putting the Data Together Calculating the Probabilities Folding in the Prior Inventorying the Results Viewing the Results from Different Perspectives SummaryChapter 6 Regression Using Bayesian Methods Regression a la Bayes Sample Regression Analysis Matrix Algebra Methods Understanding quap Continuing the Code A Full Example Designing the Multiple Regression Arranging a Bayesian Multiple Regression SummaryChapter 7 Handling Nominal Variables Using Dummy Coding Supplying Text Labels in Place of Codes Comparing Group Means SummaryChapter 8 MCMC Sampling Methods Quick Review of Bayesian Sampling Grid Approximation Quadratic Approximation MCMC Gets Up To Speed A Sample MCMC Analysis ulam's Output Validating the Results Getting Trace Plot Charts Summary and Concluding ThoughtsAppendix Installation Instructions for RStan and the rethinking Package on the Windows PlatformGlossary Downloadable Bonus Content Excel Worksheets Book: Statistical Analysis: Microsoft Excel 2016 (PDF) 9780137580989 TOC 10/24/2022

Read more less

Book SynopsisMaster business modeling and analysis techniques with Microsoft Excel 2019, and transform data into bottom-line results. Written by award-winning educator Wayne Winston, this hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables–and how to effectively build a relational data source inside an Excel workbook.Table of Contents Chapter 1 Basic spreadsheet modeling Chapter 2 Range names Chapter 3 Lookup functions Chapter 4 The INDEX function Chapter 5 The MATCH function Chapter 6 Text functions Chapter 7 Dates and date functions Chapter 8 Evaluating investment by using net present value criteria Chapter 9 Internal rate of return Chapter 10 More Excel financial functions Chapter 11 Circular references Chapter 12 IF statements Chapter 13 Time and time functions Chapter 14 The Paste Special command Chapter 15 Three-dimensional formulas and hyperlinks Chapter 16 The auditing tool Chapter 17 Sensitivity analysis with data tables Chapter 18 The Goal Seek command Chapter 19 Using the Scenario Manager for sensitivity analysis Chapter 20 The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions Chapter 21 The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS functions Chapter 22 The OFFSET function Chapter 23 The INDIRECT function Chapter 24 Conditional formatting Chapter 25 Sorting in Excel Chapter 26 Tables Chapter 27 Spin buttons, scroll bars, option buttons, check boxes, combo boxes, and group list boxes Chapter 28 The analytics revolution Chapter 29 An introduction to optimization with Excel Solver Chapter 30 Using Solver to determine the optimal product mix Chapter 31 Using Solver to schedule your workforce Chapter 32 Using Solver to solve transportation or distribution problems Chapter 33 Using Solver for capital budgeting Chapter 34 Using Solver for financial planning Chapter 35 Using Solver to rate sports teams Chapter 36 Warehouse location and the GRG Multistart and Evolutionary Solver engines Chapter 37 Penalties and the Evolutionary Solver Chapter 38 The traveling salesperson problem Chapter 39 Importing data from a text file or document Chapter 40 Validating data Chapter 41 Summarizing data by using histograms and Pareto charts Chapter 42 Summarizing data by using descriptive statistics Chapter 43 Using PivotTables and slicers to describe data Chapter 44 The Data Model Chapter 45 Power Pivot Chapter 46 Power View and 3D Maps Chapter 47 Sparklines Chapter 48 Summarizing data with database statistical functions Chapter 49 Filtering data and removing duplicates Chapter 50 Consolidating data Chapter 51 Creating subtotals Chapter 52 Charting tricks Chapter 53 Estimating straight-line relationships Chapter 54 Modeling exponential growth Chapter 55 The power curve Chapter 56 Using correlations to summarize relationships Chapter 57 Introduction to multiple regression Chapter 58 Incorporating qualitative factors into multiple regression Chapter 59 Modeling nonlinearities and interactions Chapter 60 Analysis of variance: One-way ANOVA Chapter 61 Randomized blocks and two-way ANOVA Chapter 62 Using moving averages to understand time series Chapter 63 Winters method Chapter 64 Ratio-to-moving-average forecast method Chapter 65 Forecasting in the presence of special events Chapter 66 An introduction to probability Chapter 67 An introduction to random variables Chap

Read more less

Book SynopsisTable of ContentsAbout This Book ix 1 • Microsoft Excel Basics 1 1.1 Introduction to Worksheets 2 1.2 How to Use This Book 2 1.3 Typographic Conventions Used in This Book 3 1.4 Understanding the Excel 2010 Screen 4 1.4.1 Title Bar 5 1.4.2 Ribbon 6 1.4.3 Formula Bar 8 1.4.4 Work Area 9 1.4.5 Sheet Tabs 10 1.4.6 Status Bar 10 1.5 Getting Help 10 1.5.1 Browsing the Help Topic List 11 1.5.2 Searching the Help System 12 1.6 Creating and Saving Worksheets and Workbooks 12 1.6.1 Creating a New Workbook 12 1.6.2 Opening an Existing Workbook 13 1.6.3 Creating a New Worksheet 14 1.6.4 Introduction to Templates 15 1.6.5 Opening Workbooks with Macros 18 1.6.6 Saving Documents 18 1.6.7 The AutoRecover Feature 21 1.6.8 Naming Documents 22 1.7 Moving Around a Worksheet 23 1.7.1 Movement by Using the Keyboard 24 1.7.2 Movement by Using the Mouse 25 1.7.3 Movement by Using the Go To Dialog Box 25 1.8 Selecting a Region 27 1.8.1 Selection by Using Cell References 27 1.8.2 Selection by Using the Mouse 27 1.8.3 Selection by Using the Keyboard 28 1.9 Cutting, Moving, Copying, and Pasting 29 1.9.1 Cutting a Region 30 1.9.2 Moving a Region (Cut and Paste) 30 1.9.3 Copying a Region 31 1.10 Inserting and Deleting Cells 31 1.10.1 Deleting Cells 31 1.10.2 Clearing Cells 32 1.10.3 Inserting Cells 32 1.11 Shortcut Keys 32 1.12 Finding and Correcting Mistakes 33 1.12.1 Undoing Mistakes 33 1.12.2 Checking Spelling 34 1.12.3 The AutoCorrect Feature 35 1.13 Printing 36 1.13.1 Setting the Print Area 36 1.13.2 Previewing a Worksheet 36 1.13.3 Printing a Worksheet 38 2 • Entering and Formatting Data 44 2.1 Introduction to Entering and Formatting Data 44 2.2 Entering Data 45 2.2.1 Entering Numeric Data 45 2.2.2 Entering Text Data 50 2.2.3 Entering Date and Time Data 52 2.3 Using the Fill Handle 54 2.3.1 Using the Fill Handle with the Left Mouse Button to Copy Cells 55 2.3.2 Using the Fill Handle with the Left Mouse Button–More Copy Options 55 2.3.3 Creating a Linear Series with a Non-Unity Increment 56 2.3.4 Using the Fill Handle with the Right Mouse Button for Additional Options 56 2.4 Formatting for Appearance 58 2.4.1 Changing the Appearance of Cells 58 2.4.2 Changing the Appearance of Columns and Rows 72 2.5 Working with Worksheets 74 2.5.1 Inserting New Worksheets into a Workbook 75 2.5.2 Renaming a Worksheet 75 2.5.3 Changing the Color of the Worksheet Tab 75 2.5.4 Hiding a Worksheet 75 2.5.5 Locking a Worksheet 76 2.6 Formatting a Data Set as an Excel Table 78 2.6.1 Creating an Excel Table 78 2.6.2 Sorting Data in Excel Tables 80 2.6.3 Filtering Data in Excel Tables 81 2.6.4 Using a Total Row with Excel Tables 82 2.7 Conditional Formatting 85 3 • Formulas and Functions 99 3.1 Introduction 99 3.2 Referencing Cells and Cell Ranges 100 3.2.1 Cell References 100 3.2.2 Referencing a Range of Cells 101 3.2.3 Naming a Cell or Range of Cells 102 3.3 Creating and Using Formulas 103 3.3.1 Formula Syntax 103 3.3.2 Arithmetic Operators and Operator Precedence 104 3.4 Using Excel’s Built-In Functions 106 3.4.1 Examples of Statistical Functions 110 3.4.2 Examples of Trigonometric Functions 113 3.4.3 Examples of Matrix Operations 115 3.4.4 Examples of Financial Functions 118 3.5 Absolute and Relative Cell References 120 3.6 Excel Error Messages 124 3.7 Debugging Excel Worksheets 124 3.7.1 Highlighting Formulas 125 3.7.2 Tracing Dependents 127 3.7.3 Tracing Precedents 127 3.8 Using Macros to Automate Computations 129 3.8.1 Recording a Macro 130 3.8.2 Running a Macro 134 3.8.3 Editing a Macro 134 4 • Working with Charts 149 4.1 Introduction 149 4.2 Creating a Basic XY Scatter Chart 150 4.2.1 Creating a Basic XY Scatter

Read more less

Book Synopsis  Margo Chaney Adkins is an Assistant Professor of Information Technology at Carroll Community College in Westminster, Maryland. She holds a bachelor's degree in Information Systems and master's degree in Post-Secondary Education from Salisbury University. She teaches computer application and office technology courses, both online and in the classroom. She enjoys athletic activities, gardening, and traveling with her husband.   Catherine Hain is an instructor at Central New Mexico Community College in Albuquerque, New Mexico. She teaches computer applications classes in the Business and Information Technology School, both in the classroom and through the distance learning office. Catherine holds a bachelor's degree in Management and Marketing and a master's degree in Business Administration.   Lisa Hawkins Table of Contents Part 1 | Getting Started with Microsoft Office Applications Chapter 1 Common Features of Office 2016 Part 2 | Microsoft Office Microsoft Excel Introduction to Excel Chapter 1 Create Workbooks with Excel 2016 Chapter 2 Insert Summary Functions and Create Charts Chapter 3 Manage Multiple Worksheets Chapter 4 More Functions and Excel Tables Chapter 5 Format Cells and Worksheets Chapter 6 Insert Advanced Functions and Create Scenarios Chapter 7 Work with Data and Audit Formulas Chapter 8 Manage and Present Data Visually Chapter 9 Link and Analyze Data in PivotTables and PivotCharts Chapter 10 Secure and Share Workbooks Glossary Appendix Index

Read more less

Book SynopsisAbout our authors Amy S. Kinser, Esq., Series Editor, holds a BA degree in chemistry with a business minor from Indiana University, and a JD from the Maurer School of Law, also at Indiana University. After working as an environmental chemist, starting her own technology consulting company, and practicing intellectual property law, she has spent the past 15 years teaching technology at the Kelley School of Business in Bloomington, Indiana. Currently, she serves as Director of Computer Skills and senior lecturer at the Kelley School of Business at Indiana University. She loves spending time with her two sons, Aidan and J. Matthew, and her husband, J. Eric. Kristyn A. Jacobson holds an MS degree in education from the University of Wisconsin-La Crosse and a BS degree in business education from the University of Wisconsin-Eau Claire. She has been a faculty member and department chair of the Business Technology department at Madison CollegTable of ContentsTable of Contents COMMON FEATURES Understanding the Common Features of Microsoft Office EXCEL EXCEL BUSINESS UNIT 1: UNDERSTANDING THE FUNDAMENTALS Create, Navigate, Work with, and Print Worksheets Formats, Functions, and Formulas Excel Business Unit 1 Capstone EXCEL BUSINESS UNIT 2: CONDUCTING BUSINESS ANALYSIS Cell References, Named Ranges, and Functions Effective Charts Excel Business Unit 2 Capstone EXCEL BUSINESS UNIT 3: INTEGRATING COMPLEX FUNCTIONS INTO BUSINESS ANALYSIS Complex Logical and Retrieval Functions Integrating Complex Functions into Business Analysis Excel Business Unit 3 Capstone EXCEL BUSINESS UNIT 4: BUILD AN APPLICATION WITH MULTIPLE WORKSHEETS AND WORKBOOKS Multiple Worksheets, Workbooks, and Templates Building an Application with Multiple Worksheets and Workbooks Excel Business Unit 4 Capstone EXCEL BUSINESS UNIT 5: MANIPULATING DATA SETS FOR DECISION MAKING Organize, Import, Export, and Cleanse Data Sets Data Tables, Scenario Manager, and Solver Excel Business Unit 5 Capstone EXCEL BUSINESS UNIT 6: BUILDING FINANCIAL AND STATISTICAL MODELS Loan Amortization, Investment Analysis, and Asset Depreciation Business Statistics and Regression Analysis Excel Business Unit 6 Capstone EXCEL BUSINESS UNIT 7 The Excel Data Model and Business Intelligence Visual Basic for Applications--VBA Excel Business Unit 7 Capstone Appendices Leveraging Microsoft’s Power Business Intelligence (‘BI’) Desktop Suite Mac Appendix MOS Core MOS Expert

Read more less

Book SynopsisAbout our authors Mary Anne Poatsy, Series Editor, Windows 10 Author, is a senior faculty member at Montgomery County Community College, teaching various computer applications and concepts courses in face-to-face and online environments. She holds a BA in psychology and education from Mount Holyoke College and an MBA in finance from Northwestern University's Kellogg Graduate School of Management. Mary Anne has over 20 years of educational experience. She has taught at Montgomery County Community College and Muhlenberg College. She also engages in corporate training. Before teaching, she was Vice President at Shearson Lehman in the Municipal Bond Investment Banking Department. Dr. Keith Mulbery, Excel Author, is department chair and a professor in the Information Systems and Technology Department at Utah Valley University (UVU), where he currently teaches systems analysis and design, and global and ethical issues in informationTable of ContentsBrief Contents OFFICE Office 365 Common Features EXCEL Introduction to Excel Formulas and Functions Charts Datasets and Tables Subtotals, PivotTables, and PivotCharts What-If Analysis Specialized Functions Statistical Functions Multiple-Sheet Workbook Management Power Add-Ins Additional Specialized Functions Templates, Workbook Inspection, and Macros APPLICATION CAPSTONE EXERCISE Excel Application Capstone Exercise

Read more less

Book Synopsis

Read more less

Book Synopsis

Read more less

Book SynopsisDavid M. Levine and David F. Stephan are part of a writing team known for their series of business statistics textbooks that include Basic Business Statistics, Business Statistics: A First Course, and Statistics for Managers Using Microsoft Excel. In long teaching careers at Baruch College, both were known for their classroom innovations, with Levine being honored with a Presidential Excellence Award for Distinguished Teaching Award and Stephan granted the privilege to design and develop the College's first computer-based classroom. Both are active members of the Data, Analytics and Statistics Instruction SIG of the Decision Sciences Institute. Levine is Professor Emeritus of Information Systems at Baruch College. He is nationally recognized innovator in business statistics education and is also the coauthor of Applied Statistics for Engineers and Scientists Using Microsoft Excel and Minitab. Levine is also the authorTable of ContentsIntroduction The Even You Can Learn Statistics and Analytics Owner's Manual. xiii Chapter 1 Fundamentals of Statistics. 1 1.1 The First Three Words of Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.2 The Fourth and Fifth Words. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.3 The Branches of Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.4 Sources of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 1.5 Sampling Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 1.6 Sample Selection Methods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Chapter 2 Presenting Data in Tables and Charts . 15 2.1 Presenting Categorical Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.2 Presenting Numerical Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 2.3 “Bad” Charts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Chapter 3 Descriptive Statistics. 45 3.1 Measures of Central Tendency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 3.2 Measures of Position. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 3.3 Measures of Variation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 3.4 Shape of Distributions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Chapter 4 Probability. 75 4.1 Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 4.2 More Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 4.3 Some Rules of Probability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 4.4 Assigning Probabilities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Chapter 5 Probability Distributions. 87 5.1 Probability Distributions for Discrete Variables. . . . . . . . . . . . . . . . . . . . . . . . 87 5.2 The Binomial and Poisson Probability Distributions. . . . . . . . . . . . . . . . . . . . 93 5.3 Continuous Probability Distributions and the Normal Distribution . . . . . . . 100 5.4 The Normal Probability Plot. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 Chapter 6 Sampling Distributions and Confidence Intervals. 121 6.1 Foundational Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 6.2 Sampling Error and Confidence Intervals. . . . . . . . . . . . . . . . . . . . . . . . . . . 125 6.3 Confidence Interval Estimate for the Mean Using the t Distribution (? Unknown). . . 128 6.4 Confidence Interval Estimation for Categorical Variables . . . . . . . . . . . . . . . 131 6.5 Confidence Interval Estimation When Normality Cannot Be Assumed. . . . . 134 Chapter 7 Fundamentals of Hypothesis Testing. 145 7.1 The Null and Alternative Hypotheses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 7.2 Hypothesis Testing Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 7.3 Decision-Making Risks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 7.4 Performing Hypothesis Testing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 7.5 Types of Hypothesis Tests. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Chapter 8 Hypothesis Testing: Z and t Tests. 157 8.1 Test for the Difference Between Two Proportions . . . . . . . . . . . . . . . . . . . . . 157 8.2 Test for the Difference Between the Means of Two Independent Groups . . . . 163 8.3 The Paired t Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 Chapter 9 Hypothesis Testing: Chi-Square Tests and the One-Way Analysis of Variance (ANOVA). 183 9.1 Chi-Square Test for Two-Way Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 9.2 One-Way Analysis of Variance (ANOVA): Testing for the Differences Among the Means of More Than Two Groups. . . . . . . . . . . . . . . 191 Chapter 10 Simple Linear Regression. 211 10.1 Basics of Regression Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211 10.2 Developing a Simple Linear Regression Model. . . . . . . . . . . . . . . . . . . . . . 214 10.3 Measures of Variation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221 10.4 Inferences About the Slope. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226 10.5 Common Mistakes When Using Regression Analysis . . . . . . . . . . . . . . . . . 229 Chapter 11 Multiple Regression. 243 11.1 The Multiple Regression Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 11.2 Coefficient of Multiple Determination . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 11.3 The Overall F Test . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 11.4 Residual Analysis for the Multiple Regression Model . . . . . . . . . . . . . . . . . 247 11.5 Inferences Concerning the Population Regression Coefficients. . . . . . . . . . 248 Chapter 12 Introduction to Analytics. 259 12.1 Basic Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 12.2 Descriptive Analytics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265 12.3 Typical Descriptive Analytics Visualizations. . . . . . . . . . . . . . . . . . . . . . . . 269 Chapter 13 Predictive Analytics. 279 13.1 Predictive Analytics Methods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 13.2 More About Predictive Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 13.3 Tree Induction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284 13.4 Clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287 13.5 Association Analysis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 Appendix A Microsoft Excel Operation and Configuration . 299 Appendix B Review of Arithmetic and Algebra. 301 Appendix C Statistical Tables. 311 Appendix D Spreadsheet Tips . 339 Appendix E Advanced Techniques. 343 Appendix F Documentation for Downloadable Files. 353 9780137654765, TOC, 4/25/2022

Read more less

Book SynopsisGain the hands-on experience and knowledge to solve real financial problems while taking your Excel spreadsheet skills to a new level with Mayes' FINANCIAL ANALYSIS WITH MICROSOFT EXCEL, 9E. This edition provides a reader-friendly solid foundation in corporate finance while teaching you to maximize the spreadsheet tools that professionals use every day. Packed with interesting examples, this edition covers today's most important corporate finance topics and tools, including financial statements, budgets, the Security Market Security Line, pro forma financial statements, cost of capital, Visual Basic Applications (VBA) programming and Excel pivot tables. You study the latest information on time series forecasting and work with Excel's Get & Transform feature to process large data files. This edition's self-directed learning approach and numerous self-study tools let you strengthen spreadsheet skills while equipping you with the expertise today's employers want in corporate finance.Table of Contents1. Introduction to Excel. 2. The Basic Financial Statements. 3. Financial Statement Analysis Tools. 4. The Cash Budget. 5. Financial Statement Forecasting. 6. Forecasting Sales with Time Series Methods. 7. Break-Even and Leverage Analysis. 8. The Time Value of Money. 9. Common Stock Valuation. 10. Bond Valuation. 11. The Cost of Capital. 12. Capital Budgeting. 13. Risk and Capital Budgeting. 14. Portfolio Statistics and Diversification. 15. Writing User-Defined Functions with VBA. 16. Analyzing Datasets with Tables and Pivot Tables. Appendix: Directory of User-Defined Functions in FameFncs.xlam.

Read more less

Book SynopsisUsing Microsoft Excel, the market leading spreadsheet package, this book combines theory with modelling aspects and spreadsheet analysis. Microeconomics Using Excel provides students with the tools with which to better understand microeconomic analysis.It focuses on solving microeconomic problems by integrating economic theory, policy analysis and spreadsheet modelling. This unique approach facilitates a more comprehensive understanding of the link between theory and problem solving.It is divided into four core parts: analysis of price policies analysis of structural policies multi-market models budget policy and priority settings. The theory behind each problem is explained and each model is solved using Excel. Microeconomics using Excel will be of great interest to students studying economics as well as to professionals in economic and policy analysis.Publisherâs NoteTable of ContentsIntroduction Part A: Analysis of Price Policies 1. Supply, Demand and Price Policies 2. Welfare and Distributional Effects 3. Price Policy Instruments 4. Iso-Elastic Supply and Demand Functions 5. Policy Formulation and Trade-Offs 6. External Effects 7. Integrated Markets 8. World Market and Third Country Effects Part B: Analysis of Structural Policies 9. Shifts of the Supply Curve 10. Implications of Structural Policies over Time 11. Optimal Structural Policies Part C: Multi-Market Models 12. Interdependencies of Markets 13. Microeconomic Foundations 14. Formulation of a 4-Market Model 15. Model Framework for a 12-Market Model Part D: Budget Policy and Priority Setting 16. Optimization Approach 17. Multiple Objectives 18. Parametric Analysis

Read more less

Book SynopsisReviews from previous editions: "Excel for Chemists should be part of any academic library offering courses and programs in Chemistry. " Choice "I highly recommend the book; treat yourself to it; assign it to a class; give it as a gift.Trade Review“Finally this new edition, provides chemists and students a detailed guide and examples how to apply the current versions of Excel to their needs. It should be added to the shelves of those using this program within their scientific work.” (Materials and Corrosion, 1 November 2012)Table of ContentsPreface to the Third Edition xxv Before You Begin xxvii Part I The Basics Chapter 1 Working with Excel 2007 or Excel 2010 3 Chapter 2 Working with Excel 2003 79 Chapter 3 Excel Formulas and Functions 137 Chapter 4 Excel 2007/2010 Charts 177 Chapter 5 Excel 2003 Charts 209 Part II Advanced Spreadsheet Topics Chapter 6 Advanced Worksheet Formulas 233 Chapter 7 Array Formulas 267 Chapter 8 Advanced Charting Techniques 289 Chapter 9 Using Excel's Database Features 327 Chapter 10 Importing Data into Excel 349 Chapter 11 Adding Controls to a Spreadsheet 365 Chapter 12 Other Language Versions of Excel 385 Part III Spreadsheet Mathematics Chapter 13 Mathematical Methods for Spreadsheet Calculations 403 Chapter 14 Linear Regression and Curve Fitting 435 Chapter 15 Nonlinear Regression Using the Solver 463 Part IV Excel's Visual Basic for Applications Chapter 16 Visual Basic for Applications: An Introduction 491 Chapter 17 Programming with VBA 503 Chapter 18 Working with Arrays in VBA 543 Part V Some Applications of VBA Chapter 19 Command Macros 557 Chapter 20 Custom Functions 571 Chapter 21 Automatic Procedures 589 Chapter 22 Custom Menus 595 Chapter 23 Custom Toolbars and Toolbuttons 607 Part VI Appendices Appendix A What's Where in Excel 2007/2010 629 Appendix B Selected Worksheet Functions by Category 633 Appendix C Alphabetical List of Selected Worksheet Functions 639 Appendix D Renamed Functions in Excel 2010 661 Appendix E Selected Visual Basic Keywords by Category 663 Appendix F Alphabetical List of Selected Visual Basic Keywords 667 Appendix G Selected Excel 4 Macro Functions 689 Appendix H Shortcut Keys by Keystroke 693 Appendix I Selected Shortcut Keys by Category 703 Appendix J ASCII Codes 707 Appendix K Contents of the CD-ROM 709 Index 719

Read more less

Book SynopsisA comprehensive reference to the newest version of the world s most popular spreadsheet application: Excel 2010 John Walkenbach's name is synonymous with excellence in computer books that decipher complex technical topics. Known as Mr.

Read more less

Book SynopsisThe bestselling Excel book on the market, updated for Excel 2010 As the world's leading spreadsheet application, Excel has a huge user base.Table of ContentsIntroduction 1 Part I: Getting In on the Ground Floor 9 Chapter 1: The Excel 2010 User Experience 11 Chapter 2: Creating a Spreadsheet from Scratch 49 Part II: Editing without Tears 95 Chapter 3: Making It All Look Pretty 97 Chapter 4: Going Through Changes 145 Chapter 5: Printing the Masterpiece 175 Part III: Getting Organized and Staying That Way 199 Chapter 6: Maintaining the Worksheet 201 Chapter 7: Maintaining Multiple Worksheets 229 Part IV: Digging Data Analysis 253 Chapter 8: Doing What-If Analysis 255 Chapter 9: Playing with Pivot Tables 267 Part V: Life beyond the Spreadsheet 283 Chapter 10: Charming Charts and Gorgeous Graphics 285 Chapter 11: Getting on the Data List 319 Chapter 12: Linking, Automating, and Sharing Spreadsheets 345 Part VI: The Part of Tens 363 Chapter 13: Top Ten Features in Excel 2010 365 Chapter 14: Top Ten Beginner Basics 369 Chapter 15: The Ten Commandments of Excel 2010 371 Index 373

Read more less

Book SynopsisIt is common to blame the inadequacy of credit risk models for the fact that the financial crisis has caught many market participants by surprise. On closer inspection, though, it often appears that market participants failed to understand or to use the models correctly.Table of ContentsPreface to the 2nd edition xi Preface to the 1st edition xiii Some Hints for Troubleshooting xv 1 Estimating Credit Scores with Logit 1 Linking scores, default probabilities and observed default behavior 1 Estimating logit coefficients in Excel 4 Computing statistics after model estimation 8 Interpreting regression statistics 10 Prediction and scenario analysis 12 Treating outliers in input variables 16 Choosing the functional relationship between the score and explanatory variables 20 Concluding remarks 25 Appendix 25 Logit and probit 25 Marginal effects 25 Notes and literature 26 2 The Structural Approach to Default Prediction and Valuation 27 Default and valuation in a structural model 27 Implementing the Merton model with a one-year horizon 30 The iterative approach 30 A solution using equity values and equity volatilities 35 Implementing the Merton model with a T -year horizon 39 Credit spreads 43 CreditGrades 44 Appendix 50 Notes and literature 52 Assumptions 52 Literature 53 3 Transition Matrices 55 Cohort approach 56 Multi-period transitions 61 Hazard rate approach 63 Obtaining a generator matrix from a given transition matrix 69 Confidence intervals with the binomial distribution 71 Bootstrapped confidence intervals for the hazard approach 74 Notes and literature 78 Appendix 78 Matrix functions 78 4 Prediction of Default and Transition Rates 83 Candidate variables for prediction 83 Predicting investment-grade default rates with linear regression 85 Predicting investment-grade default rates with Poisson regression 88 Backtesting the prediction models 94 Predicting transition matrices 99 Adjusting transition matrices 100 Representing transition matrices with a single parameter 101 Shifting the transition matrix 103 Backtesting the transition forecasts 108 Scope of application 108 Notes and literature 110 Appendix 110 5 Prediction of Loss Given Default 115 Candidate variables for prediction 115 Instrument-related variables 116 Firm-specific variables 117 Macroeconomic variables 118 Industry variables 118 Creating a data set 119 Regression analysis of LGD 120 Backtesting predictions 123 Notes and literature 126 Appendix 126 6 Modeling and Estimating Default Correlations with the Asset Value Approach 131 Default correlation, joint default probabilities and the asset value approach 131 Calibrating the asset value approach to default experience: the method of moments 133 Estimating asset correlation with maximum likelihood 136 Exploring the reliability of estimators with a Monte Carlo study 144 Concluding remarks 147 Notes and literature 147 7 Measuring Credit Portfolio Risk with the Asset Value Approach 149 A default-mode model implemented in the spreadsheet 149 VBA implementation of a default-mode model 152 Importance sampling 156 Quasi Monte Carlo 160 Assessing Simulation Error 162 Exploiting portfolio structure in the VBA program 165 Dealing with parameter uncertainty 168 Extensions 170 First extension: Multi-factor model 170 Second extension: t-distributed asset values 171 Third extension: Random LGDs 173 Fourth extension: Other risk measures 175 Fifth extension: Multi-state modeling 177 Notes and literature 179 8 Validation of Rating Systems 181 Cumulative accuracy profile and accuracy ratios 182 Receiver operating characteristic (ROC) 185 Bootstrapping confidence intervals for the accuracy ratio 187 Interpreting caps and ROCs 190 Brier score 191 Testing the calibration of rating-specific default probabilities 192 Validation strategies 195 Testing for missing information 198 Notes and literature 201 9 Validation of Credit Portfolio Models 203 Testing distributions with the Berkowitz test 203 Example implementation of the Berkowitz test 206 Representing the loss distribution 207 Simulating the critical chi-square value 209 Testing modeling details: Berkowitz on subportfolios 211 Assessing power 214 Scope and limits of the test 216 Notes and literature 217 10 Credit Default Swaps and Risk-Neutral Default Probabilities 219 Describing the term structure of default: PDs cumulative, marginal and seen from today 220 From bond prices to risk-neutral default probabilities 221 Concepts and formulae 221 Implementation 225 Pricing a CDS 232 Refining the PD estimation 234 Market values for a CDS 237 Example 239 Estimating upfront CDS and the ‘Big Bang’ protocol 240 Pricing of a pro-rata basket 241 Forward CDS spreads 242 Example 243 Pricing of swaptions 243 Notes and literature 247 Appendix 247 Deriving the hazard rate for a CDS 247 11 Risk Analysis and Pricing of Structured Credit: CDOs and First-to-Default Swaps 249 Estimating CDO risk with Monte Carlo simulation 249 The large homogeneous portfolio (LHP) approximation 253 Systemic risk of CDO tranches 256 Default times for first-to-default swaps 259 CDO pricing in the LHP framework 263 Simulation-based CDO pricing 272 Notes and literature 281 Appendix 282 Closed-form solution for the LHP model 282 Cholesky decomposition 283 Estimating PD structure from a CDS 284 12 Basel II and Internal Ratings 285 Calculating capital requirements in the Internal Ratings-Based (IRB) approach 285 Assessing a given grading structure 288 Towards an optimal grading structure 294 Notes and literature 297 Appendix A1 Visual Basics for Applications (VBA) 299 Appendix A2 Solver 307 Appendix A3 Maximum Likelihood Estimation and Newton’s Method 313 Appendix A4 Testing and Goodness of Fit 319 Appendix A5 User-defined Functions 325 Index 333

Read more less

Book SynopsisThis book is an introduction to the modelling of cash collateralised debt obligations (CDOs). It is intended that the reader have a basic understanding of CDOs and a basic working knowledge of Microsoft Office Excel. There will be written explanations of concepts along with understandable mathematical explanations and examples provided in Excel.Table of ContentsForeword. Acknowledgments. 1 Introduction. 1.1 To Excel or Not to Excel? 1.2 Existing Tools and Software. 2 What is a Cash CDOs? 2.1 Types of CDOs. 2.2 Description of a Cash Flow CDO. 2.3 Life Cycle of a Cash CDO. 2.4 Contribution to the “Credit Crunch”. 3 Introduction to Modelling. 3.1 Goals in Modelling. 3.2 Modelling Philosophies and Trade-Offs. 3.3 Flexibility. 3.4 Organization and Layout of a Model. 3.5 Life-Cycle Issues: Building an Adaptable Model. 4 Prerequisites to Cash Flow Modelling. 4.1 Modelling Dates. 4.2 Interest Rate Curve Modelling. 4.3 Present Value Modelling. 5 Getting Started. 5.1 Create the Input Sheet. 5.2 The Value of Labelling. 6 Modelling Assets. 6.1 Initial Asset Pool: Rep Line Modelling vs. Actual Assets. 6.2 The Collateral Sheet in the Cash Flow Model. 6.3 Modelling Defaults and Recoveries. 6.4 Amortization. 6.5 Modelling Reinvestment. 6.6 Reinvestment Cohorts. 6.7 Accounts. 6.8 Timing Models vs. Actual Timing. 6.9 Simple Warehouse Modelling. 7 Basic Waterfall Modelling. 7.1 Basic Waterfalls. 7.2 Layout and Design. 7.3 Avoiding Negative Values. 7.4 Timing Modelled vs. Actual Timing. 7.5 Liabilities Cash Flows. 7.6 Fees and Expenses Cash Flows. 7.7 Interest Waterfall. 7.8 Interest Waterfall (Available Funds after Payment). 7.9 Interest Waterfall Calculations. 7.10 Principal Waterfall. 7.11 Principle Waterfall (Available Funds after Payment). 7.12 Principal Waterfall Calculations. 7.13 Adding Over-Collaterization Tests. 7.14 Adding Interest Coverage Tests. 7.15 Technical Issues with Coverage Tests. 8 Outputs Sheet. 8.1 Purpose of the Outputs Sheet. 8.2 Collating Waterfall Outputs. 8.3 Present Value. 8.4 Duration. 8.5 Weighted Average Life and Internal Rate of Return. 8.6 Equity Analysis. 8.7 Basic Auditing. 9 Moody’s Rating Agency Methodology. 9.1 Introduction to Agency Methodologies. 9.2 The Bet Approach. 9.3 Evaluating the Collateral. 9.4 Creating the Moody’s Sheet and Related References in the Cash Flow Model. 9.5 Default Profiles. 9.6 Interest Rate Profiles. 9.7 Running the Analysis. 9.8 Variations on the BET. 9.9 2009 Methodology Update. 10 Standard & Poor’s Rating Methodology. 10.1 The S&P Approach. 10.2 Evaluating the Collateral. 10.3 Modelling Recovery Rates. 10.4 CDO Evaluator. 10.5 Default Rates. 10.6 Interest Rate Stresses. 10.7 Amortization. 10.8 Additional S&P Modelling Criteria. 10.9 Building the S7P Sheet and Related References. 10.10 Running the Stress Scenarios. 11 Advanced Waterfall Modelling. 11.1 Hedge Agreements. 11.2 Fixed Notes. 11.3 Variable Funding Notes. 11.4 Liquidity Facilities. 11.5 Interest Reserve Accounts. 11.6 Other Structural Features. 11.7 Combination Notes. 11.8 Collateral Manager Equity Analysis. 12 Maintaining the Cash Flow Model. 12.1 Adapting Your Model for Different Capital Structures. 12.2 Audit Sheet. 12.3 Debugging. 13 Advanced Structuring Issues. 13.1 Projecting Accrued Interest. 13.2 Collating Collateral Cash Flows. 14 Sourcing and Integrating Data From External Systems. 14.1 Data Requirements. 14.2 Trustee Reports. 14.3 Bloomberg. 14.4 Loan Level Information Sources. 15 Regulatory Applications of CDO Technology. 15.1 The Basel Accords. 15.2 Regulatory Capital Requirements for CDO Notes. 15.3 The Standardized Approach for CDOs. 15.4 The Internal Ratings-Based Approach for CDOs. 15.5 The Internal Ratings-Based Approach for CDOs: The Ratings-Based Approach. 15.6 The Internal Ratings-Based Approach for CDOs: The Supervisory Formula Approach. 15.7 The Internal Ratings-Based Approach: Liquidity Facilities, Overlapping Exposures, Credit Risk Mitigation and Early Amortization Features. 15.8 Supervisory Provisions. 15.9 Updates to Basel II. 16 CDO Valuation. 16.1 Introduction. 16.2 Basic Valuation Approaches. 16.3 Traditional Underwriter Analysis. 16.4 Fundamental Cash Flow Analysis. 16.5 Using Rating Agency Models. 16.6 Transition Matrices. 16.7 Conclusion. 17 In Conclusion. Index.

Read more less

Book SynopsisThis new and unique book demonstrates that Excel and VBA can play an important role in the explanation and implementation of numerical methods across finance. Advanced Modelling in Finance provides a comprehensive look at equities, options on equities and options on bonds from the early 1950s to the late 1990s. The book adopts a step-by-step approach to understanding the more sophisticated aspects of Excel macros and VBA programming, showing how these programming techniques can be used to model and manipulate financial data, as applied to equities, bonds and options. The book is essential for financial practitioners who need to develop their financial modelling skill sets as there is an increase in the need to analyse and develop ever more complex ''what if'' scenarios. Specifically applies Excel and VBA to the financial markets Packaged with a CD containing the software from the examples throughout the book Note: CD-ROM/DVD and other suppTrade ReviewNo. 4 bestseller in 'General Finance' (erivativesreview.com, December 2001)Table of ContentsPreface xi Acknowledgements xii 1 Introduction 1 1.1 Finance insights 1 1.2 Asset price assumptions 2 1.3 Mathematical and statistical problems 2 1.4 Numerical methods 2 1.5 Excel solutions 3 1.6 Topics covered 3 1.7 Related Excel workbooks 5 1.8 Comments and suggestions 5 Part One Advanced Modelling in Excel 7 2 Advanced Excel functions and procedures 9 2.1 Accessing functions in Excel 9 2.2 Mathematical functions 10 2.3 Statistical functions 12 2.3.1 Using the frequency function 12 2.3.2 Using the quartile function 14 2.3.3 Using Excel’s normal functions 15 2.4 Lookup functions 16 2.5 Other functions 18 2.6 Auditing tools 19 2.7 Data Tables 20 2.7.1 Setting up Data Tables with one input 20 2.7.2 Setting up Data Tables with two inputs 22 2.8 XY charts 23 2.9 Access to Data Analysis and Solver 26 2.10 Using range names 27 2.11 Regression 28 2.12 Goal Seek 31 2.13 Matrix algebra and related functions 33 2.13.1 Introduction to matrices 33 2.13.2 Transposing a matrix 33 2.13.3 Adding matrices 34 2.13.4 Multiplying matrices 34 2.13.5 Matrix inversion 35 2.13.6 Solving systems of simultaneous linear equations 36 2.13.7 Summary of Excel’s matrix functions 37 Summary 37 3 Introduction to VBA 39 3.1 Advantages of mastering VBA 39 3.2 Object-oriented aspects of VBA 40 3.3 Starting to write VBA macros 42 3.3.1 Some simple examples of VBA subroutines 42 3.3.2 MsgBox for interaction 43 3.3.3 The writing environment 44 3.3.4 Entering code and executing macros 44 3.3.5 Recording keystrokes and editing code 45 3.4 Elements of programming 47 3.4.1 Variables and data types 48 3.4.2 VBA array variables 48 3.4.3 Control structures 50 3.4.4 Control of repeating procedures 51 3.4.5 Using Excel functions and VBA functions in code 52 3.4.6 General points on programming 53 3.5 Communicating between macros and the spreadsheet 53 3.6 Subroutine examples 56 3.6.1 Charts 56 3.6.2 Normal probability plot 59 3.6.3 Generating the efficient frontier with Solver 61 Summary 65 References 65 Appendix 3A The Visual Basic Editor 65 Stepping through a macro and using other debug tools 68 Appendix 3B Recording keystrokes in ‘relative references’ mode 69 4 Writing VBA user-defined functions 73 4.1 A simple sales commission function 73 4.2 Creating Commission(Sales) in the spreadsheet 74 4.3 Two functions with multiple inputs for valuing options 75 4.4 Manipulating arrays in VBA 78 4.5 Expected value and variance functions with array inputs 79 4.6 Portfolio variance function with array inputs 81 4.7 Functions with array output 84 4.8 Using Excel and VBA functions in user-defined functions 85 4.8.1 Using VBA functions in user-defined functions 85 4.8.2 Add-ins 86 4.9 Pros and cons of developing VBA functions 86 Summary 87 Appendix 4A Functions illustrating array handling 88 Appendix 4B Binomial tree option valuation functions 89 Exercises on writing functions 94 Solution notes for exercises on functions 95 Part Two Equities 99 5 Introduction to equities 101 6 Portfolio optimisation 103 6.1 Portfolio mean and variance 103 6.2 Risk–return representation of portfolios 105 6.3 Using Solver to find efficient points 106 6.4 Generating the efficient frontier (Huang and Litzenberger’s approach) 109 6.5 Constrained frontier portfolios 111 6.6 Combining risk-free and risky assets 113 6.7 Problem One–combining a risk-free asset with a risky asset 114 6.8 Problem Two–combining two risky assets 115 6.9 Problem Three–combining a risk-free asset with a risky portfolio 117 6.10 User-defined functions in Module1 119 6.11 Functions for the three generic portfolio problems in Module1 120 6.12 Macros in ModuleM 121 Summary 123 References 123 7 Asset pricing 125 7.1 The single-index model 125 7.2 Estimating beta coefficients 126 7.3 The capital asset pricing model 129 7.4 Variance–covariance matrices 130 7.5 Value-at-Risk 131 7.6 Horizon wealth 134 7.7 Moments of related distributions such as normal and lognormal 136 7.8 User-defined functions in Module1 136 Summary 138 References 138 8 Performance measurement and attribution 139 8.1 Conventional performance measurement 140 8.2 Active–passive management 141 8.3 Introduction to style analysis 144 8.4 Simple style analysis 145 8.5 Rolling-period style analysis 146 8.6 Confidence intervals for style weights 148 8.7 User-defined functions in Module1 151 8.8 Macros in ModuleM 151 Summary 152 References 153 Part Three Options on Equities 155 9 Introduction to options on equities 157 9.1 The genesis of the Black–Scholes formula 158 9.2 The Black–Scholes formula 158 9.3 Hedge portfolios 159 9.4 Risk-neutral valuation 161 9.5 A simple one-step binomial tree with risk-neutral valuation 162 9.6 Put–call parity 163 9.7 Dividends 163 9.8 American features 164 9.9 Numerical methods 164 9.10 Volatility and non-normal share returns 165 Summary 165 References 166 10 Binomial trees 167 10.1 Introduction to binomial trees 167 10.2 A simplified binomial tree 168 10.3 The Jarrow and Rudd binomial tree 170 10.4 The Cox, Ross and Rubinstein tree 173 10.5 Binomial approximations and Black–Scholes formula 175 10.6 Convergence of CRR binomial trees 176 10.7 The Leisen and Reimer tree 177 10.8 Comparison of CRR and LR trees 178 10.9 American options and the CRR American tree 180 10.10 User-defined functions in Module0 and Module1 182 Summary 183 References 184 11 The Black–Scholes formula 185 11.1 The Black–Scholes formula 185 11.2 Black–Scholes formula in the spreadsheet 186 11.3 Options on currencies and commodities 187 11.4 Calculating the option’s ‘greek’ parameters 189 11.5 Hedge portfolios 190 11.6 Formal derivation of the Black–Scholes formula 192 11.7 User-defined functions in Module1 194 Summary 195 References 196 12 Other numerical methods for European options 197 12.1 Introduction to Monte Carlo simulation 197 12.2 Simulation with antithetic variables 199 12.3 Simulation with quasi-random sampling 200 12.4 Comparing simulation methods 202 12.5 Calculating greeks in Monte Carlo simulation 203 12.6 Numerical integration 203 12.7 User-defined functions in Module1 205 Summary 207 References 207 13 Non-normal distributions and implied volatility 209 13.1 Black–Scholes using alternative distributional assumptions 209 13.2 Implied volatility 211 13.3 Adapting for skewness and kurtosis 212 13.4 The volatility smile 215 13.5 User-defined functions in Module1 217 Summary 219 References 220 Part Four Options on Bonds 221 14 Introduction to valuing options on bonds 223 14.1 The term structure of interest rates 224 14.2 Cash flows for coupon bonds and yield to maturity 225 14.3 Binomial trees 226 14.4 Black’s bond option valuation formula 227 14.5 Duration and convexity 228 14.6 Notation 230 Summary 230 References 230 15 Interest rate models 231 15.1 Vasicek’s term structure model 231 15.2 Valuing European options on zero-coupon bonds, Vasicek’s model 234 15.3 Valuing European options on coupon bonds, Vasicek’s model 235 15.4 CIR term structure model 236 15.5 Valuing European options on zero-coupon bonds, CIR model 237 15.6 Valuing European options on coupon bonds, CIR model 238 15.7 User-defined functions in Module1 239 Summary 240 References 241 16 Matching the term structure 243 16.1 Trees with lognormally distributed interest rates 243 16.2 Trees with normal interest rates 246 16.3 The Black, Derman and Toy tree 247 16.4 Valuing bond options using BDT trees 248 16.5 User-defined functions in Module1 250 Summary 252 References 252 Appendix Other VBA functions 253 Forecasting 253 ARIMA modelling 254 Splines 256 Eigenvalues and eigenvectors 257 References 258 Index 259

Read more less

Book SynopsisAlberto Ferrari has achieved Microsoft SQL Server Analysis Services (SSAS) Maestro status and is a consultant and trainer who specializes in developing Microsoft BI solutions. He is the coauthor of Microsoft PowerPivot for Excel 2010 and Expert Cube Development with Microsoft SQL Server 2008 Analysis Services and SQLBI Methodology.Table of Contents Introduction Chapter 1: Introduction to PowerPivot Chapter 2: Using the unique features of PowerPivot Chapter 3: Introducing DAX Chapter 4: Understanding data models Chapter 5: Publishing to SharePoint Chapter 6: Loading data Chapter 7: Understanding evaluation contexts Chapter 8: Understanding CALCULATE Chapter 9: Using hierarchies Chapter 10: Using Power View Chapter 11: Shaping the reports Chapter 12: Performing date calculations in DAX Chapter 13: Using advanced DAX Chapter 14: Using DAX as a query language Chapter 15: Automating operations using VBA Chapter 16: Comparing Excel and SQL Server Analysis Services About the Authors

Read more less