Exploring Microsoft Excel

1. Exploring Microsoft Excel Chapter 6 A Financial Forecast: Workgroups, Auditing, and Templates By Robert T. Grauer Maryann Barber Exploring Microsoft Excel 2002 Chapter 6

2. Objectives (1 of 2) • Develop spreadsheet model for financial forecast • Explain how the Scenario Manager facilitates the decision-making process • Differentiate between precedent and dependent cells • Use the Formula Auditing toolbar • Track editing changes • Use cell comments Exploring Microsoft Excel 2002 Chapter 6

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 Excel 2002 Chapter 6

4. Overview • Financial and budget planning are common business applications • Scenario Manager allows you to specify multiple sets of assumptions and see the results at a glance • Spreadsheet is a decision making tool • accuracy is crucial • Use the Formula Auditing toolbar to ensure accuracy • Share workbook among multiple users Exploring Microsoft Excel 2002 Chapter 6

5. Creating 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 • Use both relative and absolute cell references • What should change when you copy? What should remain the same when you copy? • Copy the formulas for year two to the remaining years • Format the spreadsheet and print Exploring Microsoft Excel 2002 Chapter 6

6. A Financial Forecast Exploring Microsoft Excel 2002 Chapter 6

7. Forecast with formulas displayed Exploring Microsoft Excel 2002 Chapter 6

8. Advanced Formatting Techniques • Rotate text • Rotate text and use merge and center to give headings a vertical orientation • Indent text • Visually divides worksheet into main headings and subheadings • Use conditional formatting • Apply different formats to cells based on their values • Create and apply your own styles Exploring Microsoft Excel 2002 Chapter 6

9. Scenario Manager • Specify multiple sets of assumptions (different scenarios) • For example, optimistic, pessimistic, and likely • Scenario summary allows you to view different scenarios easily. Exploring Microsoft Excel 2002 Chapter 6

10. Scenario Manager Dialog Box • Scenario Manager dialog box lists scenarios • Selecting a Scenario, then clicking Show will change the cells on the worksheet to reflect the scenario Exploring Microsoft Excel 2002 Chapter 6

11. Pessimistic Scenario Exploring Microsoft Excel 2002 Chapter 6

12. Scenario Summary • Shows the results in a selected cell of all scenarios, side-by-side Exploring Microsoft Excel 2002 Chapter 6

13. Hands-On Exercise 1 (1 of 2) • Objectives: to develop a spreadsheet for a financial forecast; to use conditional formatting and other advanced formatting techniques • 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 Exploring Microsoft Excel 2002 Chapter 6

14. Hands-on Exercise 1 (2 of 2) • Complete the formatting • The Insert Name command • Create the scenarios • View the scenarios • The Scenario Summary Exploring Microsoft Excel 2002 Chapter 6

15. 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 or on an Intranet Exploring Microsoft Excel 2002 Chapter 6

16. Auditing the Worksheet • Formula Auditing toolbar 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 Excel 2002 Chapter 6

17. 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) • the spreadsheet is only as good as the data that goes into it. • Data validation keeps garbage out in the first place Exploring Microsoft Excel 2002 Chapter 6

18. Data Validation command Exploring Microsoft Excel 2002 Chapter 6

19. The Workbook Trace dependents Trace precedents Exploring Microsoft Excel 2002 Chapter 6

20. Hands-On Exercise 2 • Objectives: to illustrate the tools on the Formula Auditing toolbar; to identify precedent and dependent cells; to use comments • Display the Formula Auditing and Reviewing Toolbars • Highlight Changes • Trace Dependents • Trace Precedents • Accept of Reject Changes (Resolve Conflicts) • Insert a Comment • Data Validation Exploring Microsoft Excel 2002 Chapter 6

21. Templates • A special type of workbook used as the basis for other workbooks • Most templates are protected • User can only change certain cells, such as the assumptions or initial conditions • Cells with formulas are protected • Use a two-step process • Unlock cells that are subject to change • Protect the worksheet • Save the file as a template • Templates are stored in the Templates folder Exploring Microsoft Excel 2002 Chapter 6

22. Hands-On Exercise 3 • Objectives: to unlock cells in a worksheet then protect the worksheet; to create a template and then create a workbook from that template • Clear the assumption area • Protect the worksheet • Test the template • Save the template • Open the template Exploring Microsoft Excel 2002 Chapter 6

23. Summary (1 of 2) • Spreadsheet used as a decision making tool • Use Scenario Manager to test spreadsheet with different sets of assumptions • Use a style to set formatting • Apply conditional formatting • Use the Formula Auditing toolbar Exploring Microsoft Excel 2002 Chapter 6

24. Summary (2 of 2) • Build a shared workbook to be edited by multiple users • Use the Data Validation command to restrict data that can be entered into the cells • Build a template to create other workbooks • Protect the worksheet Exploring Microsoft Excel 2002 Chapter 6