پیش بینی در اکسل با تابع Forecast + مثال

پیش بینی در اکسل به سادگی امکان پذیر است!

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

این پیش بینی ها معمولاً شامل پیش‌بینی مقادیری در آینده می شود که بر اساس تاریخ هایی که آن هم توسط اکسل پیش گویی می شود، انجام می گیرد.

این پیش بینی ها می تواند حول محور فروش، تولید، تعداد، قیمت سکه و طلا یا هر KPI های دیگری بچرخد.

فرآیند ایجاد پیش‌بینی در اکسل به آسانی کلیک کردن بر روی دکمه «Like» در یک ویدیوی YouTube است.

برای اینکه به شما نشان دهیم که اینکار چقدر آسان است و با ابزارهای پیش بینی در اکسل آشنا شوید، گزارشی فصلی (سه ماهه) از مقادیر فروش یک کسب و کار را در اینجا آورده ایم.

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

پیش بینی فروش برای فصول آینده

همانگونه که در عنوان بالا می بینید، می خواهیم برای فصول در پیش رو (فصولی که هنوز نیامده اند) پیش بینی کنیم که چه تعداد فروش خواهیم داشت.

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

دقت داشته باشید که هنگامی که می خواهید میزان تغییرات یک عامل را براساس گذر زمان داشته باشید، نمودارهای خطی بهترین ایده برای این کار هستند.

برای شروع، ابتدا ستون “تاریخ” و “مقدار فروش” را با تمام داده هایشان انتخاب کنید و از طریق آدرس زیر یک نمودار خطی ترسیم کنید:

 Insert (tab) –> Charts (group) –> Line Chart

به نقاط پستی و بلندی در نمودار دقت کنید. بدون هیچ شکی واضح است که فصل ها در تعداد فروش کالاها دخالت دارند.

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

پیش بینی فروش برای فصول آینده

ابزار Forecast Sheet (پیش بینی) اولین بار در اکسل نسخه ۲۰۱۶ ارائه شد و در منوی Data و در گروه Forecast قرار داده شد.

برای استفاده از این ابزار، محدوده داده های تاریخ و فروش را انتخاب کنید (در اینجا تمام داده های ستون تعداد فروش و تاریخ ها). اما سرستون ها را انتخاب نکنید. سپس روی گزینه Forecast Sheet کلیک کنید.

همانگونه که مشاهده می کنید این نمودار، خروجی پیش بینی شده توسط اکسل را به ما نشان می دهد.

در ضمن ما می توانیم از بین نمودار خطی یا نمودار میله ای، یکی را به دلخواه انتخاب کنیم و روند تغییرات آینده را ببینیم.

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

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

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

اگر میزان اطمینان شما از این داده ها، بر روی ۹۵% (تنظیمات پیش فرض اکسل) تنظیم شده باشد، بنابراین نتیجه گیری این است که ۹۵% نقاط داده بین کران بالا و پایین قرار می گیرند.

نکته جالب اینجاست که شما می توانید با کمک گزینه Forecast End تاریخ پیش بینی اکسل را تغییر دهید و تاریخ مدنظر خود را اعمال کنید.

کار با گزینه های پیشرفته

با کلیک بر روی دکمه Options (پایین سمت چپ)، تعداد بیشتری گزینه برای سفارشی سازی این نمودار به شما پیشنهاد می شود.

شما به سلیقه خود می توانید گزینه های زیر را تغییر دهید:

• Forecast Start: این گزینه به شما این امکان را می دهد تا تاریخ شروع پیش بینی را تغییر دهید. در واقع این گزینه شروع تاریخ تجزیه و تحلیل را به ما گوشزد می کند. توصیه می شود این تاریخ را به تاریخی قبل از آخرین نقطه داده تنظیم کنید تا از این روش نیز بتوان بهتر مقدار پیش بینی را با واقعیت مقایسه کرد.
• Confidential Interval: فاصله اطمینان محدوده ای است که در اطراف (بالا و پایین) هر کدام از مقادیر پیش بینی شده وجود دارد. انتظار می رود براساس پیش بینی ها (با توزیع نرمال) ۹۵% از نقاط قابل ارائه در آینده، سقوط داشته باشند. این فاصله اطمینان به شما کمک می کند تا بهتر پیش بینی را صحت سنجی کنید. هرچه این درصد بالاتر باشد، اطمینان بیشتری در پیش بینی برای نقطه خاصی وجود دارد.
• Seasonality: تغییرات فصلی عددی است که برای طول (تعداد نقاط) الگوی فصلی درنظر گرفته می شود. به عنوان مثال در چرخه های فروش سه ماهه، با هر نقطۀ نشان دهنده یک ماه، عدد Seasonality برابر ۴ است.
• Timeline Range: محدوده سلول هایی است که داده های ستون تاریخ را شامل می شود.
• value Range: محدوده سلول هایی است که داده های ستون فروش را در بر می گیرد.
• Fill Missing Points Using: اگر هر گونه جای خالی (Gap) در بین داده ها مشاهده شود، اکسل از درون یابی برای پر کردن آن جای خالی استفاده می کند. این بدین معنی است که نقاطی که داده ها در آن وجود ندارند، به روش میانگین وزنی نقاط مجاور آنها محاسبه می شوند. اگر می خواهید نقاط نامعلوم را صفر در نظر بگیرید، کلیک کنید و صفر را انتخاب کنید.
• Aggregate Duplicates Using: هنگامی که داده های شما در یک تاریخ حاوی چندین مقدار باشد، اکسل میانگین مقادیر را درنظر می گیرد. شما می توانید به سلیقه خود این گزینه را تغییر دهید تا مثلاً به جای Ave، مقدار Sum یا Count یا median و … را درنظر بگیرد.

ایجاد کاربرگ پیش بینی (Forecast)

با کلیک بر روی دکمه Create در صفحه Create Forecasting Worksheet، وارد یک کاربرگ (Sheet) جدید می شوید و با دو المان روبرو می شوید:

• یک جدول اکسل که هم تاریخ های گذشته و هم تاریخ های آینده (پایین کاربرگ) را در بردارد. همینطور مقادیر قبلی و مقادیر پیش بینی شده را نیز نشان می دهد. مقادیر پیش بینی با کران های بالا و پایین به طور مجزا قابل رویت است.
• نموداری که مقادیر جدول را به صورت خطوط یا میله های آبی و نارنجی رنگ که قبلاً توضیح داده شده است.

با تنظیم مقادیر جدول، نمودار ما نیز به طور خودکار خود را به روز خواهد کرد.

بررسی توابع پیش بینی

با کلیک بر روی هر کدام از سلول های پیش بینی شده جدول، از قسمت نوار فرمول نویسی می توانیم ببینیم که اکسل با استفاده از تابع FORECAST.ETS مجموعه ای از داده ها را برای ما پیش بینی کرده است.

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

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