Phân tích nhân quả (What-If) (1 người xem)

[TranThanhPhong]

5.2. Phân tích nhân quả (What – If Analysis)

5.2.1. 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[FONT=&quot]ư[FONT=&quot]u ý[/FONT] phân tích độ nhạy không xét đến mối quan hệ tương quan giữa các biến.

[/FONT] Bài toán tĩnh

[FONT=&quot]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.
[/FONT] [FONT=&quot]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:
[/FONT] -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)

[FONT=&quot]

[/FONT] 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.

TP.

 

[TranThanhPhong]

Phân tích nhân quả (What-If) (P1)

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

TP.

 

[TranThanhPhong]

Phân tích nhân quả (What-If) (P1)

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ị”.

TP.

 

[TranThanhPhong]

Phân tích nhân quả (What-If) (P2) - Scenario

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.

TP.

 

[TranThanhPhong]

Phân tích nhân quả (What-If) (P3) - Goal Seek

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ị.

(Còn tiếp)

TP.

 

[TranThanhPhong]

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ả)

TP.

 

[superquaivat1]

Bác ttphong ơi, cho em hỏi với. Em sử dụng công cụ phân tích tình huống vào bài toán của bác. Nhưng khi áp dụng theo kiểu trong mỗi thay đổi giá và chi phí đơn vị, có 3 trường hợp nhỏ thay đổi giá thanh lý xưởng và giá đất thì kết quả phân tích cho NPV đúng, còn IRR toàn thấy hiển thị 1 hết bác ạ, kích đúp vào hiện ra kết quả lại vẫn đúng. Bác giúp em với.

 

[helloGPE]

Bác ttphong ơi, cho em hỏi với. Em sử dụng công cụ phân tích tình huống vào bài toán của bác. Nhưng khi áp dụng theo kiểu trong mỗi thay đổi giá và chi phí đơn vị, có 3 trường hợp nhỏ thay đổi giá thanh lý xưởng và giá đất thì kết quả phân tích cho NPV đúng, còn IRR toàn thấy hiển thị 1 hết bác ạ, kích đúp vào hiện ra kết quả lại vẫn đúng. Bác giúp em với.

Bác kiểm tra lại Tools , Options, Caculation , chọn Automatic hay chưa?

 

[suyuan]

Bác ttphong ơi, bài mà bác đưa ra em làm theo ok hết, nhưng trong trường hợp dự án của em có nhiều giá đơn vị thì làm sao để tính. Nếu lấy giá bình quân thì trong bảng tính độ nhạy không chạy, hiện ra 1 số giống nhau cho tất cả các ô.
Mong bác chỉ giáo.

 

[TranThanhPhong]

Bác ttphong ơi, bài mà bác đưa ra em làm theo ok hết, nhưng trong trường hợp dự án của em có nhiều giá đơn vị thì làm sao để tính. Nếu lấy giá bình quân thì trong bảng tính độ nhạy không chạy, hiện ra 1 số giống nhau cho tất cả các ô.
Mong bác chỉ giáo.

Phân tích độ nhạy dùng lệnh Table chỉ cho phép tối đa 2 yếu tố biến đổi. Trường hợp bạn nêu là có nhiều hơn 2 yếu tố phải dùng công cụ Add-Ins phân tích độ nhạy chuyên dụng hoặc dùng tới Crystal Ball.

http://www.treeplan.com/

Thanh Phong

 

[nicnic2111]

bác TTPhong ơi! cháu làm xong cái data table- đỡ tay nhấn vào ô trong bảng tính- thay đổi ô rùi hông thoát ra được- bác biết cách không bày cho cháu với♫♪

 

[TranThanhPhong]

bác TTPhong ơi! cháu làm xong cái data table- đỡ tay nhấn vào ô trong bảng tính- thay đổi ô rùi hông thoát ra được- bác biết cách không bày cho cháu với♫♪

@nicnic2111:

- Table là mảng nên không thể sửa đổi riêng lẻ các ô được mà cần phải xóa hết mảng và thực hiện lại lệnh.

- Nhấn phím ESC trên bàn phím để hủy bỏ thao tác hiệu chỉnh --> sẽ thoát ra được.

TTP

 

[bodien19]

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ị”.

TP.

Dear a Phong,
E làm theo cách này, nhưng mà NPV và IRR của e vẫn y xì, chẳng có gì thay đổi hết a ơi
Đầu tiên e tính ra NPV và IRR (là bài khác cái bài mẫu của a), của e là cho giá dầu diezel thay đổi, thay vì giá bán như của a.
E làm theo đúng trình tự của a khi tính độ nhạy, nhưng mà npv và irr của e chẳng có gì thay đổi hết :|

 

[TranThanhPhong]

Dear a Phong,
E làm theo cách này, nhưng mà NPV và IRR của e vẫn y xì, chẳng có gì thay đổi hết a ơi
Đầu tiên e tính ra NPV và IRR (là bài khác cái bài mẫu của a), của e là cho giá dầu diezel thay đổi, thay vì giá bán như của a.
E làm theo đúng trình tự của a khi tính độ nhạy, nhưng mà npv và irr của e chẳng có gì thay đổi hết :|

Xem kết quả và ghi chú trong file đính kèm!

 

[bodien19]

OMG, nhanh vậy hả a ơi :O
Lỗi ngớ ngẩn như vậy ạ :">
Cảm ơn a nhiều nhé :X

 

[shincucu]

em chào anh Phong,
anh ơi anh cho em hỏi là tại sao em làm theo cách anh chỉ về bài scenario nhưng sao khi em làm về scenario summary lại không hiện ra kết quả của IRR và NPV anh nhỉ.mong anh giải đáp giùm em,
Em xin chân thành cám ơn

 

[vhh]

Nếu sắp xếp giá và số lượng như thế này và thu nhập= giá đơn vị x số lượng từng năm khi tính độ nhạy sẽ cho kết quả sai, Trong trường hợp này phải dùng Add-In để tính à?

Báo cáo ngân lưu
Năm 0 1 2 3 4 5 6
Các khoản thu
Giá đơn vị 50 50 50 50 50
Số lượng 1.000 1.000 1.000 1.000 1.000
Thu nhập 50.000 50.000 50.000 50.000 50.000

 

[nthanh2410]

giả sử giá phòng có 3 loại,phân tích độ nhạy 1 chiều với sự thay đổi của giá phòng thì phải sao?lấy trung bình 3 giá phòng hay sao

 

[nguyenhuong91]

anh ơi! em làm về biến động của lợi nhuận nhưng khi làm như vậy kết quả nó ra bằng nhau hết anh ạ.
dòng của em là biến động về tỷ lệ tăng trưởng doanh thu
còn cột là biến động về tỷ lệ giá vốn trên doanh thu
Vậy em sao chỗ nào hả anh?
Mong anh giải đáp giúp em với ạ!
Em cảm ơn nhiều nhiều lắm

 

[giapcaonguyen]

Em đang tìm hiểu cái món NPV, IRR, độ nhạy này, em gà lắm, nhưng cảm nhận bài viết rất bổ ích, cám ơn Bác!