ACIS Excel 11

Solver Answer Reports

Four sections: 1. Report details 2. Objective cell information 3. Variable cell information 4. Constraints information

Solver Parameters

Solver requires three parameters: •Objective cell -Target cell -Contains the formula that is to be calculated to obtain the desired result •Variable cells -Decision cells or changing cells -Changed by Solver as it finds a solution •Constraints -The limitations or restrictions that your variable cells must follow

Solving Complex Problems

Start with the least complex method of solving the problem. 1.Use Trial and Error - make informed changes based on an understanding of the data 2.Use Goal Seek 3.Use Solver

Custom Data Validation Example

The Custom type can be used for situations where multiple criteria are needed. A formula can be used.

Scenario PivotTable Report

To create a Scenario PivotTable report, click the Summary button in the Scenario Manager Dialog box. Choose the type of report in the Scenario Summary dialog box. Enter cell references to amounts you want displayed in Result Cells box.

Watch Window

•A floating dialog box that displays values of cells located throughout the workbook. •Allows you to monitor changes to cells while you work in another part of the workbook or worksheet. •Must add the cell or range of cells you want to monitor to the Watch Window. •Enables you to observe the effects of changes without having to scroll through a large sheet or switch between multiple sheets. •Can position and size the Watch Window like any dialog box. •When you double-click a cell address in the Watch Window, the insertion point moves to that location, like a bookmark.

Scenario summary report

•A generated worksheet that describes each scenario in a workbook •Formatted as an Excel outline with two row outline levels and two column outline levels •Can hide or display details about the changing cells and the result cells •Can generate a scenario summary report as a PivotTable. To view multiple scenarios, create a Scenario Summary report. -Click the Summary button in the Scenario Manager Dialog box. -Choose the type of report in the Scenario Summary dialog box.

Scenario

•A saved set of values in a worksheet •User can vary numbers and see potential results •Often used to review best and worst possibilities •Worksheet can have up to 32 scenarios

Evaluate Formula Tool

•Breaks down a formula •Evaluates each part separately so you can see how the formula works •Can be used to help find an error in a formula

Formula Auditing

•Cells that include potential errors are marked with a green triangle in the upper-left corner of the cell. When you click the cell, Excel displays a Smart Tag to help you resolve the error.

Scenario Manager

•Define a Baseline Scenario that represents the starting assumptions •Create additional scenarios using the same cells with different values •Can display any scenario in worksheet •Only one scenario can be displayed at a time •Best practice: Name input and result cells (row headings) so that summary reports are easily understood.

List validation

•Displays a list of data values from which the user can choose. •Data for the list: -Type into dialog box separated by commas, or -Place in cell range in the workbook. •Best Practice: List data in alphabetical or other logical order

Error Checking Button

•Error checking tool identifies errors and provides information about them.

Control Data Entry with Data Validation

•Greatest source of errors: human error •Data validation tools -Help minimize data entry errors -Rules to control what can and cannot be entered in specific cells -Excel checks data as it is entered to verify that it matches established requirements •Validation settings - rules applied to data as it is entered •Input Message- Appears when a user makes a validated cell active; Comment box that contains a guideline for the person entering data •Error Alert - Pop-up message that appears after invalid data is entered; stop-User cannot enter invalid data, must cancel or retry, Warning - Invalid entry is allowed but can be edited or canceled, Information - Invalid entry is allowed

Circle Invalid Data Command

•Invalid data can occur when -Data is copied into a range with validation settings -Validation is set after data is already entered •Circle Invalid Data command -Places a red ellipse (an elongated circle) around each cell with invalid data -Highlights cells but does not edit or correct the data

Scenario Summary Report with Solver Results

•Run Solver three times using •Click Save Scenario each time Solver is run •Click Restore Original Values to return to original values in spreadsheet •Go to Scenario Manager to create Scenario Summary Report •In Result Cells box add references to values to be displayed in Report •Replace cell references with descriptive labels in Scenario Summary Report

Formula Precedents and Dependents

•Show which cells are used in a formula and how the cells are used •Precedents - cells that supply a value to the formula in the active cell -Trace Precedents-Displays lines with arrows to identify all cells referenced in the formula in the active cell •Dependents - cells whose value depends on the value in the active cell for its result -Trace Dependents- Displays lines with arrows to all cells that use the active cell directly or indirectly in a formula

Using Solver

•Solver—data analysis tool that optimizes a problem: -By manipulating the values of selected variables -But bound by constraints •Solver can be used to find: -Highest value -Lowest value -Exact value

Forecasting with a Forecast Sheet

•The Forecast Sheet feature creates a new worksheet with a data table and chart predicting future values based on the data selected. •Illustrates past data as well as predicts future values •Can be created quickly to estimate future values for -product sales -work hour requirements -expense levels etc. Uses two data series to build a line or column chart •One data series must be a date or time field •Date or time series must follow an interval, such as every hour, every other day, every month, etc. •One data series is the values used for forecasting •Generated sheet formatted as Excel table •Displays existing dates/times and values in adjacent columns •Forecast dates display at the bottom of the table with estimated values in a third column •Line or column chart with existing and forecasted values is shown to the right of Excel table.

Save the Solver Model

•To save the Solver Model, enter the cell where you want the model

Goal Seek

•Uses iteration to find the values needed to achieve a goal or objective •Backsolves a formula -Backsolving - knowing the results and determining the value needed to reach those results •Solves a formula for one cell -In Goal Seek dialog box, enter the cell reference for the formula in the Set cell box. -Type a target or goal number in the To value box