תקציב מול ביצוע זהו כלי נפוץ למדידת ביצועים תקופתית. מדידת התקציב מול תזרים המזומנים בפועל הוא כלי מתקדם יותר לצורך בקרה צמודה על ההוצאות של החברה.
המודל הזה עושה שימוש במודל שבניתי בפוסט קודם, המציג דוח תזרים בגישה הישירה, ובונה עליו את מודל התקציב מול התזרים בפועל.
את מרכיבי המודל הקודם אין צורך לפרט גם כאן, תוכלו לקרא עליהם בפירוט בפוסט קודם, אתייחס רק לטבלאות ולנוסחאות שהתווספו. וכמובן אצרף בסוף את הקבצים עצמם לשימושכם.
המודל במבט מלמעלה:
והנה הקשרים בין הטבלאות במודל:
הטבלה הראשונה שהתווספה למודל התזרים היא טבלת נתוני התקציב FACT הכוללת את כל הפעולות העסקיות הצפויות ברמה החודשית, אותה יש לבנות בנפרד לצורך המודל. את התקציב ניתן לבנות בכל סוג של תכנה חיצונית, ולשאוב למודל באופן אוטומטי. לצורך ההדגמה בניתי טבלת תקציב באקסל (מצורף בסוף), הנתונים הם כמובן פייק, וגם מוגזמים בכוונה (סטיות גדולות מהתזרים בפועל) על מנת להמחיש את יכולות המודל:
טבלה נוספת שהתווספה היא טבלת Select Period, שתשמש אותנו למעבר בין חלונות הזמן שקבענו מראש. חלונות הזמן שקבעתי מראש הן: חודש קודם, רבעון אחרון ושנה אחרונה. המודל ידע לחשב את הנתונים בכל תקופה באופן דינמי.
הנה הדגמת וידאו קצרה של הדשבורד בצורתו הסופית. ניתן להוסיף עמודות נוספות כגון עמודות תחזית מול תזרים בפועל באותו האופן בדיוק כפי שהוספתי את עמודות התקציב. שימו לב שנתוני החברה מוצגים באופן דינמי לפי הלחיצה שלי על הכפתורים המובנים. כולל שינוי מתאים בכותרת של המודל.
אוקיי, נתחיל לבנות את הנוסחאות:
לפני הנוסחה הראשונה יש לבנות עמודות בטבלת DATE שיתנו לנו TRUE כאשר השורה נמצאת בתוך חלון הזמן שקבענו מראש או FALSE כאשר השורה נמצאת מחוץ לחלון הזמן. למשל אנחנו עכשיו בינואר 2022 החודש הקודם היה דצמבר 2021. ולכן, בטבלת DATE נבנה עמודה שתתן לנו TRUE רק כאשר השורה שייכת לדצמבר 2021 , באופן הבא:
Current Month =
var YearMonthSort = CONCATENATE('Date'[Year],'Date'[Month])
return
if( YearMonthSort ="202112", "True", "False")
הנוסחה עושה שימוש בעמודות הקיימות YEAR ו MONTH, מחברת אותן לעמודה וירטואלית אחת בפורמט YYYYMM, ואז בודקת אם הצירוף שהתקבל בשורה הוא “202112”, אם כן היא רושמת TRUE, אחרת FALSE. ניתן לשנות את חלון הזמן בקלות על ידי עדכון הנוסחה.
באותו אופן נבנה עמודות לשנה הנוכחית ולרבעון הנוכחי:
Current Qurter =
var YearQurterSort = CONCATENATE('Date'[Year],'Date'[Quarter])
return
if( YearQurterSort ="2021Q4", "True", "False")
Current Year = if('Date'[Year]=2021, "True", "False")
נעבור לנוסחת ה DAX הראשונה :
ActualCFCM = CALCULATE([CashFlowD],'Date'[Current Month] = "True")
שימו לב שהנוסחה מכילה את [CashFlowD] שהיא הנוסחה לתזרים מזומן ישיר שכאמור, מפורטת במאמר קודם. הנוסחה מחשבת את התזרים לחודש האחרון בלבד.
אותו דבר בדיוק נעשה לרבעון ולשנה האחרונים :
ActualCFCQ = CALCULATE([CashFlowD],'Date'[Current Qurter] = "True")
ActualCFCY = CALCULATE([CashFlowD],'Date'[Current Year] = "True")
הנוסחה הבאה תציג את החישוב הרלוונטי בהתאם לחלון הזמן הנבחר בסלייסר :
Actuals Selected Period =
var selectedperiod = SELECTEDVALUE(SelectPeriod[Select Period])
Return
switch
(
true(),
selectedperiod="Current Month", [ActualCFCM],
selectedperiod="Quarter To Date", [ActualCFCQ],
selectedperiod="Year to Date",[ActualCFCY]
)
כך שכאשר נלחץ על כפתור רבעון נוכחי למשל, נקבל דשבורד המכיל את כל הנתונים בהקשר של הרבעון האחרון של 2021 בלבד.
עכשיו יש לבנות את אותן הנוסחאות בדיוק לנתוני התקציב. אני לא רוצה לחזור על עצמי ולהאריך את הפוסט מעבר לצורך אז אשאיר את זה לכם לחפור בקבצים המצורפים לראות איך זה מתבצע בדיוק.
בהנחה שיש לנו עמודת תקציב במטריקס, ואת נוסחת [Budget Selected Period], נסביר עכשיו את נוסחאות ההפרש האבסולוטי ובאחוזים:
חישוב ההפרש האבסולוטי פשוט:
Actual vs Budget, # Selected Period = var ActualvsbudgetCM = [Actuals Selected Period]- [Budget Selected Period] Return ActualvsbudgetCM
חישוב ההפרש באחוזים צריך לקחת בחשבון אפשרות של סימנים הפוכים במונה ובמכנה ולכן המכנה יעטף בפונקציית ABS :
Actual vs Budget, % Selected Period = var Actualvsbudget = ([Actuals Selected Period]- [Budget Selected Period]) / abs([Budget Selected Period]) Return IFERROR(Actualvsbudget,0)
נוסחה אחרונה שאציין, תפקידה לבנות משולשים קטנים FLAGS, שיסמנו איפה מתחבאים ההפרשים המהותיים בין התקציב לתזרים בפועל. אני בחרתי ב 12% כרף שנחשב “מהותי” אבל אתם יכולים להחליף לאיזה אחוז שתרצו:
Flag Budget = var green = if(MEASURS[Actual vs Budget, % Selected Period]>0.12,1) var red = if(MEASURS[Actual vs Budget, % Selected Period]<-0.12,2) return COALESCE(green,red)
קבצים מצורפים: