Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Microsoft® Excel® 2010: Data Analysis and Business Modeling
Introduction
What You Should Know Before Reading This Book
How to Use This Book
Using the Companion Content
How to Access Your Online Edition Hosted by Safari
How to Download the Online Edition to Your Computer
Errata and Book Support
We Want to Hear from You
Stay in Touch
Acknowledgments
1. What’s New in Excel 2010
Customizable Ribbon
Don’t Forget About the Quick Access Toolbar
Sparklines
Slicers
PowerPivot
New Excel Solver
File Tab
New Excel Functions
New Equation Editor
Improved Data Bars
Paste Special Live Preview
2. Range Names
How Can I Create Named Ranges?
Using the Name Box to Create a Range Name
Creating Named Ranges by Using the Create From Selection Option
Creating Range Names by Using the Name Manager Option
The Name Manager
Answers to This Chapter’s Questions
Remarks
Problems
3. Lookup Functions
Syntax of the Lookup Functions
VLOOKUP Syntax
HLOOKUP Syntax
Answers to This Chapter’s Questions
Problems
4. The INDEX Function
Syntax of the INDEX Function
Answers to This Chapter’s Questions
Problems
5. The MATCH Function
Answers to This Chapter’s Questions
Problems
6. Text Functions
Text Function Syntax
The LEFT Function
The RIGHT Function
The MID Function
The TRIM Function
The LEN Function
The FIND and SEARCH Functions
The REPT Function
The CONCATENATE and & Functions
The REPLACE Function
The VALUE Function
The UPPER and LOWER Functions
The CHAR Function
Answers to This Chapter’s Questions
Extracting Data by Using the Text To Columns Wizard
Problems
7. Dates and Date Functions
Answers to This Chapter’s Questions
Problems
8. Evaluating Investments by Using Net Present Value Criteria
Answers to This Chapter’s Questions
Problems
9. Internal Rate of Return
Answers to This Chapter’s Questions
Problems
10. More Excel Financial Functions
Answers to This Chapter’s Questions
CUMPRINC and CUMIPMT Functions
Problems
11. Circular References
Answers to This Chapter’s Questions
Problems
12. IF Statements
Answers to This Chapter’s Questions
Problems
13. Time and Time Functions
Answers to This Chapter’s Questions
Problems
14. The Paste Special Command
Answers to This Chapter’s Questions
Problems
15. Three-Dimensional Formulas
Answers to This Chapter’s Questions
Problems
16. The Auditing Tool
Answers to This Chapter’s Questions
Problems
17. Sensitivity Analysis with Data Tables
Answers to This Chapter’s Questions
Problems
18. The Goal Seek Command
Answers to This Chapter’s Questions
Problems
19. Using the Scenario Manager for Sensitivity Analysis
Answer to This Chapter’s Question
Remarks
Problems
20. The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions
Answers to This Chapter’s Questions
Remarks
Problems
21. The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS Functions
Answers to This Chapter’s Questions
Problems
22. The OFFSET Function
Answers to This Chapter’s Questions
Remarks
Problems
23. The INDIRECT Function
Answers to This Chapter’s Questions
Problems
24. Conditional Formatting
Answers to This Chapter’s Questions
What does the Stop If True option in the Manage Rules dialog box do?
Problems
25. Sorting in Excel
Answers to This Chapter’s Questions
Problems
26. Tables
Answers to This Chapter’s Questions
Problems
27. Spin Buttons, Scroll Bars, Option Buttons, Check Boxes, Combo Boxes, and Group List Boxes
Answers to This Chapter’s Questions
Problems
28. An Introduction to Optimization with Excel Solver
Problems
29. Using Solver to Determine the Optimal Product Mix
Answers to This Chapter’s Questions
Problems
30. Using Solver to Schedule Your Workforce
Answers to This Chapter’s Question
Problems
31. Using Solver to Solve Transportation or Distribution Problems
Answer to This Chapter’s Question
Problems
32. Using Solver for Capital Budgeting
Answer to This Chapter’s Question
Handling Other Constraints
Solving Binary and Integer Programming Problems
Problems
33. Using Solver for Financial Planning
Answers to This Chapter’s Questions
Problems
34. Using Solver to Rate Sports Teams
Answer to This Chapter’s Question
Problems
35. Warehouse Location and the GRG Multistart and Evolutionary Solver Engines
Understanding the GRG Multistart and Evolutionary Solver Engines
How Does Solver Solve Linear Solver Problems?
How Does the GRG Nonlinear Engine Solve Nonlinear Optimization Models?
How Does the Evolutionary Solver Engine Tackle Nonsmooth Optimization Problems?
Answer to This Chapter’s Questions
Problems
36. Penalties and the Evolutionary Solver
Answers to This Chapter’s Questions
Using Conditional Formatting to Highlight Each Employee’s Ratings
Problems
37. The Traveling Salesperson Problem
Answers to This Chapter’s Questions
Problems
38. Importing Data from a Text File or Document
Answers to This Chapter’s Question
Problems
39. Importing Data from the Internet
Answers to This Chapter’s Questions
Problems
40. Validating Data
Answers to This Chapter’s Questions
Remarks
Problems
41. Summarizing Data by Using Histograms
Answers to This Chapter’s Questions
Problems
42. Summarizing Data by Using Descriptive Statistics
Answers to This Chapter’s Questions
Using Conditional Formatting to Highlight Outliers
Problems
43. Using PivotTables and Slicers to Describe Data
Answers to This Chapter’s Questions
Remarks About Grouping
Problems
44. Sparklines
Answers to This Chapter’s Questions
Problems
45. Summarizing Data with Database Statistical Functions
Answers to This Chapter’s Questions
Problems
46. Filtering Data and Removing Duplicates
Answers to This Chapter’s Questions
Problems
47. Consolidating Data
Answer to This Chapter’s Question
Problems
48. Creating Subtotals
Answers to This Chapter’s Questions
Problems
49. Estimating Straight Line Relationships
Answers to This Chapter’s Questions
Problems
50. Modeling Exponential Growth
Answers to This Chapter’s Question
Problems
51. The Power Curve
Answer to This Chapter’s Question
Problems
52. Using Correlations to Summarize Relationships
Answer to This Chapter’s Question
Filling In the Correlation Matrix
Using the CORREL Function
Relationship Between Correlation and R2
Correlation and Regression Toward the Mean
Problems
53. Introduction to Multiple Regression
Answers to This Chapter’s Questions
54. Incorporating Qualitative Factors into Multiple Regression
Answers to This Chapter’s Questions
55. Modeling Nonlinearities and Interactions
Answers to This Chapter’s Questions
Problems for Chapter 53 Through
56. Analysis of Variance: One-Way ANOVA
Answers to This Chapter’s Questions
Problems
57. Randomized Blocks and Two-Way ANOVA
Answers to This Chapter’s Questions
Problems
58. Using Moving Averages to Understand Time Series
Answers to This Chapter’s Question
Problem
59. Winters’s Method
Time Series Characteristics
Parameter Definitions
Initializing Winters’s Method
Step 1
Step 2
Step 3
Estimating the Smoothing Constants
Step 1
Step 2
Step 3
Step 4
Step 5
Step 6
Step 7
Step 8
Remarks
Problems
60. Ratio-to-Moving-Average Forecast Method
Answers to This Chapter’s Questions
Problem
61. Forecasting in the Presence of Special Events
Answers to This Chapter’s Questions
Problems
62. An Introduction to Random Variables
Answers to This Chapter’s Questions
Problems
63. The Binomial, Hypergeometric, and Negative Binomial Random Variables
Answers to This Chapter’s Questions
Problems
64. The Poisson and Exponential Random Variable
Answers to This Chapter’s Questions
Problems
65. The Normal Random Variable
Answers to This Chapter’s Questions
Problems
66. Weibull and Beta Distributions: Modeling Machine Life and Duration of a Project
Answers to This Chapter’s Questions
Problems
67. Making Probability Statements from Forecasts
Answers to This Chapter’s Questions
Problems
68. Using the Lognormal Random Variable to Model Stock Prices
Answers to This Chapter’s Questions
Remarks
Problems
69. Introduction to Monte Carlo Simulation
Answers to This Chapter’s Questions
The Impact of Risk on Our Decision
Confidence Interval for Mean Profit
Problems
70. Calculating an Optimal Bid
Answers to This Chapter’s Questions
Problems
71. Simulating Stock Prices and Asset Allocation Modeling
Answers to This Chapter’s Questions
Problems
72. Fun and Games: Simulating Gambling and Sporting Event Probabilities
Answers to This Chapter’s Questions
Problems
73. Using Resampling to Analyze Data
Answer to This Chapter’s Question
Problems
74. Pricing Stock Options
Answers to This Chapter’s Questions
Problems
75. Determining Customer Value
Answers to This Chapter’s Questions
Problems
76. The Economic Order Quantity Inventory Model
Answers to This Chapter’s Questions
Problems
77. Inventory Modeling with Uncertain Demand
Answers to This Chapter’s Questions
The Back-Order Case
The Lost-Sales Case
Problems
78. Queuing Theory: The Mathematics of Waiting in Line
Answers to This Chapter’s Questions
Problems
79. Estimating a Demand Curve
Answers to This Chapter’s Questions
Problems
80. Pricing Products by Using Tie-Ins
Answer to This Chapter’s Question
Problems
81. Pricing Products by Using Subjectively Determined Demand
Answer to This Chapter’s Questions
Problems
82. Nonlinear Pricing
Answers to This Chapter’s Questions
Problems
83. Array Formulas and Functions
Answers to This Chapter’s Questions
How many units of makeup did Jen sell?
How many units of lipstick did Jen sell?
Can I summarize the number of units of each product sold by each salesperson?
Problems
84. PowerPivot
Answers to This Chapter’s Questions
Problem
A. About the Author
Index
About the Author
← Prev
Back
Next →
← Prev
Back
Next →