Студияда абонементтер мен келулерді Excel-де қалай есепке алуға болады

Фитнес-клубта, би немесе йога студиясында әкімші күніне бірнеше рет «менің абонементімде қанша сабақ қалды?» деген сұраққа жауап береді. Дәптерде жазылған белгілер, телефондағы фотолар, жеке хабарламалар: бәрі бір жүйеге түспесе, не клиент ренжиді, не студия ақша жоғалтады.

Абонемент есебі қарапайым логикаға құрылған: клиент белгілі сабақ санына немесе мерзімге абонемент сатып алады, әр келу журналға жазылады, ал қалдық есептеледі. Мұны Excel жақсы істейді, ал артынша ұзарту уақыты келген клиенттерді көресіз.

Мақалада кестенің құрылымын, қалдық пен аяқталу мерзімінің формулаларын, санмен мысалды және жиі қателерді көрсетеміз. Соңында дайын үлгіні сипаттаймыз.

Кестенің құрылымы

Төрт тізім жеткілікті.

1. Абонемент түрлері: атауы, сабақ саны, жарамдылық мерзімі (күн), баға.
2. Клиенттер: аты-жөні, байланысы.
3. Сатылған абонементтер: клиент, түрі, сатып алу күні, аяқталу күні.
4. Келулер журналы: күні, клиент.

Байланыстыратын мән — клиент аты немесе нөмірі. Бірдей аты бар екі клиент болмауы үшін жеке нөмір беріңіз.

Аяқталу күнін есептеу

Аяқталу күні сатып алу күні мен жарамдылық мерзімінен шығады:

=C2+VLOOKUP(B2;Түрлер!A:D;3;0)+F2

Мұнда C2 — сатып алу күні, B2 — түрі, 3-баған — мерзім (күн), F2 — қосымша күндер. Қосымша күндер бағаны абонементті мұздатқанда ыңғайлы: клиент ауырып қалса, сол күн санын қосасыз да, мерзім өздігінен жылжиды.

Қалдық сабақтарды есептеу

Абонементтің қолданыс кезеңіндегі келулерді санайсыз:

=COUNTIFS(Келулер!A:A;A2;Келулер!B:B;">="&C2;Келулер!B:B;"<="&D2)

Мұнда A2 — клиент, C2 — басталу, D2 — аяқталу күні. Қалдық:

=E2-G2

Мұнда E2 — абонементтегі сабақ саны, G2 — келу саны. Шектеусіз абонементтер үшін сабақ қалдығын есептемейді, тек мерзімін бақылайсыз.

Күй және ұзартуды еске салу

Күй бағаны мынадай болсын:

=IF(TODAY()>D2;"Аяқталды";IF(OR(H2<=0;D2-TODAY()<=7);"Жақында аяқталады";"Белсенді"))

Мұнда 7 — алдын ала ескерту күні, H2 — қалған сабақ. Сүзгі арқылы «Жақында аяқталады» күйіндегі клиенттерді алып, әкімші оларға хабарласады. Ұзарту көбіне осы кезде жүреді, ал клиент мерзімнен кейін ұмытып кетсе, студия табыстан айырылады.

Санмен мысал

«8 сабақ / 30 күн» абонементі, баға 24 000 ₸. Клиент 1 қазанда сатып алды.

• Аяқталу күні: 1 қазан + 30 күн = 31 қазан
• Келулер: 3, 5, 8, 10, 12 қазан, барлығы 5 келу
• Қалдық: 8 − 5 = 3 сабақ
• Бір сабақтың орташа құны келу бойынша: 24 000 / 8 = 3 000 ₸

Егер клиент 31 қазанға дейін қалған үш сабаққа келмесе, абонемент «Аяқталды» күйіне өтеді. Бұл деректер студияға да пайдалы: көптеген клиент сабақтарды толық пайдаланбаса, абонемент мерзімі тым қысқа болуы мүмкін.

Жиі кездесетін қателер

• Келуді кейін есте қалғанымен жазу. Журналды сол күні толтырыңыз.
• Мұздатуды абонемент күнін қолмен өзгертіп жасау. Бұл ізсіз кетеді, ал қосымша күндер бағаны тарихты сақтайды.
• Қолданыс кезеңінен тыс келуді санау.
• Шектеусіз абонементке қалдық сабақты есептеу.
• Түсімді айлар бойынша есепке алмау. Сатып алу күні бойынша жинап, айлық кіріс көрінсін.

Есептен не көруге болады

Кесте дайын болған соң, қарапайым сұрақтарға жауап аласыз. Ай сайын қанша абонемент сатылды және қандай түсім түсті? Қай топқа немесе қай күні келу көп? Клиенттердің қанша пайызы абонементін ұзартты? Соңғысын есептеу үшін абонементін аяқтағаннан кейін 30 күн ішінде жаңасын сатып алған клиенттерді санап, барлық аяқталғандарға бөлесіз.

Келу бойынша орташа деректер де пайдалы: егер абонементтің жартысы толық пайдаланылмаса, сабақ санын азайтып, бағаны түзетуге болады. Ал клиенттер сабақтың бәріне үлгерсе, мерзімі тым қысқа болмауы тиіс.

Дайын абонементтер мен келулер үлгісі

Бәрін қолмен құрмау үшін дайын ақылы үлгіні қолдануға болады.

• «Түрлер»: абонемент түрлері. Сабақтар, мерзімі, баға.
• «Клиенттер»: байланыстар, барлық келулер және келусіз күндер.
• «Абонементтер»: 1500 сатылымға дейін. Аяқталуы, пайдаланылғаны, қалдық және күйі.
• «Келулер»: жарамды абонементті тексерумен 8000 жазбаға дейін.
• «Жиынтық»: түсім, белсенді абонементтер, топтар мен айлар бойынша келу.

Клиенттің қолданыс кезеңіндегі келулері пайдаланылған сабақ деп саналады. Шектеусіз абонементтер үшін тек мерзімі бақыланады, мұздату үшін «Қосымша күндер» бағанына күн қосылады. «Жақында аяқталады» күйі ерекшеленеді және жиынтықта ескеріледі. Бұл бухгалтерлік бағдарлама емес, қарапайым формулалардағы Excel кестесі. Файл қазақ тілінде, макростарсыз, Excel 2007 және одан жаңа нұсқаларда және LibreOffice-те жұмыс істейді.

Жиі қойылатын сұрақтар

Бір реттік келуді қалай есептеу керек?

Бір сабақтық абонемент түрін жасап, оны басқалар секілді сатылымға қосыңыз. Сонда түсім мен келу бір жүйеде көрінеді.

Клиент кеш келсе, абонементті ұзартуға бола ма?

Бұл студияның ережесіне байланысты. Ұзартқан күндерді қосымша күн ретінде жазсаңыз, шешім ізсіз қалмайды.

Деректерді қанша уақыт сақтау керек?

Кемінде бір жыл. Тарих клиенттің қаншалықты жиі келетінін және қай абонемент түрі сұранысқа ие екенін көрсетеді.

Отбасылық абонементті қалай жүргізу керек?

Әр қатысушыны жеке клиент етіп жазып, абонементті бөлек сатылым ретінде енгізіңіз. Бұл келуді дәл есептеуге мүмкіндік береді.