I n s t r u c t o r ’ s M a n u a l
Projects for Microsoft Excel 2000 MICROSOFT CERTIFIED EDITION
Philip A. Koneman, Ph.D.
Prentice Hall
Projects for MicrosoftExcel 2000
Instructor’s Manual
Copyright © 2000 by Prentice Hall
Excel 2000 is a registered trademark of Microsoft. Windows is a trademark of Microsoft. Other copyrights and trademarks referred to or mentioned in this text are the property of their respective owners.
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or any other media embodiments now known or hereafter to become known, without the prior written permission of the publisher. Adopters of the textbook for which this manual was prepared are hereby given permission to reproduce selected sections and artwork from this manual for use as a classroom aid or illustration.
ISBN: 0-201-45899-3
Prentice Hall
Excel 2000- 1
Overview
Study Questions
These questions can be assigned as homework or as a classroom quiz.
Multiple Choice Answers
1-c 2-d 3-b 4-d 5-d
Short Answer Answers
1.The active cell.
2.By its cell address.
3.The horizontal dimension.
4.Text entries define the structure.
5.A formula always begins with an equals sign (=).
Fill in the Blank Answers
1.Function
2.Cells
3.Worksheet
4.“What If”
5.Text, numbers, dates (a subset of numbers), formulas, and functions
For Discussion Responses
1. A worksheet is the two-dimensional grid consisting of rows and columns that contains data. A workbook is a collection of worksheets.
2.Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure.
Project 1: Designing Worksheet and Workbooks
Study Questions
These questions can be assigned as homework or as a classroom quiz.
Multiple Choice Answers
1-c 2-d 3-c 4-d 5-c 6-d 7-c 8-a 9-3 10-b
Short Answer Answers
1.Text.
2.A1.
3.=SUM(A1:A5)
4.Book1.xls
5.Over 200.
6.Labels.
7.=Financial
8.One or more arguments
9.Preview it and check the spelling.
10.Save your changes.
Fill in the Blank Answers
1.Function
2.Parentheses
3.One or more arguments
4.Save As
5.AVERAGE
6.Fill Handle
7.Formula Palette
8.An Equals sign
9.Functions
10.Home Cell
For Discussion Responses
1.Functions make formulas less prone to error, and often contain sophisticated features to quickly perform calculations.
2.Formulas do not necessarily contain functions; a function is always contained in a formula.
3.Text refers to alphanumeric data; numbers are the values upon which calculations are performed; formulas are expressions beginning with an equals sign and often including functions that return the result of a calculation.
4.You can use the Fill Handle to simply, quickly, and accurately replicate a series or a formula.
5.You can use keystrokes, the mouse, or “Go to” to move to a cell by its address.
Project 2: Modifying Worksheets and Workbooks.
Study Questions
These questions can be assigned as homework or as a classroom quiz.
Multiple Choice Answers
1-d 2-d 3-b 4-b 5-c 6-c 7-a 8-a 9-c 10-d
Short Answer Answers
1.You are editing the worksheet contents.
2.Dragging the worksheet tab to a new location.
3.The Exclamation point (!).
4.The formula references one or more cells on a different worksheet.
5.No, once it is deleted from the workbook, it cannot be restored.
6.Click the cell, type a new entry, and press ENTER.
7.You cannot retrieve it, but when you open the file, it will contain data as of the last save operation.
8.Paste Special, Paste Link.
9.A link is a reference to data appearing in another worksheet.
10.Double-click the tab, type the new name, and press ENTER.
Fill In The Blank Answers
1.linking formula
2.exclamation point
3.double-clicking
4.worksheet
5.range
6.adjacent
7.non-adjacent
8.CTRL
9.SHIFT
10.dragging
For Discussion Responses
1.A non-adjacent selection includes cells that are not contiguous.
2.Editing refers to changing the actual content of a cell; formatting is changing how cell data is displayed without changing its content.
3.You may want to select multiple worksheets when editing so the changes will appear in each selected sheet.
4.Since a link exists, the data is updated automatically.
5.Linking formulas contain one or more references to cells appearing in another worksheet.
Project 3: Formatting Worksheets and Workbooks
Study Questions
These questions can be assigned as homework or as a classroom quiz.
Multiple Choice Answers
1-b 2-d 3-c 4-d 5-d 6-b 7-c 8-d 9-c 10-c
Short Answer Answers
1Web Page Preview.
2.Create a style, and then apply the style to additional worksheet ranges.
3.Cell borders.
4.Financial data usually is formatted using either currency or accounting format.
5.Cell alignment.
6.Percentage format.
7.Use the Insert name to add columns or rows to a worksheet.
8.Borders appear around cells; shading impacts the entire cell.
9.By selecting the desired color using the Font Color button on the Formatting Toolbar.
10.Apply formats, choose Style from the Format menu, and name the style.
Fill In The Blank Answers
1.border
2.number
3.style
4.style
5.Format Painter
6.Insert
7.font
8.Web Page Preview
9.cell alignment
10.shading
For Discussion Responses
1.A style is a saved set of cell formats. Once you have created and named a style, you can apply it to other cells or ranges.
2.To see a list of font formats, click Format, Cells, and click the Font tab. Three of the options you will see are Font, Font Style, and Font Size.
3.When you insert a column into a worksheet, you are modifying the structure of the selected sheet(s). When you insert worksheets into a workbook, you are modifying the book’s structure.
4.Excel’s Web Page Preview is a feature that allows you to see exactly how your workbook will appear in a Web Browser when it is saved as HTML. Print Preview displays a worksheet as it will appear when printed.
5.Three common number formats include Currency, Accounting, and Percent.
Project 4: Creating More Complex Workbooks
Summary and Exercises
Study Questions
These questions can be assigned as homework or as a classroom quiz.
Multiple Choice Answers
1-b 2-c 3-a 4-c 5-d 6-2 7-c 8-a 9-a 10-b
Short Answer Answers
1.The IF function conducts a logical test and returns a value.
2.Click File, Page Setup. Select the Page tab, and click the Landscape or Portrait option.
3.IF is a logical function.
4.The IF function conducts a logical test of the value in a cell and returns one of two values you specify.
5.Highlight the desired range; click File, Print Area, Set.
6.Calculates the arithmetic mean for a range of values.
7.Remove the print area.
8.Click the Insert menu and select Name, Define.
9.You can use range names in formulas by substituting the range name for the cell or range reference.
10.The AVERAGE function is a statistical function.
Fill In The Blank Answers
1.IF
2.File
3.IF
4.Statistical
5.Logical
6.range names
7.header
8.Insert menu
9.Print Area, File
10.Print Area
For Discussion Responses
1.Yes, since range names can appear in place of any valid cell reference in Excel formulas.
2.Setting print areas prior to printing a worksheet ensures that only the portion you want to print will actually print.
3.When you want to calculate these values, which is often useful information to accompany totals.
4.The IF functions is useful whenever you want to return one of two responses based upon the value of a cell. For instance, in a worksheet summarizing sales data, you may want to compare the profit/loss value to zero, and return a text string consisting of the words “Profit” or “Loss,” depending upon the results of the logical test.
5.The MIN statistical functions returns the minimum value in a range, the MAX function returns the maximum value in a range.
Project 5: Analyzing and Distributing Worksheet Data
Summary and Exercises
Study Questions
These questions can be assigned as homework or as a classroom quiz.
Multiple Choice Answers
1-a 2-b 3-c 4-c 5-d 6-d 7-a 8-c 9-b 10-d
Short Answer Answers
1.The bar chart is most like the column chart; they differ only in orientation.
2.One.
3.The X-Y scatter chart displays correlations between two data series.
4.The line chart, as with a stock’s performance.
5.A chart is a graphic representation of worksheet data that makes it easy to interpret the relationships between or among values.
6.This depends upon how that chart was created. If a chart was inserted as a new worksheet ply, select the sheet and delete it using the Edit menu. If that chart was added to a worksheet, select the chart as a graphic and delete or cut it.
7.You can preview a chart as a worksheet ply the same way you preview any worksheet.
8.Click Insert, Picture, From File from the menu.
9.The Chart Wizard.
10.A web browser.
Fill In The Blank Answers
1.Pie
2.As a New Sheet
3.data series
4.Pie
5.X-Y scatter
6.embedded
7.Insert
8.File
9.Column, bar
10.Publish
For Discussion Responses
1.Charts are useful for conveying numeric relationships because they depict these relationships graphically and are thus easily understood and interpreted.
2.Pie chart, for representing the parts of a whole, such as the total value of one’s investment portfolio. Line chart, for displaying trends over time, such as the value of a particular stock. Column chart, to compare one or more series, such as the performance of 5 mutual funds for the current year.
3.When you insert a chart as a separate worksheet, it appears on its own worksheet ply. When you embed a chart, it appears as a graphic object in the current worksheet.
4.Graphics often enhance the appearance of worksheets.
5.When you want multiple users to interact with worksheet data independent of the worksheet itself.
Project 6: Using Financial Functions
Study Questions
Multiple Choice Answers
1-a2-b3-d4-c5-e6-d7-c8-d9-d10-c
Short Answer Answers
1.J7, identified by the minus sign preceding it.
2.The longer the term, the greater the monthly payments and the total amount paid out over the repayment schedule.
3.The periodic principal payment of an annuity.
4.Where only the row or the column reference is preceded by a dollar sign.
5.Because those formulas must always reference the principal, rate, and term values.
6.5
7.The periodic principal payment.
8.Freeze one or more of the worksheet panes.
9.A dollar sign ($).
10. As a negative value, and thus proceeded with a minus sign.
Fill In The Blank Answers
1.IPMT
2.Annuity
3.Absolute
4.4
5.amortized
6.Present Value
7.Outstanding interest
8.Freeze
9.term
10.PV (Present Value)
For Discussion Responses
1.The FV function returns the future value of an annuity, where the PV function returns the present value of an annuity.
2.A macro is a method for storing a series of steps applied to a worksheet, so the same steps can be applied to similar worksheets. To record a macro, use the Tools menu. Once you have recorded a macro you can play it also using the Tools menu.
3.Individual cells or ranges can be locked, and the worksheet protected. Search for the keyword protecting in the Excel Help system to see exactly how worksheets and workbooks can be protected from changes.
4.The PMT function requires the Rate, Period, and Present value, in that order.