Microsoft Office Excel 2007: Data Analysis and Business Modeling: Data Analysis and Business Modeling [NOOK Book]

Table of Contents

Preface; What You Should Know Before Reading this Book; How to Use this Book; Using the Companion CD; Support Information; Acknowledgments; Introduction to Excel 2007: What’s New?; Chapter 1: Range Names; 1.1 How Can I Create Named Ranges?; 1.2 Remarks; 1.3 Problems; Chapter 2: Lookup Functions; 2.1 Syntax of the Lookup Functions; 2.2 Problems; Chapter 3: The INDEX Function; 3.1 Syntax of the INDEX Function; 3.2 Problems; Chapter 4: The MATCH Function; 4.1 Problems; Chapter 5: Text Functions; 5.1 Text Function Syntax; 5.2 Text Functions in Action; 5.3 Extracting Data by Using the Text To Columns Wizard; 5.4 Problems; Chapter 6: Dates and Date Functions; 6.1 Problems; Chapter 7: Evaluating Investments by Using Net Present Value Criteria; 7.1 Problems; Chapter 8: Internal Rate of Return; 8.1 Problems; Chapter 9: More Excel Financial Functions; 9.1 CUMPRINC and CUMIPMT Functions; 9.2 Problems; Chapter 10: Circular References; 10.1 Problems; Chapter 11: IF Statements; 11.1 Problems; Chapter 12: Time and Time Functions; 12.1 Problems; Chapter 13: The Paste Special Command; 13.1 Problems; Chapter 14: The Auditing Tool; 14.1 Problems; Chapter 15: Sensitivity Analysis with Data Tables; 15.1 Problems; Chapter 16: The Goal Seek Command; 16.1 Problems; Chapter 17: Using the Scenario Manager for Sensitivity Analysis; 17.1 Remarks; 17.2 Problems; Chapter 18: The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions; 18.1 Remarks; 18.2 Problems; Chapter 19: The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS Functions; 19.1 Problems; Chapter 20: The OFFSET Function; 20.1 Remark; 20.2 Problems; Chapter 21: The INDIRECT Function; 21.1 Problems; Chapter 22: Conditional Formatting; 22.1 Problems; Chapter 23: Sorting in Excel; 23.1 Problems; Chapter 24: Tables; 24.1 Problems; Chapter 25: Spin Buttons, Scroll Bars, Option Buttons, Check Boxes, Combo Boxes, and Group List Boxes; 25.1 Spin Buttons and Scroll Bars; 25.2 Problems; Chapter 26: An Introduction to Optimization with Excel Solver; 26.1 Problems; Chapter 27: Using Solver to Determine the Optimal Product Mix; 27.1 Problems; Chapter 28: Using Solver to Schedule Your Workforce; 28.1 Problems; Chapter 29: Using Solver to Solve Transportation or Distribution Problems; 29.1 Problems; Chapter 30: Using Solver for Capital Budgeting; 30.1 Handling Other Constraints; 30.2 Problems; Chapter 31: Using Solver for Financial Planning; 31.1 Problems; Chapter 32: Using Solver to Rate Sports Teams; 32.1 Problems; Chapter 33: Importing Data from a Text File or Document; 33.1 Problems; Chapter 34: Importing Data from the Internet; 34.1 Problems; Chapter 35: Validating Data; 35.1 Remarks; 35.2 Problems; Chapter 36: Summarizing Data by Using Histograms; 36.1 Problems; Chapter 37: Summarizing Data by Using Descriptive Statistics; 37.1 Mean; 37.2 Median; 37.3 Mode; 37.4 Kurtosis; 37.5 Sample variance and sample standard deviation; 37.6 Range; 37.7 Problems; Chapter 38: Using PivotTables to Describe Data; 38.1 Remarks About Grouping; 38.2 Problems; Chapter 39: Summarizing Data with Database Statistical Functions; 39.1 Problems; Chapter 40: Filtering Data and Removing Duplicates; 40.1 Problems; Chapter 41: Consolidating Data; 41.1 Problems; Chapter 42: Creating Subtotals; 42.1 Problems; Chapter 43: Estimating Straight Line Relationships; 43.1 Problems; Chapter 44: Modeling Exponential Growth; 44.1 Problems; Chapter 45: The Power Curve; 45.1 Problems; Chapter 46: Using Correlations to Summarize Relationships; 46.1 Filling in the correlation matrix; 46.2 Using the CORREL function; 46.3 Relationship between correlation and R2; 46.4 Correlation and regression towards the mean; 46.5 Problems; Chapter 47: Introduction to Multiple Regression; 47.1 What is the best prediction equation?; Chapter 48: Incorporating Qualitative Factors into Multiple Regression; Chapter 49: Modeling Nonlinearities and Interactions; 49.1 Problems for Chapters 47 Through 49; Chapter 50: Analysis of Variance: One-Way ANOVA; 50.1 Problems; Chapter 51: Randomized Blocks and Two-Way ANOVA; 51.1 Problems; Chapter 52: Using Moving Averages to Understand Time Series; 52.1 Problem; Chapter 53: Winter’s Method; 53.1 Time Series Characteristics; 53.2 Parameter Definitions; 53.3 Initializing Winter’s Method; 53.4 Estimating the Smoothing Constants; 53.5 Remarks; 53.6 Problems; Chapter 54: Forecasting in the Presence of Special Events; 54.1 Problems; Chapter 55: An Introduction to Random Variables; 55.1 Problems; Chapter 56: The Binomial, Hypergeometric, and Negative Binomial Random Variables; 56.1 Coke or Pepsi; 56.2 Elevator Rails; 56.3 Airline Overbooking; 56.4 Problems; Chapter 57: The Poisson and Exponential Random Variable; 57.1 Problems; Chapter 58: The Normal Random Variable; 58.1 What fraction of people have an IQ of less than 90?; 58.2 What ffffffraction of all people have IQs from 95 through 120?; 58.3 What fraction of all people have IQs of at least 130?; 58.4 Problems; Chapter 59: Weibull and Beta Distributions: Modeling Machine Life and Duration of a Project; 59.1 What is the probability that a machine will last at least 20 hours?; 59.2 What is the probability that a machine will last from 15 through 30 hours?; 59.3 Problems; Chapter 60: Introduction to Monte Carlo Simulation; 60.1 Problems; Chapter 61: Calculating an Optimal Bid; 61.1 Problems; Chapter 62: Simulating Stock Prices and Asset Allocation Modeling; 62.1 Problems; Chapter 63: Fun and Games: Simulating Gambling and Sporting Event Probabilities; 63.1 Problems; Chapter 64: Using Resampling to Analyze Data; 64.1 Problems; Chapter 65: Pricing Stock Options; 65.1 Problems; Chapter 66: Determining Customer Value; 66.1 Problems; Chapter 67: The Economic Order Quantity Inventory Model; 67.1 Problems; Chapter 68: Inventory Modeling with Uncertain Demand; 68.1 The back-order case; 68.2 The lost-sales case; 68.3 Problems; Chapter 69: Queuing Theory: The Mathematics of Waiting in Line; 69.1 Problems; Chapter 70: Estimating a Demand Curve; 70.1 Problems; Chapter 71: Pricing Products by Using Tie-Ins; 71.1 Problems; Chapter 72: Pricing Products by Using Subjectively Determined Demand; 72.1 Problems; Chapter 73: Nonlinear Pricing; 73.1 Problems; Chapter 74: Array Formulas and Functions; 74.1 How many units of makeup did Jen sell?; 74.2 How many units of lipstick did Jen sell?; 74.3 How many units were sold by Jen or were lipstick?; 74.4 Can I summarize the number of units of each product sold by each salesperson?; 74.5 Problems; About the Author;

Wayne L. Winston is a professor of Decision Sciences at Indiana University's Kelley School of Business and has earned numerous MBA teaching awards. For 20+ years, he has taught clients at Fortune 500 companies how to use Excel to make smarter business decisions. Wayne and his business partner Jeff Sagarin developed the player-statistics tracking and rating system used by the Dallas Mavericks professional basketball team. He is also a two time Jeopardy! champion.