การวิเคราะห์ข้อมูล (What-if Analysis)

Post Views: 38,538

ในหัวข้อนี้เราจะมาพูดถึงการวิเคราะห์ข้อมูลโดยใช้ What-if ซึ่งเป็นเครื่องมือการวิเคราะห์ที่อยู่ใน Excel การวิเคราะห์ข้อมูลแบบ What-if เป็นการเอาสูตรหรือข้อมูลที่มีมาวิเคราะห์เพื่อหาทางเลือกจากสถานการณ์ต่างๆ โดยมีการวิเคราะห์ 3 แบบ คือ สถานการณ์สมมติ(Scenario), ตารางข้อมูล(Data Table) และการค้นหาค่าเป้าหมาย(Goal Seek) สถานการณ์สมมติและตารางข้อมูลจะรับชุดของค่าข้อมูลเข้า(Input) และประเมินล่วงหน้า เพื่อหาผลลัพธ์ที่เป็นไปได้ ส่วนการค้นหาค่าเป้าหมายต่างจากสถานการณ์สมมติและตารางข้อมูลตรงที่ นำผลลัพธ์มาประเมินย้อนกลับ เพื่อหาค่าข้อมูลเข้าที่เป็นไปได้ที่ทำให้เกิดผลลัพธ์นั้นๆ

1. การค้นหาค่าเป้าหมาย(Goal Seek)

ปกติเวลาคำนวณเราจะคำนวณตามลำดับ เช่น ยอดขาย–ค่าใช้จ่าย = กำไร แต่ Goad Seek ใช้การระบุผลลัพธ์ที่ต้องการ(Output) แล้วคำนวณค่าข้อมูลเข้า(Input) เพื่อให้ได้ผลลัพธ์ตามที่เราต้องการ

ตัวอย่าง1 การคำนวณเงินกู้ ปกติจะคำนวณจากเงินต้น ระยะเวลาที่กู้ และดอกเบี้ย และใส่สูตรด้วยฟังก์ชัน PMT (ดูภาพที่ 1) ซึ่งจะได้จำนวนเงินที่ต้องจ่ายในแต่ละเดือน (ดูภาพที่ 2)

ภาพที่ 1 คำนวณอัตราผ่อนชำระต่องวด

ภาพที่ 2 จำนวนเงินที่ต้องจ่ายในแต่ละเดือน

เงื่อนไขที่ 1 : ถ้ามีต้องการผ่อนต่อเดือน 10,000 บาท ในระยะเวลา 10 ปี สามารถใช้ Goal Seek คำนวณให้ได้ว่า จะสามารถกู้เงินได้เท่าไหร่? ไปที่ Data > What-if Analysis > Goal Seek (ดูภาพที่ 3)

ภาพที่ 3 เลือกคำสั่ง Goal Seek

ในหน้าต่าง Goal Seek (ดูภาพที่ 4) :

Set cell – cell ที่จะใช้ตั้งค่า โดยคลิกเลือก cell ที่เขียนสูตรเอาไว้ (ในตัวอย่างนี้คือ B5)

To value – ระบุค่าเป้าหมายที่ต้องการ (ต้องการผ่อนเดือนละ 10,000)

By changing cell – ระบุ cell ที่ต้องการให้เปลี่ยนแปลงค่าตามเป้าหมายที่กำหนดไว้ (จำนวนเงินที่กู้ได้)

ภาพที่ 4 กำหนดค่าเป้าหมาย

สรุป ถ้าต้องการผ่อนเดือนละ 10,000 เป็นระยะเวลา 10 ปี จะต้องกู้เงินจำนวน 949,363 บาท (ดูภาพที่ 5)

ภาพที่ 5 คำตอบที่ได้ของเงื่อนไขที่ 1

เงื่อนไขที่ 2 : ถ้าต้องการกู้เงิน 1,000,000 บาท และผ่อนชำระเดือนละ 10,000 บาท จะต้องใช้เวลาเท่าไหร่? (ดูภาพที่ 6)

ภาพที่ 6 กำหนดค่าเป้าหมาย

สรุป ถ้าต้องการกู้เงิน 1,000,000 บาท และผ่อนชำระเดือนละ 10,000 บาท จะต้องใช้เวลาประมาณ 129 เดือน (ดูภาพที่ 7)

ภาพที่ 7 ระยะเวลาผ่อนชำระ(เดือน)

2. สถานการณ์สมมติ(Scenario)

ตัวอย่าง2 เปิดร้านขายเบเกอรี่มีเค้กขายอยู่ 12 ชนิด ในการวิเคราะห์กำไรจากการขาย (ดูภาพที่ 8) อาจแบ่งยอดขายได้หลายกรณี เช่น กรณีขายดี(Best Case), กรณีขายไม่ค่อยดี(Worst Case) และกรณีที่น่าจะเป็น(Most Likely) เป็นต้น

ภาพที่ 8 ตัวอย่างตารางข้อมูลสำหรับคำนวณโดยใช้ Scenario

ไปที่ Data > What-if Analysis > Scenario Manager (ดูภาพที่ 9) จะมีหน้าต่าง Edit Scenario ขึ้นมาให้กด Add…

Scenario Name ชื่อของ Scenario
Changing cells เป็นเซลล์ที่จะเปลี่ยนค่า ในตัวอย่างเป็นจำนวนเค้ก

โดยให้ทำการ Add ทั้ง 3 Case – Best Case(จำนวนมากกว่า 100 ชิ้น), Worst Case(อย่างละ 50 ชิ้น), Most Likely Case(อย่างละ 100 ชิ้น)

ภาพที่ 9 Add Scenario

ภาพที่ 10 ตัวอย่างกำหนดค่าให้ Most Likely

กดปุ่ม Summary เพื่อสรุปค่า Scenario จะมีหน้าต่าง Scenario Summary ขึ้นมา ให้เลือก Report type ที่จะแสดงในรายงาน ซึ่งมี 2 แบบ คือ

Scenario Summary เป็นตารางสรุปผล Scenario ในแต่ละกรณี (ดูภาพที่ 11)
Scenario PivotTable Report เป็น PivotTable แสดงรายละเอียดของ Scenraio

ภาพที่ 11 Scenario Summary

3. ตารางข้อมูล(Data Table)

จากตัวอย่าง1 หากต้องการทราบว่าช่วงเงินกู้ระหว่าง 800,000-1,700,000 บาท ถ้าให้ระยะเวลาผ่อน 10 ปีด้วยกันจะต้องผ่อนเดือนละเท่าไหร่??

ใส่ค่าช่วงของเงินกู้ที่ต้องการเปลี่ยนแปลง ส่วนจำนวนเงิน 10,533 ให้กด = แล้ว Link ไปที่ช่องผลลัพธ์ที่คำนวณไว้แล้ว ในที่นี้ = B5 ของตัวอย่างที่1 ลากคลุม cell ตามภาพที่ 12 จากนั้นไปที่ Data > What-if Analysis > Data Table… ที่นี้เราจะลองเปลี่ยนยอดเงินกู้ ซึ่งเรียงกันอยู่ในรูปแบบ Column ดังนั้นเราต้องใส่ค่า “จำนวนที่กู้” ลงใน Column Input Cell จะได้ผลลัพธ์ดังภาพที่ 13