1 / 21

Exploring Excel

Learn how to develop financial forecasts, automate formatting, track changes, use conditional formatting, resolve conflicts, and create templates in Excel. Explore auditing functions and collaborative work. Hands-on exercises included.

dudleye
Download Presentation

Exploring Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exploring Excel Chapter 8 The Expert User: Workgroups, Auditing, and Templates By Robert T. Grauer Maryann Barber Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  2. Objectives (1 of 2) • Develop spreadsheet model for financial forecast • Use styles to automate formatting • Differentiate between precedent and dependent cells • Track editing changes • Use cell comments Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  3. Objectives (2 of 2) • Explain how workgroup functions enable collaborative work • Resolve conflicts between users • Describe the use of data validation • Use conditional formatting • Explain how template facilitates the creation of a new spreadsheet • Create a template Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  4. Overview • Learn to use worksheets for financial and budget planning • Use a financial forecast • Spreadsheet as a decision making tool • Use the Auditing toolbar to ensure accuracy Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  5. A Financial Forecast • Enter row and column heading • Enter initial conditions and assumptions • Develop the formulas for the first year • Develop the formulas for second year and assumed rates of change • Copy the formulas and format Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  6. Building a Financial Forecast • Decide which cell references should be relative and which should be absolute • Isolate assumptions and conditions separately from forecast • Conditional formatting • Setting styles Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  7. A Financial Forecast Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  8. Checking the Forecast • Potential for user error exists in spreadsheets • Check spreadsheets for math and logic errors not just formatting mistakes • Use functions rather than formulas • Auditing toolbar helps you trace the relationships between cells Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  9. Hands-On Exercise 1 • Enter the Formulas for Year One • Enter the Formulas for Year Two • Copy the Formulas to the Remaining Years • Create a Style • Rotate and Indent Text • Conditional Formatting • Complete the Formatting Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  10. Workgroups and sharing files • Workgroups make it easier to share Excel files among a group of users • Use Reviewing toolbar to Track changes made by others • Allows for changes to be monitored • Comments can help other users • Can be shared on the Web on an Intranet Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  11. Auditing the Worksheet • Shows graphical relationships built into formulas • Identifies Precedents and Dependents • Precedents are the cells referenced by the formula • Dependents identify the formulas that reference a cell Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  12. The Workbook Trace dependents Trace precedents Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  13. Data Validation • Data Validation command restricts values to be accepted in a cell • Data validation is useful in shared workbooks • Garbage In Garbage Out (GIGO) implies that spreadsheet is only as data it is based on Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  14. Data Validation command Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  15. Hands-On Exercise 2 • Display the Auditing and Reviewing Toolbars • Highlight Changes • Trace Dependents • Trace Precedents • Accept of Reject Changes (Resolve Conflicts) • Insert a Comment • Data Validation Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  16. Templates • Most templates are based on protected workbooks • Certain cells are changed but others are protected • Unlock cells that are subject to change • Templates are stored in the template folder file Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  17. Hands-On Exercise 3 • Clear the Assumption Area • Protect the Worksheet • Test the Template • Save the Template • Open the Template Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  18. Summary (1 of 2) • Spreadsheet used as a decision making tool • What if analysis can e employed • Use a style to set formatting • Apply conditional formatting • Use the Auditing Toolbar Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  19. Summary (2 of 2) • Build a shared workbook to be edited by multiple users • Use the Data Validation command to restrict poor data entry • Build a template to create other workbooks • Protect the worksheet Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  20. Practice with Excel • Email Your Homework • The #VALUE Error • Erroneous Grade Book • The Power of Compound Interest • Add Macros to a Template • The Scenario Manager • The Expense Report Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

  21. Case Studies • The Entrepreneur • Publishing to the Web • Spreadsheet Solutions • The License Agreement • Password Protection Exploring Microsoft Office 2000 - Exploring Excel Chapter 8

More Related