Phân Tích độ Nhạy - Giáo Trình MS Excel

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 tố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 tốn cĩ dùng Solver. Phân tích độ nhạy khơng xét đến mối quan hệ tương quan giữa các biến.

B1. Nhập các thơng số bài tốn và 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:

- Thu nhập = giá đơn vị * số lượng Ỵ D13=$C$4*$C$5sau đĩ 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)

Hình 7.1. Lập mơ hình bài tốn trên bảng tính

Giá trị NPV ở trên là $3275 ởmức chiết khấu 10%. 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.

Cải tiến phân tích xác định trên bằng việc kiểm tra độ nhạy của NPV đối với sự thay đổi của một biến đầu vào “Giá đơn vị” và phân tích NPV đối với sự thay đổi của hai biến đầu vào “Giá đơn vị” và “Chi phí đơn vị” 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 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. Giá đơn vị 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 Ỵ ơ NPV: C26

B3. Đặt thêm các nhãn cho yếu tố đầu vào và nhãn cho giá trị cần phân tích giúp bài tốn được rõ ràng hơn.

B4. Đánh dấu chọn cả vùng C34:I35

B5. Chọn thực đơ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).

Hình 7.2. Chọn địa chỉ của ơ là yếu tố rủi ro B7. Nhấp nút OK.

Hình 7.3. Kết quả phân tích độ nhạy một chiều – một yếu tố rủi ro.

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

Tại đây xét sự thay đổi của hai yếu tố “Giá đơn vị”, “Chi phí đơn vị” đầu vào tác động đến kết quả NPV. Giá đơn vị dao động từ $48 đến $53 và mỗi lần dao động 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ị cĩ thể cĩ 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.

B2. Tạo vùng chứa các giá trị cĩ thể cĩ 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.

B3. Tại ơ C43 tham chiếu đến địa chỉ ơ cần phân tích Ỵ ơ NPV: C26

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 giúp bài tốn được rõ ràng hơn.

B5. Đánh dấu chọn cả vùng C43:I54

B6. Chọn thực đơ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)

Hình 7.4. Khai báo địa chỉ chứa các yếu tố rủi ro B8. Nhấp nút OK.

Hình 7.5. Kết quả phân tích độ nhạy hai chiều – hai yếu tố rủi ro.

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