Excel Format Requirements

This document describes the required Excel file structure for successful quotation processing.

File Specifications

Basic Requirements

Sheet Requirements

Minimum Sheets Required

1. PII Sheet - Required

   • Contains quotation from PT Punch Industry Indonesia
   • Standard format with item lists

2. SGI/Sales Sheet - Optional

   • Can be: Standard SGI, TMMIN, METINDO, or MARUICHI format
   • Contains sales/logistics information

Sheet Naming Conventions

Acceptable Sheet Names

The system detects sheets by checking for specific keywords:

PII Sheet Detection

• "PII"
• "PUNCH"
• "PT. PUNCH"
• "PUNCH INDUSTRY"

SGI Sheet Detection

• "SGI"
• "SOMAGEDE"
• "PT. SOMAGEDE"

TMMIN Format Detection

• "TMMIN"
• "TOYOTA"

METINDO Format Detection

• "METINDO"
• "LOGISTICS"

MARUICHI Format Detection

• "MARUICHI"
• "DISCOUNT"

Note: Sheet name matching is case-insensitive.

Column Structure

Standard Format (PII/SGI)

12 columns required:

TMMIN Format

8 columns required:

METINDO Format

9 columns required:

MARUICHI Format

10 columns required:

Header Row Detection

The system searches for header rows containing:

• "NO"
• "ITEM"
• "KODE ITEM"
• "QTY"
• "MATA UANG"
• "HARGA"
• "TOTAL"

Header Row Rules

1. Header must be in a single row
2. Must contain at least 5 of the required column headers
3. Can be anywhere in first 50 rows
4. Case-insensitive matching

Quotation Metadata

Each quotation should contain:

Required Information

1. QTI Number

   • Format: QTI-XXXXXX or QTI-XXXXXX-REV-X
   • Must appear above item table
   • Used as unique identifier

2. Customer Name

   • Should be clearly labeled
   • Can contain: "TO:", "KEPADA:", "CUSTOMER:"
   • Single line preferred

3. Date

   • Format: Jakarta, DD MMMM YYYY
   • Example: Jakarta, 23 Oktober 2025
   • Comma and space handling flexible

4. Attention (Optional)

   • Person to address quotation to
   • Label: "ATTN:", "UP:", "KEPADA YTH:"

5. Salesman (SGI sheets only)

   • Sales representative name
   • Label: "SALESMAN:", "SALES:"

TMMIN Specific Fields

• Kanban Number
• RF Number
• CC Field

Located near top of quotation, before item table.

Data Formatting

Number Formats

• Quantities: Integer or decimal (e.g., 10, 15.5)
• Prices: Decimal with 2 places (e.g., 1000.00)
• Percentages: Decimal (e.g., 0.10 for 10%)

Text Formats

• Item Codes: Alphanumeric, can include hyphens
• Customer Names: Any characters, avoid line breaks
• Dates: Indonesian format with month names

Currency

• Default: IDR (Indonesian Rupiah)
• Others accepted: USD, JPY, EUR

Notes and Totals

Notes Section

Located after item table:

• Begin with "NOTE:", "CATATAN:", or "KETERANGAN:"
• Can span multiple rows
• Ends at company footer

Totals Section

Must include:

1. Subtotal (before tax)
   • Label: "TOTAL TAX LIABLE", "TOTAL SEBELUM PAJAK"
2. Tax (10% VAT)
   • Label: "TAX", "PAJAK", "PPN"
3. Grand Total (after tax)
   • Label: "TOTAL", "TOTAL SESUDAH PAJAK"

Common Formatting Issues

❌ Avoid These

1. Merged Cells in data rows

   • Can merge in headers or metadata
   • Never merge in item list

2. Empty Rows in item list

   • System stops at first empty row
   • Keep items contiguous

3. Multiple Headers in same sheet

   • One header row per quotation
   • Use separate sheets for multiple quotations

4. Hidden Rows/Columns

   • May cause extraction issues
   • Unhide all before upload

5. Formulas in critical cells

   • Use values instead
   • System reads displayed value

6. Special Characters in codes

   • Avoid: \, /, *, ?
   • Use: -, _, .

✅ Best Practices

1. Use template provided by IT department
2. Keep formatting consistent across quotations
3. Verify QTI numbers are unique
4. Ensure dates use correct format
5. Check item codes match SAP HANA
6. Remove test data before upload
7. Save as .xlsx format (not .xls)

Validation Rules

System validates:

1. ✓ File extension is .xlsx
2. ✓ File size under 10MB
3. ✓ At least one PII sheet exists
4. ✓ Header row detected in each sheet
5. ✓ QTI number found for each quotation
6. ✓ At least one item in quotation
7. ✓ Required columns present
8. ✓ No password protection

Example Structure

┌─────────────────────────────────────┐
│ QUOTATION                           │
│ QTI-240102                          │
│ Jakarta, 15 Januari 2024            │
│ TO: PT CUSTOMER NAME                │
├─────────────────────────────────────┤
│ NO │ ITEM │ KODE ITEM │ QTY │ ...  │
├────┼──────┼───────────┼─────┼──────┤
│ 1  │ Pin  │ AAP-001   │ 100 │ ...  │
│ 2  │ Bush │ AAP-002   │ 50  │ ...  │
├─────────────────────────────────────┤
│ TOTAL SEBELUM PAJAK: Rp 1,000,000   │
│ PAJAK 10%: Rp 100,000               │
│ TOTAL: Rp 1,100,000                 │
├─────────────────────────────────────┤
│ NOTE:                               │
│ - Delivery: 7 working days          │
│ - Valid: 30 days                    │
└─────────────────────────────────────┘

Template Downloads

Request templates from:

• IT Department
• Sales Department
• Email: it@somagede.co.id

Next Steps

• Standard Format Details
• TMMIN Format Details
• METINDO Format Details
• MARUICHI Format Details