Dùng AI Phân Tích Biến Động Lãi Gộp: Tách Giá – Lượng – Cơ Cấu Bằng Excel, Python Và Prompt Mẫu

Doanh thu tăng 18% nhưng lãi gộp chỉ tăng 6%: chuyện gì đã xảy ra?

Cuối quý, Giám đốc nhìn báo cáo và hỏi một câu quen thuộc: "Doanh thu tăng mạnh vậy, sao lãi gộp gần như đứng yên? Biên lãi gộp còn giảm từ 31,5% xuống 28,4%?"

Nhiều kế toán trả lời theo cảm tính: "Do giá vốn tăng", "Do bán nhiều hàng rẻ". Câu trả lời có thể đúng một phần, nhưng Ban Giám đốc cần con số cụ thể: giá vốn tăng làm mất bao nhiêu tiền? Bán lệch sang hàng biên thấp làm mất bao nhiêu? Tăng giá bán bù lại được bao nhiêu?

Công cụ để trả lời câu hỏi đó là phân tích biến động lãi gộp theo giá – lượng – cơ cấu (tiếng Anh gọi là Price–Volume–Mix analysis hay gross margin bridge). Bài này hướng dẫn bạn làm từ đầu đến cuối bằng Excel, Python và AI, kèm prompt mẫu dùng được ngay.

Phân tích giá – lượng – cơ cấu là gì?

Lãi gộp thay đổi giữa hai kỳ luôn do 4 nhóm nguyên nhân. Ta tách tổng chênh lệch thành 4 "viên gạch" cộng lại đúng bằng tổng:

Công thức (ký hiệu: kỳ gốc = 0, kỳ phân tích = 1; m₀ᵢ = lãi gộp đơn vị kỳ gốc của mặt hàng i):

• Ảnh hưởng sản lượng = (Tổng SL₁ − Tổng SL₀) × Lãi gộp bình quân/đơn vị kỳ gốc
• Ảnh hưởng cơ cấu = Σ (SL₁ᵢ − Tổng SL₁ × Tỷ trọng₀ᵢ) × m₀ᵢ
• Ảnh hưởng giá bán = Σ SL₁ᵢ × (Giá₁ᵢ − Giá₀ᵢ)
• Ảnh hưởng giá vốn = − Σ SL₁ᵢ × (Giá vốn₁ᵢ − Giá vốn₀ᵢ)

Kiểm tra: Sản lượng + Cơ cấu + Giá bán + Giá vốn = Lãi gộp₁ − Lãi gộp₀. Nếu không khớp, bảng tính đang sai.

Lưu ý: có nhiều biến thể (tính giá theo sản lượng kỳ gốc, tách thêm "chênh lệch kết hợp"...). Điều quan trọng là chọn một phương pháp, ghi rõ trong báo cáo và dùng nhất quán giữa các kỳ.

Ví dụ minh họa: 3 nhóm sản phẩm, quý 2 so với quý 3

Số liệu dưới đây là giả định minh họa, không phải số liệu của doanh nghiệp thực tế. Đơn vị giá: nghìn đồng/thùng; đơn vị tiền tổng: triệu đồng.

Kết quả tổng:

Tách 130 triệu tăng thêm thành 4 yếu tố:

1. Sản lượng: lãi gộp bình quân Q2 = 2.050 triệu / 20.000 thùng = 102,5 nghìn/thùng. Bán thêm 4.000 thùng → 4.000 × 102,5 = +410 triệu.
2. Cơ cấu: cơ cấu Q2 là A 50% – B 25% – C 25%. Nếu Q3 giữ cơ cấu cũ thì sẽ bán A 12.000, B 6.000, C 6.000 thùng. Thực tế:
   • A thiếu 3.000 thùng × 90 = −270 triệu
   • B thừa 500 thùng × 200 = +100 triệu
   • C thừa 2.500 thùng × 30 = +75 triệu
   • Cộng: −95 triệu
3. Giá bán: A tăng 10 × 9.000 = +90 triệu; B giảm 10 × 6.500 = −65 triệu; C không đổi → +25 triệu.
4. Giá vốn: A tăng 15 × 9.000 = −135 triệu; B tăng 5 × 6.500 = −32,5 triệu; C tăng 5 × 8.500 = −42,5 triệu → −210 triệu.

Kiểm tra: 410 − 95 + 25 − 210 = 130 triệu ✔

Đọc kết quả: doanh nghiệp bán được nhiều hàng hơn (+410 triệu), nhưng tăng trưởng chủ yếu đến từ hàng C biên lãi thấp, còn hàng chủ lực A lại giảm sản lượng (−95 triệu do cơ cấu). Giá vốn tăng ở cả 3 nhóm "ăn" mất 210 triệu, trong khi tăng giá bán chỉ bù được 25 triệu. Đây là câu trả lời có số liệu cho câu hỏi của Giám đốc.

Bước 1 – Chuẩn bị dữ liệu từ phần mềm kế toán

Bạn cần 2 báo cáo cho 2 kỳ cần so sánh:

• Sổ chi tiết bán hàng (MISA, Fast, Bravo... đều có): mã hàng, số lượng, doanh thu thuần (sau chiết khấu, giảm giá, hàng bán trả lại).
• Báo cáo giá vốn theo mặt hàng hoặc sổ chi tiết TK 632 theo mã hàng.

Mẹo làm sạch trước khi phân tích:

• Gộp theo nhóm hàng nếu có hàng trăm mã — phân tích 5–15 nhóm dễ đọc hơn 500 mã.
• Dùng doanh thu thuần, không dùng doanh thu trước chiết khấu, nếu không ảnh hưởng giá bán sẽ bị sai.
• Mặt hàng mới (chỉ có ở kỳ 1) hoặc ngừng bán (chỉ có ở kỳ 0) nên tách riêng một dòng "hàng mới/ngừng bán" để tránh méo yếu tố giá.
• Thống nhất đơn vị tính (thùng/hộp/kg) giữa hai kỳ.

Bước 2 – Lập bảng tính trên Excel

Giả sử dữ liệu nằm ở các cột: B = SL₀, C = Giá₀, D = Giá vốn₀, E = SL₁, F = Giá₁, G = Giá vốn₁, dữ liệu từ dòng 2 đến dòng 4, dòng tổng là dòng 5.

Ảnh hưởng sản lượng (một ô riêng):

=($E$5-$B$5) * SUMPRODUCT(B2:B4,H2:H4) / $B$5

Ô kiểm tra tổng (phải bằng 0):

=SUMPRODUCT(E2:E4,F2:F4-G2:G4) - SUMPRODUCT(B2:B4,H2:H4) - (Ô_sản_lượng + SUM(J2:L4))

Sau đó vẽ biểu đồ Waterfall (Insert → Chart → Waterfall, có từ Excel 2016): Lãi gộp Q2 → Sản lượng → Cơ cấu → Giá bán → Giá vốn → Lãi gộp Q3. Một biểu đồ này thường thuyết phục Ban Giám đốc hơn cả trang thuyết minh.

Bước 3 – Tự động hóa bằng Python (khi có nhiều nhóm hàng, nhiều kỳ)

Nếu mỗi tháng bạn phải làm lại, đoạn Python dưới đây đọc file Excel và xuất bảng phân tích trong vài giây:

import pandas as pd

# File có các cột: nhom_hang, sl0, gia0, gv0, sl1, gia1, gv1
df = pd.read_excel("du_lieu_lai_gop.xlsx")

df["m0"] = df["gia0"] - df["gv0"]
tong_sl0, tong_sl1 = df["sl0"].sum(), df["sl1"].sum()
lg0 = (df["sl0"] * df["m0"]).sum()
lg1 = (df["sl1"] * (df["gia1"] - df["gv1"])).sum()

df["anh_huong_co_cau"] = (df["sl1"] - tong_sl1 * df["sl0"] / tong_sl0) * df["m0"]
df["anh_huong_gia_ban"] = df["sl1"] * (df["gia1"] - df["gia0"])
df["anh_huong_gia_von"] = -df["sl1"] * (df["gv1"] - df["gv0"])
anh_huong_san_luong = (tong_sl1 - tong_sl0) * lg0 / tong_sl0

tong_hop = pd.Series({
    "Lãi gộp kỳ gốc": lg0,
    "Sản lượng": anh_huong_san_luong,
    "Cơ cấu": df["anh_huong_co_cau"].sum(),
    "Giá bán": df["anh_huong_gia_ban"].sum(),
    "Giá vốn": df["anh_huong_gia_von"].sum(),
    "Lãi gộp kỳ phân tích": lg1,
})

# Kiểm tra khớp tổng
chenh = lg1 - lg0 - tong_hop.iloc[1:5].sum()
assert abs(chenh) < 1, f"Không khớp tổng: {chenh}"

with pd.ExcelWriter("phan_tich_lai_gop.xlsx") as w:
    df.to_excel(w, sheet_name="Chi tiết", index=False)
    tong_hop.to_frame("Giá trị").to_excel(w, sheet_name="Tổng hợp")
print(tong_hop)

Chạy với số liệu minh họa ở trên, kết quả đúng như phần tính tay: +410 / −95 / +25 / −210, tổng +130 triệu.

Bước 4 – Dùng AI để kiểm tra và viết thuyết minh

AI (ChatGPT, Claude, Gemini...) rất hữu ích ở hai việc: soát công thức và viết thuyết minh cho Ban Giám đốc. Đừng để AI tự tính từ đầu với dữ liệu lớn — hãy tính bằng Excel/Python, rồi đưa kết quả cho AI diễn giải.

Prompt 1 – Soát phương pháp:

Bạn là kế toán quản trị. Tôi phân tích biến động lãi gộp giữa Q2 và Q3
theo 4 yếu tố: sản lượng, cơ cấu, giá bán, giá vốn. Công thức tôi dùng:
[dán công thức]. Bảng kết quả: [dán bảng].
Hãy: (1) kiểm tra 4 yếu tố có cộng lại đúng bằng chênh lệch lãi gộp không;
(2) chỉ ra điểm có thể sai về phương pháp (hàng mới, hàng ngừng bán,
chiết khấu); (3) không tự bịa thêm số liệu ngoài bảng tôi đưa.

Prompt 2 – Viết thuyết minh cho Ban Giám đốc:

Dựa trên bảng phân tích biến động lãi gộp dưới đây, viết đoạn thuyết minh
tối đa 200 chữ cho Ban Giám đốc, giọng văn ngắn gọn, đi thẳng vào nguyên nhân.
Cấu trúc: (1) kết luận chính trong 1 câu; (2) 3 nguyên nhân lớn nhất theo
giá trị tuyệt đối; (3) 2-3 đề xuất hành động cụ thể (giá bán, cơ cấu,
mua hàng). Chỉ dùng số liệu trong bảng.
[dán bảng tổng hợp và bảng chi tiết theo nhóm hàng]

Prompt 3 – Tìm mặt hàng cần hành động:

Từ bảng chi tiết theo nhóm hàng, liệt kê các nhóm có ảnh hưởng giá vốn âm
lớn nhất mà giá bán không tăng tương ứng. Với mỗi nhóm, tính mức tăng giá
bán/đơn vị cần thiết để giữ nguyên biên lãi gộp kỳ gốc. Trình bày dạng bảng.

Với ví dụ trên, AI có thể gợi ý: hàng A cần tăng giá thêm hoặc đàm phán lại giá nhập vì giá vốn tăng 15 nghìn/thùng mà giá bán chỉ tăng 10; xem lại chính sách đẩy hàng C (sản lượng tăng 70% nhưng biên chỉ 25 nghìn/thùng); tìm nguyên nhân hàng A giảm sản lượng. Những gợi ý này là điểm khởi đầu để thảo luận, không phải kết luận cuối cùng.

Lợi ích và những lưu ý quan trọng

Lợi ích:

• Trả lời câu hỏi "vì sao lãi gộp thay đổi" bằng con số, không bằng cảm tính.
• Chỉ ra đúng bộ phận cần hành động: kinh doanh (giá, cơ cấu), mua hàng/sản xuất (giá vốn).
• Khi đã có file mẫu + Python, mỗi kỳ chỉ mất vài phút cập nhật.

Lưu ý:

• AI không thay thế phán đoán chuyên môn. AI không biết hàng A giảm sản lượng là do đứt hàng, đối thủ giảm giá hay chủ động cắt khách nợ xấu — kế toán và bộ phận kinh doanh phải xác minh.
• Số liệu gốc phải đúng. Giá vốn chưa phân bổ chi phí sản xuất chung, chưa ghi nhận hàng bán trả lại... sẽ làm sai cả bảng phân tích.
• Bảo mật dữ liệu. Không đưa nguyên sổ chi tiết có tên khách hàng, mã số thuế, đơn giá hợp đồng lên công cụ AI công cộng. Hãy mã hóa tên hàng/khách hàng (A, B, C...), chỉ gửi bảng tổng hợp, và ưu tiên tài khoản AI doanh nghiệp có cam kết không dùng dữ liệu để huấn luyện.
• Nhất quán phương pháp giữa các kỳ để số liệu so sánh được.

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

1. Nên so sánh với kỳ trước hay với kế hoạch? Cả hai đều được. So với kỳ trước để thấy xu hướng; so với kế hoạch/ngân sách để đánh giá thực hiện. Công thức giữ nguyên, chỉ thay "kỳ gốc" bằng số kế hoạch.

2. Doanh nghiệp dịch vụ không có "sản lượng" thì làm sao? Dùng đơn vị đo phù hợp: số giờ công, số hợp đồng, số khách hàng, số phòng-đêm... miễn là đo được cho cả doanh thu và giá vốn.

3. Có cần phần mềm BI không? Không bắt buộc. Excel + biểu đồ Waterfall đủ cho đa số SME. Khi dữ liệu nhiều kỳ, nhiều chi nhánh, có thể đưa file kết quả Python vào Power BI hoặc Looker Studio.

4. Tách theo mặt hàng hay theo khách hàng/kênh bán? Có thể làm theo bất kỳ chiều nào: mặt hàng, kênh (siêu thị, đại lý, online), vùng, nhân viên kinh doanh. Nên bắt đầu với chiều mà Ban Giám đốc ra quyết định nhiều nhất.

5. AI có tự đọc file Excel và tính luôn được không? Một số công cụ AI hiện nay đọc được file và chạy code. Tuy vậy, với số liệu dùng để báo cáo, bạn nên tự giữ file tính (Excel/Python) để kiểm soát và đối chiếu được, chỉ dùng AI để soát và diễn giải.

Kết luận

"Doanh thu tăng mà lãi không tăng" là câu hỏi mọi kế toán sẽ gặp. Phân tích giá – lượng – cơ cấu giúp bạn tách tổng chênh lệch thành 4 con số rõ ràng, có kiểm tra khớp tổng, và biến báo cáo kế toán thành công cụ ra quyết định. Kết hợp Excel để tính, Python để tự động hóa và AI để soát lỗi, viết thuyết minh, bạn sẽ có báo cáo quản trị chuyên nghiệp chỉ sau vài phút mỗi kỳ.

Bạn muốn có sẵn file Excel mẫu, script Python và bộ prompt kế toán quản trị để áp dụng ngay? Hãy tìm hiểu các khóa học và công cụ AI cho kế toán – tài chính trên ecostartupai.com — được thiết kế cho người mới bắt đầu, đi từ ví dụ thực tế đến quy trình làm việc hằng ngày.

Tài liệu tham khảo về phương pháp: FTI Consulting – A Quantifiable Approach to Price-Volume-Mix Analysis; eFinancialModels – Mastering the Gross Margin Bridge.