เชี่ยวชาญ What-If Analysis ใน Excel: 3 เทคนิคพิสูจน์แล้วสำหรับการพยากรณ์และสร้างโมเดลธุรกิจ

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

ในบทช่วยสอนนี้ เราแสดง 3 เทคนิค What-If analysis สำหรับพยากรณ์และสร้างโมเดลสถานการณ์ใน Excel

เราจะสำรวจ 3 เทคนิค What-If analysis ที่จำเป็น:

• Goal Seek: ทำงานย้อนกลับเพื่อหาค่า input ที่ต้องการสำหรับผลลัพธ์เฉพาะ
• Data Tables: วิเคราะห์ว่าตัวแปรหนึ่งหรือสองส่งผลต่อผลลัพธ์อย่างไร
• Scenario Manager: บันทึกและเปรียบเทียบหลายชุดสมมติฐาน

1. Goal Seek: หา Input สำหรับผลลัพธ์ที่ต้องการ

Goal Seek เหมาะสำหรับ reverse-engineering โมเดล มันปรับค่า input เดียวเพื่อให้ได้ output ที่เฉพาะเจาะจง ช่วยตอบคำถามอย่าง "ฉันต้องการค่า input เท่าไหร่เพื่อให้ได้ผลลัพธ์ที่ต้องการ?"

Goal Seek ใช้ iteration เปลี่ยนเซลล์หนึ่ง (input) จนกว่าเซลล์ที่ขึ้นอยู่กับสูตร (output) จะถึงค่าที่คุณต้องการ มันเหมาะสำหรับ optimization ง่ายๆ แต่จำกัดเพียงตัวแปรเดียว

เมื่อไหร่ควรใช้ Goal Seek:

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

ลองหาว่า: "เราต้องขายกี่หน่วยเพื่อได้กำไร $20,000?"

ขั้นตอน:

• ตั้งค่าโมเดลใน Excel (เช่น ใส่รายละเอียดผลิตภัณฑ์และสูตรกำไร)
• ใส่สูตรต่อไปนี้เพื่อคำนวณกำไร

• ไปที่แท็บ Data >> เลือก What-If Analysis >> เลือก Goal Seek

• ในกล่องโต้ตอบ:
  • Set cell: เลือกเซลล์ output (เช่น E2, สูตรกำไร)
  • To value: ใส่ค่าเป้าหมาย output (เช่น target profit 20000)
  • By changing cell: เลือกเซลล์ input ที่จะปรับ (เช่น B2, Units Sold)
• คลิก OK

• Excel ทำ iteration อัตโนมัติเพื่อปรับค่ายอดขายจนถึงกำไรเป้าหมาย

ข้อมูลเชิงลึก: ใช้ Goal Seek เมื่อสูตรของคุณทำงานย้อนกลับจากเป้าหมายที่ทราบ (เช่น break-even หรือ target sales)

• ข้อดี: รวดเร็วและตรงไปตรงมาสำหรับปัญหาตัวแปรเดียว
• ข้อเสีย: ไม่จัดการหลายตัวแปรได้ อาจไม่ converge ถ้าโมเดลซับซ้อน

2. Data Table: ดูว่าตัวแปรหนึ่งหรือสองส่งผลต่อผลลัพธ์อย่างไร

Data Tables ให้คุณเห็นว่าการเปลี่ยนแปลงตัวแปร input หนึ่งหรือสองตัวส่งผลต่อผลลัพธ์อย่างไร แสดงความเป็นไปได้ทั้งหมดในตารางเดียว นี่สร้าง sensitivity analysis ที่แสดงช่วงของผลลัพธ์ที่เป็นไปได้

• One-variable table: เปลี่ยน input หนึ่งตัวข้ามแถวหรือคอลัมน์ ทดสอบค่าต่างๆ สำหรับ input เดียว
• Two-variable table: เปลี่ยนสอง inputs (หนึ่งแถว หนึ่งคอลัมน์) สร้างเมทริกซ์ คำนวณโมเดลใหม่สำหรับแต่ละการจับคู่ และช่วยแสดงแนวโน้ม

One-Variable Data Table

ลองดูว่าราคาต่อหน่วยต่างๆ ส่งผลต่อกำไรอย่างไร กำไรเปลี่ยนอย่างไรถ้า Unit Price เพิ่มจาก $40 ถึง $70?

ขั้นตอน:

• ตั้งค่าโมเดลของคุณ
  • ใส่ราคาเหล่านี้แนวตั้งในคอลัมน์หนึ่ง
  • ในเซลล์ติดกับราคาแรก (เช่น B5) อ้างอิงเซลล์ผลลัพธ์กำไรหลัก (เช่น E2)

• เลือกช่วงตารางทั้งหมด รวมถึงส่วนหัว (เช่น A5:B12)
• ไปที่แท็บ Data >> เลือก What-If Analysis >> เลือก Data Table

• เว้น Row input cell ว่าง
• ใน Column input cell เลือกเซลล์ Unit Price ต้นฉบับ (A2)
• คลิก OK

• Excel เติมกำไรสำหรับแต่ละราคาอัตโนมัติ

การตีความ:

• คอลัมน์ที่ได้แสดง sensitivity ของกำไรต่อราคา
• ราคาสูงมักเพิ่มกำไร จนถึงจุดที่ความต้องการลดลง (ถ้ามีในโมเดล)

Two-Variable Data Table

ลองดู: "ถ้า Unit Price และ Units Sold เปลี่ยนทั้งคู่ จะเป็นอย่างไร?"

ขั้นตอน:

• ใส่ราคาแนวตั้ง (คอลัมน์ซ้าย) และ units sold แนวนอน (แถวบน)
  • ในเซลล์มุมซ้ายบนของตาราง (เหนือ 40 และซ้าย 800) อ้างอิงเซลล์ผลลัพธ์กำไรหลัก (เช่น ใส่ =E2 ใน D5)

• เลือกตารางทั้งหมด รวมถึงส่วนหัว (เช่น D5:H9)
• ไปที่แท็บ Data >> เลือก What-If Analysis >> เลือก Data Table
• ใน Row input cell เลือก Units Sold (B2)
• ใน Column input cell เลือก Unit Price (A2)
• คลิก OK

• Excel สร้างเมทริกซ์เต็มรูปแบบแสดงกำไรภายใต้ทุกการจับคู่ราคา-ยอดขาย

หมายเหตุ: สำหรับ Data Table เซลล์ input ต้องอยู่บนชีทเดียวกับ Data Table สูตรที่ใช้เติมตารางสามารถอยู่บนชีทอื่นได้โดยการอ้างอิง

ข้อมูลเชิงลึก: Data Tables เหมาะสำหรับ sensitivity testing รวดเร็ว เช่น price elasticity หรือ volume-profit analysis

• ข้อดี: แสดงช่วงภาพชัด จัดการได้ถึงสองตัวแปรอย่างมีประสิทธิภาพ
• ข้อเสีย: อาจใช้ทรัพยากรคำนวณมากสำหรับตารางใหญ่ ผลลัพธ์เป็น static จนกว่าจะ refresh

3. Scenario Manager: เปรียบเทียบโมเดล "What-If" หลายแบบ

Scenario Manager ให้คุณบันทึกและเปรียบเทียบชุดค่า input ที่ต่างกัน (scenarios) โดยไม่เขียนทับสูตร คุณสามารถสลับระหว่างพวกมันได้อย่างรวดเร็ว หรือสร้างรายงานสรุปเปรียบเทียบทุก scenario ดانๆ กัน เหมาะสำหรับสถานการณ์ซับซ้อนที่มีหลายตัวแปร เช่น การพยากรณ์ best/worst-case

เมื่อไหร่ควรใช้:

• เปรียบเทียบการคาดการณ์ธุรกิจ best-case, worst-case, และ most-likely
• ประเมินตัวเลือกเชิงกลยุทธ์ที่แตกต่างกันพร้อมสมมติฐานที่เปลี่ยนหลายตัว
• นำเสนอหลาย scenarios ให้ผู้มีส่วนได้ส่วนเสียในรูปแบบชัดเจน จัดระเบียบ
• ติดตามเวอร์ชันต่างๆ ของโมเดลตามเวลา

มาสร้างการคาดการณ์ธุรกิจด้วย 3 scenarios: Best Case, Base Case, และ Worst Case

ขั้นตอน:

• ไปที่แท็บ Data >> เลือก What-If Analysis >> เลือก Scenario Manager

• คลิก Add
  • Scenario name: ตั้งชื่อ scenario แรก (เช่น Best Case)
  • ใน Changing cells เลือก Unit Price (A2), Units Sold (B2), Fixed Cost (C2), และ Variable Cost (D2)
  • คลิก OK

• ใส่ค่าสำหรับ scenario Best Case
  • Unit Price: 65
  • Units Sold: 1600
  • Fixed Cost: 8500
  • Variable Cost: 25
• คลิก OK

• คลิก Add อีกครั้ง และทำซ้ำสำหรับ "Base Case"

• คลิก Add อีกครั้ง และทำซ้ำสำหรับ "Worst Case"

• เลือก scenario ใดๆ >> คลิก Show เพื่อใช้ค่าทันที
• คลิก Summary เพื่อแสดงทุก case

• เลือก Scenario summary
• เลือกเซลล์กำไรเป็น "Result cell" (เช่น E2)
• คลิก OK

• Excel สร้างชีท Scenario Summary ใหม่เปรียบเทียบผลลัพธ์ทุก scenario

ข้อมูลเชิงลึก: คุณสามารถใช้ Scenario Manager What-If analysis เพื่อพยากรณ์และสร้างโมเดลสถานการณ์ใน Excel มันเป็นหนึ่งในเครื่องมือตัดสินใจที่ดีที่สุด (เช่น การลงทุน งบประมาณ หรือการประเมินความเสี่ยง) ที่หลายสมมติฐานต้องบันทึกและทบทวนร่วมกัน

• ข้อดี: จัดการหลายตัวแปร ได้รายงานเปรียบเทียบง่าย
• ข้อเสีย: ตั้งค่าด้วยมือ จำกัด 32 changing cells ต่อ scenario

ดาวน์โหลด Practice Workbook

สรุป

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