شيت المخزون في إكسل وحساب متوسط التكلفة خطوة بخطوة
ورقة المخزون تحتاج أربعة أعمدة للبدء: الصنف، والكمية، وتكلفة الوحدة، وقيمة المخزون — حيث القيمة `= الكمية × متوسط التكلفة`. والجزء الذي يهمّ فعلاً هو أن تكلفة الوحدة يجب أن تكون متوسطاً مرجحاً، لا سعر آخر عملية شراء. إن كانت أسعارك تتغير، فالتسعير بآخر سعر شراء سيرفع أرباحك المسجَّل ويخفي خسارة حتى تُباع البضاعة.
لماذا المخزون أخطر بند في حسابات المشروع الصغير
المخزون هو الحساب الوحيد الذي يكون في الوقت نفسه أصلاً، وتكلفة، وخروج نقدي — وهو أسوأ ما يُتابع. وهذه هي الحالات الثلاثة:
- تطلب أكثر مما تحتاج، فيكون رأس المال نائماً على رف بدل أن يبيع.
- ينفد صنف سريع الحركة فتتوقف مبيعات كانت مضمونة لأسبوع.
- يختفي شيء، وبلا تكلفة مسجَّلة لا تستطيع حتى قياس الخسارة.
الأعمدة الأساسية
| العمود | مثال | ملاحظة |
|---|---|---|
| الصنف | مثال قميص قطن | ملاحظة مفتاح أساسي — لا يتكرر |
| الكمية الحالية | مثال 34 | ملاحظة الرصيد بعد كل حركة |
| متوسط التكلفة | مثال 212.50 | ملاحظة محسوب لا مكتوب يدوياً |
| قيمة المخزون | مثال 7,225 | ملاحظة الكمية × متوسط التكلفة |
| حد إعادة الطلب | مثال 20 | ملاحظة معدل البيع اليومي × مدة التوريد |
| آخر حركة | مثال 2026-02-11 | ملاحظة صنف فوق صفر بلا حركة منذ 90 يوماً بضاعة ميتة |
آخر عمودين هما ما يحوّل الورقة من سجل إلى أداة. حد إعادة الطلب يجعلك تخطط بدل أن تتفاعل. وآخر حركة يكشف البضاعة التي توقفت — وهي نقد أنفقته بالفعل وصار يدفع إيجاراً على مساحة كان يمكن أن تحمل صنفاً سريع الحركة.
متوسط التكلفة المرجح: لماذا لا سعر آخر شراء
طريقتان للاحتساب. تشتري 10 قمصان بـ 200 لكل واحد، ثم 10 أخرى بـ 220.
| الطريقة | تكلفة الـ 20 قطعة | ملاحظة |
|---|---|---|
| تكلفة آخر شراء | تكلفة الـ 20 قطعة 20 × 220 = 4,400 | ملاحظة تبني التكلفة على الفاتورة الأخيرة فقط |
| المتوسط المرجح | تكلفة الـ 20 قطعة (10×200 + 10×220) ÷ 20 = 210 | ملاحظة تمثل التكلفة الفعلية للقطع المشتراة |
الآن بِع القطع العشرين كلها بسعر 300، أي إيراد 6,000:
| البند | آخر شراء | متوسط مرجح |
|---|---|---|
| تكلفة البضاعة المباعة | آخر شراء 4,400 | متوسط مرجح 4,200 |
| مجمل الربح | آخر شراء 1,600 | متوسط مرجح 1,800 |
| الهامش المُعلن | آخر شراء 26.7% | متوسط مرجح 30.0% |
واتجاه الخطأ ثابت: آخر تكلفة ترفع الربح حين ترتفع الأسعار، وترفعه أيضاً حين تنخفض، لأنها في الحالتين تفترض أن آخر فاتورة تمثّل ما بِع فعلاً — وهو غير صحيح. الخطأ يميل دائماً في الاتجاه الذي يؤخر اكتشاف خسارتك.
حساب متوسط التكلفة في إكسل
عند تسجيل عملية شراء، يجب إعادة حساب المتوسط لكامل المخزون من ذلك الصنف. خليتان تفعلان ذلك. عند وصول كمية `qty` بسعر `price`، والمخزون الحالي `stock` بمتوسط `avg`:
F2: =IF((stock+qty)=0, 0, (stock*avg + qty*price) / (stock+qty)) املأ هذه الخلية الواحدة في عمود الشراء مرة واحدة، فتُعاد حسابات المتوسط تلقائياً مع كل عملية شراء مُدخلة. الآلية كلها صيغة واحدة.
الأرقام على الورق
| الحدث | الكمية قبل | المتوسط قبل | شراء | سعر الشراء | المتوسط بعد |
|---|---|---|---|---|---|
| رصيد افتتاحي | الكمية قبل 10 | المتوسط قبل 200.00 | شراء — | سعر الشراء — | المتوسط بعد 200.00 |
| شراء ١ | الكمية قبل 20 | المتوسط قبل 200.00 | شراء 10 | سعر الشراء 220 | المتوسط بعد 210.00 |
| بيع ١٢ قطعة | الكمية قبل 18 | المتوسط قبل 210.00 | شراء — | سعر الشراء — | المتوسط بعد 210.00 |
| شراء ٢ | الكمية قبل 18 | المتوسط قبل 210.00 | شراء 20 | سعر الشراء 190 | المتوسط بعد 200.00 |
| بيع ١٥ قطعة | الكمية قبل 23 | المتوسط قبل 200.00 | شراء — | سعر الشراء — | المتوسط بعد 200.00 |
تحقّق من الصف الثالث: `((18 × 210) + (20 × 190)) ÷ 38 = (3,780 + 3,800) ÷ 38 = 199.47`. المتوسط ينخفض لأن البضاعة الجديدة أرخص — وهذا بالضبط ما ينبغي أن يحدث، وهو ما لن يظهر أبداً في جدول تكلفة مكتوبة يدوياً.
اكتشاف الفاقد والتسريب
بعد أن تصبح الكميات وتكاليف الوحدة حيّة، هناك صيغة واحدة تكشف ما لا يبلّغ عنه أحد:
الفرق = الكمية الدفترية − (الكمية الافتتاحية + المشترى − المباع) أي نتيجة غير صفرية على صنف هي إما خطأ تسجيل أو خسارة حقيقية. نفّذها شهرياً على كل صنف. ثم اسأل السؤال الذي يغيّر السلوك فعلاً: ما نسبة هذه الخسارة من قيمة مخزونك؟
- أقل من 0.5% — طبيعي في معظم أنشطة التجزئة.
- بين 0.5% و2% — يستحق التحقيق؛ ابدأ بأعلى أصنافك قيمة.
- أعلى من 2% — مشكلة عمليات لا حظ. غالباً بضاعة تدخل من الباب الخلفي بلا تسجيل.
شيت المخزون جاهز للتحميل
تحميل شيت المخزون وقوالب المحاسبة في إكسلأسئلة شائعة
هل أقوم بتحديث متوسط التكلفة بعد كل عملية بيع؟
لا. بعد الشراء فقط. البيع لا يغيّر متوسط تكلفة ما تبقّى، بل يغيّر مقدار ما خرج من ذلك المتوسط. هذا خطأ شائع ومكلف — إعادة الاحتساب مع كل عملية بيع تجعل التكلفة تنزلق مع كل معاملة.
ماذا أفعل إن كان للسلعة أكثر من سعر شراء؟
هذه بالضبط الحالة التي صُمّم المتوسط المرجح من أجلها. إن لم ترد تعقيد التتبع، فأعِد حساب المتوسط يدوياً عند كل شراء، وسجّل تاريخ آخر إعادة حساب حتى تعرف متى كان صحيحاً آخر مرة.
هل أستخدم متوسط تكلفة مرجح أم FIFO؟
المتوسط المرجح للبساطة وإمكانية الجدول. ونظام الدفع أولاً صادر أولاً لمطابقة التكلفة بطبقات شراء محددة، وهو مطلوب عادة في التصنيع وأقل حاجة إليه في التجارة. وإن لم تكن متأكداً، فالصواب للتاجر الصغير هو المتوسط المرجح.
متى أفعل جرداً فعلياً؟
مرة واحدة على الأقل في السنة، وفحص شهري لأعلى أصنافك قيمة. الجرد الكامل لمحل كبير يستغرق يوماً ويغلق المحل؛ أما فحص شهري لأعلى عشرين صنفاً مرتفع القيمة فيستغرق أقل من ساعة ويلتقط معظم الخسائر الحقيقية، لأن السرقة تتركّز فيما هو ثمين. جرد لا يُقارن بالكمية المسجَّلة ليس جرداً.
ما الفرق بين الفاقد والتسريب؟
شيئان مختلفان. الفاقد هو إجمالي الفجوة بين المخزون المسجَّل والفعلي، ويشمل السرقة والتلف وخطأ العد وتوثيق ناقص. التسريب هو الجزء الناجم تحديداً عن خلل في العملية: باب خلفي غير مؤمّن، تنازل مجاني غير مسجَّل، إيصالات مفقودة. الفاقد هو الرقم، والتسريب هو سبب. الثاني وحده في يدك.