داشبورد بودجه در برابر عملکرد با Power Query و SUMIFS
داشبورد بودجه در برابر عملکرد زمانی ارزش دارد که با تغییر بودجه یا ثبت اعداد واقعی جدید، فقط با یک «تازه‌سازی» به‌روز شود. راز این کار در دو چیز است: ساختار درست داده با Power Query و محاسبه‌ی مبلغ‌ها با SUMIFS. در این نوشته مسیر ساخت چنین داشبوردی را قدم‌به‌قدم می‌بینیم.
چرا داشبورد بودجه در برابر عملکرد مهم است؟
مقایسه‌ی برنامه با نتیجه‌ی واقعی، پایه‌ی بیشتر گزارش‌های مدیریتی و جلسات هیئت‌مدیره است. اگر هر ماه این مقایسه را با کپی و چسباندن دستی بسازید، هم زمان زیادی می‌گیرد و هم خطا وارد کار می‌شود. داشبورد خودکار سه ویژگی دارد:
- به یک منبع داده‌ی بودجه و یک منبع داده‌ی عملکرد واقعی وصل است.
- با تغییر بازه‌ی زمانی، همه‌ی اعداد و نمودارها خودشان تغییر می‌کنند.
- انحراف را متناسب با ماهیت هر حساب نشان می‌دهد، نه با یک قاعده‌ی یکسان.
برای مرور مفهوم انحراف بودجه می‌توانید راهنمای تحلیل بودجه در برابر عملکرد را هم ببینید.
آماده‌سازی داده: سه برگه‌ی ساده
فرض کنید فایل شما سه برگه دارد:
- بودجه: بودجه‌ی کامل سال برای صورت سود و زیان، با ماه‌ها در ستون‌ها.
- عملکرد و پیش‌بینی چرخشی: سه ماه اول سال با اعداد واقعی ثبت شده و ماه‌های باقی‌مانده هنوز پیش‌بینی است.
- داشبورد: برگه‌ی خالی که قرار است بسازیم.
گزارش‌های مالی معمولاً ماه‌ها را افقی نشان می‌دهند. این قالب برای خواندن خوب است، اما برای فیلتر و جمع‌زدن مناسب نیست. هدف ما این است که داده را به شکل «تاریخ در یک ستون و مبلغ در ستون دیگر» دربیاوریم.
باز کردن ستون‌ها با Power Query
به‌جای تبدیل داده به جدول اکسل، که ساختار را سفت و کم‌انعطاف می‌کند، برای محدوده‌ی بودجه و محدوده‌ی عملکرد «نام محدوده» (Named Range) تعریف کنید. سپس از مسیر Data > From Table/Range، همان محدوده‌ی نام‌دار را به Power Query بیاورید.
مراحل تبدیل به این ترتیب است:
- ستون‌های ماه را با عمل Unpivot به دو ستون «تاریخ» و «مبلغ» تبدیل کنید.
- نوع ستون تاریخ را درست تنظیم کنید.
- ردیف‌های جمع کل را فیلتر کنید تا مبلغ‌ها دوبار شمرده نشوند.
- ستونی اضافه کنید که مشخص کند هر ردیف «بودجه» است یا «عملکرد».
همین مراحل را برای مجموعه‌ی دوم هم تکرار کنید؛ Power Query امکان کپی‌کردن گام‌ها را می‌دهد. در پایان دو جدول را با Append روی هم بچینید. چون این جدول به برگه‌های اصلی وصل است، با تغییر بودجه یا عملکرد فقط کافی است Refresh بزنید. اگر می‌خواهید جایگاه این روش را در میان ابزارها بسنجید، مقایسه‌ی اکسل و Power BI کمک می‌کند.
محاسبه با SUMIFS و بازه‌ی زمانی
در برگه‌ی داشبورد دو سلول نام‌دار برای «تاریخ شروع» و «تاریخ پایان» بسازید تا کاربر بازه را خودش انتخاب کند. فرمول پایه SUMIFS است و سه شرط را هم‌زمان اعمال می‌کند:
- نوع داده: بودجه یا عملکرد
- گروه خلاصه: درآمد، بهای تمام‌شده، هزینه‌های عملیاتی و مانند آن
- بازه‌ی زمانی: بین تاریخ شروع و پایان
وقتی فرمول پایه درست کار کرد، آن را در کل جدول کپی کنید و شاخص‌های محاسبه‌شده را بسازید:
| شاخص | فرمول |
|---|---|
| سود ناخالص | درآمد منهای بهای تمام‌شده |
| حاشیه‌ی سود ناخالص | سود ناخالص تقسیم بر درآمد |
| سود عملیاتی | سود ناخالص منهای کل هزینه‌های عملیاتی |
| سود خالص | سود عملیاتی به‌علاوه‌ی خالص سایر درآمدها |
انحراف: جهت خوب و بد یکسان نیست
نکته‌ی ظریف داشبورد اینجاست. در حساب‌های درآمدی، عملکرد بالاتر از بودجه خبر خوبی است. در حساب‌های هزینه‌ای، عملکرد بالاتر از بودجه یعنی هزینه‌ی اضافه و خبر بد. پس نمی‌توان یک فرمول یکسان برای همه‌ی ردیف‌ها نوشت.
فرض کنید بودجه‌ی درآمد ماه ۱۰۰ میلیون و عملکرد ۱۱۰ میلیون است؛ انحراف مثبت ۱۰ میلیون یعنی «مطلوب». حالا فرض کنید بودجه‌ی هزینه‌ی تبلیغات ۴۰ میلیون و عملکرد ۴۶ میلیون است؛ همان تفاوت مثبت اینجا ۶ میلیون «نامطلوب» است. راه‌حل این است که علامت انحراف را بر اساس نوع حساب برگردانید یا یک ستون «مطلوب/نامطلوب» بسازید. برای تکمیل این نگاه، انحراف درصدی گمراه‌کننده است را بخوانید.
نمودارهای دونات برای انحراف
برای نمایش بصری، از نمودار دونات استفاده می‌شود. یک جدول کمکی بسازید که انحراف مثبت و منفی را جدا کند و مقدار مکمل را هم حساب کند تا بخش «خالی» دونات شکل بگیرد. ترفندهای کاربردی:
- قالب‌بندی یک دونات را به‌عنوان الگو ذخیره کنید و برای بقیه استفاده کنید.
- در مرکز هر دونات یک کادر متن بگذارید که مبلغ انحراف و عبارت «محقق شد» یا «محقق نشد» را نشان دهد.
- با تغییر بازه‌ی زمانی، همه‌ی دونات‌ها همراه هم به‌روز می‌شوند.
انتخاب شاخص‌های مناسب برای نمایش هم بحث جداگانه‌ای دارد؛ انتخاب شاخص‌های داشبورد مالی را ببینید.
پرسش‌های متداول
چرا داده را به جدول اکسل تبدیل نکنیم؟
جدول اکسل ساختار را سفت می‌کند و اگر قالب گزارش عوض شود، دردسر می‌سازد. محدوده‌ی نام‌دار انعطاف بیشتری می‌دهد و Power Query هم آن را می‌خواند.
Unpivot دقیقاً چه کاری می‌کند؟
ستون‌های افقی ماه را به دو ستون تاریخ و مبلغ تبدیل می‌کند. داده‌ی عمودی برای SUMIFS و فیلتر بسیار راحت‌تر است.
چرا ردیف‌های جمع کل را حذف می‌کنیم؟
اگر هم ردیف‌های جزئی و هم جمع کل در داده بمانند، SUMIFS مبلغ‌ها را دوبار می‌شمارد و نتیجه غلط می‌شود.
انحراف مثبت همیشه خوب است؟
خیر. در درآمد خوب است، اما در هزینه یعنی خرج بیش از برنامه. جهت را باید بر اساس نوع حساب تعیین کرد.
جمع‌بندی
داشبورد بودجه در برابر عملکرد از سه بخش ساخته می‌شود: داده‌ی عمودی‌شده با Power Query، محاسبه‌ی مبلغ‌ها با SUMIFS و بازه‌ی زمانی انتخابی، و انحرافی که جهت خوب و بد را بر اساس نوع حساب تشخیص می‌دهد. وقتی این سه کنار هم باشند، گزارش ماهانه از یک کار دستی تکراری به یک تازه‌سازی ساده تبدیل می‌شود و وقت شما صرف تفسیر عددها می‌شود، نه ساختن جدول.