داشبورد مالی پویا در اکسل؛ هشت تابع برای گزارش خودکار
داشبورد مالی پویا در اکسل داشبوردی است که با ورود داده‌ی جدید از صورت‌های مالی تفصیلی، خودش به‌روز می‌شود. برای ساختش به ابزار پیچیده نیاز ندارید؛ هشت تابع کافی است: EOMONTH، YEAR، MONTH، ROUNDUP، DATE، IF، SUMIFS و XLOOKUP. در این راهنما ببینید هر تابع چه نقشی دارد و چگونه کنار هم قرار می‌گیرند.
ایده‌ی کلی؛ از صورت تفصیلی به نمای مدیریتی
فرض کنید از نرم‌افزار حسابداری، صورت سود و زیان و ترازنامه‌ی ماهانه را به اکسل می‌آورید. هدف داشبوردی است که این نماها را نشان دهد:
- از ابتدای سال تا امروز
- ۱۲ ماه گذشته
- سالانه
- فصلی
- ماهانه
نکته‌ی اصلی این است که همه‌ی نماها از یک منبع تغذیه می‌شوند. با اضافه‌شدن داده‌ی ماه جدید، فقط داده‌ی خام را جایگزین می‌کنید و بقیه خودکار تغییر می‌کند.
ساخت ساختار دوره‌ها با EOMONTH
هر داشبورد با ردیف تاریخ‌ها شروع می‌شود. تابع EOMONTH(تاریخ، تعداد ماه) آخرین روز ماه را برمی‌گرداند:
- عدد صفر: پایان ماه جاری
- عدد ۱: پایان ماه بعد
- عدد منفی ۱: پایان ماه قبل
وقتی تاریخ پایان دوره را دارید، تاریخ اولین روز ماه بعد را با یک واحد افزودن به نتیجه‌ی EOMONTH می‌گیرید. اگر این ترکیب را در ستون‌ها کپی کنید، بازه‌های زمانی پویا دارید.
تابع‌های YEAR، MONTH و ساخت فصل
YEAR(تاریخ) سال و MONTH(تاریخ) شماره‌ی ماه را جدا می‌کنند. ساده‌اند اما قدرتمند، چون برای برچسب‌گذاری ستون‌ها لازم‌اند.
اکسل تابع آماده‌ی فصل ندارد، ولی می‌توانید با MONTH و ROUNDUP آن را بسازید: شماره‌ی ماه را بر ۳ تقسیم و گرد به بالا کنید.
- ماه ۱: ۱ ÷ ۳ تقریباً ۰٫۳۳، گرد به بالا برابر ۱ (فصل اول)
- ماه ۴: ۴ ÷ ۳ تقریباً ۱٫۳۳، گرد به بالا برابر ۲ (فصل دوم)
اگر حرف «Q» را با اتصال متن جلوی نتیجه بگذارید، برچسب فصل پویا (مثل Q2) ساخته می‌شود. برای تقویم شمسی، کافی است ماه‌های شمسی را به همان شیوه گروه‌بندی کنید.
تابع IF؛ واقعی یا پیش‌بینی؟
IF(شرط، مقدار اگر درست، مقدار اگر نادرست) پرکاربردترین تابع است. در داشبورد از آن برای نشان‌دادن اینکه هر دوره داده‌ی واقعی دارد یا پیش‌بینی استفاده می‌شود.
منطق ساده است: یک نام تعریف‌شده برای «آخرین ماه داده‌ی واقعی» دارید. اگر تاریخ دوره کوچک‌تر یا مساوی آن بود، «واقعی» نمایش داده می‌شود؛ وگرنه «پیش‌بینی». بدین‌ترتیب خواننده بی‌درنگ می‌فهمد کدام اعداد قطعی‌اند.
SUMIFS برای صورت سود و زیان
اینجا به بخش اصلی گزارشگری می‌رسیم. SUMIFS(محدوده‌ی جمع، محدوده‌ی شرط اول، شرط اول، …) اعداد را با چند شرط جمع می‌زند.
صورت سود و زیان فعالیت را در یک بازه نشان می‌دهد، پس باید مقادیر را بین تاریخ شروع و پایان جمع بزنید:
- برای ماه: داده‌های همان ماه
- برای فصل: جمع سه ماه
- برای سال: جمع دوازده ماه
اگر ارجاع‌های سلولی را درست قفل کنید، همان فرمول را در همه‌ی دوره‌ها و همه‌ی ردیف‌ها کپی می‌کنید.
XLOOKUP برای ترازنامه
ترازنامه با سود و زیان فرق دارد. سود و زیان جریان است؛ ترازنامه موجودی در یک لحظه. بنابراین نباید جمع زد؛ باید مقدار را در تاریخ پایان دوره جست‌وجو کرد.
XLOOKUP(مقدار جست‌وجو، آرایه‌ی جست‌وجو، آرایه‌ی بازگشتی) تاریخ پایان را در ردیف تاریخ‌های ماهانه پیدا می‌کند و مقدار متناظر ترازنامه را برمی‌گرداند. از VLOOKUP انعطاف‌پذیرتر و فهم آن آسان‌تر است. اگر می‌خواهید با گزینه‌های جست‌وجوی بیشتر آشنا شوید، فرمول‌های اکسل در مدل مالی را ببینید.
مثال عددی ساده
فرض کنید فروش ماهانه‌ی یک شرکت ۱۰۰، ۱۲۰ و ۱۴۰ میلیون تومان در سه ماه اول سال است و موجودی نقد پایان همین ماه‌ها ۵۰، ۴۰ و ۶۰ میلیون.
| نما | روش | نتیجه |
|---|---|---|
| فروش فصل اول | SUMIFS بین سه ماه | ۳۶۰ |
| موجودی نقد پایان فصل | XLOOKUP تاریخ پایان فروردین تا خرداد | ۶۰ |
جمع‌زدن موجودی نقد سه ماه (۱۵۰) بی‌معناست، چون موجودی یک «عکس لحظه‌ای» است. همین تفاوت مهم‌ترین نکته‌ی مفهومی داشبورد است.
نمایش نهایی
داشبورد را می‌توان به‌صورت تصویر در ارائه چسباند یا کل برگه را به PDF تبدیل کرد. ولی ارزش واقعی در این است که تحلیلگر بتواند داده‌ی درهم‌ریخته‌ی خروجی نرم‌افزار حسابداری را در چند دقیقه به گزارش آماده‌ی مدیریتی بدل کند. برای انتخاب شاخص‌های روی داشبورد انتخاب شاخص‌های درست داشبورد مالی و برای قاب‌بندی نهایی داشبورد پایان فصل را بخوانید.
پرسش‌های متداول
چرا برای سود و زیان از SUMIFS و برای ترازنامه از XLOOKUP استفاده می‌کنیم؟
سود و زیان مجموع فعالیت یک بازه است، اما ترازنامه موجودی یک تاریخ مشخص است و جمع‌زدنش مفهومی ندارد.
آیا بدون تابع آماده‌ی فصل می‌توان فصل ساخت؟
بله؛ شماره‌ی ماه را بر ۳ تقسیم کنید و با ROUNDUP گرد به بالا کنید.
XLOOKUP در نسخه‌های قدیمی اکسل کار می‌کند؟
خیر؛ تابعی جدید است و در نسخه‌های قدیمی وجود ندارد. در آن نسخه‌ها از ترکیب INDEX و MATCH استفاده کنید.
جمع‌بندی
داشبورد مالی پویا از سه بخش ساخته می‌شود: ساختار تاریخ با EOMONTH و توابع تاریخ، منطق وضعیت با IF و ارجاع داده با SUMIFS برای جریان‌ها و XLOOKUP برای موجودی‌ها. وقتی این ساختار را درست بچینید، با هر بار ورود داده‌ی جدید گزارش آماده است و وقتتان صرف تحلیل می‌شود.