Template Google Sheets Quan Ly Mua Hang va Nha Cung Cap 2027: Toi Uu Chi Phi Procurement

Trong boi canh canh tranh kinh doanh ngay cang gay gat, viec kiem soat chi phi mua hang va quan ly nha cung cap hieu qua la yeu to song con cua doanh nghiep. Nhieu doanh nghiep vua va nho van dang vat loc voi tinh trang mua hang khong kiem soat: mua trung lap, khong so sanh gia ca, cham giao hang anh huong den san xuat va kinh doanh.

Bai viet nay se huong dan ban xay dung mot he thong quan ly mua hang va nha cung cap hoan chinh bang Google Sheets — mien phi, linh hoat va phu hop voi doanh nghiep tu 5 den 200 nguoi.

Phan 1: Nhung Van De Pho Bien Trong Quan Ly Mua Hang

1.1 Mua Hang Trung Lap va Lang Phi

Day la van de cuc ky pho bien khi khong co he thong tap trung. Bo phan ke toan dat mua van phong pham, trong khi bo phan hanh chinh cung dat mua cung loai ma khong biet. Ket qua la kho hang ton dong, tien bac bi chon vui trong hang ton kho khong can thiet.

• Khong co danh sach nha cung cap da duoc phe duyet (approved supplier list)
• Moi bo phan tu lien he nha cung cap rieng, mat di suc manh dam phan tap trung
• Khong tracking lich su mua hang, dan den mua trung
• Thieu quy trinh phe duyet purchase order ro rang

1.2 Gia Ca Khong Toi Uu

Khi khong so sanh gia giua cac nha cung cap dinh ky, doanh nghiep de roi vao tinh trang trung thanh voi mot nha cung cap quen thuoc nhung gia khong con canh tranh. Nghien cuu cho thay, viec so sanh gia dinh ky co the tiet kiem tu 10-25% chi phi mua hang hang nam.

1.3 Quan Ly Lead Time Kem

Lead time (thoi gian tu khi dat hang den khi nhan hang) khong duoc tracking dan den:

• Dat hang tre, thieu nguyen lieu dau vao cho san xuat
• Phai tra phi giao hang nhanh khan cap
• Khong danh gia duoc do tin cay cua nha cung cap

1.4 Bao Cao Mua Hang Khong Co

Ban lanh dao khong biet moi thang doanh nghiep chi bao nhieu tien cho mua hang, chi cho danh muc nao nhieu nhat, nha cung cap nao dang duoc dung nhieu nhat. Thieu du lieu de dua ra quyet dinh chien luoc ve procurement.

Phan 2: Kien Truc Template Quan Ly Mua Hang Toan Dien

Template duoc thiet ke voi 6 sheet chinh, moi sheet phuc vu mot muc dich cu the trong quy trinh procurement:

2.1 Sheet 1: Danh Sach Nha Cung Cap (Supplier Master)

Day la sheet nen tang, chua toan bo thong tin ve nha cung cap da duoc phe duyet:

Cong thuc tinh diem danh gia nha cung cap tu dong:

=IFERROR(
  AVERAGEIFS(
    'Delivery Log'!F:F,
    'Delivery Log'!B:B, A2
  ),
  "Chua co du lieu"
)

2.2 Sheet 2: So Sanh Gia Theo Nha Cung Cap (Price Comparison Matrix)

Sheet nay cho phep so sanh gia cua cung mot san pham tu nhieu nha cung cap khac nhau, tu dong highlight nha cung cap re nhat:

Cau truc bang so sanh gia:
| Ma SP | Ten san pham | Don vi | SUP-001 | SUP-002 | SUP-003 | Gia thap nhat | NCC tot nhat |
|-------|-------------|--------|---------|---------|---------|---------------|--------------|
| SP001 | Giay A4     | Ream   | 85,000  | 82,000  | 88,000  | =MINIFS(...)  | =INDEX(...)  |

Cong thuc tim gia thap nhat (MINIFS):
=MINIFS(D2:F2, D2:F2, ">"&0)

Cong thuc tim nha cung cap co gia thap nhat (INDEX/MATCH):
=INDEX($D$1:$F$1, MATCH(MIN(D2:F2), D2:F2, 0))

Tiet kiem so voi gia cao nhat:
=MAX(D2:F2) - MIN(D2:F2)

Phan tram tiet kiem:
=(MAX(D2:F2) - MIN(D2:F2)) / MAX(D2:F2) * 100

Ap dung Conditional Formatting de highlight:

• Mau xanh la: Gia thap nhat trong hang
• Mau do: Gia cao nhat trong hang
• Mau vang: Gia chenh lech >20% so voi gia thap nhat

Rule Conditional Formatting - Gia thap nhat:
=D2=MIN($D2:$F2)
Format: Fill xanh la nhat, chu xanh dam

Rule gia cao nhat:
=D2=MAX($D2:$F2)
Format: Fill do nhat, chu do dam

2.3 Sheet 3: Purchase Order Tracker

Trung tam cua he thong, tracking toan bo don mua hang tu khi tao den khi nhan hang va thanh toan:

Cong thuc PO Number tu dong:

=IF(B2="","",
  "PO-" & YEAR(B2) & "-" &
  TEXT(COUNTIFS($B$2:B2,"<>"&""), "000")
)

Cong thuc ngay giao du kien (bo qua cuoi tuan):
=WORKDAY(B2, VLOOKUP(C2, 'Supplier Master'!A:H, 8, 0))

Canh bao giao hang tre (Conditional Formatting):
=AND(L2="", TODAY()>K2)
Format: Fill cam dam - don hang chua nhan nhung da qua han

2.4 Sheet 4: Lead Time Tracking va Delivery Performance

Sheet nay giup danh gia hieu suat giao hang cua tung nha cung cap theo thoi gian:

Cong thuc tinh Lead Time thuc te:
=IF(AND(L2<>"", B2<>""),
  NETWORKDAYS(B2, L2) - 1,
  ""
)

Cong thuc On-Time Delivery Rate (theo nha cung cap):
=COUNTIFS(
  'PO Tracker'!C:C, A2,
  'PO Tracker'!L:L, "<>"&"",
  'PO Tracker'!L:L, "<="&DATEVALUE(TEXT('PO Tracker'!K:K,"YYYY-MM-DD"))
) / COUNTIFS(
  'PO Tracker'!C:C, A2,
  'PO Tracker'!L:L, "<>"&""
)

Diem hieu suat nha cung cap (1-5):
Giao dung han 100%: 5 diem
Giao dung han 90-99%: 4 diem
Giao dung han 80-89%: 3 diem
Giao dung han 70-79%: 2 diem
Duoi 70%: 1 diem

=IFS(
  B2>=1, 5,
  B2>=0.9, 4,
  B2>=0.8, 3,
  B2>=0.7, 2,
  TRUE, 1
)

2.5 Sheet 5: Bao Cao Chi Phi Mua Hang

Dashboard tong hop chi phi mua hang theo thang va theo danh muc:

Chi phi mua hang theo thang:
=SUMPRODUCT(
  (MONTH('PO Tracker'!B:B)=MONTH(A2))*
  (YEAR('PO Tracker'!B:B)=YEAR(A2))*
  ('PO Tracker'!M:M="Received")*
  'PO Tracker'!J:J
)

Chi phi theo danh muc trong thang:
=SUMPRODUCT(
  (MONTH('PO Tracker'!B:B)=B$1)*
  (YEAR('PO Tracker'!B:B)=$A2)*
  ('PO Tracker'!E:E=$B2)*
  'PO Tracker'!J:J
)

Top 5 nha cung cap theo chi phi:
=LARGE(SUMIF('PO Tracker'!C:C, 'Supplier Master'!A:A, 'PO Tracker'!J:J), ROW()-1)

Phan 3: Huong Dan Setup Template Tung Buoc

3.1 Buoc 1: Chuan Bi Du Lieu Nha Cung Cap

Truoc khi setup template, hay thu thap thong tin cua tat ca nha cung cap hien tai. Day la buoc quan trong nhat vi toan bo he thong phu thuoc vao du lieu nay.

1. Liet ke tat ca nha cung cap dang hop tac (ke ca khong thuong xuyen)
2. Thu thap thong tin: ten cong ty, nguoi lien he, email, dien thoai, dieu khoan thanh toan
3. Xac dinh lead time thuc te cua tung nha cung cap (hoi truc tiep hoac dua vao kinh nghiem)
4. Phan loai nha cung cap theo danh muc hang hoa cung cap
5. Danh gia so bo chat luong va do tin cay (Active/Probation/Inactive)

3.2 Buoc 2: Nhap Du Lieu Gia Va Xay Dung Price Matrix

Thu thap bao gia hien tai tu tat ca nha cung cap cho cac san pham/dich vu ban mua thuong xuyen nhat.

Checklist Price Matrix:
[x] Liet ke top 20 san pham mua nhieu nhat
[x] Gui yeu cau bao gia cho it nhat 3 nha cung cap moi loai
[x] Nhap gia vao Price Comparison Matrix
[x] Cai dat Conditional Formatting highlight gia tot nhat
[x] Len lich review gia dinh ky (3 thang/lan)

3.3 Buoc 3: Thiet Lap Quy Trinh PO

Xac dinh ro quy trinh tu khi co nhu cau den khi nhan hang va thanh toan:

• Nguong phe duyet: PO duoi 5 trieu — truong bo phan duyet; 5-50 trieu — giam doc duyet; tren 50 trieu — hoi dong quan tri duyet
• Timeline: Tao PO - Phe duyet trong 24h - Gui cho nha cung cap - Nhan hang - Kiem tra - Nhap kho - Thanh toan
• Format PO Number: PO-YYYY-NNN (tu dong bang cong thuc)

3.4 Buoc 4: Cai Dat Canh Bao Tu Dong

Canh bao don hang sap den han (Conditional Formatting):
Mau vang: Con 3 ngay den han giao
=AND(K2-TODAY()<=3, K2-TODAY()>=0, L2="")

Mau do: Da qua han giao
=AND(TODAY()>K2, L2="")

Mau xanh: Da nhan hang
=L2<>""

Script Apps Script de gui email nhac nho (tuy chon):
function sendPOReminders() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('PO Tracker');
  const data = sheet.getDataRange().getValues();
  const today = new Date();
  
  for (let i = 1; i < data.length; i++) {
    const expectedDate = data[i][10];
    const actualDate = data[i][11];
    
    if (!actualDate && expectedDate) {
      const daysUntil = Math.ceil(
        (new Date(expectedDate) - today) / (1000 * 60 * 60 * 24)
      );
      if (daysUntil <= 2 && daysUntil >= 0) {
        MailApp.sendEmail({
          to: Session.getActiveUser().getEmail(),
          subject: 'Nhac nho: PO sap den han',
          body: 'PO ' + data[i][0] + ' du kien giao ngay ' + expectedDate
        });
      }
    }
  }
}

Phan 4: Cong Thuc Nang Cao Cho Procurement

4.1 Supplier Scorecard - Danh Gia Da Tieu Chi

Khong chi so sanh gia, hay danh gia nha cung cap theo nhieu tieu chi:

Trong so danh gia nha cung cap:
- Gia ca: 40%
- Thoi gian giao hang (on-time rate): 30%
- Chat luong san pham: 20%
- Dich vu ho tro: 10%

Cong thuc diem tong hop:
=SUMPRODUCT(
  {0.4, 0.3, 0.2, 0.1},
  {GiaDiem, LeadTimeDiem, ChatLuongDiem, DichVuDiem}
)

Diem gia (1-5, nghich dao - gia thap diem cao):
=6 - RANK(I2, $I$2:$I$10, 1)

Diem lead time (1-5, cang nhanh diem cang cao):
=6 - RANK(J2, $J$2:$J$10, 0)

4.2 Phan Tich Pareto Chi Phi Mua Hang

Nguyen tac Pareto (80/20) trong procurement:
Thuong 20% nha cung cap chiem 80% gia tri mua hang

Xep hang nha cung cap theo gia tri:
=LARGE(SUMIF('PO Tracker'!C:C,
  'Supplier Master'!A:A,
  'PO Tracker'!J:J), ROW()-1)

Phan tram tich luy:
=SUM($B$2:B2)/SUM($B$2:$B$20)*100

Phan loai ABC:
A (0-80%): Nha cung cap chien luoc - can quan tam dac biet
B (80-95%): Nha cung cap quan trong - theo doi dinh ky
C (95-100%): Nha cung cap nho - co the hop nhat/loai bo

4.3 Du Bao Nhu Cau Mua Hang

Du bao don gian dua tren trung binh dong 3 thang:
=AVERAGE(
  SUMPRODUCT(
    (MONTH('PO Tracker'!B:B)=MONTH(DATE(YEAR(TODAY()),A2-1,1)))*
    ('PO Tracker'!E:E=B$1)*
    'PO Tracker'!J:J
  ),
  SUMPRODUCT(
    (MONTH('PO Tracker'!B:B)=MONTH(DATE(YEAR(TODAY()),A2-2,1)))*
    ('PO Tracker'!E:E=B$1)*
    'PO Tracker'!J:J
  ),
  SUMPRODUCT(
    (MONTH('PO Tracker'!B:B)=MONTH(DATE(YEAR(TODAY()),A2-3,1)))*
    ('PO Tracker'!E:E=B$1)*
    'PO Tracker'!J:J
  )
)

Canh bao du bao tang bat thuong (>20% so voi thang truoc):
=IF(
  (B2 - C2) / C2 > 0.2,
  "Tang manh - Kiem tra lai nhu cau",
  "Binh thuong"
)

Phan 5: So Sanh Template Google Sheets vs Phan Mem ERP

5.1 Dau Hieu Can Upgrade Len Phan Mem ERP

• Xu ly hon 200 purchase orders moi thang
• Can tich hop tu dong voi he thong ke toan (MISA, Fast Accounting)
• Co nhieu kho hang o cac dia diem khac nhau
• Can workflow phe duyet da cap phuc tap voi audit trail
• Team mua hang hon 5 nguoi lam viec dong thoi
• Yeu cau bao cao real-time cho ban lanh dao

Phan 6: Tich Hop Voi Cac Cong Cu Khac

6.1 Tich Hop Google Forms Cho Yeu Cau Mua Hang

Tao Google Form "Yeu Cau Mua Hang" voi cac truong:
- Ten nguoi yeu cau
- Bo phan
- Mo ta hang hoa can mua
- So luong uoc tinh
- Ly do/muc dich su dung
- Ngay can co hang
- Nha cung cap de xuat (neu co)
- Ngan sach uoc tinh

Ket noi Form -> Google Sheets (Responses sheet)
Apps Script trigger tu dong tao PO draft khi co form submission moi:

function onFormSubmit(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('PO Tracker');
  const formData = e.values;
  const lastRow = sheet.getLastRow();
  const poNumber = 'PO-' + new Date().getFullYear() + '-' +
    String(lastRow).padStart(3, '0');
  
  sheet.appendRow([
    poNumber,
    new Date(),
    '',
    formData[2],
    formData[6],
    '',
    formData[7],
    formData[4],
    '',
    'Draft',
    formData[1],
    formData[3]
  ]);
}

6.2 Ket Noi Voi Google Drive Luu Tru Invoice

Cau truc thu muc Google Drive:
Procurement 2027/
  Purchase Orders/
    Q1-2027/
      PO-2027-001.pdf
      PO-2027-002.pdf
  Supplier Invoices/
    SUP-001_ABC Company/
      Invoice_Jan2027.pdf
  Contracts/
    SUP-001_Contract_2027.pdf

Cong thuc tao link Drive trong Google Sheets:
=HYPERLINK(
  "https://drive.google.com/drive/folders/FOLDER_ID",
  "Xem ho so nha cung cap"
)

Phan 7: Chi So KPI Procurement Quan Trong

Phan 8: Cac Chi So Hieu Suat Procurement Nang Cao

8.1 Spend Under Management (SUM)

Ty le chi tieu duoc quan ly chinh thuc qua PO system:
=SUMIF('PO Tracker'!M:M, "<>Draft", 'PO Tracker'!J:J) /
 SUM('PO Tracker'!J:J) * 100

Muc tieu: >80% tong chi phi mua hang duoc quan ly qua PO

Cac dau hieu chi tieu ngoai he thong:
- Nhan vien dat hang truc tiep khong qua PO
- Thanh toan bang tien mat khong co chung tu
- Nha cung cap moi khong co trong danh sach phe duyet

8.2 Contract Coverage Rate

% nha cung cap co hop dong chinh thuc:
=COUNTIF('Supplier Master'!L:L, "<>"&"") /
 COUNTA('Supplier Master'!A2:A) * 100

Loi ich co hop dong:
- Gia co dinh, tranh bien dong gia bat ngo
- Dieu khoan giao hang ro rang
- Chinh sach doi tra hang ro rang
- Bao ve phap ly khi co tranh chap

Phan 9: Thuc Hanh Tot Nhat - Best Practices

9.1 Quy Tac Chon Nha Cung Cap Moi

1. Thu thap it nhat 3 bao gia truoc khi quyet dinh
2. Tham khao y kien cua 2-3 khach hang khac cua nha cung cap do
3. Dat don hang thu nho truoc khi ky hop dong dai han
4. Kiem tra giay to phap ly: giay phep kinh doanh, ma so thue
5. Danh gia nha may/kho hang neu san pham co gia tri cao

9.2 Quan Ly Rui Ro Chuoi Cung Ung

Ma tran rui ro nha cung cap:
| Rui ro | Muc do anh huong | Xac suat | Bien phap giam thieu |
|--------|-----------------|----------|---------------------|
| NCC dung san xuat | Cao | Thap | Du phong 2 NCC cho hang chien luoc |
| Tre giao hang | Trung binh | Trung binh | Buffer stock 2 tuan |
| Tang gia dot ngot | Trung binh | Trung binh | Hop dong gia co dinh 6-12 thang |
| Chat luong kem | Cao | Thap | Checklist kiem tra dau vao |

Canh bao NCC rui ro cao (Conditional Formatting):
=AND(I2<3, H2<0.8)
Format: Fill do dam - nha cung cap co van de nghiem trong

Phan 10: FAQ - Cau Hoi Thuong Gap

Q: Template nay phu hop cho doanh nghiep quy mo nao?

Template phu hop nhat cho doanh nghiep tu 5-200 nhan vien, xu ly tu 10-200 purchase orders moi thang. Voi quy mo lon hon, ban nen can nhac phan mem ERP chuyen dung nhu SAP Business One, Oracle NetSuite hoac cac giai phap phan mem mua hang Viet Nam.

Q: Lam the nao de nhieu nguoi dung cung luc khong gay xung dot du lieu?

Google Sheets ho tro collaboration realtime. De tranh xung dot, hay:

• Phan cong ro ai phu trach sheet nao (vi du: team mua hang dung PO Tracker, ke toan dung Payment sheet)
• Protect cac sheet/range quan trong chi cho phep edit boi nguoi co trach nhiem
• Dung Named Ranges va Data Validation de giam loi nhap lieu

Q: Co the tu dong gui PO cho nha cung cap qua email khong?

Co, su dung Google Apps Script voi MailApp service. Ban co the tao template email HTML, dien thong tin PO tu sheet va gui tu dong khi status doi sang "Approved". Can khoang 20-30 dong code Apps Script.

Q: Lam sao de track nhung don hang da thanh toan va chua thanh toan?

Them cot "Trang thai thanh toan" (Unpaid/Partial/Paid), "Ngay thanh toan", "So hoa don" vao PO Tracker. Tao summary cong no phai tra voi cong thuc SUMIFS loc theo trang thai "Unpaid" va "Partial".

Q: Template co tuong thich voi Excel khong?

Hau het cac cong thuc deu tuong thich voi Excel. Tuy nhien, ARRAYFORMULA va mot so ham GS dac thu (QUERY, IMPORTRANGE) khong hoat dong tren Excel. Neu can dung Excel, hay thay the bang ham tuong duong hoac dung Power Query.

Ket Luan

Mot he thong quan ly mua hang hieu qua khong can thiet phai ton kem. Voi template Google Sheets duoc thiet ke dung cach, ban co the:

• Tiet kiem 10-25% chi phi mua hang nho so sanh gia va dam phan tot hon
• Giam 50% thoi gian xu ly PO voi workflow tu dong hoa
• Cai thien moi quan he voi nha cung cap nho du lieu minh bach
• Co bao cao procurement day du de ra quyet dinh chien luoc

Tai template mien phi tai SheetStore va bat dau toi uu hoa quy trinh mua hang cua ban ngay hom nay!