كيفية إعداد جدول أعمار الديون بالفيديو والصور ,,, شرح رائع جدا
كاتب الموضوع
رسالة
Moh'd S Hammoudeh ..
الجنس : تاريخ التسجيل : 03/02/2010 العمر : 33
بطاقة الشخصية احترام القوانين: (3/3)
موضوع: كيفية إعداد جدول أعمار الديون بالفيديو والصور ,,, شرح رائع جدا السبت يونيو 15, 2013 7:53 am
مقدمة عن جدول أعمار الديون
إن جدول أعمار الديون يمثل تحليلاً زمنياً لديون الزبائن الواجبة القبض أو التحصيل بحيث يكون مقسم إلى مجموعة من الفترات والتي تساعد المحاسب أو قسم التحصيل على معرفة الوقت الفعلي الذي يستطيع فيه المحاسب أن يطالب الزبون قانونياً بمبلغ الفاتورة المرسلة إليه.
إن الفترات التحليلية يعود تحديدها إلى إدارة الشركة وهي مختلفة من شركة إلى أخرى ، ولكن أكثرها شهرة هي الفترات التالية:
0 أو غير مستحقة.
من 1 إلى 30 يوم.
من 31 إلى 60 يوم.
من 61 إلى 90 يوم.
من 91 إلى 180 يوم.
من 181 إلى 360 يوم.
من 361 فأكثر.
يجب أن يتكون جدول أعمار الديون مما يلي:
تاريخ التقرير.
أسماء الزبائن.
فترة السداد (الإئتمان) لكل زبون.
إجمالي قيمة الديون لكل زبون.
الفترات التحليلية لأعمار الديون.
إجمالي قيمة الديون لكل فترة زمنية.
النسبة المئوية لإجمالي قيمة الديون لكل فترة زمنية.
مشكلة حساب تاريخ الإستحقاق الفعلي
إن عدنا إلى العقد المبرم مع الزبون "شركة المقاولات العامة" نجد أنه نص على أن تكون فترة السداد بعد 30 يوماً من تاريخ تسليم الفاتورة إلى الزبون ، هذا النص جعل العديد من البرامج المحاسبية قاصرة عن حساب فترات الإستحقاق الفعلية وذلك بسبب أن المحاسب قد يقوم بإعداد الفاتورة الشهرية خلال العشرة أيام الأولى من الشهر التالي على سبيل المثال ، ففي مثالنا عن تأجير العمال عن شهر يناير 2012 فقد يقوم المحاسب بالتأخر في اعداد الفاتورة بسبب تأخر وصول ورقة العمل المعتمدة من الزبون ، وقد يطبعها في العاشر من شهر فبراير 2012 ، ولكن بنفس الوقت يجب عليه أن يقوم بإدخال الفاتورة في شهر يناير 2012 داخل البرنامج المحاسبي وذلك لتطبيق مبداً المقابلة (جميع الإيرادات يجب أن تسجل في نفس الفترة التي وقعت أو سجّلت فيها المصاريف التابعة لنفس لفترة). فعندما نقوم بتسجيل القيد المحاسبي في 31 يناير 2012 سيقوم البرنامج المحاسبي بحساب تاريخ الاستحقاق على أساس 30 يوماً من هذا التاريخ ، أي أنه سيحسب تاريخ الاستحقاق في 01 مارس 2012 ، وهو من المفترض أن يكون 11 مارس 2012 (30 يوماً من تاريخ 10 فبراير 2012). إن لم يكن البرنامج المحاسبي مرناً بالشكل المطلوب فيتوجب على المحاسب استخدام برنامج الإكسل لحساب أعمار الديون بالشكل الصحيح دون أخطاء.
دفتر وحساب الأستاذ المساعد
للتغلب على مشكلة حساب تاريخ الإستحقاق سنقوم بإعداد حساب الأستاذ في صفحة مستقلة داخل برنامج الإكسل ، والهدف من ذلك هو معرفة تاريخ الاستحقاق الفعلي لكل فاتورة على حدة.
في البداية ننشئ صفحة جديدة في برنامج الإكسل ونكتب في الخلية الأولى منها اسم الشركة وهو "شركة القوى العاملة المحدودة" ، ثم نكتب في أسفلها مباشرة "حساب الأستاذ المساعد" ، بعد ذلك نحدد الخلية رقم (A3) ونكتب فيها "اسم الزبون: شركة المقاولات العامة" لتظهر لدينا النتيجة كما يلي:
الخطوة التالية هي أن نكتب عناوين أعمدة حساب الأستاذ المساعد. نبدأ بالخلية رقم (A5) ونكتب فيها "التسلسل" ، ثم نكتب على يسارها مباشرة العنوان "رقم الفاتورة" ، ثم "تاريخ الفاتورة" ، ثم "تاريخ التسليم" ، ثم "فترة السداد" ، ثم "تاريخ الاستحقاق" ، ثم "مبلغ الفاتورة" ، ثم "المبلغ المسدد" ، ثم "تاريخ السداد" ، ثم "رقم المرجع" ، ثم "المبلغ المتبقي" ، ثم "عدد الأيام بعد الاستحقاق" لتصبح النتيجة كما يلي:
نبدأ بتعبئة الجدول من الخلية رقم (A6) تحت عامود التسلسل ونكتب فيها رقم "1" وذلك لإدخال أول فاتورة ، وتحت عامود رقم الفاتورة نكتب الرقم "1001" وهو نفس الرقم الذي استخدمناه في اعداد وطباعة الفاتورة ، تاريخ الفاتورة هو "31-01-2012" وذلك من أجل الإعتراف بالإيراد في شهر يناير 2012 ، تاريخ تسليم الفاتورة هو "10-02-2012" ، فترة السداد نأخذها من العقد وهي "30" ، تاريخ الاستحقاق هو ثلاثون يوماً من تاريخ التسليم ولحسابها في الإكسل نحدد الخلية رقم (F6) ونكتب فيها الدالة التالية:
=D6+E6
بالنسبة للخلايا (C6) و (D6) و (F6) يجب أن نُعلم الإكسل أنها خلايا تحتوي على تاريخ ، ولعمل ذلك نحدد هذه الخلايا ونختار من القائمة المنسدلة لطبيعة الخلايا "Short Date" كما يلي:
بالنسبة لمبلغ الفاتورة فهو "33,480" وهونفس المبلغ الذي نتج لدينا بعد استخدام جدول (PivotTable) ونختار طبيعة الخلية من نفس القائمة المنسدلة "Accounting" وذلك لتسهيل قراءة مبلغ الفاتورة ، المبلغ المسدد حتى هذه اللحظة هو "0" ، تاريخ التسديد ورقم المرجع نتركهما فارغين لعدم وجود سداد للفاتورة حتى الآن ، المبلغ المتبقي هو الفرق بين مبلغ الفاتورة والمبلغ المسدد لذلك نكتب في الخلية رقم (K6) الدالة التالية:
=G6-H6
ونجعل طبيعة الخلية أيضاً "Accounting" لتصبح النتيجة كما يلي:
بالنسبة لعدد الأيام بعد الإستحقاق نريد أن نحسب عدد الأيام التي مرت بعد الإستحقاق وذلك من تاريخ اعداد تقرير أعمار الديون ، على فرض أن تاريخ اعداد تقرير أعمار الديون هو 31 مارس 2012 نكتب في الخلية رقم (L4) التاريخ "31-03-2012" ونختار من القائمة المنسدلة لطبيعة الخلية "Short Date" لتصبح النتيجة كما يلي:
لحساب عدد الأيام بعد الاستحقاق سنستخدم الدالة (IF) ، والتي تتكون قاعدتها على الشكل التالي:
القيمة [value_if_true] : هي النتيجة إن تحقق الشرط.
القيمة [value_if_false] : هي النتيجة إن لم يتحقق الشرط.
يظهر لدينا في هذا المثال شرطين مهمين في كتابة المعادلة الرياضية:
نريد أن نحسب الفرق بين تاريخ التقرير وتاريخ الاستحقاق فقط إن كان تاريخ التقرير أكبر من تاريخ الاستحقاق ، وفي حال كان تاريخ التقرير أصغر من تاريخ الاستحقاق فستكون النتيجة هي "0" بحيث لا نريد وجود نتيجة سالبة.
في حالة سداد كامل مبلغ الفاتورة نريد من الإكسل أن يعطينا نتيجة "0" بشكل دائم.
لإستيفاء هذين الشرطين نقوم باستخدام دالتين (IF) بداخل بعضهما البعض ، لذلك نحدد الخلية رقم (L6) ونكتب فيها الدالة التالية:
=IF(L4<F6,0,IF(K6=0,0,L4-F6))
ستكون لدينا النتيجة هي "20" والتي تمثل الفرق بين تاريخ التقرير "31 مارس 2012" و تاريخ الإستحقاق "11 مارس 2012".
لإختبار مدى صحة الدالة الرياضية نقوم بالإختبارين التاليين:
نقوم بتغيير تاريخ التقرير إلى "28 فبراير 2012" على سبيل المثال فتظهر لدينا النتيجة "0" بسبب أن تاريخ "28 فبراير 2012" هو أصغر من تاريخ الإستحقاق "11 مارس 2012".
نعيد تاريخ التقرير كما هو "31 مارس 2012" ونقوم بإفتراض أن الفاتورة تم سدادها بالكامل ونضع مبلغ "33,480" في الخلية رقم "H6" ، فتظهر لدينا النتيجة "0" وهو المطلوب في حالة السداد.
لغرض تنبيه المحاسب إلى وجود فواتير مستحقة التحصيل وفي حال وجود عدد كبير من الفواتير في نفس الجدول نستطيع إضافة شرط لإعطاء لون أحمر لخلايا العامود "عدد الأيام بعد الإستحقاق" ، ولعمل ذلك نحدد الخلية رقم (L6) ومن النافذة الرئيسية لبرنامج الإكسل نضغط على "Conditional Formatting" ثم نختار "New Rule" كما يلي:
ستظهر لدينا نافذة جديدة لتحديد نوع الشرط ، سنختار الخيار الثاني وهو "Format only cells that contain" ، تحت تفاصيل الشرط نختار في البداية قيمة الخلية "Cell Value" ، ثم من القائمة المنسدلة على يمينها مباشرة نختار "greater than or equal to" ، ثم نضع القيمة "1" في الفراغ بسبب أننا نريد أن يغير الإكسل اللون فقط في حالة إستحقاق الفاتورة ليوم واحد فأكثر كما يلي:
بعد ذلك نضغط على الأيقونة "Format" ، ونختار من قائمة الألوان "Color" اللون الأحمر ، بعد ذلك نضغط على "OK" ، ستعود لنافذة الشرط مرة أخرى ولكن مع تغير لون النص إلى الأحمر ، نضغط على "OK" أيضاً للموافقة.
نلاحظ أن لون الخط في الخلية رقم (L6) قد تحول إلى اللون الأحمر وذلك بسبب أن القيمة "20" هي أكبر من الواحد كما يلي:
نستطيع إعطاء شرط تحريري أخر في حالة عدم إستحقاق الفاتورة أو عندما تكون القيمة هي "0" ، ولعمل ذلك نحدد نفس الخلية (L6) ونضغط على "Conditional Formatting" مرة أخرى ، ثم نضغط على "New Rule" ونختار الخيار الثاني أيضاً وهو "Format only cells that contain" ، تحت تفاصيل الشرط نختار في البداية "Cell Value" ثم نختار من القائمة المنسدلة على يمينها "equal to" ونضع القيمة "0" في الفراغ وذلك لأننها نريد عمل الشرط في حالة عدم الإستحقاق وهي في مثالنا تكون مساوية للصفر. نضغط بعد ذلك على "format" ونختار على سبيل المثال اللون الأخضر ثم نضغط على "OK" لتظهر النافذة كما يلي:
لإختبار الشرط التحريري نغير تاريخ التقرير إلى "28 فبراير 2012" لتتغير قيمة الخلية رقم (L6) إلى الصفر ويتغير أيضاً لونها إلى اللون الأخضر كما يلي:
نعيد تاريخ التقرير إلى "31 مارس 2012" وننتقل إلى الخطوة الأخيرة وهو عمل خطوط عريضة للجدول وإضافة سطر للمجموع في نهاية الجدول ، ولتوضيح طريقة إدخال الدالة (IF) في الخلية رقم (L6) مع عمل التحرير الشرطي للخلية الرجاء الإطلاع على المشهد التالي:
ستظهر لدينا في النهاية النتيجة التالية بعد عمل حدود عريضة للجدول وإضافة سطر للمجموع:
طريقة إعداد جدول أعمار الديون
بعد أن حسبنا عدد الأيام بعد تاريخ الإستحقاق أصبح بإمكاننا أن نقوم بإعداد جدول أعمار الديون ، حيث سنستخدم هذه البيانات في الدالة الشرطية داخل خلايا الفترات التحليلية لأعمار الديون. ولكن قبل البدء بتعبئة جدول أعمار الديون سنضيف فاتورة افتراضية ثانية على حساب الأستاذ المساعد بحيث تكون غير مستحقة ، فنحدد الخلية رقم (A7) ونكتب التسلسل رقم "2" ، ثم نكتب رقم الفاتورة "1002" ، تاريخ الفاتورة هو "28 فبراير 2012" ، تاريخ التسليم هو "10 مارس 2012" ، فترة السداد هي نفسها "30" يوماً ، تاريخ الإستحقاق هو ثلاثون يوماً بعد تاريخ التسليم ولذلك نكتب الدالة:
=D7+E7
مبلغ الفاتورة هو "55,000" ونفترض أنها غير مسدد حتى تاريخ التقرير وهو 31 مارس 2012 ، المبلغ المتبقي هو الفرق بين مبلغ الفاتورة و المبلغ المسدد لذلك نكتب الدالة:
=G7-H7
لحساب عدد الأيام بعد الإستحقاق نستخدم نفس الدالة الشرطية التي استخدمناها في الفاتورة الأولى ونكتب الدالة:
=IF(L4<F7,0,IF(K7=0,0,L4-F7))
ستظهر لنا النتيجة صفر "0" ، ونطبق نفس الشرط التحريرية: اللون الأحمر إن كانت النتيجة أكبر من أو مساوية للواحد "1" ، و اللون الأخضر إن كانت النتيجة مساوية للصفر ”0” ، وتكون النتيجة النهائية كما يلي:
بعد ذلك ننتقل إلى الصفحة الثانية من الملف وهي "Sheet2" لإستخدامها في اعداد جدول أعمار الديون ، نكتب في أول خلية اسم الشركة "شركة القوى العاملة المحدودة" ، ونكتب تحتها عنوان التقرير وهو "جدول أعمار الديون". لكتابة عناوين الأعمدة ننتقل إلى الخلية رقم (A4) ونكتب فيها "التسلسل" ، ثم نكتب على يسارها "اسم الزبون" ثم "مجموع الديون" ، بعد ذلك نضع الفترات التحليلية بالأيام وهي "غير مستحقة"، و "1 إلى 30"، و "31 إلى 60"، و "61 إلى 90"، و "91 إلى 180"، و "181 إلى 360"، و"361 فأكثر" كما يلي:
لوضع التاريخ نحدد الخلية رقم (J2) ونكتب التاريخ "31-03-2012" ونغير طبيعة الخلية إلى "Short Date". بعد ذلك نقوم بإختيار الخلية رقم (A5) ونكتب التسلسل رقم واحد "1" ثم نكتب اسم الزبون "شركة المقاولات العامة" ، مجموع الديون هو إجمالي الفترات التحليلية لذلك نحدد الخلية رقم (C5) ونكتب الدالة التالية:
=SUM(D5:J5)
أو نضغط على "AutoSum" ونحدد الخلايا من (D5) إلى (J5) كما يلي:
الخطوة التالية هي أن نكتب الدالة الشرطية في كل من الفترات التحليلية على حدة ، ولفعل ذلك سنستخدم الدالة (SUMIFS) والتي تكون قاعدتها كما يلي:
=SUMIFS(sum_range, criteria_range1, criteria1, …)
القيمة sum_range : المجال من الخلايا المراد جمع قيمها مع بعضها البعض في حال تحقق الشرط ، وفي مثالنا هنا هي عامود "المبلغ المتبقي".
القيمة criteria_range1 : هي المجال من الخلايا التي يبحث فيها الإكسل عن الشرط ، وفي مثالنا هنا هي عامود "عدد الأيام بعد الإستحقاق".
القيمة criteria1 : هي الشرط ، وفي مثالنا هي بداية الفترة التحليلية.
النقاط في آخر قاعدة الدالة تعني امكانية إضافة مجال جديد على نفس الدالة وفي مثالنا سنحتاجها عند وجود مجالين شرطيين في الفترات التحليلية (1 إلى 30) و (31 إلى 60) و (61 إلى 90) و (91 إلى 180) و (181 إلى 360) ، أما الفترات التحليلية (غير مستحقة) و (361 فأكثر) فسنستخدم مجال شرطي واحد.
لتطبيق الدالة (SUMIFS) على الفترات التحليلية سنقوم بما يلي:
نحدد الخلية رقم (D5) تحت عامود "غير مستحقة" ونكتب الدالة:
=SUMIFS(Sheet1!K6:K7, Sheet1!L6:L7,"=0")
لاحظ أننا اخترنا مجال شرطي واحد وهو مساوي للصفر ، كما اخترنا الفاتورتين لمجال الجمع ، وتاريخ الإستحقاق للفاتورتين أيضاً.
نحدد الخلية رقم (E5) تحت عامود "1 إلى 30" ونكتب الدالة:
لاحظ أننا اخترنا مجالين شرطيين لأننا نريد تحديد الفترة بين يوم واحد وثلاثين يوماً واخترنا نفس المرجع في المجال الثاني للبحث عن الشرط وهو عدد الأيام بعد الإستحقاق.
نحدد الخلية رقم (F5) تحت عامود "31 إلى 60" ونكتب الدالة:
لاحظ أننا اخترنا مجالين شرطيين لأننا نريد تحديد الفترة بين مائة وواحد وثمانين يوماً وثلاثمائة وستين يوماً.
نحدد الخلية رقم (J5) تحت عامود "361 فأكثر" ونكتب الدالة:
=SUMIFS(Sheet1!K6:K7, Sheet1!L6:L7,">=361")
لاحظ أننا اخترنا مجال شرطي واحد لأننا نريد من الإكسل أن يجمع مبالغ الفواتير التي استحقاقها أكبر من أو مساوي لـ 361 يوماً فقط.
بعد تطبيق هذه الدوال والمعادلات الرياضية سينتج لدينا الشكل التالي:
نلاحظ أن الفاتورة الأولى رقم "1001" كان عدد أيامها بعد الإستحقاق هو 20 يوماً ، لذلك ظهرت تحت عامود "1 إلى 30" ، بينما الفاتورة الثانية رقم "1002" ظهرت تحت عامود "غير مستحقة" وذلك بسبب أن تاريخ استحقاقها هو 9 أبريل 2012 أي أنها غير مستحقة حتى تاريخ التقرير (عدد الأيام بعد الإستحقاق هو صفر).
لتوضيح طريقة عمل دالة SUMIFS أكثر فأرجو الإطلاع على المشهد التالي:
حل بديل لمشكلة حساب تاريخ الإستحقاق
رأينا فيما سبق كيف حللنا مشكلة حساب تاريخ الإستحقاق الفعلي من خلال استخدام برنامج الإكسل في حال كان البرنامج المحاسبي المستخدم في الشركة غير مرن كفاية لحساب هذا التاريخ بالشكل الصحيح. في الواقع بإمكان المحاسب أن يتغلب على قصور البرنامج المحاسبي بطريقة أخرى وهي كما يلي:
تسجيل الفاتورة بتاريخ التسليم الفعلي إلى الزبون.
الإعتراف بإيرادات مستحقة القبض في نهاية الشهر الذي تم تقديم الخدمة فيه ، هذا الحل ممكن إن كانت الشركة تتعامل مع عدد قليل من الزبائن ، ولكن يصبح من الصعب استخدامه في حالة وجود عدد كبير من الزبائن ، وفي النهاية تبقى طريقة الحل بيد المحاسب في تقدير أفضل الطريقتين لشركته.
موضوع: كيفية إعداد جدول أعمار الديون بالفيديو والصور ,,, شرح رائع جدا 
