Guide to Microsoft Excel 2002 for Business and Management (eBook)

Cover 1
Contents 2
Preface 6
1 The Microsoft Excel Window 8
Objectives 8
What is Excel? 8
Versions of Excel 8
Exercise 1: Anatomy of the Workspace 8
Exercise 2: Making Cell Entries 12
Exercise 3: Editing 16
Exercise 4: Select from the Menu 17
Exercise 5: Know Your Tools 19
Exercise 6: Getting Help 20
Exercise 7: The AutoCalculate Feature 21
Summary 22
Problems 24
2 Formulas and Formats 26
Objectives 26
Exercise 1: Filling in a Series of Numbers 26
Exercise 2: Entering and Copying a Formula 28
Exercise 3: Formatting the Results 29
Exercise 4: Parentheses and Percentages 31
Exercise 5: More About Formulas 34
Exercise 6: Copy and Paste 35
Exercise 7: Formatting Money 37
Exercise 8: Displayed and Stored Values 40
Exercise 9: Borders, Fonts and Patterns 42
Exercise 10: When Things Go Wrong 44
Summary 46
Problems 47
3 Cell References and Names 48
Objectives 48
Exercise 1: Relative References 48
Exercise 2: Absolute References 50
Exercise 3: What-if Analyses 52
Exercise 4: Scenarios 52
Exercise 5: Mixed Cell References 54
Exercise 6: Using Names 56
Formulas Using Labels 60
Summary 60
Problems 62
4 Using Functions 64
Objectives 64
Introduction to Functions 64
Exercise 1: Using the AutoSum tool 67
Exercise 2: Entering Functions Manually 69
Exercise 3: Insert Function Dialog 69
Exercise 4: Mixed Text and Numeric Values 71
Exercise 5: Rounding and Truncating Functions 72
Exercise 6: Rounding the Interest 73
Exercise 7: Weighed Average Problem 75
The Basic Financial Functions 76
Exercise 8: Another Savings Plan 80
Exercise 9: A Decision Model 82
Exercise 10: A Loan Amortization 84
Summary 85
Problems 87
5 The Decision Functions 88
Objectives 88
Decision Functions 88
Exercise 1: A What-if Analysis 91
Exercise 2: Nested Ifs 93
Exercise 3: Logical Functions 94
Table Lookup Functions 95
Exercise 4: Using VLOOKUP 97
Exercise 5: Another VLOOKUP example 99
Exercise 6: Using INDEX and MATCH 101
Exercise 7: Conditional Counting and Summing 102
Summary 106
Problems 107
6 Printing a Worksheet 108
Objectives 108
Exercise 1: A Quick Way to Print 108
Exercise 2: Another Way to Print 108
Exercise 3: Page Setup 110
Exercise 4: Changing Margins 111
Exercise 5: Header and Footer 111
Exercise 6: Gridlines and Row/ Column Headings 113
Exercise 7: Setting the Print Area 113
Exercise 8: Printing Formulas 113
Exercise 9: Printing a Large Worksheet 114
Summary 116
7 Charts 118
Objectives 118
Types of Charts 118
Anatomy of a Chart 120
X- and Y-values 121
Embedded Charts and Chart Sheets 122
Exercise 1: Column Chart 122
Exercise 2: Changing a Chart Size and Position 125
Exercise 3: Modifying a Chart 126
Exercise 4: A Combination Chart with Two Y- axes 129
Exercise 5: Changing the Scale 131
Exercise 6: Changing Axis Crossings 132
Exercise 7: Chart with Error Bars 132
Exercise 8: Blank Cells in the Y Range 133
Exercise 9: Exploding a Pie Chart 134
Exercise 10: Selecting Non-adjacent Data 134
Exercise 11: Annotating a Chart 135
Exercise 12: Using Pictures as Markers or Columns 136
Exercise 13: Data Added Automatically 139
Exercise 14: Adding a New Data Series 139
Exercise 15: Drawing Lines 140
Exercise 16: Gantt Charts 141
Summary 143
Problems 144
8 Modelling 146
Objectives 146
Exercise 1: Repaying a Loan 146
Exercise 2: Whose Rule? 148
Exercise 3: Depreciation Models 152
Exercise 4: To Buy Or Not To Buy? 157
Exercise 5: Cost of Inventory: FIFO and LIFO 160
Problems 165
9 Goal Seek and Solver 168
Objectives 168
Exercise 1: Goal Seeking 168
Exercise 2: Another Goal 171
Exercise 3: Goal Seek and Charts 171
Exercise 4: Introducing Solver 172
Exercise 5: Finding a Maximum 174
Exercise 6: Using Constraints 175
Exercise 7: Linear Programming 178
Exercise 8: A More Complex Problem 180
Options in Solver 182
Summary 183
Problems 184
10 Working with Lists 186
Objectives 186
Exercise 1: Sorting a List 186
Exercise 2: The FREQUENCY function 189
Exercise 3: The Histogram Tool 191
Exercise 4: Generating Data 193
Exercise 5: Pivot Tables 195
Exercise 6: Filtering Lists 199
Exercise 7: Importing and Exporting 201
Summary 205
Problems 206
11 Dates and Times 208
Objectives 208
Introduction 208
Exercise 1: Entering and Formatting Dates 209
Exercise 2: Simple Date Calculations 211
Exercise 3: What Day/ Date/ Time is it? 213
Exercise 4: The Standard Date Functions 215
Exercise 5: Date for the Next Meeting? 216
Exercise 6: Other Date Functions 218
When in Rome ƒ 219
Exercise 7: The DATEDIF Function 219
Exercise 8: Time on Your Hands 220
Exercise 9: Time Allocation 222
Summary 223
Problems 225
12 Report Writing 226
Objectives 226
Exercise 1: Copy and Paste 226
Exercise 2: Copy and Paste Special 227
Exercise 3: A Picture is Worth a Thousand Words 228
Exercise 4: Copying a Chart 229
Exercise 5: Object Linking and Embedding 230
Exercise 6: Embedding and Linking 231
Exercise 7: Creating an Equation 232
Exercise 8: Putting Microsoft Excel on a Web Page 234
Summary 236
Appendix A Microsoft Excel Add-Ins 238
Appendix B Answers to Problems 240
Chapter 2 240
Chapter 3 240
Chapter 4 240
Chapter 5 241
Chapter 8 241
Chapter 9 242
Chapter 10 242
Chapter 11 242
Index 244