এক্সেল বিশাল ডেটাসেট হ্যান্ডল করার জন্য সবচেয়ে ব্যাপকভাবে ব্যবহৃত টুল। এক্সেল-এ আমরা বহুবিধ মাত্রার অসংখ্য কাজ সম্পাদন করতে পারি। এই আর্টিকেলে, আমি ব্যাখ্যা করব কীভাবে এক্সেল-এ হোল্ট-উইন্টারস এক্সপোনেনশিয়াল اسمুথিং প্রয়োগ করতে হয়। এই পদ্ধতি পূর্বাভাসের জন্য উপকারী।
ওয়ার্কবুক ডাউনলোড করুন
এই ওয়ার্কবুক ডাউনলোড করুন এবং আর্টিকেল পড়ার সময় অনুশীলন করুন।
হোল্ট-উইন্টারস এক্সপোনেনশিয়াল اسمুথিং-এ পরিচিতি
হোল্ট-উইন্টারস পদ্ধতি একটি উন্নত পূর্বাভাস পদ্ধতি। এটি সিজন্যালিটি এবং ট্রেন্ড ইফেক্ট বিবেচনা করে পূর্বাভাস দেয়। তাই মানগুলো কিছু র্যান্ডমনেস বাদে প্রকৃত মানগুলোর প্রতিনিধিত্ব করে।
এক্সেল-এ হোল্ট-উইন্টারস এক্সপোনেনশিয়াল اسمুথিং ব্যবহার করে পূর্বাভাস মান ক্যালকুলেট করার ফর্মুলা হলো:
Ft+k = (Lt+k*Tt)*St-m+k
যেখানে, F = পূর্বাভাসিত মান
L = লেভেল
T = ট্রেন্ড
M = ত্রৈমাসিক कालের জন্য ৪, মাসিক কালের জন্য ১২
S = সিজন্যালিটি সূচক
এক্সেলে হোল্ট উইন্টারস এক্সপোনেনশিয়াল اسمুথিং সম্পাদনের ১১ সহজ ধাপ
এটি আজকের আর্টিকেলের ডেটাসেট। আমাদের কাছে ২০২২ വര্গে ত্রৈমাসিক বিক্রয় রয়েছে।
আমরা ২০২৩ সালের পূর্বাভাসিত মান বের করব।
ধাপ ১: র্যান্ডম অ্যালফা, বেটা ও গামা মান নির্ধারণ
প্রথম কাজ হলো কনস্ট্যান্ট alpha, beta, এবং gamma-র জন্য কিছু র্যান্ডম মান নির্ধারণ করা।
আমরা পরে এই মানগুলো অপ্টিমাইজ করব।
ধাপ ২: প্রাথমিক সিজন্যাল সূচক ক্যালকুলেট
তারপর, আমরা প্রথম ৪ ত্রৈমাসের জন্য প্রাথমিক সিজন্যাল সূচক নির্ধারণ করব। প্রাথমিক সিজন্যাল সূচকগুলো বের করব প্রতিটি ত্রৈমাসের বিক্রয়কে প্রথম ৪ ত্রৈমাসের গড় বিক্রয়-র সাথে ভাগ করে। এজন্য AVERAGE ফাংশন ব্যবহার করব।
- F11-এ যান এবং নিচের ফর্মুলা লিখুন।
=C11/AVERAGE($C$11:$C$14)
- এখন ENTER চাপুন। এক্সেল আউটপুট ফেরত দেবে।
- তারপর Fill Handle ব্যবহার করে F14 পর্যন্ত AutoFill করুন।
ধাপ ৩: প্রাথমিক লেভেল ও ট্রেন্ড নির্ধারণ
এখন ডেটাসেটের জন্য প্রাথমিক লেভেল ও ট্রেন্ড ক্যালকুলেট করার পালা।
প্রাথমিক লেভেল হলো ৫ম ত্রৈমাস-এর লেভেল কারণ এক বছরে ৪ টি ত্রৈমাস থাকে।
প্রাথমিক লেভেলের ফর্মুলা হলো,
L5 = Y5/S1
যেখানে, Y5= ৫ম ত্রৈমাসের বিক্রয়।
S1= ১ল ত্রৈমাসের সিজন্যাল সূচক।
এটি বের করতে,
- D15-এ যান এবং ফর্মুলা লিখুন
=C15/F11
- এখন ENTER চাপুন। এক্সেল আউটপুট ফেরত দেবে।
এবারের পালা প্রাথমিক ট্রেন্ড ক্যালকুলেট করা (যেটিও ৫ম ত্রৈমাসের জন্য)। প্রাথমিক ট্রেন্ডের ফর্মুলা হলো,
T5 = L5-Y4/S4
যেখানে, L5 = ৫ম ত্রৈমাসের লেভেল।
Y4 = ৪র্থ ত্রৈমাসের বিক্রয়।
S4 = ৪র্থ ত্রৈমাসের সিজন্যাল সূচক।
এটি ক্যালকুলেট করতে,
- E15-এ যান এবং ফর্মুলা লিখুন,
=D15-C14/F14
- ENTER চাপিয়ে আউটপুট পান।
ধাপ ৪: পরবর্তী সিজন্যাল সূচক ক্যালকুলেট
এখন, আমরা সাধারণ ফর্মুলা ব্যবহার করে পরবর্তী সিজন্যাল সূচক বের করব। সিজন্যাল সূচক ক্যালকুলেট করার সাধারণ ফর্মুলা হলো,
St = ɣ(Yt/Lt)+(1-ɣ)St-m
যেখানে
L = লেভেল।
T = ট্রেন্ড।
M = ত্রৈমাসিক কালের জন্য ৪, মাসিক কালের জন্য ১২।
S = সিজন্যালিটি সূচক।
Ɣ = কোইফিশিয়েন্ট।
সিজন্যাল সূচক ক্যালকুলেট করতে,
- F15-এ যান এবং নিচের ফর্মুলা লিখুন
=$C$6*(C15/D15)+(1-$C$6)*F11
- ENTER চাপুন।
- F22 পর্যন্ত AutoFill করুন।
নোট: একটু এরর উপেক্ষা করুন, পরবর্তী Levels এবং Trends মাপার পর এটা ঠিক হয়ে যাবে।
ধাপ ৫: পরবর্তী লেভেলগুলো নির্ধারণ
এখন আমি দেখাব কীভাবে পরবর্তী লেভেলগুলো নিচের ফর্মুলা দিয়ে নির্ধারণ করতে হয়,
Lt = α(Yt/St-m)+(1-α)(Lt-1+Tt-1)
যেখানে
L = লেভেল
T = ট্রেন্ড
M = ত্রৈমাসিক কালের জন্য ৪, মাসিক কালের জন্য ১২
S = সিজন্যালিটি সূচক
α = কোইফিশিয়েন্ট
- D16-এ যান এবং নিচের ফর্মুলা লিখুন
=$C$4*(C16/F12)+(1-$C$4)*(D15+E15)
- ENTER চাপুন।
- D22 পর্যন্ত AutoFill করুন।
ধাপ ৬: পরবর্তী ট্রেন্ড মাপা
আসুন ট্রেন্ড ইফেক্টের ধারণা নেই। ফর্মুলা হলো
Tt= β(Lt-Lt-1)+(1-β)Tt-1
যেখানে
L = লেভেল
T = ট্রেন্ড
M = ত্রৈমাসিক কালের জন্য ৪, মাসিক কালের জন্য ১২
S = সিজন্যালিটি সূচক
β= কোইফিশিয়েন্ট
ট্রেন্ড ইফেক্ট ক্যালকুলেট করতে,
- E6-এ যান এবং নিচের ফর্মুলা লিখুন
=$C$5*(D16-D15)+(1-$C$5)*E15
- ENTER চাপিয়ে চলুন।
- E22 পর্যন্ত AutoFill করুন।
ধাপ ৭: প্রকৃত বিক্রয়ের সাথে তুলনার জন্য পূর্বাভাসিত মান বের করা
এখন, আমরা প্রকৃত বিক্রয়ের সাথে তুলনার জন্য পূর্বাভাসিত মান ক্যালকুলেট করব। প্রথমটি হবে ৬ষ্ঠ ত্রৈমাসের জন্য। পূর্বাভাসিত মান ক্যালকুলেট করার ফর্মুলা (তুলনার জন্য) হলো,
Ft = (Lt-1 + Tt-1)* St-M
তাহলে করা যাক।
- G16-এ যান এবং নিচের ফর্মুলা লিখুন
=(D16+E16)*F12
- ENTER চাপিয়ে যান।
- G22 পর্যন্ত AutoFill করুন।
ধাপ ৮: পূর্বাভাস এরর ক্যালকুলেট
এখন, আমরা প্রকৃত বিক্রয় থেকে পূর্বাভাসিত মান বিয়োগ করে পূর্বাভাস এরর বের করব।
- H16-এ যান এবং ফর্মুলা লিখুন
=C16-G16
- তারপর ENTER চাপুন।
- H22 পর্যন্ত AutoFill করুন।
ধাপ ৯: পূর্বাভাস করার ত্রৈমাসের জন্য K মান নির্ধারণ
এখন পূর্বাভাস ক্যালকুলেট করার পালা। কিন্তু তার আগে, আমাদের কো-এফিশিয়েন্ট k বুঝতে হবে। এটি ভবিষ্যতের কাল নির্দেশ করে যাতে পূর্বাভাস করা হবে। আমাদের কেসে, আমরা ২০২৩ সালের ৪ টি ত্রৈমাসের পূর্বাভাস বের করব। এবং আমাদের কাছে ২০২২ সালের ডেটা উপলব্ধ।
সুতরাং, ২০২৩ সালের প্রথম ত্রৈমাসের জন্য k-এর মান হবে ১, ২য় ত্রৈমাসের জন্য ২, এভাবে চলবে।
ধাপ ১০: পূর্বাভাসিত মান ক্যালকুলেট
এখন, আমরা আমাদের পূর্বাভাসিত মান ক্যালকুলেট করার জন্য প্রস্তুত। আমরা শেষ উপলব্ধ লেভেল, ট্রেন্ড এবং সিজন্যালিটি ব্যবহার করব এগুলো বের করার জন্য।
- G23-এ যান এবং নিচের ফর্মুলা লিখুন
=($D$22+F23*$E$22)*F19
- তারপর ENTER চাপিয়ে আউটপুট পান।
- এখন, G25 পর্যন্ত AutoFill করুন।
ধাপ ১১: অ্যালফা, বেটা, এবং গামা অপ্টিমাইজ
এখন, এরর মিনিমাইজ করার জন্য, আমরা alpha, beta, এবং gamma-র মান অপ্টিমাইজ করব। এজন্য আমরা এক্সেল সলভার-র সাহায্য নেব।
- প্রথমে, রুট মিন স্কয়ার এরর ক্যালকুলেট করতে হবে। এজন্য C7-এ যান এবং নিচের ফর্মুলা লিখুন
=SQRT(SUMSQ(H15:H21)/COUNT(H15:H21))
ফর্মুলা ব্রেকডাউন:
- COUNT(H15:H21) → সেল সংখ্যা গণনা।
- আউটপুট → ৭
- SUMSQ(H15:H21) → H5:H11-এর বর্গগুলোর যোগফল ক্যালকুলেট।
- আউটপুট → ৪৬৩৪৯৩৬৫৩৩০১
- =SQRT(SUMSQ(H15:H21)/COUNT(H15:H21)) → RMSE ক্যালকুলেট করে
- =SQRT(৯৯২.৪৬৩৪৯৩৬৫৩৩০১/৭)
- =SQRT(১৪১.৭৮০৪৯৯০৯৩৩২৯)
- আউটপুট → ৯০৭২
- তারপর ENTER চাপুন।
- এখন, Data ট্যাবে যান >> Solver সিলেক্ট করুন।
- Solver Parameters উইন্ডো পপ আপ হবে। যেহেতু আমরা এরর মিনিমাইজ করতে চাই, আমাদের উদ্দেশ্য RMSE-কে ন্যূনতম করতে হবে কোইফিশিয়েন্ট-দের মান পরিবর্তন করে।
- তারপর, কনস্ট্রেইন্ট যোগ করতে Add ক্লিক করুন।
- Add Constraint উইন্ডো আসবে। কনস্ট্রেইন্টগুলো হলো 0<=α,४,ß<=1। সুতরাং প্রথম কনস্ট্রেইন্ট যোগ করতে cell reference এবং value সেট করুন। (ছবি দেখুন)
- এভাবে দ্বিতীয় কনস্ট্রেইন্ট যোগ করার পর আপনার আউটপুট নিচের মত হবে। তারপর Solve ক্লিক করুন।
- এক্সেল alpha, beta, এবং gamma অপ্টিমাইজ করে এরর মিনিমাইজ করবে।
মনে রাখার বিষয়গুলো
- আপনাকে solver add-in এক্টিভেট করতে হবে।
- আমরা উপলব্ধ প্রকৃত বিক্রয়ের সাথে তুলনার জন্য পূর্বাভাস ক্যালকুলেট করার সময় k value-র চিন্তা করি না।
উপসংহার
এই আর্টিকেলে, আমি ব্যাখ্যা করেছি কীভাবে এক্সেল-এ হোল্ট-উইন্টারস এক্সপোনেনশিয়াল اسمুথিং প্রয়োগ করতে হয়। আশা করি এটি সবার কাজে লাগবে। কোনো প্রশ্ন থাকলে কমেন্ট করুন।
