مدیریت حساب‌های دریافتنی و پرداختنی در اکسل : چرا مدیریت مطالبات و بدهی‌ها اهمیت دارد؟

فرض کنید یک فروشگاه به چند مشتری کالا فروخته و قرار است مبلغ فاکتورها را در ماه آینده دریافت کند. هم‌زمان، از چند تأمین‌کننده نیز کالا خریده و باید بخشی از بدهی خود را در تاریخ‌های مشخص پرداخت کند.

اگر اطلاعات این معاملات فقط در دفتر، پیام‌رسان یا چند فایل پراکنده ثبت شده باشد، احتمال فراموش‌شدن سررسیدها، ثبت تکراری پرداخت‌ها و اشتباه در محاسبه مانده حساب زیاد می‌شود.

اینجاست که اکسل می‌تواند به کمک حسابدار و مدیر کسب‌وکار بیاید.

با ساخت یک فایل اکسل مناسب می‌توانیم:

• مشخص کنیم هر مشتری چقدر به ما بدهکار است.
• مانده بدهی به هر تأمین‌کننده را محاسبه کنیم.
• تاریخ سررسید فاکتورها را کنترل کنیم.
• مطالبات و بدهی‌های معوق را شناسایی کنیم.
• گزارش ماهانه و فهرست پیگیری وصول مطالبات تهیه کنیم.

در این آموزش، مرحله‌به‌مرحله یک سیستم ساده و کاربردی برای مدیریت حساب‌های دریافتنی و پرداختنی در اکسل می‌سازیم.

حساب‌های دریافتنی و پرداختنی چیست؟

قبل از طراحی فایل، باید تفاوت این دو مفهوم را بدانیم.

حساب‌های دریافتنی (Accounts Receivable)

حساب‌های دریافتنی شامل مبالغی است که مشتریان بابت فروش کالا یا ارائه خدمات به کسب‌وکار بدهکار هستند و هنوز دریافت نشده‌اند.

مثال:

• فروش نسیه کالا به مشتری
• ارائه خدمات با پرداخت در آینده
• فاکتور صادرشده‌ای که هنوز تسویه نشده است

در یک کسب‌وکار معمولی، حساب‌های دریافتنی نشان‌دهنده مطالبات شرکت از مشتریان است.

حساب‌های پرداختنی (Accounts Payable)

حساب‌های پرداختنی شامل مبالغی است که کسب‌وکار بابت خرید کالا، خدمات یا سایر تعهدات تجاری باید به فروشندگان و تأمین‌کنندگان پرداخت کند.

مثال:

• خرید نسیه کالا از تأمین‌کننده
• دریافت خدمات با پرداخت در آینده
• فاکتور خریدی که هنوز تسویه نشده است

به زبان ساده:

طراحی فایل اکسل مدیریت دریافتنی‌ها و پرداختنی‌ها

برای اینکه فایل منظم و قابل توسعه باشد، پیشنهاد می‌کنیم چهار شیت بسازید:

1. Customers: فهرست مشتریان
2. Suppliers: فهرست تأمین‌کنندگان
3. Transactions: ثبت فاکتورها و دریافت‌ها و پرداخت‌ها
4. Reports: گزارش مانده حساب‌ها و سررسیدها

بهتر است اطلاعات هر معامله فقط یک بار در شیت Transactions ثبت شود و گزارش‌ها از روی همان اطلاعات ساخته شوند.

گام اول: ساخت فهرست مشتریان

در شیت Customers جدولی با ستون‌های زیر بسازید:

این فهرست کمک می‌کند هنگام ثبت فاکتور، نام مشتری به شکل یکسان وارد شود و اشتباه تایپی کاهش پیدا کند.

گام دوم: ساخت فهرست تأمین‌کنندگان

در شیت Suppliers جدولی مشابه زیر ایجاد کنید:

با داشتن فهرست جداگانه، می‌توانید اطلاعات مشتریان و تأمین‌کنندگان را مستقل از یکدیگر مدیریت کنید.

گام سوم: طراحی جدول اصلی تراکنش‌ها

در شیت Transactions جدول اصلی را ایجاد کنید.

پیشنهاد می‌شود ستون‌های زیر را داشته باشید:

مبالغ جدول صرفاً نمونه آموزشی هستند و واحد پول باید در فایل مشخص شود.

نکته مهم: این جدول برای ثبت اولیه فاکتورها طراحی شده است. در استفاده واقعی بهتر است پرداخت‌ها و دریافت‌های بعدی را به‌عنوان ردیف‌های مستقل ثبت کنید و با شماره فاکتور یا شناسه سند به فاکتور اصلی ارتباط دهید. این کار امکان ثبت چند پرداخت برای یک فاکتور را فراهم می‌کند.

تبدیل محدوده به جدول اکسل

محدوده اطلاعات را انتخاب کنید و کلیدهای زیر را بزنید:

Ctrl + T

سپس از قسمت Table Design نام جدول را Transactions قرار دهید.

جدول اکسل با اضافه‌شدن ردیف‌های جدید، فرمول‌ها و محدوده گزارش را راحت‌تر توسعه می‌دهد.

گام چهارم: محاسبه مانده حساب هر فاکتور

برای پیگیری هر فاکتور، یک ستون با عنوان «مانده فاکتور» اضافه کنید.

فرمول ساده:

=[@[مبلغ فاکتور]]-[@دریافت]

این فرمول زمانی مناسب است که دریافت هر فاکتور در همان ردیف ثبت شود.

برای حالتی که هر فاکتور چند دریافت یا پرداخت دارد، بهتر است دو جدول جداگانه داشته باشید:

• جدول Invoices برای فاکتورها
• جدول Settlements برای دریافت‌ها و پرداخت‌ها

در جدول Settlements ستون‌هایی مانند شماره فاکتور، تاریخ تسویه، نوع تسویه و مبلغ تسویه ایجاد کنید.

سپس برای محاسبه مجموع دریافت‌های مرتبط با هر فاکتور فروش:

=SUMIFS(Settlements[مبلغ],Settlements[شماره فاکتور],[@[شماره فاکتور]],Settlements[نوع تسویه],"دریافت")

برای محاسبه مجموع پرداخت‌های مرتبط با هر فاکتور خرید:

=SUMIFS(Settlements[مبلغ],Settlements[شماره فاکتور],[@[شماره فاکتور]],Settlements[نوع تسویه],"پرداخت")

اکنون مانده هر فاکتور از تفاضل مبلغ فاکتور و مجموع تسویه‌های آن به دست می‌آید.

گام پنجم: محاسبه مانده حساب مشتریان

در شیت Reports فهرست مشتریان را قرار دهید.

فرض کنید نام مشتری در سلول A2 نوشته شده است و جدول فاکتورها Invoices نام دارد.

برای محاسبه مجموع فاکتورهای فروش مشتری:

=SUMIFS(Invoices[مبلغ],Invoices[طرف حساب],A2,Invoices[نوع معامله],"فروش")

برای محاسبه مجموع دریافت‌های مشتری:

=SUMIFS(Settlements[مبلغ],Settlements[طرف حساب],A2,Settlements[نوع تسویه],"دریافت")

سپس مانده مطالبات مشتری:

=مجموع_فاکتورهای_فروش-مجموع_دریافت‌ها

اگر نتیجه مثبت باشد، مشتری هنوز به کسب‌وکار بدهکار است؛ اگر صفر باشد، مانده فاکتورهای ثبت‌شده تسویه شده‌اند.

توجه: در یک سیستم کامل حسابداری، برگشت از فروش، تخفیف، اصلاح فاکتور، پیش‌دریافت و مانده‌های بستانکار مشتری نیز باید در محاسبه لحاظ شوند. فرمول ساده بالا برای حالت پایه و بدون این تعدیلات است.

گام ششم: محاسبه مانده حساب تأمین‌کنندگان

برای کنترل بدهی به تأمین‌کنندگان نیز می‌توان از روش مشابه استفاده کرد.

مجموع فاکتورهای خرید یک تأمین‌کننده:

=SUMIFS(Invoices[مبلغ],Invoices[طرف حساب],A2,Invoices[نوع معامله],"خرید")

مجموع پرداخت‌ها به تأمین‌کننده:

=SUMIFS(Settlements[مبلغ],Settlements[طرف حساب],A2,Settlements[نوع تسویه],"پرداخت")

مانده بدهی:

=مجموع_فاکتورهای_خرید-مجموع_پرداخت‌ها

اگر نتیجه مثبت باشد، مبلغی به تأمین‌کننده بدهکار هستیم.

در صورت وجود پیش‌پرداخت، برگشت از خرید یا اصلاح فاکتور، باید این موارد نیز در گزارش لحاظ شوند تا مانده واقعی به دست آید.

گام هفتم: شناسایی فاکتورهای سررسیدشده

یکی از قابلیت‌های مهم فایل مدیریت دریافتنی‌ها و پرداختنی‌ها، شناسایی فاکتورهایی است که موعد پرداخت یا دریافت آن‌ها فرا رسیده است.

فرض کنید:

• تاریخ سررسید در ستون سررسید قرار دارد.
• مانده فاکتور در ستون مانده قرار دارد.

برای نمایش وضعیت فاکتور می‌توانید از فرمول زیر استفاده کنید:

=IF([@مانده]<=0,"تسویه شده",IF([@سررسید]<TODAY(),"معوق","در انتظار سررسید"))

این فرمول سه وضعیت ایجاد می‌کند:

• تسویه شده
• معوق
• در انتظار سررسید

نکته درباره تاریخ شمسی: تابع TODAY() تاریخ جاری سیستم را به‌صورت تاریخ قابل‌پردازش اکسل برمی‌گرداند. اگر تاریخ سررسید به شکل متن شمسی وارد شده باشد، مقایسه مستقیم آن با TODAY() درست عمل نمی‌کند. برای استفاده از این فرمول، تاریخ‌ها باید به شکل تاریخ واقعی اکسل ذخیره شوند یا یک ستون تاریخ استاندارد قابل‌محاسبه داشته باشید.

گام هشتم: هایلایت کردن بدهی‌ها و مطالبات معوق

برای اینکه فاکتورهای معوق سریع دیده شوند، از Conditional Formatting استفاده کنید.

مراحل:

1. ستون وضعیت را انتخاب کنید.
2. وارد تب Home شوید.
3. گزینه Conditional Formatting را انتخاب کنید.
4. روی Highlight Cells Rules و سپس Text that Contains کلیک کنید.
5. عبارت «معوق» را وارد کنید.
6. قالب رنگی موردنظر را انتخاب کنید.

می‌توانید برای وضعیت «تسویه شده» و «در انتظار سررسید» نیز قالب‌های جداگانه تعریف کنید.

گام نهم: ساخت گزارش سنی مطالبات (Aging Report)

گزارش سنی مطالبات نشان می‌دهد چه مدت از سررسید فاکتورهای تسویه‌نشده گذشته است.

یک جدول گزارش با دسته‌بندی‌های زیر بسازید:

اگر تاریخ سررسید در ستون سررسید و مانده در ستون مانده باشد، می‌توانید با یک ستون کمکی تعداد روزهای تأخیر را محاسبه کنید:

=IF([@مانده]<=0,0,MAX(0,TODAY()-[@سررسید]))

این فرمول برای تاریخ‌های واقعی اکسل کاربرد دارد.

سپس با توجه به تعداد روزهای تأخیر، گروه سنی را مشخص کنید:

=IF([@مانده]<=0,"تسویه شده",IF([@روزهای تاخیر]=0,"سررسیدنشده",IF([@روزهای تاخیر]<=30,"۱ تا ۳۰ روز",IF([@روزهای تاخیر]<=60,"۳۱ تا ۶۰ روز",IF([@روزهای تاخیر]<=90,"۶۱ تا ۹۰ روز","بیش از ۹۰ روز")))))

در نهایت می‌توانید با SUMIFS مجموع مانده هر گروه را محاسبه کنید.

برای نمونه، مجموع مطالبات معوق بین ۱ تا ۳۰ روز:

=SUMIFS(Invoices[مانده],Invoices[گروه سنی],"۱ تا ۳۰ روز")

این گزارش به شما کمک می‌کند پیگیری وصول مطالبات را بر اساس میزان و مدت تأخیر اولویت‌بندی کنید.

گام دهم: ساخت داشبورد دریافتنی‌ها و پرداختنی‌ها

برای مشاهده سریع وضعیت مالی، در شیت Reports چند شاخص اصلی قرار دهید:

• مجموع مطالبات مشتریان
• مجموع بدهی به تأمین‌کنندگان
• تعداد فاکتورهای تسویه‌نشده
• مجموع مطالبات معوق
• مجموع بدهی‌های سررسیدشده
• مبلغ مطالبات بیش از ۹۰ روز
• نزدیک‌ترین تاریخ سررسید

برای نمایش این اطلاعات می‌توانید از نمودار ستونی، نمودار دایره‌ای و Conditional Formatting استفاده کنید.

بهتر است گزارش مطالبات و بدهی‌ها در دو بخش جداگانه نمایش داده شود تا جهت مانده‌ها با هم اشتباه نشود.

۷ نکته برای حرفه‌ای‌تر شدن فایل اکسل

۱. برای هر فاکتور شناسه یکتا تعریف کنید

شماره فاکتور باید یکتا باشد تا دریافت یا پرداخت اشتباهاً به فاکتور دیگری متصل نشود.

۲. دریافت‌ها و پرداخت‌ها را جداگانه ثبت کنید

به‌جای تغییر مبلغ فاکتور پس از هر تسویه، بهتر است رویدادهای تسویه در جدول مستقل ثبت شوند. این کار سابقه پرداخت‌ها را حفظ می‌کند.

۳. از Data Validation استفاده کنید

برای نوع معامله، نوع تسویه و نام طرف حساب فهرست کشویی بسازید تا اطلاعات یکدست ثبت شوند.

۴. تاریخ سررسید را جدی بگیرید

بدون تاریخ سررسید، تشخیص بدهی‌های معوق و اولویت‌بندی پیگیری‌ها دشوار می‌شود.

۵. گزارش‌ها را دوره‌ای کنترل کنید

مانده مشتریان و تأمین‌کنندگان را با اسناد و گردش حساب مربوط تطبیق دهید.

۶. فرمول‌ها را قفل کنید

سلول‌های محاسباتی را از ورود اطلاعات جدا کنید و از تغییر ناخواسته فرمول‌ها جلوگیری کنید.

۷. نسخه پشتیبان داشته باشید

سوابق فاکتورها و تسویه‌ها اطلاعات مهم مالی هستند. از فایل در بازه‌های منظم نسخه پشتیبان تهیه کنید.

اشتباهات رایج در مدیریت حساب‌های دریافتنی و پرداختنی

• ثبت یک فاکتور بیش از یک بار
• ثبت‌نکردن بخشی از دریافت یا پرداخت
• اتصال تسویه به شماره فاکتور اشتباه
• محاسبه مانده بدون درنظرگرفتن برگشت از خرید و فروش
• فراموش‌کردن پیش‌پرداخت‌ها
• اشتباه گرفتن بدهی با هزینه و طلب با درآمد
• واردکردن تاریخ شمسی به‌صورت متن و استفاده مستقیم از فرمول‌های تاریخ
• حذف سوابق تسویه پس از پرداخت کامل فاکتور

سوالات متداول درباره مدیریت حساب‌های دریافتنی و پرداختنی در اکسل

۱. برای محاسبه مانده مشتری در اکسل از چه تابعی استفاده کنیم؟

تابع SUMIFS گزینه مناسبی است. با این تابع می‌توان مجموع فاکتورهای فروش و دریافت‌های هر مشتری را جداگانه محاسبه و از هم کم کرد.

۲. آیا می‌توان چند پرداخت را به یک فاکتور متصل کرد؟

بله. کافی است برای هر پرداخت یک ردیف مستقل در جدول تسویه ثبت کنید و شماره فاکتور مربوط را در آن وارد کنید. سپس با SUMIFS مجموع پرداخت‌های هر فاکتور را محاسبه کنید.

۳. گزارش سنی مطالبات چه کاربردی دارد؟

این گزارش مطالبات را بر اساس مدت‌زمان گذشته از سررسید دسته‌بندی می‌کند و کمک می‌کند مطالبات قدیمی‌تر و پرریسک‌تر زودتر پیگیری شوند.

۴. آیا اکسل برای مدیریت بدهی و مطالبات کافی است؟

برای کسب‌وکارهای کوچک با تعداد محدود فاکتور و گردش اطلاعات قابل‌کنترل، اکسل می‌تواند ابزار مناسبی باشد. اما با افزایش تعداد کاربران، حجم تراکنش‌ها و نیازهای کنترلی، استفاده از نرم‌افزار حسابداری یا سیستم یکپارچه می‌تواند مطمئن‌تر باشد.

۵. تفاوت حساب دریافتنی و درآمد چیست؟

درآمد زمانی شناسایی می‌شود که شرایط شناسایی درآمد طبق اصول حسابداری برقرار باشد؛ اما حساب دریافتنی نشان‌دهنده حق دریافت وجه از مشتری است. این دو مفهوم مرتبط‌اند، اما یکسان نیستند.

۶. تفاوت حساب پرداختنی و هزینه چیست؟

هزینه به مصرف منابع یا خدمات مربوط است؛ حساب پرداختنی نشان‌دهنده تعهد پرداخت به فروشنده یا طرف حساب است. همه پرداختنی‌ها لزوماً هزینه نیستند؛ برای مثال خرید نسیه دارایی می‌تواند یک بدهی ایجاد کند.

جمع‌بندی: مطالبات و بدهی‌ها را با یک فایل اکسل منظم کنترل کنید

مدیریت حساب‌های دریافتنی و پرداختنی یکی از بخش‌های مهم کنترل مالی هر کسب‌وکار است. اگر فاکتورها، دریافت‌ها، پرداخت‌ها و سررسیدها به‌درستی ثبت شوند، می‌توان وضعیت مطالبات از مشتریان و بدهی به تأمین‌کنندگان را در هر زمان بررسی کرد.

با استفاده از جدول‌های اکسل، تابع SUMIFS، فهرست‌های کشویی، Conditional Formatting و گزارش سنی مطالبات می‌توانید یک سیستم ساده و کاربردی بسازید.

مهم‌ترین نکته این است که فاکتور و تسویه را از هم جدا نگه دارید؛ چون یک فاکتور ممکن است در چند مرحله تسویه شود. با این روش، سابقه معاملات حفظ می‌شود و گزارش مانده حساب‌ها دقت بیشتری خواهد داشت.