Використання Excel в задачах фінансового менеджменту

1. Планування інвестицій, оцінка інвестиційних проектів
2. фінансове планування
3. бюджетування
4. Аналіз даних із зовнішніх систем
5. Підготовка даних для зовнішніх систем
6. калькуляція собівартості
7. Аналіз фінансової звітності (фінансовий аналіз)
8. УПРАВЛІНСЬКИЙ облік
9. Складський облік
10. Дивись також
11. »Рівні підготовки користувачів
12. »Основні принципи оптимізації роботи в електронних таблицях
13. »Надбудови Excel

Подробиці Створено 14 Січень 2014

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

Планування інвестицій, оцінка інвестиційних проектів

універсальний інструмент для опису будь-якого проекту;
вбудовані функції оцінки ефективності можливість побудови моделі з некоректними розрахунками при помилках в формулах

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

Метою програм даного типу є розрахунок цільових показників ефективності вкладень коштів у новий проект або розширення / поліпшення існуючого бізнесу. До таких цільових показників оцінки, наприклад, відносяться:

• NPV (net present value) - чиста поточна вартість інвестицій
• IRR (internal rate of return) - внутрішня норма прибутковості
• Термін окупності інвестицій (pay-back period)

При оцінці інвестиційних проектів використовується ставка дисконтування, що характеризує зміну вартості грошей у часі.

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

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

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

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

На нашому сайті представлена ​​спільна з нашими партнерами КГ "Воронов і Максимов" розробка для експрес-оцінки інвестиційних проектів:

Майстер проектів. Попередня оцінка

фінансове планування

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

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

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

Економічна модель підприємства зазвичай складається з декількох взаємопов'язаних блоків:

• Розрахунок обсягів і прямих витрат на основі натуральних і вартісних прогнозів продажів
• Розрахунок маржинального прибутку за видами продукції або товарними групами
• Розрахунок постійних витрат за статтями
• Розрахунок залучених коштів і виплат по кредитах
• Інша фінансова діяльність, лізинг, факторинг
• Інвестиційна діяльність, розрахунок капітальних вкладень
• Розрахунок податкових виплат

Наявність і складність кожного блоку визначається розміром організації та іншими характеристиками бізнесу.

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

Якісний фінансовий план в результаті повинен надавати три основних взаємопов'язаних звіту:

• Бюджет доходів і витрат (плановий звіт про прибутки і збитки)
• Бюджет руху грошових коштів (плановий cash-flow)
• плановий баланс

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

Створення якогось універсального програмного продукту для побудови фінансового плану організацій різних сфер бізнесу - завдання практично нездійсненне, так як потрібно моделювання різних принципів обліку прямих витрат, а також безліч інших унікальних особливостей роботи конкретного підприємства. У більшості випадків навіть у великих холдингах реальне фінансове планування залишається в Excel, а в ERP-систему вводяться тільки підсумкові результати. Ми свого часу намагалися розробити типові комерційні програмні продукти в Excel, але, на жаль, великим попитом вони не користувалися.

На нашому сайті представлена ​​модель для планування руху грошових коштів в невеликих торгових організаціях:

Фінансовий облік в Excel. Планування грошових потоків

бюджетування

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

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

До основних етапів бюджетного процесу можна віднести наступні операції:

• Підготовка бюджету кожного підрозділу відповідальним фахівцем
• Об'єднання даних в загальний фінансовий план, розрахунок і аналіз результатів планування
• Внесення коригувань для досягнення цільових показників
• Контроль виконання бюджету на рівні кожного підрозділу і в цілому по організації

Існує цілий клас програмних продуктів для підтримки процесу бюджетування у великих організаціях і холдингових структурах. Такі програми зазвичай вимагають значних матеріальних витрат при впровадженні та підтримці, але об'єктивно оцінити економічний ефект від їх впровадження буває важко. Основною перевагою таких програм перед електронними таблицями є зручність консолідації бюджетів в єдиний файл і робота в многопользовательском режимі.

Недоліки використання електронних таблиць для розподіленої багатокористувацької роботи загальновідомі. У завданнях бюджетування ці особливості проявляються в повній мірі. Відзначимо, зокрема:

• Складність захисту структури даних від зміни
• Труднощі при консолідації даних в кінцевий фінансовий план: потрібно копіювання, зв'язку з зовнішніми файлами, або автоматизований імпорт
• Ненадійність пов'язаних файлів Excel - посилання легко втрачають правильні адреси при недбалої роботи
• Можливість некоректного виправлення формул консолідації
• Помилки і обмеження Excel при многопользовательском режимі роботи з одним файлом

Крім того, Excel не підтримує потужні механізми аналізу даних в зведених звітах і діаграмах, заснованих на власних таблицях, набагато зручніше побудована робота із зовнішніми базами даних.

Тим не менше, більшість компаній, як невеликих, так і великих, все ж використовують Excel для підготовки і консолідації бюджетів підрозділів, вважаючи такий підхід виправданим з економічної точки зору. Ми, до речі, теж швидше підтримуємо такий підхід - при деякій мірі автоматизації багато недоліків електронних таблиць можна знизити до прийнятного рівня. Бажано автоматизувати наступні функції:

• Імпорт бюджетів підрозділів даних в кінцевий файл фінансового планування
• Імпорт даних з облікових систем для план-фактного аналізу
• Підготовка даних для багатовимірного аналізу

В принципі, Excel включає в себе деякі методи захисту даних і розподіленої роботи . функції консолідації даних за кодами також можуть істотно спростити збір інформації в єдиний файл, а за допомогою макросів на VBA можна написати досить «розумну» процедуру імпорту. Знання і використання всіх цих засобів може істотно спростити процес бюджетування на підприємстві.

Існує ряд тиражованих і індивідуальних розробок, які суміщають принципи безпечного зберігання інформації в базах даних і їх аналізу за допомогою Excel. Так, зокрема, працює відома система бюджетування PALO.

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

Фінанси в Excel. консолідація бюджетів

Аналіз даних із зовнішніх систем

швидкі формули, фільтри, зведені таблиці Excel, Power Pivot потрібні фахівці для розробки запитів до баз даних

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

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

У будь-якому випадку Excel надає потужні інструменти аналізу, де-факто будучи стандартом багатовимірної обробки даних і віртуальних кубів.

На нашому сайті аналізу великих обсягів даних присвячений цілий цикл статей:

Обробка великих обсягів даних. Фомули

Обробка великих обсягів даних. інтерфейс

Обробка великих обсягів даних. зведені таблиці

Підготовка даних для зовнішніх систем

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

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

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

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

калькуляція собівартості

простий і зрозумілий варіант для розрахунку однорівневих специфікацій (наприклад, для торговельних організацій) складно забезпечити розрахунок багаторівневих специфікацій / рецептур;
потрібна синхронізація змінюються параметрів з обліковими системами

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

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

Завдання формування планових калькуляцій часто вирішується в електронних таблицях. Для простих однорівневих специфікацій - це цілком допустимий і часто оптимальне рішення. У файлі Excel формуються єдиний довідник цін закупівлі і таблиці компонентів специфікації. Основний розрахунок складається з досить простих формул пошуку даних і підсумовування підсумків.

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

На нашому сайті є спеціальна стаття, присвячена розрахунками за специфікаціями .

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

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

Ми розробили спеціальну програму для вирішення описаних задач:

Фінанси в Excel. калькуляція собівартості

Аналіз фінансової звітності (фінансовий аналіз)

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

У теорії фінансового менеджменту фінансовий аналіз призначений для об'єктивної оцінки економічного стану підприємства на основі форм стандартної звітності. Для цього розраховуються різні коефіцієнти, будуються графіки і схеми.Прі допомогою універсальних коефіцієнтів можна порівняти ефективність роботи підприємства в різні періоди часу або провести порівняльний аналіз діяльності різних організацій. В якості вихідних даних фінансової звітності потрібно, як мінімум, бухгалтерський баланс. Для спеціальних розрахунків можуть знадобитися додаткові дані, наприклад, звіт про прибутки і збитки.

Комплексний фінансовий аналіз включає в себе кілька типів завдань:

• аналіз активів
• аналіз платоспроможності
• аналіз рентабельності
• Аналіз фінансової стійкості
• Факторний аналіз

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

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

Як не дивно, але головним недоліком аналізу фінансової звітності в реаліях нашої економіки є низька цінність отриманих результатів незалежно від якості вироблених обчислень. Більшість вітчизняних підприємства готує окрему управлінську звітність, дані якої сильно відрізняються від стандартних бухгалтерських форм. Наприклад, на багатьох підприємствах висока частка "сірих" готівкових розрахунків, які не відображаються у бухгалтерії. Оцінка основних засобів, нематеріальних активів, goodwill може взагалі не потрапляти в управлінський облік. Ці ж показники в бухгалтерському обліку можуть не відповідати ринковій оцінці в рази. Відповідно, застосування методів фінансового аналізу до таких даних буде мати на практиці дуже низький ефект.

УПРАВЛІНСЬКИЙ облік

звичний інтерфейс;
відсутність витрат на впровадження додаткових програм неможливість організувати нормальну систему подвійного запису;
складність підтримки зв'язків між елементами обліку;
ненадійність зберігання інформації

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

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

На відміну від бухгалтерського, для управлінського обліку, в принципі, відсутня будь-яка загальноприйнята теорія побудови автоматизованих систем, немає чітко прописаних стандартів або вимог. Тому кожне підприємство часто «винаходить велосипед», довільним чином перетворюючи вихідні дані в фінансові звіти. Цьому також сприяє невисокий рівень економічної освіти керівників компаній, які потребують якихось специфічних форм і розрахунків для аналізу діяльності підприємства. Для таких завдань електронні таблиці Excel є цілком зручним інструментом, але перевірити коректність результатів обчислень буває дуже складно, та й не потрібно.

На нашу думку, комплексний управлінський облік повинен будуватися на базових принципах подвійного запису - нічого ціннішого в економіці підприємства не винайдено з XV століття. На виході повинні вийти три основних звіту:

• Звіт про прибутки і збитки
• Звіт про рух грошових коштів
• управлінський баланс

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

Теоретично можна спробувати вирішити задачу введення даних за принципом подвійного запису і в Excel. Для цього можна зробити загальний журнал операцій, звідки через формули будуть обчислюватися звітні форми. Про зручність інтерфейсу користувача в такому випадку говорити не доводиться. До того ж для побудови звітних форм доведеться задіяти механізми автоматизації та діалогові форми VBA, тобто це буде, по суті, вже не зовсім Excel.

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

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

Фінанси в Excel. УПРАВЛІНСЬКИЙ облік

Фінанси в Excel. Облік грошових потоків

Фінанси облік в Excel. Домашні фінанси

баланси контрагентів

Складський облік

звичний універсальний інтерфейс ненадійність зберігання даних;
незручність роботи з довідниками

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

Файл Excel не є базою даних, незважаючи на наявність функцій пошуку і теоретично величезний розмір листа в xlsx-форматі. Електронні таблиці не підтримують зовсім, або реалізують в дуже обмеженому вигляді стандартні функції навіть застарілих файл-серверних систем управління базами даних (СКБД). Ось деякі важливі особливості сховища даних:

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

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

Використовуючи Excel в якості системи складського обліку, бажано хоча б дотримуватися принцип накопичувального збору інформації. Так зокрема, побудована програма, представлена ​​на нашому сайті:

Фінанси в Excel. Складський облік

Дивись також

»Копіювання та вставка

У статті описуються можливості використання буфера обміну Windows і Microsoft Office, а також особливості копіювання і вставки даних в Excel ....

»Рівні підготовки користувачів

У вимогах до офісних співробітників часто згадується фраза «досвідчений користувач Excel». Це ж все пишуть в своїх резюме. І, ...

»Основні принципи оптимізації роботи в електронних таблицях

Знання спеціальних прийомів роботи в електронних таблицях Excel дозволяє в рази скоротити час розробки моделей, підвищити ...

»Надбудови Excel

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