پیش‌بینی موجودی کالا با جدول آبشاری؛ گام‌به‌گام
شرکت‌های موجودی‌محور هم باید از کمبود کالا بترسند و هم از انبار پر. موجودی زیاد سرمایه در گردش را قفل می‌کند و موجودی کم، مشتری را ناراضی. پیش‌بینی موجودی کالا درست همین‌جا اهمیت پیدا می‌کند. در این نوشته با ابزاری به اسم جدول آبشاری، پیش‌بینی خرید و موجودی پایان دوره را در مدل مالی می‌سازیم.
چرا موجودی را مثل سود و زیان پیش‌بینی نمی‌کنیم؟
صورت سود و زیان فعالیت یک بازه را نشان می‌دهد؛ مثلاً فروش اسفند. اما موجودی کالا قلمی از ترازنامه است و ماهیت انباشتی دارد. مانده‌ی پایان اسفند نتیجه‌ی مانده‌ی پایان بهمن به‌علاوه‌ی اتفاق‌های اسفند است.
برای همین، اولین کار در پیش‌بینی هر قلم ترازنامه‌ای این است که مانده‌ی ابتدای دوره را از آخرین ماه واقعی بردارید. فرمول عمومی این است:
- مانده‌ی ابتدای دوره
- به‌اضافه‌ی افزایش‌ها (مثل خرید)
- منهای کاهش‌ها (مثل پرداخت یا هزینه‌شدن)
- برابر مانده‌ی پایان دوره
برای موجودی کالا این فرمول به شکل زیر در می‌آید:
مانده‌ی ابتدا + خرید جدید کالا − بهای تمام‌شده‌ی کالای فروش‌رفته = موجودی پایان
بهای تمام‌شده‌ی کالای فروش‌رفته (COGS) را معمولاً از پیش‌بینی فروش و حاشیه‌ی سود ناخالص به‌دست می‌آورید. مجهول اصلی، «خرید جدید» است و جدول آبشاری دقیقاً برای حل همین مجهول به کار می‌آید.
جدول آبشاری چیست؟
در مدل‌سازی مالی، جدول آبشاری جدولی است که یک مقدار را طبق یک برنامه‌ی قابل‌تنظیم بین چند دوره پخش می‌کند. تاریخ‌ها هم در سطرها هستند و هم در ستون‌ها. هر سطر نشان می‌دهد مبلغ یک دوره‌ی مقصد از چه ماه‌هایی تأمین می‌شود و هر ستون نشان می‌دهد سهم هر ماه چقدر است.
همین ساختار برای هر قلم ترازنامه‌ای کاربرد دارد: مخارج سرمایه‌ای، حساب‌های پرداختنی، حساب‌های دریافتنی و مانند آن. اگر یک بار آن را خوب یاد بگیرید، بقیه‌ی پیش‌بینی‌ها ساده‌تر می‌شوند. برای مرور مفهوم مشابه در مدل‌سازی، مدل‌سازی سرمایه در گردش؛ روش کلاسیک و کورک‌اسکرو را ببینید.
گام اول: برنامه‌ی خرید را جدا و قابل‌تغییر بنویسید
فرض کنید در فروردین ۱۴۰۳ پیش‌بینی می‌کنید ۱۰۰ میلیون تومان بهای تمام‌شده‌ی کالای فروش‌رفته خواهید داشت. معنایش این نیست که در همان فروردین ۱۰۰ میلیون تومان کالا بخرید؛ کالا باید زودتر در انبار باشد.
پس یک جدول فرض‌ها می‌سازید، مثلاً:
| فاصله از ماه فروش | سهم از خرید |
|---|---|
| ۳ ماه قبل | ۵۰٪ |
| ۱ ماه قبل | ۵۰٪ |
یعنی نیمی از کالایی که قرار است در یک ماه به COGS تبدیل شود، سه ماه قبل از آن خریداری می‌شود و نیم دیگر یک ماه قبل. نکته‌ی مهم انعطاف‌پذیری است: اگر زمان تأمین‌کننده یا سیاست خرید عوض شد، فقط همین جدول را تغییر می‌دهید و همه‌ی فرمول‌ها خودکار به‌روز می‌شوند.
گام دوم: اعداد جدول فرض‌ها را قابل‌ارجاع کنید
برای اینکه فرمول بتواند به «سه ماه قبل» ارجاع بدهد، آن را باید به‌صورت عدد منفی (مثلاً ۳−) ذخیره کنید، اما متن «ماه قبل» کنارش دیده شود. این کار با قالب‌بندی سفارشی انجام می‌شود: در اکسل با Ctrl+1 پنجره‌ی Format Cells را باز کنید و در بخش Custom قالبی بنویسید که برای مقادیر منفی بعد از عدد عبارت «ماه قبل» را نمایش دهد.
نتیجه این است که ظاهر سلول متن است، ولی محتوای واقعی آن عدد است و می‌توان با آن محاسبه کرد.
گام سوم: فرمول جست‌وجو را در جدول آبشاری بنویسید
حالا باید برای هر خانه‌ی جدول آبشاری مشخص کنید چند درصد از COGS ماه مقصد باید در ماه جاری خریداری شود. منطق ساده است:
- فاصله‌ی ماه خرید (ستون) تا ماه مصرف (سطر) را حساب کنید. اگر ماه خرید ژانویه و ماه مصرف آوریل باشد، فاصله ۳− است.
- این عدد را در جدول فرض‌ها جست‌وجو کنید. برای ۳− همان ۵۰٪ برگردانده می‌شود.
- درصد را در مبلغ COGS ماه مصرف ضرب کنید تا مبلغ خرید به‌دست بیاید.
ترکیب INDEX و MATCH برای جست‌وجو ابزار مناسبی است. اگر فاصله در جدول فرض‌ها نبود، نتیجه صفر می‌شود؛ یعنی در آن ماه برای آن مصرف خریدی انجام نمی‌شود.
مثال عددی
فرض کنید COGS فروردین ۱۰۰ میلیون و COGS اردیبهشت ۸۰ میلیون تومان است. طبق برنامه‌ی بالا:
- خرید دی‌ماه = ۵۰٪ از COGS فروردین = ۵۰ میلیون
- خرید اسفند = ۵۰٪ از COGS فروردین = ۵۰ میلیون
- خرید بهمن = ۵۰٪ از COGS اردیبهشت = ۴۰ میلیون
- خرید فروردین = ۵۰٪ از COGS اردیبهشت = ۴۰ میلیون
مبلغ خرید هر ماه، مجموع سهم‌هایی است که از چند ماه آینده‌ی مصرف روی همان ماه افتاده است. جمع ستونی جدول آبشاری، سطر «خرید جدید کالا» را می‌سازد.
گام چهارم: موجودی پایان دوره را ببندید
آخرین گام ساده است. برای هر ماه:
موجودی پایان = موجودی ابتدا + خرید جدید − COGS
موجودی ابتدای ماه اول پیش‌بینی را از آخرین ترازنامه‌ی واقعی بردارید و بعد از آن، موجودی پایان هر ماه را به ابتدای ماه بعد منتقل کنید. اگر موجودی منفی شد، یعنی برنامه‌ی خرید با فروش پیش‌بینی‌شده هماهنگ نیست. اگر موجودی بیش از حد بالا رفت، نشانه‌ی قفل‌شدن نقدینگی است. برای سنجش آن، روزهای موجودی (DIO) چیست و چگونه کاهشش دهیم؟ را بخوانید.
نکته‌های اجرایی
- بهتر است جمع درصدهای برنامه‌ی خرید ۱۰۰٪ باشد؛ این را با یک سلول کنترلی بررسی کنید.
- برنامه‌ی خرید را برای هر گروه کالایی جداگانه تعریف کنید، چون زمان تأمین کالاها یکسان نیست.
- اگر کالاها فصلی‌اند، حتماً به ریسک کسب‌وکار فصلی؛ ۵ راه کنترل نقدینگی و موجودی نگاه کنید.
- همین الگو را برای پرداختنی‌ها و دریافتنی‌ها هم می‌توانید تکرار کنید.
پرسش‌های متداول
جدول آبشاری فقط برای موجودی است؟
نه. هر جا لازم است مقداری طبق برنامه بین چند دوره پخش شود، از آن می‌توان استفاده کرد؛ مانند خرید دارایی، استهلاک، دریافت از مشتریان و پرداخت به تأمین‌کنندگان.
اگر شرکت موجودی کالا ندارد، این روش به چه کارم می‌آید؟
ساختار آبشاری مستقل از موجودی است. با یادگیری آن روی موجودی، می‌توانید سایر اقلام ترازنامه را هم با همان منطق پیش‌بینی کنید.
چرا از INDEX و MATCH استفاده کنیم و ارجاع مستقیم ندهیم؟
ارجاع مستقیم با تغییر برنامه‌ی خرید به هم می‌ریزد. جست‌وجو بر اساس فاصله‌ی زمانی باعث می‌شود با عوض‌کردن جدول فرض‌ها، کل مدل بدون ویرایش فرمول به‌روز شود.
برنامه‌ی خرید را چطور تعیین کنم؟
از زمان تأمین واقعی، حداقل سفارش و سیاست موجودی ایمنی شرکت شروع کنید و آن را با داده‌ی تاریخی کنترل کنید.
جمع‌بندی
پیش‌بینی موجودی کالا از یک قاعده‌ی ساده شروع می‌شود: مانده‌ی ابتدا، به‌علاوه‌ی خرید، منهای COGS. مجهول اصلی یعنی خرید را با جدول آبشاری و یک برنامه‌ی خرید قابل‌تغییر پیدا می‌کنید. اگر برنامه را جدا نگه دارید، اعداد را به‌صورت قابل‌ارجاع ذخیره کنید و فرمول جست‌وجو بنویسید، مدل شما با هر تغییر فرض‌ها دوباره محاسبه می‌شود. همین ساختار به سایر اقلام ترازنامه هم تعمیم می‌یابد.