به‌روزرسانی مدل مالی با اعداد واقعی؛ دو فرمول ساده
هر ماه که اعداد واقعی جدید از نرم‌افزار حسابداری می‌رسد، باید مدل پیش‌بینی را با آن‌ها به‌روز کنید. به‌روزرسانی مدل مالی با اعداد واقعی ظاهراً کپی و جای‌گذاری است، اما اگر فهرست حساب‌ها تغییر کرده باشد، یک ردیف جابه‌جا می‌شود و همه‌ی اعداد پایین‌تر به حساب اشتباه می‌نشینند. این نوشته روشی را شرح می‌دهد که فقط دو فرمول لازم دارد.
چرا به‌روزرسانی با اعداد واقعی دردسر دارد؟
مشکل اصلی تغییر سرفصل حساب‌هاست. گاهی حسابدار یک حساب تازه باز می‌کند؛ مثلاً «هزینه‌ی کمیسیون» که ماه قبل وجود نداشت. خروجی نرم‌افزار حسابداری حالا یک ردیف بیشتر دارد. اگر داده‌ی جدید را مستقیم روی داده‌ی قبلی بچسبانید، از آن ردیف به بعد هر عدد یک خانه پایین می‌افتد و مدل بی‌صدا اشتباه می‌شود.
خطرناک‌تر اینکه این خطا اغلب خطای فرمول نشان نمی‌دهد. جمع‌ها هنوز محاسبه می‌شوند، فقط عددها به حساب دیگری تعلق دارند. بنابراین باید پیش از چسباندن داده، مطمئن شوید فهرست حساب‌ها در دو مجموعه دقیقاً یکی است.
ساختار داده را ثابت نگه دارید
پیش از هر فرمولی یک قاعده‌ی ساده وجود دارد: قالب داده‌ی منبع را همان‌طور که از سیستم خارج می‌شود نگه دارید. هر فایل اکسلی که از منبع بیرونی داده می‌گیرد، باید ورودی‌اش مثل خروجی اصلی باشد. اگر ستون‌ها را دستی جابه‌جا یا ردیف‌ها را مرتب کنید، در ماه بعد باید همان کارهای تکراری را دوباره انجام دهید.
بهتر است داده‌ی خام دست‌نخورده بماند و تغییر قالب را فرمول‌ها انجام دهند. برای یک قدم جلوتر می‌توانید از Power Query استفاده کنید تا داده‌ی خام در هر بار به‌روزرسانی به‌صورت خودکار به شکل موردنیاز مدل تبدیل شود. این اصل هم برای مدل پیش‌بینی صادق است و هم برای داشبورد.
با این حال همیشه نمی‌توان از تغییر قالب پرهیز کرد؛ حساب تازه همیشه ممکن است بیاید. اینجا دو فرمول وارد می‌شوند.
فرمول اول: مقایسه با علامت مساوی
فرض کنید مدل با اعداد واقعی تیر به‌روز شده و حالا نوبت مرداد است. داده‌ی قدیمی و جدید را موقتاً کنار هم بگذارید؛ در عمل معمولاً در دو برگه یا دو فایل‌اند. در ستونی کنار آن‌ها نام هر حساب قدیمی را با نام حساب هم‌ردیف در داده‌ی جدید مقایسه کنید:
=B2=E2
نتیجه TRUE یا FALSE است. تا وقتی همه‌ی ردیف‌ها TRUEاند، ساختار یکی است. نخستین FALSE نشان می‌دهد از کجا فهرست‌ها از هم جدا شده‌اند. در مثال بالا اولین ناهماهنگی در «هزینه‌ی کمیسیون» دیده می‌شود.
راه‌حل ساده است: در داده‌ی منبع مدل یک ردیف تازه برای آن حساب اضافه کنید و دوباره مقایسه کنید. این کار را تا زمانی که همه‌ی ردیف‌ها منطبق شوند ادامه دهید. وقتی همه‌چیز هماهنگ شد، داده‌ی جدید را بچسبانید و همه‌ی ردیف‌ها درست قرار می‌گیرند.
این روش سریع است و نیاز به دانش خاصی ندارد. نقطه‌ضعفش وقتی پیدا می‌شود که فهرست حساب‌ها صدها ردیف باشد و هشت حساب تازه داشته باشید: هشت بار مقایسه و درج، خسته‌کننده و پرخطاست.
فرمول دوم: COUNTIF برای یافتن همه‌ی حساب‌های تازه
به‌جای مقایسه‌ی ردیف‌به‌ردیف می‌توانید همه‌ی حساب‌های جدید را با یک ستون پیدا کنید. تابع COUNTIF می‌شمارد هر مقدار چند بار در یک محدوده آمده است. اگر برای هر حساب در داده‌ی جدید بشمارید که در داده‌ی قدیمی چند بار هست، پاسخ یکی از دو حالت است:
- عدد ۱: حساب پیشتر وجود داشته است.
- عدد ۰: حساب تازه است.
=COUNTIF($B$2:$B$120, E2)
برای خواناتر شدن می‌توانید >0 را به انتهای فرمول اضافه کنید تا خروجی TRUE یا FALSE شود:
=COUNTIF($B$2:$B$120, E2)>0
حالا هر FALSE یک حساب تازه است. با فیلتر کردن ستون روی FALSE همه‌ی حساب‌های جدید را در چند ثانیه می‌بینید، حتی اگر فهرست بیش از صد ردیف باشد.
محدوده را قفل کنید
علامت دلار در $B$2:$B$120 عمدی است. اگر محدوده قفل نشود، با پایین کشیدن فرمول جابه‌جا می‌شود و بخشی از فهرست قدیمی از بررسی خارج می‌ماند. پیش از کپی‌کردن فرمول حتماً کلید F4 را بزنید.
مراحل عملی به‌روزرسانی ماهانه
با کنار هم گذاشتن دو فرمول می‌توان یک روال ثابت ساخت:
- داده‌ی جدید را بدون هیچ تغییری از نرم‌افزار حسابداری خارج کنید.
- آن را در کنار داده‌ی ماه قبل بگذارید.
- ستون COUNTIF را بنویسید و حساب‌های دارای مقدار FALSE را فیلتر کنید.
- هر حساب تازه را در جای درست ساختار مدل اضافه کنید و فرمول‌های آن را از ردیف مجاور کپی کنید.
- ستون مقایسه با علامت مساوی را اجرا کنید تا همه‌ی ردیف‌ها TRUE شوند.
- داده‌ی جدید را جایگزین کنید و جمع‌های مدل را با جمع منبع تطبیق دهید.
گام آخر مهم است. جمع کل سود و زیان و ترازنامه در مدل باید با جمع همان گزارش در نرم‌افزار حسابداری برابر باشد. اگر اختلاف داشت، برای دانستن ریشه‌ی آن به کنترل خطا در مدل مالی نگاه کنید.
مثال عددی ساده
فرض کنید فهرست ماه تیر ۴۰ حساب دارد و در مرداد حسابداری حساب «هزینه‌ی کمیسیون» را بین «حقوق» و «اجاره» اضافه کرده است. فهرست جدید ۴۱ ردیف دارد. اگر بدون بررسی بچسبانید، مبلغ اجاره در ردیف کمیسیون می‌نشیند و مبلغ هر حساب بعدی یک ردیف بالاتر یا پایین‌تر می‌افتد. ستون COUNTIF برای «هزینه‌ی کمیسیون» عدد ۰ می‌دهد، یعنی همان‌جا ردیف تازه لازم است. پس از درج ردیف، ۴۱ ردیف با هم برابرند و بدون جابه‌جایی چسبانده می‌شوند.
پیشنهادهایی برای کم‌کردن کار دستی
- حساب‌های تازه را در یک برگه‌ی یادداشت ثبت کنید تا در بازبینی معلوم باشد کی و چرا اضافه شده‌اند.
- فهرست حساب‌ها را ساده نگه دارید. ساختار کوتاه‌تر کار تطبیق را آسان می‌کند؛ نگاه کنید به ساده‌سازی سرفصل حساب‌ها.
- پیش از و پس از به‌روزرسانی، نسخه‌ها را مقایسه کنید؛ برای این کار مقایسه‌ی دو نسخه‌ی مدل مالی ایده‌هایی می‌دهد.
- اگر هر ماه همین کار را تکرار می‌کنید، گام‌های داده‌ورود را با Power Query خودکار کنید.
پرسش‌های متداول
به‌روزرسانی مدل با اعداد واقعی را چه زمانی باید انجام داد؟
بعد از بسته‌شدن دفاتر هر ماه، یعنی وقتی حسابداری اعداد را نهایی کرده است. به‌روزرسانی روی اعداد پیش‌نویس باعث تکرار کار می‌شود.
COUNTIF با حساب‌های هم‌نام اما با فاصله‌ی اضافی چه می‌کند؟
آن‌ها را متفاوت می‌بیند. اگر نتیجه عجیب بود، با تابع TRIM فاصله‌های اضافی را حذف کنید و دوباره بررسی کنید.
اگر حسابی حذف شده باشد چه؟
COUNTIF را برعکس هم اجرا کنید: هر حساب مدل قدیمی را در داده‌ی جدید بشمارید. مقدار ۰ یعنی آن حساب دیگر در منبع نیست.
آیا بدون اکسل هم همین روش جواب می‌دهد؟
بله. گوگل‌شیت هم COUNTIF دارد و منطق کار عیناً تکرار می‌شود.
جمع‌بندی
به‌روزرسانی مدل با اعداد واقعی وقتی دردسر می‌شود که فهرست حساب‌ها بی‌صدا تغییر کند. ساختار داده‌ی منبع را ثابت نگه دارید، با فرمول مساوی ناهماهنگی را ببینید و با COUNTIF همه‌ی حساب‌های تازه را یک‌جا بیابید. پس از آن جایگزینی داده ساده است و در پایان تطبیق جمع‌ها با منبع از درستی کار مطمئن‌تان می‌کند.