מודל תקבולים צפויים וגיול חובות לקוחות

למה מנהלי הכספים זקוקים למודל תקבולים צפויים? הסיבה העיקרית היא כי אנחנו רוצים להיות עם האצבע על הדופק ולדעת בכל יום מה עתיד להיכנס לחברה מהלקוחות ומה עתיד לצאת. ככה נוכל להיערך מראש ובמידת הצורך לקבל החלטות יחד עם המנכ”ל כדי להישאר תמיד עם תזרים חיובי

המודל הזה מתבסס על שני פרמטרים: 

1> חשבוניות שנרשמו בספרים. 

2> תקופת הסדר התשלום עם כל לקוח ולקוח. 

המודל שואב אוטומטית את כרטסת הנהלת החשבונות המעודכנת ביותר ומחשב את תאריך היעד של כל חשבונית (DUEDATE). הדרך לעשות זאת היא פשוטה, עבור כל חשבונית, לקחת את תאריך הוצאת החשבונית ולחבר לה את הסדר ימי התשלום שהוסכם עם הלקוח. 

לאחר חישוב תאריך היעד, המודל מחשב את הימים שנותרו מתאריך החתך (למשל היום) ועד לתאריך היעד. ואז מקטלג את החוב מכל חשבונית לפי הימים שנותרו על פי טווחי זמן שקבעתי מראש. במקרה הזה:

מ1 ועד 30 ימים 

מ31 ימים ועד 60 ימים. 

מ 61 ימים ועד 90 ימים.

מ 90 ימים ועד 120 ימים. 

בנוסף, כדובדבן נוסף, אראה איך “לצבוע” את החוב כ”חוב מוסדר” או כחוב שעבר את תאריך היעד ונמצא “מחוץ להסדר“.  

התוצאה הסופית יכולה להיות טבלה מסכמת, אבל לא רק,  אפשר להראות את התקבולים הצפויים על ציר הזמן במגוון דרכים וחיתוכים.

לדוגמא בטבלה

או בגרף עמודות מעורב:  

איך בונים את זה? 

נבין קודם מה אנחנו רוצים לחשב: 

שלב 1 – חישוב כלל החוב של כל לקוח לסוף ליום החתך – חישוב פשוט של סיכום רץ של סכום התנועות פר לקוח חובה וזכות (יתרה מצטברת). 

שלב 2 – סיכום רגיל של החשבוניות בחובה בלבד ליום החתך (חוב שוטף). 

שלב 3 – אם היתרה המצטברת קטנה מהחוב השוטף (או מאפס), החוב יהיה החוב השוטף אחרת המודל יתן את היתרה המצטברת כחוב. 

לשים לב לביטול חשבוניות! חשבונית מבוטלת חייבת להירשם בצד חובה במינוס. 

נסתכל על המודל במבט מלמעלה:

טבלאות שנשתמש בהן: 

CUSTOMERS – טבלת נתוני לקוחות (מספר חשבון, שם, ימי תשלום לפי הסכם- שוטף + 30 למשל):

Rollertable – טבלת כרטסת חשבונות (מספר חשבון, שם, חובה,זכות, סה”כ לשורה, תאריך רישום חשבונית). יש לשים לב שאני צריך רק את הלקוחות אז אני יכול לבחור אם לסנן את הכרטסת בשלב האדיטור (QUERY EDITOR) או לבנות מסנן בתוך אחת הנוסחאות בהמשך. נתון לשיקולכם, רק חשוב לפתור את העניין כדי שתקבלו נתונים הגיוניים.

DATE – טבלת תאריכון .

AGE_GROUPS – טבלת טווחי ימי החוב שקבענו מראש:

שימו לב שטבלה זו אינה מקושרת לטבלאות האחרות. 

והנה הקשרים בין הטבלאות:

אוקיי, עכשיו לנוסחאות.

נוסחה ראשונה – סיכום עמודת החובה בכרטסת כאשר ההתייחסות היא ללקוחות בלבד:

HovaNet = 
CALCULATE(SUM(Rollertable[SchumHova]), 
FILTER(Customers, 
Customers[AccuRang]>=150000&&
Customers[AccuRang]<160000))

נוסחה הבאה – סיכום רץ של אותה העמודה באותו ההקשר:

RuningTotal = 
var
RUNINIGT = CALCULATE(
    sumx(Rollertable,Rollertable[schum_hova]-Rollertable[schum_zchut]),
    FILTER(all(Rollertable),
    Rollertable[asmchta_date]<= MAX(Rollertable[asmchta_date])),VALUES(Rollertable[cust_id]))
VAR
ONLYCUSTOMER = FILTER(Customers,Customers[AccuRang]>=150000&&
Customers[AccuRang]<160000)

RETURN
IF(RUNINIGT>0,CALCULATE(RUNINIGT,
ONLYCUSTOMER),
BLANK())

נבנה נוסחה שתחשב את החוב של הלקוח לפי הנמוך מבין היתרה המצטברת לבין החוב השוטף של כל תקופה:

Debt = 

VAR HOVABIGGER = [RuningTotal]<=[HovaNet] 

return 

if(HOVABIGGER, [RuningTotal],[HovaNet])

נבנה נוסחה שתחשב את התאריך העתידי לתשלום של כל חשבונית לפי תנאי התשלום של הלקוח:  

DueDateMesure =  
SUMX(GROUPBY(rollertable, rollertable[asmchta_date], 
Customers[Terms]),rollertable[asmchta_date]+Customers[Terms])

עכשיו אפשר לבנות את הנוסחה המרכזית שתחבר את כל הנוסחאות ותסדר לנו את החוב של כל לקוח לפי סולם הטווחים העתידי שקבענו:

חוב בהסדר  = 

VAR daysleftforDue = ADDCOLUMNS(VALUES(rollertable[asmacta1]), 
  "daysleftColumn",CALCULATE(DATEDIFF( [SelectedDate],[DueDateMesure],DAY))) 

VAR MinRange = MIN ( AGE_Groups[MIN] ) 

VAR MaxRange = MAX ( AGE_Groups[MAX] ) 

VAR CustomersInRange = 

FILTER (daysleftforDue, 

   [daysleftColumn] > MinRange && 

   [daysleftColumn] <= MaxRange) 

var DBA =  SUMX(CustomersInRange,[debt]) 

return 

if(DBA>1,dba,BLANK())

נסביר את הנוסחה:

משתנה ראשון -  daysleftforDue – בונה טבלה וירטואלית מטבלת הכרטסת שלנו (NASTING) שתפקידה לחשב לנו עמודה (וירטואלית גם כן) של “הימים שנותרו” מתאריך החתך ועד לתאריך היעד [SelectedDate] שהיא נוסחה שמחשבת תאריך דינמי הניתן לשינוי באמצעות סלייסר. אפשר בקלות להחליף בנוסחת תאריך רגיל (DATE) לפי תאריך חתך שתבחרו. למשל: 

DATE(2018,12,31)

משתנים 2 ו 3 – MinRange וגם MaxRange מגדירים לנו את ערכי המינימום/מקסימום עבור הפילטר שיגיע מייד. 

משתנה 4 – CustomersInRange זהו פילטר הלוקח את הטבלה הוירטואלית שבנינו ומאתר את השורות בכרטסת שבהן הערך בעמודת “הימים שנותרו” בכל שורה נכנס לאחד הטווחים שקבענו בטבלת AGE_GROUPS. 

משתנה 5 – DBA – סוכם רוחבית את ערכי הנוסחה DEBT בשורות שנותרו לאחר הפילטר. 

לבסוף הנוסחה המסיימת מחזירה רק את ערכי DEBT הגדולים מ 1, מנכה את יתרות האגורות כדי לקבל רק חשבוניות קיימות

חוב מחוץ להסדר 

אם נחשוב על זה רגע, חישבנו כאן ערכי חוב על בסיס חשבוניות “פתוחות” שטרם עבר מועד התשלום שנקבע להן. 

אבל מה קורה אם עברנו את מועד התשלום שנקבע? 

למעשה במקום לחשב את “הימים שנותרו” כלומר תאריך היעד פחות תאריך החתך, נחשב את “הימים שעברו” כלומר תאריך החתך פחות תאריך היעד. שאר החישוב נותר זהה:

חוב מחוץ להסדר =  

VAR daysoverdue  = ADDCOLUMNS(VALUES(rollertable[asmacta1]), 
  "daysOver",CALCULATE(DATEDIFF( [DueDateMesure],[SelectedDate],DAY))) 

VAR MinRange = MIN ( AGE_Groups[MIN] ) 

VAR MaxRange = MAX ( AGE_Groups[MAX] ) 

VAR CustomersInRange1 = 

FILTER (daysoverdue, 

   [daysOver] > MinRange && 

   [daysOver] <= MaxRange&& 

   [daysOver]<61) 

var 

DBA =  SUMX(CustomersInRange1,[debt]) 

return 

if(DBA>1,dba,BLANK())