پیش‌بینی سود و زیان با یک فرمول و فهرست روش‌ها
بیشتر پیش‌بینی‌های صورت سود و زیان در اکسل دستی‌اند: عددهای تایپ‌شده، فرمول‌های ناهمگون و منطقی که دنبال کردنش سخت است. راه‌حل، پیش‌بینی سود و زیان با یک فرمول واحد است که روش هر ردیف را از یک فهرست کشویی می‌گیرد. در این راهنما ساختار برگه‌ی محرک‌ها، فرمول SWITCH و نکته‌های کاربردی آن را می‌بینید.
مشکل پیش‌بینی دستی چیست؟
فرض کنید برای هر مشتری تازه ابتدا سود و زیان تاریخی را باز می‌کنید. برای فروش میانگین سه‌ماهه می‌گذارید، برای اجاره عددی ثابت و برای هزینه‌ی تبلیغات نسبتی از فروش. هر فرمول جداگانه نوشته شده است. اگر بخواهید روش را عوض کنید، فرمول را بازنویسی می‌کنید؛ اگر بخواهید حاشیه‌ی اطمینان اضافه کنید، منطق را از نو می‌سازید. نتیجه مدلی است که بازبینی و تغییرش وقت‌گیر است و خطا در آن راحت پنهان می‌ماند.
ایده‌ی بهتر این است که انتخاب روش از محاسبه جدا شود. این همان منطق پیش‌بینی مبتنی بر محرک است، اما در سطح عملیاتی و برای هر ردیف سود و زیان.
برگه‌ی محرک‌ها؛ سه ستون کافی است
پیش از نوشتن فرمول به جایی نیاز دارید که همه‌چیز از آنجا کنترل شود. این برگه سه ستون دارد:
- فهرست کشویی روش: برای هر سطر یکی از روش‌ها انتخاب می‌شود، مثل میانگین سه‌ماهه، میانگین شش‌ماهه، ماه قبل، سال قبل، مقدار ثابت یا درصدی از فروش.
- حاشیه (بافر): اگر میانگین شش‌ماهه را می‌خواهید اما سخت‌گیر یا خوش‌بین‌تر باشید، مثلاً ۱۰٪ اضافه می‌کنید و فرمول آن را روی عدد اعمال می‌کند.
- مفروضه‌ی ثابت: اگر مبلغ سالانه ۱۲۰٬۰۰۰ دلار وارد کنید، فرمول خودش آن را بر دوازده تقسیم می‌کند.
نکته‌ی تمیزکاری: پاک‌کردن اعداد دست‌نویس
وقتی ستون‌های پیش‌بینی را به سمت راست می‌کشید، اکسل مقدارهای ثابت را هم کپی می‌کند. ویژگی «Go To Special» با گزینه‌ی ثابت‌ها (Constants) همه‌ی اعداد تایپ‌شده را یک‌جا انتخاب می‌کند و با یک کلید پاک می‌شوند. جمع‌های فرعی که فرمول‌اند سالم می‌مانند و پوسته‌ی تمیزی برای فرمول باقی می‌ماند.
چرا SWITCH به جای IF تودرتو؟
اولین غریزه نوشتن IF تودرتو است: اگر محرک برابر میانگین شش‌ماهه بود این، اگر میانگین سه‌ماهه بود آن. با هشت روش، فرمول به سختی خوانا می‌شود. SWITCH مقدار فهرست کشویی را می‌خواند، با هر حالت مقایسه می‌کند و محاسبه‌ی متناظر را برمی‌گرداند. بدون تودرتویی و بدون شلوغی.
یک طرح ساده از ساختار فرمول:
- مقدار انتخاب‌شده در فهرست را بخوانید.
- برای هر روش یک شاخه بنویسید، مثلاً میانگین سه ماه آخر یا مقدار همان ماه سال قبل.
- نتیجه را در
(1 + حاشیه)ضرب کنید. - برای مفروضه‌ی ثابت، مبلغ را بر تعداد دوره‌ی هر سال تقسیم کنید.
- کل فرمول را داخل IFERROR بگذارید تا خطاها گزارش را خراب نکنند.
فرمول را یک بار می‌نویسید و روی همه‌ی سلول‌های پیش‌بینی کپی می‌کنید؛ کل سود و زیان زنده می‌شود.
هر سطر روش خودش را دارد
همه‌ی ردیف‌ها را نباید یکسان پیش‌بینی کرد. چند مثال:
- فروش: میانگین سه‌ماهه با حاشیه‌ی ۱۰٪ وقتی رشد را جسورانه می‌خواهید.
- کارمزد درگاه پرداخت: درصد ثابتی از هر فروش، مثلاً ۲٪ از فروش.
- تبلیغات: اگر شرکت در فاز رشد است، شاید ۲۰٪ فروش.
- حسابداری، حقوقی، مشاوره: معمولاً ثابت؛ مبلغ ماهانه یا فصلی را وارد کنید.
- حقوق و دستمزد: بهتر است برگه‌ی جداگانه‌ی نیروی انسانی داشته باشید؛ روش را «سفارشی» بگذارید و مقدارها را لینک کنید. برنامه‌ی کارکنان در مدل مالی جزئیات آن را توضیح می‌دهد.
یک هشدار: روش «درصدی از فروش» را روی خود سطر فروش به کار نبرید، چون ارجاع دایره‌ای می‌سازد. برای همه‌ی ردیف‌های زیر آن کاملاً مناسب است.
مثال عددی ساده
فرض کنید فروش سه ماه اخیر ۹۰، ۱۰۰ و ۱۱۰ هزار دلار بوده است. میانگین سه‌ماهه ۱۰۰ هزار می‌شود. اگر حاشیه‌ی ۱۰٪ بگذارید، پیش‌بینی ماه بعد ۱۱۰ هزار دلار است. کارمزد ۲٪ فروش، یعنی ۲٬۲۰۰ دلار. هزینه‌ی حقوقی ثابت ۱۲۰٬۰۰۰ دلار سالانه، ماهانه ۱۰٬۰۰۰ دلار می‌شود. هر سه با همان فرمول و فقط با تغییر فهرست کشویی محاسبه می‌شوند.
ارائه‌ی نتیجه به مدیران
برگه‌ی محرک‌ها برای نمایش جذاب نیست و نباید به مدیران نشان داده شود. نمایش را از طریق یک خلاصه با مقایسه‌ی دوره‌ها بسازید. موتور زیرین یک فرمول است و ظاهر را جداگانه طراحی می‌کنید. برای ساختار مناسب خروجی، داشبورد مالی مؤثر را ببینید.
پرسش‌های متداول
آیا این روش جای مدل کامل را می‌گیرد؟
نه. برای ردیف‌های ساده‌ی سود و زیان خوب است. فروش و نیروی انسانی معمولاً به برگه‌ی تفصیلی نیاز دارند.
چند روش لازم است؟
هشت روش برای شروع کافی است؛ هر زمان روش جدیدی خواستید، یک شاخه به SWITCH اضافه کنید.
حاشیه چه کاری می‌کند؟
درصد افزایش یا کاهش را روی نتیجه‌ی روش اعمال می‌کند، بدون بازنویسی فرمول.
چرا IFERROR لازم است؟
چون سلول خالی یا تقسیم بر صفر در یک ردیف نباید کل گزارش را خراب کند.
جمع‌بندی
انتخاب روش را از محاسبه جدا کنید: یک برگه‌ی محرک‌ها با سه ستون، یک فرمول SWITCH و یک IFERROR. نتیجه پیش‌بینی‌ای است که تغییر روش در آن به قیمت یک کلیک تمام می‌شود، قابل بازبینی است و برای هر ردیف منطق خودش را دارد.