Microsoft Excel 2013 · Data Analysis and Business Modeling by Winston, Wayne L. -- Read -- Imperial Library of Trantor

Index

Microsoft Excel 2013: Data Analysis and Business Modeling Introduction

What’s new in this edition What you should know before reading this book How to use this book Using the companion content Acknowledgments Support & feedback

Errata We want to hear from you Stay in touch

1. Range names

How can I create named ranges?

Using the Name box to create a range name Creating named ranges by using Create From Selection Creating range names by using Define Name Name Manager

Answers to this chapter’s questions Remarks Problems

2. Lookup functions

Syntax of the lookup functions

VLOOKUP syntax HLOOKUP syntax

Answers to this chapter’s questions Problems

3. INDEX function

Syntax of the INDEX function Answers to this chapter’s questions Problems

4. MATCH function

Answers to this chapter’s questions Problems

5. 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, LOWER, and PROPER functions The CHAR function The CLEAN Function The SUBSTITUTE FUNCTION

Answers to this chapter’s questions

Extracting data by using the Convert Text To Columns Wizard

Problems

6. Dates and date functions

Answers to this chapter’s questions Problems

7. Evaluating investments by using net present value criteria

Answers to this chapter’s questions Problems

8. Internal rate of return

Answers to this chapter’s questions Problems

9. More Excel financial functions

Answers to this chapter’s questions CUMPRINC and CUMIPMT functions Problems

10. Circular references

Answers to this chapter’s questions Problems

11. IF statements

Answers to this chapter’s questions Problems

12. Time and time functions

Answers to this chapter’s questions Problems

13. The Paste Special command

Answers to this chapter’s questions Problems

14. Three-dimensional formulas

Answer to this chapter’s question Problem

15. The Auditing tool and Inquire add-in

Answers to this chapter’s questions Problems

16. Sensitivity analysis with data tables

Answers to this chapter’s questions Problems

17. The Goal Seek command

Answers to this chapter’s questions Problems

18. Using the Scenario Manager for sensitivity analysis

Answer to this chapter’s question Remarks Problems

19. The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK functions

Answers to this chapter’s questions Remarks Problems

20. The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS functions

Answers to this chapter’s questions Problems

21. The OFFSET function

Answers to this chapter’s questions Remarks Problems

22. The INDIRECT function

Answers to this chapter’s questions Problems

23. Conditional formatting

Answers to this chapter’s questions Problems

24. Sorting in Excel

Answers to this chapter’s questions Problems

25. Tables

Answers to this chapter’s questions Problems

26. Spinner buttons, scroll bars, option buttons, check boxes, combo boxes, and group list boxes

Answers to this chapter’s questions Problems

27. The analytics revolution

Answers to this chapter’s questions

28. Introducing 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

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

Answers 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

Answer to this chapter’s question Problems

39. Importing data from the Internet

Answer to this chapter’s question 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. The Data Model

Answers to this chapter’s questions Problems

45. PowerPivot

Answers to this chapter’s questions Problems

46. Power View

Answers to this chapter’s questions Problems

47. Sparklines

Answers to this chapter’s questions Problems

48. Summarizing data with database statistical functions

Answers to this chapter’s questions Problems

49. Filtering data and removing duplicates

Answers to this chapter’s questions Problems

50. Consolidating data

Answer to this chapter’s question Problems

51. Creating subtotals

Answers to this chapter’s questions Problems

52. Charting tricks

Answers to this chapter’s questions Problems

53. Estimating straight-line relationships

Answers to this chapter’s questions Problems

54. Modeling exponential growth

Answer to this chapter’s question Problems

55. The power curve

Answer to this chapter’s question Problems

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

57. Introduction to multiple regression

Answers to this chapter’s questions

58. Incorporating qualitative factors into multiple regression

Answers to this chapter’s questions

59. Modeling nonlinearities and interactions

Answers to this chapter’s questions Problems for Chapters 57 and 58

60. Analysis of variance: one-way ANOVA

Answers to this chapter’s questions Problems

61. Randomized blocks and two-way ANOVA

Answers to this chapter’s questions Problems

62. Using moving averages to understand time series

Answer to this chapter’s question Problem

63. Winters’s method

Time series characteristics Parameter definitions Initializing Winters’s method Estimating the smoothing constants Remarks Problems

64. Ratio-to-moving-average forecast method

Answers to this chapter’s questions Problem

65. Forecasting in the presence of special events

Answers to this chapter’s questions Problems

66. An introduction to random variables

Answers to this chapter’s questions Problems

67. The binomial, hypergeometric, and negative binomial random variables

Answers to this chapter’s questions Problems

68. The Poisson and exponential random variable

Answers to this chapter’s questions Problems

69. The normal random variable

Answers to this chapter’s questions Problems

70. Weibull and beta distributions: modeling machine life and duration of a project

Answers to this chapter’s questions Problems

71. Making probability statements from forecasts

Answers to this chapter’s questions Problems

72. Using the lognormal random variable to model stock prices

Answers to this chapter’s questions Remarks Problems

73. Introduction to Monte Carlo simulation

Answers to this chapter’s questions

The impact of risk on your decision Confidence interval for mean profit

Problems

74. Calculating an optimal bid

Answers to this chapter’s questions Problems

75. Simulating stock prices and asset allocation modeling

Answers to this chapter’s questions Problems

76. Fun and games: simulating gambling and sporting event probabilities

Answers to this chapter’s questions Problems

77. Using resampling to analyze data

Answer to this chapter’s question Problems

78. Pricing stock options

Answers to this chapter’s questions Problems

79. Determining customer value

Answers to this chapter’s questions Problems

80. The economic order quantity inventory model

Answers to this chapter’s questions Problems

81. Inventory modeling with uncertain demand

Answers to this chapter’s questions Problems

82. Queuing theory: the mathematics of waiting in line

Answers to this chapter’s questions Problems

83. Estimating a demand curve

Answers to this chapter’s questions Problems

84. Pricing products by using tie-ins

Answer to this chapter’s question Problems

85. Pricing products by using subjectively determined demand

Answers to this chapter’s questions Problems

86. Nonlinear pricing

Answers to this chapter’s questions Problems

87. Array formulas and functions

Answers to this chapter’s questions Problems

A. About the Author Index About the Author Copyright

← Prev
Back
Next →

← Prev
Back
Next →