What-if analysis (Phân tích giả định) là phương pháp mạnh mẽ để khám phá sự không chắc chắn và kiểm tra các kịch bản khác nhau trước khi ra quyết định quan trọng. Nó giúp đạt được kết quả kinh doanh hoặc nghiên cứu, như "Điều gì xảy ra nếu giá tăng?" hoặc "Chúng ta sẽ lãi bao nhiêu nếu chi phí giảm?" Các công cụ What-If Analysis của Excel giúp bạn khám phá các khả năng khác nhau mà không cần viết lại công thức.
Trong hướng dẫn này, chúng tôi chỉ ba kỹ thuật What-If analysis để dự báo và mô hình hóa kịch bản trong Excel.
Chúng ta sẽ khám phá ba kỹ thuật What-If analysis thiết yếu:
- Goal Seek: Làm việc ngược để tìm đầu vào cần thiết cho kết quả cụ thể
- Data Tables: Phân tích cách một hoặc hai biến số ảnh hưởng đến kết quả
- Scenario Manager: Lưu và so sánh nhiều tập giả định
1. Goal Seek: Tìm Đầu Vào Cho Kết Quả Mong Muốn
Goal Seek lý tưởng cho việc đảo ngược mô hình. Nó điều chỉnh một giá trị đầu vào để đạt được đầu ra cụ thể, giúp trả lời câu hỏi như "Tôi cần giá trị đầu vào nào để đạt kết quả mong muốn?"
Goal Seek dùng lặp để thay đổi một ô (đầu vào) cho đến khi ô phụ thuộc công thức (đầu ra) đạt giá trị mong muốn. Nó tuyệt vời cho tối ưu hóa đơn giản nhưng giới hạn ở một biến số.
Khi Nào Dùng Goal Seek:
- Xác định lượng bán cần để đạt mục tiêu lợi nhuận
- Tính lãi suất cần để đáp ứng ràng buộc trả vay
- Tìm điểm hòa vốn cho sản phẩm hoặc dịch vụ
- Xác định điểm cần đạt bài thi cuối kỳ để đạt xếp loại nhất định
Hãy tìm ra: "Chúng ta phải bán bao nhiêu đơn vị để kiếm $20,000 lợi nhuận?"
Các Bước:
- Thiết lập mô hình trong Excel (ví dụ: nhập chi tiết sản phẩm và công thức lợi nhuận)
- Chèn công thức sau để tính lợi nhuận
- Vào tab Data >> chọn What-If Analysis >> chọn Goal Seek
- Trong hộp thoại:
- Set cell: Chọn ô đầu ra (ví dụ: E2, công thức lợi nhuận)
- To value: Nhập đầu ra mục tiêu (ví dụ: lợi nhuận mục tiêu 20000)
- By changing cell: Chọn ô đầu vào để điều chỉnh (ví dụ: B2, Units Sold)
- Click OK
- Excel tự động lặp để điều chỉnh giá trị bán cho đến khi đạt mục tiêu lợi nhuận
Insight: Dùng Goal Seek khi công thức của bạn làm việc ngược từ mục tiêu đã biết (như hòa vốn hoặc mục tiêu bán hàng).
- Ưu điểm: Nhanh và đơn giản cho bài toán một biến
- Nhược điểm: Không xử lý nhiều biến; có thể không hội tụ nếu mô hình phức tạp
2. Data Table: Xem Cách Một Hoặc Hai Biến Ảnh Hưởng Kết Quả
Data Tables cho phép bạn thấy cách thay đổi một hoặc hai biến đầu vào ảnh hưởng đến kết quả, hiển thị tất cả khả năng trong một bảng duy nhất. Điều này tạo ra phân tích độ nhạy cho thấy phạm vi kết quả tiềm năng.
- Bảng một biến: Thay đổi một đầu vào dọc hàng hoặc cột; kiểm tra giá trị khác nhau cho một đầu vào đơn lẻ
- Bảng hai biến: Thay đổi hai đầu vào (một hàng, một cột) tạo ma trận; tính toán lại mô hình cho mỗi tổ hợp và giúp trực quan hóa xu hướng
Bảng Dữ Liệu Một Biến
Hãy xem giá đơn vị khác nhau ảnh hưởng lợi nhuận như thế nào. Lợi nhuận thay đổi ra sao nếu Đơn Giá tăng từ $40 lên $70?
Các Bước:
- Thiết lập mô hình
- Nhập các giá này dọc theo một cột
- Trong ô liền kề giá đầu tiên (ví dụ: B5), tham chiếu ô kết quả lợi nhuận chính (ví dụ: E2)
- Chọn toàn bộ phạm vi bảng, kể cả tiêu đề (ví dụ: A5:B12)
- Vào tab Data >> chọn What-If Analysis >> chọn Data Table
- Để Row input cell trống
- Trong Column input cell, chọn ô Đơn Giá gốc (A2)
- Click OK
- Excel tự động điền lợi nhuận cho mỗi giá
Diễn Giải:
- Cột kết quả cho thấy độ nhạy lợi nhuận theo giá
- Giá cao thường tăng lợi nhuận, đến điểm cầu giảm (nếu được mô hình hóa)
Bảng Dữ Liệu Hai Biến
Hãy xem: "Điều gì nếu cả Đơn Giá VÀ Số Lượng Bán đều thay đổi?"
Các Bước:
- Nhập giá dọc (cột trái) và số lượng bán ngang (hàng trên)
- Trong ô góc trên bên trái của bảng (trên 40 và trái 800), tham chiếu ô kết quả lợi nhuận chính (ví dụ: nhập =E2 trong D5)
- Chọn toàn bộ bảng, kể cả tiêu đề (ví dụ: D5:H9)
- Vào tab Data >> chọn What-If Analysis >> chọn Data Table
- Trong Row input cell, chọn Units Sold (B2)
- Trong Column input cell, chọn Unit Price (A2)
- Click OK
- Excel tạo ma trận đầy đủ hiển thị lợi nhuận dưới mọi tổ hợp giá–bán
Lưu ý: Đối với Data Table, ô đầu vào phải nằm trên cùng sheet với Data Table. Công thức dùng để điền bảng có thể ở sheet khác bằng tham chiếu.
Insight: Data Tables tốt nhất cho kiểm tra độ nhạy nhanh, như độ co giãn giá hoặc phân tích lượng-lợi nhuận.
- Ưu điểm: Trực quan hóa phạm vi; xử lý hiệu quả tới hai biến
- Nhược điểm: Có thể tốn tài nguyên tính toán cho bảng lớn; kết quả tĩnh cho đến khi làm mới
3. Scenario Manager: So Sánh Nhiều Mô Hình "What-If"
Scenario Manager cho phép bạn lưu và so sánh các tập giá trị đầu vào khác nhau (kịch bản) mà không ghi đè công thức. Bạn có thể nhanh chóng chuyển đổi giữa chúng hoặc tạo báo cáo tóm tắt so sánh tất cả kịch bản cạnh nhau. Nó phù hợp cho kịch bản phức tạp với nhiều biến, như dự báo tốt nhất/xấu nhất.
Khi Nào Dùng Nó:
- So sánh dự báo kinh doanh best-case, worst-case, và most-likely
- Đánh giá các lựa chọn chiến lược với nhiều giả định thay đổi
- Trình bày nhiều kịch bản cho các bên liên quan trong định dạng rõ ràng, có tổ chức
- Theo dõi các phiên bản mô hình khác nhau theo thời gian
Hãy tạo dự báo kinh doanh với ba kịch bản: Best Case, Base Case, và Worst Case.
Các Bước:
- Vào tab Data >> chọn What-If Analysis >> chọn Scenario Manager
- Click Add
- Scenario name: Đặt tên kịch bản đầu tiên (ví dụ: Best Case)
- Trong Changing cells, chọn Unit Price (A2), Units Sold (B2), Fixed Cost (C2), và Variable Cost (D2)
- Click OK
- Nhập giá trị cho kịch bản Best Case
- Unit Price: 65
- Units Sold: 1600
- Fixed Cost: 8500
- Variable Cost: 25
- Click OK
- Click Add lại và lặp lại cho "Base Case"
- Click Add lại và lặp lại cho "Worst Case"
- Chọn bất kỳ kịch bản nào >> click Show để áp dụng giá trị ngay lập tức
- Click Summary để hiển thị tất cả trường hợp
- Chọn Scenario summary
- Chọn ô lợi nhuận làm "Result cell" (ví dụ: E2)
- Click OK
- Excel tạo sheet Scenario Summary mới so sánh kết quả tất cả kịch bản
Insight: Bạn có thể dùng Scenario Manager What-If analysis để dự báo và mô hình hóa kịch bản trong Excel. Đây là một trong những công cụ ra quyết định tốt nhất (ví dụ: đầu tư, ngân sách, hoặc đánh giá rủi ro) nơi nhiều giả định phải được lưu và xem xét cùng nhau.
- Ưu điểm: Quản lý nhiều biến; so sánh dễ dàng với báo cáo
- Nhược điểm: Thiết lập thủ công; giới hạn 32 ô thay đổi mỗi kịch bản
Tải Workbook Thực Hành
Kết Luận
Hướng dẫn này chỉ cách dùng ba kỹ thuật What-If analysis để dự báo và mô hình hóa kịch bản trong Excel. What-If analysis biến sự không chắc chắn từ rào cản thành cơ hội bằng cách khám phá các khả năng khác nhau với Goal Seek, Data Tables, và Scenario Manager. Dù bạn đang lập ngân sách kinh doanh, đánh giá lựa chọn đầu tư, hoặc mô hình hóa kết quả dự án, các công cụ Excel này giúp bạn hiểu cách thay đổi trong giả định ảnh hưởng đến kết quả của bạn.
Nhận MIỄN PHÍ Bài Tập Excel Nâng Cao Có Giải Pháp!