Bài viết: Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 3) (1 người xem)

[BNTT]

3. Làm việc với các Scenario

Theo định nghĩa, phân tích What-If không phải là một môn khoa học chính xác. Tất cả những mô hình What-If chỉ đưa ra những tiên đoán và giả định cụ thể, các sự kiện mong muốn đạt được, hay bất cứ điều gì nảy ra cách bất chợt trong đầu... Và tập hợp những tiên đoán, giả định đó, khi bạn đưa vào trong một mô hình, nó sẽ được gọi là một Scenario.

Bởi vì hầu hết các bảng tính sử dụng What-If đòi hỏi phải có những dãy giá trị đầu vào tương đối rộng, nên bạn sẽ phải tạo nhiều Scenario để kiểm tra (chẳng hạn như với mỗi dự đoán, bạn tạo một Goal Seek, bởi vì Goal Seek chỉ cho phép bạn xử lý mỗi lần một biến). Tuy nhiên, để khỏi phải mất thời gian làm cái công việc tẻ nhạt là chèn những giá trị vào những ô thích hợp, Excel cung cấp cho bạn một công cụ là Scenario Manager, để giải quyết vấn đề này dùm bạn. Phần tiếp theo đây sẽ giúp bạn nắm vững công cụ hữu dụng này.
3.1. Tìm hiểu Sceriano

​

Như bạn đã thấy, Excel có nhiều tính năng mạnh mẽ giúp bạn xây dựng những mô hình tinh vi để có thể trả lời cho những câu hỏi phức tạp. Dù vậy, vấn để ở đây không phải là việc trả lời câu hỏi, mà là đặt ra những câu hỏi này. Ví dụ, hình 19 dưới đây minh họa một bảng tính mô phỏng việc phân tích một bài toán mua nhà trả góp. Bạn sẽ sử dụng mô hình này để quyết định sẽ phải trả góp bao nhiêu tiền hằng tháng, trong bao nhiêu năm, liệu việc tăng/giảm số tiền trả góp hằng tháng này có tiết kiệm được cho bạn không?

Hình 19​

​

Trong bảng tính trên, tôi đã chú thích cho các thuật ngữ tiếng Anh được dùng trong bảng tính. Tuy nhiên, tôi sẽ giải thích rõ thêm một chút.

Bảng tính này mô phỏng cho trường hợp bạn muốn mua trả góp một căn nhà trị giá $100,000 (100.000 USD). Người bán hàng nói rằng, bạn chỉ cần trả trước cho họ $20,000 (20% trị giá nhà), phần còn lại trả góp trong vòng 20 năm, với lãi suất không đổi 6% năm. Tuy nhiên họ còn nói rằng, bạn có thể giảm hoặc tăng số tiền trả trước, cũng như thời hạn vay, và nếu thích thì mỗi tháng bạn có thể trả thêm cho họ bao nhiêu đó (trừ vào tiền gốc) để giảm bớt số tiền lãi...

Bạn đem bảng dự tính của người bán về nhà, và sẽ ngồi suy nghĩ, tính toán... Trong bảng tính ở hình 19, tôi đã đưa ra một mô phỏng: giả sử rằng tôi sẽ trả thêm hằng tháng $100 nữa (giá trị tại ô B9, có tên là Paydown, dịch nôm na là trả bớt), thì sẽ như thế nào, có tiết kiệm được không? Và tôi làm thêm một phần nhỏ để tính thử.

Tôi trình bày trong bảng tính hai phần kết quả: môt là Regular Mortgage (B12:B15), phương án trả góp bình thường (theo các số liệu người bán gợi ý), và một là With Paydown (C12:C15), là phương án của tôi, dùng để tính thử nếu có cái khoản Paydown này vào thì sẽ ra sao.

Tại Regular Mortgage, tôi dùng hàm PMT() để tính ra số tiền tôi phải trả hằng tháng, gồm cả gốc và lãi, sau khi đã trừ đi số tiền trả trước lúc ban đầu, là bao nhiêu:

=PMT(Interest_Rate / 12, Term * 12, House_Price - Down_Payment) = $573.14

​

(Ở đây tôi đã đặt tên cho các ô tham chiếu: Interest_Rate = B4, Term = B8, House_Price = B3 và Down_Payment = B7)

Sau đó tôi dùng công thức cho ô B13: =B12*Term*12 để tính ra tổng số tiền tôi phải trả cho căn nhà này theo phương án trả góp trên, nó là $137,554.76.

Bây giờ, nếu mỗi tháng tôi trả thêm $100 thì sao? Tại With Paydown, tôi dùng một phép tính bình thường cho ô C12: =B12 + Paydown (= 573.14 + 100 = 673.14), rồi dùng Goal Seek để làm thay đổi số năm trả góp, Goal Seek cho một kết quả là 15.1 (năm)

Nhập con số 15.1 này vào ô C15, dùng công thức = C12*C15*12 cho ô C13, tôi biết được tổng số tiền tôi phải trả góp cho căn nhà trong vòng 15.1 năm là $121,733.04, tiết kiệm được $15,821.72 so với tổng số tiền tôi phải trả theo phương án trả góp trong 25 năm mà người bán đề nghị ($137,544.76).

Nhưng chẳng lẽ cứ với mỗi phương án, lại phải chạy một Goal Seek? Chẳng hạn, tôi muốn biết nếu tăng thời hạn vay lên 25 năm, hoặc tăng số tiền trả tiền trả trước lên 25%, hoặc mỗi tháng chỉ đưa thêm $50 thôi... Và nhất là, sau mỗi lần đưa ra phương án, có cách nào lưu lại để sau này ngồi so sánh phương án nào là tối ưu nhất? Để giải quyết vấn đề này, tôi sẽ sử dụng Scenario.

Tiếp theo đây những ví dụ cụ thể về các Scenario. Scenario Manager của Excel 2007 sẽ cho phép định nghĩa từng scenario (có thể hiểu là phương án, là kịch bản) riêng biệt trong bảng tính. Bạn có thể lưu lại các scenario này, đạt tên cho nó, và sau này có thể gọi nó ra (cùng với tất cả các giá trị nhập liệu đã làm với nó) từ một danh sách.



3.2. Thiết lập bảng tính để sử dụng Scenario

Trước khi tạo một Scenario, bạn cần quyết định ô nào trong mô hình là ô nhập liệu. Đây sẽ là các biến, là các ô mà khi bạn thay đổi giá trị của chúng thì sẽ làm thay đổi kết quả của mô hình (Excel gọi các ô này là Changing Cell). Bạn có thể sử dụng đến 32 Changing Cell trong một Scenario.

Để đạt được kết quả tốt nhất, bạn hãy làm theo các hướng dẫn sau đây khi thiết lập một Scenario:

• Các Changing Cell nên chứa các hằng số. Vì các công thức có thể bị ảnh hưởng bởi những ô khác, và có thể loại bỏ hoàn toàn Scenario.
  
  
• Để dễ thiết lập một Scenario, và cũng để cho bảng tính được trình bày rõ ràng hơn, bạn hãy kết nhóm các ô và ghi nhãn cho chúng (xem lại hình 19).
  
  
• Để rõ ràng hơn nữa, hãy đặt tên cho các Changing Cell.

3.3. Tạo một Sceriano

Để làm việc với các Scenario, bạn sử dụng công cụ Scenario Manager của Excel. Công cụ này cho phép bạn thêm, sửa, hiển thị và xóa các Scenario, cũng như tạo các báo cáo tổng kết Scenario.

Khi bảng tính của bạn đã được thiết lập theo ý bạn muốn, bạn có thể thêm một Scenario vào theo các bước sau đây:

1. Chọn Data, What-If Analysis, Scenario Manager. Excel hiển thị hộp thoại Scenario Manager như minh họa ở hình 20:
   
   
   

   
   Hình 20​
   
   
2. Nhấn Add. Hộp thoại Add Scenario xuất hiện. Hình 21 minh họa một hộp thoại Add Scenario đã được điền đầy đủ:
   
   
   

   
   Hình 21​
   
   
3. Nhập tên cho Scenario trong khung Scenario Name.
   
   
4. Nhập tham chiếu dẫn đến các ô thay đổi trong bảng tính ở khung Changing Cell. Bạn có thể nhập trực tiếp vào (nhớ tách biệt những ô không liền nhau bằng dấu phẩy), hoặc dùng chuột chọn trong bảng tính. Trong ví dụ này, tôi nhập dãy ô B7:B9.
   
   
5. Sử dụng khung Comment để nhập một vài mô tả cho Scenario này. Phần mô tả này sẽ xuất hiện trong Comment ngoài hộp thoại Scenario Manager, tương ứng với mỗi Scenario được chọn.
   
   
6. Nhấn OK. Excel sẽ hiển thị tiếp hộp thoại Scenario Value như hình ở hình 22. Trong hình này, bạn thấy Excel sử dụng tên cho các Changing cell, nhờ đó mà việc nhập liệu được chính xác hơn. Để có được như vậy, bạn phải đặt tên cho các Changing cell này từ trước. Nếu chưa có tên, Excel sẽ hiển thị địa chỉ của các ô đó.
   
   
   

   
   Hình 22​
   
   
7. Nhập các giá trị mà bạn muốn thay đổi vào các Changing cell tương ứng. Ví dụ như bạn muốn thay đổi số tiền trả trước (khi mua trả góp), nhập một giá trị mới cho ô Down_Payment; muốn tăng/giảm thời hạn trả góp, nhập giá trị mới vào ô Term; và nếu muốn sửa đổi số tiền sẽ trả thêm hằng tháng, ngoài số tiền gốc và lãi, bạn nhâp một giá trị mới cho ô Paydown.
   
   
8. Để bổ sung thêm một Scenario khác, bạn nhấn Add để quay về lại bước 3. Nếu không, nhấn OK để quay về hôp thoại Scenario Manager.
   
   
9. Nhấn Close để trở về bảng tính.

3.4. Hiển thị một Sceriano

Sau khi bạn đã định nghĩa một Scenario, bạn có thể nhập những giá trị vào trong các ô thay đổi (changing cell) bằng cách hiển thị lại Scenario đó từ hộp thoại Scenario Manager. Các bước sau đây trình bày chi tiết cho bạn:

1. Chọn Data, What-If Analysis, Scenario Manager.
   
   
2. Trong danh sách Scenario, chọn Scenario mà bạn muốn hiển thị.
   
   
3. Nhấn Show. Excel sẽ điền các giá trị từ Scenario vào trong các Changing Cell. Hình 23 minh họa cho bạn một ví dụ:
   
   
   

   
   Hình 23​
   
   Bạn hãy so sánh hình này với hình 22, sẽ thấy sự khác biệt trong các Changing Cell, và đồng thời nó cũng làm thay đổi giá trị của kết quả. Đây là một Scenario mà tôi đã định nghĩa cho trường hợp trả trước $15,000, mua trả góp trong thời hạn 25 năm, và mỗi tháng tôi trả thêm cho người bán $50 nữa, trừ vào tiền gốc. Với "kịch bản" này, số tiền tiết kiệm được từ khoản mua trả góp không bằng trường hợp "Best Case" mà tôi đã ví dụ trong bài trước. Tuy nhiên, số tiền tôi trả góp hằng tháng thì ít hơn, và thời gian trả góp được lâu hơn...
   
   
4. Làm lại bước 2 và bước 3 để hiển thị (và sử dụng) một Scenario khác.
   
   
5. Nhấn Close để trở về bảng tính.

3.5. Sửa lại một Sceriano

Khi bạn cần thực hiện những thay đổi của một Scenario (đổi tên, chọn lại Changing cell khác, hoặc nhập giá trị khác...), bạn theo các bước sau đây:

1. Chọn Data, What-If Analysis, Scenario Manager.
   
   
2. Trong danh sách Scenario, chọn Scenario mà bạn muốn sửa.
   
   
3. Nhấn Edit. Excel sẽ hiển thị hộp thoại Edit Scenario, hộp thoại này giống hệt hộp thoại Add Scenario mà bạn đã thấy ở hình 21.
   
   
4. Thực hiện những thay đổi bạn muốn, rồi nhấn OK. Hộp thoại Scenario Manager sẽ mở ra, và tại đây bạn có thể áp dụng những giá trị mới vào bảng tính, như đã nói ở bài trước.
   
   
5. Làm lại bước 2 đến bước 5 để sửa (và cho áp dụng) một Scenario khác.
   
   
6. Nhấn Close để trở về bảng tính.

3.6. Trộn các Sceriano

Các Scenario mà bạn đã tạo ra được lưu trữ trong mỗi WorkSheet. Nếu như bạn có những mô hình tương tự trong các WorkSheet khác, bạn có thể ra các Scenario riêng biệt cho mỗi Sheet và sau đó trộn (merge) chúng lại. Đây là các bước để thực hiện:

1. Kích hoạt WorkSheet mà bạn muốn lưu trữ các Scenario đã được trộn.
   
   
2. Chọn Data, What-If Analysis, Scenario Manager.
   
   
3. Nhấn Merge, Excel sẽ hiển thị hộp thoại Merge Scenario như hình 24 sau đây:
   
   
   

   
   Hình 24​
   
   
4. Chọn bảng tính chứa Sheet có Scenario trong hộp Book.
   
   
5. Chọn Sheet có chứa Scenario mà bạn sẽ trộn với những Scenario đang có ở Sheet hiện hành trong hộp Sheet.
   
   
6. Nhấn OK để quay về Scenario Manager.
   
   
7. Nhấn Close để trở về bảng tính.

3.7. Tạo một báo cáo tổng kết các Scenario

Bạn có thể tạo một báo cáo tổng kết (Summary Report) trình bày các ô thay đổi (changing cell) trong từng Scenario cùng với các ô kết quả được chọn. Đây là cách tiện nhất để so sánh các "phương án" khác nhau. Bạn có thể thử nó bằng cách làm theo những bước sau đây:

1. Chọn Data, What-If Analysis, Scenario Manager.
   
   
2. Nhấn Summary, Excel sẽ hiển thị hộp thoại Scenario Summary.
   
   
3. Trong nhóm Report Type, bạn chọn Scenario Summary (báo cáo bình thường) hoặc Scenario PivotTable Report (báo cáo theo kiểu PivotTable).
   
   
4. Trong hộp Result Cells, nhập các tham chiếu dẫn đến các ô kết quả mà bạn muốn xuất hiện trong báo cáo (xem hình 25). Bạn có thể chọn trực tiếp các ô trên bảng tính hoặc gõ nhập các tham chiếu vào (nhớ tách biệt các ô không liền nhau bằng các dấu phẩy).
   
   
   

   
   Hình 25​
   
   
5. Nhấn OK. Excel sẽ hiển thị báo cáo trong một Sheet mới, có tên là Scenario Summary.

Hình 26 sau đây minh họa một Scenario Report cho bảng tính Mortgage Analysis.

Hình 26​

Trong bảng báo cáo ở hình trên, tôi đã thiết lập để so sánh các phương án cho việc mua nhà trả góp với phương án hiện tại (Current Value) mà ngân hàng vạch ra. Trong bảng tính này, tôi đã lập 3 Scenario: Best Case, Worst Case và Likliest Case.

Hình 27 sau đây minh họa báo cáo Scenario của bảng tính Mortgage Analysis với dạng Scenario PivotTable Report:

Hình 27​

3.8. Xóa các Sceriano

Nếu bạn đã tạo ra nhiều Scenario để thử các "phương án", và sau khi đã chọn được Scenario thích hợp nhất, có thể bạn sẽ không cần đến những Scenario này nữa. Bạn có thể xóa chúng theo các bước sau:

1. Chọn Data, What-If Analysis, Scenario Manager.
   
   
2. Chọn Scenario mà bạn muốn xóa trong danh sách.
   
   Lưu ý: Excel sẽ không hỏi xác nhận bạn có muốn xóa một Scenario hay không, mà khi bạn đã nhấn Delete, Excel sẽ xóa ngay tức khắc; và cũng không có cách nào để phục hồi lại một Scenario đã lỡ xóa. Do đó bạn hãy chắc chắn rằng Scenario mà bạn đang chọn ở bước này là cái mà bạn muốn xóa, trước khi thực hiện tiếp bước 3.
   
   
3. Nhấn Delete. Excel sẽ xóa Scenario được chọn.
   
   
4. Nhấn Close để trở về bảng tính.

Một số bài viết có liên quan:
1/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 2)
2/ Sử dụng các công cụ tạo mô hình kinh doanh của Excel (phần 1)
3/ Lựa chọn danh mục đầu tư (Portfolio) - phần 2
4/ Lựa chọn danh mục đầu tư (Portfolio) - phần 1
5/ Hướng dẫn sử dụng Crystal Ball (phần 7)
6/ Hướng dẫn sử dụng Crystal Ball (phần 6)
7/ Hướng dẫn sử dụng Crystal Ball (phần 5)
8/ Hướng dẫn sử dụng Crystal Ball (phần 4)
9/ Hướng dẫn sử dụng Crystal Ball (phần 3)
10/ Hướng dẫn sử dụng Crystal Ball (phần 2)
http://www.giaiphapexcel.com/vbb/content.php?327

 

[solecao]

cho mình hỏi, khi làm các "kịch bản" như vầy, giá trị Revised_Term vẫn phải chạy manual goal-seek từng cái và điền giá trị tính từ đó ra bằng tay vào ô C15?