لوگوی تهران حسابتهران حسابحسابداری، مالی و هوش مصنوعی
ورودثبت نام

مقاله

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

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

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

این تحلیل پیش‌بینی قطعی آینده یا تضمین سود نیست. ارزش آن در آشکارکردن دامنه نتایج، شناسایی مفروضات مهم و آماده‌کردن تصمیم‌های مشروط است. راهنمای Microsoft Support درباره Data Table نیز توضیح می‌دهد که جدول داده نشان می‌دهد تغییر یک یا دو متغیر در فرمول چه اثری بر نتایج دارد. در ادامه، مدل را برای یک دوره بودجه‌ای می‌سازیم؛ اما همین منطق برای ماه، فصل یا هر محصول نیز قابل استفاده است.

پیش از تحلیل حساسیت در اکسل برای بودجه، مدل را قابل ممیزی کنید

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

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

  • درآمد = حجم فروش × نرخ فروش هر واحد
  • هزینه متغیر کل = حجم فروش × هزینه متغیر هر واحد
  • سود ناخالص = درآمد − هزینه متغیر کل
  • حاشیه سود ناخالص = سود ناخالص ÷ درآمد
  • سود عملیاتی ساده = سود ناخالص − هزینه ثابت

اگر درآمد صفر باشد، تقسیم حاشیه سود خطا می‌دهد؛ بنابراین می‌توان از =IF(Revenue=0,0,GrossProfit/Revenue) یا ساختاری معادل استفاده کرد. نام‌گذاری سلول‌ها، مانند Volume، Price، VariableCost و FixedCost، خواندن فرمول را آسان‌تر می‌کند؛ با این حال آدرس سلول و نام‌ها باید در کل مدل یکدست باشند.

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

مثال پایه: از مفروضه تا سود و نقطه سر به سر

فرض کنید بودجه یک دوره چنین است: حجم فروش ۱۰٬۰۰۰ واحد، نرخ فروش هر واحد ۱۲۰ واحد پولی، هزینه متغیر هر واحد ۷۲ واحد پولی و هزینه ثابت ۳۰۰٬۰۰۰ واحد پولی. در این مثال، درآمد ۱٬۲۰۰٬۰۰۰، هزینه متغیر کل ۷۲۰٬۰۰۰ و سود ناخالص ۴۸۰٬۰۰۰ است. پس حاشیه سود ناخالص ۴۰٪ و سود عملیاتی ساده ۱۸۰٬۰۰۰ واحد پولی خواهد بود.

برای تحلیل نقطه سر به سر، ابتدا «حاشیه مشارکت هر واحد» را محاسبه کنید: نرخ فروش منهای هزینه متغیر هر واحد؛ یعنی ۴۸ واحد پولی. سپس نقطه سر به سر تعدادی برابر است با هزینه ثابت تقسیم بر حاشیه مشارکت هر واحد: =FixedCost/(Price-VariableCost). در مثال، نتیجه ۶٬۲۵۰ واحد است. اگر هر واحد واقعاً با همین نرخ و هزینه فروخته شود و هزینه ثابت در محدوده مربوط تغییر نکند، فروش بالاتر از این مقدار به پوشش هزینه ثابت و ایجاد سود عملیاتی کمک می‌کند.

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

تحلیل حساسیت یک طرفه با جدول داده اکسل

تحلیل حساسیت یک طرفه زمانی مناسب است که می‌خواهید اثر تغییر یک مفروضه را، در حالی که بقیه مفروضات پایه ثابت‌اند، ببینید. برای مثال، نرخ فروش را در پنج سطح ۱۰۸، ۱۱۴، ۱۲۰، ۱۲۶ و ۱۳۲ قرار دهید و خروجی مورد نظر را سود عملیاتی انتخاب کنید.

چیدمان و ساخت جدول

  1. در سلولی بیرون از مدل، مثلاً H2، فقط به سلول سود عملیاتی ارجاع دهید؛ برای نمونه =B12. این سلول «فرمول خروجی» جدول است، نه عدد تایپ‌شده.
  2. سطوح نرخ فروش را در G3:G7 وارد کنید و H2 را در بالای ستون خروجی بگذارید.
  3. محدوده G2:H7 را انتخاب کنید؛ سپس از مسیر Data > What-If Analysis > Data Table، مرجع سلول ورودی نرخ فروش را در کادر Column input cell وارد کنید.
  4. خروجی‌ها را با قالب عددی یکسان نمایش دهید و مقدار پایه را مشخص کنید.

در مثال، سود عملیاتی برای نرخ‌های ۱۰۸ تا ۱۳۲ به‌ترتیب ۶۰٬۰۰۰، ۱۲۰٬۰۰۰، ۱۸۰٬۰۰۰، ۲۴۰٬۰۰۰ و ۳۰۰٬۰۰۰ می‌شود. به این ترتیب، جدول فقط یک پاسخ تولید نمی‌کند؛ شیب اثر نرخ فروش را نیز در دامنه آزمون نشان می‌دهد. مایکروسافت تأکید می‌کند که فرمول یک جدول داده یک‌متغیره باید به همان سلول ورودیِ در حال آزمون ارجاع داشته باشد و می‌توان چند خروجی فرمولی را کنار هم گذاشت.

همین ساختار را برای حجم فروش تکرار کنید. اگر حجم‌های ۸٬۰۰۰ تا ۱۲٬۰۰۰ را با گام ۱٬۰۰۰ تست کنیم، سود عملیاتی مثال به‌ترتیب ۸۴٬۰۰۰، ۱۳۲٬۰۰۰، ۱۸۰٬۰۰۰، ۲۲۸٬۰۰۰ و ۲۷۶٬۰۰۰ خواهد بود. مقایسه دو جدول کمک می‌کند تشخیص دهید در محدوده مفروض، کدام متغیر اثر پولی بزرگ‌تری دارد؛ اما اندازه اثر به دامنه‌های انتخاب‌شده وابسته است، پس دامنه باید با شواهد فروش، ظرفیت و قراردادها توجیه شود.

تحلیل حساسیت دو طرفه: حجم و نرخ فروش در یک ماتریس

وقتی تصمیم به ترکیب دو فرض وابسته است، جدول داده دوطرفه مناسب‌تر است. در گوشه بالای چپ ماتریس، ارجاع به سلول سود عملیاتی را قرار دهید. نرخ‌های فروش را در سطر بالا و حجم‌های فروش را در ستون اول بنویسید. کل ماتریس را انتخاب کنید و در Data Table، سلول ورودیِ مربوط به مقادیر سطر را در Row input cell و سلول ورودیِ مربوط به مقادیر ستون را در Column input cell معرفی کنید.

برای مثال، سطرها حجم‌های ۸٬۰۰۰، ۱۰٬۰۰۰ و ۱۲٬۰۰۰ و ستون‌ها نرخ‌های ۱۱۴، ۱۲۰ و ۱۲۶ باشند. هر خانه، سود عملیاتی همان ترکیب را نشان می‌دهد. در حجم ۸٬۰۰۰ و نرخ ۱۱۴، سود برابر ۳۶٬۰۰۰ است؛ در حجم ۱۲٬۰۰۰ و نرخ ۱۲۶، سود به ۳۴۸٬۰۰۰ می‌رسد. این ماتریس برای مذاکره قیمت، برنامه ظرفیت یا تعیین آستانه هشدار کاربرد دارد، زیرا سؤال را از «اگر نرخ تغییر کند چه می‌شود؟» به «در چه ترکیبی از قیمت و حجم به سود قابل قبول می‌رسیم؟» تبدیل می‌کند.

جدول داده در اکسل حداکثر دو متغیر ورودی را آزمون می‌کند. طبق راهنمای رسمی Data Table، برای بیش از دو متغیر باید از رویکردهای دیگر، از جمله سناریوها، استفاده کرد. همچنین این جدول‌ها هنگام محاسبه کاربرگ باز‌محاسبه می‌شوند؛ در مدل‌های بزرگ، زمان محاسبه را پیش از ارائه فایل کنترل کنید.

سناریونویسی بودجه با حساسیت یکسان نیست

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

Scenario Manager اکسل امکان ذخیره و جایگزینی مجموعه‌های مقدار را فراهم می‌کند و می‌تواند گزارش خلاصه بسازد. این ابزار برای مقایسه سناریوی پایه، محتاطانه و فرصت مناسب است. هر سناریو تا ۳۲ سلول متغیر را می‌پذیرد؛ با این حال، تعداد کمترِ مفروضات کلیدی معمولاً بازبینی را روشن‌تر می‌کند. برای یافتن یک ورودی لازم جهت رسیدن به خروجی هدف، مانند حجم فروش لازم برای سود هدف، می‌توان از Goal Seek استفاده کرد؛ مایکروسافت تفاوت آن را با سناریو و جدول داده در این می‌داند که Goal Seek از خروجی هدف به سوی ورودی موردنیاز حرکت می‌کند.

کنترل خطا و تفسیر تصمیم‌محور نتایج

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

بعد از کنترل فرمول، فقط به بیشترین سود نگاه نکنید. سه خروجی را کنار هم بخوانید: سود عملیاتی، حاشیه سود ناخالص و فاصله حجم برنامه‌ریزی‌شده تا نقطه سر به سر. سپس برای هر خانه نگران‌کننده یک اقدام مشروط تعریف کنید؛ مثلاً «اگر حجم و نرخ به این ترکیب برسند، هزینه ثابت جدید تصویب نشود» یا «اگر حاشیه مشارکت از آستانه داخلی پایین‌تر رفت، قیمت‌گذاری و هزینه خرید بازبینی شود». رهنمود CFA Institute بر آزمودن خروجی مدل، توجه به مفروضات اثرگذار و در نظر گرفتن نتایج منفی خارج از حالت عادی تأکید دارد؛ این نگاه، جدول را از یک تمرین اکسل به ابزار کنترل تصمیم تبدیل می‌کند.

چک‌لیست اجرای سریع

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

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

منابع و مطالعه بیشتر

مطالعه بیشتر

همه مقالات