# REVIEW

## REVIEW

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. REVIEW Midterm Review ICS 101 Fall 2004

2. Inserting a Function 0 Description

3. The Function Wizard 0 Enter arguments into text boxes Collapse button shrinks dialog box if necessary Value returned by the function (answer) is displayed

4. PMT function 0 PMT(rate,nper,pv,fv,type) • Rateis the interest rate for the loan. If you make monthly payments on a four-year loan at an annual interest rate of 10 percent, use 10%/12 for rate If you make a payment every 2 months then divide by 6 since there will be 6 payments in a year.

5. PMT function 0 PMT(rate,nper,pv,fv,type) • Nper   is the total number of payments for the loan. If your loan expands for 4 years, then use 4*12 for nper. This way you will be making 48 payments during the duration of your loan. If you were to make a payment per year then 4 will be used as Nper.

6. PMT function 0 PMT(rate,nper,pv,fv,type) • Pv   is the present value, or the total amount that a series of future payments is worth now; also known as the principal. This is the amount that you want financing for. Write it down as negative because you owe it.

7. PMT function 0 PMT(rate,nper,pv,fv,type) • Fv   is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

8. PMT function 0 PMT(rate,nper,pv,fv,type) • Type   is the number 0 (zero) or 1 and indicates when payments are due. • 0 payments are due at the end of the period • 1 payments are due at the beginning of the period.

9. 0 = PMT(rate,nper,pv,fv,type)

10. The FV function 0 • Returns the future value of a series of payments • For example, contributions to your 401K or IRA • Based on: • Number of periods • Expected rate of return • Amount invested each period

11. Using the FV Function 0 • Rate divided by number of periods • Number of payments in all periods • Payments per year (must be negative) Amount of contribution, rate of return, and years contributing are all expressed as assumptions

12. Using the FV Function 0 \$598,905.34

13. The Goal Seek Command 0 • Allows you to set an end result and vary the inputs (assumptions) to produce that result • Only one input can be varied at a time • All other assumptions remain constant • For example, set a desired monthly car payment • Vary the amount financed • Interest rate and number of months remain the same

14. Using the Goal Seek Command 0 Enter the cell containing the desired result Enter the desired value Enter the cell containing the desired result Only one value can change at a time

15. The IF Function 0 • Enables decision making in a worksheet • Requires three arguments: • A condition • A value if the condition is true • A value if the condition is false • Condition must be able to be evaluated as true or false • Uses relational operators (=, <, etc.)

16. Using the IF Function Incorrectly 0 Value_if_true entered as a conditional test. Function will return True or False

17. The VLOOKUP function 0 • Allows Excel to look up a value in a table and return a related value • Requires three arguments: • the numeric value (or cell) to look up • the range of the table • the column number containing the value you want to return

18. VLOOKUP 0 Breakpoints are Sorted

19. Using the VLOOKUP Function 0 Look up the value found in cell I4, in this case, the semester average This argument tells the function where to look. Absolute references used for the table Look in the second column of the table, NOT in column J

20. Using the AutoFilter Command Click the dropdown on the Homework column, then select Poor as the criteria

21. Auto Filter, custom

22. More Functions • HLOOKUP • COUNT • COUNTA

23. Opening Additional Windows 0 • New Window command (Window menu) opens a new window

24. Copying to Another Workbook 0 Workbook name is displayed in the title bar. Active window is highlighted Select Sheet1 in Atlanta workbook, hold down the Ctrl key and drag to Summary workbook

25. Check to Copy To Move or to Copy 0 Move sheet to… In same workbook Move to different workbook

26. Tabs 0 Use these arrows to see the tabs Right click on the tab, Then select what you want to do You can change the tab order by dragging

27. Worksheet References 0 • Reference cells in other worksheets in the same workbook • Requires the name of the worksheet before the cell range • Exclamation mark separates worksheet and cell reference • =Atlanta!B3 +Boston!B3+Chicago!B3 • Worksheet reference is absolute • Cell reference canbeabsolute, relative, or mixed • Can be entered by pointing

28. Using Worksheet References 0 Referenced worksheet is an absolute reference Exclamation mark separates worksheet reference from cell reference

29. To add references to cells from several worksheets 0 This works to add different cells for each worksheet • <Shift> and select the tab by pointing • Then you can release the shift • Go to the tab and point the cells that you need to use

30. Shift Grouping Worksheets 0

31. Grouped 0 Must make changes in the worksheet that has this

32. AutoFormat Command 0 • Applies a predefined format to selected cells • First select the cells to format… selection

33. AutoFormat Command 0 • Then, select the AutoFormat.

34. Formatting the Documentation Worksheet 0 Worksheets are listed and described Title is merged and centered. Font is bold, with an offsetting background Wrap text in this cell, in much the way a word processor wraps text

35. An External Reference 0 External reference surrounded by brackets Active cell contains references to cells in other workbooks

36. Create a Workspace 0 Four open workbooks Save as a Workspace Opening the workspace will open all four workbooks and tile them

37. Inserting Comments 0 Comments provide explanation for values and/or descriptions of formulas