Автоматизація та моделювання бізнес-процесів в Excel

Розділ 21 Модель прогнозування грошових потоків. Створення платіжного календаря

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

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

Приклад класичного платіжного календаря

За логікою до складу грошових потоків, як кошти оплати, входять безпосередньо кошти - товар, який має миттєвою ліквідністю, і їх еквіваленти. Як еквіваленти виступають різні активи, отримані як оплата за реалізований товар (послуги). Це, як правило, інший товар (взаємозалік, бартер), векселі та ін Але при надходженні цих еквівалентів сума на банківському рахунку залишається незмінною і ліквідність еквівалентів не є миттєвою. Тому розглядаємо платіжний календар, тільки для прогнозування та фіксації безпосередньо руху готівки.

У складі класичного платіжного календаря (рис. 21.1) можна виділити три складові ДДС:

грошовий потік від основної діяльності;
грошовий потік від інвестиційної діяльності;
грошовий потік від фінансової діяльності
з деталізацією їх у окремі статті.

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

Платіжний календар постає як підсумковий звіт, на підставі якого плануватиметься і контролюватиметься касове життя підприємства. Як приклад можливої автоматизації заповнення платіжного календаря розглянемо операцію поставки товару та отримання коштів за кредитною лінією, розглянуті у попередніх розділах.

Мал. 21.1. Приклад класичного платіжного календаря

Завдання автоматизації формування платіжного календаря

Створення платіжного календаря

Таблиця безпосередньо платіжного календаря розташована в області осередків С6: AI83. Рядки 12:29 календаря виділені для формування постатейно даних грошових коштів, що надійшли, а рядки 32:79 - для списаних.

Перед автоматичним заповненням платіжного календаря в комірку D2 з клавіатури вводиться номер місяця, за який формуватиметься платіжний календар. Проміжні розрахунки для формування платіжного календаря будуть проводитися в стовпцях ВА:ВС.

Формування найменувань статей платіжного календаря

У підпрограму входять дві інші процедури: введення формул вибору найменувань статей надходження (ПрибутковаЧасть) і статей списання коштів (ВитратнаЧасть). Сама ж підпрограма здійснює лише вставку вибраного тексту найменувань статей у календар.

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

Якщо немає необхідності в постійному оновленні найменування статей при формуванні платіжного календаря, то виконання цієї підпрограми можна не проводити, поставивши перед її ім'ям у підпрограмі ЗаповненняПлатіжногоКалендаря (рис. 21.5.), що розглядається далі, знак примітки.

Мал. 21.3. Підпрограма ФормуванняСтатейКалендаря на формування найменування статей надходження коштів

Формування найменування статей надходження коштів

Перш ніж приступити до запису макросу Дохідна Частина, введіть формули в комірку ВА12:

яка спочатку перевіряє - чи відрізняється значення в осередку I10 робочого аркуша ВихДані (надходження грошових коштів) від значення 0. Якщо відрізняється, то проводиться додаткова перевірка - чи введена ознака грошових потоків в осередок Н10 робочого аркуша ВихДані. При виконанні цих умов формула повертає текст найменування операції, введену в комірку С10 робочого листа Вихідні.

І в осередку ВВ12 формула:

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

Мал. 21.4. Макрос ПрибутковаЧастина

Формування найменування статей списання коштів

При створенні макросу Витратна Частина немає необхідності повторювати всі дії, виконані під час запису попереднього. Скопіюйте макрос Дохідна Частина в Редакторі Visual Basic і замінивши ім'я, відредагуйте його, що полягає в заміні посилання на комірку з I10 при введенні формули в діапазон комірок ВА:

на посилання на комірку J10:

Це відносна система посилань і вказує на те, що стовпець I знаходиться 44-м, а стовпець J 43-м зліва по відношенню до стовпця ВА, в комірки якого вводяться формули.

Заповнення платіжного календаря сумами щоденних оборотів коштів

Підпрограма ЗаповненняПлатіжногоКалендаряПоДатам (рис. 21.5.) складається з трьох складових:

підпрограми ФормулиЗаповненняПлатіжногоКалендаря безпосередньо для введення формул, що проводять розрахунок;
циклу For-Next для послідовного введення розрахованих щоденних оборотів ДС за кожною статтею платіжний календар;
очищення вмісту області із запровадженими формулами проміжних розрахунків.

Критеріями під час виборів даних служать:

номер місяця, введений в комірку D2 перед початком обчислень;
номер дня обраного місяця, що вводиться циклом For-Next за допомогою змінної X в комірку ВА1, і при виконанні підпрограми, що змінює значення від 1 до 31;
найменування статті управлінського обліку, сформованих раніше платіжному календарі в діапазоні осередків С12:С79.

Мал.21.5. Підпрограма ЗаповненняПлатіжногоКалендаряПоДатам

Формули розрахунку щоденних оборотів коштів

Для введення формулВизначення щоденних оборотів коштів запишіть макрос Формули Заповнення Платіжного Календаря, попередньо ввівши такі формули. У комірці ВА10 формула:

яка спочатку визначає, чи відповідає номер місяця в даті, що знаходиться в комірці D10 робочого листа Вихідні номеру місяця, введеному в комірку D2 робочого листа ПК. При виконанні цієї умови перевіряється наступна умова - чи номер дня місяця цієї дати відповідає номеру дня місяця, що вводиться в комірку ВА1 робочого листа ПК. При виконанні цих умов формула повертає найменування проведеної операції, введеної в комірку D10 робочого листа Вихідні. В іншому випадку формула повертає значення 0.

Формула в осередку ВВ10:

перевіряє результат обчислення формули в комірці ВА10. Якщо результат не дорівнює нулю, то формула підсумовує значення надходження та списання коштів за цією операцією, що містяться в осередках I10:J10 робочого аркуша Вихідні. Підсумовування значень доходних і видаткових статей пояснюється тим, що у осередках I10:J10 значення одночасно утримуватися що неспроможні. Це має обов'язково враховуватися при формуванні таблиці на аркуші ВихДані, адже інакше такий запис не матиме жодного сенсу.

Формула в осередку ВС12:

підсумовує обороти руху коштів у галузі осередків ВВ10:ВВ5000 за номер дня місяця, введеного в осередок ВА1 та за умови, що текст найменування статті в осередку С12 ідентичний тексту найменування статті в області осередків ВА10:ВА5000.

Запис макросу полягає в послідовному введенні цих формул у діапазони осередків BC12:BC79, ВА10:ВА5000 та ВВ10:ВВ5000.

Мал. 21.6. Підпрограма введення формул визначення оборотів за день за кожною статтею управлінського обліку

Цикл заповненняплатіжного календаря щоденними оборотами руху коштів

Введення дат та сальдо на початок дня

Завданням макросу КалендарДатиСальдо (рис. 21.7.) є введення формул:

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

Як пам'ятаєте, при створенні таблиці на аркуші СуммПотоки було встановлено обмеження вертикального розташування таблиці рядком 200 або, виходячи з робочої області, таблиця охоплює часовий інтервал у 198 днів. Обмеження було з збільшенням швидкості обробки інформації. Причому алгоритм створення цієї таблиці самостійно визначає дату, з якої починається цей часовий інтервал. Як наслідок, якщо початкова дата перебуває у другій половині року, то часовий інтервал перейде і наступного року.

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

Незважаючи на складність, завдання досить проста і визначається однією формулою, за критеріями: заданий часовий діапазон і значення номера місяця. Формула визначення номера року в осередку D3: