Template Google Sheets Quan Ly Thu Chi Hoi Nhom va CLB 2027: Minh Bach Tai Chinh Cong Dong

Quan ly tai chinh hoi nhom, cau lac bo, to chuc cong dong la viec lam quan trong nhung thuong bi xem nhe. Tien quy khong ro rang, khong ai biet tien dau noi, chi phi su kien khong minh bach — nhung van de nay gay mat niem tin, manh mun cong dong va doi khi dan den nhung tranh chap kho chiu. Google Sheets cung cap giai phap quan ly tai chinh hoi nhom hoan toan mien phi, minh bach va de chia se.

Phan 1: Nhung Van De Thuong Gap Trong Quan Ly Tai Chinh Hoi Nhom

1.1 Thieu Minh Bach - Nguyen Nhan So 1 Cua Xung Dot

Mot hoi nhom pho bien to chuc hang su kien moi thang nhung chi co 1-2 nguoi biet tien dang o dau, chi vao viec gi. Cac thanh vien con lai khong co cach nao kiem tra va dan dan mat tin tuong vao nguoi quan quy.

• Khong co bao cao tai chinh chinh thuc, chi co "loi miem" tu nguoi giu quy
• Khong ro thu phi hoi vien da duoc su dung nhu the nao
• Khong co lich su giao dich de doi chieu khi co tranh chap
• Kho doi soat chi phi su kien voi tong quy thu

1.2 Theo Doi Dong Gop Cua Thanh Vien Kho Khan

Voi hoi nhom co 30-100 thanh vien, viec nho ai da dong phi, ai chua, ai dong thieu la rat met moi neu chi dung ghi chu ho so hay nho trong dau. Dac biet phuc tap khi co nhieu muc phi khac nhau (phi hoi vien, phi su kien, quyen gop tu nguyen).

1.3 Quyet Toan Chi Phi Su Kien Cham Tre

Sau moi su kien, thu quy phai thu tien, quy toan va bao cao ket qua tai chinh. Neu khong co he thong, viec nay thuong mat hang tuan, cac thanh vien su kien khong biet co lai hay lo, ai duoc hoan tien ai con no.

Phan 2: Kien Truc Template Quan Ly Tai Chinh Hoi Nhom

2.1 Sheet 1: Danh Sach Thanh Vien

Cong thuc tinh tong da dong cua tung thanh vien:

Tong dong gop tu sheet "Lich Su Thu":
=SUMIFS(
  'Lich Su Thu'!D:D,
  'Lich Su Thu'!B:B, A2,
  'Lich Su Thu'!E:E, "Phi hoi vien"
)

Kiem tra trang thai dong phi (ARRAYFORMULA):
=ARRAYFORMULA(
  IF(I2:I<=0, "Da dong du",
  IF(I2:I>0, "Con no: "&TEXT(I2:I,"#,##0")&" dong",
  ""))
)

Danh sach thanh vien con no (>0):
=FILTER(B:B, I:I>0)

Tong so no chua thu:
=SUMIF(I:I, ">0", I:I)

2.2 Sheet 2: Lich Su Thu (Dong Gop)

Ghi chep toan bo cac khoan thu, ke ca phi hoi vien, phi su kien va quyen gop:

Cau truc sheet "Lich Su Thu":
| STT | Ma TV | Ten TV | So tien | Loai thu | Ngay thu | Hinh thuc | Ghi chu |
|-----|-------|--------|---------|---------|----------|-----------|---------|
| 1 | TV-001 | Nguyen A | 500,000 | Phi hoi vien | 01/01/2027 | CK | Nam 2027 |
| 2 | TV-002 | Tran B | 200,000 | Phi su kien | 15/01/2027 | Tien mat | Su kien Tet |
| 3 | TV-003 | Le C | 100,000 | Quyen gop | 20/01/2027 | CK | Tu nguyen |

Loai thu (Data Validation):
- Phi hoi vien
- Phi su kien
- Quyen gop tu nguyen
- Tien phat (den muon, bo hop...)
- Thu khac

Hinh thuc thanh toan (Data Validation):
- Chuyen khoan
- Tien mat
- Momo/VNPay
- Chuyen qua truong nhom

Tong thu theo tung loai (SUMIF):
=SUMIF('Lich Su Thu'!E:E, "Phi hoi vien", 'Lich Su Thu'!D:D)
=SUMIF('Lich Su Thu'!E:E, "Phi su kien", 'Lich Su Thu'!D:D)
=SUMIF('Lich Su Thu'!E:E, "Quyen gop tu nguyen", 'Lich Su Thu'!D:D)

Tong thu trong thang:
=SUMPRODUCT(
  (MONTH('Lich Su Thu'!F:F)=MONTH(TODAY()))*
  (YEAR('Lich Su Thu'!F:F)=YEAR(TODAY()))*
  'Lich Su Thu'!D:D
)

2.3 Sheet 3: Lich Su Chi (Chi Phi)

Cau truc sheet "Lich Su Chi":
| STT | Ngay chi | Danh muc | Mo ta | So tien | Nguoi chi | Hinh thuc | Chung tu | Su kien lien quan |
|-----|----------|---------|-------|---------|-----------|-----------|----------|------------------|
| 1 | 15/01/2027 | Su kien | Thue dia diem Tet | 2,000,000 | Nguyen A | CK | Hoa don #001 | Su kien Tet 2027 |
| 2 | 16/01/2027 | An uong | Tiec tat nien | 3,500,000 | Tran B | Tien mat | Hoa don #002 | Su kien Tet 2027 |
| 3 | 05/02/2027 | Van phong pham | In an tai lieu | 150,000 | Le C | CK | Receipt #003 | |

Danh muc chi (Data Validation):
- Su kien / To chuc
- An uong / Tiec
- Di lai / Van chuyen
- Van phong pham / In an
- Mua sam tai san CLB
- Phi dich vu (website, phan mem...)
- Hoc tap / Dao tao
- Qua tang / Hoa hong
- Chi khac

Tong chi theo danh muc (dashboard):
=SUMIF('Lich Su Chi'!C:C, "Su kien / To chuc", 'Lich Su Chi'!E:E)

Tong chi trong thang:
=SUMPRODUCT(
  (MONTH('Lich Su Chi'!B:B)=B1)*
  (YEAR('Lich Su Chi'!B:B)=A1)*
  'Lich Su Chi'!E:E
)

Chi phi trung binh moi su kien:
=SUMIF('Lich Su Chi'!I:I,"<>"&"","'Lich Su Chi'!E:E") /
 COUNTIF('Lich Su Chi'!I:I,"<>"&"")

Canh bao vuot ngan sach su kien:
=IF(
  SUMIF('Lich Su Chi'!I:I, A2, 'Lich Su Chi'!E:E) >
  VLOOKUP(A2, 'Ke Hoach Su Kien'!A:D, 4, 0),
  "VUOT NGAN SACH!",
  "OK"
)

2.4 Sheet 4: Phan Bo Chi Phi Su Kien (Event Cost Split)

Phan bo chi phi su kien cho cac thanh vien tham gia:

Vi du: Su kien Tet 2027
Tong chi phi: 6,500,000 dong
So thanh vien tham gia: 25 nguoi
Tien thu tu thu (phi su kien 200,000/nguoi): 5,000,000 dong

Chi phi ngan sach ho tro (tu quy chung): 1,500,000 dong
Chi phi moi thanh vien thuc te: 260,000 dong
Hoan tien cho nguoi thu phi: (5,000,000 - 5,000,000) = 0 dong (Chinh xac!)

Cong thuc chi phi moi dau nguoi:
=SUMIF('Lich Su Chi'!I:I, A2, 'Lich Su Chi'!E:E) /
 COUNTIFS('Danh Sach Tham Du'!B:B, A2, 'Danh Sach Tham Du'!C:C, "Xac nhan")

Kiem tra so du su kien:
=SUMIFS('Lich Su Thu'!D:D, 'Lich Su Thu'!H:H, A2, 'Lich Su Thu'!E:E, "Phi su kien") -
 SUMIF('Lich Su Chi'!I:I, A2, 'Lich Su Chi'!E:E)

Danh sach tham du su kien:
| Ma TV | Su kien | Trang thai | Da dong phi | So tien | Ngay dong |
|-------|--------|-----------|------------|---------|----------|
| TV-001 | Tet 2027 | Xac nhan | Co | 200,000 | 10/01/2027 |
| TV-002 | Tet 2027 | Xac nhan | Chua | 0 | |

2.5 Sheet 5: Bao Cao Tai Chinh Cong Khai

Sheet nay duoc chia se dang xem-only voi tat ca thanh vien de dam bao tinh minh bach:

Bao Cao Tai Chinh Thang 1/2027 - HOI NHOM ABC

=== TON QUY DAU KY ===
So du dau nam 2027: 15,000,000 dong

=== THU TRONG KY ===
Phi hoi vien 2027: =SUMIF(...)     -> 25,000,000 dong
Phi su kien Tet: =SUMIF(...)       -> 5,000,000 dong
Quyen gop tu nguyen: =SUMIF(...)   -> 3,000,000 dong
Tong thu:                          -> 33,000,000 dong

=== CHI TRONG KY ===  
To chuc su kien Tet: =SUMIF(...)   -> 6,500,000 dong
Mua sam tai san: =SUMIF(...)       -> 1,200,000 dong
Van phong pham: =SUMIF(...)        -> 350,000 dong
Tong chi:                          -> 8,050,000 dong

=== KET QUA ===
Thu - Chi:                         -> 24,950,000 dong
TON QUY CUOI KY:                   -> 39,950,000 dong
(= ton dau ky + thu - chi)

Link chung tu: https://drive.google.com/...
Nguoi lap bao cao: [Ten Thu Quy]
Ngay lap: [Ngay]

Phan 3: Tich Hop Google Forms De Thu Tien Online

3.1 Form Dang Ky Su Kien va Dong Phi

Tao Google Form "Dang Ky Tham Du [Ten Su Kien]":
Cau hoi:
1. Ten thanh vien (Short answer)
2. Ma thanh vien (Short answer)
3. So dien thoai (Short answer)
4. Ban co tham du khong? (Multiple choice: Co / Khong)
5. So nguoi kem theo (neu cho phep) (Number)
6. Hinh thuc dong phi (Multiple choice: CK / Momo / VNPay / Dong mat)
7. Ban da chuyen tien chua? (Multiple choice: Da chuyen / Se dong truc tiep)
8. Ghi chu them (Paragraph)

Thong tin chuyen khoan (them vao form description):
Ngan hang: Vietcombank
So tai khoan: 1234567890
Ten: HOI NHOM ABC
Noi dung: [Ma TV] - Phi su kien [Ten su kien]

Sau khi co response:
- Responses tu dong vao Google Sheets
- Tao column de thu quy danh dau "Da nhan tien" = TRUE/FALSE
- COUNTIF de biet tong so xac nhan tham du
- SUMIF de tinh tong phi thu duoc

3.2 Nhac Nho Dong Phi Tu Dong Bang Apps Script

Gui email nhac nho cho thanh vien chua dong phi hoi vien:

function sendPaymentReminders() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const memberSheet = spreadsheet.getSheetByName('Danh Sach Thanh Vien');
  const data = memberSheet.getDataRange().getValues();
  
  for (let i = 1; i < data.length; i++) {
    const email = data[i][4];  // Cot E: email
    const name = data[i][1];   // Cot B: ten
    const outstanding = data[i][8]; // Cot I: con no
    
    if (outstanding > 0 && email) {
      MailApp.sendEmail({
        to: email,
        subject: 'Nhac nho dong phi hoi vien - Hoi Nhom ABC',
        htmlBody: 
          '<p>Chao ' + name + ',</p>' +
          '<p>Ban con no <strong>' + 
            outstanding.toLocaleString('vi-VN') + 
          ' dong</strong> phi hoi vien nam 2027.</p>' +
          '<p>Vui long chuyen khoan truoc ngay 28/02/2027:</p>' +
          '<ul>' +
          '<li>Ngan hang: Vietcombank</li>' +
          '<li>STK: 1234567890</li>' +
          '<li>Chu TK: HOI NHOM ABC</li>' +
          '<li>Noi dung: ' + data[i][0] + ' phi hoi vien 2027</li>' +
          '</ul>' +
          '<p>Xem bao cao tai chinh cong khai: [Link GS]</p>' +
          '<p>Tran trong,<br>Thu Quy Hoi Nhom ABC</p>'
      });
    }
  }
  
  Logger.log('Da gui ' + da_gui + ' email nhac nho');
}

Cai dat trigger chay tu dong ngay 1 moi thang:
Edit > Triggers > Add Trigger:
- Function: sendPaymentReminders
- Event source: Time-driven
- Type: Month timer
- Day: 1st of month
- Hour: 9:00 AM

Phan 4: Phan Quyen Truy Cap Google Sheets

4.1 Cau Hinh Chia Se Cho Cac Vai Tro Khac Nhau

Cac muc chia se:

1. QUAN LY / BAN CHAP HANH:
   Share > "Anyone with the link" > Editor
   Hoac: Share voi tung email cu the > Editor

2. THANH VIEN BINH THUONG (chi xem):
   Share > "Anyone with the link" > Viewer
   Chi chia se cac sheet bao cao cong khai:
   - Sheet "Bao Cao Tai Chinh"
   - Sheet "Lich Su Thu Chi" (khong biet ten nguoi chi)
   - Sheet "Ke Hoach Su Kien"

3. PROTECT SHEETS QUAN TRONG:
   Data > Protect sheets & ranges:
   - "Danh Sach Thanh Vien": Chi admin edit
   - "Lich Su Thu": Chi thu quy edit
   - "Lich Su Chi": Chi thu quy va chu tich edit
   - "Bao Cao Tai Chinh": Cong thuc tu dong, KHONG ai edit truc tiep

4. HIDE CAC SHEET NHAY CAM:
   Chuot phai vao tab sheet > Hide sheet
   An cac sheet co thong tin ca nhan day du khoi cai nhin mac dinh

4.2 Kinh Sach - Audit Trail Cho Thu Quy

Sheet "Nhat Ky Giao Dich" (khong ai duoc xoa):
| Thoi gian | Loai GD | Ma TV | So tien | Nguoi thuc hien | Ghi chu |
|-----------|--------|-------|---------|----------------|---------|
| 01/01/2027 09:30 | Thu | TV-001 | 500,000 | Thu Quy A | Phi HV nam 2027 |
| 02/01/2027 14:00 | Chi | - | 200,000 | Chu tich B | In to bieu cuoi nam |

Nguyen tac kinh sach:
- KHONG xoa du lieu cu, chi sua trang thai
- Moi giao dich co ky thuat so: Ngay + Ten nguoi nhap + Hash ID
- In bao cao tai chinh hang quy ky va dong dau tap the
- Doi soat so du ngan hang va so du Google Sheets moi thang

Apps Script ghi audit log tu dong:
function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  
  // Chi ghi log khi chinh sua cac sheet tai chinh
  const financialSheets = ['Lich Su Thu', 'Lich Su Chi'];
  if (!financialSheets.includes(sheet.getName())) return;
  
  const logSheet = e.source.getSheetByName('Nhat Ky Giao Dich');
  const user = Session.getActiveUser().getEmail();
  const timestamp = new Date();
  const cellAddress = range.getA1Notation();
  const oldValue = e.oldValue || '';
  const newValue = e.value || '';
  
  logSheet.appendRow([
    timestamp,
    sheet.getName(),
    cellAddress,
    oldValue,
    newValue,
    user
  ]);
}

Phan 5: Quan Ly Tai San CLB

Sheet "Tai San CLB" - Kiem ke va giam sat:
| Ma TS | Ten tai san | So luong | Gia tri mua | Ngay mua | Nguoi giu | Vi tri | Tinh trang |
|-------|------------|---------|------------|---------|----------|--------|-----------|
| TS-001 | Loa Bluetooth | 2 | 1,500,000 | 01/03/2026 | Nguyen A | Phong hop | Tot |
| TS-002 | Man chieu | 1 | 3,000,000 | 01/03/2026 | Tran B | Kho | Tot |
| TS-003 | Ao dong phuc | 30 | 300,000/cai | 15/05/2026 | Le C | Kho | Tot |

Tong gia tri tai san:
=SUMPRODUCT(C2:C100*D2:D100)

Tai san can bao duong (qua 1 nam chua check):
=FILTER(B:B, AND(TODAY()-F:F>365, G:G="Tot"))

Dang ky muon tai san (Google Form):
- Nguoi muon
- Tai san muon (dropdown)
- Ngay muon
- Ngay tra du kien
- Muc dich su dung

Theo doi tai san dang duoc muon:
=FILTER(
  'Tai San CLB'!B:B,
  COUNTIFS('Muon Tai San'!C:C, 'Tai San CLB'!A:A,
           'Muon Tai San'!F:F, "") > 0
)

Phan 6: Bieu Do Va Truc Quan Hoa

Cac bieu do nen co trong Dashboard:

1. Donut Chart: Ty trong thu phi (phi HV / phi su kien / quyen gop)
Data:
=QUERY('Lich Su Thu'!A:E,
  "SELECT E, SUM(D) WHERE D > 0 GROUP BY E LABEL SUM(D) 'Tong thu'")

2. Bar Chart: Thu chi theo thang (so sanh)
Data:
=QUERY({
  'Lich Su Thu'!B:D;
  'Lich Su Chi'!B:E
}, "SELECT ...")

3. Line Chart: So du quy theo thang
=B2+SUM(ThuThang)-SUM(ChiThang)

4. Pie Chart: Chi phi theo danh muc
=QUERY('Lich Su Chi'!A:E,
  "SELECT C, SUM(E) WHERE E > 0 GROUP BY C
   ORDER BY SUM(E) DESC
   LABEL SUM(E) 'Tong chi'")

Tao Sparkline mini tren Dashboard:
=SPARKLINE(
  ARRAYFORMULA(
    MMULT(
      IFERROR(DATEVALUE(TEXT(B2:B13,"YYYY-MM")) = TRANSPOSE(B2:B13), 0),
      D2:D13
    )
  ),
  {"charttype","column";"color","#4CAF50"}
)

Phan 7: Mau Bao Cao Cuoi Nam

BAO CAO HOAT DONG TAI CHINH NAM 2027
HOI NHOM [TEN HOI NHOM]

1. KET QUA THU CHI
   Tong thu: xxx,xxx,xxx dong
   Tong chi: xxx,xxx,xxx dong
   Thu - Chi: xxx,xxx,xxx dong
   
2. CAN DOI QUY
   Du dau nam: xxx,xxx,xxx dong
   Du cuoi nam: xxx,xxx,xxx dong
   
3. PHI HOI VIEN
   Tong so thanh vien: xx nguoi
   Da dong phi day du: xx nguoi (xx%)
   Con no phi: xx nguoi / xxx,xxx,xxx dong
   
4. SU KIEN DA TO CHUC
   So su kien: xx su kien
   Tong chi phi su kien: xxx,xxx,xxx dong
   Chi phi trung binh/su kien: xxx,xxx dong
   
5. TAI SAN CLB
   So tai san: xx loai
   Tong gia tri: xxx,xxx,xxx dong
   
6. KE HOACH 2028
   Ngan sach du kien: xxx,xxx,xxx dong
   So su kien ke hoach: xx su kien
   ...

Ky xac nhan:
Thu Quy: [Ten] [Chu ky] [Ngay]
Chu Tich: [Ten] [Chu ky] [Ngay]
Giam Sat: [Ten] [Chu ky] [Ngay]

Phan 8: FAQ - Cau Hoi Thuong Gap

Q: Neu thu quy doi nguoi, lam the nao de ban giao?

Voi Google Sheets, ban giao thu quy don gian hon rat nhieu so voi file Excel truyen thong. Chi can:

1. In bao cao tai chinh tai thoi diem ban giao (kem xac nhan thu quy cu va moi)
2. Chup anh so du tai khoan ngan hang tai thoi diem ban giao
3. Doi quyen Edit sheet tu email thu quy cu sang email thu quy moi
4. Thu quy moi ky xac nhan da nhan ban giao (ghi vao sheet Nhat Ky)

Q: Lam sao cac thanh vien co the xem bao cao ma khong chinh sua?

Tao mot tab "Bao Cao Cong Khai" chi dung cong thuc tham chieu den cac sheet chinh. Share link voi quyen "Viewer" cho tat ca thanh vien. Ho co the xem nhung khong the thay doi bat ky du lieu nao.

Q: Can luu tru chung tu nhu the nao?

Scan/chup anh tat ca hoa don, bien lai vao Google Drive. Tao thu muc theo nam va thang. Trong Google Sheets, them cot "Link chung tu" va dan link toi file trong Drive. Vua de truy cap, vua khong mat.

Q: Neu hoi nhom co nhieu tai khoan ngan hang thi sao?

Them cot "Tai khoan" vao sheet Lich Su Thu va Lich Su Chi. Tao sheet "Doi Soat Ngan Hang" rieng cho tung tai khoan voi cong thuc tinh so du doc lap. Dashboard tong hop so du tat ca tai khoan bang SUMIF.

Ket Luan

Quan ly tai chinh hoi nhom minh bach khong kho neu co cong cu dung. Voi template Google Sheets nay, hoi nhom cua ban co the:

• Minh bach hoa 100% dong thu va chi: moi giao dich deu co lich su ro rang
• Tu dong nhac nho thanh vien dong phi khong can thu cong
• Chia se bao cao real-time cho tat ca thanh vien xem
• Quan ly su kien va phan bo chi phi chinh xac
• Xay dung niem tin trong cong dong nho su minh bach

Tai template mien phi tai SheetStore va bat dau xay dung hoi nhom minh bach ngay hom nay!