LALCO Report

LALCO Report

1 · Sources & data checks

2 · Summary (live calculation for this date)

Today = change vs the previous stored date. ▲ gap = the previous working day is missing, so "Today" covers more than one day.

Beginning of the month Every day Automatic

1&2. Bottom Out — what is done

The steps in the order of the work. Click a step (or its button) to open it. ✔ done · ⚠ to check · ○ not done yet.

Loading…

① Contract lists for the BL_LMS query

Upload an Excel file (contract numbers in the first column) or paste numbers. The query uses these lists for the month above — no more editing the SQL.

② Manual balance rows (source 2)

Fixed rows for the beginning of the month, same columns as the BL_LMS sheet (header on row 1). If a contract is also in the LMS result, the LMS row is used.

③ Balance of the month — BL_LMS (source 1 + source 2) + marks

Replaces the sheets BL_LMS and Balance (A:AL). One table: LMS data, your corrections, the manual marks and the calculated columns.

How to use
  1. Sync from LMS now — runs the BL_LMS query for the report month (lists in ①). Do it every working day. A contract in both sources → LMS row; only in source 2 → manual row.
    What a sync changes: the LMS columns (status, amounts, dates…) are updated from LMS. It never changes your corrections or the marks. Current Staff No is taken from LMS only the first time a contract appears in the month and then kept — if LMS later shows another staff, the cell gets a purple edge (hover to see the LMS value).
  2. Beginning of the month: Copy marks from last month (Got car, Principal mark, Status note — Accident, Not pay last month and “paid” come from ⑥), or Upload Inada's / old Balance file (columns checked before the import; the data of ③ instead of LMS and / or all mark columns at once — header row and old column names are found automatically).
  3. View rows → all contracts. Show filters by source, status, marked or corrected rows; the ☰ on each column filters / finds a contract. Column colours: white data from BL_LMS (can be corrected) · blue header manual marks · grey calculated (cannot be edited).
  4. To change a value: double-click the cell, type or choose, Enter → yellow = not saved yet. The calculated cells of that row are recalculated at once → light blue. Save changes → orange = saved (hover to see the LMS value / who / when). Undo saved change puts the LMS value back (or removes the mark).
  5. Update many contracts by Excel: choose the column (Current Staff No, Accident, Closed last month, …) → Download template (2 columns: Contract No + that column) → fill it → Upload. For a mark, a blank cell removes the mark; for data, a blank cell is skipped.
  6. Download Excel — the Balance with changed cells in orange and a sheet listing every change.

Rules (same as the sheet): USD = contract amount ÷ month rate (LMS_CURRENCY). Principal mark (AG) is kept from the previous day: your mark (typed / uploaded) stays · when LMS status changes to Closed / Refinance → ×× and Date of change = that report date (blank for Refinance) (status) · when the last payment date moves later (term extended, e.g. 2026-10-05 → 2027-04-05) → ○ and Date of change = that report date (extended) · a contract not marked this month keeps last month's mark (○ stays ○) (kept) · otherwise × (×× when LMS Closed / Refinance) (rule). To change: type / upload another mark (it stays until the next status change to Closed / Refinance). Balance T = Now amount − principal this month if mark × / ××, otherwise Now amount. Now status: mark ×× or T = 0 → Closed · Accident ○ and not “paid” → Accident · Not pay last month ○ and not “paid” → Not pay last month · otherwise Normal · Now status (override) wins. Status wrong in LMS → correct LMS status.

Update by Excel

③✓ Check the Balance with Inada's file

Upload Inada's original file 月初残高・落込・完済 (Balance sheet.xlsx) of this month. Every contract of KHAM (= this system, ③) is compared with Inada on the columns of row 4 of your sheet Balance (Inada D, E, F, AD, G (AW when filled), T, U, W, Y, Z, AA, AB, AC, AE, AF, AG, J, AI–AL, AO, AP), and each difference gets its reason. Then decide: Keep KHAM (KHAM value is right) or Use Inada (Inada's value is written into ③ as a correction / mark — orange there; Clear decision takes it out again).

Selected: 0

④ Irregular repayment

How it is made (replaces the sheet "Irregular repayment" — automatic, nothing to copy)
  1. Contracts of the EARLY_CLOSED list in ① (= the Early Closed of last month, see ⑤) and of the ADD_CASE list in ①.
  2. irregular_amount = 1st amount (Now amount USD of the Balance) − principal of this month (One-time + Installment), not below 0.
  3. Removed: Early Closed with irregular_amount = 0 · ADD CASE that are Active in LMS now (they are counted in the Balance as normal contracts). An ADD CASE is never removed for its amount: if the Balance has no Now amount, the Loan amount USD is used (an ADD CASE has not started paying); if that is also empty it is kept and flagged in red.
  4. Recalculated every time the Balance changes (sync, upload, save), so an ADD CASE disappears on the day it becomes Active. The Balance column ADD case (sheet X) = in this list.

Fixed compared with the sheet: “Contract Status” (K) looked up row 8 for every row (B$8), so every contract showed the status of the first one — now each contract has its own status.

Check with Inada

⑤ Early Closed (list for next month)

Replaces the sheet "Early Closed": contracts of this month's Balance with LMS status Closed, Closed type Early Closed, not in ④, and last payment date (Installment) / original last payment date (One-time) after the due date.

⑥ Not pay last month

How it is made (replaces the sheet "not pay last month")
  1. 6-1, beginning of the month: fill the lists for the query (upload Excel / paste). NPL_PROJECT → From 10. Payment: the latest file of the SME project list of last month (10. Payment of last report month → This month), frozen at the first save of the month: 1.1 target not paid · 1.2 not target but already paid in the list of 2 or 3 months ago · 1.3 car got (●) not paid. NPL_PREV_UNPAID → From last month's not pay list (contracts not paid last month, old cell B1). NPL_SEIZED_2023 = rule 6 (fixed list, copy last month).
  2. 6-1 also: No1 auto (Seized Car before Last month, not paid yet) → From 10. Payment: ● in the SME list of 2 months ago and no “already paid” in the lists of 2 months ago and last month (files of last report month, frozen at the first save; or upload Contract No · Date seized · Place · Remark) — not used by the query, but done at the same time.
  3. 6-2, beginning of the month: the paid conditions — Special Introduction (COO), Last month (Already paid in This month) = From 10. Payment (list “Last month” of this report month) or upload last month's CC SME project list, SOA = automatic.
  4. 6-3: upload Inada's list → match / type differs / only in KHAM / only in Inada. Remove these from the list takes the contracts only in KHAM out (list NPL_EXCLUDE: no marks, not counted, not in next month's list).
  5. 6-3 upload shows the column review (like the CC file). Below the tables: Use Inada's data for all… (after you check and confirm) or per contract ⇐ Use Inada / ✔ Keep KHAM — Inada's type and paid are used, the marks of ③ follow; Back to KHAM's data undoes it.
  6. Sync from LMS runs your query with the dates of the report month (:prev_due_date, :prev_month_end, :prev2_month_end) and the lists → columns B:H.
  7. paid = 1 (column K), in the order of 6-2: Special Introduction · CC list “●” (got car 1st time) unless the car is in No1 auto (seized before last month, not paid yet) · CC list “already paid” · SOA (LMS Closed / Refinance). The CC list is matched on Contract No and NCN.
  8. The result is written to the Balance marks: Accident ○, Not pay last month ○, Not-pay case paid ○ (→ Now status). A mark typed or uploaded by a person is never changed. Rebuilt after every LMS sync, upload and list change.

Checked with the September sheet: the paid rule gives the same 864 “paid” as column K for all 3,253 contracts (with the seized list of the sheet).

6-1 · Lists for the LMS query (beginning of the month)

Then run the query (columns B:H) — dates and lists of the report month.

6-2 · Paid conditions (column K “paid”)

6-3 · Reconcile with Inada

Upload Inada's original file (“Inada's Not pay last month YYYY-MM-DD.xlsx”, sheet not pay last month: O = Contract No, R filled = Not pay last month, T filled = Accident, S = paid) — or the template (Contract No · Collection Type). Upload it every day: each upload is kept and compared with the one before. Contracts only in KHAM can be removed from the list; removed contracts are kept in NPL_EXCLUDE (undo there).

Result

⑦ Individual (sheet Individual A:EZ)

One row per staff (Stf, staff with a Title group) by Current Staff No: This month Start amount (③ Balance, not Accident) · Increase: 1. This month contracted (3. Execution → Individual), 2. Paid last month (⑥) · Decrease: 1. regular repayment (③ + ④ ADD CASE), 2. not pay last month (⑥), 3. Refinance (③ + 3. Execution), 4. Paid off (④ Early Closed) · Now amount (③ + 3. Execution + Early Closed) · SME bottom out = Now amount − Start amount.

⑧ BO_MAX / Balance_MAX (sheets BO_MAX and Balance_MAX)

This month: the value of the current report date is calculated from ⑦ Individual (Sales row 7) — Balance = EA / EB / EF, Only SME = EM / EN / EP, bottom out = Balance − Start amount (V / W, AH / AI). When the report date has passed the value is kept and not calculated again. MAX / case MAX / two months before / last month = values kept for those months (upload the old 1&2 workbook once, or the template — under Show below). Differences: same day index (or the last day when that month has fewer days); Balance vs MAX uses the month-end rule of column AH:AJ.

Download template gives the selected series / table (All series + All months = everything); upload it, or the old 1&2 workbook, to replace the values. Double-click case / amount / Only Installment (yellow) to type a value; empty = no value for that day. SME bottom out of a month with a start amount = SME Balance − start amount (like BO_MAX X = Balance_MAX AD − Individual V7) by default; a value typed or uploaded for the bottom out (or for today's formula row) is kept as an adjustment — empty it to go back to the calculation. day no is calculated like Balance_MAX AA (holiday / Sunday empty, Saturday = Friday's no.) and is used to find the day to compare — change it only when your old sheet used another no.

Replaces the sheet M_LMS_old of 3. Execution (the separate LMS sheet is not needed — Customer Introduction, Approach and Reason For Refinance are in the query). A:AO come from LMS (you can correct them), AQ:BD are calculated like the sheet, AZ Date Report is the date you choose before the sync.

Every day Report

3. Execution — what is done

The steps of the day in order. Click a step (or Open). ✔ done · ⚠ to check · ○ not done yet.

Loading…

1 · Queries & ADD CASE list

Last sync — status changes

2 · Rows (M_LMS_old A:BD)

How to correct data
  • On screen: double-click a cell of A:AO or AZ (Date Report), type, then Save changes. Yellow = not saved, orange = corrected (kept after every new sync; the LMS value is in the tooltip). Light blue = recalculated by your last save.
  • With Excel: in 1 · Queries & ADD CASE list → Upload Excel… (Normal Execution or ADD CASE): each row of the file replaces that contract in the system (new contracts are added, the others are not changed). Use the Template, or Download Excel, correct it and upload it ([calc] columns are ignored).
  • What a sync changes: a new contract is recorded with all its LMS data. After that its data is kept — a sync only updates Status C. An ADD CASE that becomes Active / Closed / Refinance in LMS gets all its data from LMS (Date Report kept).
  • If LMS would overwrite one of your corrections, the sync does not do it: the cell is red and the contract is listed under “Manual vs LMS — decide” (keep your value or use LMS).
  • LMS data that changed later for a contract recorded before is not applied — Show → “LMS data changed (kept)” (purple edge, tooltip = LMS value); you can take it with “Take LMS data…”.

3 · Check the Execution with Inada's file

Before the import you see the columns of the file: every item (Inada's columns, his category flags 新規 / 休眠 / 貸増 AM / AO / AU, the LMS data CF:DO = the data he calculates from) is mapped by its header; the screen says if the format changed since the last upload and you can choose another column. The LMS data is compared with KHAM A:AO and the category with KHAM AR (table By category). Upload Inada's original file Inada's Execution …xlsx (sheet Cont, header on row 2, contract = Ringi No. AH) as it is. Every counted contract of KHAM (= this system, 2 · Rows) is compared with Inada on all the columns both have (customer, staff N/P/R, HO/BR, currency, amount, commission, offset, USD amounts X–AA, request route, installment / one-time, times, special, withdrawal date, case no) and each difference gets its reason. Then decide: Keep KHAM (KHAM value is right) or Use Inada (Inada's value is written into 2 · Rows as a correction — orange there; Clear decision takes it out again). The old template (4 columns) can still be uploaded.

Selected: 0

Removed from the Execution (EX_EXCLUDE)

4 · Totals

For next month (1&2 Bottom Out)

5 · Individual (sheet Individual A:DN)

One row per staff (Stf, staff with a Title group). Sales staff: Sale ID + Double Count ID (Case 1 / 0.5); CC staff: CC ID (count, Net Amount USD). Today = Date Report (AZ) = the report date. Sales plans from the Google Sheets A_Credit_Salesplan (GS_CREDIT_PLAN) and A_Frappe_Salesplan (GS_FRAPPE_PLAN).

6 · MAX (sheet MAX)

Execution of each day (Date Report AZ, Net Amount USD AU) for New / Dor / Inc (Product SME Car + Customer Type AR), New Business (Product New Business Loan) and All (= the four), for Total, HO and BR (HQ BR, BB). Case / amount total = month to date; % Amount = installment amount ÷ amount total. This month: the current report date is calculated from 2 · Rows (counted contracts) automatically after each sync / upload (Normal, ADD CASE) / correction — days already saved are not changed; Recalculate this month only to take every day of the month again; when the month has passed it is calculated a last time and kept. MAX / two months before / last month = kept months (load the old workbook once, or the template). Differences = month to date vs the same day no; the last business day (or a day ≥ the reference month's last day) is compared with that month's last day.

Download template gives the selected section / category / table (All = everything); upload it, or the old workbook, to replace the values. Double-click a yellow cell to type; case total / amount total / % are calculated when left empty. This month is calculated (not read from files); All of a month with New / Dor / Inc = the sum of the four (change those instead). day no is calculated like column AL (holiday / Sunday empty, Saturday = Friday's no.).

7 · Ind_MAX — each staff's best month (sheet Ind_MAX)

How it works (replaces the sheet Ind_MAX — no more copying last month's report)
  • MAX before this month (old AC:AO) = each staff's best month before this month: New / Dor / Inc (month · case · amount), Execution MAX month (Full amount), Net increase. Automatic: last month's “MAX up to now” at its month end. Only once (or to correct): upload the template or your old 3. Execution workbook — last month's report → its MAX up to now (G:S); this month's report → its MAX before this month (AC:AO). You can also double-click a cell of the blue columns and save.
  • This month (T:Z) = Individual of the report date: New W/X, Dor Y/Z, Inc AA/AB, execution AF (Full amount).
  • MAX up to now (G:S): this month higher than before → this month (green, month = this month), else before. Case and amount are compared separately (the month follows the case).
  • Individual MAX total (Summary K6 / K11 / K16 / K26) = Individual row 5 (Sales) − ROUNDUP(Σ MAX up to now of the Sales staff without a resigned date ÷ total workdays × elapsed workdays, Saturday 0.5). Total = AE / AF (contracted, Full amount) − the three rounded New + Dor + Inc, like the sheet.
MAX before this month
How it works

Early payment = 1&2 Bottom Out ④ Irregular repayment of the month (rows without ADD CASE and Accident, close_type Early Closed = ending payment date after the 5th), by Current Staff No; amount = irregular amount. Delay history from the Google Sheet "All DDT+Installment check (delay after 5th)" sheet DDT (Y payment group, Z Is delay). Current-month early closed = 1&2 Bottom Out ⑤ Early Closed.

Every day Check Report

4&5. Early Payment — what is done

The steps in order. Click a step (or Open). ✔ done · ⚠ to check · ○ not done yet · • for information. Click an underlined number to see its rows.

Loading…

1 · Delay history (Google Sheet DDT)

2 · Summary

3 · Irregular repayment (from 1&2 Bottom Out ④) + delay

4 · Closed this month (Early) — 1&2 Bottom Out ⑤ Early Closed + 3. Execution Early_Closed

This list is next month's 1&2. Bottom Out ① list: the Bottom Out rows → EARLY_CLOSED "(3)(4) Closed but last month early close", the 3. Execution rows (executed and closed in the same month) → CLOSED_IN_MONTH "(9)". In the next month click "From last month's Early Closed" / "From last month's Execution (Early_Closed)".

5 · Individual (sheet Individual A:AK)

6 · MAX (sheet MAX)

How it works

Customers who paid late while they have a special condition (DDT, special interest, LAK) — the condition must be changed (MOU / update schedule / refinance to USD). Target list of the month from the DDT sheet of "All DDT+Installment check" (synced in 4&5. Early Payment), status from LMS every day.

Beginning of the month Every day Report

6. Delay history — what is done

The steps in order. Click a step (or Open). ✔ done · ⚠ to check · ○ not done yet · • for information. Click an underlined number to see its rows.

Loading…

1 · Target list of the month — step by step

Each row is one step of the selection, in order. Data source: sync / upload / paste what the step needs. Before → Removed → Remaining: how many contracts each step takes out; click View / Excel to check them. Steps 0–3 are counted as soon as the DDT sheet is synced; Build (step 3b) saves the Remaining as the list of the month (typed columns are kept when it is rebuilt); steps 4–7 work on that list.

2 · Update status (every day)

Every day: Update from LMS takes the LMS condition of the target contracts of the month (DDT / special interest / LAK changed → Done). The DDT sheet is not needed every day — it makes the target list at the beginning of the month (1 Target list, sync there when you need it).

Manual change → Done (CD)

A contract with a manual change is Done (CE) even when LMS still shows DDT / special interest / LAK. Type it in the List below (columns CD + Reason), or paste / upload many at once (Contract No · Manual change · Reason).

3 · Summary

3b · Report on target

The text report (copy and send by WhatsApp / Line / email) and the tables of sheet "Report on target", for the report date above. The List download also has this report as a second sheet.

Note texts and unit % (saved for every month)

Placeholders: {pending1} {pending2} {target1} {target2} = cases of delay 1 time / 2 times or more · {mou2} = pending 2 times or more with "MOU but delay 2" (CJ).

4 · List (A:AV + BV:CK)

Double-click the yellow columns to type (AQ:AV Sales follow-up, CD Manual change = Signed MOU / A at 10th / S at 5th / Other → Done, with its Reason; CF Done date). CF is set automatically to the report date on which a contract becomes Done.

5 · Individual (sheet Individual)

How it works

DDT contracts whose last payment date is the 5th of this month (This month DDT) or of next month (Next month DDT) — did the customer sign a new contract (Refinance) or extend the schedule? Contracted (P) = 1 when LMS status is Refinance or the last payment date is no longer the DDT date; you can set it by hand with a reason. Call result / plan date / reason come from the sales team's Google Sheet.

Beginning of the month Every day Report Check

7. DDT — what is done

The steps in order. Click a step (or Open). ✔ done · ⚠ to check · ○ not done yet · • for information. Click an underlined number to see its rows.

Loading…

1 · Contract lists for the queries

2 · Sources of the month

3 · Summary

4 · List

Bulk:

Double-click the yellow columns: Contracted (empty = automatic, 1 / 0 = by hand) with its Reason, Staff No (person in charge), and the plan columns (when the sales sheet is missing a contract).

5 · Individual (sheet Individual)

6 · Dept / Unit

7 · MAX

One row per working day: rate = contracted amount ÷ total amount (whole list), cases / amount not contracted yet. The current report date is calculated; a day already passed keeps its value. Differences = this month − the same working day of the MAX month / two months before / last month (the last day when that month has fewer days). Double-click rate / cases / amount to type a value.

8 · Check with Inada's DDT files

Upload Inada's original files DDT1005 (This month DDT, due the 5th of the report month) and DDT1105 (Next month DDT) — the list is found from the header "10/5 DDT" / the sheet name. Every contract of KHAM (= this system, 4 List) is compared with Inada's row: in the list or not, staff, contracted, LMS status, amount / USD rate, call result / date will do, accident / got car. Each difference has its reason; click a number to see the contracts. Use Inada writes Inada's value as a typed value of the List (Staff No N, Contracted P, Call result S, Date will do U).

Loading…

Contract lists (paste instead of the file)

How it works

Source: 1&2. Bottom Out → ③ Balance of the month and its marks (no new upload needed). Monthly interest income = contracts without Accident and Not pay last month, amount = Interest this month (Balance R). Received (KHAM) = Inada's rule: paid this month → Interest this month · Not pay last month / Accident paid this month → only 1 month of interest (loan USD × rate) · ADD CASE of last month not counted. Until 10. Payment Rate is in the system, "paid this month" only comes from the marks of ③ (Paid this month AI, Not-pay case paid AJ), so the reported value is Inada's month-to-date figure (old MAX column X) typed below — switch to "System calculation" when Payment Rate is ready.

Every day Report

8. Interest Received — what is done

The steps in order. Click a step (or Open). ✔ done · ⚠ to check · ○ not done yet · • for information. Click an underlined number to see its rows.

Loading…

1 · Summary

2 · Contracts (sheet From_BO)

3 · Individual (sheet Individual V:AA)

4 · MAX (sheet MAX)

Double-click month to date (yellow) to type Inada's figure of a day; today = month to date − the previous working day that has a value (the old sheet subtracted an empty day and showed the whole month). Day no. comes from the calendar (holiday / Sunday empty, Saturday = Friday's no.).

How it works

"Tax" = each staff member's monthly case budget per product (New / Dor / Inc) by title. Paid tax = result ≥ budget · Not pay tax = result ≥ 2 · Maethong = result ≥ 1 · Puak = result < 1. Results this month / today = 3. Execution → Individual. Last month = Inada's 過去実績 (upload) or 3. Execution of last month. Population = staff without retirement date and with a budget for the product; last month target = also hired before the 1st, not removed.

Every day Report Every month

9. Puak — what is done

The steps in order. Click a step (or Open). ✔ done · ⚠ to check · ○ not done yet · • for information. Click an underlined number to see its rows.

Loading…

1 · Sources

2 · Summary

3 · Individual (sheet Individual)

4 · Budget by month (sheet M_Rule)

One budget per month, uploaded every month. Each month is checked with its own budget: last month's result (paid tax / Maethong / Puak of last month) with last month's budget, this month's result with this month's budget — like the old Individual Z:AB (last month) and AD:AF (this month).

Budget columns — a column you add is kept for every month (template, upload, grid and copy). Values are kept per month and title.

Tip: drag the bottom-right corner of a table (◢) to make it taller; the size is kept for next time (double-click the corner area to reset).

How it works

Staff = staff list (Stf) with a Title group (Sales + CC). Start = hired before the 1st · Hiring = hired this month · Resignation = retirement date this month · Resignation rate = resigned ÷ (start + hired). Attendance = file of the attendance machine: Absence = no punch on the report date · Come late = first punch at / after the late time · Attendance rate = days with an AM punch ÷ working days (from the hire date, before the retirement date, only days with a timecard; approved leave days are not counted).

Every day Report

12. HR — what is done

The steps in order. Click a step (or Open). ✔ done · ⚠ to check · ○ not done yet · • for information. Click an underlined number to see its rows.

Loading…

1 · Sources

2 · Summary

3 · Individual (sheet Individual)

How it works

Collection list of the month: the 10. Payment lists This month / Last month / 2 months ago (target rows) and the 5 SOA lists of the report date (OA · No.1 · 訴訟 · 不動産 · 4Products), one row per contract — a contract in several lists is kept once with the priority This month → Last month → 2 months ago → SOA (the Excel has a sheet per type with all its rows). Collected = the CCC file (10. Payment "already paid" or seized car) or the SOA list (status paid) — not the result keyed by the collection staff.

Plan = the Google Sheet "Plan to Visit" (Date for Plan · Contract No · Collection person · Customer Name · Reason to visit · Comments · Collection type · Now amount), kept in the system: a sync replaces the plan dates of the sheet from the report date on; an earlier plan date is added once and then kept; a plan date deleted from the sheet stays as saved. Visits = Frappe tabActivity_of_collection (CURDATE() = the report date): the month up to the report date, every day kept. Both are also synced by the automatic sync (Today).

Collection person = the staff no. in the name (AEK3239 / "3239 - AEK" → 3239), name from the staff list. Visited · in the plan = a visit of a contract planned for that day; not in the plan = visited without a plan; Plan · not visited. Click a number to see its contracts.

How it works

Make the list of the month (step 1): A. From LMS — ① paste / upload the contract numbers (list DOR) → ② Sync from LMS (queries/lms/dor_list.sql: NCN, Customer No, Case No, close date, product, contract date, currency, lease amount, method, won staff AR, province / district / village / district ID; Registration and the statuses stay empty) → ③ the assigned staff BI (upload Contract No + Staff No — Inada's own rule; without it the staff of the list before / last month is kept) → ④ HC / Visited from Frappe (tabSME_Approach_list: HC = called this month, Visited = Yes / WA up to the report date). Or B. Inada's Excel (his file Dormant, as before).

The Dormant list of the month (Inada's file "Dormant", sheet Dormant — later an LMS query) has one row per dormant contract with the staff that won the customer (AR) and the staff it is assigned to (BI). The system sets the marks as of the report date: Self (BN) = BI is AR · Old sales unit (BM) = the unit of AR in the staff list = the unit of BI · Contracted (BR) = the Customer No (B) has a contract in 3. Execution up to the report date (Case No 79976-1 → customer 79976), with its Sales / CC / W count ID (○ = BI). Individual (every staff of the staff list of the report date): Stock dormant Target E = assigned to the staff · (Allocation) H = not Self, contracted I = BM blank and contracted · (Self) K = Self, contracted L = BM ● and contracted · F = I + L · rates G = F ÷ E (0 when E = 0), J = I ÷ H and M = L ÷ K (1 when the target is 0). Amount = Lease amount (T, contract currency) ÷ the rate of the report month (Masters → currency); USD contracts = T; without T or a rate the Amount USD (AS) of the file. Every target / contracted has its case and USD amount. Results data ④: Dormant contract target = H · Contracted from assigned dormant = I · Assigned dormant based on Area marketing = K — case and USD.

Every month / day Report

Dormant — what is done

Loading…

1 · Prepare the Dormant list

rule of Inada-san and Morikawa-san — the result becomes the contract numbers of 2 · Dormant list
How the list is prepared
  1. Source: every contract of last month's Dormant list + the contracts Closed in last month (③ Balance of last month, Now status Closed).
  2. Remove the duplicates and the existing customers (customers with an Active contract in LMS): 1) same customer ID · 2) same full name · 3) same phone (main_contact_no, last 8 digits) · 4) the customer is the guarantor of an active contract (same full name) · 5) the guarantor phone of an active contract = the customer's phone (last 8 digits). Inside the list, one contract per customer is kept (the latest close date). A contract not in LMS or still Active is removed too.
  3. Delete the contracts in: 1) the No1 auto list (Google Sheet "List car" column O = NCN, compared with the NCN of the contract in LMS) · 2) the HC list with rank_update N1 / N2 / Z1 / Z2 / Z3 (Frappe tabSME_Approach_list, approach_id) · 3) Delay F_after_1_month (3 or more month-over delays, DDT sheet of 6. Delay history).
  4. Check the prepared list (a draft): every contract with the reason, and the comparison with the Dormant list in use of the month (added / dropped). Nothing is changed yet.
  5. Use as the Dormant list (after the check): the kept contracts replace list DOR of the month and the Dormant list is synced from LMS (customer, car, guarantor …).

① Sources

Loading…

② Prepare (a draft to check)

Preparing makes a draft only: you can check it below (③). The Dormant list in use (2 · Dormant list) does not change. It changes only when you click Use as the Dormant list in ④.

2 · Dormant list

WayStepNowActions
A. From LMS recommended
contract numbers → LMS (customer, case, close date, product, amount, area, won staff) · assigned staff (upload) · HC / Visited (Frappe)
① Contract numbers of the month (list DOR)
upload (1 column) or paste — Contract No. (Inada's column D)
—
② Sync from LMS
makes the Dormant list of the month (replaces it)
③ Assigned staff (BI) — Inada's own rule
2 columns: Contract No · Staff No — used at the sync and applied to the list at once; a contract without it keeps the staff of the list before / last month
—
④ HC (BP) / Visited (BQ) from Frappe
tabSME_Approach_list — read at every sync; update any time
B. Inada's Excel
his file "Dormant" (sheet Dormant) or the template
Upload the file — it replaces the list of the month

The contracts of the list

3 · Individual

How it works

Upload Inada's SOA file (sheets OA, No.1, 訴訟, 不動産 — the sheets in the file replace the same lists of the month). Target = the rows of the category of each list: OA (X = OA) · No.1 (AG = NO1 Auto) · 訴訟 (HU = LAWYER) · 不動産 (X = Real Estate); amount = OA AH · No.1 AZ · 訴訟 EX · 不動産 DR (USD). Collected = status OA BE "Already paid" · No.1 BC / 訴訟 HF / 不動産 EA "Paid". The unit written in the list (OA R, No.1 R, 訴訟 T, 不動産 N) → unit no. with the staff list of the report date (not case sensitive; not found → 0, 不動産 → 1 as Inada's sheet). Unit (sheet Unit): one row per unit of the staff list, In Charge ID = the first staff of the unit · E/F target · G/H collected · I/J outstanding = target − collected. Results data ③: the In Charge ID gets the numbers of his unit(s).

4Products: Google Sheet ⑤-4Product new, tab 4Prodcuk New — target = the rows with X = "4PS" · contract D (the Cash LAO contracts have 3–4 digits) · unit J (typed), else I · amount AB ຕົ້ນທືນທີ່ຍັງຄ້າງ (USD) · collected = BJ Result OF CC★ "already paid" (unit not found → 0). In the Chairman report SOA回収 it is in the oldest part, like No.1 / 訴訟 / 不動産.

Each sync is kept for its report date (Sync from Google Sheets and an uploaded file → the report date of this page; the automatic sync → the current report date). A report date shows, for each list, the last sync on or before it; a correction in 1 Lists belongs to its report date only (when the date has no sync of its own, the list it shows is copied for it first); a new sync of the date replaces its values and corrections; Remove removes only the lists shown.

Every day Report

SOA — lists of the month

Loading…

1 · Lists

Double-click Unit (list), Amount USD, Status, Collected or Target (1 / 0) to correct it — for this report date only (the other dates keep their own values); a new sync of this date replaces its corrections; empty = back to the list value.

2 · Unit

3 · MAX — SOA collected month to date

Loading…

Case / amount total = SOA collected from the 1st to that day (Total "Collected" of the Overview, the 4 lists). The report date is calculated from the lists of the month; a passed day keeps its value. Differences = this month − the month on the same day no. (Saturday = Friday's no.; a shorter month → its last day). Type a value in Case total / Amount total to correct a day (empty both = removed); Inada's uploaded values win over the calculation.

4 · Chairman report — 管理 · SOA回収

The block SOA回収 of the Chairman report (Inada's Summary1 rows 431–442), made from the 4 lists of this month + the two-months-ago list of 10. Payment. Collected (実績) = status paid · 残債 = not paid · 本日 = month to date − the values of the last report date (kept by the system every day, no pasting). OA cases are split by the year of "The month that is left" (OA AO); No.1, 訴訟 and 不動産 are all in the oldest part (Inada: "2025年以降のOA以外全部"), so the parts always add up to the 合計.

Loading…

Increase — 貸増 customer list

How it works

The list of the month (made once a month, like the ③ Balance) — one row per contract with Max Loan, Now amount, Available (= Max Loan − Now amount) and the delay rank. Two ways:

  • A. From LMS: paste or upload the contract numbers (list INC — contract no, NCN or prospect id), then Sync from LMS (query queries/lms/inc_list.sql): Note No · Customer No · customer name · Max Loan = the sum of the asset valuations of the prospect. From the ③ Balance of the month: N = this month start amount, T = Now amount, the person in charge. Rank = first letter of “Delay” of the DDT sheet (6. Delay history). × = not in the ③ Balance (e.g. a prospect still pending) — not counted. Now amount, Available and rank follow the latest ③ Balance / DDT sheet every time the list is shown.
  • B. Inada's Excel: his workbook as it is (only the sheet Increase is read) or the template — the values are kept as in his file.

Up to the report date the system adds フォロー = a Sahed Sales&cc row of the month for the customer (cutlb / visit) or Inada's follow date, 訪問 = a Sahed visit (Yes / WA) or his visit date, 実績 = a contract of 3. Execution of the month that refinances it (Pre Contract No / Pre NCN) or his "contracted"; the 部 = the person in charge now (③ Balance) in the staff list. Sahed rows are matched by contract no when the Frappe query gives one, else by the customer name.

→ Chairman report (7)貸増状況: 遅延案件 (Available ≥ 5,000, rank B / C / F / N) · 自動貸付可能 (≥ 5,000, S / A) · その他 (< 5,000, rank ≠ ×) — 対象 / フォロー / 訪問 / 実績 / 取込率 = ROUNDUP(実績 − 対象 × 20%).

1 · The list of the month

WayStepsContractsActions
A. From LMS recommended
contract numbers → LMS (customer, Max Loan) + ③ Balance (amounts) + DDT sheet (rank)
① Get the contract numbers: Find (Available ≥ 1,000) — like Inada: Max Loan − this month start amount ≥ 1,000 USD — or upload / paste them (list INC)
② Click Sync from LMS
—
B. Inada's Excel
his workbook (sheet Increase) or the template
Upload the file — it replaces the list of the month

2 · (7)貸増状況 — as it goes into the Chairman report

3 · The contracts of the list

How it works

Upload two files every month: Sales Score Report (.csv / .xlsx, one row per staff per day — the file replaces its days) and Sales&cc data (.xlsx, sheets cutlb and visit — each sheet replaces that sheet of the month). Sahed (Quantity) Win / Lose = days with Win / Lose in AU "Personal" · Sahed (Quality) = BV (to the report date). Number of calls: Target = workdays from the 1st to the report date (Mon–Fri 1, Sat 0.5, no Sunday / holiday) × calls per day of the Title group (CC 200 · Sales 150 · TL 100 · UL 50); Actual = Σ BK "call". Prospect cases got = cutlb rows / Σ usd_loan_amount by staff_no · Visited = visit rows by Staff No with "Visit or not" Yes (real visit) / WA (WhatsApp) · to Current (type Inc) / Dormant (type Dor). Organizational: Win / Lose = the leader's own days in the column of his level (Team AR / BX · Mini Unit AO / BZ · Unit AL / CB · Section AI / CD · Department AF / CF · General Department AC / CH; Branch empty); the other items = sums of the staff. The same numbers go to Results data ⑤.

Every month Report Chairman report

Sahed — overview

Loading…

1 · Files of the month

2 · Individual

3 · Organizational

4 · SP 5 items — Chairman report (5)5つの縁とSP5項目

How it works

Click Sync from Frappe every day (the lists of the month are replaced; the targets are the state at the sync). Per 部 of the staff list of the report date and per item ①5つの縁 · ②X見込み · ③Z見込み · ④退職者SP · ⑤村長 · ⑥Facebook: 対象件数 = the target lists (queries/frappe/sp5_target_*.sql — one row = one SP / village; ⑥ = Σ followers of the staff) · 紹介件数 新規 = BO and Plan cases made in the month up to the report date (sp5_ref_new.sql) · 紹介件数 SP = sales partners registered in the month up to the report date (sp5_ref_sp.sql), by their approach (approach_ja) · 実績 = 3. Execution, counted contracts of the month up to the report date whose Approach type has group_type_ja = the item (Masters → Approach types): Sale ID gets the case (0.5 with a Double Count) and the Double Count ID 0.5. Click a number to see its rows. The table goes to the Chairman report (5) (Recalculate there).

(5)5つの縁とSP5項目 by 部

How it works

3 collection lists of the report month (dues of this month / last month / 2 months ago). Collection rate = (already paid + seized car) ÷ target (Target ●) · Remain = target not paid and not seized · Will pay = remain with the promised date in tomorrow / this week / this month.

Every day Report History

10. Payment Rate — what is done

The steps in order. Click a step (or Open). ✔ done · ⚠ to check · ○ not done yet · • for information. Click an underlined number to see its rows.

Loading…

1 · Collection lists

Upload the file from Collection CC as it is received (e.g. SME project list) — before import you see the columns of the file with their data and can choose the column of each item; the choice is remembered for the next day.

2 · Summary

3 · Individual (sheet Individual)

4 · 6 exception items

Not to count in the 6 items: 0 contracts

5 · MAX

One row per working day: collection rate (amount) and unpaid cases / amount of the whole list. The current report date is calculated from the list; a day already passed keeps its value. Differences = this month − the same working day of the other month (the last day when that month has fewer days); unpaid: + = fewer unpaid.

Car seize — number 1 auto

Every seizure of a car, kept by the system from the collection lists: a car is seized when its contract has the ● (number 1 auto) and is not "already paid" (Result OF CC★) in the newest list that holds the contract (the lists of the 3 newest due months). It ends when the list says already paid (the customer paid, LALCO returned the car), when the ● is removed without payment, or when the contract leaves the lists. Every upload of a list opens / closes the seizures; the person in charge (No / Name of the list), Collection CC and Collection are kept as they were on that day.

Chairman report — 実績報告(営業・管理・人事)

The daily Word report (Inada → Arosha → 大島会長). Section 1.全社実績 is filled from the modules of the report date (the saved report when the date is saved, else live). A cell the system cannot calculate yet is typed here (kept for that date; a label like “4月遅延” stays until changed). Download Word = Inada's file with these values (minus numbers in red). The layout is Inada's of 20 Oct 2026: 【本日の勝敗】 next to 人事 and 2.その他実績 (1)–(8) — their tables are below.

【本日の勝敗】 and 2.その他実績 (Inada's layout of 20 Oct 2026)

One row per 部 (or 係). The organisation follows the staff list of the date (GS_STAFF): 統括 = the groups with a 統括部長 / 部長, 部 = their departments — when the staff list changes (e.g. 統括 TAY / FERN / DITH → TAY / NO / LING) the rows change with it. Calculated automatically when you open the date: the system makes 7 tables with Inada's rules (his workbook "SME sales result": Summary1 本日の勝敗, sheet 守川) — 本日の勝敗 (both), (1) 500計画, (3) 新規事業未実行, (4) 5つの縁, (6)① 休眠獲得率, (8) 本店研修・不良係 (the 係 of (8) = Inada's choice: the 係 of his last Word / the last saved (8)). (2) マンツーマン, (5) 5つの縁とSP5項目, (6)② 休眠訪問 and (7) 貸増 need data the system does not have yet: import Inada's Word of the day or type them (the rows = 統括 / 部 of the staff list). A Word of the 19 Oct layout (with 事業部) is read without that column. Saved values (Inada / typed / system kept) come first; the cells that differ from the system are framed red (hover: the system's value). A table without saved values uses the system's values in the Word. 合計 = his / typed / system total, else the sum of the number columns.

Daily Top10 — 係長 + マンツーマン

Inada's sheet Daily Top10: one row per 係 with its unit leader and the man-to-man pair (MM) = the member the leader coaches. The pairs are a set that applies from its date until a newer set (upload Inada's workbook — sheet Daily Top10, H unit no + AB MM — or the template, or type them below). For each MM: 三商品合計 = New + Dor + Inc cases of the month (3. Execution Individual), 納税件数 = 人頭税 New + Dor + Inc of his rank (0 when retired), 達成率 = 三商品合計 ÷ 納税件数, TM実行 = New Business cases. The Chairman report (2)マンツーマン実績 is made from this (per 部: MM by 達成率 100% / 75% / 50% / 25% / under 25% / 0, and TM実行済み).

(2)マンツーマン実績 — as it goes into the Chairman report

Daily Top10

Pairs (Unit No → MM Staff ID) — edit / add / delete

納税件数 = Tax budget New + Dor + Inc of the MM's rank in the Budget of the month (one table for the system: Masters → Budget, the same as 9. Puak → 4 Budget, sheet M_Rule).

CIB report

CIB report - LALCO

The monthly credit information files for LCIC (mf.lcic.com.la) — LALCO (bank code 415) and N1PS (No.1 pawn shop, bank code 951), the same steps — due by the 5th of the next month (e.g. 4 Nov → the report of 31 Oct). The data of each month is kept in this system (before: database cib_report + the workbook CIB_split + the text file). Follow the steps from top to bottom.

Data of the month

Double-click a cell to correct it (kept, shown in the column "edited")

Months

Results data — 成績表の自動作成元データ項目集計フォーマット

Inada's format "Raw data items format": sheet Individual (one row per staff of the staff list of the report date — the same rows as GS_STAFF in Today) and Organizational (Team, Unit, Section, Department, General Department, Branch — the Individual columns summed by the leader's Staff ID). The numbers are as of the report date (choosing another date of the month calculates that date again: staff list, Execution up to the date, ③ Balance of the date, payments paid up to the date, timecard). Every column says what it is made from; a column the system has no data or rule for yet is empty (grey header in the Excel, red in the sheet "Columns") — write there what it should be.

Win Lose — 勝敗表

How it works

Inada's workbook Win Lose (sheets All / G-dept / Dept / Block / Sec・Br / Unit / Unit3-2-1 / M-unit / Team / Person) made by the system as of the report date: 3. Execution (counted contracts, Date Report = the day) + the staff list of the date + the working days of the month (Monday–Saturday, holidays out; Saturday budget = half).

  1. Points of a day = New + Dormant + Increase cases: the Sale ID gets the Case credit (1, or 0.5 with a Double Count) and the Double Count staff the same 0.5 — counted in the unit / M-unit / Team of each staff.
  2. Unit: budget by the rank of the unit leader (25 → 4 · < 10 → 0 · < 30 → 5 · < 40 → 4 · < 50 → 3) · ○ points ≥ budget, else ×. G-dept / Dept / Block / Sec・Br = the units of the group (budget and points summed) · ○ ≥ budget · △ ≥ half · ×.
  3. Unit 3-2-1 = New cases of SME Car (budget 3 / 2 / 1) · M-unit, Team budget 1 · Person = New cases to the day (cumulative) ≥ 1.
  4. Contracted amount = Net Amount by case (Sale ID + Double Count): Unit budget 30,000 / 25,000 / 20,000 by rank; Person = 9. Puak budget "individual Execution" ÷ 10.
  5. Results: Win / Draw / Lose = days with ○ / △ / × · Carryover = total ÷ budget (rounded down) wins, a draw when the rest ≥ half the budget.
  6. Check with Inada: upload his Win Lose file → every level, day by day (symbol, points, amount) with the reason of a difference. Click a day cell to see its contracts.

Saved daily reports

"Lock & save version" (Today tab) keeps the report of every module for the report date. Choose the date in any tab to see it again (banner 🔒), or download the saved parts here.

Pick a date

Metric lines (old summary)

Pick a date

Compare with another date

Trend

What the system does by itself, in which order, and how you change it.

One budget per month (the old sheet M_Rule): titles by rank with the Poiku / BO budgets and the Tax budget New / Dor / Inc / RE. Used by 9. Puak (tax / Maethong / Puak of each month with its own budget) and Reports → Daily Top10 (納税件数 of the MM = Tax budget New + Dor + Inc of his rank → Chairman report (2)マンツーマン). Upload the template or Inada's workbook (sheet M_Rule), copy another month, or type — the same table as 9. Puak → 4 Budget.

Import past daily values

Excel file with columns report_date, metric_code, dim_key, cases, amount, ratio. Used to load history from the current Google Sheets so comparisons start with real data.

How it works

admin = everything + Users · editor = sync, upload and change data · viewer = read only (every change is refused). A user is never deleted (the logs keep his name): turn him off and he cannot log in any more (an open session ends at his next click). The last active admin cannot be turned off or changed; nobody can change his own role or turn himself off. Passwords: at least 8 characters with letters and numbers; an admin can reset any password; every user changes his own with his name at the top right. Every change is in Runs & logs → Change log.

How it works

1 Files: the Tokyo salary file of the month (main) and the "New staff" file(s) — sheets iBank用, October_in&pena…, 間接…, 紹介新規紹介採用 —, your TC file (sheets "time (…)") and the Word template "Salary Pay Slip.docx" (kept for the next months). 2 Settings: the transfer date printed on the slip, the working days ("28 / 24"), which time sheets of the TC file are read (in order), the Emp No without a slip. 3 Data: one row per staff with every value of the slip, worked out like the sheet PaySlip of your workbook (iBank用 → amounts, October_in&pena → SME details, 間接 / 紹介 → indirect staff, TC → Service record). Double-click a value to change it (yellow); empty = back to the calculated value. 4 Generate: Word (one file and one per staff), PDF (one file and one per staff) and the images EmpNo_Nickname - page.png in zip files. The PDF is made by Microsoft Word of this PC (or LibreOffice). Only admins see this tab.