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

پیش بینی تقاضا با کمک اکسل

اکسل (EXCEL)، نرم‌افزاری از مجموعه Office است که اکثراً به آن دسترسی داریم و با محیط آن آشناییم. یکی از امکانات این نرم‌افزار، قابلیت فرمول‌نویسی برای محاسبات ریاضی است؛ یعنی می‌توانیم روابط ریاضی را تعریف کرده و از آن‌ها برای پردازش داده‌ها استفاده کنیم.

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

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

تعریف مسأله

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

تنظیم ستون‌ها و ثبت داده‌های اولیه

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

پیش‌بینی دوره‌های قبلی با نمو هموار ساده

روش نمو هموار دوبل، نتایج حاصل از نمو هموار ساده را با یک مقدار جبرانی جمع می‌کند تا تأثیر الگوی روند جبران شود؛ برای همین در قدم اول باید داده‌ها را با روش نمو هموار ساده پیش‌بینی کنیم. بر اساس روش نمو هموار ساده، مقدار پیش‌بینی شده برای هر دوره برابر است با:

فرمول نمو هموار ساده

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

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

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

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

در اکسل «*» برای ضرب، «/» برای تقسیم،  «+» برای جمع  و «-» برای منهاست. برای تکمیل جمله‌ی اول،‌ علامت * را اضافه کرده و روی خانه مربوط به فروش واقعی در دوره دوم -که عدد ۹۸ در آن نوشته شده- کلیک می‌کنیم. نام این خانه (یعنی G4) به فرمول‌ اضافه می‌شود. فرمولی که نوشتیم، مقدار آلفا (خانه‌ی H4) را در مقدار واقعی فروش در دوره قبل (G4) ضرب می‌کند.

برای نوشتن جمله‌ی دوم، علامت + گذاشته، یک پرانتر باز کرده و در آن مقدار آلفا -یعنی خانه H4- را از عدد یک کم می‌کنیم.

پرانتر را می‌بندیم و آن را در مقدار پیش‌بینی شده برای دوره قبل -دوره دوم- ضرب می‌کنیم. برای این کار علامت * را می‌نویسیم و روی ردیف دوم از ستون «پیش‌بینی با نمو هموار ساده» کلیک می‌کنیم. نام خانه‌ای که بر آن کلیک کردیم به فرمول اضافه می‌شود.

اگر دکمه‌ی Enter را فشار دهیم همان مقدار ۱۷۳ نمایش داده می‌شود، چون مقدار آلفا را وارد نکرده‌ایم و اکسل آن را صفر در نظر گرفته است، پس آلفا را ۰٫۸ انتخاب می‌کنیم تا نتیجه‌ی پیش‌بینی دوره‌ی سوم نمایش داده شود. در این حالت مقدار ۱۱۳ ثبت خواهد شد.

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

خانه‌ی H4 همان جایی است که بالاتر برای ورود مقدار آلفا انتخاب کردیم. برای این که اکسل بفهمد مقدار آلفا در همه محاسبات ثابت می‌ماند، آن را به شکل $H$4 اصلاح می‌کنیم. در فرمولی که نوشته بودیم، در دو جای مختلف از عبارت H4 استفاده شده است. آن‌ها را پیدا می‌کنیم و عبارت $H$4 را جای آن‌ها قرار می‌دهیم.

دکمه Enter را می‌زنیم تا تغییرات ثبت شوند. برای کدنویسی خانه‌های بعدی، دوباره روی خانه ردیف سوم کلیک می‌کنیم. تصویر زیر را ببینید.

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

در ستون سوم باید ببینیم در هر نوبت، فاصله‌ی «آن چه پیش‌بینی کرده‌ایم» تا «مقدار واقعی» چقدر است و آن را بنویسیم. برای این کار فرمول‌نویسی می‌کنیم. در سلول دوم، فرمولی که در تصویر درج شده است را ثبت می‌کنیم. تابع () ABS قدر مطلق است و هر مقداری در پرانتر بنویسیم، آن را مثبت می‌کند. داخل پرانتر، خانه‌ی مربوط به مقدار پیش‌بینی شده (F4) را از خانه‌ی مربوط به فروش واقعی (G4) کم می‌کنیم. به عبارت ساده‌تر، به اکسل می‌گوییم که: مقدار «فروش واقعی» را از مقدار «پیش‌بینی با نمو هموار ساده» کم کن.

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

تنظیم مقدار جبرانی

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

جبران روند در هموارسازی نمایی

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

پیش‌بینی مقدار روند (Trend)

در ادامه اطلاعات ستون «تنظیم Trend» را تکمیل می‌کنیم. برای دوره اول به اطلاعات قبلی دسترسی نداریم و نمی‌توانیم روند را پیش‌بینی کنیم. برای دوره‌ دوم، چون پیش‌بینی برای دوره‌ی اول انجام نشده، باید یک مقدار فرضی انتخاب کنیم. این مقدار را صفر انتخاب کرده‌ایم. کدنویسی را از ردیف سوم شروع می‌کنیم. علامت = می‌گذاریم. خانه‌‌ای که به عنوان ورودی بتا تعیین شده را انتخاب می‌کنیم تا به فرمول اضافه شود.

پرانتز باز می‌کنیم و روی مقدار پیش‌بینی شده برای دوره سوم کلیک می‌کنیم.

مقدار پیش‌بینی شده برای دوره‌ی قبلی را از آن کسر می‌کنیم.

پرانتر باز می‌کنیم. در پرانتر، عدد یک را می‌نویسیم و مقدار بتا را از آن  کم می‌کنیم. این پرانتز را در روند پیش‌بینی شده برای دوره قبل ضرب می‌کنیم.

مقدار بتا را ۰٫۵ فرض می‌کنیم، آن را در خانه مربوطه می‌نویسیم تا روند برای دوره‌ی سوم پیش‌بینی شود.

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

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

پیش‌بینی نهایی دوره‌های قبلی با نمو هموار دوبل

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

این خانه را تا پایین می‌کشیم تا تمام سلول‌ها کدنویسی شوند.

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

میانگین خطا در نتایج نمو هموار ساده و نمو هموار دوبل

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

در بخش Totals روی Average کلیک می‌کنیم. اکسل از مقادیر انتخاب شده‌ میانگین می‌گیرد و نتیجه را زیر ستون ثبت می‌کند.

به همین شکل، میانگین خطا برای ستون دیگر -مربوط به نمو هموار دوبل- را محاسبه می‌کنیم.

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

تنظیم اعداد اعشاری

در جدول، اعداد اعشاری زیادی داریم که آن را نامرتب کرده‌اند. تمام اعداد (فقط اعداد) را انتخاب می‌کنیم، دکمه‌ی راست ماوس را کلیک کرده و Format Cells را می‌زننیم. در پنجره‌ی باز شده روی Number کلیک می‌کنیم و مقدار Decimal Places را روی دو قرار می‌دهیم.

حالا داده‌ها فقط تا دو رقم اعشار نمایش داده می‌شوند و جدول تمیزتری داریم. اگر کمی جدول را مرتب‌تر کنیم، نهایتاً به تصویر زیر می‌رسیم.

تنظیم مقادیر آلفا و بتا

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

پیش‌بینی برای دوره‌های آینده

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

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

همین کار را با ستون تنظیم Trend و ستون پیش‌بینی با نمو هموار تنظیم شده تکرار کنیم. مقدار فروش برای دوره بعد، ۴۴۶ واحد پیش‌بینی می‌شود.

0 پاسخ

دیدگاه خود را ثبت کنید

تمایل دارید در گفتگوها شرکت کنید؟
در گفتگو ها شرکت کنید.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *