پیش بینی با ابزار Forecast Sheet در اکسل

پیش بینی با ابزار Forecast Sheet در اکسل

همه ما در زندگی خود پیش بینی هایی برای آینده انجام می دهیم و این پیش بینی ها در کسب و کار ما هم صورت می گیرد.قبلا در اکسل به روش های گوناگون این پیش بینی ها را انجام می دادیم ولی مایکروسافت این نیاز ما را درک کرده و در نسخه جدید اکسل یعنی Excel 365 یک ابزار اختصاصی برای آن طراحی کرده است و نام آن را Forecast Sheet نهاده و دقیقا در کنار ابزار What-If Analysis اکسل قرار گرفته است.

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

آنچه در این آموزش فرا می گیرید:

• فعال کردن Forecast Sheet
• تنظیمات Forecast Sheet
• روش پیش بینی Forecast Sheet
• تفاوت Forecast Sheet با Forecasting

پیش از هر چیز بیان نماییم برای کار تخصصی ( و نه کارهای عادی) نیازمند است دروسی مانند اقتصاد سنجی و آشنایی با مفاهیمی مانند سری های زمانی را آموخته باشید.ولی برای کاربری معمولی نیاز به دانستن هیچ مفهومی نمی باشد و اکسل به طور پیش فرض تمام تنظیمات لازم را اعمال می نماید.

برای فعال کردن Forecast Sheet به مسیر زیر بروید:

Data>Forecast>Forecast Sheet

در نسخه های قبلی اکسل، تابعی به نام Forecast داشتیم ولی در نسخه جدید ابزاری برای این کار طراحی شده است.از تابع Forecast به منظور پیش بینی های خطی و یا روند های خطی گسترده بهره می گرفته اند اما تابع جدید در اکسل 365 از الگوریتم  (Exponential Triple Smoothing (ETS (روش هموار سازی نمایی)به منظور پیش بینی و کشف الگو های فصلی و فواصل اطمینان بهره می گیرد.

برای استفاده از این ابزار مراحل زیر را طی می کنیم:

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

نکته1:جداول زمانی بایستی دارای فواصل سازگار بین نقاط ارزشی خود باشند نظیر فواصل ماهانه، سالانه و عددی

نکته2: پیش بینی مقادیر تا زمانی که بیش از حداقل 70% داده ها در دسترس باشند، صحیح می باشد.

2- انتخاب هر دو ستون داده ها(اگر یک سلول از سری را انتخاب نمایید اکسل به طور اتوماتیک مابقی داده ها را انتخاب می نماید)

3- به مسیر زیر رفته و بر روی منوی Forecast Sheet کلیک نمایید.

Data>Forecast>Forecast Sheet

4- انتخاب یکی از نمودار های خطی یا ستونی

5- وارد نمودن تاریخ پایان و اتمام پیش بینی در منوی Forecast End و سپس بر روی Create کلیک کنید.اکنون می توانید نمودار پیش بینی را از تاریخی که داده در آن وجود ندارد را ببینید.و این داده ها در ستون پیشبینی در جدول هم قابل مشاهده می باشد.

اکنون به بررسی گزینه های موجود در Options می پردازیم:

Forcast Start :

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

Confidence interval:

به غیر از پیش بینی مقادیر آتی برای سری های زمانی، ورودی پیش بینی الگوریتم ETS ،می تواند فاصله اطمینان را هم برگرداند.

 فاصله اطمینان در سنجش و درک میزان دقت پیش بینی کمک می نماید

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

 سطح فرضی 95% اطمینان می تواند کم و زیاد شود و کاربرد آن در دو مورد می باشد:

1) کاهش عرض فاصله اطمینان به منظور درک دقت پیش بینی

2) به منظور تست و آزمایش گزینه های پیشرفته مختلف(نظیر چگونگی حساب کردن گزینه های مفقود روند فصلی و …) و مشخص کردن این موضوع که اگر فاصله اطمینان پیش فرض عریض تر یا طویل تر باشد.

Seasonality:

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

نکته: اکسل به هنگام تنظیم دستی اعداد کمتر از 2 دوره، قابلیت تشخیص اجزای فصلی را ندارد و زمانی که Seasonality به اندازه کافی مشخص نباشد، پیش بینی به صورت خطی اجرا می گردد.

Include forecast statistic:

انتخاب این گزینه امکان مشاهده میزان شاخص های دقت و صحت در روند را، نظیر آلفا، بتا، گاما و … را فراهم می نماید.

Timeline Range & Value Range:

به ترتیب برای ایجاد تغییرات بازه زمانی و مقدار بکار می روند و تغییر هر دو باید با یکدیگر سازگاری داشته باشد.

Fill missing point using :

این گزینه امکان انتخاب دو حالت “interpolation”  و “zeros”را می دهد به منظور بررسی نقاط از دست رفته اکسل از درون یابی خطی(interpolation) استفاده می نماید بدین صورت که نقاط مفقود شده به عنوان میانگین وزنی نقاط مجاور خود تکمیل خواهند گردید البته تا زمانی که حداکثر 30% نقاط مفقود شده باشند. زمانی که هدف صفر فرض نمودن نقاط مفقود شده باشد (zeros) انتخاب می گردد.

Duplicate Aggregates Using :

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