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 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

Book SynopsisPaul McFedries is a full-time technical author who has worked with computers in one form or another since 1975. He is the author of more than 90 computer books that have sold more than 4 million copies worldwide. His recent Office-related titles include My Office 2016, My Office 2016 for Mac, and Formulas and Functions for Microsoft Excel 2016. His other titles include Windows 10 In Depth, (with Brian Knittel), PCs for Grownups, and Fixing Your Computer Absolute Beginner's Guide. Table of ContentsChapter 1 — Manage and share workbooks 1.1 Manage workbooks 1.2 Manage workbook review Chapter 2 — Apply custom formats and layouts 2.1 Apply custom data formats 2.2 Apply advanced conditional formatting and filtering 2.3 Apply custom styles and templates 2.4 Prepare workbooks for internationalization and accessibility Chapter 3 — Create advanced formulas 3.1 Apply functions in formulas 3.2 Look up data by using functions 3.3 Apply advanced date and time functions 3.4 Perform data analysis and business intelligence 3.5 Troubleshoot formulas 3.6 Define named ranges and objects Chapter 4 — Create advanced charts and tables 4.1 Create advanced chart elements 4.2 Create and manage PivotTables 4.3 Create and manage PivotCharts

Read more less

Book SynopsisJoan Lambert has worked closely with Microsoft technologies since 1986, and in the training and certification industry since 1997. As President and CEO of Online Training Solutions, Inc. (OTSI), Joan guides 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 know how 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), five generations of Microsoft Office Specialist certification study guides, video-based training courses for SharePoint and OneNote, QuickStudy guides for Windows and Office, and the GO! series book for Outlook 2016. Blissfully based in America's Finest City, Joan is a Microsoft CertifiedTable of Contents Introduction Taking a Microsoft Office Specialist exam Exam 77-727: Microsoft Excel 2016 Core Prerequisites Objective group 1: Create and manage worksheets and workbooks Objective 1.1: Create worksheets and workbooks Objective 1.2: Navigate in worksheets and workbooks Objective 1.3: Format worksheets and workbooks Objective 1.4: Customize options and views for worksheets and workbooks Objective 1.5: Configure worksheets and workbooks for distribution Objective group 2: Manage data cells and ranges Objective 2.1: Insert data in cells and ranges Objective 2.2: Format cells and ranges Objective 2.3: Summarize and organize data Objective group 3: Create tables Objective 3.1: Create and manage tables Objective 3.2: Manage table styles and options Objective 3.3: Filter and sort tables Objective group 4: Perform operations by using formulas and functions Objective 4.1: Summarize data by using functions Objective 4.2: Perform conditional operations by using functions Objective 4.3: Format and modify text by using functions Objective group 5: Create charts and objects Objective 5.1: Create charts Objective 5.2: Format charts Objective 5.3: Insert and format objects

Read more less

Book SynopsisIn this day and age of too much information and not enough time,' the ability to get to the bottom line quickly and in a concise method is what excels companies to the top of their industry. The techniques in this book will allow you to do things you only dreamt of.Jerry Kohl, president of Brighton Collectibles Develop your Excel macro programming skills using VBA instantly with proven techniques Automate Reports Handle Errors Master Pivot Tables Produce Charts Build User-Defined Functions Migrate to Excel 2007 Query Web Data Build Dialog Boxes Use Data Visualizations Automate Word  You are an expert in Excel, but the macro recorder doesn't work and you can't make heads or tails out of the recorded code. If this is you, buy this book. Macros that you record today might work today but not tomorrow. Recorded macros might handle a dataset with 14 re

Read more less

Book Synopsis

Read more less

Book SynopsisFinancial models in Excel allow investment analysts and other finance professionals to take the laborious number crunching out of financial analysis and forecasting. Models help them to gain meaningful insights into the way that a business is working and focus attention on areas to improve bottom-line results. They can also be used as powerful tools to test the potential impact of various risks on business performance.In this brand new guide, financial modelling expert Paul Lower presents step-by-step instructions for seven spreadsheet models that will help the user to gain a better understanding of the financial data coming out of a business.These seven models can be used to:1. Assess how a business is performing on key financial indicators.2. Produce sales and cost forecasts.3. Create a cash flow forecast.4. Understand the impact of product price changes on profitability.5. Assess potential investment decisions.6. Check the sensitivity of key financial measures to risk events.7. Produce a business valuation.The book also includes downloadable spreadsheets of the author''s original Excel models and introductory chapters about best practice when modelling in Excel.With this suite of seven tools, a financial analyst will be equipped to use Excel to achieve a deep understanding of a business and its financial data.

Read more less

Book SynopsisBusiness Finance Planning with Microsoft Excel shows how to visualize, plan, and put into motion an idea for creating a start-up company. Microsoft Excel is a tool that makes it easier to build a business financial planning process for a new business venture. With an easy-to follow structure, the book flows as a six-step process: Presenting a case study of a business start-up Creating goals and objectives Determining expenses from those goals and objectives, Estimating potential sales revenue based on what competitors charge their customers Predicting marketing costs Finalizing the financial analysis with a of financial statements. Written around an IT startup case study, the book presents a host of Excel worksheets describing the case study along with accompanying blank forms. Readers can use these forms in their own businesses, so they can build parts of their own busiTable of ContentsChapter 1. In the Beginning. Chapter 2. Your Business Goals. Chapter 3. Predicting Expenses with SMART Objectives. Chapter 4. Predicting Sales Using Your Competitors. Chapter 5. Predicting Marketing Costs. Chapter 6. Formal Financial Planning and Budgeting. Appendix A. Case Study. Appendix B. Your Business. Appendix C. Tools and Additions.

Read more less

Book SynopsisThis book gives readers a hands-on understanding of Excel-assisted statistical techniques to take effective business decisions. It showcases applications of the tools and techniques of statistics for analysing business data from the domain of business statistics.The volume provides an exhaustive introduction to the application of statistics in solving business problems and implementing data analytics for effective decision making in all kinds of business situations around the world. With an emphasis on simplicity in presentation of concepts of statistical methods and associated Excel functions, the volume explores the implementation of Excel functions through well-defined sequences of steps. It covers an array of key topics which include Discussions on real-world problems, decision support systems, scope of business statistics, types, and steps of research; Introduction to Excel and its mathematical and preliminary statistical functions; usage of different Table of Contents1. Introduction 2. Introduction to Excel 3. Count, Frequency and Histogram 4. Average Functions 5. Median and Mode 6. Measures of Variation 7. Measures Of Skewness 8. Probability Distributions 9. Sampling Distribution of Mean and Variance 10. Testing Of Hypothesis 11. Chi-Square Test 12. Nonparametric Test 13. Correlation and Covariance 14. Forecasting 15. Analysis of Variance (Anova) 16. Charts 17. Linear Programming

Read more less

Book Synopsis

Read more less

Book Synopsis

Read more less

Book SynopsisUpdated look at financial modeling and Monte Carlo simulation with software by Oracle Crystal Ball This revised and updated edition of the bestselling book on financial modeling provides the tools and techniques needed to perform spreadsheet simulation. It answers the essential question of why risk analysis is vital to the decision-making process, for any problem posed in finance and investment. This reliable resource reviews the basics and covers how to define and refine probability distributions in financial modeling, and explores the concepts driving the simulation modeling process. It also discusses simulation controls and analysis of simulation results. The second edition of Financial Modeling with Crystal Ball and Excel contains instructions, theory, and practical example models to help apply risk analysis to such areas as derivative pricing, cost estimation, portfolio allocation and optimization, credit risk, and cash flow analysis. It includes the resouTable of ContentsPreface xi Acknowledgments xvii About the Author xix Chapter 1 Introduction 1 1.1 Financial Modeling 2 1.2 Risk Analysis 2 1.3 Monte Carlo Simulation 4 1.4 Risk Management 8 1.5 Benefits and Limitations of Using Crystal Ball 9 Chapter 2 Analyzing Crystal Ball Forecasts 11 2.1 Simulating a 50–50 Portfolio 11 2.2 Varying the Allocations 22 2.3 Presenting the Results 27 Chapter 3 Building A Crystal Ball Model 29 3.1 Simulation Modeling Process 29 3.2 Defining Crystal Ball Assumptions and Forecasts 30 3.3 Running Crystal Ball 33 3.4 Sources of Error 34 3.5 Controlling Model Error 36 Chapter 4 Selecting Crystal Ball Assumptions 37 4.1 Crystal Ball’s Basic Distributions 37 4.2 Using Historical Data to Choose Distributions 55 4.3 Specifying Correlations 64 Chapter 5 Using Decision Variables 79 5.1 Defining Decision Variables 79 5.2 Decision Table with One Decision Variable 81 5.3 Decision Table with Two Decision Variables 87 5.4 Using OptQuest 98 Chapter 6 Selecting Run Preferences 105 6.1 Trials 105 6.2 Sampling 109 6.3 Speed 111 6.4 Options 113 6.5 Statistics 115 Chapter 7 Net Present Value and Internal Rate of Return 117 7.1 Deterministic NPV and IRR 117 7.2 Simulating NPV and IRR 119 7.3 Capital Budgeting 123 7.4 Customer Net Present Value 133 Chapter 8 Modeling Financial Statements 137 8.1 Deterministic Model 137 8.2 Tornado Chart and Sensitivity Analysis 138 8.3 Crystal Ball Sensitivity Chart 139 8.4 Conclusion 143 Chapter 9 Portfolio Models 145 9.1 Single-period Crystal Ball Model 145 9.2 Single-period Analytical Solution 148 9.3 Multi-period Crystal Ball Model 149 Chapter 10 Value at Risk 155 10.1 VaR 155 10.2 Shortcomings of VaR 157 10.3 Conditional Value at Risk 157 Chapter 11 Simulating Financial Time Series 163 11.1 White Noise 163 11.2 Random Walk 165 11.3 Autocorrelation 166 11.4 Additive Random Walk with Drift 170 11.5 Multiplicative Random Walk Model 173 11.6 Geometric Brownian Motion Model 176 11.7 Mean-reverting Model 180 Chapter 12 Financial Options 187 12.1 Types of Options 187 12.2 Risk-neutral Pricing and the Black-Scholes Model 188 12.3 Portfolio Insurance 192 12.4 American Option Pricing 194 12.5 Exotic Option Pricing 197 12.6 Bull Spread 201 12.7 Principal-protected Instrument 201 Chapter 13 Real Options 205 13.1 Financial Options and Real Options 205 13.2 Applications of Real Options Analysis 206 13.3 Black-Scholes Real Options Insights 209 13.4 Real Options Valuation Tool 211 Chapter 14 Credit Risk 221 14.1 Expected Loss 221 14.2 Credit Risk Simulation Model 223 14.3 Conditional Value at Risk 225 14.4 Using CVaR to Manage Credit Risk 227 Chapter 15 Construction Project Management 229 15.1 Project Description 229 15.2 Choosing Construction Methods 231 15.3 Risk Analysis 231 15.4 Stochastic Optimization 234 Chapter 16 Oil and GasExploration 235 16.1 Well Properties 235 16.2 Statistical Models 236 16.3 Conclusion 239 Appendix A Crystal Ball’s Probability Distributions 241 A.1 Bernoulli 241 A.2 Beta 243 A.3 Beta PERT 244 A.4 Binomial 246 A.5 Custom 247 A.6 Discrete Uniform 251 A.7 Exponential 252 A.8 Gamma 254 A.9 Geometric 255 A.10 Hypergeometric 257 A.11 Logistic 259 A.12 Lognormal 260 A.13 Maximum Extreme 262 A.14 Minimum Extreme 263 A.15 Negative Binomial 264 A.16 Normal 266 A.17 Pareto 267 A.18 Poisson 269 A.19 Student’s t 270 A.20 Triangular 272 A.21 Uniform 273 A.22 Weibull 275 A.23 Yes-No 276 Appendix B Generating Assumption Values 279 B.1 Generating Random Numbers 279 B.2 Generating Random Variates 282 B.3 Latin Hypercube Sampling 284 Appendix C Variance Reduction Techniques 287 C.1 Using Crystal Ball to Value an Asian Option 288 C.2 Antithetic Variates 289 C.3 Control Variates 289 C.4 Comparison 290 C.5 Conclusion 292 Appendix D About the Download 293 Glossary 297 References 301 Index 311

Read more less

Book SynopsisHelping tech-savvy marketers and data analysts solve real-world business problems with Excel Using data-driven business analytics to understand customers and improve results is a great idea in theory, but in today's busy offices, marketers and analysts need simple, low-cost ways to process and make the most of all that data.Table of ContentsIntroduction xxiii I Using Excel to Summarize Marketing Data 1 1 Slicing and Dicing Marketing Data with PivotTables 3 Analyzing Sales at True Colors Hardware 3 Analyzing Sales at La Petit Bakery 14 Analyzing How Demographics Affect Sales 21 Pulling Data from a PivotTable with the GETPIVOTDATA Function 25 Summary 27 Exercises 27 2 Using Excel Charts to Summarize Marketing Data 29 Combination Charts 29 Using a PivotChart to Summarize Market Research Surveys 36 Ensuring Charts Update Automatically When New Data is Added 39 Making Chart Labels Dynamic 40 Summarizing Monthly Sales-Force Rankings 43 Using Check Boxes to Control Data in a Chart 45 Using Sparklines to Summarize Multiple Data Series 48 Using GETPIVOTDATA to Create the End-of-Week Sales Report 52 Summary 55 Exercises 55 3 Using Excel Functions to Summarize Marketing Data 59 Summarizing Data with a Histogram 59 Using Statistical Functions to Summarize Marketing Data 64 Summary 79 Exercises 80 II Pricing 83 4 Estimating Demand Curves and Using Solver to Optimize Price 85 Estimating Linear and Power Demand Curves 85 Using the Excel Solver to Optimize Price 90 Pricing Using Subjectively Estimated Demand Curves 96 Using SolverTable to Price Multiple Products 99 Summary 103 Exercises 104 5 Price Bundling 107 Why Bundle? 107 Using Evolutionary Solver to Find Optimal Bundle Prices 111 Summary 119 Exercises 119 6 Nonlinear Pricing 123 Demand Curves and Willingness to Pay 124 Profit Maximizing with Nonlinear Pricing Strategies 125 Summary 131 Exercises 132 7 Price Skimming and Sales 135 Dropping Prices Over Time 135 Why Have Sales? 138 Summary 142 Exercises 142 8 Revenue Management 143 Estimating Demand for the Bates Motel and Segmenting Customers 144 Handling Uncertainty 150 Markdown Pricing 153 Summary 156 Exercises 156 III Forecasting .159 9 Simple Linear Regression and Correlation 161 Simple Linear Regression 161 Using Correlations to Summarize Linear Relationships 170 Summary 174 Exercises 175 10 Using Multiple Regression to Forecast Sales 177 Introducing Multiple Linear Regression 178 Running a Regression with the Data Analysis Add-In 179 Interpreting the Regression Output 182 Using Qualitative Independent Variables in Regression 186 Modeling Interactions and Nonlinearities 192 Testing Validity of Regression Assumptions 195 Multicollinearity 204 Validation of a Regression 207 Summary 209 Exercises 210 11 Forecasting in the Presence of Special Events 213 Building the Basic Model 213 Summary 222 Exercises 222 12 Modeling Trend and Seasonality 225 Using Moving Averages to Smooth Data and Eliminate Seasonality 225 An Additive Model with Trends and Seasonality 228 A Multiplicative Model with Trend and Seasonality 231 Summary 234 Exercises 234 13 Ratio to Moving Average Forecasting Method 235 Using the Ratio to Moving Average Method 235 Applying the Ratio to Moving Average Method to Monthly Data 238 Summary 238 Exercises 239 14 Winter’s Method 241 Parameter Definitions for Winter’s Method 241 Initializing Winter’s Method 243 Estimating the Smoothing Constants 244 Forecasting Future Months 246 Mean Absolute Percentage Error (MAPE) 247 Summary 248 Exercises 248 15 Using Neural Networks to Forecast Sales 249 Regression and Neural Nets 249 Using Neural Networks 250 Using NeuralTools to Predict Sales 253 Using NeuralTools to Forecast Airline Miles 258 Summary 259 Exercises 259 IV What do Customers Want? 261 16 Conjoint Analysis 263 Products, Attributes, and Levels 263 Full Profile Conjoint Analysis 265 Using Evolutionary Solver to Generate Product Profiles 272 Developing a Conjoint Simulator 277 Examining Other Forms of Conjoint Analysis 279 Summary 281 Exercises 281 17 Logistic Regression 285 Why Logistic Regression Is Necessary 286 Logistic Regression Model 289 Maximum Likelihood Estimate of Logistic Regression Model 290 Using StatTools to Estimate and Test Logistic Regression Hypotheses 293 Performing a Logistic Regression with Count Data 298 Summary 300 Exercises 300 18 Discrete Choice Analysis 303 Random Utility Theory 303 Discrete Choice Analysis of Chocolate Preferences 305 Incorporating Price and Brand Equity into Discrete Choice Analysis 309 Dynamic Discrete Choice 315 Independence of Irrelevant Alternatives (IIA) Assumption 316 Discrete Choice and Price Elasticity 317 Summary 318 Exercises 319 19 Calculating Lifetime Customer Value 327 Basic Customer Value Template 328 Measuring Sensitivity Analysis with Two-way Tables 330 An Explicit Formula for the Multiplier r 331 Varying Margins 331 DIRECTV, Customer Value, and Friday Night Lights (FNL) 333 Estimating the Chance a Customer Is Still Active 334 Going Beyond the Basic Customer Lifetime Value Model 335 Summary 336 Exercises 336 20 Using Customer Value to Value a Business 339 A Primer on Valuation 339 Using Customer Value to Value a Business 340 Measuring Sensitivity Analysis with a One-way Table 343 Using Customer Value to Estimate a Firm’s Market Value 344 Summary 344 Exercises 345 21 Customer Value, Monte Carlo Simulation, and Marketing Decision Making 347 A Markov Chain Model of Customer Value 347 Using Monte Carlo Simulation to Predict Success of a Marketing Initiative 353 Summary 359 Exercises 360 22 Allocating Marketing Resources between Customer Acquisition and Retention 347 Modeling the Relationship between Spending and Customer Acquisition and Retention 365 Basic Model for Optimizing Retention and Acquisition Spending 368 An Improvement in the Basic Model 371 Summary 373 Exercises 374 VI Market Segmentation 375 23 Cluster Analysis 377 Clustering U.S. Cities 378 Using Conjoint Analysis to Segment a Market 386 Summary 391 Exercises 391 24 Collaborative Filtering 393 User-Based Collaborative Filtering 393 Item-Based Filtering 398 Comparing Item- and User-Based Collaborative Filtering 400 The Netflix Competition 401 Summary 401 Exercises 402 25 Using Classification Trees for Segmentation 403 Introducing Decision Trees 403 Constructing a Decision Tree 404 Pruning Trees and CART 409 Summary 410 Exercises 410 26 Using S Curves to Forecast Sales of a New Product 415 Examining S Curves 415 Fitting the Pearl or Logistic Curve 418 Fitting an S Curve with Seasonality 420 Fitting the Gompertz Curve 422 Pearl Curve versus Gompertz Curve 425 Summary 425 Exercises 425 27 The Bass Diffusion Model 427 Introducing the Bass Model 427 Estimating the Bass Model 428 Using the Bass Model to Forecast New Product Sales 431 Deflating Intentions Data 434 Using the Bass Model to Simulate Sales of a New Product 435 Modifications of the Bass Model 437 Summary 438 Exercises 438 28 Using the Copernican Principle to Predict Duration of Future Sales 439 Using the Copernican Principle 439 Simulating Remaining Life of Product 440 Summary 441 Exercises 441 29 Market Basket Analysis and Lift 445 Computing Lift for Two Products 445 Computing Three-Way Lifts 449 A Data Mining Legend Debunked! 453 Using Lift to Optimize Store Layout 454 Summary 456 Exercises 456 30 RFM Analysis and Optimizing Direct Mail Campaigns 459 RFM Analysis 459 An RFM Success Story 465 Using the Evolutionary Solver to Optimize a Direct Mail Campaign 465 Summary 468 Exercises 468 31 Using the SCAN*PRO Model and Its Variants 471 Introducing the SCAN*PRO Model 471 Modeling Sales of Snickers Bars 472 Forecasting Software Sales 475 Summary 480 Exercises 480 32 Allocating Retail Space and Sales Resources 483 Identifying the Sales to Marketing Effort Relationship 483 Modeling the Marketing Response to Sales Force Effort 484 Optimizing Allocation of Sales Effort 489 Using the Gompertz Curve to Allocate Supermarket Shelf Space 492 Summary 492 Exercises 493 33 Forecasting Sales from Few Data Points 495 Predicting Movie Revenues 495 Modifying the Model to Improve Forecast Accuracy 498 Using 3 Weeks of Revenue to Forecast Movie Revenues 499 Summary 501 Exercises 501 34 Measuring the Effectiveness of Advertising 505 The Adstock Model 505 Another Model for Estimating Ad Effectiveness 509 Optimizing Advertising: Pulsing versus Continuous Spending 511 Summary 514 Exercises 515 35 Media Selection Models 517 A Linear Media Allocation Model 517 Quantity Discounts 520 A Monte Carlo Media Allocation Simulation 522 Summary 527 Exercises 527 36 Pay per Click (PPC) Online Advertising 529 Defining Pay per Click Advertising 529 Profitability Model for PPC Advertising 531 Google AdWords Auction 533 Using Bid Simulator to Optimize Your Bid 536 Summary 537 Exercises 537 X Marketing Research Tools 539 37 Principal Components Analysis (PCA) 541 Defining PCA 541 Linear Combinations, Variances, and Covariances 542 Diving into Principal Components Analysis 548 Other Applications of PCA 556 Summary 557 Exercises 558 38 Multidimensional Scaling (MDS) 559 Similarity Data 559 MDS Analysis of U.S. City Distances 560 MDS Analysis of Breakfast Foods 566 Finding a Consumer’s Ideal Point 570 Summary 574 Exercises 574 39 Classification Algorithms: Naive Bayes Classifier and Discriminant Analysis 577 Conditional Probability 578 Bayes’ Theorem 579 Naive Bayes Classifier 581 Linear Discriminant Analysis 586 Model Validation 591 The Surprising Virtues of Naive Bayes 592 Summary 592 Exercises 593 40 Analysis of Variance: One-way ANOVA 595 Testing Whether Group Means Are Different 595 Example of One-way ANOVA 596 The Role of Variance in ANOVA 598 Forecasting with One-way ANOVA 599 Contrasts 601 Summary 603 Exercises 604 41 Analysis of Variance: Two-way ANOVA 607 Introducing Two-way ANOVA 607 Two-way ANOVA without Replication 608 Two-way ANOVA with Replication 611 Summary 616 Exercises 617 XI Internet and Social Marketing 619 42 Networks 621 Measuring the Importance of a Node 621 Measuring the Importance of a Link 626 Summarizing Network Structure 628 Random and Regular Networks 631 The Rich Get Richer 634 Klout Score 636 Summary 637 Exercises 638 43 The Mathematics Behind The Tipping Point 641 Network Contagion 641 A Bass Version of the Tipping Point 646 Summary 650 Exercises 650 44 Viral Marketing 653 Watts’ Model 654 A More Complex Viral Marketing Model 655 Summary 660 Exercises 661 45 Text Mining 663 Text Mining Definitions 664 Giving Structure to Unstructured Text 664 Applying Text Mining in Real Life Scenarios 668 Summary 671 Exercises 671 Index 673

Read more less

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

Read more less

Book SynopsisMaximize your Excel 2013 experience using VBA application development The new Excel 2013 boasts updated features, enhanced power, and new capabilities.Table of ContentsIntroduction 1 Part I: Some Essential Background Chapter 1: Excel in a Nutshell 11 Chapter 2: Formula Tricks and Techniques 39 Chapter 3: Understanding Excel Files 75 Chapter 4: Essentials of Spreadsheet Application Development 97 Part II: Understanding Visual Basic for Applications Chapter 5: Introducing Visual Basic for Applications 119 Chapter 6: VBA Programming Fundamentals 177 Chapter 7: Working with VBA Sub Procedures 227 Chapter 8: Creating Function Procedures 269 Chapter 9: VBA Programming Examples and Techniques 317 Part III: Working with UserForms Chapter 10: Custom Dialog Box Alternatives 395 Chapter 11: Introducing UserForms 417 Chapter 12: UserForm Examples 453 Chapter 13: Advanced UserForm Techniques 491 Part IV: Advanced Programming Techniques Chapter 14: Developing Excel Utilities with VBA 543 Chapter 15: Working with Pivot Tables 565 Chapter 16: Working with Charts 583 Chapter 17: Understanding Excel’s Events 645 Chapter 18: Interacting with Other Applications 685 Chapter 19: Creating and Using Add-Ins 711 Part V: Developing Applications Chapter 20: Working with the Ribbon 743 Chapter 21: Working with Shortcut Menus 779 Chapter 22: Providing Help for Your Applications 801 Chapter 23: Developing User-Oriented Applications. 821 Part VI: Other Topics Chapter 24: Compatibility Issues 837 Chapter 25: Manipulating Files with VBA 851 Chapter 26: Manipulating Visual Basic Components 885 Chapter 27: Understanding Class Modules 911 Chapter 28: Working with Colors 927 Chapter 29: Frequently Asked Questions about Excel Programming 953 Part VII: Appendixes Appendix A: VBA Statements and Functions Reference 989 Appendix B: VBA Error Codes 999 Appendix C: This Book’s Website 1003 Index 1019

Read more less