چگونه نقطه سر به سر کسبوکار را در اکسل محاسبه کنیم؟
محاسبه نقطه سر به سر در اکسل به مدیر و حسابدار کمک میکند بداند برای پوشش هزینههای کسبوکار به چه مقدار فروش نیاز است. نقطه سربهسر سطحی از فروش است که در آن درآمد کل با مجموع هزینههای ثابت و متغیر برابر میشود؛ بنابراین سود عملیاتی در این سطح صفر است. اگر مقدار فروش کمتر از نقطه سربهسر باشد، با فرض ثابت بودن سایر شرایط، کسبوکار زیان عملیاتی خواهد داشت و اگر بیشتر باشد وارد محدوده سود عملیاتی میشود.
در این آموزش، فرمول نقطه سربهسر را با مثال عددی توضیح میدهیم، یک مدل ساده در اکسل میسازیم، جدول فروش و سود را بررسی میکنیم و نحوه ترسیم نمودار و تحلیل نقطه سر به سر کسب و کار را از دید مدیریتی یاد میگیریم.
نقطه سربهسر چیست و چه کاربردی دارد؟
نقطه سربهسر یا Break-Even Point حداقل سطح فعالیتی است که در آن درآمد فروش = هزینه ثابت + هزینه متغیر میشود. این شاخص در تحلیل هزینه–حجم–سود (CVP) برای برنامهریزی فروش، قیمتگذاری، ارزیابی هزینهها و بررسی امکانپذیری یک محصول یا خدمت به کار میرود.
برای نمونه، یک فروشگاه میخواهد بداند در ماه چند کالا باید بفروشد تا اجاره، حقوق اداری، سایر هزینههای ثابت و هزینه متغیر هر کالا پوشش داده شوند. پاسخ این سؤال را میتوان با نقطه سربهسر به دست آورد.
نکته: نقطه سربهسرِ محاسبهشده در این مقاله مربوط به سود عملیاتی در مدل ساده تکمحصولی است؛ نه الزاماً جریان نقدی، سود خالص پس از مالیات یا وصول واقعی مطالبات.
برای محاسبه نقطه سربهسر به چه اطلاعاتی نیاز داریم؟
۱. هزینههای ثابت
هزینه ثابت در دامنه فعالیت مورد بررسی با تغییر تعداد فروش، مستقیماً تغییر نمیکند؛ مانند بخشی از اجاره محل، حقوق ثابت اداری یا حق اشتراک نرمافزار. منظور این نیست که این هزینه در همه زمانها و هر سطح فعالیتی ثابت میماند؛ هزینه ثابت ممکن است با افزایش ظرفیت یا تغییر قراردادها پلهای تغییر کند.
۲. هزینه متغیر هر واحد
هزینه متغیر به سطح تولید یا فروش وابسته است؛ برای مثال مواد مصرفی، بستهبندی، کمیسیون وابسته به فروش یا بخشی از هزینه حمل که برای هر واحد فروش ایجاد میشود. در محاسبات باید اقلام متغیر مرتبط با همان محصول یا خدمت را در نظر گرفت و از دو بار شمردن هزینهها جلوگیری کرد.
۳. قیمت فروش هر واحد
قیمت فروش واحد، مبلغی است که از فروش یک کالا یا ارائه یک واحد خدمت بهعنوان درآمد عملیاتی شناسایی میشود. در مدل بهتر است قیمت فروش و هزینهها بر مبنایی یکسان و بدون اقلامی مانند مالیات بر ارزش افزوده وصولی که درآمد واحد تجاری نیست، وارد شوند.
۴. دوره زمانی و واحد اندازهگیری
اگر هزینه ثابت را ماهانه ثبت میکنید، قیمت و هزینه متغیر هم باید مربوط به همان مدل فروش ماهانه باشند. همچنین واحد شمارش باید روشن باشد: یک عدد محصول، یک سفارش، یک ساعت خدمت یا یک پروژه استاندارد.
فرمول نقطه سربهسر چیست؟
ابتدا حاشیه مشارکت هر واحد را محاسبه میکنیم. حاشیه مشارکت، مبلغ باقیمانده از قیمت فروش پس از کسر هزینه متغیر همان واحد است که برای پوشش هزینه ثابت و سپس ایجاد سود در دسترس قرار میگیرد.
حاشیه مشارکت واحد = قیمت فروش هر واحد − هزینه متغیر هر واحد
نقطه سربهسر تعدادی = هزینه ثابت کل ÷ حاشیه مشارکت هر واحد
نسبت حاشیه مشارکت = حاشیه مشارکت واحد ÷ قیمت فروش واحد
نقطه سربهسر ریالی = هزینه ثابت کل ÷ نسبت حاشیه مشارکت
در صورتی که حاشیه مشارکت واحد صفر یا منفی باشد، افزایش تعداد فروش در همین شرایط قیمت و هزینه متغیر، هزینه ثابت را پوشش نمیدهد؛ در نتیجه نقطه سربهسر مثبت و معناداری در این مدل وجود ندارد.
مثال عددی محاسبه نقطه سر به سر در اکسل
فرض کنید کسبوکاری فقط یک نوع محصول میفروشد و اطلاعات ماهانه آن به شرح زیر است. همه مبلغها به تومان هستند و این مثال صرفاً آموزشی است:
| عنوان | مبلغ یا تعداد |
|---|---|
| هزینه ثابت ماهانه | ۱۵۰٬۰۰۰٬۰۰۰ تومان |
| قیمت فروش هر واحد | ۵۰۰٬۰۰۰ تومان |
| هزینه متغیر هر واحد | ۳۰۰٬۰۰۰ تومان |
| فروش برنامهریزیشده ماهانه | ۹۰۰ واحد |
| هدف سود عملیاتی ماهانه | ۵۰٬۰۰۰٬۰۰۰ تومان |
حاشیه مشارکت هر واحد برابر است با ۲۰۰٬۰۰۰ تومان (۵۰۰٬۰۰۰ منهای ۳۰۰٬۰۰۰). بنابراین:
نقطه سربهسر تعدادی = ۱۵۰٬۰۰۰٬۰۰۰ ÷ ۲۰۰٬۰۰۰ = ۷۵۰ واحد
نقطه سربهسر فروش = ۷۵۰ × ۵۰۰٬۰۰۰ = ۳۷۵٬۰۰۰٬۰۰۰ تومان
همچنین نسبت حاشیه مشارکت برابر ۴۰٪ است و نقطه سربهسر ریالی از رابطه ۱۵۰٬۰۰۰٬۰۰۰ ÷ ۰٫۴۰، باز هم ۳۷۵ میلیون تومان به دست میآید.
کنترل نتیجه با صورت سود و زیان ساده
| شرح | محاسبه در ۷۵۰ واحد | مبلغ (تومان) |
|---|---|---|
| درآمد فروش | ۷۵۰ × ۵۰۰٬۰۰۰ | ۳۷۵٬۰۰۰٬۰۰۰ |
| هزینه متغیر کل | ۷۵۰ × ۳۰۰٬۰۰۰ | ۲۲۵٬۰۰۰٬۰۰۰ |
| حاشیه مشارکت کل | درآمد − هزینه متغیر | ۱۵۰٬۰۰۰٬۰۰۰ |
| هزینه ثابت | ماهانه | ۱۵۰٬۰۰۰٬۰۰۰ |
| سود (زیان) عملیاتی | حاشیه مشارکت − هزینه ثابت | صفر |
این کنترل نشان میدهد محاسبه صحیح است: در فروش ۷۵۰ واحد، درآمد و هزینه کل هر دو ۳۷۵ میلیون تومان خواهند بود.
آموزش گامبهگام ساخت جدول نقطه سربهسر در اکسل
اکسل را باز کنید و در یک کاربرگ جدید، ستون A را برای عنوان دادهها و ستون B را برای مقدار یا فرمول اختصاص دهید. عددها را بدون جداکننده هزارگان تایپ کنید تا اکسل آنها را بهعنوان مقدار عددی بشناسد؛ سپس از Number Format برای نمایش جداکننده استفاده کنید.
| سلول | عنوان در ستون A | مقدار یا فرمول در ستون B | نتیجه نمونه |
|---|---|---|---|
| B2 | هزینه ثابت ماهانه | 150000000 |
۱۵۰٬۰۰۰٬۰۰۰ |
| B3 | قیمت فروش هر واحد | 500000 |
۵۰۰٬۰۰۰ |
| B4 | هزینه متغیر هر واحد | 300000 |
۳۰۰٬۰۰۰ |
| B5 | فروش برنامهریزیشده (واحد) | 900 |
۹۰۰ |
| B6 | هدف سود عملیاتی | 50000000 |
۵۰٬۰۰۰٬۰۰۰ |
| B8 | حاشیه مشارکت واحد | =B3-B4 |
۲۰۰٬۰۰۰ |
| B9 | نقطه سربهسر تعدادی | =IF(B8<=0,"غیرقابل محاسبه",ROUNDUP(B2/B8,0)) |
۷۵۰ واحد |
| B10 | نسبت حاشیه مشارکت | =IF(B3<=0,"غیرقابل محاسبه",B8/B3) |
۴۰٪ |
| B11 | نقطه سربهسر ریالی نظری | =IF(B8<=0,"غیرقابل محاسبه",B2/(B8/B3)) |
۳۷۵٬۰۰۰٬۰۰۰ |
| B12 | فروش لازم با تعداد صحیح | =IF(B8<=0,"غیرقابل محاسبه",B9*B3) |
۳۷۵٬۰۰۰٬۰۰۰ |
| B13 | درآمد فروش برنامهای | =B5*B3 |
۴۵۰٬۰۰۰٬۰۰۰ |
| B14 | هزینه متغیر فروش برنامهای | =B5*B4 |
۲۷۰٬۰۰۰٬۰۰۰ |
| B15 | سود عملیاتی برنامهای | =B13-B14-B2 |
۳۰٬۰۰۰٬۰۰۰ |
| B16 | درصد حاشیه ایمنی فروش | =IF(B13=0,"فاقد فروش",(B13-B11)/B13) |
۱۶٫۶۷٪ |
| B17 | تعداد فروش برای سود هدف | =IF(B8<=0,"غیرقابل محاسبه",ROUNDUP((B2+B6)/B8,0)) |
۱۰۰۰ واحد |
نکته فنی: در برخی تنظیمات منطقهای اکسل، جداکننده آرگومانهای توابع بهجای کاما (,) نقطهویرگول (;) است. اگر فرمول با خطا مواجه شد، جداکننده را متناسب با تنظیمات اکسل تغییر دهید. برای سلولهای B10 و B16، قالب Percentage را انتخاب کنید.
تابع ROUNDUP تعداد فروش لازم را به عدد صحیح بالاتر گرد میکند؛ زیرا در بسیاری از کسبوکارها امکان فروش کسری از یک محصول وجود ندارد. در نتیجه اگر نقطه سربهسر نظری ۷۵۰٫۴ واحد باشد، کسبوکار باید حداقل ۷۵۱ واحد بفروشد. به همین دلیل بین «فروش لازم با تعداد صحیح» و نقطه سربهسر نظری تفاوت احتمالی وجود دارد.
در مثال ما نتیجه دقیقاً ۷۵۰ واحد است و نیازی به گردکردن ندارد. با توجه به اینکه تعداد فروش برنامهای ۹۰۰ واحد است، سود عملیاتی موردانتظار برابر با ۳۰ میلیون تومان خواهد بود.
برای یادگیری ابزارهای محاسباتی مکمل، مقاله مهمترین توابع اکسل برای حسابداران و آموزش Pivot Table در اکسل برای تهیه گزارش مالی را نیز مطالعه کنید.
چطور نمودار نقطه سربهسر را در اکسل رسم کنیم؟
نمودار کمک میکند نقطه برخورد خط درآمد و خط هزینه کل را بهصورت بصری ببینید. برای این کار، جدولی با ستون تعداد فروش، درآمد کل و هزینه کل بسازید. فرمولها را به دادههای ورودی کاربرگ متصل کنید تا با تغییر قیمت یا هزینهها، نمودار بهروز شود.
| تعداد فروش (واحد) | درآمد کل (تومان) | هزینه کل (تومان) | سود یا زیان (تومان) |
|---|---|---|---|
| ۰ | ۰ | ۱۵۰٬۰۰۰٬۰۰۰ | ۱۵۰٬۰۰۰٬۰۰۰− |
| ۵۰۰ | ۲۵۰٬۰۰۰٬۰۰۰ | ۳۰۰٬۰۰۰٬۰۰۰ | ۵۰٬۰۰۰٬۰۰۰− |
| ۷۵۰ | ۳۷۵٬۰۰۰٬۰۰۰ | ۳۷۵٬۰۰۰٬۰۰۰ | ۰ |
| ۹۰۰ | ۴۵۰٬۰۰۰٬۰۰۰ | ۴۲۰٬۰۰۰٬۰۰۰ | ۳۰٬۰۰۰٬۰۰۰ |
| ۱۰۰۰ | ۵۰۰٬۰۰۰٬۰۰۰ | ۴۵۰٬۰۰۰٬۰۰۰ | ۵۰٬۰۰۰٬۰۰۰ |
برای ساخت جدول پویا، در ستون A تعداد فروش را وارد کنید. در ستون B از فرمول =A21*$B$3 و در ستون C از فرمول =$B$2+A21*$B$4 استفاده کنید؛ در اینجا فرض شده اولین تعداد فروش در سلول A21 است. این فرمولها را برای ردیفهای بعدی کپی کنید.
- ستونهای تعداد فروش، درآمد کل و هزینه کل را انتخاب کنید.
- از منوی Insert گزینه Scatter with Straight Lines را انتخاب کنید تا محور افقی بر اساس تعداد واحدها عددی باشد.
- محور افقی را «تعداد فروش» و محور عمودی را «مبلغ به تومان» نامگذاری کنید.
- در نقطه ۷۵۰ واحد، دو خط به یکدیگر میرسند و مقدار هر دو برابر ۳۷۵ میلیون تومان است.
- برای نمودار خواناتر، از قالببندی اعداد و برچسب نقطه تقاطع استفاده کنید.
در این نمودار، خط درآمد از مبدأ شروع میشود؛ اما خط هزینه کل از مقدار هزینه ثابت یعنی ۱۵۰ میلیون تومان آغاز میشود. پیش از نقطه تقاطع، هزینهها بیشتر از درآمد هستند و پس از آن، با ثابت بودن فرضهای مدل، درآمد از هزینهها پیشی میگیرد.
تفسیر مدیریتی نقطه سربهسر کسبوکار
آیا نقطه سربهسر پایینتر همیشه بهتر است؟
پایینتر بودن نقطه سربهسر معمولاً به این معناست که کسبوکار با سطح فروش کمتری میتواند هزینههای خود را پوشش دهد؛ اما بهتنهایی برای قضاوت درباره کیفیت یک تصمیم کافی نیست. سرمایهگذاری جدید ممکن است هزینه ثابت را افزایش دهد و در عوض ظرفیت تولید، کیفیت محصول یا حاشیه مشارکت را بهبود ببخشد.
حاشیه ایمنی فروش چیست؟
حاشیه ایمنی نشان میدهد فروش پیشبینیشده یا واقعی چقدر بالاتر از نقطه سربهسر قرار دارد. در مثال ما، فروش برنامهای ۹۰۰ واحد است و نقطه سربهسر ۷۵۰ واحد؛ یعنی کسبوکار نسبت به نقطه سربهسر، ۱۵۰ واحد فاصله مثبت دارد.
حاشیه ایمنی تعدادی = ۹۰۰ − ۷۵۰ = ۱۵۰ واحد
درصد حاشیه ایمنی = (۹۰۰ − ۷۵۰) ÷ ۹۰۰ = ۱۶٫۶۷٪
به بیان ساده، اگر سایر فرضها تغییر نکنند، فروش برنامهای میتواند حدود ۱۶٫۶۷ درصد کاهش پیدا کند تا به نقطه سربهسر برسد. هرچه این حاشیه محدودتر باشد، کسبوکار در برابر کاهش فروش حساستر خواهد بود.
برای رسیدن به سود هدف چه مقدار فروش لازم است؟
اگر هدف سود عملیاتی ۵۰ میلیون تومان باشد، هزینه ثابت و سود هدف را با هم جمع میکنیم و بر حاشیه مشارکت واحد تقسیم میکنیم:
تعداد فروش لازم = (۱۵۰٬۰۰۰٬۰۰۰ + ۵۰٬۰۰۰٬۰۰۰) ÷ ۲۰۰٬۰۰۰ = ۱۰۰۰ واحد
بنابراین برای رسیدن به این سود عملیاتی در همان شرایط، باید حداقل ۱۰۰۰ واحد محصول یا ۵۰۰ میلیون تومان فروش داشته باشیم. این نتیجه به معنی تضمین فروش یا سود نیست و به تحقق فرضهای قیمت، هزینه و حجم بستگی دارد.
تحلیل حساسیت: اگر قیمت یا هزینه تغییر کند چه میشود؟
یکی از مزایای محاسبه نقطه سر به سر در اکسل، امکان بررسی چند سناریو بدون محاسبه دستی مجدد است. کافی است قیمت فروش، هزینه متغیر یا هزینه ثابت را تغییر دهید تا تعداد لازم برای پوشش هزینهها بهروز شود.
| سناریو | هزینه ثابت (میلیون تومان) | قیمت واحد (تومان) | هزینه متغیر واحد (تومان) | نقطه سربهسر (واحد) |
|---|---|---|---|---|
| حالت پایه | ۱۵۰ | ۵۰۰٬۰۰۰ | ۳۰۰٬۰۰۰ | ۷۵۰ |
| کاهش قیمت فروش | ۱۵۰ | ۴۵۰٬۰۰۰ | ۳۰۰٬۰۰۰ | ۱۰۰۰ |
| افزایش هزینه متغیر | ۱۵۰ | ۵۰۰٬۰۰۰ | ۳۵۰٬۰۰۰ | ۱۰۰۰ |
| افزایش هزینه ثابت | ۱۸۰ | ۵۰۰٬۰۰۰ | ۳۰۰٬۰۰۰ | ۹۰۰ |
| افزایش قیمت فروش | ۱۵۰ | ۵۵۰٬۰۰۰ | ۳۰۰٬۰۰۰ | ۶۰۰ |
این جدول نشان میدهد کاهش قیمت یا افزایش هزینه متغیر میتواند تعداد فروش لازم برای رسیدن به نقطه سربهسر را افزایش دهد. از سوی دیگر، افزایش قیمت ممکن است نقطه سربهسر را کاهش دهد؛ اما نباید فرض کرد مشتریان در هر قیمتی همان تعداد محصول را خواهند خرید. تصمیم قیمتگذاری باید همراه با تحلیل تقاضا و رقبا انجام شود.
اشتباهات رایج هنگام محاسبه نقطه سربهسر
- مخلوطکردن هزینههای ماهانه و سالانه: دوره زمانی همه ورودیها باید یکسان باشد.
- حذف هزینههای متغیر فروش: کمیسیون، بستهبندی یا هزینههای وابسته به تعداد فروش ممکن است از محاسبه جا بمانند.
- استفاده از سود ناخالص بهجای حاشیه مشارکت: حاشیه مشارکت بر اساس تفکیک هزینه ثابت و متغیر محاسبه میشود و لزوماً برابر سود ناخالص گزارش مالی نیست.
- بیتوجهی به تخفیف و مرجوعی: بهتر است قیمت فروش مؤثر با توجه به واقعیت عملیات سنجیده شود.
- تقسیم بر صفر یا عدد منفی: وقتی قیمت فروش از هزینه متغیر واحد بیشتر نباشد، فرمول معمول نقطه سربهسر قابل استفاده نیست.
- فرض فروش نامحدود: ظرفیت تولید، تعداد مشتریان و کشش تقاضا ممکن است دستیابی به نقطه سربهسر را ناممکن یا دشوار کند.
- تعمیم مدل تکمحصولی به چند محصول: در کسبوکار چندمحصولی باید ترکیب فروش، حاشیه مشارکت موزون یا روشهای مناسب دیگر بررسی شود.
- یکسان دانستن نقطه سربهسر با نقدینگی: فروش نسیه و اقساط بدهی میتوانند بر جریان نقدی تأثیر بگذارند، حتی اگر کسبوکار از نظر سود عملیاتی سربهسر باشد.
محاسبه نقطه سربهسر در کسبوکار چندمحصولی
اگر کسبوکار چند محصول با حاشیه مشارکت متفاوت بفروشد، محاسبه نقطه سربهسر بر مبنای یک کالای فرضی ممکن است نتیجه گمراهکنندهای بدهد. در این شرایط باید ترکیب فروش یا Sales Mix را مشخص کرد و متناسب با آن از حاشیه مشارکت موزون استفاده کرد. اگر سهم فروش محصولات تغییر کند، نقطه سربهسر نیز تغییر خواهد کرد.
برای شروع تحلیل محصولات، آشنایی با محاسبه بهای تمامشده ضروری است؛ مقاله فرمول بهای تمامشده کالای فروشرفته و راهنمای طراحی جدول بهای تمامشده در اکسل میتوانند برای جمعآوری دادههای هزینه مفید باشند. توجه داشته باشید که «بهای تمامشده» لزوماً تمام هزینههای متغیر مدل CVP را پوشش نمیدهد و باید هزینهها متناسب با رفتارشان بازطبقهبندی شوند.
چکلیست نهایی قبل از استفاده مدیریتی از نتیجه
- واحد فروش و دوره زمانی مشخص شدهاند.
- هزینههای ثابت و متغیر بهدرستی از هم تفکیک شدهاند.
- قیمت فروش با واحد پول و مبنای حسابداری هزینهها سازگار است.
- حاشیه مشارکت هر واحد مثبت است.
- تعداد سربهسر به عدد صحیح مناسب گرد شده است.
- نتیجه با جدول درآمد، هزینه کل و سود عملیاتی کنترل شده است.
- ظرفیت فروش و احتمال تحقق تقاضا ارزیابی شده است.
- سناریوهای تغییر قیمت، هزینه و مقدار فروش بررسی شدهاند.
سؤالات متداول درباره نقطه سربهسر در اکسل
فرمول نقطه سربهسر در اکسل چیست؟
در مدل تکمحصولی، اگر هزینه ثابت در B2، قیمت واحد در B3 و هزینه متغیر واحد در B4 باشد، میتوانید از فرمول =IF(B3<=B4,"غیرقابل محاسبه",ROUNDUP(B2/(B3-B4),0)) برای محاسبه حداقل تعداد فروش صحیح استفاده کنید.
تفاوت نقطه سربهسر ریالی و تعدادی چیست؟
نقطه سربهسر تعدادی نشان میدهد چند واحد کالا یا خدمت باید فروخته شود. نقطه سربهسر ریالی نشاندهنده مبلغ فروش لازم برای پوشش مجموع هزینههای ثابت و متغیر است.
آیا رسیدن به نقطه سربهسر یعنی کسبوکار سودآور شده است؟
در نقطه سربهسر، سود عملیاتی مدل صفر است؛ بنابراین هنوز سود عملیاتی مثبت ایجاد نشده است. فروش بیش از این نقطه، با فرض ثابت بودن شرایط مدل، میتواند باعث ایجاد سود عملیاتی شود.
جمعبندی
محاسبه نقطه سر به سر در اکسل ابزاری ساده و کاربردی برای بررسی حداقل فروش لازم، برنامهریزی سود و سنجش حساسیت کسبوکار به تغییر قیمت و هزینههاست. در مثال مقاله، هزینه ثابت ۱۵۰ میلیون تومان، قیمت واحد ۵۰۰ هزار تومان و هزینه متغیر واحد ۳۰۰ هزار تومان بود؛ بنابراین نقطه سربهسر در ۷۵۰ واحد یا ۳۷۵ میلیون تومان فروش قرار گرفت.
ارزش مدیریتی این تحلیل فقط به یک عدد محدود نمیشود؛ با تغییر فرضها در اکسل، میتوانید اثر تصمیمهای قیمتگذاری، کنترل هزینه و هدفگذاری فروش را پیش از اجرا بررسی کنید. بااینحال، بهتر است نتیجه را در کنار ظرفیت عملیاتی، تقاضای بازار، ترکیب محصولات و وضعیت جریان نقدی ارزیابی کنید.
منابع و مطالعه بیشتر
- OpenStax — Calculate a Break-Even Point in Units and Dollars
- U.S. Small Business Administration — Break-even Point
- Microsoft Support — IFERROR Function in Excel
توجه: این آموزش بر یک مدل ساده تکمحصولی و رابطه خطی هزینه–حجم–سود استوار است. اعداد مثال فرضی هستند و تحلیل نهایی هر کسبوکار باید با اطلاعات واقعی، سیاست قیمتگذاری، هزینههای مربوط و ظرفیت عملیاتی همان مجموعه انجام شود.