Spreadsheet software Books

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

Read more less

Book SynopsisJoan Lambert has worked closely with Microsoft technologies since 1986, and in the training and certification industry since 1997. As President and CEO of Online Training Solutions, Inc. (OTSI), Joan guides the translation of technical information and requirements into useful, relevant, and measurable resources for people who are seeking certification of their computer skills or who simply want to know how to get things done efficiently. Joan is the author or coauthor of more than four dozen books about Windows and Office (for Windows, Mac, and iPad), five generations of Microsoft Office Specialist certification study guides, video-based training courses for SharePoint and OneNote, QuickStudy guides for Windows and Office, and the GO! series book for Outlook 2016. Blissfully based in America's Finest City, Joan is a Microsoft CertifiedTable of Contents Introduction Taking a Microsoft Office Specialist exam Exam 77-727: Microsoft Excel 2016 Core Prerequisites Objective group 1: Create and manage worksheets and workbooks Objective 1.1: Create worksheets and workbooks Objective 1.2: Navigate in worksheets and workbooks Objective 1.3: Format worksheets and workbooks Objective 1.4: Customize options and views for worksheets and workbooks Objective 1.5: Configure worksheets and workbooks for distribution Objective group 2: Manage data cells and ranges Objective 2.1: Insert data in cells and ranges Objective 2.2: Format cells and ranges Objective 2.3: Summarize and organize data Objective group 3: Create tables Objective 3.1: Create and manage tables Objective 3.2: Manage table styles and options Objective 3.3: Filter and sort tables Objective group 4: Perform operations by using formulas and functions Objective 4.1: Summarize data by using functions Objective 4.2: Perform conditional operations by using functions Objective 4.3: Format and modify text by using functions Objective group 5: Create charts and objects Objective 5.1: Create charts Objective 5.2: Format charts Objective 5.3: Insert and format objects

Read more less

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

Read more less

Book Synopsis

Read more less

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

Read more less

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

Read more less

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

Read more less

Book Synopsis

Read more less

Book Synopsis

Read more less

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

Read more less

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

Read more less

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

Read more less

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

Read more less

Book SynopsisA practical guide to data mining using SQL and Excel Data Analysis Using SQL and Excel, 2nd Edition shows you how to leverage the two most popular tools for data query and analysisSQL and Excelto perform sophisticated data analysis without the need for complex and expensive data mining tools. Written by a leading expert on business data mining, this book shows you how to extract useful business information from relational databases. You''ll learn the fundamental techniques before moving into the where and why of each analysis, and then learn how to design and perform these analyses using SQL and Excel. Examples include SQL and Excel code, and the appendix shows how non-standard constructs are implemented in other major databases, including Oracle and IBM DB2/UDB. The companion website includes datasets and Excel spreadsheets, and the book provides hints, warnings, and technical asides to help you every step of the way. Data Analysis Using SQL and Excel, 2nd Table of ContentsForeword xxxiii Introduction xxxvii Chapter 1 A Data Miner Looks at SQL 1 Databases, SQL, and Big Data 2 Picturing the Structure of the Data 6 Picturing Data Analysis Using Dataflows 16 SQL Queries 21 Subqueries and Common Table Expressions Are Our Friends 36 Lessons Learned 47 Chapter 2 What’s in a Table? Getting Started with Data Exploration 49 What Is Data Exploration? 50 Excel for Charting 51 Sparklines 65 What Values Are in the Columns? 68 More Values to Explore—Min, Max, and Mode 79 Exploring String Values 81 Exploring Values in Two Columns 86 From Summarizing One Column to Summarizing All Columns 90 Lessons Learned 96 Chapter 3 How Different Is Different? 97 Basic Statistical Concepts 98 How Different Are the Averages? 105 Sampling from a Table 110 Counting Possibilities 115 Ratios and Their Statistics 128 Chi-Square 132 What Months and Payment Types Have Unusual Affinities for Which Types of Products? 140 Lessons Learned 143 Chapter 4 Where Is It All Happening? Location, Location, Location 145 Latitude and Longitude 146 Census Demographics 160 Geographic Hierarchies 172 Mapping in Excel 188 Lessons Learned 194 Chapter 5 It’s a Matter of Time 197 Dates and Times in Databases 198 Starting to Investigate Dates 204 How Long Between Two Dates? 218 Year-over-Year Comparisons 229 Counting Active Customers by Day 239 Simple Chart Animation in Excel 247 Lessons Learned 254 Chapter 6 How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value 255 Background on Survival Analysis 256 The Hazard Calculation 260 Survival and Retention 269 Comparing Different Groups of Customers 280 Comparing Survival over Time 287 Important Measures Derived from Survival 293 Using Survival for Customer Value Calculations 298 Forecasting 308 Lessons Learned 314 Chapter 7 Factors Affecting Survival: The What and Why of Customer Tenure 315 Which Factors Are Important and When 316 Left Truncation 328 Time Windowing 336 Competing Risks 342 Before and After 353 Lessons Learned 366 Chapter 8 Customer Purchases and Other Repeated Events 367 Identifying Customers 368 RFM Analysis 393 Which Households Are Increasing Purchase Amounts Over Time? 404 Time to Next Event 416 Lessons Learned 420 Chapter 9 What’s in a Shopping Cart? Market Basket Analysis 421 Exploring the Products 422 Products and Customer Worth 437 Product Geographic Distribution 448 Which Customers Have Particular Products? 451 Lessons Learned 463 Chapter 10 Association Rules and Beyond 465 Item Sets 466 The Simplest Association Rules 480 One-Way Association Rules 483 Two-Way Associations 489 Extending Association Rules 499 Lessons Learned 506 Chapter 11 Data Mining Models in SQL 507 Introduction to Directed Data Mining 508 Look-Alike Models 515 Lookup Model for Most Popular Product 522 Lookup Model for Order Size 528 Lookup Model for Probability of Response 534 Naive Bayesian Models (Evidence Models) 546 Lessons Learned 559 Chapter 12 The Best-Fit Line: Linear Regression Models 561 The Best-Fit Line 562 Measuring Goodness of Fit Using R2 581 Direct Calculation of Best-Fit Line Coefficients 584 Weighted Linear Regression 592 More Than One Input Variable 600 Lessons Learned 607 Chapter 13 Building Customer Signatures for Further Analysis 609 What Is a Customer Signature? 610 Designing Customer Signatures 617 Operations to Build Customer Signatures 622 Extracting Features 639 Summarizing Customer Behaviors 644 Lessons Learned 653 Chapter 14 Performance Is the Issue: Using SQL Effectively 655 Query Engines and Performance 656 Considerations When Thinking About Performance 660 Performance: Its Meaning and Measurement 663 Performance Improvement 101 665 Using Indexes Effectively 668 When OR Is a Bad Thing 683 Pros and Cons: Different Ways of Expressing the Same Thing 686 Window Functions 694 Lessons Learned 701 Appendix Equivalent Constructs Among Databases 703 Index 731

Read more less

Book SynopsisSolutions Manual to accompany Introduction to Quantitative Methods in Business: With Applications Using Microsoft(R) Office Excel(R).Table of Contents1. The Mathematical Toolbox: A Summary 1 1.2 Linear Functions 1 1.3.1 Solving Two Simultaneous Linear Equations 1 1.4 Summation Notation 2 1.5 Sets 3 1.6 Functions and Graphs 3 1.7 Working with Functions 4 1.8 Differentiation and Integration 5 Solutions to Odd-Numbered Exercises 8 2. Applications of Linear and Nonlinear Functions: A Summary 32 2.2 Linear Demand and Supply Functions 32 2.3 Linear Total Cost and Total Revenue Functions 33 2.4 Market Equilibrium 33 2.6 Applications of Nonlinear Functions 34 2.7 Present Value of an Income Stream 35 2.8 Average Values 35 2.9 Marginal Values 36 2.10 Elasticity 36 Solutions to Odd-Numbered Exercises 37 3. Optimization: A Summary 47 3.2 Unconstrained Optimization 47 3.2.1 Models of Profit and Revenue Maximization 47 3.2.3 Solution Using the Calculus Approach 47 3.2.5 Solution Using the Calculus Approach 47 3.3 Models of Cost Minimization: Inventory Cost Functions and Economic Order Quantity (EOQ) 48 3.3.2 Solution Using the Calculus Approach 49 3.4 Constrained Optimization: Linear Programming 50 3.4.1 Linear Programming: Maximization 50 3.4.2 Linear Programming: Minimization 51 Solutions to Odd-Numbered Exercises 52 4. What Is Business Statistics? 68 4.3 Descriptive Statistics: Tabular and Graphical Techniques 68 4.4 Descriptive Statistics: Numerical Measures of Central Tendency or Location of Data 70 4.4.1 Population Mean 70 4.4.2 Sample Mean 70 4.4.3 Weighted Mean 70 4.4.4 Mean of a Frequency Distribution: Grouped Data 71 4.4.5 Geometric Mean 71 4.4.6 Median 71 4.4.7 Quantiles, Quartiles, Deciles, and Percentiles 71 4.4.8 Mode 72 4.5 Descriptive Statistics: Measures of Dispersion (Variability or Spread) 73 4.5.2 Variance 73 4.5.3 Standard Deviation 74 4.5.4 Coefficient of Variation 74 4.5.5 Some Important Uses of the Standard Deviation 75 1. Standardization of Values 75 2. Chebysheff’s Theorem 75 4.5.6 Empirical Rule 75 4.6 Measuring Skewness 76 Solutions to Odd-Numbered Exercises 76 5. Probability and Applications 96 5.2 Some Useful Definitions 96 5.3 Probability Sources 96 5.3.1 Objective Probability 96 5.4 Some Useful Definitions Involving Sets of Events in the Sample Space 96 5.5 Probability Laws 97 5.5.2 Rule of Complements 97 5.5.3 Conditional Probability 97 5.5.4 General Multiplication Rule (Product Rule) 97 5.5.5 Independent Events 98 5.5.6 Probability Tree Approach 98 5.6 Contingency Table 98 Solutions to Odd-Numbered Exercises 100 6. Random Variables and Probability Distributions 105 6.2 Probability Distribution of a Discrete Random Variable X 105 6.3 Expected Value, Variance, and Standard Deviation of a Discrete Random Variable 106 6.3.1 Some Basic Rules of Expectation 106 6.3.2 Some Useful Properties of the Variance of X 107 6.4 Continuous Random Variables and Their Probability Distributions 107 6.5 A Specific Discrete Probability Distribution: The Binomial Case 108 6.5.1 Binomial Probability Distribution 108 6.5.2 Mean and Standard Deviation of the Binomial Random Variable 109 6.5.3 Cumulative Binomial Probability Distribution 110 Solutions to Odd-Numbered Exercises 110 Index 119

Read more less

Book SynopsisSet includes Introduction to Quantitative Methods in Business: With Applications Using Microsoft Office Excel ISBN 978-1-119-22097-8 and the accompanying Solutions Manual ISBN 978-1-119-22102-9 A well-balanced and accessible introduction to the elementary quantitative methods and Microsoft Office Excel applications used to guide business decision making Featuring quantitative techniques essential for modeling modern business situations, Introduction to Quantitative Methods in Business: With Applications Using Microsoft Office Excel provides guidance to assessing real-world data sets using Excel. The book presents a balanced approach to the mathematical tools and techniques with applications used in the areas of business, finance, economics, marketing, and operations. The authors begin by establishing a solid foundation of basic mathematics and statistics before moving on to more advanced concepts. The first part of the book starts by develop

Read more less

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

Read more less

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

Read more less

Book SynopsisThe must-have statistics guide for students of health services Statistics for Health Care Management and Administration: Working with Excel introduces the uses of statistics in healthcare management and administration using the features and functions of Microsoft Excel. The book introduces students to statistics within the context of health care, focusing on the major data and analysis techniques used in the field. Step-by-step instructions in the latest version of Excel and numerous annotated screen shots make examples easy to follow and understand. This updated fourth edition provides the same content and explanations that have made the previous editions so popular, offering revisions drawn directly from universities where the book has been used. All content has been brought current with the newest version of excel, and changes in the field of healthcare administration are covered as well. Statistics for Health Care Management and Administration gets students off to a great start by introducing statistics in the context of their chosen field. Learn the basics of statistics in the context of ExcelUnderstand how to acquire data and display it for analysisMaster important concepts and tests, including regressionTurn test results into usable information with proper analysisThis book not only helps students develop the necessary data analysis skills, but also boosts familiarity with important software that employers will be looking for.Table of ContentsPreface xiii Introducing Excel xiii So How Did We Get to Here? xiii Intended Level of the Textbook xiv Textbook Organization xiv Leading by Example(s) xv Acknowledgments xvii The Authors xix About the Companion Website xxi Part 1 1 Chapter 1 Statistics and Excel 3 1.1 How This Book Differs from Other Statistics Texts 3 1.2 Statistical Applications in Health Policy and Health Administration 4 Exercises for Section 1.2 14 1.3 What Is the ‘‘Big Picture’’? 15 1.4 Some Initial Definitions 16 Exercises for Section 1.4 26 1.5 Five Statistical Tests 28 Exercises for Section 1.5 30 Chapter 2 Excel as a Statistical Tool 33 2.1 The Basics 33 Exercises for Section 2.1 35 2.2 Working and Moving Around in a Spreadsheet 36 Exercises for Section 2.2 41 2.3 Excel Functions 41 Exercises for Section 2.3 46 2.4 The =IF() Function 47 Exercises for Section 2.4 50 2.5 Excel Graphs 51 Exercises for Section 2.5 56 2.6 Sorting a String of Data 57 Exercise for Section 2.6 60 2.7 The Data Analysis Pack 61 2.8 Functions That Give Results in More than One Cell 63 Exercises for Section 2.8 66 2.9 The Dollar Sign ($) Convention for Cell References 67 Chapter 3 Data Acquisition: Sampling and Data Preparation 71 3.1 The Nature of Data 71 Exercises for Section 3.1 78 3.2 Sampling 79 Exercises for Section 3.2 93 3.3 Data Access and Preparation 94 Exercises for Section 3.3 107 3.4 Missing Data 108 Chapter 4 Data Display: Descriptive Presentation, Excel Graphing Capability 111 4.1 Creating, Displaying, and Understanding Frequency Distributions 111 Exercises for Section 4.1 129 4.2 Using the Pivot Table to Generate Frequencies of Categorical Variables 131 Exercises for Section 4.2 135 4.3 A Logical Extension of the Pivot Table: Two Variables 135 Exercises for Section 4.3 140 Chapter 5 Basic Concepts of Probability 141 5.1 Some Initial Concepts and Definitions 141 Exercises for Section 5.1 150 5.2 Marginal Probabilities, Joint Probabilities, and Conditional Probabilities 150 Exercises for Section 5.2 160 5.3 Binomial Probability 161 Exercises for Section 5.3 171 5.4 The Poisson Distribution 173 Exercises for Section 5.4 178 5.5 The Normal Distribution 178 Chapter 6 Measures of Central Tendency and Dispersion: Data Distributions 183 6.1 Measures of Central Tendency and Dispersion 183 Exercises for Section 6.1 196 6.2 The Distribution of Frequencies 197 Exercises for Section 6.2 208 6.3 The Sampling Distribution of the Mean 209 Exercises for Section 6.3 219 6.4 Mean and Standard Deviation of a Discrete Numerical Variable 220 Exercises for Section 6.4 222 6.5 The Distribution of a Proportion 222 Exercises for Section 6.5 227 6.6 The t Distribution 227 Exercises for Section 6.6 232 Part 2 235 Chapter 7 Confidence Limits and Hypothesis Testing 237 7.1 What Is a Confidence Interval? 237 Exercises for Section 7.1 243 7.2 Calculating Confidence Limits for Multiple Samples 244 Exercises for Section 7.2 246 7.3 What Is Hypothesis Testing? 247 Exercises for Section 7.3 249 7.4 Type I and Type II Errors 250 Exercises for Section 7.4 266 7.5 Selecting Sample Sizes 267 Exercises for Section 7.5 269 Chapter 8 Statistical Tests for Categorical Data 271 8.1 Independence of Two Variables 271 Exercises for Section 8.1 282 8.2 Examples of Chi-Square Analyses 283 Exercises for Section 8.2 289 8.3 Small Expected Values in Cells 290 Exercises for Section 8.3 292 Chapter 9 t Tests for Related and Unrelated Data 295 9.1 What Is a t Test? 295 Exercises for Section 9.1 302 9.2 A t Test for Comparing Two Groups 303 Exercises for Section 9.2 316 9.3 A t Test for Related Data 318 Exercises for Section 9.3 321 Chapter 10 Analysis of Variance 323 10.1 One-Way Analysis of Variance 323 Exercises for Section 10.1 339 10.2 ANOVA for Repeated Measures 340 Exercises for Section 10.2 348 10.3 Factorial Analysis of Variance 349 Exercises for Section 10.3 362 Chapter 11 Simple Linear Regression 365 11.1 Meaning and Calculation of Linear Regression 365 Exercises for Section 11.1 373 11.2 Testing the Hypothesis of Independence 374 Exercises for Section 11.2 380 11.3 The Excel Regression Add-In 381 Exercises for Section 11.3 388 11.4 The Importance of Examining the Scatterplot 388 11.5 The Relationship between Regression and the t Test 391 Exercises for Section 11.5 392 Chapter 12 Multiple Regression: Concepts and Calculation 395 12.1 Introduction 395 Exercises for Section 12.1 406 Chapter 13 Extensions of Multiple Regression 409 13.1 Dummy Variables in Multiple Regression 409 Exercises for Section 13.1 420 13.2 The Best Regression Model 421 Exercises for Section 13.2 431 13.3 Correlation and Multicolinearity 432 Exercises for Section 13.3 435 13.4 Nonlinear Relationships 435 Exercises for Section 13.4 447 Chapter 14 Analysis with a Dichotomous Categorical Dependent Variable 449 14.1 Introduction to the Dichotomous Dependent Variable 450 14.2 An Example with a Dichotomous Dependent Variable: Traditional Treatments 451 Exercises for Section 14.2 462 14.3 Logit for Estimating Dichotomous Dependent Variables 463 Exercises for Section 14.3 475 14.4 A Comparison of Ordinary Least Squares, Weighted Least Squares, and Logit 476 Exercises for Section 14.4 480 Appendix A Multiple Regression and Matrices 481 An Introduction to Matrix Math 481 Addition and Subtraction of Matrices 482 Multiplication of Matrices 483 Matrix Multiplication and Scalars 484 Finding the Determinant of a Matrix 484 Matrix Capabilities of Excel 486 Explanation of Excel Output Displayed with Scientific Notation 489 Using the b Coefficients to Generate Regression Results 490 Calculation of All Multiple Regression Results 491 Exercises for Appendix A 494 References 497 Glossary 499 Index 513

Read more less

Book Synopsis

Read more less

Book Synopsis

Read more less

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

Read more less

Book SynopsisExcel Tips & Tricks A handy resource for beginning, intermediate or advanced Excel users, this 3-panel (6-page) guide is jam-packed with information and helpful, time-saving hints on Microsoft's award-winning spreadsheet software. Featuring easy-to-see screen captures and icons, this guide is an ideal next-to-the-monitor reference.

Read more less

Book Synopsis

Read more less

Book SynopsisAmong the many data analyzing features to be found in Microsoft's Excel software, PivotTables and PivotCharts are two of the most useful. Find out how they can work for you with our jam-packed 3-panel guide, which features step-by-step instructions and full-color screen shots for easy reference.

Read more less

Book Synopsis

Read more less

Book SynopsisQuick and easy 6-page laminated guide with locations of functions and how to perform them in Excel 2016. Whether you are a beginner or experienced user, the advanced version is more coverage of the most popular and useful functions that users may even discover and use for the first time. Curtis Frye, author of multiple books on Excel, creator of many Lynda.com videos and an experienced corporate trainer used his experience and knowledge to cover the most relevant functions for users at different levels. Look for Excel 2016, Excel 2016 'Tips and Tricks', 'Formulas' and 'Pivot Tables' QuickStudy guides to have a complete power-user reference set. Suggested uses: ⢠Workplace â flat for easy storage and access at a moments notice to find a function you need to use, or to jog your memory for a function you do not use often ⢠Company Training â reduce help-desk calls and keep productivity flowing for a team or for your entire company

Read more less

Book SynopsisQuick and easy 6 page laminated guide focuses on creating and using formulas efficiently and effectively in Excel 2016. Whether you are a beginner or experienced user, formulas are Excel's superpower that can be yours. Curtis Frye, author of multiple books on Excel, creator of many Lynda.com videos and an experienced corporate trainer used his experience and knowledge to cover the most relevant use of formulas for users at different levels. Look for Excel 2016, Excel 2016 'Tips and Tricks', 'Advanced' and 'Pivot Tables' QuickStudy guides to have a complete power-user reference set. Suggested uses: o Workplace â flat for easy storage and access at a moments notice to find a formula function you need to use, or to jog your memory for a function you do not use often o Company Training â reduce help-desk calls and keep productivity flowing for a team or for your entire company Students/Professors â make lives easier for students in many majors needing

Read more less

Book SynopsisQuick and easy 6-page laminated guide focuses on creating and using pivot tables efficiently and effectively in Excel 2016. Whether you are a beginner or experienced user, pivot tables offer flexibility, doubling down on Excel's power. Curtis Frye, author of multiple books on Excel, creator of many Lynda.com videos and an experienced corporate trainer used his experience and knowledge to cover the most relevant use of pivot tables for users at different levels. Look for Excel 2016, Excel 2016 'Tips and Tricks', 'Advanced' and 'Formulas' QuickStudy guides to have a complete power-user reference set. Suggested uses: o Workplace â flat for easy storage and access at a moments notice to find the answer you need, or to jog your memory for a function you do not use often o Company Training â reduce help-desk calls and keep productivity flowing for a team or for your entire company o Students/Professors â make lives easier for students in many majors

Read more less

Book SynopsisMaster Excel in less than two weeks with this unique project-based book! Let's face it, we all master skills in our own way, but building a soup-to-nuts project is one of the best ways to make learning stick and get up to speed quickly. Whether you are just getting started with Excel or are an experienced user, this book will elevate your knowledge and skills. For a beginner, the micro examples in each chapter will warm you up before you dive into the projects. For experienced users, the projects, especially those with table setup considerations, will help you become more creative in your interactions with Excel. Readers will benefit from building eight unique projects, each covering a different topic, including a word game, a food nutrition ranking, a payroll (tax withholding) calculation, an encryption, a two-way table, a Kaplan-Meier analysis, a data analysis via a pivot table and the K-means Clustering data mining method. Through these projects, you will experience firsthand how Table of ContentsChapter 1: Master Excel through Projects Chapter 2: Food Nutrition Ranking Chapter 3: Payroll Calculation Chapter 4: Public and Private Key Cryptography Chapter 5: Two-Way Table and Chi-Square Test Chapter 6: Kaplan-Meier Analysis Chapter 7: PivotTable Data Analysis Chapter 8: K-means Clustering and Iterative Calculation

Read more less

Book SynopsisUnderstand the power of dynamic arrays:a single formula can generate new and vastly more efficient spreadsheet possibilities. This bookintroduces spreadsheet users to dynamic array functions in Microsoft Excel 365, defines and details the distinctive ways in which they work, and shows how they can be applied to a wide swath of data-analytic tasks. While array formulas and functions have long held a place in the spreadsheet toolbox (although, for many of us, shunted to an obscure corner), the dynamic array engine offers a more user-friendly and intelligible set of means for manipulating spreadsheet data in the array mode. The single-formula, multi-cell capability of dynamic arrays has been extended to nearly all existing spreadsheet functions, offering a new, default way of working. As a result, many tasks can now be executed with dynamic arrays without having to resort to the new functions at all. After defining arrays and dynamic array formulas, this book helps you examine the dynTable of ContentsChapter 1: What’s An Array, Anyway? Chapter 2: Array Formula Basics Chapter 3: Dynamic Arrays -- Time for Some Heavy Lifting Chapter 4: The SEQUENCE Function: Made to Order Chapter 5: UNIQUE -- A Singular Function Chapter 6: SORT and SORTBY: The ABCs (and the CBAs) Chapter 7: The FILTER Function: Cutting the Data Down to Size Chapter 8: RANDARRAY: Unpredictably Useful Chapter 9: The Implicit Intersection Operator: The Function You'll Probably Never UseChapter 10: TEXTSPLIT, TEXTBEFORE, and TEXTAFTER: Putting Words in Their PlacesChapter 11: TOCOL and TOROW -- Straightening Out the DataChapter 12: WRAPCOLS and WRAPROWS: Giving Some Direction to the DataChapter 13: VSTACK and HSTACK: What They're AboutChapter 14: CHOOSECOLS and CHOOSEROWS: Less is MoreChapter 15: TAKE and DROP: Selecting and Rejecting the DataChapter 16: EXPAND: Bulking Up the Data

Read more less

Book SynopsisThis practical guide teaches analysts a useful subset of Excel skills that will enable them to access and interpret financial informationwithout any prior programming experience.

Read more less

Book SynopsisWhile Excel remains ubiquitous in the business world, recent Microsoft feedback forums are full of requests to include Python as an Excel scripting language. What makes this combination so compelling? In this hands-on guide, Felix Zumstein shows experienced Excel users how to integrate these two worlds efficiently.

Read more less

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

Read more less

Book SynopsisPrepare for Microsoft Exam 70-778–and help demonstrate your real-world mastery of Power BI data analysis and visualization. Designed for experienced BI professionals and data analysts ready to advance their status, Exam Ref focuses on the critical thinking and decision-making acumen needed for success at the MCSA level. Focus on the expertise measured by these objectives: Consume and transform data by using Power BI Desktop Model and visualize data Configure dashboards, reports, and apps in the Power BI Service This Microsoft Exam Ref: Organizes its coverage by exam objectives Features strategic, what-if scenarios to challenge you Assumes you have experience consuming and transforming data, modeling and visualizing data, and configuring dashboards using Excel and Power BI Table of Contents Chapter 1 Consuming and transforming data by using Power BI Desktop Skill 1.1: Connect to data sources Skill 1.2: Perform transformations Skill 1.3: Cleanse data Chapter 2 Modeling and visualizing data Skill 2.1: Create and optimize data models Skill 2.2: Create calculated columns, calculated tables, and measures Skill 2.3: Measure performance by using KPIs, gauges, and cards Skill 2.4: Create hierarchies Skill 2.5: Create and format interactive visualizations Skill 2.6: Manage custom reporting solutions Chapter 3 Configure dashboards, reports, and apps in the Power BI Service Skill 3.1: Access on-premises data Skill 3.2: Configure a dashboard Skill 3.3: Publish and embed reports Skill 3.4: Configure security for dashboards, reports, and apps Skill 3.5 Configure apps and apps workspaces

Read more less

Book SynopsisThe quick way to learn Microsoft Excel 2019! This is learning made easy. Get more done quickly with Microsoft Excel 2019. Jump in wherever you need answers–brisk lessons and detailed screenshots show you exactly what to do, step by step. Quickly set up workbooks, enter data, and format it for easier viewing Perform calculations and find and correct errors Create sophisticated forecast worksheets, key performance indicators (KPIs), and timelines Visualize data with 3D maps, funnel charts, and other powerful tools Import, process, summarize, and analyze huge datasets with Excel’s improved PowerPivot and Power Query Build powerful data models and use them in business intelligence Look up just the tasks and lessons you need Table of Contents Part 1: Create and format workbooks Chapter 1: Set up a workbook Chapter 2: Work with data and Excel tables Chapter 3: Perform calculations on data Chapter 4: Change workbook appearance Part 2: Analyze and present data Chapter 5: Manage worksheet data Chapter 6: Reorder and summarize data Chapter 7: Combine data from multiple sources Chapter 8: Analyze alternative data sets Chapter 9: Create charts and graphics Chapter 10: Create dynamic worksheets by using PivotTables Part 3: Collaborate and share in Excel Chapter 11: Print worksheets and charts Chapter 12: Automate repetitive tasks by using macros Chapter 13: Work with other Microsoft Office apps Chapter 14: Collaborate with colleagues Part 4: Perform advanced analysis Chapter 15: Perform business intelligence analysis Chapter 16: Create forecasts and visualizations

Read more less

Book Synopsis

Read more less

Book SynopsisDesigned with Excel gurus in mind, this handbook outlines how to create formulas that can be used to solve everyday problems with a series of data values that standard Excel formulas cannot or would be too arduous to attempt. Beginning with an introduction to array formulas, this manual examines topics such as how they differ from ordinary formulas, the benefits and drawbacks of their use, functions that can and cannot handle array calculations, and array constants and functions. Among the practical applications surveyed include how to extract data from tables and unique lists, how to get results that match any criteria, and how to utilize various methods for unique counts. This book contains 529 screen shots.

Read more less

Book SynopsisData analysis expressions (DAX) is the formula language of Power Pivot. Learning the DAX language is key to empower Excel users so they can take advantage of these new Business Intelligence (BI) capabilities. This volume clearly explains the concepts of Power Pivot while at the same time offering hands-on practice to engage the reader and help new knowledge stick. This second edition has been updated for the Excel 2016 user interface while still providing a bridge for readers wanting to learn DAX in the Excel environment and then transfer their new DAX skills across to Power BI.

Read more less