Scenario Manager như mình đã giới thiệu trong bài viết trước là một công cụ khá “xịn” trong Excel, cho phép bạn so sánh nhiều lựa chọn một cách trực quan.
Cách dùng Scenario Manager trong Excel

So sánh nhiều phương án với Scenario Manager

spiderum.com
Tuy nhiên, trong quá trình sử dụng thực tế, bạn sẽ gặp phải một số điểm hạn chế:
Thay đổi thông số đầu vào: Ví dụ, khi muốn điều chỉnh Chi phí cố định trong một kịch bản, bạn phải mở lại Scenario Manager, chỉnh sửa thủ công rồi chạy lại.
Thêm chỉ số đầu ra: Nếu bạn cần phân tích thêm một chỉ tiêu như Thuế phải trả, bạn cũng sẽ phải thiết lập lại công cụ để bổ sung đầu ra đó.
Để giải quyết những hạn chế này, mình sẽ giới thiệu một phương pháp thay thế bằng cách thiết lập một hệ thống mô phỏng kịch bản ngay trong bảng tính. Dù tốn công hơn lúc đầu, nhưng một khi đã hoàn thiện, bạn sẽ có một bảng tính cực kỳ linh hoạt, chỉ cần chọn kịch bản từ danh sách thả xuống và toàn bộ thông số cũng như kết quả phân tích sẽ được cập nhật tự động.

Thiết Lập Hệ Thống Kịch Bản Linh Hoạt trong Excel

Để xây dựng hệ thống này, bạn sẽ cần sử dụng hai công cụ trong Excel:
DATA VALIDATION – để tạo danh sách kịch bản có thể lựa chọn
XLOOKUP – để tự động truy xuất dữ liệu theo từng kịch bản

1. Tạo danh sách lựa chọn bằng Data Validation

Bạn chọn một ô bất kỳ (ví dụ E13) và thực hiện như sau: Vào tab Data > chọn Data Validation
Ở cửa sổ hiện ra:
Allow: chọn List Source: chọn vùng chứa tên các kịch bản (ví dụ: F3:H3)
Kết quả: bạn đã tạo được một danh sách thả xuống gồm các tên kịch bản để lựa chọn

2. Tra cứu dữ liệu bằng công thức XLOOKUP

Sau khi chọn một kịch bản, bạn cần Excel tự động cập nhật các thông số đầu vào tương ứng. Lúc này, bạn sử dụng hàm XLOOKUP với cú pháp (các thông số khác là optional):
=XLOOKUP(Giá_trị_tham_chiếu, Vùng_tham_chiếu, Vùng_trả_về)
Cụ thể, ví dụ tại ô C3, bạn nhập:
=XLOOKUP(E13, F3:H3, F4:H4, , 0)
Trong đó:
E13: là ô bạn chọn kịch bản F3:H3: là hàng chứa tên các kịch bản F4:H4: là hàng chứa giá trị đầu vào tương ứng với từng kịch bản
Mỗi khi bạn thay đổi kịch bản ở E13, công thức sẽ tự động lấy giá trị phù hợp từ bảng dữ liệu và cập nhật vào ô tương ứng. Mục Số lượng đã được cập nhật thành 20,000.

3. Tự động áp dụng cho toàn bộ giá trị đầu vào

Để không phải viết lại công thức cho từng ô, bạn có thể khoá công thức bằng cách sử dụng ký hiệu $. Ví dụ:
=XLOOKUP($E$13, $F$3:$H$3, F4:H4, , 0)
Sau đó, bạn chỉ cần sao chép công thức này xuống các ô còn lại như C4, C5,… C9. Với cách khoá như trên:
$E$13: cố định ô chọn kịch bản $F$3:$H$3: cố định hàng chứa tên kịch bản F4:H4: không cố định, để công thức tự động lấy dữ liệu dòng tương ứng khi copy

4. Kết quả sau khi thiết lập

Khi bạn chọn một kịch bản khác trong ô E13, toàn bộ giá trị đầu vào và chỉ số kinh doanh liên quan sẽ được cập nhật theo dữ liệu đã thiết lập sẵn cho kịch bản đó.
Ví dụ: bạn muốn thay đổi chi phí cố định của kịch bản “Base” từ 1.000.000.000 đồng lên 1.200.000.000 đồng, và đồng thời muốn xem thuế phải trả trong kịch bản này. Với hệ thống này, bạn chỉ cần chỉnh sửa giá trị ở ô F7 – Excel sẽ tự động cập nhật tất cả kết quả liên quan, bao gồm thuế và lợi nhuận, mà không cần chạy lại bất kỳ công cụ nào.
File tham khảo:
Đang tải Google Trang tính

docs.google.com
Kỹ năng nghề nghiệp Tự học Ngành Kinh tế