Hướng dẫn Phân tích Độ nhạy trong Excel: Tổng quan, Các loại và Thực tiễn Tốt nhất

Khi thiết kế một mô hình trên bảng tính, chúng ta có thể muốn biết kết quả sẽ thay đổi ra sao khi thay đổi giá trị của một trong các biến liên quan. Quy trình này có thể thực hiện trong Excel bằng cách chạy phân tích độ nhạy

Biết cách thực hiện phân tích độ nhạy là điều quan trọng nếu bạn là người làm dữ liệu trong lĩnh vực mô hình tài chính hoặc các quy trình ra quyết định. 

Trong bài viết này, chúng ta sẽ đi sâu vào phân tích độ nhạy. Chúng ta sẽ trình bày những điều cơ bản của kỹ thuật hữu ích này trong nhóm phân tích “giả định” (what-if) và cách thực hiện cũng như diễn giải kết quả của phân tích độ nhạy cơ bản và nâng cao trong Excel. Cuối cùng, chúng ta sẽ thảo luận vai trò của AI và máy học trong việc nâng cao các phân tích độ nhạy mạnh mẽ hơn, cùng các thực tiễn tốt nhất và những cạm bẫy thường gặp. 

Nếu bạn muốn thực hành trực tiếp, hãy xem khóa học Phân tích Tài chính trong Excel của chúng tôi, khóa này trình bày chi tiết phân tích what-if. 

Phân tích Độ nhạy trong Excel là gì?

Cách tiếp cận phổ biến nhất trong phân tích độ nhạy là xem xét việc thay đổi từng biến một sẽ ảnh hưởng đến kết quả cuối cùng như thế nào trong khi giữ các yếu tố khác không đổi. Quy trình này được lặp lại cho tất cả các biến đang xem xét. Nó giúp xác định các yếu tố tác động mạnh nhất trong mô hình, cho phép doanh nghiệp tập trung vào các yếu tố then chốt ảnh hưởng đến hiệu quả hoạt động. 

Phân tích độ nhạy giúp doanh nghiệp ưu tiên các rủi ro và cơ hội chính bằng cách xác định những yếu tố có tác động lớn nhất đến hiệu suất. Do đặc thù, phân tích độ nhạy rất phổ biến trong tài chính, kinh doanh và quản lý dự án, ví dụ để tính toán lãi suất có thể thay đổi theo thời gian hoặc để hiểu các xu hướng tăng trưởng khác nhau. 

Hơn nữa, độ nhạy thường được kết hợp với phân tích kịch bản, vốn thường phân tích sự kết hợp của các biến cùng lúc. Cả hai phân tích what-if này đều nghiên cứu cách một biến phụ thuộc phản ứng trước các đầu vào nhất định. Tuy nhiên, phân tích kịch bản cụ thể cho một “kịch bản” nhất định, trong khi phân tích độ nhạy mở rộng hơn vì nó đưa ra một dải đầu vào và giá trị. 

Mục tiêu của phân tích độ nhạy là hiểu cách biến phụ thuộc phản ứng trước một dải giá trị đầu vào, còn gọi là các biến độc lập.

Bằng cách đưa kỹ thuật này vào quá trình ra quyết định, tổ chức có thể chủ động điều chỉnh chiến lược và tăng cường khả năng chống chịu trước bất định. 

Để có lời giải thích chi tiết hơn về lý thuyết phía sau phân tích độ nhạy, chúng tôi khuyến nghị khóa học Xác suất Nâng cao: Bất định trong Dữ liệu.

Thường thì phân tích độ nhạy được trình bày trong một bảng độ nhạy với định dạng có điều kiện làm nổi bật các giá trị từ cao xuống thấp. 

Dưới đây là ví dụ về độ nhạy giá của cung và cầu. Cung và cầu là các biến độc lập được gắn nhãn trên trục, còn giá là biến phụ thuộc, chứa các giá trị trong bảng.

Chúng ta sẽ làm việc với các bảng độ nhạy Excel tương tự trong các phần tiếp theo.

Bảng độ nhạy. Nguồn: DataCamp

Bắt đầu với Phân tích Độ nhạy trong Excel

Hãy xem cách thực hiện phân tích độ nhạy trong Excel theo từng bước của quy trình. 

Công cụ và tính năng trong Excel

Excel là công cụ tiện lợi để tạo các mô hình toán học, bao gồm phân tích what-if. Phân tích what-if về bản chất là tạo các công thức cho phép khám phá các câu hỏi, như phân tích độ nhạy.

Hầu hết các phiên bản Excel gần đây đều có Thẻ Data, cung cấp vô số công cụ và khả năng làm việc với dữ liệu. Điều này bao gồm nút What-if Analysis trên dải băng. Nó bao gồm ba công cụ. Nút Data Table được thiết kế riêng để tạo các bảng độ nhạy.

Để thực hiện phân tích kịch bản, chỉ cần nhấp vào nút Scenario Manager.

Chúng ta cũng sẽ sử dụng Solver, một tiện ích bổ sung bạn có thể kích hoạt từ Thẻ Developer. 

Solver được dùng trong các phân tích what-if để tìm giá trị tối ưu (cực đại hoặc cực tiểu) cho một công thức trong một ô — gọi là ô mục tiêu – với các ràng buộc hoặc giới hạn đối với giá trị của các ô công thức khác trong trang tính.

Trước khi bật Solver, bạn phải truy cập Thẻ Developer, thẻ này không hiển thị theo mặc định. Để hiển thị thẻ này:

• Vào File > Options
• Chọn Customize Ribbon ở bảng bên trái
• Ở cột bên phải dưới mục "Main Tabs", đánh dấu chọn Developer

Thực hiện Phân tích Độ nhạy một biến

Khi đã sẵn sàng công cụ, hãy bắt đầu bằng cách tạo một phân tích độ nhạy đơn giản với một biến trong Excel.

Thiết lập mô hình của bạn

Bước đầu tiên để thực hiện phân tích độ nhạy trong Excel là xác định các đầu vào và đầu ra mà mô hình của bạn sẽ dựa vào. 

Trong hướng dẫn này, chúng ta sẽ dùng phân tích độ nhạy để nghiên cứu lợi nhuận ròng của một công ty bán đàn guitar sẽ thay đổi thế nào nếu chúng ta thay đổi giá trị của một số biến đầu vào, như số lượng guitar bán ra, giá bán mỗi cây, hoặc chi phí sản xuất.

Bên dưới, bạn có thể thấy các bảng chứa các biến đầu vào và đầu ra. 

Khi dùng bảng dữ liệu cho phân tích độ nhạy, điều quan trọng là liên kết các ô đầu ra với các biến đầu vào để Excel hiểu mối quan hệ giữa các biến của mô hình. Quy trình này được thực hiện qua các công thức. Trong ví dụ của chúng ta:

• Doanh thu được tính bằng cách nhân giá mỗi đơn vị với số đơn vị bán ra (=B2*B4)
• Giá vốn hàng bán được tính bằng cách nhân chi phí sản xuất mỗi đơn vị với số đơn vị bán ra (=B3*B4)
• Lợi nhuận được tính bằng cách lấy doanh thu trừ đi giá vốn hàng bán (=B7-B8).

Tạo bảng dữ liệu một chiều

Bạn có thể muốn biết lợi nhuận thay đổi thế nào nếu tăng hoặc giảm giá mỗi đơn vị, trong khi giữ nguyên chi phí sản xuất và số lượng bán. Khi chúng ta phân tích cách một biến đầu ra thay đổi khi điều chỉnh giá trị của một biến đầu vào, chúng ta cần một bảng dữ liệu một chiều.

GIF sau đây cho thấy cách tạo một bảng dữ liệu một chiều trong Excel. Như bạn thấy, bảng sẽ tự động tính lại lợi nhuận cho mỗi số lượng bán ra. Tự nhiên, lợi nhuận khi bán 250 đơn vị sẽ giống như ở các tham số ban đầu (tức 50.000 €).

Bạn cũng có thể muốn nghiên cứu cách các biến đầu ra khác nhau thay đổi khi chúng ta điều chỉnh số lượng bán ra. Việc này cũng khá đơn giản, như minh họa bên dưới. Trong GIF sau, chúng ta tính cách số lượng bán ra ảnh hưởng đến cả lợi nhuận và giá vốn hàng bán.

Thực hiện Phân tích Độ nhạy hai biến

Thay vì phân tích cách một đầu vào ảnh hưởng đến đầu ra, bạn cũng có thể tạo một bảng hai biến để phân tích tác động của hai biến đầu vào. Giả sử bạn muốn biết lợi nhuận thay đổi thế nào nếu chúng ta điều chỉnh số lượng guitar bán ra và giá mỗi cây. 

Trước tiên, bạn cần tạo một bảng hai chiều với các khoảng giá mỗi đơn vị ở các hàng và số lượng bán ra ở các cột. Sau đó, ở góc trên bên trái của bảng, chọn biến bạn muốn nghiên cứu. Cuối cùng, chọn tham chiếu ô của các biến độc lập trong bảng biến đầu vào (tức giá mỗi đơn vị và số lượng bán).

Như bạn thấy, sau khi tạo bảng, bạn có thể dễ dàng thay đổi biến đầu ra ở góc trên bên trái sang một biến khác, và Excel sẽ tính lại toàn bộ giá trị

Diễn giải Kết quả Phân tích Độ nhạy trong Excel

Giờ bạn đã có các bảng dữ liệu, bước cuối cùng là diễn giải kết quả để rút ra insight về cách thay đổi đầu vào tác động đến đầu ra.

Phân tích các đầu ra của bảng dữ liệu

Dựa trên phân tích độ nhạy này, chúng ta có thể đưa ra quyết định sáng suốt về lợi nhuận. Dưới đây là một số nhận định:

• Nếu chúng ta tăng giá guitar lên 350 €, chúng ta có thể đạt mức lợi nhuận hiện tại 50.000 € chỉ với 200 cây bán ra.
• Nếu chúng ta giảm giá xuống 200 €, chúng ta sẽ cần bán 500 đơn vị để đạt mức lợi nhuận hiện tại.
• Nếu chúng ta giảm số guitar bán ra xuống 150, chúng ta sẽ không đạt lợi nhuận hiện tại trừ khi tăng giá guitar lên trên 400 €.

Việc diễn giải phân tích sẽ phụ thuộc vào nhu cầu cụ thể của công ty bạn. Đặt ra những câu hỏi như bạn có thể sản xuất bao nhiêu guitar, mức lợi nhuận tối thiểu cần thiết để công ty duy trì ổn định là bao nhiêu, và bạn có thể làm gì để giảm chi phí sản xuất guitar sẽ rất quan trọng để rút ra insight hữu ích từ phân tích độ nhạy.

Sử dụng Solver cho phân tích độ nhạy

Khi bạn thực hiện phân tích độ nhạy, bạn đang trả về một dải đầu ra tiềm năng dựa trên các thay đổi ở biến đầu vào của mô hình. Tuy nhiên, bạn có thể muốn biết thêm chi tiết về cơ chế bên trong của mô hình.

May mắn thay, Excel có một công cụ mạnh mẽ gọi là Solver, có thể hỗ trợ bạn trong nhiệm vụ này. Như đã đề cập, Solver được dùng trong các phân tích what-if để tìm giá trị tối ưu (cực đại hoặc cực tiểu) cho một công thức với các ràng buộc hoặc giới hạn đối với giá trị của các ô công thức khác trên trang tính. 

Nghe có vẻ giống mục tiêu của Goal Seek, một dạng phân tích What-If khác tập trung điều chỉnh một biến để đạt kết quả mong muốn. Tuy nhiên, Solver còn nhiều hơn Goal Seek. 

Thứ nhất, Solver cho phép bạn tính kết quả tối ưu dựa trên nhiều hơn một biến và cho phép đưa ràng buộc vào mô hình. 

Hơn nữa, Solver còn có tính năng độ nhạy tùy chọn, cung cấp cho bạn cái nhìn về cách nghiệm tối ưu thay đổi khi bạn thay đổi các hệ số của mô hình. 

Để minh họa sức mạnh của Solver, hãy quay lại công ty guitar của chúng ta. Hãy tưởng tượng công ty sản xuất hai mẫu guitar (mẫu A và B), mỗi mẫu yêu cầu một tổ hợp nhất định của gỗ gụ (mahogany) và gỗ tuyết tùng (cedar). Mỗi mẫu có một mức giá khác nhau. 

Công ty muốn biết nên sản xuất bao nhiêu đơn vị của mỗi mẫu để tối đa hóa lợi nhuận, với mức sẵn có hiện tại của gỗ gụ và gỗ tuyết tùng. 

Có ba thứ chúng ta muốn Solver tính: Lợi nhuận, số đơn vị của Mẫu A và số đơn vị của Mẫu B. Hình trên cho thấy các đầu vào, công thức và ràng buộc mà mô hình sẽ dựa vào. 

Trong GIF sau, chúng ta minh họa cách dùng Solver để tìm giá trị tối ưu của số đơn vị nhằm tối đa hóa lợi nhuận, sử dụng các ràng buộc về lượng gỗ sẵn có và đảm bảo số đơn vị là số nguyên. Có nhiều chiến lược Solver có thể dùng để tối ưu mô hình, cả cho bài toán tuyến tính và phi tuyến. Trong trường hợp của chúng ta, sẽ dùng Simple LP. 

Khi thêm tất cả tham số, Solver ước tính rằng 48 đơn vị mẫu A và 9 đơn vị mẫu B là tổ hợp tốt nhất để tối đa hóa lợi nhuận (17.591 €). Chúng ta có thể nhấp vào Sensitivity trước khi đóng hộp thoại Solver, và Excel sẽ tạo một trang tính mới với báo cáo Độ nhạy.

Báo cáo độ nhạy cung cấp cho chúng ta những insight giá trị để ước tính lợi nhuận sẽ thay đổi thế nào nếu chúng ta thay đổi các biến đầu vào. Shadow Price cho biết lợi nhuận sẽ tăng bao nhiêu nếu bạn có thêm một đơn vị của một nguồn lực bị ràng buộc. Trong ví dụ này, mỗi mét vuông gỗ gụ bổ sung làm tăng lợi nhuận thêm 98 €, trong khi gỗ tuyết tùng tăng 103 €, cho thấy tiềm năng lợi tức đầu tư lớn hơn của gỗ tuyết tùng

Một yếu tố quan trọng khác trong báo cáo độ nhạy là các khoảng mà trong đó bạn có thể giữ giá của các mẫu để kết quả mô hình không đổi. 

Lợi nhuận được tính bằng cách nhân số đơn vị của mỗi mẫu với giá của nó, thể hiện trong các cột Final Value và Objective Coefficient. Các khoảng được nêu trong các cột Allowable Increase và Allowable Decrease. 

Ví dụ, Solver sẽ tìm cùng số lượng đơn vị cần sản xuất nếu bạn tăng giá trị của Mẫu A thêm 1.140 € hoặc giảm đi 180 €.

Trực quan hóa dữ liệu với định dạng có điều kiện

Nếu bạn tạo các bảng độ nhạy một chiều và hai chiều, định dạng kết quả có thể tạo khác biệt lớn giúp nhanh chóng phát hiện thông tin liên quan và nâng cao việc ra quyết định. Định dạng có điều kiện là chìa khóa để tạo các ngưỡng quan trọng và heatmap trực quan. 

Quay lại bảng hai chiều của chúng ta. Thay vì hiển thị bảng với các ô trắng, chúng ta có thể dùng định dạng có điều kiện để tạo heatmap đa màu hoặc thang hai màu, thiết lập một ngưỡng tùy chỉnh, như minh họa trong GIF.

Tích hợp AI và Máy học

Hãy điểm qua nhanh cách AI và máy học có thể giúp cải thiện phân tích độ nhạy của bạn.

Phân tích độ nhạy tăng cường bởi AI

Cho đến giờ, chúng ta đã thấy cách thực hiện phân tích độ nhạy thủ công. Tuy nhiên, khi độ phức tạp của mô hình tăng lên, việc thực hiện và diễn giải kết quả phân tích độ nhạy có thể tốn nhiều thời gian. 

Đây là lúc AI xuất hiện. Với AI và máy học, bạn có thể để máy móc thực hiện các phép tính mỗi khi điều kiện nhất định được đáp ứng và chèn kết quả vào các bảng điều khiển trực quan. 

Khi đã biết các phép tính bạn muốn thực hiện, bước tiếp theo là xác định rõ một pipeline AI để hợp lý hóa và tự động hóa quy trình. 

Bạn tò mò cách làm? Hãy xem Hướng dẫn về Máy học, Pipeline, Triển khai và MLOps của chúng tôi.

Mô phỏng Monte Carlo

Mô phỏng Monte Carlo là một kỹ thuật toán học dùng để mô hình hóa xác suất của các kết quả khác nhau trong một quá trình khó dự đoán do sự can thiệp của các biến ngẫu nhiên.

Bài toán trước tiên được mô hình hóa bằng một phân phối xác suất cho mỗi biến có sự bất định vốn có. Sau đó lấy một số lượng lớn mẫu ngẫu nhiên từ các phân phối xác suất này, và dùng các mẫu đó để tính toán kết quả. 

Quy trình này được lặp lại nhiều lần để tạo ra một phân phối các kết quả có thể, có thể được phân tích thống kê để đưa ra dự đoán về cách hệ thống sẽ vận hành. Monte Carlo giúp phân tích bất định toàn diện hơn so với các bảng độ nhạy truyền thống.

Bạn có thể học mọi thứ về kỹ thuật mạnh mẽ này trong Hướng dẫn Mô phỏng Monte Carlo trong Excel của chúng tôi.

Với bản chất của mô phỏng Monte Carlo, bạn có thể dùng nó để tăng cường phân tích độ nhạy nhằm tìm ra biến nào có tác động đáng kể nhất đến đầu ra. 

Trong ví dụ guitar của chúng ta, ta có thể giữ nguyên giá vốn và số lượng bán ra, thay đổi giá bán đơn vị để hiểu các thay đổi trong ước tính. Sau đó, lặp lại quy trình cho hai biến còn lại lần lượt. Cuối cùng, kỹ thuật này giúp hiểu nên tập trung vào biến nào để cải thiện độ chính xác.

Thực tiễn Tốt nhất và Cạm bẫy Thường gặp

Mặc dù Excel giúp phân tích độ nhạy trở nên rất dễ dàng và dễ tiếp cận, vẫn có một số cạm bẫy phổ biến cần lưu ý. Dưới đây là vài điểm cần cân nhắc.

Thiết kế mô hình vững chắc

Như bạn đã thấy trong hướng dẫn này, để phân tích độ nhạy hoạt động, Excel phải biết mối quan hệ giữa các biến đầu vào và đầu ra. Điều này đòi hỏi bạn sử dụng công thức hợp lý, cung cấp các tham chiếu ô và phạm vi được xác định rõ ràng. 

Nếu bạn muốn thêm giá trị mới vào các bảng dữ liệu, một thực hành tốt để tránh các vấn đề tiềm ẩn trong quá trình tính lại là sử dụng Excel Tables, vì chúng vốn dĩ linh hoạt, nghĩa là tự động mở rộng khi thêm hàng hoặc cột mới. 

Điều này sẽ giúp bạn thực hiện đúng các phép tính lại công thức để giữ thông tin ở đúng định dạng. 

Giảm thiểu lỗi và đảm bảo độ chính xác

Ngay cả khi phân tích độ nhạy của bạn được xác định rõ, bạn vẫn cần kiểm tra cẩn thận kết quả. Kiểm thử thủ công và biểu đồ là lựa chọn tuyệt vời để trực quan hóa kết quả và kiểm tra các điểm ngoại lệ hoặc bất thường trong đầu ra.  

Tuy nhiên, cần nhớ rằng phân tích độ nhạy có thể đơn giản là không phù hợp với trường hợp của bạn. Phân tích này dựa trên một tập giả định đơn giản, như tính độc lập của các biến đầu vào, sự tồn tại của mối quan hệ tuyến tính và tính tĩnh của các biến đầu vào. 

Tuy vậy, mô hình của bạn có thể phức tạp hơn và có thể bị ảnh hưởng bởi các yếu tố bên ngoài mà phân tích độ nhạy theo bản chất không xét đến. Do đó, bất chấp lợi ích của nó, điều quan trọng là có một hiểu biết toàn diện về mô hình của bạn vượt ra ngoài độ nhạy để đảm bảo phân tích chính xác.

Kết luận

Chúc mừng bạn đã đọc đến cuối hướng dẫn này. Phân tích độ nhạy là một dạng phân tích what-if mạnh mẽ có thể tạo khác biệt trong quy trình ra quyết định của bạn. Vẫn còn các phân tích what-if khác để bổ trợ cho mô hình tài chính của bạn, như Goal Seek hoặc Scenario Manager. May mắn là DataCamp luôn sẵn sàng hỗ trợ bạn. Hãy xem các khóa học và tài liệu Excel đã được tuyển chọn của chúng tôi:

• Data Analysis in Excel
• Financial Modeling in Excel
• Excel Fundamentals
• Data Analysis in Google Sheets
• Advanced Excel Functions
• How to Calculate Confidence Intervals in Excel
• Excel Formulas Cheat Sheet
• How to Calculate the Coefficient of Variation in Excel
• Monte Carlo Simulation in Excel: A Complete Guide