Excel Project 4

1
Excel Project 4

• Financial Functions, Data Tables, Amortization
  Schedules, and Hyperlinks

2
Objectives

• Control the color and thickness of outlines and
  borders
  
• Assign a name to a cell and refer to the cell in
  a formula using the assigned name
  
• Determine the monthly payment of a loan using the
  financial function PMT
  
• Use the financial functions PV (present value)
  and FV (future value)
  
• Create a data table to analyze data in a worksheet

3
Objectives

• Add a pointer to a data table
• Create an amortization schedule
• Analyze worksheet data by changing values
• Add a hyperlink to a worksheet element
• Use names and the Set Print Area command to print
  selections of a worksheet

4
Objectives

• Set print options
• Protect and unprotect cells in a worksheet
• Use the formula checking features of Excel
• Hide and unhide cell gridlines, rows, columns,
  sheets, and wokbooks

5
Starting and Customizing Excel

• Start Microsoft Office Excel 2003 and create a
  new worksheet using the instructions on pages EX
  245 through EX 247

6
(No Transcript)
7
Adding Custom Borders

• Select the range B2E6 and then right-click
• Click Format Cells on the shortcut menu
• When Excel displays the Format Cells dialog box,
  click the Border tab
  
• Click the Color box arrow, click Blue on the
  Color palette, and then click the medium line
  style in the Style box
  
• Click the Outline button in the Presets area

8
Adding Custom Borders

• Click the Color box arrow and then click
  Automatic on the Color palette
  
• Click the light border in the Style box and then
  click the Vertical Line button in the Border
  area
  
• Click the Patterns tab and then click Tan on the
  Color palette
  
• Click the OK button and then select cell B8 to
  deselect the range B2E6

9
Adding Custom Borders
10
Creating Names Based on Row Titles

• Select the range B4C6
• Click Insert on the menu bar and then point to
  Name on the Insert menu
  
• Click Create on the Name submenu
• Click the OK button

11
Creating Names Based on Row Titles

• Select the range D2E6, click Insert on the menu
  bar, and then point to Name on the Insert menu
  
• Click Create on the Name submenu and then click
  the OK button in the Create Names dialog box
  
• Select cell B8 to deselect the range D2E6 and
  then click the Name box arrow in the formula bar
  to view the names created

12
Creating Names Based on Row Titles
13
Entering the Loan Amount Formula Using Names

• Select cell C6
• Type (equal sign), click cell C4, type - (minus
  sign), and then click cell C5
  
• Click the Enter box

14
Entering the PMT Function

• Select cell E4. Type -pmt(rate / 12, 12 years,
  loan_amt as the function
  
• Click the Enter box in the formula bar

15
Financial Functions
16
Defining a Range as a Data Table

• Use the instructions on pages EX 258 through EX
  260 to enter the values and formulas to be used
  in the data table
  
• Select the range B9E20
• Click Data on the menu bar

17
Defining a Range as a Data Table

• Click Table on the Data menu
• When Excel displays the Table dialog box, click
  the Column input cell box, and then click cell E2
  in the Loan Payment Calculator section
  
• Click the OK button

18
Defining a Range as a Data Table
19
Adding a Pointer to the Data Table

• Select the range B10B20
• Click Format on the menu bar
• Click Conditional Formatting on the Format menu
• When Excel displays the Conditional Formatting
  dialog box, if necessary, click the Cell Value Is
  in the left list and then click equal to in the
  middle list

20
Adding a Pointer to the Data Table

• Type E2 in the right box
• Click the Format button, click the Patterns tab,
  and then click Orange on the Color palette
  
• Click the Font tab, click the Color box arrow,
  and then click White on the Color palette
  
• Click Bold in the Font Style list. Click the OK
  button in the Format Cells dialog box

21
Adding a Pointer to the Data Table

• Click the OK button in the Conditional Formatting
  dialog box. Click cell G20 to deselect the range
  B10B21
  
• Select cell E2 and then enter 7.75 as the
  interest rate
  
• Enter 6.25 in cell E2 to return the Loan Payment
  Calculator section and Interest Rate Schedule
  section to their original states

22
Adding a Pointer to the Data Table
23
Entering the Formulas in an Amortization Schedule

• Format the worksheet and enter the values as
  instructed in page EX 267
  
• Select cell H3 and then enter c6 as the
  beginning balance of the loan
  
• Select cell I3 and then type if(g3 pv(e2 / 12. 12 (e3 g3), -e4), 0) as the
  entry
  
• Click the Enter box in the formula bar

24
Entering the Formulas in an Amortization Schedule

• Select cell J3. Type h3 i3 and then press the
  RIGHT ARROW key
  
• Type if(h3 0, 12 e4 j3, 0) in cell K3
• Click the Enter box in the formula bar
• Copy the formulas to fill the amortization
  schedule

25
Entering the Total Formulas in the Amortization
Schedule

• Select cell I18. Enter Subtotal as the row
  title. Select the range J18K18. Click the
  AutoSum button on the Standard toolbar
  
• Select cell I19. Type Down Pymt as the row
  title. Select cell K19 and then enter c5 as the
  down payment
  
• Select cell I20. Type Total Cost as the row
  title. Select cell K20, type j18 k18 k19 as
  the total cost, and then click the Enter box in
  the formula bar

26
Entering the Total Formulas in the Amortization
Schedule
27
Assigning a Hyperlink to an Embedded Graphic

• With the graphic selected, right-click it
• Click Hyperlink on the shortcut menu
• Make sure the data disk is in drive A
• When Excel displays the Insert Hyperlink dialog
  box, click the Look in box arrow, click 3½ Floppy
  (A) in the Look in list, and then click the file
  name e-Money Lenders Statement of Condition

28
Assigning a Hyperlink to an Embedded Graphic

• Click the OK button
• If a Microsoft Office Excel dialog box appears,
  click the yes button
  
• Select cell J19 to deselect the graphic
• Click the Save button on the Standard toolbar to
  save the workbook using the file name E-Money
  Lenders Loan Payment Calculator

29
Assigning a Hyperlink to an Embedded Graphic
30
Setting the Print Area

• Select the range B1E6, click File on the menu
  bar, and then point to Print Area
  
• Click Set Print Area on the Print Area submenu
• Click the Print button on the Standard toolbar
• Click Clear Print Area on the Print Area submenu
  to reset the print area to the enter worksheet

31
Naming and Printing Sections of a Worksheet

• Select the range B1E6, click the Name box, and
  then type Loan_Payment as the name of the range
  
• Press the ENTER key
• Select the range B7E20, click the Name box, type
  Interest_Schedule as the name of the range, and
  then press the ENTER key
  
• Select the range G1K20, click the Name box, type
  Amortization_Schedule as the name of the range,
  and then press the ENTER key
  
• Select the range B1K20, click the Name box, type
  All_Sections as the name of the range, and then
  press the ENTER key

32
Naming and Printing Sections of a Worksheet

• Select any cell on the worksheet and then click
  the Name box arrow in the formula bar
  
• Click Loan_Payment in the Name list
• Click File on the menu bar and then click Print
• When Excel displays the Print dialog box, click
  Selection in the Print what area

33
Naming and Printing Sections of a Worksheet

• Click the OK button
• One at a time, use the Name box to select the
  names Interest_Schedule, Amortization_Schedule,
  and All_Sections and then print them following
  the final two instructions on the previous slide
• Click the Save button on the Standard toolbar to
  save the workbook using the file name e-Money
  Lenders Loan Payment Calculator

34
Naming and Printing Sections of a Worksheet
35
Protecting a Worksheet

• Select the range C3C5
• Hold down the CTRL key and then select the
  nonadjacent range E2E3
  
• Right-click one of the selected ranges
• Click Format Cells on the shortcut menu

36
Protecting a Worksheet

• When Excel displays the Format Cells dialog box,
  click the Protection tab, and then click Locked
  to remove the check mark
  
• Click the OK button and then select cell J19 to
  deselect the ranges C3C5 and E2E3
  
• Click Tools on the menu bar and then point to
  Protection
  
• Click Protect Sheet on the Protection submenu

37
Protecting a Worksheet

• When Excel displays the Protect Sheet dialog box,
  make sure the Protect worksheet and contents of
  locked cells check box at the top of the dialog
  box and the first two check boxes in the list
  contain check marks
• Click the OK button in the Protect Sheet dialog
  box
  
• Click the Save button on the Standard toolbar

38
Hiding and Unhiding a Sheet

• If the e-Money Lenders sheet is not active, click
  its tab
  
• Click Format on the menu bar and then point to
  Sheet
  
• Click Hide on the Sheet submenu

39
Hiding and Unhiding a Sheet

• Click Format on the menu bar, point to Sheets,
  and then click Unhide on the Sheets submenu
  
• When Excel displays the Unhide dialog box, click
  e-Money Lenders in the Unhide sheet list
  
• Click the OK button

40
Hiding and Unhiding a Workbook

• Click Window on the menu bar
• Click Hide on the Window menu
• Click Window on the menu bar and then click
  Unhide
  
• When Excel displays the Unhide dialog box, if
  necessary, click e-Money Lenders Loan Payment
  Calculator in the Unhide workbook list
  
• Click the OK button

41
Hiding and Unhiding a Workbook
42
Formula Checking
43
Enabling Background Formula Checking

• Click Tools on the menu bar, click Options, and
  then click the Error Checking tab in the Options
  dialog box
  
• If necessary, click Enable background error
  checking in the Settings area to select it
  
• Click any check box in the Rules area that does
  not contain a check mark
  
• Click the OK button

44
Enabling Background Formula Checking
45
Summary

• Control the color and thickness of outlines and
  borders
  
• Assign a name to a cell and refer to the cell in
  a formula using the assigned name
  
• Determine the monthly payment of a loan using the
  financial function PMT
  
• Use the financial functions PV (present value)
  and FV (future value)
  
• Create a data table to analyze data in a worksheet

46
Summary

• Add a pointer to a data table
• Create an amortization schedule
• Analyze worksheet data by changing values
• Add a hyperlink to a worksheet element
• Use names and the Set Print Area command to print
  selections of a worksheet

47
Summary

• Set print options
• Protect and unprotect cells in a worksheet
• Use the formula checking features of Excel
• Hide and unhide cell gridlines, rows, columns,
  sheets, and wokbooks

48
Excel Project 4 Complete