Phân tích điều gì xảy ra với trình quản lý kịch bản

Trình quản lý tình huống hữu ích trong trường hợp bạn có nhiều hơn hai biến trong phân tích độ nhạy. Trình quản lý kịch bản tạo các kịch bản cho từng tập hợp các giá trị đầu vào cho các biến đang được xem xét. Các kịch bản giúp bạn khám phá một tập hợp các kết quả có thể xảy ra, hỗ trợ những điều sau:

Thay đổi tối đa 32 bộ đầu vào.
Hợp nhất các tình huống từ một số trang tính hoặc sổ làm việc khác nhau.

Nếu bạn muốn phân tích hơn 32 bộ đầu vào và các giá trị chỉ đại diện cho một hoặc hai biến, bạn có thể sử dụng Bảng dữ liệu. Mặc dù nó chỉ giới hạn ở một hoặc hai biến, một Bảng Dữ liệu có thể bao gồm nhiều giá trị đầu vào khác nhau tùy thích. Tham khảo Phân tích Điều gì xảy ra với Bảng Dữ liệu trong hướng dẫn này.

Các tình huống

Kịch bản là một tập hợp các giá trị mà Excel lưu và có thể thay thế tự động trên trang tính của bạn. Bạn có thể tạo và lưu các nhóm giá trị khác nhau dưới dạng kịch bản trên trang tính, sau đó chuyển đổi giữa các tình huống này để xem các kết quả khác nhau.

Ví dụ: bạn có thể có một số kịch bản ngân sách khác nhau để so sánh các mức thu nhập và chi phí có thể có. Bạn cũng có thể có các tình huống cho vay khác nhau từ các nguồn khác nhau để so sánh các mức lãi suất và thời hạn vay khác nhau.

Nếu thông tin bạn muốn sử dụng trong các tình huống là từ các nguồn khác nhau, bạn có thể thu thập thông tin trong các sổ làm việc riêng biệt, sau đó hợp nhất các kịch bản từ các sổ làm việc khác nhau thành một.

Sau khi có tất cả các tình huống bạn cần, bạn có thể tạo một báo cáo tóm tắt kịch bản -

Điều đó kết hợp thông tin từ tất cả các tình huống.
Điều đó cho phép bạn so sánh các tình huống song song với nhau.

Trình quản lý kịch bản

Trình quản lý tình huống là một trong những công cụ Phân tích điều gì xảy ra trong Excel.

Để tạo báo cáo phân tích với Trình quản lý tình huống, bạn phải làm theo các bước sau:

Step 1 - Xác định tập hợp các giá trị ban đầu và xác định các ô đầu vào mà bạn muốn thay đổi, được gọi là các ô thay đổi.

Step 2 - Tạo từng kịch bản, đặt tên cho kịch bản và nhập giá trị cho mỗi ô đầu vào thay đổi cho kịch bản đó.

Step 3- Chọn các ô đầu ra, được gọi là các ô kết quả mà bạn muốn theo dõi. Các ô này chứa các công thức trong tập giá trị ban đầu. Các công thức sử dụng các ô đầu vào thay đổi.

Trình quản lý Kịch bản tạo một báo cáo chứa các giá trị đầu vào và đầu ra cho mỗi kịch bản.

Giá trị ban đầu cho các tình huống

Trước khi tạo một số kịch bản khác nhau, bạn cần xác định một tập hợp các giá trị ban đầu mà các kịch bản sẽ dựa trên đó.

Các bước để thiết lập các giá trị ban đầu cho các Kịch bản là:

Xác định các ô chứa giá trị đầu vào.
Đặt tên cho các ô đầu vào một cách thích hợp.
Xác định các ô đầu vào có giá trị không đổi.
Chỉ định các giá trị cho các đầu vào không đổi.
Xác định các ô đầu vào có giá trị thay đổi.
Chỉ định các giá trị ban đầu cho các đầu vào thay đổi.
Xác định các ô chứa kết quả. Các ô kết quả chứa công thức.
Đặt tên cho các ô kết quả một cách thích hợp.
Đặt công thức vào các ô kết quả.

Hãy xem xét ví dụ trước về khoản vay. Bây giờ, hãy tiến hành như sau:

Xác định ô cho Số tiền cho vay.
- Giá trị đầu vào này là không đổi cho tất cả các tình huống.
- Đặt tên cho ô Loan_Amount.
- Chỉ định giá trị là 5.000.000.
Xác định các ô cho Lãi suất, Số lần thanh toán và Loại (Thanh toán vào đầu hoặc cuối tháng).
- Các giá trị đầu vào này sẽ thay đổi trong các tình huống.
- Đặt tên cho các ô Lãi_phẩm, NPER và Loại.
- Chỉ định các giá trị ban đầu cho phép phân tích trong các ô này lần lượt là 12%, 360 và 0.
Xác định ô cho EMI.
- Đây là giá trị kết quả.
- Đặt tên cho ô EMI.
- Đặt công thức trong ô này là -
  
  =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

Trang tính của bạn trông như hình dưới đây -

Như bạn có thể thấy rằng các ô đầu vào và các ô kết quả nằm trong cột C với tên như đã cho trong cột D.

Tạo kịch bản

Sau khi thiết lập các giá trị ban đầu cho Kịch bản, bạn có thể tạo các kịch bản bằng Trình quản lý tình huống như sau:

Nhấp vào tab DATA trên Ribbon.
Nhấp vào Phân tích điều gì xảy ra trong nhóm Công cụ dữ liệu.
Chọn Trình quản lý kịch bản từ danh sách thả xuống.

Hộp thoại Trình quản lý Kịch bản xuất hiện. Bạn có thể quan sát thấy nó chứa một thông báo -

“No Scenarios defined. Choose Add to.”

Bạn cần tạo kịch bản cho từng tập hợp giá trị thay đổi trong Trình quản lý kịch bản. Sẽ rất tốt nếu kịch bản đầu tiên được xác định với các giá trị ban đầu, vì nó cho phép bạn chuyển về các giá trị ban đầu bất cứ khi nào bạn muốn trong khi hiển thị các kịch bản khác nhau.

Tạo kịch bản đầu tiên với các giá trị ban đầu như sau:

Nhấn vào Add trong hộp thoại Trình quản lý kịch bản.

Các Add Scenario hộp thoại xuất hiện.

Trong Tên kịch bản, nhập Tình huống 1.
Trong Thay đổi ô, nhập các tham chiếu cho các ô tức là C3, C4 và C5 bằng phím Ctrl được nhấn.

Tên của hộp thoại thay đổi thành Chỉnh sửa Kịch bản.

Chỉnh sửa văn bản trong Comment as – Initial Values cái hộp.
Chọn tùy chọn Ngăn chặn thay đổi trong Bảo vệ và sau đó bấm OK.

Các Scenario Valueshộp thoại xuất hiện. Các giá trị ban đầu mà bạn đã xác định xuất hiện trong mỗi ô thay đổi.

Scenario 1 với các giá trị ban đầu được tạo.

Tạo thêm ba kịch bản với các giá trị khác nhau trong các ô thay đổi như sau:

Nhấn vào Add trong hộp thoại Giá trị tình huống.

Hộp thoại Add Scenario xuất hiện. Lưu ý rằng C3, C4, C5 xuất hiện trong hộp Thay đổi ô.

Trong hộp Tên tình huống, nhập Tình huống 2.
Chỉnh sửa văn bản trong Comment as - Lãi suất khác nhau.
Chọn Ngăn chặn thay đổi trong Bảo vệ và nhấp vào OK.

Các Scenario Valueshộp thoại xuất hiện. Các giá trị ban đầu xuất hiện trong các ô thay đổi. Thay đổi giá trị củaInterest_Rate đến 0.13 và bấm vào Add.

Các Add Scenariohộp thoại xuất hiện. Lưu ý rằng C3, C4, C5 xuất hiện trong hộp dưới các ô thay đổi.

Trong hộp Tên tình huống, nhập Tình huống 3.
Chỉnh sửa văn bản trong Commenthộp như - Khác nhau không. thanh toán.
Chọn Ngăn chặn thay đổi trong Bảo vệ và nhấp vào OK.

Hộp thoại Giá trị tình huống xuất hiện. Các giá trị ban đầu xuất hiện trong các ô thay đổi. Thay đổi giá trị của NPER thành 300 và nhấp vàoAdd.

Các Add Scenariohộp thoại xuất hiện. Lưu ý rằng C3, C4, C5 xuất hiện trong hộp Thay đổi ô.

Trong hộp Tên tình huống, nhập Tình huống 4.
Chỉnh sửa văn bản trong Comment hộp như - Hình thức thanh toán khác nhau.
Chọn Ngăn chặn thay đổi trong Bảo vệ và nhấp vào OK.

Các Scenario Valueshộp thoại xuất hiện. Các giá trị ban đầu xuất hiện trong các ô thay đổi. Thay đổi giá trị của Loại thành 1. Nhấp OK khi bạn đã thêm tất cả các tình huống mà bạn muốn thêm.

Các Scenario Managerhộp thoại xuất hiện. Trong hộp bên dưới Kịch bản, Bạn sẽ tìm thấy tên của tất cả các kịch bản mà bạn đã tạo.

Nhấp vào Kịch bản 1. Như bạn đã biết, Kịch bản 1 chứa các giá trị ban đầu.

Bây giờ, hãy nhấp vào Summary. Hộp thoại Tóm tắt Tình huống xuất hiện.

Báo cáo Tóm tắt Kịch bản

Excel cung cấp hai loại báo cáo Tóm tắt Tình huống -

Tóm tắt kịch bản.
Báo cáo PivotTable tình huống.

Trong hộp thoại Tóm tắt Tình huống, bạn có thể tìm thấy hai loại Báo cáo này.

Chọn Tóm tắt kịch bản trong Loại báo cáo.

Tóm tắt kịch bản

bên trong Result cells hộp, chọn ô C6 (Ở đây, chúng tôi đã đặt PMTchức năng). Bấm OK.

Báo cáo Tóm tắt Tình huống xuất hiện trong một trang tính mới. Trang tính được đặt tên là Tóm tắt tình huống.

Bạn có thể quan sát những điều sau đây trong báo cáo Tóm tắt Tình huống -

Changing Cells- Sử dụng tất cả các ô được sử dụng làm ô thay đổi. Khi bạn đã đặt tên cho các ô, Lãi suất_Rate, NPER và Loại, những ô này dường như làm cho báo cáo có ý nghĩa. Nếu không, chỉ các tham chiếu ô sẽ được liệt kê.
Result Cells - Hiển thị ô kết quả được chỉ định, tức là EMI.
Current Values - Đây là cột đầu tiên và sử dụng các giá trị của kịch bản đó được chọn trong hộp thoại Trình quản lý kịch bản trước khi tạo báo cáo tóm tắt.
Đối với tất cả các trường hợp bạn đã tạo, các ô thay đổi sẽ được đánh dấu bằng màu xám.
Trong hàng EMI, các giá trị kết quả cho mỗi kịch bản sẽ được hiển thị.

Bạn có thể làm cho báo cáo có ý nghĩa hơn bằng cách hiển thị các nhận xét mà bạn đã thêm trong khi tạo các tình huống.

Nhấp vào nút + ở bên trái của hàng chứa tên kịch bản. Nhận xét cho các tình huống xuất hiện trong hàng dưới tên kịch bản.

Các tình huống từ các nguồn khác nhau

Giả sử bạn nhận được các tình huống từ ba nguồn khác nhau và bạn cần chuẩn bị báo cáo tóm tắt Kịch bản trong sổ làm việc Chính. Bạn có thể thực hiện việc này bằng cách hợp nhất các kịch bản từ các sổ làm việc khác nhau vào sổ làm việc Chính. Làm theo các bước dưới đây -

Giả sử rằng các kịch bản nằm trong sổ làm việc, Bank1_Scenarios, Bank2_Scenarios và Bank3_Scenarios. Mở ba sổ làm việc.
Mở sổ làm việc Chính, trong đó bạn có các giá trị ban đầu.
Bấm DỮ LIỆU> Phân tích điều gì xảy ra> Trình quản lý tình huống trong sổ làm việc Chính.

Các Scenario Manager Hộp thoại xuất hiện.

Như bạn có thể quan sát, không có kịch bản nào mà bạn chưa thêm vào. Nhấp chuộtMerge.

Hộp thoại Hợp nhất các kịch bản xuất hiện.

Như bạn có thể thấy, trong Hợp nhất các kịch bản từ, bạn có hai hộp -

Book
Sheet

Bạn có thể chọn trang tính cụ thể từ một sổ làm việc cụ thể có chứa các tình huống mà bạn muốn thêm vào kết quả của mình. Nhấp vào mũi tên thả xuống củaBook để xem sổ làm việc.

Note - Các sổ làm việc tương ứng sẽ được mở để xuất hiện trong danh sách này.

Chọn sách - Bank1_Scenarios.

Tờ Bank1 được hiển thị. Ở cuối hộp thoại, số lượng kịch bản được tìm thấy trên trang tính nguồn được hiển thị. Bấm OK.

Hộp thoại Trình quản lý tình huống xuất hiện. Hai kịch bản đã được hợp nhất vào sổ làm việc Chính sẽ được liệt kê trong Kịch bản.

Nhấn vào Mergecái nút. CácMerge Scenarioshộp thoại xuất hiện. Bây giờ, hãy chọnBank2_Scenarios từ danh sách thả xuống trong hộp Sách.

Tờ Bank2 được hiển thị. Ở cuối hộp thoại, số lượng tình huống được tìm thấy trên trang tính nguồn được hiển thị. Bấm OK.

Các Scenario ManagerHộp thoại xuất hiện. Bốn tình huống đã được hợp nhất vào sổ làm việc Chính được liệt kê trong Kịch bản.

Nhấn vào Mergecái nút. CácMerge Scenarioshộp thoại xuất hiện. Bây giờ, hãy chọnBank3_Scenarios từ danh sách thả xuống trong hộp Sách.

Tờ Bank3 được hiển thị. Ở cuối hộp thoại, số lượng tình huống được tìm thấy trên trang tính nguồn sẽ được hiển thị. Bấm OK.

Hộp thoại Trình quản lý Kịch bản xuất hiện. Năm tình huống đã được hợp nhất vào sổ làm việc Chính sẽ được liệt kê trong Kịch bản.

Bây giờ, bạn có tất cả các tình huống cần thiết để tạo báo cáo tóm tắt Kịch bản.

Nhấp vào nút Tóm tắt. CácScenario Summary hộp thoại xuất hiện.

Chọn Tóm tắt kịch bản.
Trong hộp ô Kết quả, nhập C6 và bấm OK.

Báo cáo tóm tắt Kịch bản xuất hiện trên một trang tính mới trong sổ làm việc Chính.

Hiển thị các tình huống

Giả sử bạn đang trình bày các kịch bản của mình và bạn muốn tự động chuyển từ kịch bản này sang kịch bản khác và hiển thị tập hợp các giá trị đầu vào và giá trị kết quả của kịch bản tương ứng.

Nhấp vào DỮ LIỆU> Phân tích điều gì sẽ xảy ra> Trình quản lý tình huống từ nhóm Công cụ dữ liệu. Hộp thoại Trình quản lý Kịch bản xuất hiện. Danh sách các tình huống xuất hiện.
Chọn kịch bản bạn muốn hiển thị. Nhấp chuộtShow.

Các giá trị trên trang tính được cập nhật thành giá trị của kịch bản đã chọn. Các giá trị kết quả được tính toán lại.

Báo cáo PivotTable Kịch bản

Bạn cũng có thể xem báo cáo Kịch bản ở dạng PivotTable.

Nhấp vào nút Tóm tắt trong Scenario ManagerHộp thoại. Hộp thoại Tóm tắt Tình huống xuất hiện.
Chọn Scenario PivotTable report dưới Loại báo cáo.
Nhập C6 vào Result cells cái hộp.

Tình huống báo cáo PivotTable xuất hiện trên một trang tính mới.