ساخت صورت جریان نقدی در اکسل
ساخت صورت جریان نقدی در اکسل : چرا باید جریان نقدی کسب‌وکار را کنترل کنیم؟...
آموزش حسابداری در اکسل
چطور در اکسل دفتر روزنامه و دفتر کل بسازیم؟ آموزش گام‌به‌گام با مثال
فهرست مطالب در این مقاله:
ساخت دفتر روزنامه و دفتر کل در اکسل : دفتر روزنامه و دفتر کل از ابزارهای پایه ثبت و طبقه‌بندی رویدادهای مالی هستند. اگر کسب‌وکار کوچکی دارید یا می‌خواهید اصول حسابداری را با اکسل تمرین کنید، می‌توانید با طراحی یک فایل منظم، اسناد حسابداری را ثبت کنید، گردش هر حساب را ببینید و توازن بدهکار و بستانکار را بررسی کنید.
در این آموزش، یک ساختار ساده و کاربردی برای دفتر روزنامه و دفتر کل در اکسل می‌سازیم. ابتدا اطلاعات سندها را در دفتر روزنامه ثبت می‌کنیم، سپس با فرمول‌های اکسل گردش حساب‌ها را در دفتر کل به دست می‌آوریم.
نکته مهم: این آموزش برای یادگیری و ساخت یک ابزار ثبت و گزارش‌گیری است. الزامات قانونی نگهداری دفاتر، قالب‌های مورد قبول مراجع مربوط، کنترل‌های داخلی و نیازهای گزارشگری کسب‌وکار خود را جداگانه بررسی کنید. یک فایل اکسل به‌تنهایی تضمین‌کننده رعایت همه الزامات حسابداری و قانونی نیست.
دفتر روزنامه و دفتر کل چه تفاوتی دارند؟
دفتر روزنامه رویدادهای مالی را به ترتیب تاریخ و در قالب اسناد حسابداری ثبت می‌کند. در هر سند، حساب‌های بدهکار و بستانکار و مبلغ مربوط مشخص می‌شوند.
دفتر کل اطلاعات ثبت‌شده را بر اساس حساب طبقه‌بندی می‌کند؛ برای مثال، گردش حساب صندوق، بانک، فروش یا هزینه اجاره را جداگانه نشان می‌دهد.
| ویژگی | دفتر روزنامه | دفتر کل |
|---|---|---|
| ترتیب اطلاعات | معمولاً بر اساس تاریخ و شماره سند | بر اساس حساب |
| هدف اصلی | ثبت رویداد مالی | نمایش گردش هر حساب |
| اطلاعات مهم | شماره سند، تاریخ، شرح، حساب و مبلغ | حساب، تاریخ، شماره سند و گردش بدهکار و بستانکار |
| کاربرد | ثبت اولیه رویدادها | بررسی مانده و گردش حساب‌ها |
در یک سیستم منظم، اطلاعات دفتر کل از ثبت‌های دفتر روزنامه استخراج می‌شود؛ بنابراین لازم نیست یک رویداد مالی را دو بار به‌صورت دستی وارد کنید.
مرحله اول: طراحی ساختار فایل اکسل
برای شروع، یک فایل اکسل با سه شیت ایجاد کنید:
دفتر روزنامهبرای ثبت اسناد حسابداریکدینگ حسابهابرای نگهداری کد و نام حسابهادفتر کلبرای مشاهده گردش حساب‌ها
در صورت نیاز، بعداً می‌توانید شیت‌های دیگری برای تراز آزمایشی، گزارش‌های مالی و تنظیمات فایل اضافه کنید.
مرحله دوم: ساخت شیت کدینگ حساب‌ها
در شیت کدینگ حساب‌ها، یک جدول با ستون‌های کد حساب، نام حساب و گروه حساب بسازید.
برای نمونه:
| کد حساب | نام حساب | گروه |
|---|---|---|
| 101 | صندوق | دارایی |
| 102 | بانک | دارایی |
| 201 | حساب‌های پرداختنی | بدهی |
| 301 | سرمایه | حقوق مالکانه |
| 401 | درآمد فروش | درآمد |
| 501 | هزینه اجاره | هزینه |
این کدها و حساب‌ها صرفاً نمونه آموزشی هستند؛ کدینگ واقعی باید با ساختار حسابداری کسب‌وکار شما هماهنگ باشد.
چرا کدینگ حساب‌ها مهم است؟
کدینگ مشخص می‌کند هر رویداد مالی به کدام حساب مربوط است. اگر نام یک حساب را در یک سند «بانک» و در سندی دیگر «حساب بانکی» بنویسید، ممکن است گزارش‌گیری بر اساس نام با مشکل مواجه شود.
برای جلوگیری از این مسئله:
- به هر حساب یک کد یکتا اختصاص دهید.
- نام حساب‌ها را استاندارد نگه دارید.
- در دفتر روزنامه از کدینگ معتبر استفاده کنید.
- حساب‌های جدید را از طریق جدول کدینگ اضافه کنید.
مرحله سوم: ساخت دفتر روزنامه
در شیت دفتر روزنامه، ستون‌های زیر را ایجاد کنید:
| ستون | عنوان | کاربرد |
|---|---|---|
| A | شماره سند | شناسایی سند حسابداری |
| B | تاریخ | تاریخ وقوع یا ثبت رویداد |
| C | شرح سند | توضیح رویداد مالی |
| D | کد حساب | کد حساب مربوط |
| E | نام حساب | نام حساب مربوط |
| F | بدهکار | مبلغ بدهکار |
| G | بستانکار | مبلغ بستانکار |
هر ردیف نشان‌دهنده یک قلم از سند حسابداری است. اگر یک رویداد مالی شامل دو حساب یا بیشتر باشد، برای هر قلم یک ردیف ثبت می‌شود و شماره سند در ردیف‌های مربوط تکرار می‌شود.
مثال: ثبت سرمایه‌گذاری اولیه در صندوق
فرض کنید مالک کسب‌وکار مبلغ ۱۰۰ میلیون تومان وجه نقد وارد کسب‌وکار می‌کند. ثبت ساده این رویداد، در صورت انطباق با ماهیت معامله، به شکل زیر است:
| شماره سند | تاریخ | شرح سند | کد حساب | نام حساب | بدهکار | بستانکار |
|---|---|---|---|---|---|---|
| 1 | 1405/07/01 | سرمایه‌گذاری اولیه | 101 | صندوق | 100,000,000 | 0 |
| 1 | 1405/07/01 | سرمایه‌گذاری اولیه | 301 | سرمایه | 0 | 100,000,000 |
در این ثبت، صندوق بدهکار و سرمایه بستانکار شده است. مجموع بدهکار و بستانکار سند هر دو برابر با ۱۰۰ میلیون تومان‌اند.
این تاریخ نمونه است. برای استفاده از امکانات تاریخ اکسل، توجه کنید که تاریخ شمسی واردشده به‌صورت متن ممکن است برای مرتب‌سازی زمانی و محاسبات تاریخ محدودیت ایجاد کند.
قانون مهم ثبت سند
برای هر سند حسابداری، مجموع بدهکار باید با مجموع بستانکار برابر باشد:
[
\text{جمع بدهکار}=\text{جمع بستانکار}
]
اگر سند توازن نداشته باشد، باید پیش از اتکا به گزارش‌های دفتر کل، علت اختلاف بررسی شود.
مرحله چهارم: محاسبه جمع بدهکار و بستانکار سند
می‌توانید در بخشی از شیت دفتر روزنامه یا در شیت کنترل، جمع ستون‌های بدهکار و بستانکار را محاسبه کنید.
اگر داده‌ها در ستون‌های F و G و ردیف‌های 2 تا 1000 باشند:
جمع بدهکار:
=SUM(F2:F1000)جمع بستانکار:
=SUM(G2:G1000)اختلاف بدهکار و بستانکار:
=SUM(F2:F1000)-SUM(G2:G1000)اگر اختلاف صفر باشد، جمع کلی این دو ستون برابر است. بااین‌حال، این کنترل به‌تنهایی ثابت نمی‌کند که تک‌تک اسناد درست ثبت شده‌اند؛ ممکن است یک سند نامتوازن با سند دیگری جبران شده باشد.
کنترل توازن هر سند به‌صورت جداگانه
فرض کنید شماره سند در ستون A، بدهکار در ستون F و بستانکار در ستون G قرار دارد. در شیت کنترل، شماره سند را در سلول A2 بنویسید و فرمول زیر را وارد کنید:
=SUMIF('دفتر روزنامه'!$A$2:$A$1000,A2,'دفتر روزنامه'!$F$2:$F$1000)-SUMIF('دفتر روزنامه'!$A$2:$A$1000,A2,'دفتر روزنامه'!$G$2:$G$1000)خروجی صفر یعنی مجموع بدهکار و بستانکار همان شماره سند برابر است. برای بررسی اسناد دیگر، شماره سند را در ردیف‌های بعدی قرار دهید و فرمول را کپی کنید.
مرحله پنجم: نمایش نام حساب بر اساس کد حساب
برای کاهش اشتباه تایپی، بهتر است کد حساب را از فهرست کدینگ انتخاب کنید و نام حساب به‌صورت خودکار نمایش داده شود.
اگر کد حساب در ستون D دفتر روزنامه و جدول کدینگ در شیت کدینگ حساب‌ها باشد، در سلول E2 می‌توانید از XLOOKUP استفاده کنید:
=XLOOKUP(D2,'کدینگ حساب‌ها'!$A$2:$A$100,'کدینگ حساب‌ها'!$B$2:$B$100,"کد نامعتبر")این فرمول کد حساب را در ستون A شیت کدینگ جست‌وجو می‌کند و نام متناظر را از ستون B برمی‌گرداند.
اگر نسخه اکسل شما از XLOOKUP پشتیبانی نمی‌کند، می‌توانید از VLOOKUP استفاده کنید:
=IFERROR(VLOOKUP(D2,'کدینگ حساب‌ها'!$A$2:$C$100,2,FALSE),"کد نامعتبر")برای جلوگیری از ثبت کدهای اشتباه، می‌توانید با قابلیت Data Validation یک فهرست کشویی از کدهای حساب ایجاد کنید.
مرحله ششم: ساخت دفتر کل بر اساس کد حساب
دفتر کل باید اطلاعات دفتر روزنامه را بر اساس حساب تفکیک کند. برای هر حساب، معمولاً تاریخ، شماره سند، شرح، گردش بدهکار، گردش بستانکار و در صورت نیاز مانده نمایش داده می‌شود.
برای مثال، دفتر کل حساب صندوق می‌تواند چنین ساختاری داشته باشد:
| تاریخ | شماره سند | شرح | بدهکار | بستانکار |
|---|---|---|---|---|
| 1405/07/01 | 1 | سرمایه‌گذاری اولیه | 100,000,000 | 0 |
| … | … | … | … | … |
این جدول تنها نمونه‌ای از گردش حساب است؛ در فایل واقعی، همه ثبت‌های مربوط به حساب صندوق باید از دفتر روزنامه استخراج شوند.
روش اول: ساخت دفتر کل با فیلتر
اگر حجم داده‌ها کم است، می‌توانید در شیت دفتر روزنامه از فیلتر ستون کد حساب استفاده کنید و فقط ردیف‌های حساب موردنظر را نمایش دهید.
مراحل:
- داخل جدول دفتر روزنامه کلیک کنید.
- با
Ctrl + Shift + Lفیلتر را فعال کنید. - از ستون کد حساب، کد حساب موردنظر را انتخاب کنید.
- ردیف‌های نمایش‌داده‌شده را بررسی کنید.
این روش برای مشاهده سریع گردش حساب مناسب است، اما برای ساخت گزارش‌های خودکار و چندحسابی، روش‌های مبتنی بر فرمول یا PivotTable کاربردی‌ترند.
روش دوم: محاسبه گردش حساب با SUMIFS
فرض کنید:
- کد حساب در ستون D دفتر روزنامه است.
- بدهکار در ستون F است.
- بستانکار در ستون G است.
- کد حساب موردنظر در سلول
B2شیت دفتر کل قرار دارد.
برای محاسبه مجموع بدهکار حساب:
=SUMIFS('دفتر روزنامه'!$F$2:$F$1000,'دفتر روزنامه'!$D$2:$D$1000,$B2)برای محاسبه مجموع بستانکار همان حساب:
=SUMIFS('دفتر روزنامه'!$G$2:$G$1000,'دفتر روزنامه'!$D$2:$D$1000,$B2)با این فرمول‌ها، گردش بدهکار و بستانکار هر حساب به‌صورت خودکار محاسبه می‌شود. کافی است کد حساب دیگری را در سلول B2 قرار دهید تا نتیجه برای آن حساب محاسبه شود.
روش سوم: نمایش ریز گردش حساب با FILTER
در نسخه‌های جدید اکسل که تابع FILTER را پشتیبانی می‌کنند، می‌توانید تمام ردیف‌های مربوط به یک حساب را به‌صورت پویا نمایش دهید.
اگر محدوده داده‌های دفتر روزنامه A2:G1000 و کد حساب انتخاب‌شده در B2 باشد، فرمول زیر را در محل شروع گزارش وارد کنید:
=FILTER('دفتر روزنامه'!A2:G1000,'دفتر روزنامه'!D2:D1000=B2,"گردشی ثبت نشده است")این فرمول ردیف‌هایی را نمایش می‌دهد که کد حساب آن‌ها با مقدار B2 برابر است. در نسخه‌هایی که از FILTER پشتیبانی نمی‌کنند، می‌توانید از فیلتر معمولی یا PivotTable استفاده کنید.
مرحله هفتم: محاسبه مانده حساب در دفتر کل
مانده حساب از تفاوت گردش بدهکار و بستانکار به دست می‌آید؛ اما جهت مانده به ماهیت حساب و مانده ابتدای دوره بستگی دارد.
اگر برای یک حساب، مانده ابتدای دوره در سلول B3 ثبت شده و علامت مثبت را برای مانده بدهکار در نظر گرفته‌اید، می‌توانید مانده پایان دوره را به این صورت محاسبه کنید:
=B3+C3-D3در این مثال:
B3مانده ابتدای دوره با علامت مثبت برای بدهکار و منفی برای بستانکار است.C3گردش بدهکار دوره است.D3گردش بستانکار دوره است.
نتیجه مثبت نشان‌دهنده مانده بدهکار و نتیجه منفی نشان‌دهنده مانده بستانکار بر اساس این قرارداد علامت است.
توجه: برای ارائه گزارش قابل‌فهم، بهتر است مبلغ مانده و جهت آن را جداگانه نمایش دهید. همچنین، مانده ابتدای دوره باید مطابق انتقال مانده‌ها و ماهیت حساب ثبت شده باشد.
مرحله هشتم: ساخت تراز آزمایشی برای کنترل حساب‌ها
پس از استخراج گردش حساب‌ها، می‌توانید یک شیت به نام تراز آزمایشی ایجاد کنید.
ساختار نمونه:
| کد حساب | نام حساب | جمع بدهکار | جمع بستانکار |
|---|---|---|---|
| 101 | صندوق | … | … |
| 102 | بانک | … | … |
| 201 | حساب‌های پرداختنی | … | … |
| 301 | سرمایه | … | … |
| 401 | درآمد فروش | … | … |
| 501 | هزینه اجاره | … | … |
برای هر حساب، مجموع بدهکار و بستانکار را با فرمول‌های SUMIFS محاسبه کنید. سپس در پایین جدول، جمع دو ستون را به دست آورید:
=SUM(C2:C100)و:
=SUM(D2:D100)برابری جمع بدهکار و بستانکار تراز آزمایشی، یکی از کنترل‌های پایه حسابداری است؛ اما همه خطاهای ثبت را شناسایی نمی‌کند. برای مثال، ثبت مبلغ در حساب اشتباه یا حذف هم‌زمان دو طرف یک سند ممکن است همچنان نیازمند بررسی جداگانه باشد.
مرحله نهم: کنترل کیفیت و محافظت از فایل
برای اینکه فایل دفتر روزنامه و دفتر کل قابل‌اعتمادتر باشد، چند کنترل ساده در نظر بگیرید.
استفاده از Data Validation
برای ستون کد حساب، فهرست کشویی ایجاد کنید تا کاربر کد را از میان حساب‌های تعریف‌شده انتخاب کند. این کار احتمال اشتباه تایپی را کاهش می‌دهد.
بررسی اختلاف سندها
در شیت کنترل، اختلاف بدهکار و بستانکار هر شماره سند را محاسبه کنید. می‌توانید با Conditional Formatting سندهای نامتوازن را مشخص کنید.
محافظت از سلول‌های فرمول
سلول‌هایی را که حاوی فرمول‌اند قفل کنید و از شیت محافظت کنید تا کاربران به‌صورت ناخواسته فرمول‌ها را تغییر ندهند. این روش جایگزین تهیه نسخه پشتیبان یا کنترل دسترسی مناسب نیست.
تهیه نسخه پشتیبان
از فایل در فواصل منظم نسخه پشتیبان تهیه کنید. بهتر است نسخه پشتیبان از فایل فعال جدا باشد و دسترسی به اطلاعات مالی فقط در اختیار افراد مجاز قرار گیرد.
خطاهای رایج در ساخت دفتر روزنامه و دفتر کل در اکسل
۱. ثبت نام‌های متفاوت برای یک حساب: از کد حساب یکتا و جدول کدینگ استفاده کنید.
۲. محاسبه گردش با محدوده ناقص: اگر فرمول فقط تا ردیف 1000 را پوشش دهد و داده‌ها بیشتر شوند، ممکن است بخشی از گردش در گزارش نیاید. استفاده از Excel Table یا کنترل محدوده فرمول‌ها این خطر را کاهش می‌دهد.
۳. واردکردن مبلغ به شکل متن: اگر عددها به‌صورت متن ذخیره شده باشند، ممکن است در محاسبات درست لحاظ نشوند. قالب سلول و نوع داده را بررسی کنید.
۴. اتکا به توازن کلی فایل: توازن کل بدهکار و بستانکار، جایگزین کنترل تک‌تک سندها و بررسی صحت حساب‌ها نیست.
۵. واردکردن دوباره اطلاعات در دفتر کل: تا حد امکان دفتر کل را از دفتر روزنامه استخراج کنید تا خطاهای ناشی از ورود دستی تکراری کاهش یابد.
پرسش‌های متداول
آیا می‌توان دفتر روزنامه را به‌صورت کامل در اکسل ساخت؟
بله، می‌توان ساختاری برای ثبت اسناد، محاسبه جمع بدهکار و بستانکار و کنترل اسناد در اکسل طراحی کرد. میزان مناسب‌بودن آن به حجم اطلاعات، نیازهای کنترل داخلی و الزامات قانونی و گزارشگری کسب‌وکار بستگی دارد.
تفاوت دفتر روزنامه و دفتر کل در اکسل چیست؟
دفتر روزنامه رویدادهای مالی را به ترتیب ثبت می‌کند؛ دفتر کل همان اطلاعات را بر اساس حساب دسته‌بندی می‌کند تا گردش و مانده هر حساب قابل بررسی باشد.
چگونه دفتر کل را خودکار کنیم؟
می‌توانید با SUMIFS گردش بدهکار و بستانکار را بر اساس کد حساب محاسبه کنید. برای نمایش ریز گردش، در نسخه‌های جدید از FILTER و در سایر نسخه‌ها از فیلتر معمولی یا PivotTable استفاده کنید.
چگونه بفهمیم سند حسابداری تراز است؟
مجموع بدهکار و بستانکار هر سند را جداگانه محاسبه کنید. اگر اختلاف صفر باشد، سند از نظر جمع دو طرف تراز است؛ بااین‌حال، صحت انتخاب حساب‌ها و شرح سند باید جداگانه بررسی شود.
آیا این فایل برای نگهداری رسمی دفاتر حسابداری کافی است؟
این موضوع به قوانین و الزامات قابل‌اعمال، نوع کسب‌وکار و نحوه نگهداری اسناد بستگی دارد. یک فایل اکسل آموزشی به‌تنهایی تضمین نمی‌کند که همه الزامات رسمی و قانونی رعایت شده‌اند.
جمع‌بندی
برای ساخت دفتر روزنامه و دفتر کل در اکسل، ابتدا کدینگ حساب‌ها را استاندارد کنید، سپس اسناد را در دفتر روزنامه ثبت کنید و گردش هر حساب را با فرمول‌هایی مانند SUMIFS استخراج کنید. در ادامه، با کنترل توازن اسناد، تهیه تراز آزمایشی، اعتبارسنجی ورودی‌ها و محافظت از فرمول‌ها، می‌توانید فایل منظم‌تری بسازید.
اصل مهم این است که اطلاعات فقط یک بار و به‌صورت استاندارد ثبت شوند و گزارش‌های بعدی از همان منبع داده استخراج شوند. این روش ورود تکراری اطلاعات را کاهش می‌دهد و بررسی خطاها را ساده‌تر می‌کند.
