محاسبه بهای تمام شده در اکسل – آموزش گام به برای کسب و کارها

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

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

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

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

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

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

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

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

۲. مجموع مبلغ هزینه‌های تهیه و موجودی کالا در ابتدای دوره مالی همان مبلغ کالاهای آماده فروش‌رسانی است. بنابراین مقدار سلول B2 را با B7 جمع می‌کنیم و در سلول می‌نویسیم.

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

برای نصب اپلیکیشن بدون هزینه مجله فرادرس، کلیک کنید.

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

در روش FIFO یا فایفو (First In First Out) فرض می‌شود کالاهایی که زودتر تهیهاری یا تولید شده‌اند، زودتر هم به ارائه می‌رسند. بنابراین برای محاسبه بهای تمام شده در اکسل، در نظر گرفتن قدیمی‌ترین ارزش کالای فروخته شده اولویت اول است.

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

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

۱. یک کاربرگ خالی در اکسل مانند جدول زیر درست می‌کنیم.

۲. مطابق گزارش‌ها جدول اصلی، مقادیر مربوط به ردیف اول را در جدول وارد می‌کنیم.

۳. برای ردیابی همه موجودی‌ها نیاز به یک جدول کمکی داریم. این جدول را در همان کاربرگ به‌عنوان مثال از ردیف نوزدهم با چهار ستون «تاریخ تهیه»،‌ «تعداد کالا»،‌ «مبلغ واحد» و «بها کل» درست می‌کنیم. در این جدول داده‌ها مربوط به موجودی کالاها را بعد از هر سفارش و ارائه ثبت می‌کنیم. در این جدول مبلغ کل در ستون D حاصل‌ضرب «تعداد کالا» در «مبلغ واحد» آن است.

۴. ردیف دوم جدول اصلی مطابق داده‌ها اولیه، مربوط به تهیه کالا است. دو مقدار این ردیف یعنی «تعداد کالای ورودی» و «مبلغ واحد کالای ورودی» را نیز می‌نویسیم. برای محاسبه مقدار «تعداد کالای موجود در انبار»، تعداد موجودی قبلی کالا یا «تعداد کالای ورودی» را با تعداد کالای خریداریاری شده فعلی جمع می‌کنیم. با کم کردن «تعداد کالای خروجی» یا فروخته شده، «تعداد کالای موجود در انبار» به‌دست می‌آید. بنابراین ابتدا با توجه داده‌ها موجود، فرمول =H2+C3-E3 را در سلول H3 می‌نویسیم.

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

۵. داده‌ها مربوط به اولین سفارش را نیز در جدول کمکی موجودی‌ها وارد می‌کنیم.

۶. در جدول اصلی برای محاسبه ارزش موجودی کالا فرمول =(C3*D3)+I2-G3 را در سلولI3 می‌نویسیم.

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

۸. گزارش‌ها ردیف سوم مربوط به تهیه بعدی در تاریخ ۱۵ دی ۱۴۰۴ را در جدول وارد می‌کنیم. در این مرحله به دلیل کپی کردن فرمول در همه ردیف‌ها، فقط کافی است مقدار مربوط به «تعداد کالا» و «مبلغ واحد» آن را در جدول وارد کنیم. بقیه موارد به‌صورت خودکار محاسبه می‌شوند.

۹. هم‌زمان داده‌ها مربوط به سفارش بعدی را نیز در جدول موجودی‌ها وارد می‌کنیم.

۱۰. ردیف بعدی در جدول اصلی مربوط به عرضه کالا است. با توجه به شرح موجود، مقدار «کالای خروجی» و «مبلغ واحد» را به ترتیب در سلول‌های E5 و F5 می‌نویسیم. طبق اصول روش FIFO مبلغ فروش‌رسانی واحد کالای خروجی اولین ارزش مربوط به کالای موجود در جدول مطابق تاریخ است. در اینجا همان بها موجودی اولیه یعنی «۹» میلیون تومان خواهد بود.

با توجه به این گزارش‌ها بهای تمام شده کالای فروخته شده را با ضرب بها واحد اولین ارائه در تعداد کالا و نوشتن فرمول =E5*F5 به‌دست می‌آوریم.

۱۱. برای به‌دست آوردن تعداد واقعی موجودی کالا با توجه مطابق گزارش‌ها تازه، تعداد کالای فروخته شده را از موجودی کل کم می‌کنیم. بنابراین موجودی کالا در جدول کمکی به «۱۲۰» عدد می‌رسد.

۱۲. ردیف‌های بعدی طبق جدول اصلی همگی سفارش هستند. بنابراین آن‌ها را بدون تغییر در جدول موجودی‌ها وارد می‌کنیم. اما در دومین عرضه کالا در تاریخ ۲۵ دی ۱۴۰۴ که طبق جدول اصلی «۱۲۰» عدد است، میزان موجودی اولیه به صفر می‌رسد.

طبق اصل FIFO اولین کالای ورودی، اولین کالایی است که فروخته می‌شود. بنابراین با کم کردن میزان عرضه اخیر که تعداد «۱۲۰» عدد کالا است از اولین کالای ورودی یا همان موجودی اولیه، مقدار به‌روز آن صفر می‌شود. بنابراین جدول موجودی به شکل تصویر زیر تغییر می‌کند.

۱۳. به همین ترتیب ردیف‌های دیگر را مانند تصویر زیر در جدول اصلی پر می‌کنیم.

همان‌طور که مشخص است در تاریخ ۵ بهمن ۱۴۰۴ مبلغ واحد کالای خروجی را برابر «۱۰» میلیون تومان در نظر می‌گیریم. زیرا مطابق جدول کمکی بعد از ارائه «۱۲۰» عدد کالا در تاریخ ۲۲ دی ماه، موجودی اولیه صفر شد. بنابراین طبق اصل FIFO برای محاسبه نرخ ارائه کالا سراغ دومین بها قدیمی سفارش کالا یعنی «۱۰» میلیون تومان می‌رویم. با توجه به این اعداد مقدار بهای تمام شده کالای فروخته شده (COGS) با ضرب «تعداد کالا» در نرخ عرضه محاسبه می‌شود.

۱۴. در هر مرحله جدول کمکی را نیز بروز می‌کنیم تا میزان موجودی کالا را رصد کنیم. با توجه به اینکه در تاریخ ۵ بهمن تعداد «۹۰» واحد فروش‌رسانی داشتیم، موجودی انبار از موجودی «۱۰۰» عددی کالا در تاریخ ۱۱ دی کم می‌شود. بنابراین موجودی اخیر در این تاریخ «۱۰» عدد کالا است که آن را در جدول وارد می‌کنیم.

۱۵. در تاریخ ۱۶ بهمن مطابق جدول اصلی «۲۰۰» عدد ارائه کالا داریم. با توجه به اینکه قدیمی‌ترین موجودی کالا در تاریخ ۱۱ دی ماه است، طبق اصل فایفو ابتدا باید مقدار آن را از این عدد کم کنیم. اما چون موجودی کافی نیست، برای پر کردن کسری از موجودی تاریخ‌های دیگر کم می‌کنیم. به این شکل که ابتدا «۱۰» واحد از موجودی در تاریخ ۱۱ دی برمی‌داریم. سپس «۱۵۰» واحد از موجودی در تاریخ «۱۵» دی ماه و در نهایت «۴۰» واحد از موجودی در تاریخ ۲۲ دی برداشت می‌کنیم. بنابراین جدول موجودی به شکل تصویر زیر بروز می‌شود.

۱۶. در این مرحله لازم است جدول اصلی را بروز کنیم. اما با توجه به اینکه از سه موجودی مختلف یعنی ۱۱ دی، ۱۵ دی و ۲۲ دی برداشت کرده‌ایم، سه بها متفاوت برای عرضه کالا داریم.

بنابراین برای محاسبه بهای تمام شده کالای فروخته شده فرمول =10*10+150*12+(200-150-10)*11 را در سلول G11 می‌نویسیم.

در این فرمول با توجه به برداشت از موجودی‌های مختلف، «۱۰» عدد کالای «۱۰» میلیون تومانی، «۱۵۰» عدد کالای «۱۲» میلیون تومانی و «۴۰» عدد کالای «۱۱» میلیون تومانی داریم. برای خواناتر شدن فرمول از نظر حسابداری، عدد «۴۰» را به‌صورت مستقیم نمی‌نویسیم تا مشخص کنیم که «۲۰۰» عدد کالا از دو موجودی «۱۵۰» تایی و «۱۰» تایی برداشت شده است.

از سوی دیگر از آنجا که در اکسل نمی‌توانیم سه عدد را در یک سلول بنویسیم، برای مشخص کردن بها کالای فروخته شده در سلول G10، سه عدد «۱۰»، «۱۱» و «۱۲» را با علامت / از هم جدا می‌کنیم و در این سلول می‌نویسیم. اما چون ممکن است بعد از فشار دادن دکمه ENTER اکسل مقدار سلول را به‌عنوان تاریخ بشناسد، با نوشتن یک علامت " فرمت را به شکل متن در می‌آوریم.

بنابراین جدول به شکل زیر درمی‌آید.

۱۷. به همین ترتیب بقیه ردیف‌ها را نیز تکمیل می‌کنیم. در نهایت برای محاسبه بهای تمام شده کل، لازم است همه مقادیر COGS در جدول را با هم جمع کنیم.

بنابراین حاصل نهایی به شکل جدول زیر درمی‌آید.

علاوه بر این جدول نهایی موجودی هم به شکل زیر درمی‌آید.

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

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

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

در روش لایفو ( Latest In First Out | LIFO) ارزش ارائه کالا مطابق آخرین نرخ محاسبه می‌شود. یعنی بر خلاف روش FIFO در این حالت برای محاسبه بهای تمام شده در اکسل، تازهترین بها کالای فروخته شده را در اولویت قرار می‌دهیم.

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

۱. تا قبل از رسیدن به اولین ارائه تغییری در روش انجام کار وجود ندارد. جدول کمکی موجودی نیز مانند تصویر زیر است.

در تاریخ ۲۰ دی که اولین ارائه اتفاق می‌افتد، برای محاسبه بهای تمام شده، ارزش واحد کالای فروخته شده را برابر آخرین مبلغ موجودی قبل از تاریخ ارائه یعنی عدد «۱۲» میلیون در نظر می‌گیریم. بر این اساس جدول موجودی بعد از «۸۰» واحد فروش‌رسانی کالا که از آخرین تاریخ قبل از این ارائه کسر می‌شود، به شکل زیر درمی‌آید.

۲. به همین ترتیب در سایر موارد نیز مبلغ واحد عرضه کالا را مطابق آخرین بها موجودی قرار می‌دهیم و بهای تمام شده را با حاصل ضرب «تعداد کالا» در «بها واحد عرضه» حساب می‌کنیم.

۳. جدول موجودی نیز تا تاریخ ۱۰ بهمن بعد از عرضه‌های انجام شده به شکل زیر درمی‌آید.

۴. اما در تاریخ ۱۶ بهمن که تعداد فروش‌رسانی کالا «۲۰۰» عدد است، به ترتیب از آخرین موجودی شروع کار به برداشت می‌کنیم. بنابراین «۱۵۰» عدد از تاریخ ۱۰ بهمن، «۱۰» عدد از تاریخ ۳۰ دی و «۴۰» عدد از تاریخ ۲۲ دی برداشت می‌کنیم. جدول موجودی بعد از این برداشت‌ها به شکل زیر درمی‌آید.

حال برای محاسبه بهای تمام شده کالا با توجه به سه برداشت مختلف با سه بها متفاوت فرمول =150*14+10*13+40*11 را در سلول G11 می‌نویسیم. به این شکل که با توجه به تازهترین ارزش، «۱۵۰» عدد کالای «۱۴» میلیون تومانی، «۱۰» عدد کالای «۱۳» میلیون تومانی و «۴۰» عدد کالای «۱۱» میلیون تومانی خواهیم داشت.

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

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

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

۱. در اولین فروش‌رسانی این مقدار با تقسیم مقدار سلول I4 بر H4 به‌دست می‌آید. مقدار سلول I4 همان مجموع ارزش موجودی‌های قبلی تا قبل از اولین ارائه است. بنابراین با تقسیم آن بر تعداد کل موجودی، متوسط مبلغ واحد ارائه را محاسبه می‌کنیم. مقدار بهای تمام شده نیز حاصل‌ضرب این مبلغ در تعداد کالا است.

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

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

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

سوالات متداول

چرا محاسبه بهای تمام شده در اکسل – آموزش گام به گام برای کسب و کارها اهمیت دارد؟

این موضوع در روزهای اخیر مورد توجه رسانه‌ها و مخاطبان قرار گرفته است.

'.محاسبه بهای تمام شده در اکسل – آموزش گام به گام برای کسب و کارها.' چیست؟

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

آخرین وضعیت محاسبه بهای تمام شده در اکسل – آموزش گام به گام برای کسب و کارها چگونه است؟

جدیدترین جزئیات و تغییرات مربوط به این موضوع در متن گزارش آمده است.