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

Để thực hiện phân tích biến động trong Excel, hãy đặt các con số ngân sách (budget) và thực tế (actual) cạnh nhau cho từng hạng mục. Lấy thực tế trừ đi ngân sách để có biến động bằng số tiền tuyệt đối. Chia biến động đó cho ngân sách để có tỷ lệ phần trăm. Sau đó, đánh dấu mỗi dòng là thuận lợi hoặc không thuận lợi và giải thích những khoảng cách lớn nhất.
Hướng dẫn này sẽ đi qua các công thức, cách bố trí bảng tính và một ví dụ thực tế đầy đủ. Nó cũng bao gồm cách lập biểu đồ kết quả và cách chia tách khoảng cách doanh thu thành giá và khối lượng. Các bước thực hiện trong Excel được tham khảo từ các trang hỗ trợ của Microsoft đọc vào ngày 10 tháng 11 năm 2026.
Phân tích biến động cho thấy điều gì
Biến động là khoảng cách giữa những gì bạn đã lập kế hoạch và những gì đã xảy ra. Một bản phân tích biến động liệt kê những khoảng cách đó theo từng dòng và giải thích những khoảng cách quan trọng.
Hầu hết các nhóm so sánh số thực tế với một trong ba mốc cơ sở:
- Ngân sách (Budget). Kế hoạch được thiết lập vào đầu năm.
- Dự báo (Forecast). Kế hoạch cập nhật được thiết lập trong năm.
- Kỳ trước (Prior period). Tháng trước, quý trước hoặc cùng kỳ năm ngoái.
Kỹ năng Copilot của Microsoft cho nhiệm vụ này mô tả công việc rất tốt. Kỹ năng phân tích biến động của nó "So sánh kết quả thực tế với ngân sách, dự báo hoặc các kỳ trước và giải thích sự khác biệt theo giá, khối lượng, cơ cấu, tỷ lệ hoặc thời điểm."
Kết quả thường là một bảng cộng với một vài ghi chú bằng văn bản. Bảng hiển thị mọi khoảng cách. Các ghi chú giải thích ba hoặc bốn điểm mà các nhà lãnh đạo cần phải hành động.
Các công thức cốt lõi
Ba công thức thực hiện hầu hết các công việc. Giả sử ngân sách ở cột B và thực tế ở cột C.
| Chỉ số | Công thức | Ý nghĩa |
|---|---|---|
| Biến động số tiền | =C2-B2 | Thực tế cách ngân sách bao xa, theo đơn vị tiền tệ |
| Biến động phần trăm | =IFERROR((C2-B2)/ABS(B2),"") | Khoảng cách lớn như thế nào so với kế hoạch |
| Trạng thái | =IF(D2=0,"Đúng kế hoạch",IF(D2>0,"Trên kế hoạch","Dưới kế hoạch")) | Dòng dữ liệu di chuyển theo hướng nào |
Hàm ABS giữ cho dấu chính xác khi ngân sách là số âm. IFERROR để trống ô khi ngân sách bằng không, thay vì hiển thị lỗi.
Hướng di chuyển không đồng nghĩa với tốt hay xấu. Doanh thu trên kế hoạch là tin tốt. Chi phí trên kế hoạch là tin xấu. Các phần tiếp theo sẽ hướng dẫn cách xử lý việc đó.
Cách thiết lập bảng tính
Một bố cục sạch sẽ giúp việc sao chép công thức trở nên dễ dàng. Sử dụng một hàng cho mỗi hạng mục và các cột sau:
- Hạng mục (Line item). Doanh thu, giá vốn hàng bán, lương, marketing, v.v.
- Loại (Type). Nhập "Doanh thu" hoặc "Chi phí" để Excel biết hướng nào là tốt.
- Ngân sách (Budget). Số tiền kế hoạch cho kỳ đó.
- Thực tế (Actual). Số tiền từ dữ liệu kế toán của bạn.
- Biến động (Variance). Thực tế trừ ngân sách.
- % Biến động (Variance %). Biến động chia cho ngân sách.
- Thuận lợi hoặc không thuận lợi (Favorable or unfavorable). Một nhãn dựa trên loại và hướng biến động.
- Giải thích (Explanation). Một ghi chú ngắn cho các dòng quan trọng.
Giữ ngân sách và thực tế cho cùng một kỳ và cùng một cách phân loại tài khoản. Phân tích biến động chỉ có giá trị khi sự khớp nối này chuẩn xác. Nếu lương được chia thành hai tài khoản trong ngân sách, hãy chia tương tự trong thực tế.
Định dạng bảng dưới dạng bảng Excel bằng Ctrl+T. Các hàng mới sau đó sẽ tự động nhận công thức.
Cách tính toán biến động trong Excel
Khi bố cục đã sẵn sàng, các bước thực hiện rất ngắn gọn. Giả sử các cột trên bắt đầu từ hàng 2, với Loại ở B, Ngân sách ở C và Thực tế ở D.
- Tại E2, nhập =D2-C2 cho biến động số tiền.
- Tại F2, nhập =IFERROR((D2-C2)/ABS(C2),"") và định dạng dưới dạng phần trăm.
- Trong cột Trạng thái, nhập kiểm tra tính thuận lợi như dưới đây.
- Sao chép công thức xuống mọi hàng.
- Thêm một hàng tổng cộng, và tính biến động của nó từ các tổng số, không phải bằng cách cộng các phần trăm.
Đây là công thức kiểm tra tính thuận lợi cho hàng 2 của cột Trạng thái:
=IF(E2=0,"Đúng kế hoạch",IF(B2="Doanh thu",IF(E2>0,"Thuận lợi","Không thuận lợi"),IF(E2<0,"Thuận lợi","Không thuận lợi")))
Nó đọc cột Loại trước. Đối với doanh thu, biến động dương là thuận lợi. Đối với chi phí, biến động âm là thuận lợi vì bạn đã chi tiêu ít hơn kế hoạch.
Phần trăm cần được lưu ý ở hàng tổng cộng. Cộng phần trăm của từng dòng sẽ cho kết quả sai. Thay vào đó, hãy chia tổng biến động cho tổng ngân sách.
Cách đánh dấu các biến động quan trọng
Không phải mọi khoảng cách đều cần ghi chú. Hầu hết các nhóm đều đặt ra quy tắc về tính trọng yếu, chẳng hạn như "trên 5% và trên $2,000." Những dòng vượt qua cả hai bài kiểm tra này sẽ được giải thích.
Một công thức đơn giản sẽ đánh dấu chúng. Thêm một cột Trọng yếu và nhập =AND(ABS(F2)>0.05,ABS(E2)>2000) ở hàng 2. Ô sẽ hiển thị TRUE cho các dòng quan trọng.
Màu sắc giúp bảng dễ quét nhanh hơn. Định dạng có điều kiện của Excel có thể thêm các bộ biểu tượng. Microsoft cho biết một bộ biểu tượng có thể "chú thích và phân loại dữ liệu thành ba đến năm danh mục được phân tách bằng một giá trị ngưỡng."
Để thêm một bộ, hãy chọn cột % Biến động. Sau đó vào Home > Conditional Formatting > Icon Sets. Microsoft lưu ý rằng trong bộ 3 Mũi tên, "mũi tên lên màu xanh lá cây đại diện cho các giá trị cao hơn." Đối với các dòng chi phí, hãy đảo ngược thứ tự biểu tượng để sự gia tăng hiển thị như một cảnh báo.
Cách giải thích các yếu tố thúc đẩy
Các con số cho thấy khoảng cách nằm ở đâu. Các yếu tố thúc đẩy giải thích tại sao. Mô tả kỹ năng của Microsoft liệt kê năm yếu tố thúc đẩy phổ biến: giá, khối lượng, cơ cấu, tỷ lệ và thời điểm.
Giá (Price). Bạn đã bán hoặc mua với mức giá khác với kế hoạch. Biến động giá = (giá thực tế − giá ngân sách) × số lượng thực tế.
Khối lượng (Volume). Bạn đã bán hoặc sử dụng một số lượng khác. Biến động khối lượng = (số lượng thực tế − số lượng ngân sách) × giá ngân sách.
Cơ cấu (Mix). Tỷ trọng của các sản phẩm, khu vực hoặc khách hàng đã thay đổi. Sự chuyển dịch sang các mặt hàng có biên lợi nhuận thấp hơn làm giảm lợi nhuận ngay cả khi khối lượng giữ nguyên.
Tỷ lệ (Rate). Một tỷ lệ chi phí thay đổi, chẳng hạn như mức lương theo giờ hoặc phí nhà cung cấp.
Thời điểm (Timing). Tiền di chuyển giữa các kỳ. Một hóa đơn tháng 3 được thanh toán vào tháng 4 sẽ hiển thị dưới dạng một khoảng cách trong cả hai tháng.
Giá và khối lượng cùng nhau giải thích toàn bộ khoảng cách doanh thu. Việc kiểm tra này đáng để thực hiện mọi lúc, vì nó xác nhận tính chính xác của toán học.
Ví dụ về phân tích biến động
Dưới đây là một ví dụ thực tế cho một công ty giả định, Cedar and Pine Coffee. Trưởng bộ phận tài chính của công ty so sánh số thực tế quý ba với ngân sách.
| Hạng mục | Loại | Ngân sách | Thực tế | Biến động | % Biến động | Trạng thái |
|---|---|---|---|---|---|---|
| Doanh thu | Doanh thu | $250,000 | $236,500 | −$13,500 | −5.4% | Không thuận lợi |
| Giá vốn hàng bán | Chi phí | $100,000 | $97,200 | −$2,800 | −2.8% | Thuận lợi |
| Lương | Chi phí | $60,000 | $64,800 | $4,800 | 8.0% | Không thuận lợi |
| Marketing | Chi phí | $20,000 | $26,000 | $6,000 | 30.0% | Không thuận lợi |
| Thuê mặt bằng | Chi phí | $15,000 | $15,000 | $0 | 0.0% | Đúng kế hoạch |
| Lợi nhuận hoạt động | $55,000 | $33,500 | −$21,500 | −39.1% | Không thuận lợi |
Tổng số đã khớp. Doanh thu giảm $13,500, giá vốn hàng bán tiết kiệm được $2,800, chi phí lương tăng thêm $4,800 và chi phí marketing tăng thêm $6,000. Tổng cộng là −$21,500, khớp với khoảng cách lợi nhuận.
Ba dòng vượt qua quy tắc trọng yếu trên 5% và trên $2,000: doanh thu, lương và marketing. Giá vốn hàng bán chỉ biến động 2.8%, nên không cần ghi chú.
Khoảng cách doanh thu cần được xem xét kỹ hơn. Ngân sách giả định 50,000 đơn vị với giá $5.00. Quý này đã bán được 43,000 đơn vị với giá $5.50.
- Biến động khối lượng. (43,000 − 50,000) × $5.00 = −$35,000.
- Biến động giá. ($5.50 − $5.00) × 43,000 = +$21,500.
- Tổng cộng. −$35,000 + $21,500 = −$13,500, khớp với dòng doanh thu.
Vì vậy, việc tăng giá đã được duy trì, nhưng nó làm giảm khối lượng. Ghi chú gửi cho ban lãnh đạo nói chính xác điều đó. Marketing đã chi nhiều hơn 30% so với kế hoạch, nhưng số lượng đơn vị bán ra lại giảm. Điều đó đặt ra câu hỏi về việc chiến dịch nào thực sự hiệu quả.
Lương cần được kiểm tra riêng. Khoảng cách $4,800 có thể là do yếu tố tỷ lệ, chẳng hạn như tăng lương, hoặc yếu tố khối lượng, chẳng hạn như làm thêm giờ. Trưởng bộ phận tài chính hỏi quản lý vận hành và biết rằng hai cửa hàng đã thêm ca làm việc cuối tuần. Điều đó làm cho nó trở thành một khoảng cách về khối lượng gắn liền với giờ mở cửa, không phải là tăng lương.
Bản tóm tắt cuối cùng cho ban lãnh đạo gói gọn trong một dòng. Lợi nhuận thấp hơn ngân sách $21,500, do việc tăng giá làm giảm khối lượng nhiều hơn mức bù đắp được, và chi phí marketing cùng các ca làm việc cuối tuần đã vượt kế hoạch.
Cách viết các ghi chú biến động
Bảng số liệu chỉ là một nửa của phân tích biến động. Các nhà lãnh đạo đọc các ghi chú, vì vậy mỗi ghi chú nên trả lời ba câu hỏi: cái gì đã thay đổi, tại sao nó thay đổi và điều gì sẽ xảy ra tiếp theo.
Một ghi chú tốt tuân theo một mô hình đơn giản:
- Cái gì. "Marketing đã chi vượt ngân sách $6,000, cao hơn 30.0% so với kế hoạch."
- Tại sao. "Hai chiến dịch trả phí đã chạy lâu hơn dự kiến."
- Tiếp theo. "Chúng tôi đã tạm dừng một chiến dịch vào ngày 3 tháng 10 và dự kiến Quý 4 sẽ đạt đúng kế hoạch."
Giữ mỗi ghi chú trong khoảng hai hoặc ba câu. Sử dụng cùng thứ tự các dòng như trong bảng, để người đọc có thể khớp các ghi chú với các con số.
Tách biệt các khoảng cách phát sinh một lần với các xu hướng. Một hóa đơn sửa chữa một lần khác với việc tăng lương sẽ lặp lại hàng tháng. Đánh dấu rõ ràng các khoảng cách lặp lại, vì chúng làm thay đổi dự báo.
Viết các ghi chú cùng với người chịu trách nhiệm của mỗi dòng. Nhóm tài chính nhìn thấy các con số, nhưng chủ sở hữu ngân sách mới biết lý do. Một cuộc trao đổi ngắn với mỗi chủ sở hữu sẽ biến một dự đoán thành một lời giải thích.
Cuối cùng, thêm một dòng tóm tắt ở trên cùng. Đối với ví dụ trên, nó sẽ là: "Lợi nhuận hoạt động Quý 3 thấp hơn ngân sách $21,500, chủ yếu do khối lượng thấp hơn và chi tiêu marketing cao hơn."
Thời điểm cũng quan trọng như cách diễn đạt. Hầu hết các nhóm thực hiện phân tích biến động hàng tháng, sau khi chốt sổ sách. Nhịp độ hàng tháng giúp phát hiện các vấn đề khi vẫn còn thời gian để hành động.
Các đánh giá hàng quý sẽ sâu hơn. Chúng thêm phần chia tách giá và khối lượng, dự báo lại và đánh giá xem liệu bản thân ngân sách có còn hợp lý hay không. Các đánh giá cuối năm nhìn lại cả năm so với kế hoạch ban đầu.
Giữ nguyên một mẫu mỗi lần. Khi các cột và quy tắc được giữ cố định, việc phân tích biến động sẽ tốn ít thời gian hơn mỗi tháng và các xu hướng sẽ dễ dàng được nhìn thấy qua các kỳ.
Cách lập biểu đồ biến động
Một biểu đồ giúp các nhà lãnh đạo nhìn thấy lộ trình từ ngân sách đến thực tế. Phù hợp nhất là biểu đồ thác nước (waterfall chart).
Microsoft cho biết biểu đồ thác nước "hiển thị tổng lũy kế khi các giá trị được cộng vào hoặc trừ đi." Nó nói thêm rằng "biểu đồ thác nước còn được gọi là biểu đồ cầu (bridge charts)."
Để xây dựng một cái, hãy liệt kê các bước từ lợi nhuận ngân sách đến lợi nhuận thực tế. Bắt đầu với $55,000, sau đó thêm hiệu ứng của từng dòng đối với lợi nhuận và kết thúc bằng $33,500. Chọn dữ liệu và chọn Insert > Insert Waterfall > Waterfall.
Đặt các thanh đầu tiên và cuối cùng làm tổng số. Trang của Microsoft lưu ý rằng các cột bắt đầu và kết thúc "thường bắt đầu trên trục hoành," trong khi các thanh ở giữa lơ lửng. Các mức tăng và giảm được mã hóa bằng màu sắc, để các yếu tố thúc đẩy lớn nhất nổi bật lên.
Để theo dõi từng tháng, một biểu đồ cột đơn giản về % biến động cũng có hiệu quả. Hướng dẫn của chúng tôi về cách xây dựng báo cáo ngân sách so với thực tế bao gồm báo cáo hàng tháng đầy đủ xoay quanh bảng này.
Thực hiện phân tích biến động với AI
Các công thức chỉ mất vài phút khi bảng đã tồn tại. Phần chậm chạp thường là đối soát tài khoản, tìm kiếm các yếu tố thúc đẩy và viết ghi chú. Một công cụ AI có thể soạn thảo các bước đó từ dữ liệu xuất ra 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 ngân sách và thực tế của bạn
Xuất ngân sách và thực tế từ công cụ kế toán hoặc lập kế hoạch của bạn. AI Budget Analysis Tool chấp nhận "các tệp xuất Excel, CSV hoặc PDF từ kế toán và FP&A."
Bao gồm dữ liệu đơn vị và giá nếu bạn muốn chia tách giá và khối lượng. Giữ tên tài khoản giống nhau trong cả hai tệp.
Bước 2: Yêu cầu các biến động và yếu tố thúc đẩy bằng ngôn ngữ tự nhiên
Mô tả kết quả bạn cần. Ví dụ: "So sánh thực tế Quý 3 với ngân sách theo từng hạng mục. Đánh dấu các dòng trên 5% và $2,000, và chia tách khoảng cách doanh thu thành giá và khối lượng."
Trang công cụ liệt kê "Biến động theo từng hạng mục với các ngưỡng trọng yếu được áp dụng" là một chế độ xem. Nó cũng liệt kê một bảng tính dự báo lại với "ngân sách gốc, thực tế lũy kế từ đầu năm và cuối năm đã điều chỉnh."
Bước 3: Kiểm tra các con số và xuất báo cáo
So sánh các tổng số với bảng tính của riêng bạn trước khi chia sẻ bất cứ điều gì. Sau đó xuất kết quả. Trang Phân tích Ngân sách liệt kê một "Bảng điều khiển biến động với các dòng quan trọng được làm nổi bật và các yếu tố thúc đẩy được đính kèm."
Để có cái nhìn liên tục, AI Finance Dashboard Builder bao gồm P&L, tiền mặt, vốn lưu động và dự báo. Hãy thử Powerdrill Bloom với ngân sách và thực tế của quý trước.
Các lỗi thường gặp cần tránh
Một vài lỗi xuất hiện trong hầu hết mọi bản phân tích biến động đầu tiên.
- Nhầm lẫn về dấu. Quyết định một lần xem biến động là thực tế trừ ngân sách hay ngược lại, và sử dụng nó ở mọi nơi.
- Gọi mọi khoảng cách dương là tốt. Chi phí cao hơn là không thuận lợi ngay cả khi con số đó là dương.
- Cộng các phần trăm. Tính toán lại phần trăm tổng cộng từ tổng số tiền.
- So sánh các kỳ khác nhau. Thực tế lũy kế từ đầu năm so với ngân sách hàng tháng sẽ tạo ra một khoảng cách sai lệch.
- Bỏ qua thời điểm. Một hóa đơn trễ có thể tạo ra hai khoảng cách đối lập trong hai tháng.
- Giải thích mọi thứ. Các ghi chú về những khoảng cách nhỏ sẽ che lấp những khoảng cách thực sự quan trọng.
- Nhập số bằng tay. Dán hoặc liên kết số thực tế từ tệp xuất, để lỗi đánh máy không tạo ra khoảng cách giả.
- Bỏ qua việc kiểm tra tổng số. Biến động của các dòng phải cộng lại bằng tổng biến động. Nếu không, có nghĩa là một hàng đang bị thiếu hoặc bị tính hai lần.
Các câu hỏi thường gặp
Công thức tính biến động trong Excel là gì?
Đối với biến động ngân sách, hãy sử dụng thực tế trừ ngân sách, chẳng hạn như =C2-B2. Đối với phần trăm, hãy sử dụng =IFERROR((C2-B2)/ABS(B2),"") và định dạng ô dưới dạng phần trăm.
Làm thế nào để biết một biến động là thuận lợi?
Nó phụ thuộc vào loại dòng. Đối với doanh thu, thực tế trên ngân sách là thuận lợi. Đối với chi phí, thực tế dưới ngân sách là thuận lợi. Một cột Loại và một công thức IF có thể dán nhãn cho từng hàng.
Biến động trọng yếu là gì?
Đó là một khoảng cách đủ lớn để cần giải thích. Các nhóm tự đặt ra quy tắc riêng, chẳng hạn như trên 5% và trên $2,000. Những dòng vượt qua cả hai bài kiểm tra này sẽ có một ghi chú bằng văn bản.
Biểu đồ nào là tốt nhất cho phân tích biến động?
Biểu đồ thác nước, còn được gọi là biểu đồ cầu, cho thấy cách mỗi dòng làm thay đổi tổng số từ ngân sách sang thực tế. Excel có tính năng này trong Insert > Insert Waterfall.
Copilot trong Excel có thể thực hiện phân tích biến động không?
Microsoft liệt kê một kỹ năng phân tích biến động cho Copilot trong Excel. Nó so sánh thực tế với ngân sách, dự báo hoặc các kỳ trước và soạn thảo các giải thích để xem xét. Nó yêu cầu giấy phép Copilot.
Nguồn: Hỗ trợ Microsoft, Sử dụng các kỹ năng tích hợp với Copilot trong Excel · Hỗ trợ Microsoft, Tạo biểu đồ thác nước · Hỗ trợ Microsoft, Sử dụng định dạng có điều kiện để làm nổi bật thông tin trong Excel.