Using Scenario Manager - Business Decision Making - ثاني ثانوي

Lesson 2 Chapter 10 www.ien.edu.sa Using Scenario Manager Excel's Scenario Manager is a tool that allows a decision maker to create, ana- lyze, and compare results in different situations. In this lesson you will learn to use Excel's Scenario Manager to capture different combinations of inputs and results as you play "what if?" with the spreadsheet you were working on in Lesson 1. There are four possible combinations of input values: Opt-Up (Optimistic-Up), Opt-Down (Optimistic-Down), Pess-Up (Pessimistic-Up), and Pess-Down (Pessimistic-Down). Financial results for each combination will be different. Each combination of input values can be referred to as a scenario. Scenario Manager records the results of each combination of input values as a separate scenario and then shows a summary of all scenarios in a separate worksheet. The sum- mary worksheet values can be used as a raw table of numbers and then printed or copied into a document or report. The table of data can then be used to cre- ate an Excel chart, which can also be printed or inserted into a document. 370 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 370 DEFINITION Scenario: A set of values that collectively represent a particular situation in a spreadsheet that can be saved and then used for modeling the outcomes of business decisions. (For example: higher or lower profits). The four sets of input values produce different financial results. When you use Scenario Manager, you will define the four scenarios, run the input values, and put the results for each input scenario in a summary sheet. 2-1 Defining a Scenario When you define a scenario in Scenario Manager, you give it a name and iden- tify the input cells and input values. Then you identify the output cells so Excel can capture the output values in a summary sheet. STEP 1 Select the Data tab. In the Forecast group, click the drop-down arrow on the What-If Analysis icon, and then click the Scenario Manager option. Initially, no scenarios are defined, as you can see in Figure 10-19. 30/06/2023 14:28

FIGURE 10-19: Initial Scenario Manager window Scenario Manager Scenarios: No Scenarios defined. Choose Add to add scenarios. Changing cells: Comment: ? X Add... Delete Edit... Merge... Summary... Show Close QUICK TIP When working with this window, and the ones that come after it, do not press the Enter key to navigate. Use mouse clicks to move from one step to the next. QUICK TIP Contiguous cells are cells that are next to each other, and can be represented as a cell range (e.g., C8:C15). Non-contiguous cells are separated by other cells (that aren't part of the set), and so need to be grouped (e.g., C8, C10, C14). C8 and C9 are contiguous input cells and shown with a colon symbol. Noncontiguous input cells are grouped using commas. You can use this window to add, delete, or edit scenarios. Toward the end of the process, you create the summary sheet. Click the Add button. In the Add Scenario window, name the first scenario "Opt-Up". Then type the input cells in the Changing cells field-here, they are C8:C9. Excel may add dollar signs to the cell address, but do not be concerned about that. The window should look like the one shown in Figure 10-20. وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 371 Decision Support System Fundamentals 371 30/06/2023 14:28

372 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 372 FIGURE 10-20: Entering Opt-Up as a scenario Edit Scenario ? ✗ Scenario name: Opt-Up Changing cells: C8:C9 Ctrl+click cells to select non-adjacent changing cells. Comment: Protection Prevent changes Hide OK Cancel STEP 2 Click OK, which moves you to the Scenario Values window. Here you indicate what the input values will be for the scenario. Values in the current spreadsheet cells will be displayed. They may or may not be the correct ones for your scenario. For the Opt-Up scenario, you need to enter an "O" and a "U" if they are not the displayed values. Enter those values if needed, as shown in Figure 10-21. FIGURE 10-21: Entering Opt-Up scenario input values Scenario Values Enter values for each of the changing cells. 1: $C$8 O اله 2: $C$9 U ? OK Cancel Click OK, which takes you back to the Scenario Manager window. Enter the other three Opt-Down, Pess-Up, and Pess-Down scenarios, including the related input values. When you finish, you should see that the names and changing cells for the four scenarios have been entered, as in Figure 10-22. 30/06/2023 14:28

FIGURE 10-22: Scenario Manager window with all scenarios entered Scenario Manager ? Scenarios: Opt-Up Opt-Down Pess-Up Pess-Down Changing cells: $C$8:$C$9 Comment: > Add... Delete Edit... Merge... Summary... Show Close QUICK TIP When input values change, the output values in output cells are updated but the addresses of the output cells do not change. STEP 3 You can now create a summary sheet that shows the results of the four scenarios. Click the Summary button to open the Scenario Summary window. You must provide Excel with the output cell addresses-they will be the same for all four scenarios. Assume that you are interested in the results that have accrued at the end of the two-year period. These results are your two Year 3 Summary of business indica- tors section cells for Net income and End-of-year cash on hand (D12 and D13). Type those addresses in the window's input area, as shown in Figure 10-23. FIGURE 10-23: Entering Result cells addresses in the Scenario Summary window Scenario Summary Report type Scenario summary Scenario PivotTable report Result cells: ? وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 373 D12:D13 OK Cancel + Decision Support System Fundamentals 373 30/06/2023 14:28

Click OK. Excel calculates the results for each set of inputs and lists each of the outputs in tabular format. When it finishes this task, Excel creates a new sheet called the Scenario Summary and displays the results, as shown in Figure 10-24. FIGURE 10-24: Scenario Summary sheet created by Scenario Manager AutoSave Off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Calibri 11 AA = Wrap Text General Past BIU 4 Merge & Center ▾ - % 610 Cond Forma Undo Clipboard E Font Б Alignment 17 Number Б L19 XVfx T 2 B E H 7 523565 Scenario Summary Current Values: Opt-Up Opt-Down Pess-Up Pess-Down Changing Cells: $C$80 $C$9 U P D 8 Result Cells: 9 10 $D$12 $D$13 SAR 2,756,281.25 SAR 5,791,535.45 SAR 2,756,281.25 SAR 4,063,531.42 SAR 5,791,535.45 SAR 7,661,264.02 SAR 1,684,240.59 SAR 2,824,535.98 SAR 4,159,368.09 SAR 5,824,997.08 11 12 13 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. 14 QUICK TIP The Current Values in the spreadsheet are given an output column, which duplicates one of the four defined scenarios. Because we don't need it, you can delete it by selecting it, selecting the Home tab, locating the Delete icon's drop-down arrow within the Cells group, and selecting Delete Sheet Columns. To delete a sheet's row, follow these steps but select Delete Sheet Rows instead of Delete Sheet Columns. Column A is unused. You can click on the A tab and right-click your mouse. Choose the Delete option to delete it and to move everything to the left (see Figure 10-25). Columns of data should be easier to see on the screen without scrolling. FIGURE 10-25: Deleting column A and moving the rest of the spreadsheet to the left A1 T 12356189 Calibri BIE A R 11 AA、%,國 Search the menus X Cut Copy Paste Options: 374 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 374 .25 SAR 2,75 10 Paste Special... 45 SAR 5,79 11 sents values of Insert 12 created. Char 13 Delete 14 Clear Contents 15 16 Format Cells... 17 Column Width... 18 19 Hide 20 Unhide 21 22 23 30/06/2023 14:28

وزارة التعليم Ministry of Education 2024-1446 Other ways to make the worksheet easier to read include: entering text in column A to describe the input and output cells centering cell values by using the Center icon in the Home tab's Alignment group showing data in Currency format by using the Number Format drop-down menu within the Home tab's Number group. When finished, your summary sheet could resemble the one shown in Figure 10-26. Business Decision Making S1 S2 S3.indb 375 FIGURE 10-26: Scenario Summary sheet after formatting AutoSave aff Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat 92 Calibri 11 AA Wrap Text General Paste BIU Merge & Center ▾ %8 Undo Clipboard TW Font Alignment Б Number J16 fx A 1 Scenario Summary B E F 2 Opt-Up Opt-Down Pess-Up Pess-Down 4 Changing Cells: 5 Economic outlook 6 Purchase price outlook $C$8 $C$9 D 7 Result Cells: 8 Net income (Year 3) 9 End-of-year cash (Year 3) $D$12 SAR 2,756,281.25 $D$13 SAR 5,791,535.45 SAR 4,063,531.42 SAR 1,684,240.59 SAR 2,824,535.98 SAR 7,661,264.02 SAR 4,159,368.09 SAR 5,824,997.08 10 Notes: Current Values column represents values of changing cells at 11 time Scenario Summary Report was created. Changing cells for each 12 scenario are highlighted in gray. 9 Cond Forma E H Note that column C shows the Opt-Up case. The Net income in that scenario is SAR 2,756,281.25, and End-of-year cash is SAR 5,791,535.45. • What combination of Year 3 Net income and End-of-year cash would be best? Opt-Down (O-D) is the best result because it yields the highest income and highest cash. What is the worst combination? Pess-Up (P-U) yields the lowest income and lowest cash. Decision Support System Fundamentals 375 30/06/2023 14:28

QUICK TIP You can also delete a sheet by right-clicking its tab at the bottom of the screen. In the resulting menu, select Delete. Click Delete again when asked if you really want to delete the sheet. 2-2 Using Summary Sheets When you complete Scenario Manager case studies, you'll need to use summary sheets and their data. Next, you will look at some of those operations. 1. Rerunning Scenario Manager The Scenario Summary sheet does not update itself when the spreadsheet formulas or inputs change. To see an updated Scenario Summary sheet after making a change to the data, you must rerun Scenario Manager by clicking the Summary button in the Scenario Manager dialog box and then click OK. Another summary sheet is created. 2. Deleting Unwanted Scenario Manager Summary Sheets Suppose you want to delete a summary sheet. With the summary sheet on the screen, follow this procedure: . Select the Home tab. Within the Cells group, select the Delete icon's drop-down arrow. Select Delete Sheet. When asked if you really want to delete the sheet, click Delete. 3. Charting Summary Sheet Data The summary sheet results can be conveniently charted using Excel's chart- ing tools (see Figure 10-27). FIGURE 10-27: The Charts menu in Excel 376 10 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 376 L? 凸 X. Recommended Charts Maps PivotChart Charts 4. Copying Summary Sheet Data to the Clipboard If you want to copy the summary sheet data into the Clipboard to use in a word processing document, follow these steps: . Select the data range. . . Copy the data range into the Clipboard (Ctrl+C). Open your word processor document. Click the cursor where you want the upper-left part of the data to be positioned. Paste the data into the document (Ctrl+V). 30/06/2023 14:28

REVIEW QUESTIONS 1. What is the next step in Scenario Manager after you have added a scenario in the Initial Scenario Manager window? a. Indicate what the input values will be for the scenario in the Scenario Values window b. Format the summary sheet to make it easier to read c. Create a summary sheet in the Scenario Summary window d. Copy the summary sheet data to the clipboard to use elsewhere 2. How can you use summary sheets once they are completed? a. Rerun Scenario Manager to create an updated summary sheet b. Chart summary sheet data using Excel c. Copy summary sheet data to use in a word processing document d. All of the above وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 377 Decision Support System Fundamentals 377 30/06/2023 14:28