Học Excel -
Thủ Thuật Excel
Các công cụ tạo mô hình kinh doanh có
trong Excel (phần 2)
Sử dụng các công cụ tạo mô hình kinh doanh có trong Excel (phần 2):
15.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.
15.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 15.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 15.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 15.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.
15.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
15.19).
· Để rõ ràng hơn nữa, hãy đặt tên cho các Changing Cell.
15.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 Managernhư minh họa ở hình 15.20:
Hình 15.20
2. Nhấn Add. Hộp thoại Add Scenario xuất hiện. Hình 15.21 minh họa một
hộp thoại Add Scenario đã được điền đầy đủ:
Hình 15.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 15.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 15.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ấnOK để quay về hôp thoại Scenario Manager.
9. Nhấn Close để trở về bảng tính.
15.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 15.23 minh họa cho bạn một ví dụ:
Hình 15.23
Bạn hãy so sánh hình này với hình 15.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.
15.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 15.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.
15.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 15.24
sau đây:
Hình 15.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.
15.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 15.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 15.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 15.26 sau đây minh họa một Scenario Report cho bảng tính Mortgage
Analysis.
Hình 15.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 15.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 15.27
15.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.
