What-If Analysis trong Excel: Goal Seek, Scenario Manager và Data Tables

Bạn cung cấp ba thứ:

• Ô đặt: ô chứa công thức của bạn (đầu ra)
• Giá trị cần đạt: kết quả mục tiêu bạn muốn
• Bằng cách thay đổi ô: ô đầu vào Excel được phép điều chỉnh

Hãy xem thực hành. 

Bạn kinh doanh nhỏ mặt hàng sổ tay. Doanh thu hiện tại là $25,000, nhưng mục tiêu là $50,000. Giá bán cố định ở $25, nên bạn cần biết phải bán bao nhiêu đơn vị. 

Cách thực hiện:

1. Vào Data > What-If Analysis > Goal Seek

2. Trong ô Set, bấm vào ô B4 (ô công thức doanh thu)

3. Trong ô To value, nhập 50000

4. Trong By changing cell, chọn B2 (Units Sold)

5. Bấm OK

Goal Seek điều chỉnh B2 từ 1.000 lên 2.000 đơn vị — đúng số lượng cần để đạt doanh thu $50.000 với giá $25 mỗi đơn vị.

Dùng Goal Seek để đạt một kết quả cụ thể. Ảnh: Tác giả.

Cách dùng Scenario Manager trong Excel

Scenario Manager là công cụ What-If Analysis cho phép bạn lưu và chuyển đổi giữa nhiều bộ giá trị đầu vào trong cùng một bảng tính. Mỗi phiên bản lưu lại được gọi là một kịch bản. 

Bạn có thể tạo bao nhiêu cũng được và chuyển đổi ngay lập tức để so sánh kết quả. 

Hữu ích khi bạn muốn lập kế hoạch cho kịch bản tốt nhất, xấu nhất và kỳ vọng mà không cần giữ ba tệp riêng. 

Bạn cung cấp ba thứ:

• Tên kịch bản: nhãn cho mỗi bộ giả định
• Ô thay đổi: các ô đầu vào khác nhau giữa các kịch bản
• Giá trị: các con số cụ thể cho từng kịch bản

Tạo kịch bản

Giả sử bạn đang lập kế hoạch doanh số theo quý cho kinh doanh sổ tay.

Giả định lợi nhuận hiện tại là $15,000. Bạn muốn mô phỏng ba kịch bản: tốt nhất, xấu nhất và kỳ vọng. 

Cách tạo một kịch bản:

1. Vào Data > What-If Analysis > Scenario Manager

2. Trong hộp thoại Scenario Manager, bấm Add

3. Trong Scenario name, nhập Best Case.

4. Trong Changing cells, chọn B2:B4.

5. Một hộp thoại Scenario Value sẽ xuất hiện. Trong các ô, nhập các giá trị sau:

• B2 = 2000, 

• B3 = $30

• B4= $10

6. Bấm OK. 

Lặp lại các bước này cho hai kịch bản còn lại với các giá trị dưới đây:

Tạo một kịch bản với ba trường hợp. Ảnh: Tác giả.

Để chuyển giữa các kịch bản, chọn một kịch bản từ danh sách và bấm Show. Excel thay các giá trị ngay lập tức, và lợi nhuận của bạn sẽ cập nhật.

Bấm Show để xem giá trị của từng kịch bản. Ảnh: Tác giả.

Chỉnh sửa kịch bản

Chọn bất kỳ kịch bản nào từ danh sách và bấm Edit. Bạn có thể đổi tên, thay ô áp dụng hoặc cập nhật giá trị. Không có gì thay đổi trong bảng tính cho đến khi bạn bấm Show

Tạo báo cáo kịch bản

Scenario Manager cũng có thể tạo một báo cáo so sánh tất cả kịch bản đã lưu cạnh nhau. 

Để tạo báo cáo kịch bản:

1. Trong Scenario Manager, bấm Summary

2. Trong Scenario Summary, chọn Scenario Summary

3. Trong Result cells, chọn B7 (ô lợi nhuận)

4. Bấm OK

Excel tạo một bảng tóm tắt trên trang tính mới hiển thị cả ba kịch bản cạnh nhau

Tạo báo cáo tóm tắt kịch bản. Ảnh: Tác giả.

Cách dùng Data Tables trong Excel

Data Tables tự động hóa các phép tính lặp lại trên một dải giá trị đầu vào. Thay vì thay đổi đầu vào thủ công, ghi nhận đầu ra rồi lặp lại, bạn xác định dải một lần và Excel điền mọi kết quả cùng lúc.

Bạn cung cấp ba thứ:

• Dải đầu vào: các giá trị bạn muốn kiểm thử
• Ô đầu vào hàng/cột: ô trong công thức mà các giá trị đó sẽ thay thế
• Ô công thức: đầu ra mà bảng của bạn xoay quanh

Data Table một biến

Data Table một biến kiểm thử việc thay đổi một đầu vào ảnh hưởng đến kết quả công thức ra sao.

Ví dụ, bạn có công thức khoản trả vay và muốn xem khoản trả hàng tháng thay đổi thế nào khi lãi suất từ 5% đến 10%.

Để tạo Data Table một biến:

1. Thiết lập bộ dữ liệu. Lưu ý: trong bộ dữ liệu của tôi, tại ô E1, ngay trên mức lãi đầu tiên, không phải cạnh tiêu đề cột, tôi nhập =B5. Nếu để trống E1 hoặc tham chiếu công thức đặt ở ô khác, data table vẫn chạy nhưng trả về toàn số 0 thay vì giá trị khoản trả.

Tạo Data Table một biến. Ảnh: Tác giả.

2. Tiếp theo, chọn toàn bộ vùng D1:E9 (bao gồm E1)

3. Vào Data > What-If Analysis > Data Table

4. Để trống ô đầu vào Row

5. Trong ô đầu vào Column, chọn B4 (lãi suất)

6. Bấm OK.

Excel điền bảng bằng các kết quả tính toán cho từng giá trị đầu vào. Hãy lưu ý khoản trả không tăng đều. Mức tăng từ 3% lên 4% là $49, nhưng từ 9% lên 10% là $61. Lãi suất càng cao thì tác động của thay đổi càng mạnh.

Kết quả tính cho mỗi giá trị đầu vào. Ảnh: Tác giả.

Data Table hai biến

Data Table hai biến kiểm thử việc thay đổi đồng thời hai đầu vào ảnh hưởng thế nào đến kết quả công thức.

Ví dụ, một mô hình định giá có thể phụ thuộc cả giá sản phẩm và số đơn vị bán. Data Table hai biến có thể hiển thị lợi nhuận theo nhiều mức giá và sản lượng bán trong một khung nhìn.

Đầu tiên, thiết lập bộ dữ liệu. 

Xem bộ dữ liệu tôi đang dùng:

• Tại ô D1, nhập =B5. Đây là tham chiếu công thức, và phải đặt ở góc trên bên trái của lưới, không đặt cạnh nhãn

• Trên hàng 1, bắt đầu từ E1, liệt kê các giá trị số đơn vị bán muốn kiểm thử: 500, 1000, 1500, 2000

• Ở cột D, bắt đầu từ D2, liệt kê các mức giá muốn kiểm thử: $20, $25, $30, $35

Thiết lập bộ dữ liệu cho Data Table hai biến. Ảnh: Tác giả.

Để tạo Data Table hai biến:

1. Chọn toàn bộ vùng D1:H5.

2. Vào Data > What-If Analysis > Data Table

3. Trong ô Row input cell, chọn B3 (số đơn vị bán)

4. Trong ô Column input cell, chọn B2 (giá mỗi đơn vị)

5. Bấm OK.

Excel điền lợi nhuận cho mọi kết hợp giá và số đơn vị bán

Kết quả tính cho giá trị đầu vào. Ảnh: Tác giả.

Data Table hai biến đặc biệt hữu ích khi một giả định đơn lẻ không kể hết câu chuyện. Giá có thể trông ổn khi đứng một mình, nhưng lợi nhuận còn phụ thuộc vào số lượng khách hàng thực sự mua. Biến số thứ hai đó làm cho mô hình trở nên hữu ích hơn.

Goal Seek so với Scenario Manager và Data Tables

Goal Seek, Scenario Manager và Data Tables đều thuộc bộ công cụ What-If Analysis của Excel, nhưng giải quyết các vấn đề khác nhau. 

Dưới đây là so sánh nhanh giữa chúng 

What-If Analysis so với Solver

What-If Analysis và Solver đều giúp chúng ta khám phá các kết quả khác nhau trong Excel, nhưng chúng giải quyết các loại vấn đề khác nhau.

• What-If Analysis kiểm thử giả định. Bạn thay đổi một hoặc nhiều đầu vào và xem những thay đổi đó ảnh hưởng đến kết quả ra sao. Mục tiêu là hiểu các khả năng kết quả chứ không phải tìm phương án tốt nhất.
• Solver hoạt động khác. Bạn đưa ra một mục tiêu, như tối đa hóa lợi nhuận hoặc tối thiểu hóa chi phí, và nó tìm tổ hợp đầu vào đáp ứng tốt nhất mục tiêu đó. Nó không kiểm thử một giả định; nó giải cho phương án tối ưu. 

Ví dụ, một người vay có thể dùng Goal Seek để xác định khoản vay dẫn đến khoản trả $500 mỗi tháng. Nhưng một công ty sản xuất có thể dùng Solver để xác định cơ cấu sản xuất tạo lợi nhuận cao nhất trong khi vẫn tuân thủ giới hạn lao động và vật liệu. 

Lưu ý: Solver không thuộc bộ công cụ What-If Analysis tiêu chuẩn. Đây là một phần bổ trợ riêng. Trên hầu hết các phiên bản Excel, bạn cần bật thủ công trong Excel Options trước khi nó xuất hiện trên tab Data.

Thực hành tốt với What-If Analysis

Dưới đây là một số thực hành tốt khi làm việc với What-If Analysis: 

1. Tách biệt giả định khỏi công thức

Lưu giá trị đầu vào trong các ô riêng và tham chiếu các ô đó trong công thức. Tránh chèn cứng số trực tiếp vào phép tính. 

Ví dụ, công thức này khó cập nhật:

=(50*1000)-10000

Nhưng phiên bản này dễ bảo trì hơn nhiều:

=(B2*B3)-B4

Khi giả định thay đổi, bạn chỉ cần cập nhật các ô đầu vào thay vì chỉnh sửa công thức khắp sổ làm việc.

2. Gắn nhãn kịch bản rõ ràng

Tên kịch bản nên cho người đọc biết ngay chúng đại diện cho điều gì. "Scenario 1," "Scenario 2," và "Scenario 3" sẽ vô nghĩa sau sáu tháng. Hãy đặt tên theo đúng nội dung, như "Best Case: Tăng trưởng 15%." 

3. Xác thực đầu vào

What-If Analysis chỉ đáng tin cậy khi đầu vào được kiểm thử đáng tin cậy. Trước khi chạy phân tích, hãy kiểm tra rằng:

• Phần trăm được nhập đúng
• Ngày tháng dùng định dạng nhất quán
• Đơn vị đo lường nhất quán
• Giá trị đầu vào nằm trong phạm vi thực tế

Một dự báo doanh thu dựa trên mức tăng 500% có thể đúng về mặt kỹ thuật nhưng vẫn vô dụng cho việc ra quyết định.

4. Ghi chú giả định

Một mô hình hôm nay bạn hiểu rõ không đảm bảo sau ba tháng bạn hay người khác còn hiểu. 

Một ghi chú ngắn cạnh mỗi giả định, dù chỉ "dựa trên số liệu thực tế Q3," sẽ tiết kiệm rất nhiều phỏng đoán sau này. Việc ghi chú giúp người khác hiểu nguồn gốc số liệu và giúp cập nhật trong tương lai dễ dàng hơn.

Vấn đề thường gặp với What-If Analysis

Hầu hết lỗi trong What-If Analysis đến từ cách thiết lập bảng tính hơn là bản thân công cụ. Nếu Goal Seek, Scenario Manager hoặc Data Tables cho ra kết quả bất ngờ, hãy kiểm tra vài điểm lỗi phổ biến; thường sẽ giải quyết được vấn đề.

1. Công thức không cập nhật 

Bạn thay đổi đầu vào mà không có gì xảy ra. Thường là do chế độ tính toán của Excel đã bị chuyển sang thủ công ở đâu đó. 

Cách khắc phục: vào Formulas > Calculation Options và đảm bảo đặt Automatic. Nếu đã là Automatic, nhấn F9 để buộc tính lại.

2. Tham chiếu ô không chính xác

Các công cụ What-If Analysis phụ thuộc vào tham chiếu ô chính xác. Nếu chọn sai ô, kết quả sẽ sai dù Excel vẫn tính toán xong. 

Ví dụ, Goal Seek có thể thay sai ô đầu vào. Một Data Table có thể tham chiếu sai ô đầu vào hàng hoặc cột. Scenario Manager có thể lưu giá trị cho sai ô.

Cách khắc phục: Rà soát các ô đầu vào trước khi chạy phân tích, kiểm tra kỹ nội dung đã chọn trong hộp thoại của từng công cụ, và dùng nhãn mô tả để dễ hiểu mỗi ô đại diện cho điều gì. 

3. Hiểu sai giới hạn của Goal Seek

Nhiều người dùng kỳ vọng Goal Seek giải bài toán có nhiều biến thay đổi. Không thể. Goal Seek chỉ thay đổi một ô đầu vào tại một thời điểm để đạt giá trị mục tiêu. 

Ví dụ, Goal Seek có thể xác định sản lượng bán cần để tạo lợi nhuận $15.000. Nó không thể đồng thời điều chỉnh sản lượng, giá và chi phí để đạt mục tiêu đó.

Cách khắc phục: Dùng Goal Seek khi chỉ cần thay một biến. Dùng Scenario Manager hoặc Solver khi có nhiều biến. 

4. Mô hình kịch bản quá phức tạp

Về mặt kỹ thuật, Scenario Manager cho phép tối đa 32 ô thay đổi mỗi kịch bản, nhưng không phải vì có thể là nên làm. 

Vượt quá bốn hoặc năm biến, kịch bản sẽ khó so sánh và còn khó giải thích cho người khác hơn. Nếu mô hình của bạn cần quá nhiều thành phần chuyển động, có lẽ đã đến lúc tái cấu trúc thay vì nhồi tất cả vào một kịch bản.

Kết luận 

Mọi bảng tính đều có các giả định ở đâu đó. Có thể là tỷ lệ tăng trưởng, mức giá hoặc lãi suất.

What-If Analysis không xóa bỏ các giả định đó. Nó cho bạn thấy điều gì xảy ra nếu chúng sai, và đó chính là phần lớn bản chất của việc ra quyết định tốt.

Nếu bạn mới bắt đầu, đừng khởi động với một mô hình có năm biến và ba kịch bản lồng nhau. Hãy xây thứ gì đó nhỏ trước: một đầu vào, một đầu ra, một lần Goal Seek, và hiểu cách đầu vào và kết quả di chuyển cùng nhau. Những thứ phức tạp có thể để sau, khi đã nắm vững nền tảng.