Excel Project 4

1. Excel Project 4 Financial Functions, Data Tables, Amortization Schedules, and Hyperlinks

2. 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. 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. 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. 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. 6

7. 7 Adding Custom Borders Select the range B2:E6 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. 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 B2:E6

9. 9 Adding Custom Borders

10. 10 Creating Names Based on Row Titles Select the range B4:C6 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. 11 Creating Names Based on Row Titles Select the range D2:E6, 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 D2:E6 and then click the Name box arrow in the formula bar to view the names created

12. 12 Creating Names Based on Row Titles

13. 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. 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. 15 Financial Functions

16. 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 B9:E20 Click Data on the menu bar

17. 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. 18 Defining a Range as a Data Table

19. 19 Adding a Pointer to the Data Table Select the range B10:B20 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. 20 Adding a Pointer to the Data Table Type =$E$2 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. 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 B10:B21 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. 22 Adding a Pointer to the Data Table

23. 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 <= $e$3, pv($e$2 / 12. 12 * ($e$3 – g3), -$e$4), 0) as the entry Click the Enter box in the formula bar

24. 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 * $e$4 – j3, 0) in cell K3 Click the Enter box in the formula bar Copy the formulas to fill the amortization schedule

25. 25 Entering the Total Formulas in the Amortization Schedule Select cell I18. Enter Subtotal as the row title. Select the range J18:K18. 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. 26 Entering the Total Formulas in the Amortization Schedule

27. 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. 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. 29 Assigning a Hyperlink to an Embedded Graphic

30. 30 Setting the Print Area Select the range B1:E6, 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. 31 Naming and Printing Sections of a Worksheet Select the range B1:E6, click the Name box, and then type Loan_Payment as the name of the range Press the ENTER key Select the range B7:E20, click the Name box, type Interest_Schedule as the name of the range, and then press the ENTER key Select the range G1:K20, click the Name box, type Amortization_Schedule as the name of the range, and then press the ENTER key Select the range B1:K20, click the Name box, type All_Sections as the name of the range, and then press the ENTER key

32. 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. 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. 34 Naming and Printing Sections of a Worksheet

35. 35 Protecting a Worksheet Select the range C3:C5 Hold down the CTRL key and then select the nonadjacent range E2:E3 Right-click one of the selected ranges Click Format Cells on the shortcut menu

36. 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 C3:C5 and E2:E3 Click Tools on the menu bar and then point to Protection Click Protect Sheet on the Protection submenu

37. 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. 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. 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. 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. 41 Hiding and Unhiding a Workbook

42. 42 Formula Checking

43. 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. 44 Enabling Background Formula Checking

45. 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. 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. 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