إنشاء جدول بيانات لدعم القرارات

يقوم الدرس على بناء نموذج جدول بيانات على برنامج الجداول الخضراء لشركة طيران تهدف إلى تقليص تكاليفها التشغيلية أو زيادة أرباحها اليومية إلى أدنى أو أقصى حد ممكن عبر أداة سولفر. يتكون النموذج من ثلاثة أقسام رئيسية: قسم الثوابت الذي يحوي خصائص الطائرات وأسعار التذاكر، وقسم العمليات الحسابية والنتائج الذي يحسب المسافات والحجوزات ومسارات الطائرات وتكاليفها، وقسم قائمة الدخل الذي يجمع إيرادات الركاب والشحنات ليطرح منها التكاليف التشغيلية. مثال سريع: إذا كانت شركة طيران تشغل أربع طائرات مختلفة على سبع وجهات، فإن أداة سولفر ستوزع عدد كل طائرة على كل وجهة بحيث تغطي طلبات المسافرين والشحنات دون تجاوز عدد الطائرات المتوفرة، وتحقق أقل تكلفة ممكنة أو أعلى ربح إجمالي.

الفكرة الأساسية

بناء نموذج جدول بيانات متكامل لشركة طيران على برنامج الحاسب، بحيث يربط بين خصائص الطائرات وأنواعها وأسعار التذاكر ورسوم الشحنات وحجوزات المسافرين، لتستخدم أداة سولفر في توزيع مسارات الطائرات على الوجهات بغرض الوصول إلى أقل تكلفة تشغيلية ممكنة أو أعلى ربح إجمالي ممكن مع الالتزام بالقيود.

الشرح

ينقسم العمل على جدول البيانات إلى ثلاثة أقسام أساسية:

• قسم الثوابت: تضع فيه بيانات الطائرات الأربع (بوينغ 717، بومباردييه سي آر جيه 700، إمبراير إي 170، إيرباص إي 220) من حيث عدد الركاب، وسعة شحن البضائع المحسوبة بضرب الطول في العرض في الارتفاع ($الطول \times العرض \times الارتفاع$)، والتكلفة التشغيلية لكل راكب وكل شحنة لكل كيلومتر، وأسطول قيد الخدمة، بالإضافة إلى جدول الرسوم الذي يحوي متوسط سعر تذكرة السفر وسعر الشحنة لكل متر مكعب لكل وجهة من الوجهات السبع (جدة، المدينة المنورة، الدمام، رفحاء، ينبع، أبها، نجران).
• قسم العمليات الحسابية والنتائج: يخصص لعرض مسافات السفر من مطار الرياض المحوري إلى الوجهات السبع بالكيلومتر، وحجوزات المسافرين اليومية، وشحنات البضائع اليومية بالمتر المكعب، وتنظيم مسارات الطائرات في الخلايا المتغيرة (من الخلية F22 إلى I28) التي توزع أعداد الطائرات على الوجهات. وتحسب الإجماليات عبر صيغ مثل إجمالي القدرة الاستيعابية من حيث عدد الركاب للوجهة الأولى بضرب عدد الطائرات في سعة كل نوع ومجموعها عبر الصيغة = (F22*\C\6) + (G22*\C\7) + (H22*\C\8) + (I22*\C\$9)$ مع استخدام المراجع المطلقة ($) للثوابت. كما تحسب التكاليف التشغيلية لكل وجهة بتجميع تكاليف ركاب الرحلة وتكاليف شحن البضائع وفق معادلة المسافة مضروبة في الحجوزات والتكلفة التشغيلية لكل راكب أو شحنة.
• قسم قائمة الدخل: يربط إيرادات الركاب بضرب عدد التذاكر المحجوزة في متوسط سعر التذكرة، وإيرادات الشحنات بضرب شحنات البضائع اليومية في متوسط رسوم الشحن الجوي لكل وجهة، ليحسب إجمالي الإيرادات مطروحاً منه إجمالي التكاليف التشغيلية للوصول إلى إجمالي الربح اليومي.
• تهيئة أداة سولفر وتفعيلها: تعين الخلية التي تريد تقليصها إلى أدنى حد (مثل إجمالي التكلفة N29) أو زيادتها إلى أقصى حد (مثل إجمالي الربح C37)، مع تحديد الخلايا المتغيرة لمسارات الطائرات (F22:I28)، وتطبيق القيود مثل ألا تكون مسارات الطائرات سالبة، وألا يتجاوز إجمالي الطائرات في الخدمة العدد المتوفر لكل نوع في الأسطول (F29:I29 <= G6:G9)، وأن يغطي إجمالي القدرة الاستيعابية حجوزات المسافرين وشحنات البضائع لكل وجهة.

مثال محلول خطوة بخطوة

1. لإنشاء قسم الثوابت، افتح ورقة عمل جديدة، ادمج الخلايا B1:G1 واكتب عنوان «شركة الطيران»، ثم أدخل في الخلايا B5:G9 عناوين وأنواع الطائرات الأربع وقدرتها الاستيعابية من الركاب وسعة شحن البضائع والتكاليف التشغيلية وأسطول قيد الخدمة، وأدخل في الخلايا B11:D11 جدول الرسوم للوجهات.
2. في قسم العمليات الحسابية والنتائج، أدخل الوجهات في B22:B28، والمسافات والحجوزات والشحنات اليومية في الأعمدة C وD وE، واجعل خلايا تنظيم مسارات الطائرات في F22:I28 تبدأ بإدخال الرقم «1» مبدئياً لكل خلية.
3. اكتب صيغة إجمالي القدرة الاستيعابية للركاب في الخلية J22 عبر المعادلة: = (F22*\C\6) + (G22*\C\7) + (H22*\C\8) + (I22*\C\$9)$، ثم انسخ والصق الصيغة في الخلايا J23 إلى J28.
4. احسب إجمالي التكاليف التشغيلية للوجهة في الخلية N22 بضرب المسافة في حجوزات الركاب في التكلفة التشغيلية للركاب مضافاً إليها ناتج ضرب المسافة في شحنات البضائع اليومية في التكلفة التشغيلية للشحنة، ثم انسخ الصيغة لباقي الوجهات واجمعها في الخلية N29.
5. افتح أداة سولفر من تبويب بيانات، وحدد الخلية الهدف N29 لتحديد الحد الأدنى (Min)، وحدد خلايا التغيير F22:I28، وأضف القيود المطلوبة، واضبط خيار نسبة الصواب والأرقام الصحيحة، ثم شغّل الأداة لتوليد الحل الأمثل لتنظيم المسارات بأقل تكلفة.

أخطاء شائعة

• نسيان استخدام المراجع المطلقة (مثل وضع علامة الدولار \C\$6$) عند كتابة الصيغ التي تستند إلى قسم الثوابت، مما يغير الخلايا عند نسخ الصيغة للأسفل.
• إدخال قيم سالبة في خلايا تنظيم مسارات الطائرات رغم أن عدد الطائرات يجب أن يكون أرقاماً صحيحة غير سالبة.
• تجاوز إجمالي الطائرات المستخدمة لعدد الطائرات المتوفرة في الأسطول لكل نوع دون إضافته كقيود في أداة سولفر.
• عدم تساوي أو تجاوز القدرة الاستيعابية للركاب والشحنات لحجم الطلب اليومي لكل وجهة.

سؤالان للتدريب مع الإجابة

السؤال الأول: ما أفضل طريقة تقيس بها شركة الطيران قدرتها الاستيعابية من حيث عدد الركاب؟ الإجابة: عدد الركاب. السؤال الثاني: ما أفضل طريقة تقيس بها شركة الطيران سعتها المستخدمة لشحن البضائع؟ الإجابة: حجم الشحنة.