Phân tích nhân quả (What – If Analysis) TranThanhPhong GPE

- Phân tích độ nhạy (Sentitive Analysis)

Phân tích độ nhạy là dạng phân tích nhằm trả lời câu hỏi “điều gì sẽ xảy ra … nếu như … ” (what – if). Phân tích này xem xét yếu tố đầu vào nào là quan trọng nhất (yếu tố mang tính chất rủi ro) ảnh hưởng đến kết quả bài toán (lợi nhuận hoặc chi phí). Trong Excel hỗ trợ phân tích độ nhạy 1 chiều và hai chiều, nghĩa là chỉ đánh giá được tối đa 2 yếu tố rủi ro. Tuy nhiên bổ sung thư viện “Sensitivity.xla” thêm vào Excel sẽ giúp chúng ta phân tích được độ nhạy nhiều chiều cho các bài toán có dùng Solver. Lưu ý phân tích độ nhạy không xét đến mối quan hệ tương quan giữa các biến.

Bài toán tĩnh

B1. Nhập các thông số bài toán vào các ô C2:C8 với các nhãn tương ứng.
B2. Lập bảng báo cáo ngân lưu cho dự án trong 5 năm. Với các công thức sau:
-Thu nhập = giá đơn vị * số lượng à D13=$C$4*$C$5 sau đó chép công thức cho các ô E13:H13.
-Giá trị thanh lý Đất tại ô I15 chính là tham chiếu ô C2
-Giá trị thanh lý Nhà xưởng tại ô I16 chính là tham chiếu ô C7
-Ngân lưu vào từ năm 1 đến năm 6 - thanh lý chính là tổng của Thu nhập, giá trị thanh lý Đất, giá trị thanh lý Nhà xưởng hàng năm tương ứng. C17=SUM(C11:C16) sau đó chép công thức cho các ô D17:I17
-Chi phí đầu tư Đất tại ô C21 chính là tham chiếu ô C2
-Chi phí đầu tư Nhà xưởng tại ô C22 chính là tham chiếu ô C6.
-Chi phí vận hành = Chi phí đơn vị * Số lượng à D23=$C$3*$C$5 sau đó chép công thức cho các ô E23:H23.
-Ngân lưu ra từ năm 1 đến năm 6 –thanh lý chính là tổng của Chi phí đầu tư Đất, Nhà xưởng và Chi phí vận hàng hàng năm tương ứng. C24=SUM(C20:C23) sau đó chép công thức cho các ô D24:I24
-Ngân lưu ròng = Ngân lưu vào – Ngân lưu ra à C25=C17-C24 sau đó chép công thức cho các ô D25:I25
-Giá trị NPV tại ô C26=C25+NPV(C8,D25:I25)
-Giá trị IRR tại ô C27=IRR(C25:I25)



Giá trị NPV ở trên là $3275 với mức chiết khấu 10% và IRR thu được là 15.20% > 10% do vậy về cơ bản có thể kết luận là dự án trên đáng giá. Các giá trị này được phân tích dựa trên giả thuyết giá trị của các yếu tố đầu vào không đổi suốt thời kỳ hoạt động của dử án. Do vậy, giá trị đơn lẻ của NPV thu được từ phân tích xác định là giá trị không thực bởi vì giá trị riêng biệt này sẽ không bao giờ có được.
Ta có thể cải thiện kết quả phân tích trên bằng việc kiểm tra độ nhạy của NPV (và IRR) đối với sự thay đổi giá trị của các biến đầu vào như “Giá đơn vị” (C4) và “Chi phí đơn vị” (C3) bằng công cụ phân tích độ nhạy một chiều và hai chiều của Excel.

Phân tích độ nhạy một chiều

Tại đây ta xét sự thay đổi của một yếu tố “Giá đơn vị” đầu vào tác động đến kết quả NPV (và IRR). Giá nguyên vật liệu đầu vào dao động từ $48 đến $53 và mỗi lần dao động 1 đơn vị.

B1. Tạo vùng chứa các giá trị có thể có của “Giá đơn vị” tại các ô D34:I34, lần lượt nhập các con số từ 48 đến 53.
B2. Tại ô C35 tham chiếu đến địa chỉ ô cần phân tích là ô C26 (NPV) và tại C36 tham chiếu đến ô C27 (IRR).
B3. Đặt thêm các nhãn cho yếu tố đầu vào và nhãn cho các giá trị cần phân tích sẽ giúp bài toán được rõ ràng hơn.
B4. Đánh dấu chọn cả vùng C34:I36
B5. Chọn Ribbon à nhóm Data à Data Tools à What-If Analysis à Data Table … (Excel phiên bản cũ thì chọn Data à Table…)
B6. Khai báo tại Row input cell địa chỉ của ô chứa “Giá đơn vị” à ô C4 (nhập vào Row input cell do các giá trị của yếu tố đầu vào “Giá đơn vị” được bố trí theo dòng).


B7. Nhấp nút OK và xem kết quả phân tích

Phân tích độ nhạy hai chiều




Tại đây xét sự thay đổi của hai yếu tố đầu vào là “Giá đơn vị” và “Chi phí đơn vị” xem chúng tác động đến kết quả NPV như thế nào. Giả sử giá đơn vị dao động từ $48 đến $53 và mỗi lần dao động là 1 đơn vị, chi phí đơn vị dao động từ $45 đến $55 và mỗi lần dao động 1 đơn vị.

B1. Tạo vùng chứa các giá trị biến thiên của “Giá đơn vị” tại các ô D43:I43, lần lượt nhập các con số từ 48 đến 53 vào.
B2. Tạo vùng chứa các giá trị biến thiên của “Chi phí đơn vị” tại các ô C44:C54, lần lượt nhập các con số từ 45 đến 55 vào.
B3. Tại ô C43 tham chiếu đến địa chỉ ô cần phân tích là ô C26 (NPV)
B4. Đặt thêm các nhãn cho các yếu tố đầu vào và nhãn cho giá trị cần phân tích sẽ giúp bài toán được rõ ràng hơn.
B5. Đánh dấu chọn cả vùng C43:I54
B6. Chọn Ribbon à nhóm Data à Data Tools à What-If Analysis à Data Table … (Excel phiên bản cũ thì chọn Data à Table…)
B7. Khai báo tại Row input cell địa chỉ của ô chứa “Giá đơn vị”à ô C4 (nhập vào Row input cell do các giá trị của yếu tố đầu vào “Giá đơn vị” được bố trí theo dòng). Khai báo tại Column input cell địa chỉ của ô chứa “Chi phí đơn vị” à ô C3 (nhập vào Column input cell do các giá trị của yếu tố đầu vào “chi phí đơn vị” được bố trí theo cột).



B8. Nhấp nút OK và xem kết quả



Qua phân tích độ nhạy, ta thấy rằng biên dạng của NPV là có biến đổi theo “Giá đơn vị” và “Chi phí đơn vị”.

5.2.2. Phân tích tình huống (Scenario)

Là dạng phân tích “what-if”, phân tích tình huống thừa nhận rằng các biến nhất định có quan hệ tương hỗ với nhau. Do vậy, một số ít biến số có thể thay đổi theo một kiểu nhất định tại cùng một thời điểm. Tập hợp các hoàn cảnh có khả năng kết hợp lại để tạo ra “các trường hợp” hay “các tình huống” khác nhau là:


A. Trường hợp xấu nhất/ Trường hợp bi quan
B. Trường hợp kỳ vọng/ Trường hợp ước tính tốt nhất
C. Trường hợp tốt nhất/ Trường hợp lạc quan

Ghi chú: Phân tích tình huống không tính tới xác suất của các trường hợp xảy ra

A. Chấp thuận dự án nếu NPV > 0 ngay cả trong trường hợp xấu nhất
B. Bác bỏ dự án nếu NPV < 0 ngay cả trong trường hợp tốt nhất
C. Nếu NPV đôi lúc dương, đôi lúc âm, thì các kết quả là không dứt khoát. Không may, đây sẽ là trường hợp hay gặp nhất.

Phân tích tình huống

Tiếp tục khảo sát bài toán ở trên, qua điều tra thị trường thực tế về tình hình chi phí nguyên vật liệu và giá sản phẩm của dự án trên cho kết quả như sau:



Chúng ta sẽ lần lượt tạo các Tình huống cho chi phí đơn vị và giá đơn vị cho dự án trên theo các bước sau:

B1. Lập bài toán trên bảng tính như phần 5.2.
B2. Chọn Ribbon à nhóm Data à Data Tools à What-If Analysis à Scenario Manager … (Excel phiên bản cũ chọn Tools à Scenarios… )






B3. Nhấp nút Add…

·Đặt tên cho Tình huống là “Tốt nhất” tại khung Scenario name
·Tại khung Changing cells chọn địa chỉ hai ô chứa “Chi phí đơn vị” và “Giá đơn vị” là C3:C4.




B4. Nhấp nút OK

·Tại ô C3 (Chi phí đơn vị) nhập vào giá trị 45.
·Tại ô C4 (Giá đơn vị) nhập vào giá trị 53.




B5. Tương tự, nhấp nút Add để thêm Tình huống khác, (nhấp nút OK để trở về bảng quản lý các tình huống). Trong bài này hãy nhấp nút Add

·Đặt tên cho Tình huống là “Trung bình” tại khung Scenario name. Tại khung Changing cells chọn địa chỉ hai ô chứa “Chi phí đơn vị” và “Giá đơn vị” là C3:C4. Nhấp nút OK, và nhập vào ô C3 (Chi phí đơn vị) giá trị là 47, nhập vào ô C4 (Giá đơn vị) giá trị là 50.
·Tiếp tục nhấp nút Add để tạo “Trường hợp xấu nhất”. Đặt nhãn và chọn địa chỉ các ô cần thay đổi. Nhấp OK và nhập giá trị cho các ô, tại ô C3 (Chi phí đơn vị) nhập vào giá trị 55, tại ô C4 (Giá đơn vị) nhập vào giá trị 48.

B6. Nhấp nút OK để trở về bảng quản lý các tình huống.





B7. Để xem kết quả dự án (NPV, IRR) của tình huống nào thì chọn tên tình huống đó trong danh sách vừa tạo và nhấp nút Show. Tương tự cho việc tạo thêm, hiệu chỉnh tình huống thì nhấn nút Edit…, và xóa tình huống thì nhấn nút Delete.

B8 . Nếu bạn không muốn xem từng tình huống tại một thời điểm mà muốn có một bảng báo cáo tổng hợp về các tình huống của dự án để dễ dàng ra quyết định thì nhấn nút Summary…

·Nhập địa chỉ các ô cần tạo báo cáo (ở đây chính là NPV và IRR của dự án) vào khung Result cells,bạn hãy nhập vào địa chỉ C26 và C27.
·Chọn kiểu báo cáo là Scenario summary hoặc Scenario PivotTable Report tùy bạn.




B9. Nhấp nút OK sau khi khai báo các thông số để xem báo cáo tổng hợp





Nhìn vào bảng trên để đánh giá chắc chắn có nhiều thông tin hơn việc đánh giá dự án chỉ dựa trên bài toán tĩnh. Nếu thực hiện dự án mà tình huống xấu nhất xảy trong thực tế thì dự án sẽ không thể thu hồi vốn được.

5.2.3. Tìm mục tiêu (Goal Seek)

Bài toán điểm hòa vốn

Trong hoạt động sản xuất kinh doanh, câu hỏi thường đặt ra là sản xuất hay bán bao nhiêu sản phẩm để cân bằng giữa thu nhập và chi phí, nghĩa là khi đó doanh nghiệp được hòa vốn. Dưới đây là tóm tắt lý thuyết:



Để giải bài toán điểm hòa vốn ta cần xác định các dữ liệu, các biến, hàm mục tiêu và các mối quan hệ giữa các biến.

Ví dụ: Bài toán có các số liệu tóm tắt như sau: Định phí là 5 triệu đồng, giá bán sản phẩm là 14.000 đồng và chi phí để sản xuất một đơn vị sản phẩm là 6.000 đồng. Xác định điểm hòa vốn và vẽ đồ thị.

Tính điểm hòa vốn

Cách 1. Dùng công thức tính điểm hòa vốn



Cách 2. Dùng Goal Seek

B1. Lập bài toán trên Excel: nhập các biến, thiết lập hàm mục tiêu và các quan hệ như hình bên dưới




B2. Chọn ô có địa chỉ B12, sau đó chọn Ribbon à nhóm Data à Data Tools à What-If Analysis à Goal Seek … (Excel phiên bản cũ chọn Tools à Goal Seek… và khai báo các thông số như hình bên dưới.



B3. Nhấp nút OK để chạy Goal Seek. Kết quả cần tìm sẽ hiển thị tại ô B7 (sản lượng) và giá trị của hàm mục tiêu lợi nhuận sẽ hiện tại ô B12 lúc này bằng 0.
B4. Để cho kết quả báo cáo được sinh động và trực quan, chúng ta nên vẽ đồ thị để minh họa. Lập bảng số liệu cần thiết cho vẽ đồ thị như hình dưới và tiến hành vẽ đồ thị.



Giải phương trình

Ví dụ: Giải phương trình bậc hai x2 + 5x – 6 = 0
B1. Xác định biến, hàm mục tiêu và lập mô hình trên bảng tính
·Tại ô A6 và A7 nhập các giá trị khởi động bất kỳ cho biến x (ví dụ là 2)
·Tại ô B6 và B7 nhập các công thức theo phương trình đề cho để tính f(x)







B2. Chọn ô B6, sau đó chọn Ribbon à nhóm Data à Data Tools à What-If Analysis à Goal Seek … (Excel phiên bản cũ chọn Tools à Goal Seek…) và khai báo như hình bên dưới. Nhấp nút OK để chạy Goal Seek.






B3. Sau quá trình chạy Goal Seek thì hộp thoại thông báo xuất hiện. Nhấp OK để chấp nhận kết quả hoặc nhấp Cancel để hủy kết quả chạy Goal Seek. Khi đó ta nhìn vào ô A6 thấy được nghiệm thứ nhất là 1.

B4. Phương trình bậc hai có tối đa hai nghiệm, do vậy ta cần chạy Goal Seek một lần nữa để tìm nghiệm còn lại x2.

Ghi chú: Để tránh lần chạy Goal Seek thứ hai trả về cùng kết quả với lần chạy thứ nhất, ta hãy cho giá trị khởi động x2 một con số âm rất nhỏ (Ví dụ: -10000) rồi chạy Goal Seek. Nếu kết quả trùng với lần chạy đầu tiên thì hãy cho lại giá trị khởi động x2 là một con số dương lớn (Ví dụ: 10000) rồi chạy lại Goal Seek.

B5. Cho lại giá trị khởi động tại ô A7 là -10000, sau đó chọn ô B7 và vào thanh Ribbon à nhóm Data à Data Tools à What-If Analysis à Goal Seek … (Excel phiên bản cũ chọn Tools à Goal Seek…) và khai báo như hình bên dưới. Nhấp nút OK để chạy Goal Seek.






B6. Sau quá trình chạy Goal Seek thì hộp thoại thông báo xuất hiện. Nhấp OK để chấp nhận kết quả hoặc nhấp Cancel để hủy kết quả chạy Goal Seek. Khi đó ta nhìn vào ô A7 ta thấy được nghiệm thứ hai là -6.

(Kết thúc phân tích nhân quả)

Phân tích độ nhạy

Là dạng phân tích nhằm trả lời câu hỏi " What - if" (điều gì sẽ xảy ra...nếu như). Phân tích này xem xét yếu tố đầu vào nào là quan trọng nhất (yếu tố mang tính chất rủi ro) ảnh hưởng đến kết quả bài toán (lợi nhuận hoặc chi phí). Trong Excel hỗ trợ phân tích độ nhạy 1 chiều hoặc hai chiều, nghĩa là chỉ đánh giá được tối đa 2 yếu tố rủi ro



Một người kinh doanh mua một mặt hàng với giá mua là 8$ và bán với giá bán là 10$. Lợi nhuận sẽ là : 10$ - 8$ = 2$

Lập bài toán trên bảng tính :




Giá mua biến động từ 4$ đến 13$, mỗi bước thay đổi là 1$. Xét độ nhạy của lợi nhuận. Lập bảng phân tích một chiều như sau :



Chạy độ nhạy
1. Chọn B1:C20
2. Data / Table. Nhấp OK





Giá mua biến động từ 4$ đến 13$. Giá bán biến động từ 6$ đến 15$, mỗi bước thay đổi là 1$, xét độ nhạy của lợi nhuận

Lập bảng phân tích 2 chiều :





Phân tích tình huống


Là dạng phân tích What - If, phân tích tình huống thừa nhận rằng các biến nhất định có quan hệ hỗ tương với nhau. Do vậy, một số ít biến số có thể thay đổi theo một kiểu nhất định tại cùng một thời điểm. Tập hợp các hoàn cảnh có khả năng kết hợp lại để tạo ra các "trường hợp" hay các "tình huống " khác nhau là :

- Trường hợp xấu nhất / Trường hợp bi quan
- Trường hợp kỳ vọng / Trường hợp ước tính tốt nhất
- Trường hợp tốt nhất / Trường hợp lạc quan

Một người kinh doanh mua một mặt hàng giá 8$ và bán với giá 10$. Kết quả khảo sát có thể thấy mặt hàng trên có thay đổi như sau :



Xét lợi nhuận trong các trường hợp :



Cách thực hiện :


1. Chọn Tools / Scenario
2. Nhấp Add - Đặt tên cho các tình huống và chọn vùng địa chỉ các ô chứa biến rủi ro


3. Nhập các giá trị cho tình huống tốt nhất. Nhấp OK sau khi nhập xong
4. Nhấp nút Add và thêm vào các tình huống còn lại








Mô phỏng bằng Crystal Ball
- Mở rộng khả năng dự báo của mô hình bảng tính
- Cung cấp, thông tin dự báo cần thiết hỗ trợ ra quyết định với độ chính xác cao hơn, hiệu quả và tin cậy hơn
- Sử dụng mô phỏng Monte Carlo (Hệ thống sử dụng các số ngẫu nhiên để đo ảnh hưởng của sự không chắc chắn trong mô hình bảng tính)



Cách thực hiện :

- Khởi động Add-in từ màn hình Excel. Vào Tools / Add-in, chọn Crystal Ball.
- Có thể vào Start / Programs / Crystal Ball / Crystal Ball

Khi thóat, ta bỏ dấu chọn Crystal Ball từ hộp thoại Tools / add-in, hoặc vào Menu Run / chọn Close Crystal Ball



Quá trình thao tác :

- Lập mô hình bảng tính Excel
- Khai báo các biến giả thuyết
- Khai báo các biến dự báo
- Khai báo các thông số mô phỏng
- Chạy mô phỏng
- Xem xét kết quả mô phỏng
- Tạo các báo cáo



B1 : Vào Cell / Define Assumption
B2 : Chọn kiểu phân phối xác suất
B3 : Nhập các thông số tương ứng







B1 : Vào Cell / Define ForeCast



B2 : Nhập tên, đơn vị tính cho biến



B3 : Chọn More để khai báo thêm



Ở đây, ta khai báo số lần mô phỏng, giá trị hạt nhân ban đầu, chế độ màn hình khi chạy






- Vào Run / chọn Run






Các kết quả thể hiện ở dưới các dạng khác nhau :








- Vào Run / chọn Create Report



Tính chính xác của mô phỏng :

- Mẫu lớn --> nâng cao tính chính xác. Tuy nhiên, mẫu càng lớn càng làm tăng thêm yêu cầu bộ nhớ
- Mẫu > 100 --> đủ để tạo các kết quả chấp nhận được

Sử dụng bộ nhớ :

- Mỗi giả thuyết tốn 300 bytes
- Mỗi dự báo tốn 2800 bytes, 10 bytes cho mỗi lần thử
- Nếu có sử dụng tương quan, lấy mẫu siêu lập phương Latin và phân tích độ nhạy thì bộ nhớ tối thiểu : 10 x kích thước mẫu x số giả thuyết

Tốc độ mô phỏng :

- Phụ thuộc kích thước mô hình phức tạp hay đơn giản
- Phụ thuộc bộ nhớ của máy tình
- Số lượng các biến giả thuyết và dự báo
- Dùng Brust Mode (50 hoặc 100) để tối đa hóa tốc độ mô phỏng
- Phụ thuộc các chương trình ứng dụng khác đang mở

tks ban nhiu