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

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

در این مقاله از excelrecord.ir، مراحل ساخت یک ترازنامه کاربردی را از طراحی جدول حساب‌ها تا محاسبه خودکار جمع دارایی‌ها، بدهی‌ها و حقوق مالکانه بررسی می‌کنیم.

ترازنامه چیست؟

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

برخلاف صورت سود و زیان که عملکرد مالی را طی یک دوره نشان می‌دهد، ترازنامه تصویری از وضعیت مالی در یک مقطع زمانی ارائه می‌کند؛ برای مثال در پایان ماه، پایان فصل یا پایان سال مالی.

معادله اصلی ترازنامه به شکل زیر است:

دارایی‌ها = بدهی‌ها + حقوق مالکانه

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

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

اجزای اصلی ترازنامه

برای ساخت ترازنامه در اکسل ابتدا باید بدانید چه گروه‌هایی در این گزارش قرار می‌گیرند.

۱. دارایی‌ها

دارایی منابع اقتصادی تحت کنترل واحد تجاری است که انتظار می‌رود منافع اقتصادی ایجاد کند.

دارایی‌ها معمولاً به دو گروه اصلی تقسیم می‌شوند:

دارایی‌های جاری:

اقلامی که انتظار می‌رود در چرخه عملیاتی عادی یا در کوتاه‌مدت به وجه نقد تبدیل شوند یا مصرف شوند. نمونه‌ها:

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

دارایی‌های غیرجاری:

دارایی‌هایی که برای استفاده بلندمدت در کسب‌وکار نگهداری می‌شوند؛ مانند:

• زمین
• ساختمان
• ماشین‌آلات و تجهیزات
• وسایل نقلیه
• دارایی‌های نامشهود

توجه کنید که در ترازنامه معمولاً دارایی ثابت مشهود پس از کسر استهلاک انباشته نمایش داده می‌شود.

۲. بدهی‌ها

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

بدهی‌های جاری:

تعهداتی که انتظار می‌رود در چرخه عملیاتی یا کوتاه‌مدت تسویه شوند، مانند:

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

بدهی‌های غیرجاری:

تعهداتی که سررسید یا تسویه آن‌ها بلندمدت است؛ مانند وام‌های بلندمدت.

۳. حقوق مالکانه

حقوق مالکانه نشان‌دهنده سهم صاحبان کسب‌وکار از خالص دارایی‌های واحد اقتصادی است.

برخی اقلام متداول آن عبارت‌اند از:

• سرمایه
• اندوخته‌ها
• سود یا زیان انباشته
• سایر اجزای حقوق مالکانه، در صورت وجود

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

مراحل ساخت ترازنامه در اکسل

مرحله اول: ایجاد شیت‌های موردنیاز

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

1. لیست حساب‌ها: برای نگهداری کد و عنوان حساب‌ها و گروه‌بندی آن‌ها
2. مانده حساب‌ها: برای ثبت مانده نهایی حساب‌ها در تاریخ گزارش
3. ترازنامه: برای نمایش گزارش نهایی و محاسبات خودکار

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

مرحله دوم: ساخت جدول لیست حساب‌ها

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

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

محدوده جدول را انتخاب کنید و با Ctrl + T آن را به Excel Table تبدیل کنید. نام جدول را Accounts قرار دهید.

مرحله سوم: ثبت مانده حساب‌ها

در شیت «مانده حساب‌ها» اطلاعات مانده حساب‌ها را وارد کنید.

ساختار پیشنهادی:

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

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

مرحله چهارم: طراحی شیت ترازنامه

در شیت «ترازنامه» عنوان گزارش و تاریخ تهیه آن را درج کنید.

برای مثال:

ترازنامه شرکت نمونه در تاریخ ۱۴۰۵/۰۷/۳۰

سپس ساختار گزارش را به شکل زیر بسازید:

برای خوانایی بهتر، دارایی‌ها را در بخش اول، بدهی‌ها را در بخش دوم و حقوق مالکانه را در بخش سوم قرار دهید.

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

حالا نوبت به مهم‌ترین قسمت آموزش می‌رسد: محاسبه مبالغ با فرمول.

فرض می‌کنیم اطلاعات مانده حساب‌ها در یک Excel Table به نام Balances ذخیره شده است و ستون‌های آن عبارت‌اند از:

• کد حساب
• عنوان حساب
• مانده بدهکار
• مانده بستانکار
• گروه اصلی
• زیرگروه

اگر نام ستون‌ها یا جدول شما متفاوت است، باید نام‌های داخل فرمول را مطابق فایل خود تغییر دهید.

محاسبه دارایی‌های جاری

برای جمع کردن مانده بدهکار حساب‌هایی که گروه اصلی آن‌ها «دارایی» و زیرگروه آن‌ها «جاری» است، می‌توانید از فرمول زیر استفاده کنید:

=SUMIFS(Balances[مانده بدهکار],
Balances[گروه اصلی],"دارایی",
Balances[زیرگروه],"جاری")

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

محاسبه دارایی‌های غیرجاری

=SUMIFS(Balances[مانده بدهکار],
Balances[گروه اصلی],"دارایی",
Balances[زیرگروه],"غیرجاری")

در مثال ما، تجهیزات ۶۰ میلیون تومان است؛ اما باید استهلاک انباشته از آن کسر شود.

کسر استهلاک انباشته

استهلاک انباشته ماهیت کاهنده دارایی دارد و معمولاً مانده بستانکار آن از بهای تمام‌شده دارایی مربوط کسر می‌شود.

برای نمونه:

=60000000-10000000

ارزش دفتری تجهیزات در این مثال برابر با ۵۰ میلیون تومان خواهد بود.

در فایل حرفه‌ای بهتر است استهلاک انباشته به حساب دارایی مربوط متصل شود تا در صورت وجود چند دارایی، مبلغ هرکدام به‌درستی کسر شود.

محاسبه بدهی‌های جاری

=SUMIFS(Balances[مانده بستانکار],
Balances[گروه اصلی],"بدهی",
Balances[زیرگروه],"جاری")

محاسبه بدهی‌های غیرجاری

=SUMIFS(Balances[مانده بستانکار],
Balances[گروه اصلی],"بدهی",
Balances[زیرگروه],"غیرجاری")

محاسبه حقوق مالکانه

در این مثال، سرمایه و سود انباشته هر دو مانده بستانکار دارند. بنابراین می‌توان آن‌ها را جمع کرد:

=SUMIFS(Balances[مانده بستانکار],
Balances[گروه اصلی],"حقوق مالکانه")

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

محاسبه جمع دارایی‌ها

اگر دارایی جاری در سلول B2 و دارایی غیرجاری خالص در سلول B3 باشد:

=SUM(B2:B3)

محاسبه جمع بدهی‌ها و حقوق مالکانه

اگر جمع بدهی‌ها در B6 و حقوق مالکانه در B7 باشد:

=SUM(B6:B7)

کنترل تراز بودن گزارش

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

اگر جمع دارایی‌ها در B4 و جمع بدهی‌ها و حقوق مالکانه در B8 باشد:

=B4-B8

اگر نتیجه صفر باشد، گزارش از نظر معادله اصلی ترازنامه تراز است.

برای نمایش پیام خودکار می‌توانید بنویسید:

=IF(B4=B8,"تراز است","نیاز به بررسی")

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

=IF(ABS(B4-B8)<1,"تراز است","نیاز به بررسی")

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

مثال کامل ترازنامه در اکسل

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

برای حفظ توازن مثال، سود انباشته ۶۵ میلیون تومان در نظر گرفته شده است. این مبلغ جایگزین عدد نمونه اولیه نیست و برای هماهنگ شدن داده‌های آموزشی با معادله ترازنامه تنظیم شده است.

چگونه ترازنامه را بر اساس تاریخ به‌روزرسانی کنیم؟

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

برای مثال، تاریخ گزارش را در سلول B1 قرار دهید و مانده حساب‌ها را تا آن تاریخ استخراج کنید.

روش پیشنهادی:

1. تاریخ و شماره سند را در جدول ثبت رویدادهای مالی نگهداری کنید.
2. گردش بدهکار و بستانکار هر حساب را تا تاریخ گزارش جمع کنید.
3. مانده اول دوره را با گردش دوره ترکیب کنید.
4. مانده نهایی را با توجه به ماهیت حساب محاسبه کنید.
5. مانده نهایی را در ترازنامه نمایش دهید.

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

نکات مهم برای حرفه‌ای‌تر کردن فایل ترازنامه

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

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

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

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

۳. از قالب‌بندی شرطی استفاده کنید

برای سلول اختلاف تراز، Conditional Formatting تنظیم کنید تا در صورت غیرصفر بودن اختلاف، رنگ هشدار نمایش داده شود.

۴. از فرمت عددی مناسب استفاده کنید

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

۵. گزارش را با تراز آزمایشی تطبیق دهید

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

اشتباهات رایج هنگام ساخت ترازنامه در اکسل

• استفاده از گردش حساب به‌جای مانده حساب: ترازنامه بر اساس مانده حساب‌ها در تاریخ گزارش تهیه می‌شود.
• کسر نکردن استهلاک انباشته: نمایش دارایی ثابت بدون توجه به استهلاک می‌تواند مبلغ دارایی‌ها را بیش از واقع نشان دهد.
• طبقه‌بندی اشتباه بدهی‌ها: بدهی جاری و غیرجاری باید با توجه به سررسید و شرایط تسویه تفکیک شوند.
• نادیده گرفتن حساب‌های کاهنده: برخی حساب‌ها ماهیت کاهنده دارند و باید در محاسبه مبلغ خالص لحاظ شوند.
• تغییر دستی ارقام گزارش: بهتر است ارقام از جدول اطلاعات و با فرمول استخراج شوند، نه اینکه هر بار به‌صورت دستی اصلاح شوند.
• اتکا به صفر شدن اختلاف بدون بررسی حساب‌ها: تراز بودن معادله به‌تنهایی تضمین نمی‌کند همه حساب‌ها درست ثبت یا طبقه‌بندی شده‌اند.

پرسش‌های متداول

۱. آیا می‌توان ترازنامه را به‌صورت خودکار در اکسل ساخت؟

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

۲. فرمول اصلی ترازنامه چیست؟

معادله اصلی ترازنامه عبارت است از:

دارایی‌ها = بدهی‌ها + حقوق مالکانه

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

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

ابتدا مانده حساب‌ها، ثبت‌های تکراری یا ناقص، حساب‌های کاهنده، سود و زیان انباشته و طبقه‌بندی حساب‌ها را بررسی کنید. همچنین مطمئن شوید تمام ارقام مربوط به یک تاریخ هستند.

۴. تفاوت ترازنامه و تراز آزمایشی چیست؟

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

۵. آیا برای تهیه ترازنامه به صورت سود و زیان هم نیاز داریم؟

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

جمع‌بندی

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

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

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