Microsoft Excel Fundamentals

CONTENT

1. Basic worksheet modeling
1.1 Answers to this chapter’s questions
1.2 Problems

2. Range names
2.2 How can I create named ranges?
2.3 Answers to this chapter’s questions
2.4 Remarks
2.5 Problems

3.Lookup functions
3.1 Syntax of the lookup functions
3.2 Answers to this chapter’s questions
3.3 Problems

4.The INDEX function
4.1 Syntax of the INDEX function
4.2 Answers to this chapter’s questions
4.3 Problems

5. The MATCH function
5.1 Syntax of the MATCH function
5.2 Answers to this chapter’s questions
5.3 Problems

6. Text functions and Flash Fill
6.1 Text function syntax
6.2 Answers to this chapter’s questions
6.3 Problems

7. Dates and date functions
7.1 Answers to this chapter’s questions
7.2 Problems

8. NPV and XNPV functions
8.1 Answers to this chapter’s questions
8.2 Problems

9. IRR, XIRR, and MIRR functions
9.1 Answers to this chapter’s questions
9.2 Problems

10. More Excel financial functions
10.1 Answers to this chapter’s questions
10.2 Problems

11. Circular references
11.1 Answers to this chapter’s questions
11.2 Problems

12. IF, IFERROR, IFS, CHOOSE, and SWITCH functions
12.1 Answers to this chapter’s questions
12.2 Problems

13. Time and time functions
13.1 Answers to this chapter’s questions
13.2 Problems

14. The Paste Special command
14.1 Answers to this chapter’s questions
14.2 Problems

15. Three-dimensional formulas and hyperlinks
15.1 Answers to this chapter’s questions

16. The auditing tool and the Inquire add-in
16.1 Answers to this chapter’s questions
16.2 Problems

17. Sensitivity analysis with data tables
17.1 Answers to this chapter’s questions
17.2 Problems

18. The Goal Seek command
18.1 Answers to this chapter’s questions
18.2 Problems

19. Using the Scenario Manager for sensitivity analysis
19.1 Answer to this chapter’s question
19.2 Problems

20. The COUNTIF, COUNTIFS, COUNT, COUNTA, and
20.1 Answers to this chapter’s questions
20.2 Problems

21. The SUMIF, AVERAGEIF, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS functions
21.1 Answers to this chapter’s questions
21.2 Problems

22. The OFFSET function
22.1 Answers to this chapter’s questions
22.2 Problems

23. The INDIRECT function
23.1 Answers to this chapter’s questions
23.2 Problems

24 Conditional formatting
24.1 Answers to this chapter’s questions
24.2 Problems

25. Sorting in Excel
Answers to this chapter’s questions
Problems

26. Excel tables and table slicers
Answers to this chapter’s questions
Problems

27. Spin buttons, scrollbars, option buttons, check boxes,
combo boxes, and group list boxes
27.1 Answers to this chapter’s questions
27.2 Problems

28 The analytics revolution
28.1 Answers to this chapter’s questions

29. An introduction to optimization with Excel Solver
29.1 Problems

30. Using Solver to determine the optimal product mix
30.1 Answers to this chapter’s questions
30.2 Problems

31 Using Solver to schedule your workforce
31.1 Answers to this chapter’s question
31.2 Problems

32 Using Solver to solve transportation or distribution
problems
32.1 Answer to this chapter’s question
Problems

33. Using Solver for capital budgeting
33.1 Answer to this chapter’s question
3.2 Problems

34. Using Solver for financial planning
34.1 Answers to this chapter’s questions
34.2 Problems

35. Using Solver to rate sports teams
35.1 Answer to this chapter’s question
35.2 Problems

36. Warehouse location and the GRG Multistart and
Evolutionary Solver engines
36.1 Understanding the GRG Multistart and Evolutionary Solver engines
36.2 Answer to this chapter’s questions
36.3 Problems

37 Penalties and the Evolutionary Solver
37.1 Answers to this chapter’s questions
37.2 Problems

38 The traveling salesperson problem
38.1 Answers to this chapter’s questions
38.2 Problems

39 Importing data from a text file or document
39.1 Answers to this chapter’s question
39.2 Problems

40 Get & Transform
40.1 Answers to this chapter’s questions
40.2 Problems

41 Geography and Stock data types
41.1 Answers to this chapter’s questions
41.2 Remarks
41.3 Problems

42 Validating data
42.1 Answers to this chapter’s questions
42.2 Problems

43 Summarizing data by using histograms and
43.1 Pareto charts
43.2 Answers to this chapter’s questions
43.4 Problems

44. Summarizing data by using descriptive statistics
44.1 Answers to this chapter’s questions
44.2 Problems

45 Using pivot tables and slicers to describe data
45.1 Answers to this chapter’s questions
45.2 Remarks about grouping
45.3 Problems

46 The Data Model
46.1 Answers to this chapter’s questions
46.2 Problems

47 Power Pivot
47.1 Answers to this chapter’s questions
47.2 Problems

48 Filled and 3D Power Maps
48.1 Questions answered in this chapter
48.2 Problems

49 Sparklines
49.1 Answers to this chapter’s questions
49.2 Problems

50 Summarizing data with database statistical functions
50.1 Answers to this chapter’s questions
50.2 Problems

51 Filtering data and removing duplicates
51.1 Answers to this chapter’s questions
51.2 Problems

52 Consolidating data
52.1 Answer to this chapter’s question
52.2 Problems

53 Creating subtotals
53.1 Answers to this chapter’s questions
53.2 Problems

54 Charting tricks
54.1 Answers to this chapter’s questions
54.2 Problems

55 Estimating straight-line relationships
55.1 Answers to this chapter’s questions
55.2 Problems

56 Modeling exponential growth
56.1 Answers to this chapter’s question
56.2 Problems

57 The power curve
57.1 Answer to this chapter’s question
57.2 Problems

58 Using correlations to summarize relationships
58.1 Answer to this chapter’s question
58.2 Problems

59 Introduction to multiple regression
59.1 Answers to this chapter’s questions

60 Incorporating qualitative factors into multiple
regression
60.1 Answers to this chapter’s questions

61 Modeling nonlinearities and interactions
61.1 Answers to this chapter’s questions
61.2 Problems for Chapters 59 through 61

62 Analysis of variance: One-way ANOVA
62.1 Answers to this chapter’s questions
62.2 Problems

63 Randomized blocks and two-way ANOVA
63.1 Answers to this chapter’s questions
63.2 Problems

64 Using moving averages to understand time series
64.1 Problem

65 Winters method and the Forecast Sheet
65.1 Remarks
65.2 Excel’s Forecast Sheet Tool
65.3 Problems

66 Ratio-to-moving-average forecast method
66.1 Answers to this chapter’s questions
66.2 Problem

67 Forecasting in the presence of special events
67.1 Answers to this chapter’s questions
67.2 Problems

68 An introduction to probability
68.1 Answers to this chapter’s questions
68.2 Problems

69 An introduction to random variables
69.1 Answers to this chapter’s questions
69.2 Problems

70 The binomial, hypergeometric, and negative binomial
random variables
70.1 Answers to this chapter’s questions
70.2 Problems

71 The Poisson and exponential random variable
71.1 Answers to this chapter’s questions
71.2 Problems

72 The normal random variable and Z-scores
72.1 Answers to this chapter’s questions
72.2 Problems

73 Weibull and beta distributions: Modeling machine
life and duration of a project
73.1 Answers to this chapter’s questions
73.2 Problems

74 Making probability statements from forecasts
74.1 Answers to this chapter’s questions
74.2 Problems

75 Using the lognormal random variable to model
stock prices
75.1 Answers to this chapter’s questions
75.2 Remarks
75.3 Problems

76 Importing historical stock data into Excel
Answers to this chapter’s questions
Problems

77 Introduction to Monte Carlo simulation
77.1 Answers to this chapter’s questions
77.2 The impact of risk on your decision
77.3 Confidence interval for mean profit
77.4 Problems

78 Calculating an optimal bid
78.1 Answers to this chapter’s questions
78.2 Problems

79 Simulating stock prices and asset-allocation
modeling
79.1 Answers to this chapter’s questions
79.2 Problems

80 Fun and games: Simulating gambling and sporting
event probabilities
80.1 Answers to this chapter’s questions
80.2 Problems

81 Using resampling to analyze data
81.1 Answer to this chapter’s question
81.2 Problems

82 Pricing stock options
82.1 Answers to this chapter’s questions
82.2 Problems

83 Determining customer value
83.1 Answers to this chapter’s questions
83.2 Problems

84 The economic order quantity inventory model
84.1 Answers to this chapter’s questions
84.2 Problems

85 Inventory modeling with uncertain demand
85.1 Answers to this chapter’s questions
85.2 The back-order case
85.3 The lost-sales case
85.4 What does the term 95 percent service level mean?
85.5 Problems

86 Queuing theory: The mathematics of waiting in line
86.1 Answers to this chapter’s questions
86.2 Problems

87 Estimating a demand curve
87.1 Answers to this chapter’s questions
87.2 Problems

88 Pricing products by using tie-ins
88.1 Answer to this chapter’s question
88.2 Problems

89 Pricing products by using subjectively determined
demand
89.1 Answer to this chapter’s questions
89.2 Problems

90 Nonlinear pricing
90.1 Answers to this chapter’s questions
90.2 Problems

91 Array formulas and functions
91.1 Answers to this chapter’s questions
91.2 Problems

92 Recording macros
92.1 Answers to this chapter’s questions
92.2 Problems

93 Advanced sensitivity analysis
93.1 Problems
93.2 Index