מודל אקסל לחיזוי תזרים מזומנים

עדכון (מרץ 2016): הגרסה החדשה של הקובץ נמצאת כאן.

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

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

אז ככה. ראשית, הורידו את קובץ ה-RAR הבא. פיתחו אותו.

מאחר והקובץ צריך להפעיל Python, מה שאין לרובכם, השתמשתי בטכניקה שנקראת Freezing כדי שפעולת העיבוד תוכל להיקרא בכל מחשב. מה שצריך לעשות זה לפתוח את קובץ האקסל (Cashflow Forecast Template) ולאפשר הרצת Macro. ערכו אותו כאוות נפשכם, וכשאתם רוצים לראות את התזרים שמתקבל – הריצו את הקובץ cashflow.exe שנמצא באותה בספריה. זה אוטומטית יעשה את מה שצריך באקסל.

אז איך עובדים עם הקובץ?

שלב ראשון: קלט

עיקר הקלט בקובץ נוגע בהוצאות ובמאפיינים שלהן, כפי שניתן לראות בתמונת המסך הבאה:

תמונת מסך מודל תזרים מזומנים

תמונת מסך מודל תזרים מזומנים

הקלט מוזן בלשונית Uses, ועבור כל הוצאה צריך להזין את המאפיינים הבאים:

  1. שם (בד"כ שם הספק)
  2. סיווג כללי
  3. סיווג מפורט יותר (למשל, עבור הוצאות תפעוליות: תקשורת, משכורות וכו')
  4. יום בחודש שההוצאה תשולם בו (עדיף לבחור 1 עד 28 מאחר והיום האחרון בפברואר הוא ה-28)
  5. אמצעי תשלום
  6. מטבע (כרגע עובד על שקלים, דולר ארה"ב ואירו)
  7. סכום צפוי, כל חודש בנפרד

כמו-כן, יש להזין את שערי החליפין העדכניים בלשונית Input, צ'קים שקיימים בקופה ואתם יכולים לנכות בלשונית צ'קים בקופה (לא חובה) ואת טווח התאריכים שאתם רוצים לחזות בלשונית All Periods, בתאים C3 ו-C4:

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

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

אם ברצונכם לקבל תמונת מצב של התגלגלות יתרת המזומנים מתקופה לתקופה בתוך התקופה שהזנתם בלשונית All Periods המשיכו להזין את התאריכים בלשוניות Forecast2 ואילך (רק את תאריך הסוף), רק שימו לב שאתם מסיימים עם תאריך הסוף שהוזן בלשונית All Periods.

זה לגבי השימושים, כעת למקורות.

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

  • בצד המקורות, לפי הסדר, נמצאות יתרות הפתיחה בבנקים (מסגרת פחות מצב העו"ש), לאחר צ'קים שצריכים לרדת ולא נמצאים בתחזית התזרים, הזרמות הון צפויות והפקדות צפויות במזומן, בין אם הן נכיון צ'קים או תשלומים של לקוחות. בשביל הנוחות, אני מציג את צפי ניכיון הצ'קים לפי מצב הקופה הנוכחי ואופק הפקדה של חצי שנה, אבל זה רק לעזר – תחזית ההפקדות נעשית ידני ואיננה מוזנת מתוך נוסחה כלשהי (ראה תא C45 בלשוניות של תתי התקופות).
  • בצד השימושים סידרתי את ההוצאות התזרימיות הצפויות לפי סדר חשיבות. ראשונים הם הצ'קים שכבר נמצאים בחוץ , הוראות קבע והחזרי הלוואות צפויים. לאחר מכן, משכורות קרנות ותשלומים למוסדות. לבסוף, תשלומים שוטפים לספקים, תשלומי חובות לספקים, כרטיסי אשראי והוצאות בחו"ל במידה וישנו סניף זר.

דגשים חשובים על האופן שבו הצגת השימושים עובדת

בלשונית All Periods תוכלו לראות בצהוב את כל הפרמטרים שיש להזין בצורה ידנית ומשפיעים על הלשוניות של תתי התקופות. הוצאות שמשולמות בצ'קים או כרטיסי אשראי נכללות אוטומטית במסגרת ה-SUMIF של אמצעי התשלום. הוצאות שלא, כלומר שאופן התשלום שלהן הוא "גמיש", השם שלהן צריך להיות מוזן באזור הצהוב כדי שה-SUMIF יכלול אותן. זאת גם הסיבה שעשיתי עיצוב מותנה שיצבע באפור בלשונית Uses את ההוצאות הללו. יכולתי לפרוס אותן לבד באמצעות הקוד אך התעצלתי. אם תהיה לכך דרישה אשפר זאת.

שלב שני: פלט

הפלט של המודל מתחלק לשני סוגים עיקריים.

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

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

פלט מודל תזרים המזומנים: דוח רווח והפסד תזרימי, כולל התגלגלות יתרת המזומנים

פלט מודל תזרים המזומנים: דוח רווח והפסד תזרימי, כולל התגלגלות יתרת המזומנים

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

בדיקת עקביות: סגירת מעגל ההכנסות

בדיקת עקביות: סגירת מעגל ההכנסות

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

התפתחות יתרת המזומנים מתקופה לתקופה

התפתחות יתרת המזומנים מתקופה לתקופה

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

שלב שלישי: בדיקות

מהניסיון שלי במודל הזה, ישנן שלושה מקומות שבהם הוא יכול להיכשל.

האחד, פירוק לתתי תקופות. הבדיקה לשלמות הנתונים הללו מתבצעת בטור K, שורות 50-55 בלשונית Summary. במידה ואתם מקבלים FALSE באחת מהשורות, בידקו את לשונית Check sheet ו-וודאו כי אין FALSE בעמודה G. אם יש, שם כנראה הבעיה.

השני, הוצאות שלא נכללות בטור השימושים. זה יכול לקרות אם הזנתם אמצעי תשלום "גמיש" ושכחתם להזין את שם ההוצאה בלשונית All Periods טור E באזורים הצהובים. הבדיקה לכך נעשית במסגרת שורות 57-59 בלשונית Summary:

בדיקות שלמות למודל תזרים מזומנים

בדיקות שלמות למודל תזרים מזומנים

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

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

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

מספר מילות סיכום

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

10 תגובות כתוב תגובה

  1. שלום ערן,

    אהבתי מאוד את המודל. לצערי רציתי לדווח על תקלה שיש בקובץ EXE שמריץ את הפריז.

    בכל מקרה, מודל מעניין מאוד.

    אני יכול לעבוד בלי הפריזינג הזה? אני רוצה לשחק איתו קצת מחר.

    אפשר לדבר גם על הכלי שאני בניתי.

    לילה מצוין

    • היי עידן,
      בדקתי עכשיו שוב והקובץ עובד. אגב, הקובץ לא מריץ את הפריז – הוא מריץ מאקרואים שמפעילים את קוד ה-Python.
      בכל אופן, מהי הודעת השגיאה שאתה מקבל?

      ערן

  2. היי ערן,
    קובץ רציני מאוד- שאפו!

    רציתי לשאול מדוע לא ביצעת איזשהי התייחסות לשיעור צמיחה של החברה (גידול בשכר, הוצאות תפעוליות שונות)?

    באותו הקשר, נניח ויש לי מספר מצומצם של סעיפים אשר לגביו אני יודע על יציאת מזומנים עתידית ודאית ואילו לכל השאר זה בעצם עדכון תזרים העבר לגבי צפי צמיחה של החברה או של הסעיף הספציפי, במקרה כזה, מה ההבדל בין תזמ"ז צפוי לתקציב? (בהנחה שהתקציב מנוצל במלואו בכל תקופה).

    מודה לך מראש,
    ניר

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

      ערן

  3. ערן שלום,

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

    תודה!

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

  4. שלום,
    כל הכבוד להשקעה בכלל ולגיליון תזרים המזומנים בפרט.

    איך אני מריץ בנ"ל את הקובץ cashflow.exe ?

    תודה
    גלעד

  5. שלום ערן,
    קודם כל תודה וכל הכבוד, הקובץ מרשים מאד.

    אבל יש לי חור בעלילה – הנתונים בלשונית Flat dataset מוקלדים, וגם הנתונים ב-Uses.
    הרעיון אמור להיות שהנתונים ב-Flat dataset נשלפים אוטומטית מההקלדה ב-Uses ומסודרים לפי תאריך, לא? או שאולי לא הבנתי נכון.
    אשמח אם תוכל לעשות לי קצת סדר…

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

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

    תודה רבה מראש על העזרה.

    • היי,
      1. הנתונים ב-Flat dataset ממולאים אוטומטית כשלוחצים Refresh, וזה מתבסס על מה שמוזן ב-Uses. אני ממליץ לשחק טיפה עם Power Query כדי להבין יותר טוב את הלוגיקה בקובץ (ותוכל גם לערוך אותה כראות עיניך).
      2. כן, זה אמור לעבוד.
      3. אכן, בעייתי. מה שאני הייתי עושה בזמנו זה פשוט להזין כל מועד תשלום בשורה משלו.

      ערן

כתיבת תגובה