Розробляємо платіжний календар в ms excel

Передбачається, що Новомосковсктель вже знає що таке платіжний календар і навіщо потрібен, в зв'язку з цим далі будуть обговорюватися тільки практичні питання організації платіжного календаря. Крім того, бажано мінімальне володіння термінологією бюджетування (розуміння таких термінів як «бюджет», «лімітування», «ЦФО»). Якщо все вищеперелічене для Вас зрозуміло, пропоную визначитися, а наскільки взагалі правильний похід, який передбачає ведення платіжного календаря в MS Excel? Існують різні прямо-протилежні думки, тому що тут не все так просто. Спробуємо бути об'єктивними. Якщо у Вас є програмний продукт, в якому Ви ведете оперативний або бухгалтерський облік, то швидше за все Вам краще вести платіжний календар саме в цьому продукті, навіть якщо його функціонал не цілком Вас задовольняє. Якщо мова йде про 1С, то, можливо, має сенс доопрацювати її з тим, щоб вести платіжний календар в базі даних. Але, як завжди, є винятки з правил, про які Ви зможете дізнатися в кінці наступного блоку статті.

Плюси і мінуси використання MS Excel в якості платформи для організації платіжного календаря

Перед тим як розповідати про організацію платіжного календаря в MS Excel хочу перерахувати основні недоліки такого підходу.

1) Відсутність контролю посилальної цілісності - MS Excel не контролює користувачів, якщо ті хочуть видалити значення якої-небудь комірки. Можна з цим побічно боротися установкою захисту осередків, або макросами, але проблема може проявиться в будь-який момент;

2) Проблема з розрахованих на багато користувачів режимом роботи - організувати якусь подобу багато режиму в MS Excel можна, якщо мова йде про двох-трьох користувачів, але якщо користувачів буде більше краще задуматися про базу даних;

3) Розмежування доступу і конфіденційність - все паролі в MS Excel дуже легко ламаються, при бажанні навіть далекий від ІТ-індустрії людина зможе легко знайти кілька таких способів в відкритих джерелах. Таким чином, до файлу доступ повинен надаватися тільки перевіреним людям;

4) Повторне введення даних - на відміну від 1С готових обробок обміну між MS Excel і клієнтом банку не існує, зробити звичайно ж можна, але дешевше буде доопрацювати використовувану Вами базу даних з тим, що б вести платіжний календар в ній. Втім на невеликих обсягах операцій використання обробок значної економії трудовитрат не дає;

5) Обмеження на розмір бази - великий масив даних MS Excel не потягне, хоча для середніх розмірів фірми можливостей MS Excel вистачить.

З усього вищесказаного можна зробити наступний висновок - використання MS Excel для організації платіжного календаря допустимо при одночасному дотриманні наступних умов:

1) Обсяг платіжних операцій в день не перевищує 20-30 документів;

2) З файлом працює не більш трьох довірених осіб;

3) У Вас немає функціоналу платіжного календаря в програмному продукті для бухгалтерського або оперативного обліку.

Сподіваюся зрозуміло, що перераховані вище умови не є догмою, але дозволяють «відчути» допустимі межі.

Тепер кілька слів про нюанси, які можуть переважити недоліки організації платіжного календаря в MS Excell. Справа в тому, що крім власне реєстру платежів та платіжного календаря даний файл може ще виконувати додаткові смачні функції - наприклад складання бюджетів і лімітування платежів відносного бюджетів, а недоліки MS Excel з повторного введення даних цілком можна компенсувати, організувавши двосторонній обмін з платіжної підсистемою 1С. Справа в тому, що якщо Ви збираєтеся щільно працювати з бюджетуванням, то відносна ефективність MS Excel в порівнянні зі спеціалізованих програмними продуктами бюджетування запросто може переважити мінуси використання MS Excel в якості платформи платіжного календаря. Тому, що розглядається в цій статті приклад відразу містить в собі елементи бюджетування, а саме лімітування платежів (без цього не бачу практичного сенсу у використанні MS Excel для організації платіжного календаря). Якщо у Вас є в MS Excel система бюджетів, то налаштувавши досить простими формулами з неї трансляцію лімітів платежів в платіжний календар Ви отримаєте дешевий, простий і ефективний інструмент бюджетування. Тепер, прояснив плюси і мінуси платіжного календаря MS Excel, Ви зможете самостійно вирішити, чи використовувати MS Excel для платіжного календаря чи ні.

Концепція налаштування платіжного календаря в MS Excel

1) Створюємо на окремих аркушах книги MS Excel ключові класифікатори (статті руху грошових коштів і ЦФО, для робочого прикладу ще можуть знадобиться наприклад контрагенти, організації, банківські рахунки / каси);

3) Створюємо лист з бюджетом руху грошових коштів (БРГК);

4) Створюємо лист з реєстром платежів (цей лист і буде основним робочим місцем);

5) Створюємо лист з реєстром збільшення лімітів (для надбюджетних заявок на витрату коштів);

6) Створюємо лист з таблицею даних, яка буде збирати всі необхідні для платіжного календаря дані;

7) Створюємо зведену таблицю «Платіжний календар» - це, власне, і є Ваша мета;

8) Для цілісної системи ще добре створити звіт, який показує виконання бюджету руху грошових коштів, не обов'язково - але рекомендую.

Додам також, що можна додатково налаштувати вивантаження платіжних доручень в 1С, і завантаження з 1С факту про оплати і залишки на рахунках / касах. Крім того, можна налаштувати обмін довідників з 1С, особливо таких великих як контрагенти. Процес творчий - але при надлишку часу і ресурсів головне не захоплюватися процесом відшліфовування, адже MS Excel володіє все-таки масою недоліків, і свою творчу енергію, можливо, краще в такому випадку направити на переклад платіжного календаря в нормальну базу даних.

На кожен класифікатор створюємо окремий лист, в нашому випадку це листи «Класифікатор ЦФО» і «Класифікатор статей ДДС».

З довідником статей ДДС є інший нюанс - у багатьох випадках повна версія класифікатора не потрібна, по повною версією розуміється наявність груп і дохідних статей. Іноді потрібен просто список статей ДДС по виплатах. Для цих цілей заведіть ще додатковий лист «СтатьіДДС_Виплати»

Повна ж версія довідника статей ДДС нехай «живе» на аркуші «Класифікатор статей ДДС». Після заповнення виплатних статей створіть іменований діапазон-список (вкладка «формули» - «Присвоїти ім'я»).

Важливий крок - це прив'язка з ЦФО статей ДДС. Крім того, що це правильний крок з точки зору методології управління підприємством за допомогою бюджетів, це ще значно полегшить введення даних по платежах і дозволить скоротити кількість помилок. Технічно дана можливість організовується досить просто - на окремому аркуші створюється таблиця, колонки якої іменуються найменуваннями ЦФО, а в рядках вводяться відповідні ЦФО статті ДДС.

Після цього раніше вже показаним способом (через присвоєння імені діапазону) на кожне ЦФО створюється свій іменований список.

Крім перерахованих классіфікторов необходио створити також список періодів планування, який буде служити роздільником бюджетних періодів. Зокрема цей список стане в нагоді при введенні бюджету руху грошових коштів, при збільшенні лімітів і при додаванні заявок на витрачання грошових коштів.

Управління бюджетом (лімітами)

Для того, що б контролювати поточні платежі на відповідність бюджету знадобляться дві таблиці - «Бюджет ДДС» і «Збільшення лімітів». Створимо однойменні листи і розмістимо на них ці таблиці.

Для того, що б ЦФО і періоди планування не потрібно було б кожен раз удруковувати, можна додати функціонал вибору значень зі списку. Покажемо це на прикладі колонки «ЦФО» - виділяєте свідомо великий діапазон ЦФО (для навчального прикладу досить і 20 рядків, для робочого прикладу потрібно передбачити як мінімум тисяч десять рядків). Після того як виділіть діапазон на вкладці «Дані» в блоці кнопок «Робота з даними» натисніть на кнопку «Перевірка даних», в діалоговому вікні в полі «Тип даних» виберіть «Список», а в поле «Джерело» впишіть знак одно і найменування пойменованого списку, в нашому прикладі це буде виглядати так: = ЦФО.

Після зазначених маніпуляцій поле ЦФО буде заповняться вибором зі списку, що, безсумнівно, дуже зручно.

Аналогічним чином слід налаштувати вибір в колонці «Період планування»

Вказавши з поле джерело не назву списку, а функцію ДВССИЛ (), що повертає список по найменуванню, ми зможемо виводити для вибору тільки ті статті, які відносяться до даного ЦФО!

Реєстр платежів являє собою таблицю, в якій слід реєструвати плановані, здійснені і анульовані платежі. При наявності обмеження платежів Ви зможете побачити залишки по бюджету, а також налаштувати фільтр який надасть Вам інструмент затвердження платежів на конкретну дату.

Вам слід найбільш відповідально підійти до дизайну (зручності) саме цієї таблиці, тому що з нею треба працювати кожен день. Для того, що б хоча б трохи продемонструвати можливості MS Excel в частині зручності інтерфейсу в приклад включений макрос, що допомагає спростити вибір дат, нижче функціонал макросу буде показаний у дії, але сам текст коду Ви зможете подивитися в прикладі, в статті докладно на цьому зупинятися не має сенсу - тим більше, що дати легко можна вводити вручну.

Обговоримо технічні аспекти. Макросами, як уже говорилося вище, можна спростити введення дат, реалізувавши можливість їх вибору з календаря подвійним клацанням по осередку.

ЦФО і Періоди планування налаштуйте вибором з однойменних діапазонів, вибір статей ДДС налаштуйте з підлеглих списків точно так же, як це було показано в розділі, присвяченому БДДС. Напевно найбільш складним моментом є розрахунок залишків по бюджету після заявки. Для цього слід скористатися відмінною функцією СУММЕСЛІМН ().

Платіжний календар має на увазі графік надходжень грошових коштів, виплат, і залишків за періодами (зазвичай днях). В MS Excel для графічного представлення платіжного календаря ідеально підходить інструмент «зведена таблиця», нижче наведений приклад того, що ми повинні отримати «на виході».

Однак для того, що б зведена таблиця могла бути сформована, слід підготувати таблицю з даними. У нашому випадку для цих цілей знадобиться чотиривимірна таблиця даних і ресурсом «Сума». Вимірювання наступні:

Дата - по цій даті будуть групуватися колонки

Вид - можливо чотири значення - початковий залишок, прихід, витрата і кінцевий залишок.

Контрагент - сюди будуть виводиться контрагенти по запланованим витратам.

Пропонований приклад передбачає, що початковий вхідний залишок і плановані парафії користувачем будуть вноситься вручну (поля виділені жовтим). Решта поля будуть розрахунковими. Перед початком конструювання таблиці визначитеся на який тимчасовий горизонт планування грошових потоків в розрізі днів Ви будете спиратися. Мною було вибрано 9 календарних днів. Перші рядки, відповідні горизонту планування, повинні бути фіксованими. На кожен день я створюю три рядки, з початковим залишком, приходом і кінцевим залишком, далі такі ж рядки на наступну дату і так аж до закінчення горизонту планування.

Видаткову частину розмістіть під фіксованими блоками. Ці дані будуть динамічно змінюватися в залежності від того, що Ви внесли в таблицю «Реєстр платежів».

Тепер трохи про формулах. Перші два рядки, що відносяться до першого дня планування, заповнюються вручну. Це початковий залишок і планований прихід. У робочому варіанті входить залишок доцільно налаштувати так, що б він розраховувався виходячи з реальних касових залишків, з урахуванням грошей в дорозі (підійде таблиця, періодично довантажувати з тієї ж 1С). Поле прихід містить прогнозовану суму грошових коштів, надходження яких Ви очікуєте на першу дату. По приходу те ж саме стосується і інших фіксованих рядків - питання про те, що ставити в прогнозований прихід вирішується для кожного підприємства окремо. Специфіка роздрібних мереж дозволяє скласти календарні профілі прогнозованих надходжень, у проектних організацій графік надходжень складається індивідуально по кожному акту / етапу і т.д.

Всі фіксовані дати горизонту планування, починаючи з другої, повинні містити формули розрахунку початкового і кінцевого залишку. Тут так само доведеться скористатися функцією СУММЕСЛІМН ().

Принцип формули розрахунку кінцевого залишку простий - беремо початковий залишок розраховується дати (дорівнює кінцевому залишку попереднього дня), додаємо прогнозований прихід і забираємо все суми витрат, які функцією СУММЕСЛІМН () відібрані за поточною датою в блоці містить видаткову частину.

Як користуватися розробленим платіжним календарем.

Ви, працюючи з реєстром платежів, позначаєте плановані дати оплат. У таблиці даних платіжного календаря вносите планований залишок грошових коштів та прогнозовані надходження. Після цього переходите на лист «ПК» і клацаєте «Оновити» на зведеній таблиці «Платіжний календар». Після цього Ви в наочному вигляді отримуєте платіжний календар.

Знову оновлюєте зведену таблицю з платіжним календарем і вона переміщує оплату 600 тис.руб. на 18.10 і перераховує початкові і кінцеві залишки грошових коштів!

Все просто і ефективно!