Làm thế nào để xây dựng các kịch bản thả xuống trong mô hình tài chính của bạn - những cái núm vú

Phương pháp xây dựng kịch bản được sử dụng phổ biến nhất là sử dụng kết hợp các công thức và hộp thả xuống. Trong mô hình tài chính, bạn tạo một bảng các kịch bản khả thi và các đầu vào của chúng và liên kết các tên kịch bản với một ô thả xuống ô đầu vào. Các đầu vào của mô hình được liên kết với bảng kịch bản. Nếu mô hình đã được xây dựng đúng với tất cả các đầu vào chảy qua các kết quả đầu ra, thì kết quả của mô hình sẽ thay đổi khi người dùng lựa chọn các tùy chọn khác nhau từ hộp thả xuống.

Các hộp thả xuống xác nhận dữ liệu được sử dụng cho một số mục đích khác nhau trong mô hình tài chính, bao gồm phân tích kịch bản.

Sử dụng xác nhận dữ liệu để mô hình hóa các mô hình lợi nhuận

Tải xuống tệp 0801. xlsx. Mở nó và chọn tab có nhãn 8-1-start.

Cách này đã được mô phỏng, các đầu vào được sắp xếp trong cột B. Bạn có thể thực hiện phân tích độ nhạy cảm một cách đơn giản bằng cách thay đổi một trong các yếu tố đầu vào - ví dụ: thay đổi khách hàng cho mỗi toán tử cuộc gọi trong ô B3 từ 40 đến 45 và bạn sẽ thấy tất cả các số phụ thuộc thay đổi. Đây sẽ là một phân tích độ nhạy cảm, bởi vì bạn đang thay đổi chỉ một biến. Thay vào đó, bạn sẽ thay đổi nhiều biến cùng một lúc trong bài tập phân tích kịch bản đầy đủ này, do đó bạn cần phải làm nhiều hơn tinh chỉnh một vài số theo cách thủ công.

Lấy mô hình đã tải xuống và cắt và dán các mô tả từ cột C đến cột F. Bạn có thể làm điều này bằng cách đánh dấu ô C6: C8, nhấn Ctrl + X, chọn ô F6, và nhấn Enter.

Các đầu vào trong các tế bào B3 đến B8 là phạm vi hoạt động mà ổ đĩa mô hình và sẽ vẫn như vậy. Tuy nhiên, chúng cần phải trở thành công thức thay đổi tùy thuộc vào hộp thả xuống mà bạn sẽ tạo ra.
Sao chép phạm vi trong cột B qua các cột C, D và E.
Bạn có thể làm điều này bằng cách đánh dấu B3: B8, nhấn Ctrl + C, chọn các ô C3: E3, và nhấn Enter. Những khoản tiền này sẽ giống nhau cho từng trường hợp cho đến khi bạn thay đổi chúng.
Trong hàng 2 nhập Trường hợp Tốt nhất
, Trường hợp cơ sở , và Trường hợp Tồi tệ nhất. Thiết lập mô hình phân tích kịch bản.
Lưu ý rằng công thức vẫn liên kết với đầu vào trong cột B, như bạn có thể thấy bằng cách chọn ô C12 và nhấn phím tắt F2.
Chỉnh sửa các đầu vào bên dưới mỗi kịch bản.
Bạn có thể đặt bất cứ điều gì bạn nghĩ là có thể, nhưng để phù hợp với những con số trong ví dụ này, nhập các giá trị. Bỏ qua cột B ngay bây giờ.

Đầu vào để phân tích kịch bản.
Bây giờ bạn cần thêm hộp thả xuống ở trên cùng, điều này sẽ thúc đẩy kịch bản của bạn. Không thực sự quan trọng nếu bạn đặt hộp thả xuống chính xác, nhưng nó nên ở vị trí dễ tìm, thường ở đầu trang.
Trong ô E1, nhập tiêu đề Kịch bản
. Chọn ô F1 và thay đổi định dạng để nhập để người dùng có thể thấy rằng ô này có thể chỉnh sửa được.
Cách dễ nhất để thực hiện việc này là làm theo các bước sau:
Nhấp vào một trong các ô đã được định dạng như một đầu vào, chẳng hạn như ô E3.
1. Nhấn biểu tượng Format Painter trong phần Clipboard ở phía bên tay trái của tab Home. Con trỏ của bạn sẽ thay đổi để một cây cọ.
2. Chọn ô F1 để dán định dạng.
3. Định dạng Painter thường dùng cho một lần. Sau khi bạn đã chọn ô, cây cọ sẽ biến mất khỏi con trỏ. Nếu bạn muốn Painter Format trở thành "dính" và áp dụng cho nhiều ô, hãy nhấp đúp vào biểu tượng khi bạn chọn nó từ tab Trang chủ.
  Bây giờ, trong ô F1, chọn Xác nhận dữ liệu từ phần Công cụ dữ liệu của tab Dữ liệu.
Hộp thoại Xác nhận Dữ liệu xuất hiện.
Trên tab Cài đặt, thay đổi danh mục Cho phép thả xuống Danh sách, sử dụng chuột để chọn phạm vi = $ C $ 2: $ E $ 2 và nhấp OK.
Tạo kịch bản thả xuống xác nhận dữ liệu.
Nhấp vào hộp thả xuống, hiện xuất hiện cạnh ô F1 và chọn một trong các kịch bản (ví dụ: Trường hợp cơ sở).
Áp dụng các công thức cho các kịch bản

Các ô trong cột B vẫn đang lái xe mô hình, và chúng cần được thay thế bằng các công thức. Tuy nhiên, trước khi bạn thêm các công thức, bạn nên thay đổi định dạng của các ô trong dãy để cho thấy chúng chứa các công thức, thay vì các số được mã hóa cứng. Thực hiện theo các bước sau:

Chọn các ô B3: B8 và chọn Điền màu từ nhóm Phông trên tab Trang chủ.

Thay đổi màu Điền thành màu trắng.
Điều rất quan trọng để phân biệt giữa các công thức và các tế bào đầu vào trong một mô hình. Bạn cần phải làm cho nó rõ ràng cho bất kỳ người dùng mở mô hình mà các tế bào trong phạm vi này có công thức và không nên được ghi đè.
Bây giờ bạn cần phải thay thế các giá trị được mã hoá cứng trong cột B bằng các công thức sẽ thay đổi khi hộp thả xuống thay đổi. Bạn có thể làm điều này bằng cách sử dụng một số chức năng khác nhau; một HLOOKUP, một tuyên bố IF lồng nhau, một IFS, và một SUMIF tất cả sẽ làm các trick. Thêm các công thức bằng cách làm theo các bước sau:

Chọn ô B3, và thêm một công thức sẽ thay đổi giá trị phụ thuộc vào ô F1.

Dưới đây là những gì công thức sẽ được theo các tùy chọn khác nhau:
= HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, 2, 0)
- Lưu ý rằng với giải pháp này, bạn cần phải thay đổi số chỉ mục hàng từ 2 đến 3 và như vậy khi bạn sao chép công thức xuống. Thay vào đó, bạn có thể sử dụng hàm ROW trong trường thứ ba như sau: = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, ROW (A3) -1, 0)
  = IF ($ F $ 1 = $ C $ 2, C3, IF ($ F $ 1 = $ D $ 2, D3, E3)) = IFS ($ F $ 1 = $ C $ 2, C3, $ F $ 1 = $ D $ 2, D3, IF $ F = $ E $ 2, E3)
- = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)
- Như mọi khi, có nhiều lựa chọn khác nhau để lựa chọn và giải pháp tốt nhất là là đơn giản nhất và dễ hiểu nhất.Bất kỳ chức năng nào trong số các chức năng này sẽ tạo ra kết quả chính xác như nhau, nhưng phải thay đổi số chỉ mục hàng trong HLOOKUP không mạnh, và việc thêm ROW có thể gây nhầm lẫn cho người dùng. Câu lệnh IF lồng nhau rất khó để xây dựng và tuân theo, và mặc dù chức năng IFS mới được thiết kế để tạo ra một hàm NẾU lồng nhau đơn giản hơn, nó vẫn khá khó sử dụng. SUMIF khá đơn giản để xây dựng và theo dõi, và rất dễ dàng để mở rộng nếu bạn cần bổ sung thêm các kịch bản trong tương lai.
- Lưu ý rằng IFS là một chức năng mới chỉ có sẵn với Office 365 và Excel 2016 hoặc mới hơn được cài đặt. Nếu bạn sử dụng chức năng này và ai đó mở mô hình này trong một phiên bản trước của Excel, cô ấy có thể xem công thức, nhưng cô ấy sẽ không thể chỉnh sửa nó.
  Sao chép công thức trong ô B3 xuống cột.
  
  Phân tích tình huống hoàn thành.
Bằng cách sử dụng bản sao và dán thông thường, bạn sẽ mất tất cả định dạng của mình. Điều quan trọng là giữ lại định dạng của mô hình để bạn có thể nhìn thấy trong nháy mắt mà đầu vào được theo trị giá đồng đô la, tỷ lệ phần trăm, hoặc số khách hàng. Sử dụng Công thức Dán để giữ lại định dạng. Bạn có thể truy cập nó bằng cách sao chép ô vào khay nhớ tạm, làm nổi bật phạm vi đích, nhấp chuột phải, và chọn biểu tượng Paste Formulas để dán công thức và để nguyên định dạng.
Bây giờ cho phần thú vị! Đã đến lúc kiểm tra tính năng mô hình trong mô hình.
Nhấp vào ô F1, thay đổi hộp thả xuống và xem các kết quả của mô hình thay đổi khi bạn chuyển đổi giữa các tình huống khác nhau.