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

در ادامه آموزش گسترش اندیشه پویا:

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

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

روش‌های محاسبه بهای تمام شده در اکسل

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

روش مستقیمروش FIFOروش LIFOروش میانگین وزنی

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

۱. محاسبه بهای تمام شده در اکسل با روش مستقیم

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

موجودی در انتهای دوره مالی - مبلغ خرید کالا + موجودی در ابتدای دوره مالی = بهای تمام شده کالا

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

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

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

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

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

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

براینصب اپلیکیشنرایگانمجله کلیک کنید.

۲. محاسبه بهای تمام شده کالا با روش FIFO

معمولا شرکت‌های بزرگ از روش مستقیم برای محاسبه بهای تمام شده استفاده نمی‌کنند. روش‌های 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 در جدول را با هم جمع کنیم.

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

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

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

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

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

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

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

مجموعه فیلم آموزش اکسل در حسابداری در فرادرسمجموعه فیلم آموزش اکسل برای کسب و کار در فرادرس

۳. محاسبه بهای تمام شده در اکسل به روش LIFO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

مجموعه فیلم آموزش اکسل – مقدماتی تا پیشرفتهفیلم آموزش محاسبه بهای تمام شده در اکسل Excel + گواهینامهمجموعه فیلم آموزش اکسل برای کسب و کارتوابع اکسل در حسابداری – آموزش توابع پرکاربرد به زبان ساده۱۰ آموزش‌ اکسل جامع فرادرس برای مدیران مالی

گسترش اندیشه پویا از سال ۱۳۸۲ در حوزه مشاوره فناوری اطلاعات و آموزش تخصصی فعالیت می‌کند. برای دریافت مشاوره با ما تماس بگیرید.