What-If Analysis กับ Scenario Manager

Scenario Manager มีประโยชน์ในกรณีที่คุณมีตัวแปรมากกว่าสองตัวในการวิเคราะห์ความอ่อนไหว Scenario Manager สร้างสถานการณ์จำลองสำหรับแต่ละชุดของค่าอินพุตสำหรับตัวแปรที่กำลังพิจารณา สถานการณ์ช่วยให้คุณสำรวจชุดของผลลัพธ์ที่เป็นไปได้โดยสนับสนุนสิ่งต่อไปนี้ -

• เปลี่ยนชุดอินพุตได้มากถึง 32 ชุด
• การผสานสถานการณ์จากแผ่นงานหรือสมุดงานต่างๆ

หากคุณต้องการวิเคราะห์ชุดอินพุตมากกว่า 32 ชุดและค่าแสดงถึงตัวแปรหนึ่งหรือสองตัวแปรเท่านั้นคุณสามารถใช้ตารางข้อมูลได้ แม้ว่าจะ จำกัด ไว้เพียงหนึ่งหรือสองตัวแปรตารางข้อมูลสามารถรวมค่าอินพุตต่างๆได้มากเท่าที่คุณต้องการ อ้างถึงการวิเคราะห์แบบ What-If ด้วยตารางข้อมูลในบทช่วยสอนนี้

สถานการณ์

สถานการณ์คือชุดของค่าที่ Excel บันทึกและสามารถแทนที่โดยอัตโนมัติในแผ่นงานของคุณ คุณสามารถสร้างและบันทึกกลุ่มของค่าต่างๆเป็นสถานการณ์จำลองบนเวิร์กชีตจากนั้นสลับไปมาระหว่างสถานการณ์เหล่านี้เพื่อดูผลลัพธ์ที่แตกต่างกัน

ตัวอย่างเช่นคุณสามารถมีสถานการณ์งบประมาณต่างๆเพื่อเปรียบเทียบระดับรายได้และค่าใช้จ่ายต่างๆที่เป็นไปได้ นอกจากนี้คุณยังสามารถมีสถานการณ์การกู้ยืมที่แตกต่างกันจากแหล่งต่างๆที่เปรียบเทียบอัตราดอกเบี้ยและระยะเวลาเงินกู้ต่างๆ

ถ้าข้อมูลที่คุณต้องการใช้ในสถานการณ์จำลองมาจากแหล่งที่มาที่แตกต่างกันคุณสามารถรวบรวมข้อมูลในสมุดงานที่แยกจากกันจากนั้นรวมสถานการณ์จากสมุดงานอื่นให้เป็นหนึ่งเดียว

หลังจากที่คุณมีสถานการณ์ทั้งหมดที่คุณต้องการคุณสามารถสร้างรายงานสรุปสถานการณ์ได้ -

• ซึ่งรวมเอาข้อมูลจากสถานการณ์ทั้งหมด
• ซึ่งช่วยให้คุณเปรียบเทียบสถานการณ์แบบเคียงข้างกัน

ตัวจัดการสถานการณ์

Scenario Manager เป็นหนึ่งในเครื่องมือการวิเคราะห์แบบ What-if ใน Excel

ในการสร้างรายงานการวิเคราะห์ด้วย Scenario Manager คุณต้องทำตามขั้นตอนเหล่านี้ -

Step 1 - กำหนดชุดของค่าเริ่มต้นและระบุเซลล์อินพุตที่คุณต้องการเปลี่ยนแปลงเรียกว่าเซลล์ที่เปลี่ยนแปลง

Step 2 - สร้างแต่ละสถานการณ์ตั้งชื่อสถานการณ์และป้อนค่าสำหรับแต่ละเซลล์อินพุตที่เปลี่ยนแปลงสำหรับสถานการณ์นั้น

Step 3- เลือกเซลล์ผลลัพธ์ที่เรียกว่าเซลล์ผลลัพธ์ที่คุณต้องการติดตาม เซลล์เหล่านี้มีสูตรในชุดค่าเริ่มต้น สูตรใช้เซลล์อินพุตที่เปลี่ยนแปลง

Scenario Manager สร้างรายงานที่มีอินพุตและค่าเอาต์พุตสำหรับแต่ละสถานการณ์

ค่าเริ่มต้นสำหรับสถานการณ์

ก่อนที่คุณจะสร้างสถานการณ์จำลองต่างๆคุณต้องกำหนดชุดของค่าเริ่มต้นที่จะยึดตามสถานการณ์นั้น ๆ

ขั้นตอนในการตั้งค่าเริ่มต้นสำหรับสถานการณ์คือ -

• กำหนดเซลล์ที่มีค่าอินพุต
• ตั้งชื่อเซลล์อินพุตให้เหมาะสม
• ระบุเซลล์อินพุตด้วยค่าคงที่
• ระบุค่าสำหรับอินพุตคงที่
• ระบุเซลล์อินพุตด้วยค่าที่เปลี่ยนแปลง
• ระบุค่าเริ่มต้นสำหรับอินพุตที่เปลี่ยนแปลง
• กำหนดเซลล์ที่มีผลลัพธ์ เซลล์ผลลัพธ์มีสูตร
• ตั้งชื่อเซลล์ผลลัพธ์ให้เหมาะสม
• วางสูตรในเซลล์ผลลัพธ์

พิจารณาตัวอย่างก่อนหน้าของเงินกู้ ตอนนี้ดำเนินการดังนี้ -

• กำหนดเซลล์สำหรับจำนวนเงินกู้

  • ค่าอินพุตนี้เป็นค่าคงที่สำหรับสถานการณ์ทั้งหมด

  • ตั้งชื่อเซลล์ Loan_Amount

  • ระบุมูลค่าเป็น 5,000,000

• กำหนดเซลล์สำหรับอัตราดอกเบี้ยจำนวนการชำระเงินและประเภท (การชำระเงินในช่วงต้นเดือนหรือสิ้นเดือน)

  • ค่าอินพุตเหล่านี้จะมีการเปลี่ยนแปลงในสถานการณ์ต่างๆ

  • ตั้งชื่อเซลล์ Interest_Rate, NPER และ Type

  • ระบุค่าเริ่มต้นสำหรับการวิเคราะห์ในเซลล์เหล่านี้เป็น 12%, 360 และ 0 ตามลำดับ

• กำหนดเซลล์สำหรับ EMI

  • นี่คือค่าผลลัพธ์

  • ตั้งชื่อเซลล์ EMI

  • วางสูตรในเซลล์นี้เป็น -

    =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

แผ่นงานของคุณมีลักษณะดังที่แสดงด้านล่าง -

ดังที่คุณเห็นว่าเซลล์อินพุตและเซลล์ผลลัพธ์อยู่ในคอลัมน์ C โดยมีชื่อตามที่ระบุในคอลัมน์ D

การสร้างสถานการณ์

หลังจากตั้งค่าเริ่มต้นสำหรับ Scenarios แล้วคุณสามารถสร้างสถานการณ์จำลองโดยใช้ Scenario Manager ได้ดังนี้ -

• คลิกแท็บ DATA บน Ribbon
• คลิก What-if Analysis ในกลุ่ม Data Tools
• เลือก Scenario Manager จากรายการดรอปดาวน์

กล่องโต้ตอบตัวจัดการสถานการณ์จะปรากฏขึ้น คุณสามารถสังเกตได้ว่ามีข้อความ -

“No Scenarios defined. Choose Add to.”

คุณต้องสร้างสถานการณ์จำลองสำหรับชุดค่าที่เปลี่ยนแปลงแต่ละชุดในตัวจัดการสถานการณ์ เป็นการดีที่จะกำหนดสถานการณ์แรกด้วยค่าเริ่มต้นเนื่องจากช่วยให้คุณสามารถเปลี่ยนกลับไปใช้ค่าเริ่มต้นได้ทุกเมื่อที่คุณต้องการในขณะที่แสดงสถานการณ์ต่างๆ

สร้างสถานการณ์แรกด้วยค่าเริ่มต้นดังนี้ -

• คลิก Add ในกล่องโต้ตอบตัวจัดการสถานการณ์

Add Scenario กล่องโต้ตอบปรากฏขึ้น

• ภายใต้ชื่อสถานการณ์พิมพ์สถานการณ์ที่ 1
• ภายใต้การเปลี่ยนเซลล์ให้ป้อนการอ้างอิงสำหรับเซลล์เช่น C3, C4 และ C5 โดยกดปุ่ม Ctrl

ชื่อของกล่องโต้ตอบเปลี่ยนเป็นแก้ไขสถานการณ์

• แก้ไขข้อความในไฟล์ Comment as – Initial Values กล่อง.

• เลือกตัวเลือกป้องกันการเปลี่ยนแปลงภายใต้การป้องกันจากนั้นคลิกตกลง

Scenario Valuesกล่องโต้ตอบปรากฏขึ้น ค่าเริ่มต้นที่คุณกำหนดไว้จะปรากฏในกล่องเซลล์ที่เปลี่ยนแปลงแต่ละกล่อง

Scenario 1 ด้วยค่าเริ่มต้นถูกสร้างขึ้น

สร้างสถานการณ์เพิ่มเติมอีกสามสถานการณ์โดยมีค่าที่แตกต่างกันในเซลล์ที่เปลี่ยนแปลงดังนี้ -

• คลิก Add ในกล่องโต้ตอบค่าสถานการณ์

กล่องโต้ตอบเพิ่มสถานการณ์จะปรากฏขึ้น โปรดสังเกตว่า C3, C4, C5 ปรากฏในกล่องการเปลี่ยนเซลล์

• ในกล่องชื่อสถานการณ์พิมพ์สถานการณ์ที่ 2

• แก้ไขข้อความในไฟล์ Comment เป็น - อัตราดอกเบี้ยที่แตกต่างกัน

• เลือกป้องกันการเปลี่ยนแปลงภายใต้การป้องกันและคลิกตกลง

Scenario Valuesกล่องโต้ตอบปรากฏขึ้น ค่าเริ่มต้นจะปรากฏในเซลล์ที่เปลี่ยนแปลง เปลี่ยนค่าของInterest_Rate ถึง 0.13 แล้วคลิก Add.

Add Scenarioกล่องโต้ตอบปรากฏขึ้น โปรดสังเกตว่า C3, C4, C5 ปรากฏในกล่องภายใต้เซลล์ที่เปลี่ยนแปลง

• ในกล่องชื่อสถานการณ์พิมพ์สถานการณ์ที่ 3

• แก้ไขข้อความในไฟล์ Commentกล่องเป็น - ไม่แตกต่างกัน ของการชำระเงิน

• เลือกป้องกันการเปลี่ยนแปลงภายใต้การป้องกันและคลิกตกลง

กล่องโต้ตอบค่าสถานการณ์จะปรากฏขึ้น ค่าเริ่มต้นจะปรากฏในเซลล์ที่เปลี่ยนแปลง เปลี่ยนค่าของ NPER เป็น 300 แล้วคลิกAdd.

Add Scenarioกล่องโต้ตอบปรากฏขึ้น โปรดสังเกตว่า C3, C4, C5 ปรากฏในกล่องการเปลี่ยนเซลล์

• ในกล่องชื่อสถานการณ์พิมพ์สถานการณ์ที่ 4

• แก้ไขข้อความในไฟล์ Comment กล่องเป็น - ประเภทการชำระเงินที่แตกต่างกัน

• เลือกป้องกันการเปลี่ยนแปลงภายใต้การป้องกันและคลิกตกลง

Scenario Valuesกล่องโต้ตอบปรากฏขึ้น ค่าเริ่มต้นจะปรากฏในเซลล์ที่เปลี่ยนแปลง เปลี่ยนค่าของ Type เป็น 1 คลิกตกลงเมื่อคุณได้เพิ่มสถานการณ์ทั้งหมดที่คุณต้องการเพิ่ม

Scenario Managerกล่องโต้ตอบปรากฏขึ้น ในกล่องภายใต้สถานการณ์จำลองคุณจะพบชื่อของสถานการณ์ทั้งหมดที่คุณสร้างขึ้น

• คลิกสถานการณ์สมมติ 1 ดังที่คุณทราบสถานการณ์ที่ 1 ประกอบด้วยค่าเริ่มต้น


ตอนนี้คลิก Summary. กล่องโต้ตอบสรุปสถานการณ์จะปรากฏขึ้น

รายงานสรุปสถานการณ์

Excel มีรายงานสรุปสถานการณ์สองประเภท -

• สรุปสถานการณ์
• รายงาน Scenario PivotTable

ในกล่องโต้ตอบสรุปสถานการณ์คุณจะพบประเภทรายงานทั้งสองนี้

เลือกสรุปสถานการณ์ภายใต้ประเภทรายงาน

สรุปสถานการณ์

ใน Result cells เลือกเซลล์ C6 (ที่นี่เราใส่ไฟล์ PMTฟังก์ชัน). คลิกตกลง

รายงานสรุปสถานการณ์จะปรากฏในแผ่นงานใหม่ เวิร์กชีตถูกตั้งชื่อเป็นข้อมูลสรุปสถานการณ์

คุณสามารถสังเกตสิ่งต่อไปนี้ในรายงานสรุปสถานการณ์ -

• Changing Cells- จัดเตรียมเซลล์ทั้งหมดที่ใช้เป็นเซลล์ที่เปลี่ยนแปลง ตามที่คุณตั้งชื่อเซลล์ Interest_Rate, NPER และ Type สิ่งเหล่านี้ดูเหมือนจะทำให้รายงานมีความหมาย มิฉะนั้นจะแสดงเฉพาะการอ้างอิงเซลล์เท่านั้น

• Result Cells − Displays the result cell specified, i.e. EMI.

• Current Values − It is the first column and enlists the values of that scenario which is selected in the Scenario Manager Dialog box before creating the summary report.

• For all the scenarios you have created, the changing cells will be highlighted in gray.

• In the EMI row, the result values for each scenario will be displayed.

You can make the report more meaningful by displaying the comments that you added while creating the scenarios.

• Click the + button to the left of the row containing the scenario names. The comments for the scenarios appear in the row under the scenario names.

Scenarios from Different Sources

Suppose you get the scenarios from three different sources and you need to prepare the Scenario summary report in a Master workbook. You can do this by merging the scenarios from different workbooks into the Master workbook. Follow the steps given below −

• Assume that the scenarios are in the workbooks, Bank1_Scenarios, Bank2_Scenarios and Bank3_Scenarios. Open the three workbooks.

• Open the Master workbook, in which you have the initial values.

• Click DATA > What-if Analysis > Scenario Manager in the Master workbook.

The Scenario Manager Dialog box appears.

As you can observe, there are no scenarios as you have not yet added any. Click Merge.

The Merge Scenarios dialog box appears.

As you can see, under Merge scenarios from, you have two boxes −

• Book
• Sheet

You can select specific worksheet from a specific workbook that contains the scenarios, which you want to add to your results. Click the drop-down arrow of Book to see the workbooks.

Note − The corresponding workbooks should be open to appear in this list.

Select the book – Bank1_Scenarios.

Bank1 sheet is displayed. At the bottom of the dialog box, the number of scenarios found on source sheet is displayed. Click OK.

The Scenario Manager dialog box appears. The two scenarios that were merged into the Master workbook will be listed under Scenarios.

Click the Merge button. The Merge Scenarios dialog box appears. Now, select Bank2_Scenarios from the drop-down list in the Book box.

Bank2 sheet is dislayed. At the bottom of the dialog box, the number of scenarios found on source sheet are displayed. Click OK.

The Scenario Manager Dialog box appears. The four scenarios that were merged into the Master workbook are listed under Scenarios.

Click the Merge button. The Merge Scenarios dialog box appears. Now, select Bank3_Scenarios from the drop-down list in the Book box.

Bank3 sheet is displayed. At the bottom of the dialog box, the number of scenarios found on source sheet will be displayed. Click OK.

The Scenario Manager Dialog box appears. The five scenarios that were merged into the Master workbook will be listed under Scenarios.

Now, you have all the required scenarios to produce the Scenario summary report.

Click the Summary button. The Scenario Summary dialog box appears.

• Select Scenario summary.
• In the Result cells box, type C6 and click OK.

The Scenario summary report appears on a new worksheet in the Master workbook.

Displaying Scenarios

Suppose you are presenting your scenarios and you would like to dynamically switch from one scenario to another and display the set of input values and result values of the corresponding scenario.

• Click DATA > What-if Analysis > Scenario Manager from the Data Tools group. The Scenario Manager Dialog box appears. The list of scenarios appear.

• Select the scenario you want to display. Click Show.

The values on the worksheet are updated to that of the selected scenario. The result values are recalculated.

Scenario PivotTable Report

You can see the Scenario report in the form of a PivotTable also.

• Click the Summary button in the Scenario Manager Dialog box. The Scenario Summary dialog box appears.

• Select the Scenario PivotTable report under Report type.

• Type C6 in the Result cells box.

Scenario PivotTable report appears on a new worksheet.