استخدام دوال الاكسيل فى التخطيط المالي للتكاليف
تعتبر عملية التنبؤ بقيم التكاليف عند أحجام النشاط المختلفة من أبرز المشاكل التى تعترض المخطـط المالي ومصممي الموازنات التخطيطية، خاصة بالنسبة لعناصر التكاليف المختلطة أو المرنة مرونـة غيـر كاملة، والتى تتغير مع تغييرات حجم النشاط ولكن بنسبة أقل من نسبة التغير فى حجم النشاط.
ويتم استخدام برنامج Microsoft Excel فى تقدير قيمة هذه النوعية من العناصر والتنبؤ بها، فى ضوء علاقة هذه العناصر بحجم النشاط. ويتضمن برنامج Microsoft Excel نوعين من الدوال الإحصائية التى يمكن استخدام أحدهما فى عملية التقدير والتنبؤ.. الأولى دالة Trend والثانية دالة Forecast
ولإيضاح كيفية استخدام كل من هاتين الدالتين فى عملية التنبؤ يمكن افتراض المثال التالى:
الشهر | ساعات دوران الآلات | تكاليف صيانة الآلات |
يناير | 5000 | 25000 |
فبراير | 6000 | 28000 |
مارس | 7000 | 31000 |
أبريل | 8000 | 34000 |
مايو | 9000 | 37000 |
يونيو | 10000 | 40000 |
وبفرض أننا نرغب في التنبؤ بقيمة تكاليف الصيانة عن شهر سبتمبر والمتوقع أن تكون فيه ساعات دوران الآلات ١٥٠٠٠ ساعة.
استخدام دالة TERND
تستخدم دالة TREND طريقة القيمة الصغرى لمجموع المربعات المتعارف عليها إحصائيًا، والتى تعتمـد على المعادلة المعروفة بمعادلة الخط المستقيم ص = أ + ب س أو b + mx = y حيث تمثل ص أو y قيمة عنصر التكلف كمتغير تابع، وس حجم النشاط أو x كمتغير مستقل. وتمثل قيمة كل من أ أو b الجزء الثابت من قيمة التكلفة، وقيمة ب أوm الجزء المتغير، وهي القيم المراد التنبؤ بهما.
وتأخذ الدالة الشكل التالي:
TREND(known_ y's, known_x's, New _ x's , const
حيث:
- · تعتبر s'y_known هى قيم عناصر التكاليف المعروفة مسبقًا، والتي سوف تستخدم فى التنبؤ.
- · وتعتبر s'x_known هي قيم أحجام النشاط المعروفة مسبقًا والتى سوف تستخدم فى التنبؤ.
- · وتعتبر s'x_new هى قيمة حجم النشاط المراد التنبؤ بقيمة التكاليف الخاصة به.
- · ويعتبر const أو الثابت قيمة منطقية تحدد إمكانية فرض قيمة الثابت أ أو b لكي تساوي صفر.
- -إذا كانت const تساوي TRUE أو مهملة، يتم حساب b بالشكل المعتاد. حيث يعنـي هـذا أن هناك قيمة ثابتة أي تهمل هذه الوسيطة فى حالة التكاليف المختلطة التى توجد بها قيمة ثابتة.
- إذا كانت const تساوي FALSE، تكون b مجموعة تساوي صفر، ويتم ضبط القيم m لكي تكون ص= ب س .mx = y، أى لا توجد قيمة ثابتة.. وتستخدم فى حالة التكـاليف المرنـة مرونة كاملة حيث يكتب داخل هذه الوسيطة عبارة ”FALSE”
ولحل المثال السابق باستخدام دالة TREND يتم فتح مصنف مكون من ورقة واحدة تدرج بـه بيانـات الجدول السابق، ثم يتم تنشيط الخلية المراد إدراج القيمة المتنبأ بها فيها، ولتكن الخلية F11 ويكون شكل الورقة كما يظهر في الشـكل التالي:
* يتم الضغط على قائمة إدراج ثم اختيار الأمر دالة، ومن المربع الحواري الذي يظهر يتم اختيار مجموعة الدوال الإحصائية، ثم اختيار دالة TREND، وذلك كما يظهر من الشكلين التاليين:
-يتم بعد ذلك الضغط على مفتاح الإدخال أو الموافقة على ظهور مربع حواري يتضمن عناصر الدالـة، ويـتم إدراج مؤشر الفارة في المستطيل الخاص بالعنصر الأول s'y_ known والتأشير على الخلايا الخاصـة بقـيم التكاليف المعروفة في الفترات السابقة.
-أي الخلايا من F٤إلى F٩، وبالنسبة للمستطيل الخاص بالعنصر s'x_known يتم التأشير على الخلايا المتضمنة ساعات الدوران المعروفة من فترات سابقة (الخلايا من E4 إلى E5، أما في المستطيل أمام العنصر s'x_New فيتم كتابة عدد ساعات الدوران (حجم النـشاط) المراد التنبؤ عنده بقيمة التكاليف، وهو ١٥٠٠٠ ساعة دوران.
-على أن يهمل المـستطيل أمـام العنـصر const أي يُترك خاليًا للدلالة على أن هناك قيمة ثابتة ضمن التكلفة، وبالتالي يظهر ناتج الدالة أسفل يمين المربع الحواري، وبالضغط على زر موافق يظهر الناتج داخل الخلية المحددة على أن تظهر مكونات الدالة ضمن شريط المعادلة أعلى عناوين الأعمدة كما في الشكل التالي:
استخدام دالة FORECAST
تستخدم هذه الدالة طريقة الانحدار الخطي فى التنبؤ بالقيم الجديدة، وتأخذ هذه الدالة الشكل التالي:
FORECAST (known_x's ، known_y's x)
حيث:
· أن X هي نقطة البيانات التي تريد التنبؤ بإحدى القيم لها، وهي هنا عدد ساعات الدوران ١٥٠٠٠ ساعة.
· ويعتبر s'y_Known نطاق البيانات التابع، وهو هنا قيم التكاليف فى الفترات السابقة.
· ويعتبر s'x_Known نطاق البيانات المستقل، وهو ساعات الدوران الخاصة بالفترات السابقة.
ويتم اتباع نفس الخطوات السابقة للوصول إلى النتيجة المطلوبة.
خطوات إعداد البيانات التخطيط المالي باستخدام Excel
- فتح مصنف جديد تُسجَل فيه البيانات الأساسية الخاصة بالمشكلة.
- إعداد جدول مكون من ستة حقول يُعنون الحقل الأول باسم حجم النشاط، والثانى باسم إيـرادات المبيعات، والثالث باسم التكلفة المتغيرة للنشاط، والرابع باسم التكاليف الثابتة، الخامس باسم إجمـالى التكاليف، والسادس باسم صافى الربح كما بالشكل التالي:
3- يتم إدراج سلسلة رقمية تمثل أحجام النشاط من حجم صفر حتى حجم ١٥٠٠٠ وحدة، وذلـك فـى الخلايا من C٨:C٢٣ وذلك بأن يدرج الرقم صفر فى الخلية C٨ والرقم ١٠٠٠ فـى الخليـة C٩، وتنشيط الخليتين معًا وسحبهما معًا بالجر من أسفل يسار الخلية C٩ حتى الخليـة C٢٣ فتتولـد سلسلة رقمية فى شكل متوالية عددية حدها الأول الرقم صـفر وحـدها الأخيـر العـدد ١٥٠٠٠ وأساسها العدد ١٠٠٠.
4- فى الخلية D8 يتم إدراج المعادلة =٢$D*C٨ وسحبها بالجر إلـى أسـفل لحـساب إيـرادات المبيعات للسلعة عند أحجام النشاط المختلفة.
5- فى الخلية E8 يتم إدراج المعادلة =٣$D*$C٨ وسحبها بالجر إلى أسفل لحساب التكلفة المتغيرة للسلعة عند أحجام النشاط المختلفة.
6- فى الخلية F8 يتم إدراج المعادلة =٤$D $ وسحبها بالجر إلى أسـفل لإدراج التكـاليف الثابتـة للسلعة عند أحجام النشاط المختلفة.
7- فى الخلية G8 يتم إدراج المعادلة = F٨+E٨ وسحبها بالجر إلى أسفل إجمالى التكاليف للـسلعة عند أحجام النشاط المختلفة.
8- فى الخلية H8 يتم إدراج المعادلة =G٨-D٨ وسحبها بالجر إلى أسفل لحساب صافي الربح للسلعة عند أحجام النشاط المختلفة.
9- يتم تنسيق الجدول من ناحية الأرقام والمحاذاة والخط والنقش باستخدام الأمر تنسيق /خلايا كما بالشكل التالي:
وفي نهاية المقال اتمنى أن اكون قد أفدناكم بما يخُصّ هذا الجزء، ونرجو المتابعة للمزيد من المقالات عن تطبيقات المُحاسبة في برنامج Microsoft Excel.