اگر اطلاعات فروش ماه‌های گذشته را در اکسل ثبت کرده‌اید، می‌توانید بدون محاسبات پیچیده و نرم‌افزارهای آماری جداگانه، درآمد ماه‌های آینده را تخمین بزنید. تابع TREND در اکسل با استفاده از رگرسیون خطی، روند داده‌های قبلی را بررسی می‌کند و بر اساس آن مقادیر احتمالی آینده را محاسبه می‌کند. این روش برای برآورد اولیه درآمد، پیش‌بینی فروش و برنامه‌ریزی مالی کسب‌وکارهای کوچک و بزرگ کاربرد دارد.
برای استفاده از این قابلیت در مایکروسافت اکسل، به داده‌های تاریخی نسبتاً منظم نیاز دارید؛ به عنوان مثال، درآمد ثبت‌شده در ۲۴ ماه گذشته می‌تواند مبنایی برای پیش‌بینی ۱۲ ماه آینده باشد. البته خروجی تابع TREND تضمین‌کننده درآمد واقعی نیست و دقت آن به کیفیت داده‌ها و میزان ثبات روند فروش بستگی دارد.
پیش‌بینی درآمد و فروش آینده در اکسل با تابع TREND
تابع TREND رابطه خطی میان داده‌های گذشته را پیدا می‌کند و از این رابطه برای تخمین مقادیر جدید استفاده می‌کند. اگر درآمد کسب‌وکار شما طی ماه‌های گذشته روندی نسبتاً منظم داشته باشد، این تابع می‌تواند تصویری اولیه از درآمد احتمالی ماه‌های بعد ارائه دهد.
فرض کنید درآمد ۲۴ ماه گذشته را در ستون B و شماره ماه‌ها را در ستون A وارد کرده‌اید. داده‌های تاریخی از ردیف ۲ تا ۲۵ قرار دارند و می‌خواهید درآمد ۱۲ ماه آینده را در ردیف‌های ۲۶ تا ۳۷ پیش‌بینی کنید.
در سلول B26 فرمول زیر را وارد کنید:
=TREND(B2:B25,A2:A25,A26:A37)
در نسخه‌های مایکروسافت ۳۶۵ و اکسل ۲۰۲۱ یا نسخه‌های جدیدتر که از آرایه‌های پویا پشتیبانی می‌کنند، با فشردن Enter نتیجه پیش‌بینی در سلول‌های پایین گسترش پیدا می‌کند؛ به شرط آنکه سلول‌های مقصد خالی باشند. در نسخه‌های قدیمی‌تر ممکن است لازم باشد ابتدا محدوده خروجی را انتخاب کنید و فرمول را با ترکیب کلیدهای Ctrl + Shift + Enter تأیید کنید.
برای اینکه نتیجه درست باشد، ستون A باید ترتیب واقعی ماه‌ها را نشان دهد و مقادیر ستون B نیز با همان ماه‌ها تطابق داشته باشند. اگر تاریخ‌ها را به‌جای شماره ماه وارد می‌کنید، بهتر است از مقادیر تاریخ واقعی و منظم استفاده کنید تا فاصله زمانی میان داده‌ها به‌درستی در محاسبات لحاظ شود.
آشنایی با آرگومان‌های تابع TREND در اکسل
فرمول TREND چهار آرگومان دارد. سه آرگومان اول برای تعیین داده‌های گذشته و مقادیر مورد پیش‌بینی استفاده می‌شوند و آرگومان آخر مشخص می‌کند که خط روند از مبدأ مختصات عبور کند یا نه.
ساختار کلی تابع به این صورت است:
=TREND(known_y's, [known_x's], [new_x's], [const])
- Known_y's: محدوده مقادیر وابسته و شناخته‌شده؛ در این مثال، درآمد ماه‌های گذشته است. این آرگومان ضروری است.
- Known_x's: مقادیر متناظر با داده‌های قبلی؛ مانند شماره ماه یا تاریخ فروش. اگر این آرگومان حذف شود، اکسل از شماره‌های متوالی استفاده می‌کند.
- New_x's: دوره‌هایی که می‌خواهید درآمد آن‌ها را پیش‌بینی کنید؛ مانند شماره ماه‌های آینده. در صورت حذف، تابع از مقادیر شناخته‌شده استفاده می‌کند.
- Const: اگر مقدار TRUE باشد یا آرگومان را وارد نکنید، اکسل مقدار عرض از مبدأ را به‌طور معمول محاسبه می‌کند. اگر FALSE باشد، عرض از مبدأ صفر در نظر گرفته می‌شود.
در بیشتر پیش‌بینی‌های معمول فروش، بهتر است آرگومان آخر را حذف کنید تا اکسل عرض از مبدأ را بر اساس داده‌های واقعی محاسبه کند. صفر کردن آن تنها زمانی مناسب است که از نظر مسئله مورد بررسی، عبور خط روند از مبدأ توجیه داشته باشد.
خطاهای رایج هنگام پیش‌بینی فروش با تابع TREND
حتی اگر فرمول بدون خطا اجرا شود، ممکن است پیش‌بینی فروش در اکسل دقیق نباشد. معمولاً مشکل به ترتیب آرگومان‌ها، داده‌های ناقص یا فرض خطی بودن روند مربوط می‌شود.
جابه‌جا وارد کردن محدوده درآمد و زمان
در تابع TREND، ابتدا محدوده درآمدهای گذشته و سپس محدوده زمان‌های متناظر وارد می‌شود. اگر این دو محدوده را جابه‌جا کنید، اکسل رابطه اشتباهی را محاسبه می‌کند و خروجی ممکن است غیرواقعی باشد. ترتیب آرگومان‌های این تابع با بعضی توابع پیش‌بینی دیگر یکسان نیست؛ بنابراین هنگام کپی کردن فرمول‌ها، ترتیب محدوده‌ها را بررسی کنید.
استفاده از داده‌های ناقص یا ماه‌های نامنظم
اگر اطلاعات فروش یک ماه خالی باشد یا فاصله زمانی میان داده‌ها درست ثبت نشده باشد، نتیجه ممکن است گمراه‌کننده شود. به عنوان مثال، اگر داده‌های ژانویه، فوریه، مارس و مه را به‌عنوان چهار ماه متوالی وارد کنید، فاصله واقعی میان مشاهدات نادیده گرفته می‌شود. بهتر است ماه‌های موجود را با تاریخ واقعی یا شماره دوره منظم مشخص کنید و سلول‌های خالی و خطاهای داده را پیش از محاسبه بررسی کنید.
همچنین محدوده‌های تاریخی باید با یکدیگر تطابق داشته باشند. اگر درآمدها در سلول‌های B2 تا B25 هستند، مقادیر زمان نیز باید برای همان تعداد دوره و به همان ترتیب ثبت شده باشند.
بررسی اعتبار نتایج پیش‌بینی
خروجی عددی به‌تنهایی نشان نمی‌دهد که پیش‌بینی قابل اعتماد است. نموداری از درآمدهای گذشته رسم کنید و روند کلی را با خط روند خطی مقایسه کنید. اختلاف جزئی میان مقادیر تابع و معادله نمایش‌داده‌شده روی نمودار می‌تواند ناشی از گرد شدن ضرایب معادله در نمودار باشد.
برای ارزیابی بهتر، بخشی از داده‌های تاریخی را از محاسبه کنار بگذارید و بررسی کنید که تابع تا چه اندازه می‌تواند آن دوره‌ها را پیش‌بینی کند. این کار به شما کمک می‌کند قبل از استفاده از اعداد برای بودجه‌بندی یا برنامه‌ریزی کسب‌وکار، میزان خطای احتمالی را بسنجید.
چه زمانی تابع TREND برای پیش‌بینی درآمد مناسب است؟
تابع TREND زمانی مفیدتر است که داده‌های گذشته روندی تقریباً صعودی یا نزولی داشته باشند و هدف، تخمین یک روند خطی باشد. این تابع برای پیش‌بینی اولیه فروش، درآمد یا هزینه‌ها مناسب است؛ اما الگوهای فصلی و تغییرات ناگهانی بازار را به‌صورت خودکار مدل‌سازی نمی‌کند.
اگر فروش کسب‌وکار در تعطیلات افزایش پیدا می‌کند یا در برخی ماه‌ها افت فصلی دارد، ممکن است یک خط مستقیم تصویر دقیقی از آینده ارائه ندهد. سفارش‌های بسیار بزرگ، تغییر قیمت محصولات، ورود رقیب جدید و تغییر تقاضای مشتریان نیز می‌توانند پیش‌بینی را از عملکرد واقعی دور کنند.
پیش‌بینی فروش فصلی با ابزار Forecast Sheet اکسل
اگر داده‌های فروش شما الگوی فصلی دارند، ابزار Forecast Sheet می‌تواند گزینه مناسب‌تری باشد. این قابلیت بر اساس داده‌های تاریخی زمان‌محور، یک شیت جدید شامل مقادیر پیش‌بینی‌شده و نمودار ایجاد می‌کند و امکان تنظیم تاریخ پایان پیش‌بینی و نمایش بازه اطمینان را نیز می‌دهد.
برای استفاده از این قابلیت، ابتدا یک ستون تاریخ و یک ستون فروش یا درآمد داشته باشید. سپس محدوده داده‌ها را انتخاب کنید و مسیر زیر را دنبال کنید:
Data → Forecast → Forecast Sheet
داده‌ها ← پیش‌بینی ← برگه پیش‌بینی
در پنجره بازشده، نمودار خطی یا ستونی را انتخاب کنید، تاریخ پایان پیش‌بینی را مشخص کنید و تنظیمات موردنیاز را بررسی کنید. برای تشخیص الگوهای فصلی، بهتر است داده‌های تاریخی به‌اندازه کافی طولانی باشند تا اکسل بتواند تکرار الگوها را شناسایی کند.
چه زمانی باید روش پیش‌بینی دیگری انتخاب کنید؟
اگر درآمد کسب‌وکار به‌سرعت رشد می‌کند یا تغییرات آن به شکل منحنی است، رگرسیون خطی ممکن است مناسب نباشد. در چنین شرایطی می‌توانید روش‌های پیشرفته‌تر را بررسی کنید. برای الگوهای فصلی، Forecast Sheet یا تابع FORECAST.ETS می‌تواند انتخاب بهتری باشد؛ برای روندهای غیرخطی نیز می‌توان از مدل‌های رگرسیونی مناسب استفاده کرد.
در هر روش، پیش‌بینی را با نتایج واقعی مقایسه کنید و پس از ثبت داده‌های ماه جدید، محاسبات را به‌روزرسانی کنید. استفاده از پیش‌بینی به‌عنوان مبنای تصمیم‌گیری، همراه با بررسی شرایط بازار و تغییرات کسب‌وکار، از اتکا به یک عدد قطعی جلوگیری می‌کند.
سیاره‌ی آی‌تی