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). Show 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” 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ĩnhB1. 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 ô C -Giá trị thanh lý Nhà xưởng tại ô I16 chính là tham chiếu ô C -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=SUM(C11:C16) sau đó chép công thức cho các ô D17:I -Chi phí đầu tư Đất tại ô C21 chính là tham chiếu ô C -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=SUM(C20:C23) sau đó chép công thức cho các ô D24:I -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:I -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% > 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ềuTạ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:I 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 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. 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à:
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
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. 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 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. 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. đồng và chi phí để sản xuất một đơn vị sản phẩm là 6 đồ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 Giải phương trình với GoalSeek 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 |
