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

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

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

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

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

 

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

הנה מפת הקשרים בין עמודות הטבלאות:

 

טבלת התבנית Layout CFd – מסדרת את תצוגת הדוחות בכך שכל כותרת LEVEL1/LEVEL2 מקבלת מפתח ראשי Primary Key ושני מפתחות נוספים FROM, TO. באופן זה ניתן לחשב כל שורה באופן עצמאי ללא תלות בשורות האחרות. היתרון בכך הוא שמקבלים יותר שליטה על כל תא בטבלת המודל. החיסרון הוא פחות גמישות במידה ורוצים לפלח את התוצאות באופן שונה בזמן הצגתן בפני הצופים.

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

ניתן ללחוץ על התמונה להגדלה

טבלת האינדקס Ledger Mapping CF, מכילה טבלת מפתחות זרים (PK) שמתרגמים את המפתחות הראשיים בטבלת התבנית למפתחות הראשיים של החשבונות בכרטסת הנהלת החשבונות וכן בטבלת תנועות הבנק Sub Ledger.

 

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

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

שתי הטבלאות עברו ETL ב Power Query.

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

 

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

נוסחאות DAX

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

  1. נוסחה להכנסת השורות הריקות מטבלת התבנית לתוך המטריקס
BlankRowsCFD = if(calculate(countrows('Layout CFd'),CONTAINSSTRING ( 'Layout CFd'[Calculation], "Blank" ))=1,0)

2. נוסחה לחישוב עמודת הנתונים הכספיים בטבלת הבנק

CFBlanceSum = 
CALCULATE(sum(BankDmy[Balance])

)

3. נוסחה לחישוב שורות הסיכום על פי עמודות TO, FROM בטבלת התבנית

CFSubTotalsBalance = 

var currentfrom = selectedvalue('Layout CFd'[FROM])
var currentto = selectedvalue('Layout CFd'[TO])
return

calculate(
  [CFBlanceSum],
   filter(all('Ledger Mapping CF'),
       'Ledger Mapping CF'[PK] >= currentfrom  &&
       'Ledger Mapping CF'[PK] <= currentto
   )
)

4. נוסחה שמחברת את שלושת הנוסחאות הקודמות

CashFlowBalance = 

var formatstring = SELECTEDVALUE('Layout CFd'[Format])
 
return 

COALESCE([CFBlanceSum],[CFSubTotalsBalance],[BlankRowsCFD])

5. הנוסחה הזו עושה שני דברים:

  1. בונה עמודת “סיכום-רץ” וירטואלית המאפשרת לראות תמונת מצב סטטית של סוף כל חודש ותחילת כל חודש.
  2. בודקת באיזו שורה אנחנו נמצאים ומציבה את החישוב המתאים בכל אחת משלושת השורות הרלוונטיות בטבלת התבנית שלנו.
CF Bank Balance = 

VAR stapshotactualsprevmonth = CALCULATE([CashFlowBalance], filter(ALL('Date'), 'Date'[Date] <= EOMONTH(max('Date'[Date]),-1)))
VAR stapshotactualscmonth = CALCULATE([CashFlowBalance], filter(ALL('Date'), 'Date'[Date] <= max('Date'[Date])))
VAR changeMOM = stapshotactualscmonth-stapshotactualsprevmonth
var selectedlevel = SELECTEDVALUE('Layout CFd'[Level 2])

return 

switch
    (
    true,
    selectedlevel = "תזרים מזומנים יתרת פתיחה", stapshotactualsprevmonth,
    selectedlevel = "תזרים מזומנים יתרת סגירה", stapshotactualscmonth,
    selectedlevel = "תנועה במזומן", changeMOM
    )

זו התוצאה שמתקבלת כאשר מציבים את הנתונים (ניתן ללחוץ על התמונה להגדלה):

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

  1. הנוסחה הראשונה מחשבת הנתונים הכספיים מכרטסת הנהלת החשבונות. שימו לב שהנוסחה מתייחסת לטבלת הכרטסת TransactionsDmyCF בשונה מהנוסחה למעלה שמתייחסת לטבלת תנועות הבנק BankDmy.
CFDTransSum = 
CALCULATE(sum(TransactionsDmyCF[value])
)

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

CFSubTotals = 

var currentfrom = selectedvalue('Layout CFd'[FROM])
var currentto = selectedvalue('Layout CFd'[TO])
return

calculate(
  [CFDTransSum],
   filter(all('Ledger Mapping CF'),
       'Ledger Mapping CF'[PK] >= currentfrom  &&
       'Ledger Mapping CF'[PK] <= currentto
   )
)

3. הנוסחה הראשית שמאחדת את כל הנוסחאות שבנינו במודל לכדי נוסחה אחת להצבה במודל

CashFlowD = 

var formatstring = SELECTEDVALUE('Layout CFd'[Format])
 
return 

COALESCE([CFDTransSum],[CFSubTotals],[BlankRowsCFD],[CF Bank Balance])

 

הנה התוצאה הסופית של המודל הכוללת את שתי התצוגות שבנינו. מחולקות לשתי תצוגות מטריקס לשם אסתטיקה בלבד (ניתן ללחוץ על התמונה להגדלה).

קישור לקבצים:

https://bit.ly/32R8O5P