1 / 47

Lab 4

Lab 4. Using Solver, Linking Workbooks, Using Scenarios and Creating Templates. Objectives. Use Solver. Create and use workbook templates. Protect a worksheet. Open and use multiple workbooks. Link workbooks. Create and use Scenarios. Create a Scenario Summary.

hilel-snider
Download Presentation

Lab 4

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. Lab 4 Using Solver, Linking Workbooks,Using Scenarios and Creating Templates

  2. Objectives • Use Solver. • Create and use workbook templates. • Protect a worksheet. • Open and use multiple workbooks. • Link workbooks. • Create and use Scenarios. • Create a Scenario Summary. • Create and modify 3-D shapes. • Display the current date and time.

  3. Concepts Overview • SolverSolver - Solver is a tool used to perform what-if analyses to determine the effect of changing values in two or more cells on another cell. • Workbook Template - A workbook template is a workbook file that contains predesigned worksheets that can be used as a pattern for creating similar worksheets in new workbooks. • Worksheet Protection - Worksheet protection prevents users from changing a worksheet’s contents by protecting the entire worksheet or specified areas of it.

  4. Concepts Overview • Arrange Windows - The Arrange Windows feature displays all open workbook files in separate windows on the screen, in a tiled, horizontal, vertical, or cascade arrangement. • Link Workbooks - A link creates a connection between files that automatically updates the data in one file whenever the data in the other file changes. • Scenario - A scenario is a named set of input values that you can substitute in a worksheet to see the effects of a possible alternative course of action. Scenarios are designed to help forecast the outcome of various possible actions.

  5. Outline • Analyzing the Worksheet • Using Solver • Creating an Answer Report • Creating a Custom Template • Designing the Template

  6. Outline • Protecting the Worksheet • Saving the Template • Using the Template • Working with Multiple Workbooks • Opening a Second Workbook File • Arranging Windows • Linking Windows • Updating Linked Data

  7. Outline • Evaluating Scenarios • Adding Scenarios • Using Scenarios • Creating a Scenario Report • Enhancing the Report • Creating a 3-D Effect • Enhancing a 3-D Object

  8. Outline • Displaying the Current Date and Time • Using the Now Function • Lab Review

  9. Analyzing the Worksheet • Watch Window • Lets you see what is happening to a particular cell at any time • Tools/Formula/ Auditing/Show Watch Watch Window

  10. Concept 1: SOLVER • Part of a suite of commands called What-if Analysis Tools • Determines the effect of changing values in two or more cells called adjustable cellsontarget cells • Calculates a a formula to achieve a given value

  11. Solver Example

  12. Solver Solution Reports • Answer • Sensitivity • Limits

  13. Concept 2: WORKBOOK TEMPLATE • Workbook file that contains pre-designed worksheets • Used as a pattern for creating similar worksheets in new workbooks • Templates contain… • Text • Graphics • Functions, formulas, and macros

  14. Excel Templates • Template file extension is .xlt • Stored in a special Template folder • Excel automatically displays the SAVE AS dialog box when you save a template • Can specify a new name • Changes file type to .xls

  15. Designing the Template • Design a Template • Use an existing template for your design • Existing formulas and formats are not affected • Replace existing values with 0 • Protect the Worksheet

  16. Concept 3: PROTECTION • Worksheet level protection • Workbook-level protection • Worksheet protection • Prevents users from changing a worksheet's content • Protects the entire worksheet or specified areas • All cells and graphics are locked • Can leave some cells unlocked for editing

  17. Protection • Workbook-level protection • Prevents changes to an entire workbook • Protects so that sheets cannot be moved or deleted or new sheets inserted • Protects a workbook's windows • Prevents changes to the size and positions of windows • Window appearance is consistent • Passwords allowed • One to open and view the file • One to edit and save the file

  18. Default location to save templates Saves workbook as a template file type Saving the Template

  19. Using the Template

  20. More on Workbooks • Can open multiple workbook files at the same time • Each workbook is opened in its own window • Newly opened file is the active workbook file • Can see all workbooks simultaneously

  21. Arranging Windows

  22. Concept 4: WINDOW ARRANGEMENTS • Multiple opened windows can be arranged to make them easier to view • Appear in the same size • Appear on top of any other open windows

  23. Tiled Windows are displayed one after the other in succession, across and down the screen

  24. Vertical The windows are displayed side-by-side.

  25. Horizontal The windows are displayed one above the other.

  26. Cascade The windows are displayed one on top of the other, cascading down from the top of the screen.

  27. Concept 5: LINKING WORKBOOKS • Link • Creates a connection between files • Automatically updates the linked data in file when data in the other file changes • External reference • Formula in one workbook that refers to a cell in another workbook How does linking in Excel work?

  28. More on Linking • Dependent workbook – receives the data • Source workbook – supplies the data • Dependent cell – contains the external reference cell • Source cell – contains the data to be copied

  29. Source Cell D8 Dependent Cell B8 Source Workbook Dependent Workbook Linking Workbooks 2002 Forecast.xls 2003 Forecast.xls External Reference Formula in Cell B8 =[workbook file reference]sheet refererence!cell reference =[2002 Forecast.xls]Fourth Quarter!D$8

  30. Updating Linked Data • If both files are opened and data changes, updates are automatic • If dependent file is not opened, Excel displays an alert message and prompt • Yes – update references to unopened documents • No – references not updated

  31. Evaluating Scenarios • Scenario • Named set of input values • Can substitute values in a worksheet • Evaluate affects of a possible alternative course of action • Benefits of scenarios • Forecast • Can create various scenario groups

  32. Adding Scenarios

  33. Using Scenarios • Using Scenarios • Choose Tools/Scenarios • Select Scenario name from Scenario Manager box • Click Show • Creating a Scenario Report • Use Summary on Scenario Manager box • Report displayed in a separate window

  34. Enhance the Report • Create a 3-D effect • Enhance a 3-D object

  35. Displaying the Current Date and Time • Use the Now function • Returns the current date and time • Formatted as a date and time • Syntax =NOW()

  36. Key Terms • 3-D Shape - A 3-D shape is a line, AutoShape, or free-form drawing object that has a three-dimensional effect applied to it. • Active workbook - The workbook that contains the cell selector and that will be affected by the next action. • Adjustable cell - In Solver, the cell or cells whose values will be changed in order to attain the value set in the target cell.

  37. Key Terms • Arrange windows - The arrangement of multiple open workbook windows on the screen: tiled, cascade, horizontally or vertically. • Cascade - The window arrangement that displays one workbook window on top of the other, cascading down from the top of the screen. • Dependent cell - The cell that receives the linked data. • Dependent workbook - The workbook file that receives the linked data. • External reference formula - A formula that creates a link between workbooks.

  38. Key Terms • Horizontal - The window arrangement that displays one open workbook window above the other. • Link - A relationship created between files that allows data. • Password - A secret code that prevents unauthorized users from turning off protection. • Protection - A worksheet feature that prevents users from making changes to data and formats.

  39. Key Terms • Scenario - A named set of input values that you can substitute in a worksheet to see the effects of a possible alternative course of action. • Solver - A tool that is used to perform what-if analyses to determine the effect of changing values in two more cells, called the adjustable cells, on another cell, called the target cell. • Source cell The cell or range of cells containing the data you want to copy. • Source Workbook -The workbook file that supplies linked data.

  40. Key Terms • Target Cell - In Solver, the cell you set to the value that you want to be attained. • Template - A workbook file that contains predesigned worksheets that can be used as a pattern for creating other similar sheets in new workbooks. It has an .xlt file extension. • Tiled - A window arrangement in which open workbook windows are displayed one after the other in succession, across and down the screen. • Vertical - The window arrangement in which open workbook windows are displayed side-by-side.

  41. Discussion Questions • Discuss how templates can be used to make workbook creation easier. What types of templates do you think would be most helpful, and what should these templates contain? • Discuss what happens to formulas that are linked to another workbook when the original workbook is updated. When would it be appropriate to link data between workbooks? • Discuss how Solver and scenarios are used in a worksheet. How can they help with the analysis of data?

  42. Frequently Asked Questions • When would use the Watch Window in Excel? • I need to determine the effects of changing values in two or more cells. What can Excel's Solver feature do for me? • How does a workbook template work? • I am using sensitive information. What type of data protection does Excel offer?

  43. Frequently Asked Questions • Help I need to use multiple workbooks and I can't see them all on my screen. How can I arrange these windows? • Explain how I can link two workbooks. Can data be updated if values changed in one of the workbooks? • I want to create a budget forecast based on various revenue values. How can the Excel Scenario Manager help me?

  44. Frequently Asked Questions • What is the function I use to display the current date and time in a worksheet? • What can I do to make my worksheet look more attractive? • How can I lock cells? Unlock cells?

  45. Web Links • Excel Tutorial • http://www.usd.edu/trio/tut/excel/index.html • Technology SOS • http://scils.rutgers.edu/techsos/ • University of Arizona: A Spreadsheet Tutorial • http://timon.sir.arizona.edu/sm97/506/spreadsheet.htm

  46. Web Links • John F. Lacher On-Line Consulting Service • http://www.lacher.com/ (neat samples of applications) • Alan's Excel Goodies • http://www.barasch.com/excel/ • Analyze-it • http://www.analyse-it.com/

  47. Web Links • Generator and Excel • http://myweb.iea.com/~nli/products/genetic_algorithms/generator.htm • Inside Microsoft Excel • http://www.elementkjournals.com/ime/

More Related