The view of the individual scenarios in a single table is already a pleasant thing. But a clear presentation of all values would be even better. And this representation can be created with a mouse click.
Create scenario reports
In Scenario Manager, click the Summary button. The Scenario Report dialog box is displayed. As a report type, leave the option Scenario Report activated. Select the cells to appear in your report. Areas that are not directly together are marked with [Ctrl]. In this example, the areas B3: B7; B9; B11: B12 are selected. Clicking the OK button creates the scenario report.
Scenario Report as Pivot Report
The report provides an overview of the current model and the models stored. This representation supports decision-making. It is an Excel spreadsheet, even if it does not look at first. This is because the framework has been blurred in this report. So you can easily use the data for further calculations. The data is written without formulas and references. Therefore, if you change data in your Scenario Manager, the data in the scenario report is not changed. However, you can create a new scenario report at any time.
Scenario reports as a diagram
With Pivot Tables, you can evaluate your data according to any point of view. The scenario manager also gives you the ability to create a pivot table. To do this, click the Summary button in Scenario Manager. In the following dialog box, activate the PivotTable scenario report option. Clicking the OK button creates a pivot table.
Lesetipp
Unlike normal pivot tables, the results in this table are not linked to the data from the scenario manager. So if you change scenarios, you'll need to create the report again.
In the right pane of the pivot table, you will find all the fields that belong to your pivot table. These fields are each equipped with a check box and can be inserted or dimmed using a check mark, as required. At the bottom of this window you can move the fields between the areas. This allows you to tailor the report to your needs.
In PivotTables, you can easily create graphs. Click in any cell within the pivot area. Then, in the PivotTable tools, click the Analyze tab. In the Tools group, you will find the PivotChart icon. Clicking on this symbol will display the Insert Diagram dialog box. Select the chart type that you want to apply to your data. Clicking the OK button creates the chart.
For better editing of a diagram, a separate diagram sheet is available. In order for your diagram to have its own sheet, it must first be selected. Then enable the Design tab in the Pivot Chart Tools. In the Location group, click the Move Diagram icon. In the dialog box that now appears, select the New sheet option, and then click the OK button. Your chart now appears on a new chart.
The chart is an active chart. You can filter your data directly within the chart. The filtering also affects directly on the pivot table.
What is possible with simple scenarios also works with complex scenarios. To do this, create another scenario. In Scenario Manager, click the Add button. In the following dialog box, type Complex1 in the Scenario name field. Set as variable cells: B3: B5; $ B $ 7. Click the OK button. Now all selected fields are offered to you for modification. Enter the following values: behind the selected_model, the name 40 TDE 10 years at 3%, after the interest rate the value 0.03, after the repayment period_in_year the value 10 and behind the loan the number 40000. Finally, click the OK button
If you now create a scenario report, all values are set to € 40,000 because you can not change the value in the previous scenarios. To change this, you can extend the existing scenarios by the fields Loans and Runtime, and then again maintain the 50,000 € and the 12 years.
Click the Summary button to access the Scenario Report dialog box. Select the option of the same name, and then click the OK button. Now all scenarios are displayed in a clear way.
Expand the scenario with more values
No comments:
Post a Comment