Cách thực hiện phân tích độ nhạy trong Excel: Từng bước một

Để thực hiện phân tích độ nhạy trong Excel, hãy đặt từng đầu vào của mô hình vào một ô riêng biệt. Sau đó, thay đổi các đầu vào đó và ghi lại kết quả thay đổi như thế nào. Sử dụng bảng dữ liệu một biến cho một đầu vào, bảng dữ liệu hai biến cho hai đầu vào và Scenario Manager cho các trường hợp đầy đủ. Kết thúc bằng biểu đồ lốc xoáy (tornado chart) để xếp hạng các đầu vào theo mức độ ảnh hưởng.

Hướng dẫn này giải thích phân tích độ nhạy là gì, cách thiết lập mô hình và cách sử dụng từng công cụ trong Excel. Nó bao gồm một ví dụ thực tế với các công thức cụ thể, một phương pháp nhanh hơn với AI và các sai lầm cần tránh.

Phân tích độ nhạy là gì

Phân tích độ nhạy cho thấy kết quả thay đổi bao nhiêu khi một giả định thay đổi. Nó trả lời các câu hỏi như "Điều gì xảy ra với lợi nhuận nếu giá giảm 10%?"

Hầu hết các mô hình kinh doanh đều dựa trên các dự đoán. Khối lượng bán hàng, giá cả, chi phí và tốc độ tăng trưởng đều là các ước tính. Phân tích độ nhạy kiểm tra xem dự đoán nào trong số đó là quan trọng nhất.

Excel nhóm các công cụ này dưới mục What-If Analysis. Microsoft định nghĩa đó là "quá trình thay đổi các giá trị trong ô" để xem kết quả của các công thức thay đổi như thế nào.

Microsoft liệt kê ba công cụ tích hợp sẵn: "Scenarios, Goal Seek, và Data Tables." Mỗi công cụ có một nhiệm vụ khác nhau:

• Data tables hiển thị một kết quả trên nhiều giá trị của một hoặc hai đầu vào.
• Scenario Manager lưu trữ các bộ đầu vào đầy đủ, chẳng hạn như trường hợp xấu nhất và trường hợp tốt nhất.
• Goal Seek hoạt động ngược lại. Nó tìm đầu vào cần thiết để đạt được một kết quả mục tiêu.

Phân tích độ nhạy thường sử dụng hai công cụ đầu tiên. Goal Seek giúp giải quyết một câu hỏi liên quan, chẳng hạn như giá điểm hòa vốn.

Thiết lập mô hình trước khi bắt đầu

Các công cụ của Excel chỉ hoạt động nếu mô hình được xây dựng một cách rõ ràng. Hãy kiểm tra các điểm này trước.

Đặt mọi đầu vào trong ô riêng của nó. Đơn vị, giá, chi phí và tỷ lệ mỗi thứ cần một ô. Đừng nhập số trực tiếp bên trong công thức.

Liên kết kết quả với các đầu vào. Ô kết quả, chẳng hạn như lợi nhuận, phải là một công thức tham chiếu đến các ô đầu vào. Nếu không, sẽ không có gì thay đổi khi Excel thay thế các giá trị mới.

Giữ các đầu vào cùng nhau. Nhóm chúng thành một khối ở đầu trang tính. Gắn nhãn cho từng cái và tô màu các ô đầu vào để chúng nổi bật.

Chọn một kết quả để kiểm tra. Phân tích độ nhạy sẽ dễ đọc hơn khi nó theo dõi một đầu ra, chẳng hạn như lợi nhuận, giá trị hiện tại thuần hoặc tiền mặt vào cuối năm.

Ghi chú trường hợp cơ sở của bạn. Viết ra các giá trị bắt đầu. Mọi kết quả trong phân tích đều được so sánh với cơ sở này.

Dưới đây là một mô hình lợi nhuận đơn giản để theo dõi. Đó là một cửa hàng xe đạp giả định, Northgate Cycles:

Ô	Nhãn	Giá trị
B1	Số lượng bán ra	2,400
B2	Giá mỗi đơn vị	$850
B3	Chi phí biến đổi mỗi đơn vị	$510
B4	Chi phí cố định	$520,000
B5	Lợi nhuận	=B1*(B2-B3)-B4

Tại các giá trị cơ sở này, lợi nhuận là $296,000.

Cách thực hiện phân tích độ nhạy một biến với bảng dữ liệu

Bảng dữ liệu một biến kiểm tra một phạm vi giá trị cho một đầu vào. Microsoft cho biết nó hiển thị "cách các giá trị khác nhau của một biến trong một hoặc nhiều công thức sẽ thay đổi kết quả."

Dưới đây là cách kiểm tra giá trong mô hình ví dụ:

1. Trong các ô từ D2 đến D6, nhập các mức giá cần kiểm tra: $800, $825, $850, $875, và $900.
2. Trong ô E1, phía trên một hàng và bên phải một cột so với mức giá đầu tiên, nhập =B5.
3. Chọn vùng D1:E6.
4. Trên tab Data, chọn What-If Analysis > Data Table.
5. Trong Column input cell, nhập B2, ô giá. Để trống Row input cell.
6. Nhấn OK.

Excel điền vào E2:E6 lợi nhuận cho mỗi mức giá. Vị trí đặt công thức rất quan trọng. Microsoft hướng dẫn nhập nó "phía trên một hàng và bên phải một ô so với cột giá trị."

Kết quả cho Northgate Cycles:

Giá	Lợi nhuận
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

Mỗi thay đổi $25 về giá làm lợi nhuận biến động $60,000. Đó chính là độ nhạy của lợi nhuận đối với giá.

Cách thực hiện phân tích độ nhạy hai biến

Bảng dữ liệu hai biến kiểm tra hai đầu vào cùng một lúc. Microsoft cho biết nó hiển thị "cách các giá trị khác nhau của hai biến trong một công thức sẽ thay đổi kết quả của công thức đó."

Dưới đây là cách kiểm tra giá và số lượng cùng nhau:

1. Trong một ô trống, chẳng hạn như D10, nhập =B5.
2. Bên dưới nó, từ D11 đến D13, nhập khối lượng đơn vị: 2,000, 2,400, và 2,800.
3. Bên phải nó, từ E10 đến G10, nhập các mức giá: $800, $850, và $900.
4. Chọn vùng D10:G13.
5. Chọn What-If Analysis > Data Table.
6. Trong Row input cell, nhập B2. Trong Column input cell, nhập B1.
7. Nhấn OK.

Kết quả là một lưới lợi nhuận:

Số lượng \ Giá	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

Lưới cho thấy cửa hàng vẫn có lãi trong mọi tổ hợp được kiểm tra. Góc yếu nhất, 2,000 đơn vị ở mức giá $800, vẫn tạo ra $60,000.

Có một giới hạn cứng. Microsoft cho biết "Một bảng dữ liệu không thể chứa nhiều hơn hai biến." Đối với nhiều đầu vào hơn, hãy sử dụng Scenario Manager.

Các bảng dữ liệu cũng tính toán lại bất cứ khi nào trang tính tính toán lại. Trong một sổ làm việc lớn, điều đó có thể làm chậm Excel. Microsoft gợi ý thiết lập tính toán sao cho các bảng dữ liệu chỉ tính toán lại khi bạn nhấn F9.

Cách so sánh các trường hợp đầy đủ với Scenario Manager

Scenario Manager lưu trữ các bộ đầu vào hoàn chỉnh và thay thế chúng. Microsoft mô tả một kịch bản là "một bộ giá trị mà Excel lưu lại và có thể thay thế tự động trên trang tính của bạn."

Dưới đây là cách thiết lập ba trường hợp:

1. Trên tab Data, chọn What-If Analysis > Scenario Manager.
2. Nhấp vào Add, đặt tên kịch bản là Base, và nhập B1:B4 làm các ô thay đổi. Giữ các giá trị hiện tại.
3. Thêm trường hợp Worst case: 2,000 đơn vị, giá $825, chi phí biến đổi $530 và chi phí cố định $540,000.
4. Thêm trường hợp Best case: 2,800 đơn vị, giá $875, chi phí biến đổi $495 và chi phí cố định $510,000.
5. Chọn một kịch bản và nhấp vào Show để tải các giá trị của nó.
6. Nhấp vào Summary và chọn B5 làm ô kết quả để xây dựng báo cáo.

Đối với Northgate Cycles, bản tóm tắt hiển thị ba mức lợi nhuận. Trường hợp xấu nhất tạo ra $50,000, trường hợp cơ sở là $296,000 và trường hợp tốt nhất là $554,000.

Microsoft lưu ý một vài giới hạn. Mỗi kịch bản có thể chứa tối đa 32 giá trị thay đổi. Báo cáo tóm tắt không tự động: "Các báo cáo kịch bản không được tính toán lại tự động." Nếu bạn thay đổi một kịch bản, hãy tạo một bản tóm tắt mới.

Cách tìm điểm hòa vốn với Goal Seek

Phân tích độ nhạy cho thấy kết quả thay đổi bao xa. Goal Seek trả lời câu hỏi ngược lại: giá trị đầu vào nào mang lại kết quả mục tiêu?

Microsoft cho biết Goal Seek "lấy một kết quả và xác định các giá trị đầu vào khả thi để tạo ra kết quả đó." Ví dụ của nó tìm lãi suất cần thiết cho một khoản thanh toán khoản vay.

Dưới đây là cách tìm giá hòa vốn cho Northgate Cycles:

1. Trên tab Data, chọn What-If Analysis > Goal Seek.
2. Trong Set cell, nhập B5, ô lợi nhuận.
3. Trong To value, nhập 0.
4. Trong By changing cell, nhập B2, ô giá.
5. Nhấn OK.

Excel thay đổi giá cho đến khi lợi nhuận bằng không. Ở mức 2,400 đơn vị và chi phí cơ sở, giá hòa vốn là khoảng $726.67. Mức này thấp hơn khoảng 15% so với giá $850 hiện tại.

Goal Seek có một giới hạn. Microsoft cho biết nó "chỉ hoạt động với một giá trị đầu vào biến đổi." Đối với nhiều hơn một đầu vào, Microsoft hướng dẫn sử dụng tiện ích bổ sung Solver.

Cách xây dựng biểu đồ lốc xoáy

Biểu đồ lốc xoáy xếp hạng các đầu vào theo mức độ chúng làm thay đổi kết quả. Đây là cách rõ ràng nhất để trình bày phân tích độ nhạy.

Dưới đây là cách xây dựng một biểu đồ:

1. Chọn một phạm vi kiểm tra, chẳng hạn như mỗi đầu vào thấp hơn 10% và cao hơn 10%.
2. Thay đổi từng đầu vào một và ghi lại kết quả thấp và cao. Giữ các đầu vào khác ở mức cơ sở.
3. Đặt các kết quả vào một bảng với mỗi hàng cho một đầu vào.
4. Sắp xếp các hàng sao cho đầu vào có phạm vi rộng nhất nằm ở trên cùng.
5. Chèn biểu đồ thanh của các kết quả thấp và cao.

Đối với Northgate Cycles, với mỗi đầu vào thay đổi 10% lên và xuống:

Đầu vào	Lợi nhuận tại −10%	Lợi nhuận tại +10%	Phạm vi
Giá	$92,000	$500,000	$408,000
Chi phí biến đổi	$418,400	$173,600	$244,800
Số lượng bán ra	$214,400	$377,600	$163,200
Chi phí cố định	$348,000	$244,000	$104,000

Giá cả quan trọng nhất, với một khoảng cách lớn. Việc giảm 10% giá sẽ cắt giảm lợi nhuận từ $296,000 xuống còn $92,000. Chi phí cố định ít quan trọng nhất. Điều đó cho chủ sở hữu biết nên dành thời gian kiểm tra các giả định ở đâu.

Biểu đồ thanh trong Excel vẽ các thanh từ trái sang phải. Để có hình dạng lốc xoáy, hãy vẽ biểu đồ sự thay đổi của từng đầu vào so với lợi nhuận cơ sở thay vì các giá trị thô. Khi đó, các thanh trường hợp thấp sẽ hướng sang trái và các thanh trường hợp cao sẽ hướng sang phải.

Cách thực hiện phân tích độ nhạy với AI

Xây dựng các bảng dữ liệu, kịch bản và biểu đồ lốc xoáy cho một mô hình lớn tốn nhiều thời gian. Một công cụ AI có thể chạy các bài kiểm tra tương tự từ sổ làm việc của bạn.

Dưới đây là phương pháp gồm ba bước, sử dụng Powerdrill Bloom.

Bước 1: Tải lên mô hình của bạn

Bắt đầu với bảng tính chứa mô hình của bạn. AI Sensitivity Analysis Generator yêu cầu bạn tải lên "DCF, phương án kinh doanh, kế hoạch vận hành hoặc bất kỳ mô hình bảng tính nào."

Đảm bảo các đầu vào và ô kết quả được gắn nhãn rõ ràng. Công cụ cần biết con số nào là giả định.

Bước 2: Chọn chế độ xem độ nhạy bằng ngôn ngữ tự nhiên

Mô tả bài kiểm tra bạn muốn. Ví dụ: "Hiển thị biểu đồ lốc xoáy cho lợi nhuận với mỗi đầu vào thay đổi 10% lên và xuống. Thêm bảng hai chiều cho giá và số lượng bán ra."

Trang công cụ liệt kê bốn chế độ xem: "tornado, two-way, Monte Carlo hoặc break-even." Nó mô tả đầu ra lốc xoáy là một biểu đồ "xếp hạng các đầu vào làm thay đổi câu trả lời nhiều nhất." Nó cũng cung cấp một "Bảng độ nhạy trên hai giả định quan trọng."

Bước 3: Kiểm tra kết quả và xuất phân tích

So sánh một hoặc hai kết quả với bảng dữ liệu của riêng bạn trước khi chia sẻ chúng. Trường hợp cơ sở phải khớp chính xác với mô hình của bạn.

Sau đó xuất dữ liệu. Trang công cụ liệt kê "sổ làm việc Excel, PDF có thương hiệu hoặc liên kết canvas." Đối với một bài thuyết trình, công cụ Excel to PPT sẽ chuyển cùng một tệp đó thành các trang slide. Trang AI Financial Modeling hướng dẫn cách tự xây dựng mô hình.

Cách đọc và trình bày kết quả

Phân tích độ nhạy chỉ hữu ích nếu mọi người hành động dựa trên nó. Những thói quen này sẽ giúp ích.

Bắt đầu với bảng xếp hạng. Mở đầu bằng biểu đồ lốc xoáy. Nó cho thấy trong một bức tranh duy nhất những giả định nào thúc đẩy kết quả.

Chuyển đổi các phạm vi thành các quyết định. "Lợi nhuận giảm xuống $92,000 nếu giá giảm 10%" là một sự thật. "Chúng ta nên kiểm tra giá trước khi ký hợp đồng thuê" là một quyết định.

Hiển thị điểm hòa vốn. Kết hợp phân tích với giá trị mà kết quả bằng không. Đối với Northgate Cycles, điểm hòa vốn là khoảng 1,530 đơn vị ở mức giá và chi phí cơ sở. Hướng dẫn của chúng tôi về phân tích hòa vốn trong Excel đề cập đến cách tính toán đó.

Sử dụng các phạm vi thực tế. Mức thay đổi 10% phù hợp với các đầu vào ổn định. Khối lượng của một sản phẩm mới có thể cần một phạm vi rộng hơn nhiều.

Nêu rõ những gì được giữ cố định. Mỗi bài kiểm tra từng-cái-một giữ các đầu vào khác ở mức cơ sở. Hãy nói rõ điều đó trên slide hoặc trong phần chú thích. Khi đó, người đọc sẽ biết rằng thay đổi giá 10% giả định cùng khối lượng, chi phí và chi tiêu cố định. Dòng chữ này giúp ngăn chặn hầu hết các câu hỏi tiếp theo về phương pháp.

Xem lại sau khi có kết quả thực tế. Khi các con số thực tế xuất hiện, hãy so sánh chúng với các phạm vi của bạn. Một báo cáo ngân sách so với thực tế là một bước tiếp theo tự nhiên.

Các sai lầm thường gặp trong phân tích độ nhạy

Những sai lầm này rất phổ biến và dễ tránh.

Các con số được nhập cứng trong công thức. Nếu một công thức chứa 850 thay vì B2, bảng dữ liệu không thể thay đổi nó. Hãy giữ mọi đầu vào trong một ô.

Kiểm tra quá nhiều đầu vào cùng một lúc. Một bảng dữ liệu xử lý một hoặc hai cái. Hãy chuyển sang Scenario Manager hoặc biểu đồ lốc xoáy cho nhiều hơn.

Sử dụng cùng một phạm vi cho mọi đầu vào. Giá cả và khối lượng hiếm khi có cùng mức độ không chắc chắn. Hãy chọn các phạm vi phản ánh những gì bạn thực sự biết.

Bỏ qua các liên kết giữa các đầu vào. Giá thấp hơn có thể làm tăng khối lượng. Một bài kiểm tra từng-cái-một sẽ bỏ lỡ điều đó, vì vậy hãy thêm một kịch bản thay đổi cả hai.

Các bản tóm tắt kịch bản cũ kỹ. Các báo cáo kịch bản không tự cập nhật. Hãy xây dựng lại chúng sau bất kỳ thay đổi nào.

Quên trường hợp cơ sở. Mọi bảng nên hiển thị kết quả cơ sở, để người đọc có thể thấy quy mô của từng thay đổi.

Khi bạn đã sẵn sàng để kiểm tra áp lực cho mô hình tiếp theo của mình, hãy thử Powerdrill Bloom với sổ làm việc bạn sử dụng để lập kế hoạch.

Các câu hỏi thường gặp

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

Đó là một cách để kiểm tra xem kết quả thay đổi như thế nào khi các đầu vào của nó thay đổi. Trong Excel, bạn sử dụng các công cụ What-If Analysis, chủ yếu là bảng dữ liệu và Scenario Manager, để thay thế các giá trị đầu vào mới và ghi lại kết quả.

Làm cách nào để thực hiện phân tích độ nhạy một biến trong Excel?

Liệt kê các giá trị đầu vào trong một cột. Đặt một công thức tham chiếu đến kết quả ở phía trên một hàng và bên phải một cột. Chọn phạm vi, chọn What-If Analysis > Data Table, và nhập ô đầu vào làm ô nhập cột (column input cell).

Sự khác biệt giữa bảng dữ liệu và Scenario Manager là gì?

Bảng dữ liệu kiểm tra một hoặc hai biến trên nhiều giá trị và hiển thị tất cả kết quả trong một lưới. Scenario Manager lưu trữ các bộ đầu vào đầy đủ, mỗi bộ tối đa 32 giá trị thay đổi, và thay thế chúng từng cái một.

Làm cách nào để tạo biểu đồ lốc xoáy trong Excel?

Thay đổi từng đầu vào lên và xuống cùng một lượng, từng cái một, và ghi lại kết quả. Sắp xếp các đầu vào theo kích thước của phạm vi, sau đó vẽ biểu đồ sự thay đổi của từng đầu vào so với kết quả cơ sở dưới dạng các thanh ngang.

AI có thể thực hiện phân tích độ nhạy không?

Có. Một công cụ AI có thể đọc mô hình bảng tính của bạn, chạy các bài kiểm tra lốc xoáy và hai chiều, đồng thời xuất kết quả dưới dạng sổ làm việc hoặc báo cáo. Bạn vẫn cần kiểm tra trường hợp cơ sở và chọn các phạm vi thực tế.

Nguồn: Microsoft Support, Giới thiệu về What-If Analysis · Microsoft Support, Tính toán nhiều kết quả bằng cách sử dụng bảng dữ liệu · Microsoft Support, Chuyển đổi giữa các bộ giá trị khác nhau bằng cách sử dụng kịch bản.