تحلیل سودآوری مشتریان با اکسل؛ آموزش محاسبه سود هر مشتری + دانلود قالب آماده

نویسنده: انتشار:
تحلیل سودآوری مشتریان با اکسل

کدام مشتری واقعا برای شما سود می سازد؟ در این راهنمای عملی یاد می گیرید چگونه فروش، مرجوعی، بهای تمام شده و هزینه خدمت رسانی را در اکسل به سود قابل انتساب هر مشتری تبدیل کنید، نتیجه را با یک مثال عددی بسنجید و از یک قالب آموزشی برای شروع استفاده کنید.

فهرست بزرگ ترین خریداران همیشه فهرست سودآورترین مشتریان نیست. ممکن است یک مشتری سفارش های بیشتری ثبت کند، اما به علت تخفیف، ارسال های متعدد و پشتیبانی پرهزینه، سود کمتری نسبت به مشتری کوچک تر داشته باشد. پاسخ به این پرسش با حدس یا صرفا بررسی رقم فروش به دست نمی آید؛ باید درآمد و هزینه را در یک بازه مشخص، برای هر مشتری جداگانه کنار هم بگذاریم.

در این مقاله تحلیل سودآوری مشتریان با اکسل را از تعریف شاخص ها تا تنظیم داده ها، فرمول نویسی، کنترل خطا و تفسیر مدیریتی پیش می بریم. مثال ها مربوط به یک توزیع کننده فرضی تجهیزات آشپزخانه صنعتی هستند و تمام نام ها و ارقام، داده های فرضی آموزشی محسوب می شوند. مدل ارائه شده برای تحلیل مدیریتی است و جایگزین دفاتر حسابداری یا الزامات گزارشگری مالی رسمی نیست.

تحلیل سودآوری مشتری چیست و چه مسئله ای را حل می کند؟

در این راهنما خروجی اصلی، «سود قابل انتساب مشتری در دوره» است: درآمد خالص مشتری منهای بهای تمام شده و هزینه هایی که با مبنایی روشن به همان مشتری و همان دوره تعلق دارند. سربار عمومی شرکت، از جمله هزینه هایی که نمی توان با مبنایی معنادار به یک مشتری مشخص نسبت داد، در شاخص پایه توزیع نمی شود؛ در نتیجه این عدد را نباید با سود خالص یا سود عملیاتی صورت های مالی شرکت یکی دانست.

درآمد، سود ناخالص، حاشیه مشارکت و سود قابل انتساب چه تفاوتی دارند؟

شاخص معنا در مدل این مقاله
فروش ناخالص مجموع مبلغ فروش شناسایی شده پیش از تخفیف و تعدیل مرجوعی؛ مبنای مالیات باید یکسان باشد.
درآمد خالص فروش ناخالص منهای تخفیف و مبلغ مرجوعی یا سایر تعدیلات درآمدی که در مدل ثبت شده اند.
سود ناخالص درآمد خالص منهای بهای تمام شده خالص اقلام فروش رفته.
حاشیه مشارکت (مبلغ) سود ناخالص منهای هزینه هایی که در افق تحلیل با حجم فعالیت تغییر می کنند. اینجا «حاشیه» مبلغ است، نه درصد.
سود قابل انتساب حاشیه مشارکت منهای هزینه های ثابت اختصاصی هر مشتری؛ در قالب حاضر، هزینه جذب جداگانه گزارش می شود.
حاشیه سود قابل انتساب (درصد) سود قابل انتساب تقسیم بر درآمد خالص مثبت مشتری، ضربدر ۱۰۰.

دو نکته در خواندن این جدول مهم است. نخست اینکه هزینه ای مانند حقوق مدیر حساب اختصاصی می تواند به مشتری منتسب باشد، اما با تعداد سفارش های او تغییر نکند. دوم اینکه هزینه قابل انتساب لزوما هزینه قابل حذف نیست؛ ممکن است ظرفیت تیم پشتیبانی پس از قطع همکاری با یک مشتری همچنان هزینه ایجاد کند. برای آشنایی بیشتر با انواع حاشیه و تفاوت صورت و مخرج آن ها، مقاله مرتبط زیر را ببینید.

آیا سود یک دوره همان ارزش طول عمر مشتری است؟

خیر. این مدل به دوره ای که انتخاب کرده اید نگاه می کند؛ ارزش طول عمر مشتری یا CLV به سودهای مورد انتظار آینده و ارزش فعلی آن ها مربوط است. برای برآورد CLV باید مفروضاتی درباره تکرار خرید، ماندگاری رابطه، سود مورد انتظار و نرخ تنزیل داشته باشید. بنابراین یک دوره زیان، به تنهایی به معنی بی ارزش بودن رابطه در آینده نیست. از طرف دیگر، پیش بینی سود آینده نیز جای محاسبه زیان تحقق یافته امروز را نمی گیرد. این تمایز در پژوهش Gupta، Lehmann و Stuart درباره ارزش گذاری مشتریان مطرح شده است.

چرا مشتری پردرآمد همیشه سودآورتر نیست؟ یک مثال عددی

فرض کنید یک توزیع کننده تجهیزات آشپزخانه صنعتی با دو مشتری تجاری کار می کند. مشتری الف در طول سال سفارش های متعدد و کوچک تری داده است و مشتری ب سفارش های کمتری داشته است. برای مقایسه منصفانه، دوره، واحد پول و سیاست ثبت درآمد و هزینه در هر دو مورد یکسان است. تمام اعداد جدول زیر فرضی و بر حسب میلیون تومان هستند.

شرح مشتری الف مشتری ب
تعداد سفارش فاکتورشده ۱۸ ۶
فروش ناخالص ۱۲۰۰ ۷۰۰
تخفیف / مرجوعی ۱۵۰ / ۹۰ ۲۰ / ۱۰
درآمد خالص ۹۶۰ ۶۷۰
بهای تمام شده ارسالی / برگشت ارزش موجودی ۶۲۰ / ۴۰ ۴۰۰ / ۶
بهای تمام شده خالص ۵۸۰ ۳۹۴
هزینه ارسال ۸۵ ۲۵
پردازش سفارش ۴۵ ۱۵
پردازش مرجوعی ۳۰ ۵
پشتیبانی ۷۰ ۱۵
سود قابل انتساب ۱۵۰ ۲۱۶
حاشیه سود قابل انتساب ۱۵.۶٪ ۳۲.۲٪

برای مشتری الف، درآمد خالص برابر است با ۱۲۰۰ منهای ۱۵۰ منهای ۹۰، یعنی ۹۶۰. بهای تمام شده خالص برابر ۶۲۰ منهای ۴۰، یعنی ۵۸۰ است. پس سود ناخالص ۳۸۰ می شود؛ با کسر ۲۳۰ واحد هزینه خدمت رسانی، ۱۵۰ واحد سود قابل انتساب باقی می ماند. برای مشتری ب نیز ۶۷۰ واحد درآمد خالص منهای ۳۹۴ واحد بهای تمام شده و ۶۰ واحد هزینه خدمت رسانی، برابر با ۲۱۶ واحد سود است.

در این مثال، درآمد خالص مشتری الف حدود ۴۳٪ بیشتر است، ولی سود قابل انتساب او حدود ۳۱٪ کمتر از مشتری ب است. این اعداد علت را به تنهایی اثبات نمی کنند؛ برای تصمیم عملی باید ببینیم کدام بخش از تخفیف، مرجوعی یا هزینه خدمات ناشی از الگوی سفارش، سیاست فروش یا شرایط قرارداد است.

پیش از محاسبه سود مشتری در اکسل، چه داده هایی لازم داریم؟

مدلی که فقط به یک خروجی از نرم افزار فروش متکی باشد، معمولا تصویری ناقص می دهد. ابتدا منبع هر داده، شناسه ارتباطی، تاریخ مبنا و واحد پول را مشخص کنید. در این مقاله مبالغ نمونه بر حسب میلیون تومان اند؛ اگر داده واقعی را بر حسب تومان ثبت می کنید، همه جدول ها و نرخ ها باید با همان واحد باشند.

۱. مشتریان و سفارش ها

برای هر مشتری یک CustomerID یکتا و برای هر سفارش یک OrderID یکتا داشته باشید. نام مشتری به تنهایی کلید مطمئنی نیست؛ دو نام شبیه یا دو شیوه نوشتن یک نام، تجمیع را به هم می زنند. سفارش باید دارای تاریخ ثبت، وضعیت، تاریخ شناسایی فروش در مدل، فروش ناخالص، تخفیف، بهای تمام شده و هزینه ارسال باشد.

۲. مرجوعی ها و هزینه خدمت رسانی

هر مرجوعی باید شناسه مستقل، سفارش مبدأ، تاریخ، مبلغ بازپرداخت و وضعیت قابل بازیافت بودن کالا داشته باشد. هزینه های پشتیبانی، رسیدگی به سفارش و سایر فعالیت ها نیز باید به مشتری و تاریخ مربوط متصل شوند. اگر هزینه حمل در خود سفارش ثبت شده است، همان مبلغ را دوباره در جدول هزینه های دستی نیاورید.

۳. پوشش زمانی و کیفیت نمونه داده

تحلیل فقط زمانی معنا دارد که فروش و هزینه های دوره مورد بررسی تا حد قابل قبول کامل باشند. ممکن است داده فاکتور تا پایان سال موجود باشد، اما هزینه پشتیبانی فقط تا آبان ثبت شده باشد؛ در این حالت سود محاسبه شده بیش از مقدار واقعی دوره دیده می شود. هنگام آزمایش اولیه، چند مشتری با الگوی خرید، مرجوعی و خدمات متفاوت انتخاب کنید؛ سپس قبل از نتیجه گیری درباره کل پایگاه مشتریان، کامل بودن جامعه داده را بررسی کنید.

پیشنهاد مطالعه:
جامعه و نمونه

هزینه فعالیت ها را چگونه برآورد کنیم؟

اگر هزینه ماهانه یک تیم ۱۰۰ میلیون تومان و ظرفیت عملی آن ۱۰۰ هزار دقیقه باشد، هزینه ظرفیت هر دقیقه هزار تومان است. برای درخواست ۱۵ دقیقه ای، هزینه فعالیت ۱۵ هزار تومان برآورد می شود. این مثال آموزشی را با نرخ ثابت ۲.۵ میلیون تومان به ازای هر تماس در سناریوی فرضی توزیع کننده یکی نگیرید؛ نرخ های واقعی باید از داده همان کسب و کار به دست آیند. توضیح مبنای ظرفیت و زمان در پژوهش Kaplan و Anderson آمده است.

در رابطه با شرکت هایی که خدمات پس از فروش را با کمک توزیع کننده یا شریک تجاری ارائه می کنند، لازم است روشن باشد چه بخشی از هزینه در داخل شرکت و چه بخشی بر عهده شریک است؛ در غیر این صورت ممکن است هزینه یک خدمت در دو مجموعه گزارش شود یا از تحلیل جا بماند.

مرجوعی و تاریخ ثبت درآمد؛ دو عامل تعیین کننده نتیجه

مرجوعی فقط کم شدن مبلغ فروش نیست. در مثال ما، مبلغ بازپرداخت از درآمد کسر می شود، ارزش کالای برگشتی که قابل بازیافت است از بهای تمام شده خالص کاسته می شود و هزینه عملیات مرجوعی جداگانه ثبت می شود. اگر ارزش کالای مرجوعی آسیب دیده صفر باشد، برگشت کامل بهای تمام شده صحیح نیست. برای کالایی که بخشی از ارزش خود را حفظ کرده است نیز تنها مبلغ بازیافت پذیرفته شده وارد مدل می شود.

در فایل آموزشی، برای روشن ماندن گزارش بسته شده دوره های گذشته، آثار مرجوعی به تاریخ ثبت رویداد مرجوعی نسبت داده می شوند. مثلا فروش ۱۰۰ واحدی در ژانویه و مرجوعی کامل همان کالا در فوریه می تواند درآمد خالص فوریه را منفی کند. این یک سیاست عملیاتی در قالب آموزشی است؛ در صورت های مالی رسمی، شناسایی درآمد و بدهی بازپرداخت ممکن است با توجه به قرارداد و استانداردهای گزارشگری متفاوت باشد. تاریخ فاکتور نیز در این فایل یک جانشین عملیاتی برای تاریخ فروش است و نباید در تمام کسب و کارها، بدون بررسی انتقال کنترل کالا یا ارائه خدمت، تاریخ قطعی تحقق درآمد فرض شود.

مراقب دوباره شماری باشید: اگر مبلغ مرجوعی در جدول Returns کسر شده، همان مبلغ را دوباره به صورت «هزینه مرجوعی» وارد Costs نکنید. اما هزینه واقعی حمل برگشت یا بازرسی که در نرخ پردازش مرجوعی پوشش داده نشده است، می تواند با تعریف مستقل محاسبه شود.

آموزش تحلیل سودآوری مشتریان با اکسل، گام به گام

قالب آموزشی این مقاله ۱۰ شیت دارد. Readme راهنمای فارسی کاربر است؛ Settings بازه زمانی و وضعیت پوشش داده را نگه می دارد؛ شیت های Customers، Orders، Returns، ActivityRates و Costs داده های پایه را ثبت می کنند؛ Profitability خروجی هر مشتری را می سازد و DataChecks و Dashboard برای کنترل و مشاهده نتایج به کار می روند. جدول های اصلی با نام های مشخصی ساخته شده اند تا فرمول ها خوانا و قابل پیگیری باشند.

گام اول: بازه زمانی و واحد پول را تنظیم کنید

در Settings!B5 تاریخ شروع، در Settings!B6 تاریخ پایان و در Settings!B8 واحد پول را ببینید. نام های PeriodStart و PeriodEnd در فرمول ها به همین تاریخ ها اشاره می کنند. تاریخ های ورودی باید تاریخ واقعی Excel باشند، نه متن نمایشی شبیه تاریخ. در نسخه فعلی، تاریخ ها به صورت میلادی و بدون جزء ساعت ثبت می شوند. اگر از تاریخ شمسی استفاده می کنید، ابتدا آن را با یک روش کنترل شده به تاریخ معتبر Excel تبدیل کنید و قالب نمایش را از مقدار ذخیره شده جدا نگه دارید.

در Settings وضعیت تکمیل داده های مشتریان، سفارش ها، مرجوعی ها، هزینه ها و نرخ فعالیت ها نیز مشخص می شود. وقتی داده های یک منبع کامل نیست، به خروجی داشبورد به چشم نتیجه قطعی نگاه نکنید.

گام دوم: جدول ها و شناسه ها را درست نگه دارید

در اکسل می توانید محدوده ورودی را با Ctrl + T به جدول ساختاریافته تبدیل کنید. نام جدول های فایل عبارت اند از tblCustomers، tblOrders، tblReturns، tblRates، tblActivities و tblProfit. یک مشتری می تواند چند سفارش داشته باشد و یک سفارش می تواند چند مرجوعی داشته باشد؛ بنابراین جداول متعدد را فقط بر اساس نام مشتری کنار هم نچسبانید. اگر یک هزینه ماهانه را کنار هر سفارش تکرار کنید، همان هزینه به تعداد سفارش ها دوباره محاسبه می شود.

شیت / جدول ورودی های کلیدی نقش در محاسبه
Customers / tblCustomers CustomerID, CustomerName مرجع یکتای مشتری
Orders / tblOrders OrderID, CustomerID, InvoiceDate, GrossAmount, Discount, COGS, ShippingCost فروش، بهای تمام شده، حمل و پردازش سفارش
Returns / tblReturns ReturnID, OrderID, ReturnDate, ReturnAmount, COGSReturned, Condition تعدیل درآمد، بازیافت ارزش کالا و پردازش مرجوعی
ActivityRates / tblRates RateID, ActivityCode, ValidFrom, ValidTo, RatePerUnit نرخ فعالیت در دوره اعتبار مشخص
Costs / tblActivities CostID, CustomerID, CostDate, ActivityCode, DriverQty, ManualAmount, RateID هزینه های دستی یا مبتنی بر محرک فعالیت
Profitability / tblProfit خروجی محاسباتی؛ بدون ورود دستی سود تجمیع مستقل همه رویدادها برای هر مشتری

در قالب موجود، شناسه نرخ هر رویداد و نرخ اعمال شده برای آن ثبت می شود. اگر نرخ پردازش سفارش در میانه سال تغییر کرد، برای رویدادهای جدید از نسخه نرخ معتبر استفاده کنید و نرخ های تاریخی را بی دلیل بازنویسی نکنید. ارقام مثال دو مشتری با نرخ های ثابت فرضی محاسبه شده اند؛ در فایل واقعی، هزینه پردازش از جمع هزینه تک تک رویدادها به دست می آید، نه لزوما تعداد کل سفارش ها ضربدر آخرین نرخ.

گام سوم: فروش و درآمد خالص هر مشتری را محاسبه کنید

تابع SUMIFS مبلغ فروش را فقط برای سفارش هایی جمع می کند که هر سه شرط را داشته باشند: متعلق به همین مشتری باشند، وضعیت آن ها «تکمیل شده» باشد و تاریخ فاکتورشان در بازه انتخابی قرار بگیرد. کد زیر برای ستون «فروش ناخالص» در جدول نتیجه سودآوری نوشته شده است؛ در آن، GrossAmount یعنی مبلغ فروش ناخالص، CustomerID یعنی شناسه مشتری و InvoiceDate یعنی تاریخ فاکتور:

فرمول های این مقاله برای ستون های محاسباتی جدول های Excel نوشته شده اند. با دکمه «کپی فرمول»، نسخه یک خطی بدون شکست سطر در حافظه کپی می شود و می توانید آن را مستقیما در سلول مناسب قرار دهید.

فرمول ۱ از ۴ | جمع فروش ناخالص مشتری در دوره
=SUMIFS(
tblOrders[GrossAmount],
tblOrders[CustomerID],[@CustomerID],
tblOrders[Status],"Fulfilled",
tblOrders[InvoiceDate],">="&PeriodStart,
tblOrders[InvoiceDate],"<="&PeriodEnd
)
برای کپی فرمول یک خطی و آماده جای گذاری در اکسل، دکمه بالا را انتخاب کنید.

در همان جدول سفارش ها، مجموع تخفیف ها را با همین شرط ها از ستون تخفیف (tblOrders[Discount]) جمع بزنید. مبلغ بازپرداخت مرجوعی را جداگانه از ستون «مبلغ مرجوعی» جدول مرجوعی ها (tblReturns[ReturnAmount]) بر اساس شناسه مشتری و تاریخ رویداد جمع کنید. حاصل این سه جزء، درآمد خالص دوره است: فروش ناخالص − تخفیف − بازپرداخت مرجوعی. شناسه مشتری در جدول مرجوعی ها از سفارش اصلی بازیابی می شود؛ بنابراین لازم نیست برای هر مرجوعی نام مشتری را دوباره وارد کنید.

توجه به نوع فرمول: فرمول بالا برای وارد کردن در ستون محاسباتی جدول سودآوری نوشته شده است، زیرا [@CustomerID] به شناسه همان ردیف اشاره می کند. اگر فرمول را در یک سلول عادی خارج از جدول قرار می دهید، به جای آن آدرس سلول شناسه مشتری را وارد کنید. علامت های & و < در کد HTML هنگام نمایش صفحه به علامت معمول تبدیل می شوند.

گام چهارم: بهای تمام شده و سود ناخالص را محاسبه کنید

بهای تمام شده کالاهای فروخته شده در دوره را از ستون بهای تمام شده سفارش در جدول سفارش ها جمع بزنید. سپس «ارزش کالای برگشتی که دوباره به موجودی یا دارایی قابل بازیافت تبدیل شده است» را کسر کنید. نام فنی این مبلغ در فایل tblReturns[COGSReversal] است؛ یعنی ستون «برگشت بهای تمام شده» از جدول «مرجوعی ها»، نه مبلغی که به مشتری بازپرداخت شده است. فرمول زیر در ردیف هر مرجوعی، ارزش قابل برگشت همان کالا را مشخص می کند:

راهنمای خواندن ستون ها در فرمول:

Condition: وضعیت کالای برگشتی؛ COGSReturned: بهای تمام شده اقلام مرجوعی؛ RecoveryValue: ارزش بازیافتی قابل شناسایی؛ COGSReversal: مبلغی که از بهای تمام شده خالص کسر می شود.

Resellable = قابل فروش مجدد؛ Salvage = دارای ارزش بازیافتی؛ Damaged = آسیب دیده و فاقد ارزش قابل بازیافت در فرض آموزشی این مدل.

فرمول ۲ از ۴ | محاسبه ارزش قابل برگشت کالای مرجوعی
=IF(
[@Condition]="Resellable",[@COGSReturned],
IF(
[@Condition]="Salvage",
MIN([@RecoveryValue],[@COGSReturned]),
0
)
)
برای کپی فرمول یک خطی و آماده جای گذاری در اکسل، دکمه بالا را انتخاب کنید.

این فرمول فقط باید پس از کنترل معتبر بودن وضعیت کالا و مقادیر ورودی استفاده شود؛ مقدار ناشناخته در ستون Condition نباید بدون هشدار به منزله «کالای آسیب دیده» پذیرفته شود. به همین دلیل شیت DataChecks بخشی از فرآیند محاسبه است، نه یک مرحله اختیاری. پس از محاسبه بهای تمام شده خالص، سود ناخالص = درآمد خالص − بهای تمام شده خالص خواهد بود.

گام پنجم: هزینه خدمت رسانی و سود قابل انتساب را به دست آورید

هزینه ارسال را فقط از ستون ShippingCost سفارش های مربوط به دوره بخوانید. برای پردازش سفارش و مرجوعی، هزینه ثبت شده برای هر رویداد را به تفکیک تاریخ و مشتری جمع بزنید. هزینه های پشتیبانی و سایر فعالیت ها از tblActivities به مدل وارد می شوند. در سناریوی فرضی، نرخ پردازش هر سفارش ۲.۵ و هر مرجوعی ۵ میلیون تومان است؛ در کسب و کار واقعی ممکن است نرخ های متفاوتی در طول دوره وجود داشته باشد.

اگر بخشی از حمل، پشتیبانی یا نصب کالا را یک شریک تجاری انجام می دهد، در ثبت هزینه ها مشخص کنید چه مبلغی را خودتان می پردازید و چه مبلغی طبق قرارداد بر عهده شریک است. هزینه یک خدمت نباید هم در سفارش و هم در هزینه فعالیت ها تکرار شود.

در جدول نتیجه نهایی سودآوری مشتریان (tblProfit)، ستون «سود قابل انتساب» از حاشیه مشارکت منهای هزینه ثابت اختصاصی همان مشتری محاسبه می شود. نام فنی این دو ستون در اکسل به ترتیب ContributionMargin و CustomerFixed است:

فرمول ۳ از ۴ | محاسبه سود قابل انتساب هر مشتری
=[@ContributionMargin]-[@CustomerFixed]
برای کپی فرمول یک خطی و آماده جای گذاری در اکسل، دکمه بالا را انتخاب کنید.

در همین فایل، حاشیه مشارکت از سود ناخالص پس از کسر هزینه ارسال، پردازش سفارش، پردازش مرجوعی و سایر هزینه های متغیر محاسبه می شود. هزینه جذب در ستون مستقلی با نام AcquisitionCost نگه داشته شده و خروجی ProfitAfterAcq را می سازد؛ این دو سود را در گزارش با هم جایگزین نکنید.

گام ششم: حاشیه سود و گروه سودآوری را بسازید

حاشیه سود هر مشتری، نسبت سود قابل انتساب به درآمد خالص همان مشتری است. فقط وقتی درآمد خالص مثبت است این نسبت را به درصد نشان می دهیم؛ در دوره ای با درآمد صفر یا منفی، مقدار درصدی را خالی می گذاریم و خود مبلغ سود یا زیان را جداگانه گزارش می کنیم. در جدول نتیجه، NetRevenue نام ستون درآمد خالص، AttributableProfit نام ستون سود قابل انتساب و Margin نام ستون حاشیه سود است. فرمول ستون حاشیه سود:

فرمول ۴ از ۴ | محاسبه حاشیه سود در صورت مثبت بودن درآمد
=IF(
[@NetRevenue]>0,
[@AttributableProfit]/[@NetRevenue],
""
)
برای کپی فرمول یک خطی و آماده جای گذاری در اکسل، دکمه بالا را انتخاب کنید.

این شرط از نمایش یک درصد به ظاهر معتبر برای دوره ای با درآمد خالص منفی جلوگیری می کند. طبقه بندی سودآور، سربه سر و زیان ده نیز به آستانه BreakEvenBand در Settings وابسته است. مثلا با آستانه ۵ میلیون تومان، سود بین منفی ۵ و مثبت ۵، شامل دو مرز، در گروه سربه سر قرار می گیرد. این آستانه یک قاعده تنظیمی در مدل است و به معنی استاندارد همگانی برای همه صنایع نیست.

گام هفتم: نتایج را کنترل و در داشبورد بخوانید

پیش از تفسیر، به شیت DataChecks بروید. خطاهای شناسه، مرجوعی، هزینه، نرخ و مغایرت جمع ها باید بررسی شوند. سپس در Dashboard، بازه انتخاب شده را ببینید و در سلول B6 مقدار ALL یا شناسه یک مشتری را وارد کنید. شاخص های درآمد، سود و نمودار مقایسه، با انتخاب گزارش هماهنگ می شوند. حاشیه سود کل، در صورت مثبت بودن مجموع درآمد خالص، برابر است با مجموع سود تقسیم بر مجموع درآمد خالص؛ نه میانگین ساده درصدهای مشتریان.

ظرفیت اولیه فایل آموزشی محدود و قابل توسعه است: ۵۰ ردیف مشتری، ۴۵ ردیف سفارش، ۲۵ ردیف مرجوعی و ۲۵ ردیف هزینه در جدول های آماده دارد. هنگام افزایش داده ها باید محدوده جدول ها، فرمول ها و ناحیه گزارش مشتریان نیز مطابق راهنمای فایل توسعه داده شود؛ صرف چسباندن داده در پایین صفحه، بدون کنترل خروجی، کافی نیست.

داشبورد چه چیزی را نشان می دهد و چه چیزی را نشان نمی دهد؟

داشبورد باید به دو پرسش پاسخ دهد: در دوره انتخابی، سود قابل انتساب مجموعه مشتریان چقدر است و تفاوت درآمد و سود هر مشتری از کجا می آید؟ در قالب فعلی، شاخص های درآمد خالص، سود قابل انتساب، بهای تمام شده خالص، هزینه های متغیر دیگر، هزینه جذب، مشتریان فعال، مشتریان زیان ده و حاشیه سود تجمیعی کنار نمودار مقایسه مشتریان آمده اند. وضعیت کیفیت داده نیز نمایش داده می شود.

اگر می خواهید نمودار پراکندگی درآمد در برابر سود یا منحنی سود تجمعی مشتریان را برای تحلیل پیشرفته اضافه کنید، ابتدا داده ها را بر اساس سود مرتب کنید و سپس مجموع تجمعی را بسازید. «منحنی نهنگ» نام متداول نمودار سود تجمعی پس از مرتب سازی مشتریان بر اساس سود است؛ شکل و سهم مشتریان سودآور در آن از یک مجموعه داده به مجموعه دیگر فرق می کند. در نسخه فعلی فایل، این منحنی و Slicer پیاده سازی نشده اند و نباید آن ها را به عنوان امکانات آماده قالب معرفی کرد.

همچنین توجه کنید که سهم بالای یک مشتری از فروش یا سود، به تنهایی رابطه علت و معلولی را روشن نمی کند. برای فهمیدن نقش واقعی قیمت، تعداد سفارش، مرجوعی یا هزینه پشتیبانی، داده های دوره های مختلف و مشتریان قابل مقایسه را بررسی کنید.

داشبورد را فقط برای گزارش وضعیت گذشته به کار نبرید. وقتی فروش کاهش می یابد یا هزینه خدمت رسانی تغییر می کند، مقایسه سود و درآمد مشتریان کمک می کند اثر تغییر ترکیب قیمت، تخفیف، توزیع و خدمات را پیش از تصمیم گیری بررسی کنید. در دوره رکود، این مقایسه به ویژه اهمیت دارد.

نتایج سودآوری را چگونه به تصمیم مدیریتی تبدیل کنیم؟

پس از شناسایی یک مشتری کم سود، نخست بپرسید کدام هزینه واقعا با تغییر رفتار سفارش یا شرایط قرارداد کاهش می یابد. اگر سفارش های کوچک باعث ارسال مکرر شده اند، می توان حداقل مبلغ سفارش، تجمیع تحویل یا بازنگری شرایط حمل را بررسی کرد. اگر تخفیف بالا مسئله اصلی است، باید نقش آن در حفظ حجم خرید و حاشیه سود را هم زمان سنجید. برای مشتریان با مرجوعی بالا، علت مرجوعی ممکن است اشتباه در مشخصات محصول، بسته بندی نامناسب یا خطای ثبت سفارش باشد؛ بدون شناخت علت، تغییر سیاست مرجوعی صرفا مشکل را به مشتری منتقل می کند.

در دوره رکود، ممکن است حفظ حجم فروش و حفظ حاشیه سود با هم تعارض پیدا کنند. تحلیل سودآوری مشتری کمک می کند اثر ترکیب قیمت، تخفیف، توزیع و خدمات را در سطح مشتری بسنجید؛ اما درباره کاهش یا افزایش تخفیف، باید محدودیت تقاضا و واکنش بازار نیز بررسی شود.

اگر خدمت رسانی، توزیع یا فروش با مشارکت شرکت دیگری انجام می شود، نحوه تقسیم وظایف و هزینه ها را نیز در قرارداد بررسی کنید. سودآوری ظاهری یک حساب تجاری ممکن است حاصل ثبت نشدن بخشی از هزینه های شریک، یا برعکس، انتقال دو باره هزینه مشترک به یک مشتری باشد. تصمیم درباره توسعه یا بازنگری همکاری را باید در کنار تحلیل مالی، با شناخت منافع و مسئولیت های طرفین گرفت.

در نهایت، زیان یک دوره را حکم قطعی برای قطع همکاری ندانید. یک مشتری ممکن است در دوره جاری به علت مرجوعی فروش دوره قبل یا هزینه جذب اولیه زیان ده دیده شود. پیش از تصمیم، سود چند دوره، قابلیت تغییر هزینه، ظرفیت بلااستفاده و ارزش مورد انتظار ادامه رابطه را کنار هم بررسی کنید. این همان نقطه ای است که داده های مالی می توانند به تصمیم های سنجیده تر در بازاریابی و مدیریت مشتری کمک کنند.

خطاهای رایج در محاسبه سود مشتریان با اکسل

  1. تجمیع با VLOOKUP: این تابع یک مقدار منطبق را برمی گرداند؛ برای جمع چند سفارش یک مشتری، از SUMIFS یا یک روش تجمیع مناسب استفاده کنید.
  2. ادغام بی قاعده جدول ها: اتصال ردیف به ردیف سفارش و هزینه می تواند یک هزینه ثابت را به تعداد سفارش ها تکثیر کند.
  3. ثبت تکراری مرجوعی: مبلغ بازپرداخت را یک بار از درآمد کم کنید؛ هزینه واقعی پردازش مرجوعی قلم دیگری است.
  4. نادیده گرفتن ارزش بازیافتی کالا: مرجوعی سالم، آسیب دیده و دارای ارزش بازیافتی لزوما اثر یکسانی بر بهای تمام شده ندارند.
  5. تغییر بی ردپای نرخ ها: بازنویسی نرخ تاریخی می تواند سود دوره های گذشته را تغییر دهد؛ نسخه نرخ رویداد را حفظ کنید.
  6. مخلوط کردن بازه ها: فروش کامل یک سال با هزینه های ناقص همان سال، سود قابل اعتماد تولید نمی کند.
  7. تقسیم سود بر درآمد صفر یا منفی: خالی کردن نتیجه درصد در چنین وضعیتی از نمایش نسبت گمراه کننده جلوگیری می کند؛ مبلغ سود همچنان باید نمایش داده شود.
  8. فرض کردن حذف تمام هزینه های منتسب: هزینه منابع اختصاص یافته به مشتری لزوما پس از قطع همکاری صرفه جویی نمی شود.
  9. وارد کردن تاریخ به صورت متن: داده ای که فقط ظاهر تاریخ دارد ممکن است در فیلتر و مقایسه زمانی درست عمل نکند.
  10. یکسان ندانستن واحدها: نرخ فعالیت بر حسب تومان و فروش بر حسب میلیون تومان، نتیجه را به شدت مخدوش می کند.

دانلود قالب اکسل تحلیل سودآوری مشتریان

قالب آموزشی این مقاله در قالب فایل XLSX و بدون ماکرو تهیه شده و دارای راهنمای فارسی، داده های فرضی، جدول های ثبت سفارش و مرجوعی، نرخ فعالیت ها، محاسبه سود هر مشتری، کنترل کیفیت و داشبورد مدیریتی است. نمونه های مشتریان الف، ب و یک مشتری تست بین دوره ای در فایل قرار دارند. فرمول های پایه با هدف سازگاری با Excel 2019 و نسخه های جدیدتر طراحی شده اند.

قالب آماده تحلیل سودآوری مشتریان

فایل اکسل آموزشی شامل داده های فرضی، فرمول های محاسبه سود هر مشتری، کنترل کیفیت داده ها و داشبورد مدیریتی است.

فرمت XLSX | بدون نیاز به نصب افزونه یا فعال سازی ماکرو

برای شروع، ابتدا شیت Readme را بخوانید، دوره و واحد پول را در Settings مشخص کنید، داده های ورودی را به جدول های مربوط انتقال دهید و پیش از اعتماد به نمودار، وضعیت DataChecks را بررسی کنید. قالب آموزشی برای جایگزینی مستقیم یک سیستم مالی سازمانی طراحی نشده است و برای مقیاس های بزرگ باید توسعه و کنترل شود.

یادآوری: فایل نمونه برای آموزش و آزمایش مدل تهیه شده است. پیش از تحلیل داده های واقعی، نسخه Excel، روش ثبت مرجوعی و صحت نرخ ها را با رویه مالی مجموعه خود تطبیق دهید.

پرسش های متداول

آیا سود هر مشتری همان حاشیه سود اوست؟

خیر. سود یک مبلغ است؛ حاشیه سود نسبت آن مبلغ به درآمد خالص مثبت مشتری است و معمولا به درصد بیان می شود. دو مشتری می توانند حاشیه یکسان اما مبلغ سود کاملا متفاوتی داشته باشند.

اگر مشتری در این ماه خرید نکند اما هزینه پشتیبانی داشته باشد، چه می شود؟

اگر مشتری در فهرست Customers وجود داشته باشد و هزینه او با تاریخ معتبر ثبت شده باشد، هزینه در سود همان دوره لحاظ می شود؛ درآمد می تواند صفر و مبلغ سود منفی باشد. حاشیه سود درصدی در چنین وضعیتی در این مدل خالی نمایش داده می شود.

چرا مرجوعی فروش ماه قبل می تواند سود ماه جاری را منفی کند؟

در سیاست آموزشی این فایل، مرجوعی در تاریخ رویداد خودش ثبت می شود. بنابراین اگر در ماه جاری فروش جدیدی ندارید اما مبلغ فروش قبلی را برگردانده اید، درآمد خالص دوره ممکن است منفی شود. این سیاست را نباید بدون تطبیق با ضوابط حسابداری، به گزارشگری مالی رسمی تعمیم داد.

آیا هزینه جذب مشتری در سود قابل انتساب کم شده است؟

در خروجی پایه خیر؛ هزینه جذب در ستون مستقل AcquisitionCost و سود پس از جذب در ProfitAfterAcq گزارش می شود. در مقایسه مشتریان جدید و قدیمی باید مشخص باشد کدام شاخص را مقایسه می کنید.

آیا قالب با Excel 2019 سازگار است؟

فرمول های اصلی بدون تکیه بر XLOOKUP یا توابع آرایه پویای اختصاصی نسخه های جدیدتر طراحی شده اند. با این حال، پیش از استفاده عملی و انتشار عمومی، عملکرد نهایی فایل، نمودارها و افزودن رکوردها را در نسخه دسکتاپ Excel خود بررسی کنید.

آیا باید همه سربار شرکت را بین مشتریان تقسیم کنیم؟

الزاما نه. برای مدل فعلی، سود قابل انتساب بر هزینه های مستقیم و هزینه های مبتنی بر مصرف منابع تمرکز دارد. اگر هدف شما قیمت گذاری بلندمدت یا محاسبه بهای تمام شده کامل است، می توانید مدل سربار جداگانه بسازید؛ اما مبنای تخصیص و تفاوت آن با هزینه واقعا قابل حذف را روشن نگه دارید.

منابع و مآخذ انگلیسی

برای تعاریف و بخش های تخصصی، از منابع اصلی غیرفارسی زیر استفاده شده است. پنج باکس «پیشنهاد مطالعه» در متن، لینک های داخلی سایت برای مطالعه مرتبط هستند و به عنوان منبع علمی محاسبات معرفی نشده اند.

  • Kaplan, R. S., & Anderson, S. R. (2004). Time-driven activity-based costing. Harvard Business Review, 82(11), 131–138. Original article
  • Gupta, S., Lehmann, D. R., & Stuart, J. A. (2004). Valuing customers. Journal of Marketing Research, 41(1), 7–18. Publisher / academic record
  • Microsoft. (n.d.). SUMIFS function. Microsoft Support. Official documentation
  • Microsoft. (n.d.). Using structured references with Excel tables. Microsoft Support. Official documentation
  • Microsoft. (n.d.). XLOOKUP function: version availability. Microsoft Support. Official documentation

دسته‌بندی‌ها

آموزش اکسل پرسش و پاسخ های بازاریابی

برچسب‌ها

اکسل تحلیل سودآوری مشتریان تحلیل مشتریان حاشیه سود حسابداری مدیریت سودآوری مشتری محاسبه سود مشتری هزینه خدمت رسانی به مشتری

اشتراک این مطلب

0 پاسخ

دیدگاه خود را ثبت کنید

تمایل دارید در گفتگوها شرکت کنید؟
در گفتگو ها شرکت کنید.

دیدگاه خود را ثبت کنید

سؤال، تجربه یا دیدگاه خود را درباره این مطلب بنویسید.

لطفاً دیدگاهی مرتبط و روشن بنویسید. درج لینک یا آدرس وب در دیدگاه مجاز نیست.

انواع بازاریابی؛ راهنمای طبقه بندی، مقایسه و انتخاب روش مناسب...انواع بازاریابیارزش بازاریابیارزش بازاریابی
💥 دوره های تخصصی بازاریابی و فروش به همراه فایل های اکسل و پاورپوینت های کاربردی✨👈 آموزش بازاریابی و فروش