Spreadsheet software Books
O'Reilly Media Learning Generative AI Tools for Excel
£35.99
Pearson Education (US) Microsoft Excel Data Analysis and Business
Book SynopsisWayne L. Winston is Professor Emeritus of Decision Sciences at the Indiana University School of Business. He has also taught at the University of Houston and Wake Forest. He has won more than 40 teaching awards and taught Excel modeling and analytics at many Fortune 500 companies, accounting firms, the U.S. Army, and the U.S. Navy. He is a two-time Jeopardy! champion, and also is a co-developer of a player tracking system utilized by Mark Cuban and the Dallas Mavericks. Table of ContentsCHAPTER 1 Basic worksheet modeling CHAPTER 2 Range names CHAPTER 3 Lookup functions CHAPTER 4 The INDEX function CHAPTER 5 The MATCH function CHAPTER 6 Text functions and Flash Fill CHAPTER 7 Dates and date functions CHAPTER 8 The net present value functions: NPV and XNPV CHAPTER 9 The internal rate of return: IRR, XIRR, and MIRR functions CHAPTER 10 More Excel financial functions CHAPTER 11 Circular references CHAPTER 12 IF, IFERROR, IFS, CHOOSE, SWITCH, and the IS functions CHAPTER 13 Time and time functions CHAPTER 14 The Paste Special command CHAPTER 15 Three-dimensional formulas and hyperlinks CHAPTER 16 The auditing tool and the Inquire add-in 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, AVERAGEIFS, MAXIFS, and MINIFS functions CHAPTER 22 The OFFSET function CHAPTER 23 The INDIRECT function CHAPTER 24 Conditional formatting CHAPTER 25 Excel tables and table slicers CHAPTER 26 Spin buttons, scrollbars, option buttons, check boxes, combo boxes, and group list boxes CHAPTER 27 Importing data from a text file or document CHAPTER 28 The Power Query Editor CHAPTER 29 Excel's new data types CHAPTER 30 Summarizing data with histograms and Pareto charts CHAPTER 31 Summarizing data with descriptive statistics CHAPTER 32 Sorting in Excel CHAPTER 33 Filtering data and removing duplicates CHAPTER 34 Summarizing data with database statistical functions CHAPTER 35 Array formulas and functions CHAPTER 36 Excel's new dynamic array functions CHAPTER 37 Validating data CHAPTER 38 Using PivotTables and slicers to describe data CHAPTER 39 The Data Model CHAPTER 40 Power Pivot CHAPTER 41 Consolidating data CHAPTER 42 Creating subtotals CHAPTER 43 Basic charting CHAPTER 44 Advanced charting CHAPTER 45 Filled and 3D Maps CHAPTER 46 Sparklines CHAPTER 47 Estimating straight-line relationships CHAPTER 48 Modeling exponential growth CHAPTER 49 The power curve CHAPTER 50 Using correlations to summarize relationships CHAPTER 51 Introduction to multiple regression CHAPTER 52 Incorporating qualitative factors into multiple regression CHAPTER 53 Modeling nonlinearities and interactions CHAPTER 54 Analysis of variance: One-way ANOVA CHAPTER 55 Randomized blocks and two-way ANOVA CHAPTER 56 Using moving averages to understand time series CHAPTER 57 Ratio-to-moving-average forecast method CHAPTER 58 An introduction to probability CHAPTER 59 An introduction to random variables CHAPTER 60 The binomial, hypergeometric, and negative binomial random variables CHAPTER 61 The Poisson and exponential random variable CHAPTER 62 The normal random variable and Z-scores CHAPTER 63 Making probability statements from forecasts CHAPTER 64 Using the lognormal random variable to model stock prices CHAPTER 65 Importing past stock prices, exchange rates, and cryptocurrency prices with the STOCKHISTORY function CHAPTER 66 An introduction to optimization with Excel Solver CHAPTER 67 Using Solver to determine the optimal product mix CHAPTER 68 Using Solver to schedule your workforce CHAPTER 69 Using Solver to solve transportation or distribution problems CHAPTER 70 Using Solver for capital budgeting CHAPTER 71 Using Solver for financial planning CHAPTER 72 Using Solver to rate sports teams CHAPTER 73 The Winters method and the Forecast Sheet tool CHAPTER 74 Forecasting in the presence of special events CHAPTER 75 Warehouse location and the GRG Multistart and Evolutionary Solver engines CHAPTER 76 Penalties and the Evolutionary Solver CHAPTER 77 The traveling salesperson problem CHAPTER 78 Weibull and beta distributions: Modeling machine life and duration of a project CHAPTER 79 Introduction to Monte Carlo simulation CHAPTER 80 Calculating an optimal bid CHAPTER 81 Simulating stock prices and asset-allocation modeling CHAPTER 82 Fun and games: Simulating gambling and sporting-event probabilities CHAPTER 83 Using resampling to analyze data CHAPTER 84 Pricing stock options CHAPTER 85 Determining customer value CHAPTER 86 The economic order quantity inventory model CHAPTER 87 Inventory modeling with uncertain demand CHAPTER 88 Queuing theory: The mathematics of waiting in line CHAPTER 89 Estimating a demand curve CHAPTER 90 Pricing products by using tie-ins CHAPTER 91 Pricing products by using subjectively determined demand CHAPTER 92 Nonlinear pricing CHAPTER 93 Use Analyze Data to find patterns in your data CHAPTER 94 Recording macros CHAPTER 95 The LET and LAMBDA functions and the LAMBDA helper functions CHAPTER 96 Advanced sensitivity analysis
£37.79
John Wiley & Sons Inc Marketing Analytics
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
£33.59
Pearson Education Collect Combine and Transform Data Using Power
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
£26.99
No Starch Press,US Python for Excel Power Users
£33.74
Pearson Education Microsoft Excel Pivot Table Data Crunching
Book Synopsis
£29.69
In Easy Steps Limited Microsoft Excel in easy steps: Illustrated using
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!
£11.39
In Easy Steps Limited 100 Top Tips - Microsoft Excel
Book Synopsis100 Top Tips Microsoft Excel will help you boost your productivity and get more value from your spreadsheets with this powerful book of tips. It showcases: Time-saving features (including data entry shortcuts)Powerful functions for processing text and numbersInsightful data analysis features, including pivot tables and what-if analysis. How to use graphsDebugging formulasPrinting clearlyAnd much more!You'll also discover how to use graphs, debug formulas, and print everything out cleanly. Whether you've been using Excel for years and never gone beyond the basics, or are just entering the world of spreadsheets, this book will quickly supercharge your skills.As you would expect from the In Easy Steps brand the book is written in an approachable style with step by step instructions provided for many of the tips, and full colour screenshots guiding you through the processes.
£7.49
Pearson Education (US) MOS Study Guide for Microsoft Excel Exam MO200
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
£17.09
Pearson Education Microsoft Copilot Studio Step by Step
a huge range and FREE tracked UK delivery on ALL orders.
£44.64
Taylor & Francis Ltd Business Statistics Using Excel
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
£35.99
No Starch Press Automate the Excel Stuff with Python
£37.49
Pearson Education (US) Microsoft Excel Formulas and Functions Office
Book SynopsisPaul McFedries is an Excel expert and full-time technical writer. Paul has been authoring computer books since 1991 and has more than 100 books to his credit, which combined have sold more than 4 million copies worldwide. His titles include the Que Publishing books My Office 2016, Windows 10 In Depth (with coauthor Brian Knittel), and PCs for Grownups, as well as the Sams Publishing book Windows 7 Unleashed. Please drop by Paul's personal websiTable of ContentsPart 1: Mastering Excel formulas Chapter 1: Building basic formulas Chapter 2: Creating advanced formulas Chapter 3: Troubleshooting formulas Part 2: Harnessing the power of functions Chapter 4: Understanding functions Chapter 5: Working with text functions Chapter 6: Working with logical and information functions Chapter 7: Working with lookup functions Chapter 8: Working with date and time functions Chapter 9: Working with math functions Part 3: Building business formulas Chapter 10: Implementing basic business formulas Chapter 11: Building descriptive statistical formulas Chapter 12: Building inferential statistical formulas Chapter 13: Applying regression to track trends and make forecasts Chapter 14: Building loan formulas Chapter 15: Working with investment formulas Chapter 16: Building discount formulas Part IV: Building business models Chapter 17: Analyzing data with tables Chapter 18: Analyzing data with PivotTables Chapter 19: Using Excel's business modeling tools Chapter 20: Solving complex problems with Solver
£27.19
O'Reilly Modern Data Analytics in Excel
Book Synopsis
£38.39
John Wiley & Sons Inc Teach Yourself VISUALLY Excel 365
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
£19.54
Microsoft Press,U.S. Microsoft Excel 2019 Formulas and Functions
Book SynopsisMaster core Excel tools for building powerful, reliable spreadsheets with Excel 2019 Formulas and Functions. Excel expert Paul McFedries shows how to use Excel core features to solve problems and get the answers you need. Using real-world examples, McFedries helps you get the absolute most out of features and improvements ranging from AutoFill to Excel’s newest functions. Along the way, you discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation. Becoming an Excel expert has never been easier! You’ll find crystal-clear instructions; insider insights; even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more. • Quickly create powerful spreadsheets with range names and array formulas • Use conditional formatting to instantly reveal anomalies, problems, or opportunities • Analyze your data with standard tables and PivotTables • Use complex criteria to filter data in lists • Understand correlations between data • Perform sophisticated what-if analyses • Use regression to track trends and make forecasts • Build loan, investment, and discount formulas • Validate data, troubleshoot problems, and build more accurate, trustworthy spreadsheets Table of ContentsPart I—Mastering Excel formulas Chapter 1—Building basic formulas Chapter 2—Creating advanced formulas Chapter 3—Troubleshooting formulas Part II—Harnessing the power of functions Chapter 4—Understanding functions Chapter 5—Working with text functions Chapter 6—Working with logical and information functions Chapter 7—Working with lookup functions Chapter 8—Working with date and time functions Chapter 9—Working with math functions Part III—Building business formulas Chapter 10—Implementing basic business formulas Chapter 11—Building descriptive statistical formulas Chapter 12—Building inferential statistical formulas Chapter 13—Applying regression to track trends and make forecasts Chapter 14—Building loan formulas Chapter 15—Working with investment formulas Chapter 16—Building discount formulas Part IV—Building business models Chapter 17—Analyzing data with tables Chapter 18—Analyzing data with PivotTables Chapter 19—Using Excel's business modeling tools Chapter 20—Solving complex problems with Solver
£26.99
Microsoft Press,U.S. Collect, Combine, and Transform Data Using Power
Book SynopsisDid you know that there is a technology inside Excel, and Power BI, that allows you to create magic in your data, avoid repetitive manual work, and save you time and money? Using Excel and Power BI, you can: Save time by eliminating the pain of copying and pasting data into workbooks and then manually cleaning that data. Gain productivity by properly preparing data yourself, rather than relying on others to do it. Gain effiiciency by reducing the time it takes to prepare data for analysis, and make informed decisions more quickly. With the data connectivity and transformative technology found in Excel and Power BI, users with basic Excel skills import data and then easily reshape and cleanse that data, using simple intuitive user interfaces. Known as “Get & Transform” in Excel 2016, as the “Power Query” separate add-in in Excel 2013 and 2010, and included in Power BI, you'll use this technology to tackle common data challenges, resolving them with simple mouse clicks and lightweight formula editing. With your new data transformation skills acquired through this book, you will be able to create an automated transformation of virtually any type of data set to mine its hidden insights.Table of Contents Section 1: Transforming Data Chapter 1: Introduction to Power Query Chapter 2: Basic Data Challenges Chapter 3: Combining Data from Multiple Sources Chapter 4: Unpivoting and Transforming Data Chapter 5: Pivoting & Handling Multiline Records Section 2: Exploring Data Chapter 6: Ad-Hoc Analysis Chapter 7: Using Query Editor to Further Explore Data Section 3: Scaling Up Queries for Production or Larger Data Sets Chapter 8: Introduction to the M Query Language Chapter 9: Lightweight modification of M formulas to improve query robustness Section 4: Real Life Challenges Chapter 10: Solving Real-Life Data Challenges Chapter 11: Social Listening Chapter 12: Text Analytics Chapter 13: Concluding Exercise – Hawaii Tourism Data
£26.99
Pearson Education The Definitive Guide to DAX
a huge range and FREE tracked UK delivery on ALL orders.
£38.39
Pearson Education (US) Even You Can Learn Statistics and Analytics
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
£23.39
Rheinwerk Verlag GmbH Excel
£48.02
Microsoft Press,U.S. Microsoft Excel 2016 Step by Step
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
£22.09
Pearson Education (US) Microsoft Excel Step by Step Office 2021 and
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
£29.69
John Wiley & Sons Inc Using Excel for Business and Financial Modelling
Book SynopsisA hands-on guide to using Excel in the business context First published in 2012, Using Excel for Business and Financial Modelling contains step-by-step instructions of how to solve common business problems using financial models, including downloadable Excel templates, a list of shortcuts and tons of practical tips and techniques you can apply straight away. Whilst there are many hundreds of tools, features and functions in Excel, this book focuses on the topics most relevant to finance professionals. It covers these features in detail from a practical perspective, but also puts them in context by applying them to practical examples in the real world. Learn to create financial models to help make business decisions whilst applying modelling best practice methodology, tools and techniques. Provides the perfect mix of practice and theory Helps you become a DIY Excel modelling specialist Includes updates for Excel 2019/365 and Excel for Table of ContentsPreface xi Chapter 1 What is Financial Modelling? 1 What’s the Difference Between a Spreadsheet and a Financial Model? 3 Types and Purposes of Financial Models 5 Tool Selection 6 What Skills Do You Need to Be a Good Financial Modeller? 17 The “Ideal” Financial Modeller 23 Summary 27 Chapter 2 Building a Model 29 Model Design 29 The Golden Rules for Model Design 31 Design Issues 32 The Workbook Anatomy of a Model 33 Project Planning Your Model 36 Model Layout Flowcharting 37 Steps to Building a Model 39 Information Requests 47 Version-Control Documentation 49 Summary 50 Chapter 3 Best-Practice Principles of Modelling 51 Document Your Assumptions 51 Linking, Not Hardcoding 52 Enter Data Only Once 53 Avoid Bad Habits 53 Use Consistent Formulas 53 Format and Label Clearly 54 Methods and Tools of Assumptions Documentation 55 Linked Dynamic Text Assumptions Documentation 62 What Makes a Good Model? 65 Summary 67 Chapter 4 Financial Modelling Techniques 69 The Problem with Excel 69 Error Avoidance Strategies 71 How Long Should a Formula Be? 76 Linking to External Files 78 Building Error Checks 81 Circular References 85 Summary 90 Chapter 5 Using Excel in Financial Modelling 91 Formulas and Functions in Excel 91 Excel Versions 94 Handy Excel Shortcuts 100 Cell Referencing Best Practices 104 Named Ranges 107 Basic Excel Functions 110 Logical Functions 114 Nesting Logical Functions 117 Summary 125 Chapter 6 Functions for Financial Modelling 127 Aggregation Functions 127 LOOKUP Functions 139 Nesting Index and Match 150 OFFSET Function 153 Regression Analysis 158 Choose Function 164 Working with Dates 165 Financial Project Evaluation Functions 171 Loan Calculations 177 Summary 183 Chapter 7 Tools for Model Display 185 Basic Formatting 185 Custom Formatting 186 Conditional Formatting 191 Sparklines 195 Bulletproofing Your Model 199 Customising the Display Settings 203 Form Controls 210 Summary 226 Chapter 8 Tools for Financial Modelling 227 Hiding Sections of a Model 227 Grouping 233 Array Formulas 234 Goal Seeking 240 Structured Reference Tables 242 PivotTables 245 Macros 254 Summary 263 Chapter 9 Common Uses of Tools in Financial Modelling 265 Escalation Methods for Modelling 265 Understanding Nominal and Effective (Real) Rates 270 Calculating a Cumulative Sum (Running Totals) 274 How to Calculate a Payback Period 275 Weighted Average Cost of Capital (WACC) 278 Building a Tiering Table 282 Modelling Depreciation Methods 286 Break-Even Analysis 295 Summary 300 Chapter 10 Model Review 301 Rebuilding an Inherited Model 301 Improving Model Performance 312 Auditing a Financial Model 317 Summary 323 Appendix: QA Log 323 Chapter 11 Stress Testing, Scenarios, and Sensitivity Analysis in Financial Modelling 325 What are the Differences Between Scenario, Sensitivity, and What-If Analyses? 326 Overview of Scenario Analysis Tools and Methods 328 Advanced Conditional Formatting 337 Comparing Scenario Methods 340 Adding Probability to a Data Table 350 Summary 351 Chapter 12 Presenting Model Output 353 Preparing an Oral Presentation for Model Results 353 Preparing a Graphic or Written Presentation for Model Results 355 Chart Types 358 Working with Charts 367 Handy Charting Hints 374 Dynamic Named Ranges 376 Charting with Two Different Axes and Chart Types 382 Bubble Charts 384 Creating a Dynamic Chart 387 Waterfall Charts 391 Summary 395 About the Author 397 About the Website 399 Index 403
£59.85
John Wiley & Sons Inc Excel Workbook For Dummies
Book SynopsisGet practical walkthroughs for the most useful Excel features Looking for easy-to-understand, practical guidance on how to go from Excel newbie to number crunching pro? Excel Workbook For Dummies is the hands-on tutorial you've been waiting for. This step-by-step guide is packed with exercises that walk you through the basic and advanced functions and formulas included in Excel. At your own speed, you'll learn how to enter data, format your spreadsheet, and apply the mathematical and statistical capabilities of the program. Work through the book from start to finish or, if you'd prefer, jump right to the section that's giving you trouble, whether that's data visualization, macros, analysis, or anything else. You can also: Brush up on and practice time-saving keyboard shortcuts for popular commands and actionsGet a handle on multi-functional and practical pivot tables with intuitive practice exercisesLearn to secure your data with spreadsheet password protections Perfect for new users of Excel, Excel Workbook For Dummies is also the ideal resource for those who know their way around a spreadsheet but need a refresher on some of the more advanced features of this powerful program.Table of ContentsIntroduction 1 Part 1: Building Worksheets 5 Chapter 1: Getting Familiar with the Excel Interface 7 Chapter 2: Entering the Worksheet Data 21 Chapter 3: Formatting the Worksheet 37 Chapter 4: Printing Worksheet Reports 59 Chapter 5: Modifying the Worksheet 79 Part 2: Using Formulas and Functions 101 Chapter 6: Building Formulas 103 Chapter 7: Copying and Correcting Formulas 123 Chapter 8: Creating Date and Time Formulas 145 Chapter 9: Financial Formulas and Functions 155 Chapter 10: Using Math Functions 167 Chapter 11: Using Common Statistical Functions 179 Chapter 12: Using Lookup Functions 187 Chapter 13: Using Logical Functions 197 Chapter 14: Text Formulas and Functions 209 Part 3: Working with Graphics 217 Chapter 15: Charting Worksheet Data 219 Chapter 16: Adding Graphics to Worksheets 231 Part 4: Managing and Securing Data 249 Chapter 17: Building and Maintaining Tables 251 Chapter 18: Protecting Workbooks and Worksheet Data 271 Part 5: Doing Data Analysis 283 Chapter 19: Performing What-If Analysis 285 Chapter 20: Generating PivotTables 299 Part 6: Macros and Visual Basic for Applications 315 Chapter 21: Using Macros 317 Chapter 22: Using the Visual Basic Editor 327 Part 7: The Part of Tens 341 Chapter 23: Top Ten Features in Excel 343 Chapter 24: Top Ten Tips for Using Excel Like a Pro 347 Index 355
£20.39
Microsoft Press,U.S. Analyzing Data with Power BI and Power Pivot for
Book SynopsisRenowned DAX experts Alberto Ferrari and Marco Russo teach you how to design data models for maximum efficiency and effectiveness. How can you use Excel and Power BI to gain real insights into your information? As you examine your data, how do you write a formula that provides the numbers you need? The answers to both of these questions lie with the data model. This book introduces the basic techniques for shaping data models in Excel and Power BI. It’s meant for readers who are new to data modeling as well as for experienced data modelers looking for tips from the experts. If you want to use Power BI or Excel to analyze data, the many real-world examples in this book will help you look at your reports in a different way—like experienced data modelers do. As you’ll soon see, with the right data model, the correct answer is always a simple one! By reading this book, you will: • Gain an understanding of the basics of data modeling, including tables, relationships, and keys • Familiarize yourself with star schemas, snowflakes, and common modeling techniques • Learn the importance of granularity • Discover how to use multiple fact tables, like sales and purchases, in a complex data model • Manage calendar-related calculations by using date tables • Track historical attributes, like previous addresses of customers or manager assignments • Use snapshots to compute quantity on hand • Work with multiple currencies in the most efficient way • Analyze events that have durations, including overlapping durations • Learn what data model you need to answer your specific business questions About This Book • For Excel and Power BI users who want to exploit the full power of their favorite tools • For BI professionals seeking new ideas for modeling dataTable of ContentsCHAPTER 1 Introduction to data modeling CHAPTER 2 Using header/detail tables CHAPTER 3 Using multiple fact tables CHAPTER 4 Working with date and time CHAPTER 5 Tracking historical attributes CHAPTER 6 Using snapshots CHAPTER 7 Analyzing date and time intervals CHAPTER 8 Many-to-many relationships CHAPTER 9 Working with different granularity CHAPTER 10 Segmentation data models CHAPTER 11 Working with multiple currencies APPENDIX A Data modeling 101
£26.54
Microsoft Press,U.S. Microsoft Power BI Dashboards Step by Step
Book Synopsis Your hands-on guide to building effective Power BI dashboards Expand your expertise–and teach yourself how to create world-class Power BI business analysis dashboards that bring data to life for better decision-making. If you’re an experienced business intelligence professional or manager, you’ll get all the guidance, examples, and code you need to succeed–even if you’ve never used Power BI before. Successfully design, architect, and implement Power BI in your organization Take full advantage of any Microsoft Power BI platform, including Power BI Premium Make upfront decisions that position your Power BI project for success Build rich, live dashboards to monitor crucial data from across your organization Aggregate data and data elements from numerous internal and external data sources Develop dynamic visualizations, including charts, maps, and graphs Bring data to life with stunning interactive reports Ensure dashboard security and compliance Drive user adoption through effective training Table of Contents 1. Introduction of Power BI and Its Capabilities 2. Understanding Power BIs Architecture and Dynamic Dashboard Development Requirements 3. Developing Initial Power BI Dashboards 4. Developing Power BI Dashboards with Microsoft Excel 5. Developing Power BI Dashboards with SharePoint Online Lists and Libraries | Office 365 6. Developing Power BI Dashboards with SQL Server Databases and Related Data 7. Developing Power BI Dashboards with Azure SQL 8. Developing Power BI Dashboards with Oracle Databases 9. Developing Power BI Dashboards with Dynamics 365 10. Developing Power BI Dashboards with Salesforce.com 11. Developing Power BI Dashboards with XML 12. Developing Advanced Dashboards 13. Working with Advanced Power BI Dashboards 14. Understanding DAX and Power BI Dashboard Development
£28.49
Holy Macro! Books Power BI for the Excel Analyst: The essential
Book SynopsisThe essential guide for getting started with Power BI. Power BI is an amazing cloud-based business analytics service provided by Microsoft that allows people to transform data into rich, interactive visual reports that presents a 360-degree business view and improves business decision making. This book is designed to get the Excel analyst started with Power BI, touching on Power Query and Power Pivot.Table of ContentsIntroduction to the Power BI environmentBuild your first report in just a few minutesPreparing Dashboard DataDiving in to Power Query and understanding M codeCreating Interactive VisualizationsUsing custom visualizationsUsing PowerBI.com to add value to your reports
£29.71
In Easy Steps Limited Excel VBA in easy steps
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!
£12.34
Pearson Education (US) MOS Study Guide for Microsoft Excel Expert Exam
Book SynopsisPaul McFedries, a Microsoft Office expert and owner of the technical writing company Logophilia Limited, has authored nearly 100 books, including Microsoft Excel 2019 Formulas and Functions. Combined, his books have sold more than 4 million copies worldwide.Table of Contents Chapter 1 – Manage workbook options and settings Objective 1.1: Manage workbooks Objective 1.2: Prepare workbooks for collaboration Objective 1.3: Use and configure language options Chapter 2 – Manage and format data Objective 2.1: Fill cells based on existing data Objective 2.2: Format and validate data Objective 2.3: Apply advanced conditional formatting and filtering Chapter 3 – Create advanced formulas and macros Objective 3.1: Perform logical operations in formulas Objective 3.2: Look up data by using functions Objective 3.3: Apply advanced date and time functions Objective 3.4: Perform data analysis Objective 3.5: Troubleshoot formulas Objective 3.6: Create and modify simple macros Chapter 4 – Manage advanced charts and tables Objective 4.1: Create and modify advanced charts Objective 4.2: Create and modify PivotTables Objective 4.3: Create and modify PivotCharts
£18.04
Pearson Education (US) Microsoft Excel Inside Out Office 2021 and
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
£34.84
Microsoft Press,U.S. Microsoft Excel 2019 Pivot Table Data Crunching
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
£25.49
Pearson Education Limited Brilliant Excel VBA Programming
Book SynopsisTable of Contents Introduction 1 Recording and running macros 2 Starting with the Visual Basic Editor 3 Working with data and variables 4 Managing workbooks and files 5 Managing worksheets 6 Managing ranges 7 Managing cells 8 Formatting worksheets and worksheet elements 9 Sorting and filtering data 10 Managing charts 11 Using built-in functions and statements 12 Managing code ssing logical constructs 13 Debugging your VBA code 14 Using Excel events in your VBA code 15 Gathering data with UserForms Jargonbuster Troubleshooting guide
£17.99
Cengage Learning, Inc Illustrated Series Collection Microsoft Office
Book SynopsisTable of Contents1. Getting Started with Excel. 2. Formatting a Worksheet. 3. Analyzing Data Using Formulas. 4. Working with Charts. 5. Working with Tables. 6. Managing Workbook Data. 7. Working with Images and Integrating with Other Programs. 8. Analyzing Data with PivotTables. 9. Automating Worksheet Tasks. 10. Advanced Worksheet Management. 11. Advanced Formulas and Functions. 12. Performing What-if Analysis. Index.
£69.99
Pearson Education (US) Business Analysis with Microsoft Excel
Book SynopsisConrad G. Carlberg is president of Network Control Systems, Inc., a software- development and consulting firm that specializes in statistical and database applications. He holds a PhD in statistics and is a many-time recipient of Microsoft's Most Valuable Professional (MVP) award. He lives near San Diego, California.Table of ContentsChapter 1 Working with Income Statements Keeping Score Choosing the Right Perspective Defining Two Purposes for Accounting Using the Income Statement Choosing a Reporting Method Cells in Excel Measuring the Operating and Nonoperating Segments Moving from the General Journal to the Income Statement Getting the General Journal into Excel Understanding Absolute, Relative, and Mixed References Getting the Journal Data to the Ledger Getting the Ledger Data to the Income Statement Managing the Financial Analyses with Accrual Accounting Using Straight-Line Depreciation Preparing the Trial Balance Moving Information into an Income Statement Organizing with Traditional Versus Contribution Approaches About Power BI Power BI Desktop Power BI Service and Mobile Apps Summary Chapter 2 Balance Sheet: Current Assets Designing the Balance Sheet Understanding Balance Sheet Accounts Understanding Debit and Credit Entries Getting a Current Asset Cash Balance Using Sheet-Level Names Getting a Cash Balance for Multiple Cash Accounts Handling Restricted Cash Accounts Getting a Current Asset Accounts Receivable Balance Allowing for Doubtful Accounts Using the Aging Approach to Estimating Uncollectibles Using the Percentage of Sales Approach to Estimating Uncollectibles Displaying Doubtful Account Balances with Power BI Managing the Sort Order Via the Axis Values Managing the Sort Order with Another Field Getting a Prepaid Expenses Balance Dealing with Insurance as a Prepaid Expense Getting a Current Asset Balance Understanding the Inventory Flow Closing the Inventory Account Closing the Revenue and Expense Accounts Summary Chapter 3 Valuing Inventories for the Balance Sheet Understanding Perpetual and Periodic Inventory Systems Perpetual Inventory Systems Periodic Inventory Systems Valuing Inventories Valuation Methods Summarized Using Specific Identification Using Average Cost Using the Moving Average Method Using FIFO Using LIFO Comparing the Four Valuation Methods Specification Identification Average Cost FIFO LIFO Handling Purchase Discounts Calculating Turns Ratios Summary Chapter 4 Summarizing Transactions: From the Journals to the Balance Sheet Understanding Journals Understanding Special Journals Structuring the Special Sales Journal Structuring the Special Purchases Journal Structuring the Cash Receipts Journal Structuring the Cash Payments Journal Excel Tables and Dynamic Range Names Building Dynamic Range Names Using Dynamic Range Names in the Journals Choosing Between Tables and Dynamic Range Names Understanding Ledgers Creating the General Ledger Using Subsidiary Ledgers Automating the Posting Process Getting a Current Liabilities Balance Summary Chapter 5 Working Capital and Cash Flow Analysis Matching Costs and Revenues Broadening the Definition: Cash Versus Working Capital Determining the Amount of Working Capital Determining Changes in Working Capital Analyzing Cash Flow Developing the Basic Information Summarizing the Sources and Uses of Working Capital Identifying Cash Flows Due to Operating Activities Combining Cash from Operations with Cash from Nonoperating Transactions Summary Chapter 6 Statement Analysis Understanding a Report by Means of Common-Sizing Using Common-Sized Income Statements Using Common-Sized Balance Sheets Using Comparative Financial Statements Using Dollar and Percent Changes in Statement Analysis Assessing the Financial Statements Handling Error Values Evaluating Percentage Changes Common-Sizing for Variance Analysis Common-Sizing by Headcount Showing Common-Sized Statements with Power BI Summary Chapter 7 Ratio Analysis Interpreting Industry Averages and Trends Comparing Ratios Within Industries Analyzing Ratios Vertically and Horizontally Getting a Basis for Ratios Analyzing Profitability Ratios Finding and Evaluating Earnings Per Share Determining Gross Profit Margin Determining Net Profit Margin Determining the Return on Assets Determining the Return on Equity Analyzing Leverage Ratios Determining the Debt Ratio Determining the Equity Ratio Determining the Times Interest Earned Ratio Analyzing Liquidity Ratios Determining the Current Ratio Determining the Quick Ratio Analyzing Activity Ratios Determining the Average Collection Period Determining Inventory Turnover Displaying Financial Ratios in Power BI Reports Summary Chapter 8 Budgeting and Planning Cycle Creating Pro Forma Financial Statements Forecasting by Percentage of Sales Using Excel to Manage the Analysis Performing Sensitivity Analysis Moving from the Pro Forma to the Budget Projecting Quarterly Sales Estimating Inventory Levels Fitting the Budget to the Business Plan Summary Chapter 9 Forecasting and Projections Making Sure You Have a Useful Baseline Moving Average Forecasts Creating Forecasts with the Moving Average Add-In Dealing with the Layout of Excel’s Moving Averages Creating Moving Average Forecasts with Excel’s Charts Forecasting with Excel’s Regression Functions Making Linear Forecasts: The TREND Function Making Nonlinear Forecasts: The GROWTH Function Creating Regression Forecasts with Excel’s Charts Forecasting with Excel’s Smoothing Functions Projecting with Smoothing Using the Exponential Smoothing Tool Choosing a Smoothing Constant Making Smoothed Forecasts Handle Seasonal Data Using the Box-Jenkins ARIMA Approach: When Excel’s Built-In Functions Won’t Do Understanding ARIMA Basics Charting the Correlograms Starting with Correlograms to Identify a Model Identifying Other Box-Jenkins Models Displaying Forecast Data with Power BI Displaying Forecasts with Power BI Using Power BI to Display Correlograms Summary Chapter 10 Measuring Quality Monitoring Quality Through Statistical Process Control Using Averages from Samples Using X-and-S Charts for Variables Interpreting the Control Limits Manufacturing Publishing Control Charts with Power BI Using P-Charts for Dichotomies Choosing the Sample Size Determining That a Process Is Out of Control Using X-and-MR Charts for Individual Observations Creating SPC Charts Using Excel Performing Acceptance Sampling Charting the Operating Characteristic Curve Using Worksheet Functions for Quality Control Sampling Units from a Finite Population Sampling Units from a Nonfinite Population Using NORM.S.DIST to Approximate BINOM.DIST Sampling Defects in Units Using the BINOM.INV Function Summary Chapter 11 Examining a Business Case: Investment Developing a Business Case Getting Consensus for the Plan Showing Your Work Developing the Excel Model Developing the Inputs Identifying the Costs Moving to the Pro Forma Preparing the Cash Flow Analysis Summary Chapter 12 Examining Decision Criteria for a Business Case Understanding Payback Periods Understanding Future Value, Present Value, and Net Present Value Calculating Future Value Calculating Present Value Calculating Net Present Value Optimizing Costs Summary Chapter 13 Creating a Sensitivity Analysis for a Business Case Reviewing the Business Case Managing Scenarios Saving a Scenario for the Base Case Developing Alternative Scenarios Developing Scenarios That Vary Expenses Summarizing the Scenarios Measuring Profit Calculating Internal Rate of Return Calculating Profitability Indexes Estimating the Continuing Value Varying the Discount Rate Input Using the Goal Seek Tool Summary Chapter 14 Planning Profits Understanding the Effects of Leverage The Effect of Business Risk Analyzing Operating Leverage Evaluating the Financial Implications of an Operational Change Evaluating Fixed Expenses Evaluating Effect of Increasing Fixed Costs Planning by Using the DOL Analyzing Financial Leverage Distinguishing Business from Financial Risk Determining the Debt Ratio Determining the Times Interest Earned Ratio Summary Chapter 15 Making Investment Decisions Under Uncertain Conditions Using Standard Deviations Using Excel’s Standard Deviation Functions Understanding Confidence Intervals Using Confidence Intervals in a Market Research Situation Calculating a Confidence Interval Interpreting the Interval Refining Confidence Intervals Using Regression Analysis in Decision Making Regressing One Variable onto Another Interpreting the Trendline Avoiding Traps in Interpretation: Association Versus Causation Regressing One Variable onto Several Other Variables: Multiple Regression Using Excel’s Regression Add-In Interpreting Regression Output Estimating with Multiple Regression Using Excel’s TREND Function Creating Charts in Power BI Creating a Scatter Chart Creating a Clustered Column Chart in Power BI Summary Chapter 16 Fixed Assets Determining Original Cost Determining Costs Choosing Between Actual Cost and Replacement Cost Depreciating Assets Understanding the Concept of Depreciation Matching Revenues to Costs Using Straight-Line Depreciation Using the Declining Balance Method Using the Double Declining Balance Function to Calculate Depreciation Using Variable Declining Balance Depreciation Using Sum-of-Years’-Digits Depreciation Summary Chapter 17 Importing Business Data into Excel Creating and Using ODBC Queries Preparing to Import Data Specifying Data Sources Creating Queries with the Query Wizard Creating Queries with Microsoft Query Creating Parameterized Queries in Microsoft Query Using Joins in Microsoft Query Working with External Data Ranges Include Row Numbers Adjust Column Width Preserve Column Sort/Filter/Layout Preserve Cell Formatting Insert Cells for New Data, Delete Unused Cells Insert Entire Rows for New Data, Clear Unused Cells Overwrite Existing Cells with New Data, Clear Unused Cells Managing Security Information Arranging Automatic Refreshes Setting Other Data Range Options Importing Data to Pivot Tables and Charts Creating and Using Web Queries Get External Data and Get Data From Web Using Get Data with a Website Using Get External Data and VBA Summary Chapter 18 Exporting Business Data from Excel Using VBA to Update an External Database Getting at VBA Structuring the Worksheet Establishing Command Buttons Editing the Record’s Values Using Database Objects Using With Blocks Finding the Right Record Editing the Record Adding New Records to the Recordset Choosing to Use ADO Summary Chapter 19 Using Excel 2016 and Power BI to Analyze QuickBooks Data Exporting an Income Statement to Excel Publishing a QuickBooks Report in Power BI Preparing the Data in Excel Moving the Report to Power BI Using the QuickBooks Software Development Kit Parsing a Simple Subroutine Invoking QBFC Identifying the Sources of the Variables Understanding then Rationale Running the Assembly Tracker Opening the QuickBooks Company File Opening the Excel File Allowing Access to QuickBooks Data Chapter 20 Analyzing Contributions and Margins Calculating the Contribution Margin Classifying Costs Estimating Semivariable Costs Using Unit Contribution Producing Digital Video Discs (Continued) Increasing the Contribution Margin Creating an Operating Income Statement Finding the Break-Even Point Calculating Breakeven in Units Calculating Breakeven in Sales Calculating Breakeven in Sales Dollars with a Specified Level of Profit Charting the Break-Even Point Choosing the Chart Type Displaying a Break-Even Chart in Power BI Making Assumptions in Contribution Analysis Linear Relationships Assignment of Costs Constant Sales Mix Worker Productivity Determining Sales Mix Summary Chapter 21 Pricing and Costing Using Absorption and Contribution Costing Understanding Absorption Costing Understanding Contribution Costing Applying the Contribution Approach to a Pricing Decision: Goal Seek Applying the Contribution Approach to a Pricing Decision: Solver Using Contribution Analysis for New Products Allocating Expenses to Product Lines Varying the Inputs Estimating the Effect of Cross-Elasticity Summary 9780789759580 TOC 11/12/2018
£29.69
O'Reilly Excel Cookbook
Book Synopsis
£42.39
John Wiley & Sons Inc Excel Macros for Dummies
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
£24.79
John Wiley & Sons Getting Great Results with Excel Pivot Tables
Book SynopsisGet more out of your data with step-by-step tutorials for the Excel features you need to know Excel is still the most popular tool for organizing and analyzing data, and today's professionals are expected to have a high degree of fluency with it. Complex Excel tools like Pivot Tables, PowerQuery, and PowerPivot can help you manage and report on data the way you need to. Getting Great Results with Excel Pivot Tables, PowerQuery and PowerPivot offers a fresh look at how these tools can help you. Author and Microsoft Certified Trainer Thomas Fragale breaks down the topics into easy-to-use steps and screenshots, so you'll be able to put your advanced Excel skills into practice right away. Using Pivot Tables, PowerQuery, and PowerPivot, you can import, sort, transform, summarize, and present your data, all without having to be a programmer. This book takes the technical jargon out of using these features, so you can do your job more efficiently, bring value to your teams, and advance your c
£30.39
Microsoft Press,U.S. Microsoft Excel 2019 Inside Out
Book SynopsisYou’re beyond the basics—so dive in and really put your spreadsheet skills to work! This supremely organized reference is packed with hundreds of timesaving solutions, troubleshooting tips, and workarounds. It’s all muscle and no fluff. Learn how the experts tackle Excel—and challenge yourself to new levels of mastery. Includes companion eBook and sample files. Topics include: Customizing the Excel workspace Best practices for designing and managing worksheets Creating formulas and functions Performing statistical, what-if, and other data analysis Core to advanced charting techniques Using graphics and sparklines Managing databases and tables Automating Excel with macros and custom functions Collaborating in Excel online, in the cloud, and more Extending Excel Table of ContentsPart I THE EXCEL INTERFACE 1 What’s New in Excel 2019 and Office 365 2 Using the Excel Interface 3 Customizing Excel 4 Keyboard Shortcuts Part II CALCULATING WITH EXCEL 5 Understanding Formulas 6 Controlling Formulas 7 Understanding Functions 8 Using Everyday Functions: Math, Date and Time, and Text Functions 9 Using Powerful Functions: Logical, Lookup, Web, and Database Functions 10 Other Functions 11 Connecting Worksheets and Workbooks 12 Array Formulas and Names in Excel Part III DATA ANALYSIS WITH EXCEL 13 Transforming Data with Power Query 14 Summarizing Data Using Subtotals or Filter 15 Using Pivot Tables to Analyze Data 16 Using Slicers and Filtering a Pivot Table 17 Mashing Up Data with Power Pivot 18 Using What-If, Scenario Manager, Goal Seek, and Solver 19 Automating Repetitive Functions Using VBA Macros 20 More Tips and Tricks for Excel 2016 Part IV EXCEL VISUALS 21 Formatting Worksheets 22 Using Data Visualizations and Conditional Formatting 23 Graphing Data Using Excel Charts 24 Using 3D Maps 25 Using Sparklines 26 Formatting Spreadsheets for Presentation 27 Printing 28 Excel Online APPENDIXES A DAX Functions B Power Query M Functions
£34.19
BPB Publications Learning Power Query
Book SynopsisImport data from Excel, folders, and various external sources. Transform, filter, and reshape data effectively. Develop dynamic parameters, lists, and filters driven by queries. Construct and repurpose custom M functions for use across different files.
£32.39
Pearson Education (US) Bayesian Analysis with Excel and R
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
£34.19
John Wiley & Sons Inc Modeling Structured Finance Cash Flows with
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
£54.75
John Wiley & Sons Inc Reverse Engineering Deals on Wall Street with
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.
£56.25
John Wiley & Sons Inc Excel for Chemists with CDROM
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
£53.96
John Wiley & Sons Inc Credit Risk Modeling using Excel and VBA
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
£69.35
John Wiley & Sons Inc Cash CDO Modeling in Excel
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.
£46.54
John Wiley & Sons Inc Advanced Modelling in Finance Using Excel and VBA
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
£75.99
Microsoft Press,U.S. MOS 2016 Study Guide for Microsoft Excel Expert
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
£16.64
Pearson Education VBA and Macros for Microsoft Office Excel 2007
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
£35.28
