A Guide to Microsoft Excel 2002 for Business and Management

by Liengme, Bernard V.

• ISBN13: 9780750656146
• ISBN10: 075065614X
• Edition: 2nd
• Format: Paperback
• Copyright: 2002-04-01
• Publisher: Elsevier Science Ltd

Summary

The essential guide for managers and business students who wish to use Microsoft Excel to its full potential As the industry standard spreadsheet for the analysis and presentation of results, Microsoft Excel is indispensable in the business world. This text provides a practical and straightforward guide to fully utilising the functions of Microsoft Excel, guiding the reader from basic principles through to the more complicated areas such as modelling, the analysis of charts, reporting, and automatic importing of data from the web directly into an Excel workbook. bernard Liengme has written this book specifically to meet the requirements of business students and professionals working with Microsoft Excel. The text is illustrated throughout with screen-shots, as well as a wide variety of examples and case studies based in real-world business contexts, introduced with a minimum of maths, and readily adaptable to workplace situations. The new edition has been brought fully up to date with the new Microsoft Office XP release of Excel 2002 but can be used alongside any previous version of Excel, with new Excel 2002 features clearly indicated throughout.

Table of Contents

Preface	ix
The Microsoft® Excel Window
Objectives	1	(1)
What is Excel?	1	(1)
Versions of Excel	1	(1)
Anatomy of the Workspace	1	(4)
Making Cell Entries	5	(4)
Editing	9	(1)
Select from the Menu	10	(2)
Know Your Tools	12	(1)
Getting Help	13	(1)
The AutoCalculate Feature	14	(1)
Summary	15	(2)
Problems	17	(2)
Formulas and Formats
Objectives	19	(1)
Filling in a Series of Numbers	19	(2)
Entering and Copying a Formula	21	(1)
Formatting the Results	22	(2)
Parentheses and Percentages	24	(3)
More About Formulas	27	(1)
Copy and Paste	28	(2)
Formatting Money	30	(3)
Displayed and Stored Values	33	(2)
Borders, Fonts and Patterns	35	(2)
When Things Go Wrong	37	(2)
Summary	39	(1)
Problems	40	(1)
Cell References and Names
Objectives	41	(1)
Relative References	41	(2)
Absolute References	43	(2)
What-if Analyses	45	(1)
Scenarios	45	(2)
Mixed Cell References	47	(2)
Using Names	49	(4)
Formulas Using Labels	53	(1)
Summary	53	(2)
Problems	55	(2)
Using Functions
Objectives	57	(1)
Introduction to Functions	57	(3)
Using the AutoSum tool	60	(2)
Entering Functions Manually	62	(1)
Insert Function Dialog	62	(2)
Mixed Text and Numeric Values	64	(1)
Rounding and Truncating Functions	65	(1)
Rounding the Interest	66	(2)
Weighed Average Problem	68	(2)
The Basic Financial Functions	70	(4)
Another Savings Plan	74	(1)
A Decision Model	75	(2)
A Loan Amortization	77	(1)
Summary	78	(2)
Problems	80	(1)
The Decision Functions
Objectives	81	(1)
Decision Functions	81	(3)
A What-if Analysis	84	(2)
Nested Ifs	86	(1)
Logical Functions	87	(1)
Table Lookup Functions	88	(2)
Using VLOOKUP	90	(2)
Another VLOOKUP example	92	(2)
Using INDEX and MATCH	94	(1)
Conditional Counting and Summing	95	(4)
Summary	99	(1)
Problems	100	(1)
Printing a Worksheet
Objectives	101	(1)
A Quick Way to Print	101	(1)
Another Way to Print	101	(2)
Page Setup	103	(1)
Changing Margins	104	(1)
Header and Footer	104	(2)
Gridlines and Row/Column Headings	106	(1)
Setting the Print Area	106	(1)
Printing Formulas	106	(1)
Printing a Large Worksheet	107	(2)
Summary	109	(2)
Charts
Objectives	111	(1)
Types of Charts	111	(2)
Anatomy of a Chart	113	(1)
X- and Y-values	114	(1)
Embedded Charts and Chart Sheets	115	(1)
Column Chart	115	(3)
Changing a Chart Size and Position	118	(1)
Modifying a Chart	119	(3)
A Combination Chart with Two Y-axes	122	(2)
Changing the Scale	124	(1)
Changing Axis Crossings	125	(1)
Chart with Error Bars	126	(1)
Blank Cells in the Y Range	126	(1)
Exploding a Pie Chart	127	(1)
Selecting Non-adjacent Data	127	(1)
Annotating a Chart	128	(1)
Using Pictures as Markers or Columns	129	(3)
Data Added Automatically	132	(1)
Adding a New Data Series	132	(1)
Drawing Lines	133	(1)
Gantt Charts	134	(2)
Summary	136	(1)
Problems	137	(2)
Modelling
Objectives	139	(1)
Replaying a Loan	139	(2)
Whose Rule?	141	(4)
Depreciation Models	145	(5)
To Buy Or Not To Buy?	150	(3)
Cost of Inventory: FIFO and LIFO	153	(5)
Problems	158	(3)
Goal Seek and Solver
Objectives	161	(1)
Goal Seeking	161	(2)
Another Goal	163	(1)
Goal Seek and Charts	164	(1)
Introducing Solver	165	(2)
Finding a Maximum	167	(1)
Using Constraints	168	(3)
Linear Programming	171	(2)
A More Complex Problem	173	(2)
Options in Solver	175	(1)
Summary	176	(1)
Problems	177	(2)
Working with Lists
Objectives	179	(1)
Sorting a List	179	(3)
The FREQUENCY function	182	(2)
The Histogram Tool	184	(2)
Generating Data	186	(2)
Pivot Tables	188	(4)
Filtering Lists	192	(2)
Importing and Exporting	194	(4)
Summary	198	(1)
Problems	199	(2)
Dates and Times
Objectives	201	(1)
Introduction	201	(1)
Entering and Formatting Dates	202	(2)
Simple Date Calculations	204	(2)
What Day/Date/Time is it?	206	(2)
The Standard Date Functions	208	(1)
Date for the Next Meeting?	209	(2)
Other Date Functions	211	(1)
When in Rome...	212	(1)
The DATEDIF Function	212	(1)
Time on Your Hands	213	(3)
Time Allocation	216	(1)
Summary	217	(1)
Problems	218	(1)
Report Writing
Objectives	219	(1)
Copy and Paste	219	(1)
Copy and Paste Special	220	(1)
A Picture is Worth a Thousand Words	221	(1)
Copying a Chart	222	(1)
Object Linking and Embedding	223	(1)
Embedding and Linking	224	(1)
Creating an Equation	225	(2)
Putting Microsoft Excel on a Web Page	227	(2)
Summary	229	(2)
Appendix A Microsoft Excel Add-Ins	231	(2)
Appendix B Answers to Problems	233	(4)
Index	237