Зробити фінансовий аналіз стану підприємства в Excelза 5 хвилин вам допоможе QFinAnalysis.

З цієї статті ви дізнаєтесь:

• Про завдання, які ви зможете вирішити за допомогою програми;
• Про можливості фінансового аналізув QFinAnalysis;
• Як працювати із програмою;

А також зможете завантажити файл Excel з прикладом розрахунку фінансового стану за допомогою QFinAnalysis.

За допомогою QFinAnalysis ви зможете вирішитинаступні завдання фінансового аналізу підприємства:

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

Програма QFinAnalysis допоможе вам зробити:

• Діагностику ймовірності банкрутства - 23 сучасні методики розрахунку ймовірності банкрутствавключаючи методики з використанням апарату штучного інтелекту;
• Аналіз фінансових показників – близько 20 фінансових коефіцієнтів(ліквідності, ділової активності, фін.стійкості, рентабельності та ін.);
• Оцінку платоспроможності підприємства – регламентований аналіз платоспроможності за методикою Уряду РФ;
• Розрахунок чистих активів підприємства;

Аналіз ліквідності балансу підприємства;

горизонтальний вертикальний аналіз балансу компанії;

Прогнозування банкрутства підприємстваза найбільш популярними західними методиками (Альтмана, Бівера, Таффлера та ін.) та адаптованим вітчизняним методикам (ІГЕА, Казанської школи, Зайцевої, Савицької та ін.).

Як працювати із програмою?

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

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

Для того, щоб зробити фінансовий аналіз стану підприємстваза допомогою QFinAnalysis, вам необхідно в програму ввести показники балансу підприємства:

Баланс підприємства у старій формі вводимо в лист "Баланс":

Баланс у новій формі вводимо в лист "Новий баланс":

Переходимо по сторінках та отримуємо:

1. Аналіз динаміки балансу та частки за статтями у листі "аналіт. баланс"

2. Коефіцієнти аналізу підприємства для:

• Оцінки фінансової стійкості;
• оцінки ліквідності;
• оцінки рентабельності;
• Оцінки ділової активності;

- Лист "коеф.":

3. Оцінку ліквідності - лист "аналіз лікв.":

4. Моделі множинного дискримінантного аналізу (MDA-моделі) оцінки ризику банкрутства підприємства:

• Двофакторна модель Альтмана
• Двофакторна модель Федотової
• П'ятифакторна модель Альтмана
• Модифікована модель Альтмана для Росії
• Чотирьохфакторна модель Таффлера
• Чотирьох факторна модель Лиса
• Чотирифакторна модель Спрінгейту
• Чотирьох факторна модель ІДЕА
• Модель Сайфуліна-Кадикова
• Модель Пареної-Долголаєва
• Модель Республіки Білорусь
• Модель Савицької

5. Логістичні моделі (Logit-моделі) оцінки ризику банкрутства підприємства показують ймовірність банкрутства у відсотках

• Модель Альтмана-Сабато (2007)
• Модель Ліна-П'єсе (2004)
• Модель ДжуХа-Техонгу (2000)
• Модель ДжуХа-Техонгу (2000)
• Модель Грузчинського (2003)
• Модель Грузчинського (2003)

6. Експертна модель "Зайцевої", в якій кожному показнику можна задавати вагу вручну, - лист "експертн. моделі"

• Модель Казанського Державного Технологічного Університету (КДТУ) підприємств промисловості (яка дозволяє визначити клас кредитоспроможності);
• Модель Бівера;

8. Модель уряду РФ (оцінка платоспроможності та структури балансу підприємства):

9. Розрахунок чистих активів підприємства - лист "ЧА":

• Novo Forecast Lite- автоматичний розрахунок прогнозув Excel.
• 4analytics - ABC-XYZ-аналізта аналіз викидів у Excel.
• Qlik Sense Desktop та QlikViewPersonal Edition - BI-системи для аналізу та візуалізації даних.

Тестуйте можливості платних рішень:

• Novo Forecast PRO- прогнозування Excel для великих масивів даних.

Фінансовий аналіз у Excel

Фінансовий аналіз у Excel Призначення.

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

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

Примітка: Додаткові функції, які потребують встановлення доповнення"Пакет аналізу" ( Analysis ToolPak ) за допомогою диспетчера надбудов, що входить до комплекту постачанняMicrosoft Excel, Тут не розглядаються.

Види фінансових функций.

За типом розв'язуваних завдань, всі фінансові функції в Excel можна розбити на наступні умовні групи:

Функції до розрахунків амортизаційних відрахувань.

Функції для аналізу звичайних ануїтетів.

Функції аналізу ефективності інвестиційних проектів.

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

Функції до розрахунків амортизаційних відрахувань.

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

балансова вартістьактиву початку періоду експлуатації;

залишкова (ліквідаційна) вартістьактиву;

термін експлуатації(корисного життя) активу;

період- Порядковий номер періоду нарахування амортизації.

Функції розрахунку амортизаційних відрахувань використовують різні методи списання амортизації:

Назва методу амортизації	Функція Excel, що реалізує метод та її синтаксис
1. Рівномірний (лінійний)	АПЛ (початкова вартість активу; ліквідаційна вартість активу; час експлуатації)
2. За сумою років корисного використання	АСЧ (початкова вартість активу; ліквідаційна вартість активу; час експлуатації; період для розрахунку величини відрахувань)
3. Метод подвійного списання (прискорена амортизація)	ДДОБ (початкова вартість активу; ліквідаційна вартість активу; час експлуатації; знижуючий коефіцієнт) Якщо коефіцієнт не заданий, то вважається, що він дорівнює 2
4. Метод зменшуваного залишку	ФУО (початкова вартість активу; ліквідаційна вартість активу; час експлуатації; період для розрахунку величини відрахувань; у місяців експлуатації у першому році)

Основні методи амортизації активів та функції їх розрахунків у MS Excel

Функція АПЛ() обчислює розмір щорічних відрахувань під час використання методу рівномірного списаннязносу активу.

Функції АСЧ(), ФУО(), ДДОБ() реалізують застосування методів прискореної амортизації, які дозволяють списати основну частину вартості активів у початкові періоди їх експлуатації, коли вони використовуються з максимальною інтенсивністю, створюючи цим резерв для їх своєчасної заміни у разі фізичного зносу або морального старіння. Методи прискореної амортизації дозволяють також знизити базу оподаткування підприємства.

Як опис практичного застосування вищеперелічених функцій наведемо наступний приклад.

Припустимо, для забезпечення виробничої діяльності підприємства Ви придбали деяке обладнання. На момент введення цього активу в експлуатацію його первісна вартість становила 10000 тис. руб. Корисний термін експлуатації обладнання складає 6 років. Будь-який вид довгострокового активу (що функціонує понад 1 рік) має такі властивості, як фізичне та моральне старіння. Таким чином, наприкінці терміну експлуатації (терміну корисної служби) даного активу його ліквідаційна вартість імовірно становитиме 1000 тис. руб.

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

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

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

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

Введіть у будь-яку комірку створеної таблиці формулу для розрахунку суми амортизації при рівномірному списанні зносу - функцію АПЛ().

Функції в Excel вводяться за допомогою «Майстри функцій», який викликається за командою Вставка/функція…або натисканням кнопки Стандартної панелі інструментів. Щоб ввести функцію в комірку, виберіть категорію Фінансовіу вікні зі списку зліва і необхідну функцію з відповідного списку справа.

Натисніть кнопку ОК.На робочому аркуші з'явиться діалогове вікно з назвою обраної функції у рядку формул та описом обов'язкових та необов'язкових аргументів. Після імені кожної функції у дужках задаються аргументи. Якщо функція не використовує аргументів, то її ім'ям слідують порожні дужки (), без пробілу з-поміж них. Аргументи відокремлюються крапкою з комою (;). Як елемент формули може бути адреса осередку як абсолютної чи відносної посилання, тобто. у обчисленні бере участь вміст цієї комірки.

Синтаксис усіх функцій можна переглянути там же у майстрі функцій. Синтаксис функцій для розрахунок амортизаційних відрахувань наведено у таблиці “Основні методи амортизації активів та функції їх розрахунків у MS Excel”.

При обчисленні суми амортизації за методом рівномірного списання амортизації використовується співвідношення виду:

Для будь-кого i-гоперіоду терміну експлуатації активу сума нарахування амортизації АПЛ i однакова.

Діапазон осередків С10:С15 містить формулу розрахунку амортизації відповідно до синтаксису Excel:

=АПЛ(10000;1000;6) (Повертається результат: 1500,00).

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

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

де: початкова вартість- Початкова вартість активу;

ліквідаційна вартість- Ліквідаційна вартість активу;

термін- Термін експлуатації активу;

період- Порядковий номер періоду нарахування амортизації;

Таким чином, за два послідовні періоди (наприклад, за 1-й та 2-й) сума амортизації складе відповідно:

= АСЧ (10000; 1000; 6; 1) (Результат: 2571,43)

= АСЧ (10000; 1000; 6; 2) (Результат: 2142,86)

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

При розрахунку амортизації активу за конкретний період функція використовує таку формулу:

де: накопичена амортизація- Накопичена амортизація за попередні періоди експлуатації активу;

ставка– фіксована процентна ставка, що розраховується Excel за такою формулою:

,

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

Особливим випадком у використанні функції ДОБ() є розрахунок амортизації за перший та останній періоди експлуатації активу.

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

Для останнього періоду функція ДОБ() використовує іншу формулу:

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

Таким чином, за 1-й та 2-й періоди амортизація складе:

= ФУО (10000; 1000; 6; 1) (Результат: 3190,00)

= ФУО (10000; 1000; 6; 2) (Результат: 2172,39)

Метод подвійного списання ґрунтується на застосуванні прискореної норми щорічної амортизації. Як остання зазвичай приймається норма, яка використовується при рівномірному списанні, помножена на деякий коефіцієнт. В Excel цей метод реалізує функція ДДОБ(), що дозволяє використовувати як коефіцієнт будь-яке позитивне число. За замовчуванням значення коефіцієнта приймається 2.

Сума зносу за i-йперіод визначається з наступного співвідношення:

де: коефіцієнт –коефіцієнт, що визначає норму зниження балансової вартості (прискорення нарахування амортизації).

При обчисленні зношування використовуючи функцію ДДОБ() величина амортизації максимальна у період і знижується у наступні періоди.

Якщо не обов'язково застосовувати в розрахунках дворазове списання зносу, то можна варіювати значенням аргументу коефіцієнт.

Для нашого прикладу амортизація за 1-й та 2-й періоди становитиме:

= ДДОБ(10000; 1000; 6; 1) (Результат: 3333,33)

= ДДОБ(10000; 1000; 6; 2) (Результат: 2222,22)

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

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

Синтаксис функції:

=ПУО (початкова вартість; ліквідаційна вартість; період амортизації; початковий період; кінцевий період; коефіцієнт; без перемикання),

де: Початковий період – початковий період, для якого обчислюється амортизація;

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

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

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

Н
апример:

а) за період з 6 до 12 місяців експлуатації:

= ПВВ (10000; 1000; 6 * 12; 6; 12) (Результат: 1313,28)

де: 6 років * 12 місяців– загальна кількість місяців експлуатації цього активу;

6,12 - Порядкові номери періоду нарахування зносу.

б) з 1 по 200 день експлуатації (з точним числом днів на рік):

=ПУО (10000; 1000; 6 * 365; 1; 200) (Результат: 1660,95)

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

Таблиці Excelдля проведення фінансового аналізувід Малахова В.І. дозволяють розрахувати баланс у відсотковій формі, оцінку ефективності управління, оцінку фінансової (ринкової) стійкості, оцінку ліквідності та платоспроможності, оцінку рентабельності, ділову активність, положення підприємства на РЦБ, модель Альтмана. Будуються діаграми активу балансу, динаміки виручки, динаміка валового та чистого прибутку, динаміка заборгованості.

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

Таблиці Excel фінансового аналізу підприємстваЗайковського В.Е. дозволяють на основі 1 та 2 форм зовнішньої бухгалтерської звітності зробити розрахунок банкрутства підприємства за моделлю Альтмана, Таффлера та Лиса,оцінити фінансовий стан підприємства за показниками ліквідності, фінансової стійкості, стану основних засобів, оборотності активів, рентабельності. Крім цього, знаходять зв'язок між неплатоспроможністю підприємства із заборгованістю держави перед ним. Є графіки зміни активів та пасивів підприємства у часі.

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

Модуль фінансового аналізу для MS Excel

Дозволяє отримати загальну оцінку про ступінь стійкості підприємства, що цікавиться, на підставі стандартної зведеної звітності про його діяльність.

Оцінка фінансово-господарської діяльності

Надіслати свою гарну роботу до бази знань просто. Використовуйте форму нижче

Студенти, аспіранти, молоді вчені, які використовують базу знань у своєму навчанні та роботі, будуть вам дуже вдячні.

Розміщено на http://www.allbest.ru/

ФІНАНСОВИЙ АНАЛІЗ ВEXCEL

Завдання №1

Опис функції ПЛТ або ППЛАТ (ставка; кпер; пс; бс; тип)

Повертає суму періодичного платежу на основі сталості сум платежів та сталості відсоткової ставки.

ставка - відсоткова ставка за позикою.

кпер - загальна кількість виплат за позикою.

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

тип - число 0 (нуль) або 1, що означає, коли має здійснюватися виплата.

Розглянемо приклад розрахунку 30-річної іпотечної позички зі ставкою 8% річних за початкового внеску 20% та щомісячної (щорічної) виплати за допомогою функції ППЛАТ

ФункціяППЛАТ (ПЛТ) обчислює величину постійної періодичної виплати ренти (наприклад, регулярних платежів за позикою) за постійної відсоткової ставки.

Зазначимо, що дуже важливо бути послідовним у виборі одиниць виміру для завдання аргументів СТАВКА та КПЕР. Наприклад, якщо ви робите щомісячні виплати за чотирирічною позикою з розрахунку 12% річних, для завдання аргументу СТАВКА використовуйте 12%/12, а завдання аргументу КПЕР - 4*12. Якщо ви робите щорічні платежі з тієї ж позики, то для завдання аргументу СТАВКА використовуйте 12%, а для завдання аргументу КПЕР - 4.

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

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

Наприклад, депозит у банк у сумі 1000 рублів представляються аргументом -1000, якщо вкладник, і аргументом 1000, якщо ви представник банку.

ІНДИВІДУАЛЬНЕ ЗАВДАННЯ. Обчислити n-річну (загальна кількість періодів виплат) іпотечну позику купівлі квартири за руб. з річною ставкою i% та початковим внеском A%, . Зробити розрахунок для щомісячних та щорічних виплат. Знайти суми періодичних щомісячних та щорічних виплат, загальні суми щомісячних та щорічних виплат, загальні суми щомісячних та щорічних комісійних.

Для виконання завдання заповніть таблицю своїми вихідними даними:

Вартість квартири - Р

Річна ставка i%

Строк погашення позички n

Початковий внесок A%

Початковий внесок у грошах розраховується за формулою:

вартість квартири * А%

Щорічні виплати розраховуються за функцією

(ПЛТ(ставка; кпер; пс; бс; тип) чи ППЛАТ(ставка; термін; -суду);

щомісячні виплати

ППЛАТ(ставка/12; термін * 12; -суду)), або ПЛТ (ставка / 12; термін * 12; -суду)

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

Загальні щомісячні = щомісячні * термін * 12

Загальні щорічні = щорічні термін

Щомісячні комісійні = загальні щомісячні - позичка

Щорічні комісійні = загальні щорічні - позичка

ВАРІАНТИ ЗАВДАНЬ

ЗАВДАННЯ №2

ЧПС (ставка; значение1; значение2; ...) чи НПЗ (ставка; значение1; значение2; ...)

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

ставка - ставка дисконтування за період.

Значение1, значение2,... -- від 1 до 29 аргументів, які мають витрати і доходи.

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

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

Приклад 1

	Опис
	Річна ставка дисконтування
	Початкові витрати на інвестиції за один рік, рахуючи від поточного моменту
	Дохід за перший рік
	Дохід за другий рік
	Дохід за третій рік
	Опис (результат)
ЧПС(A2; A3; A4; A5; A6)	Чиста наведена вартість інвестиції (1188,44)

У прикладі початкові витрати 10 000 крб. були включені як одне із значень, оскільки виплата проводилася наприкінці першого періоду.

Приклад 2

	Опис
	Річна ставка дисконтування. Вона може бути темпом інфляції або відсоткову ставку за конкуруючими інвестиціями.
	Початкові витрати на інвестиції
	Дохід за перший рік
	Дохід за другий рік
	Дохід за третій рік
	Дохід за четвертий рік
	Дохід за п'ятий рік
	Опис (результат)
ЧПС(A2; A4:A8)+A3	Чиста наведена вартість цієї інвестиції (1 922,06)
ЧПС(A2; A4:A8; -9000)+A3	Чиста наведена вартість цієї інвестиції зі втратою 9000 на шостому році (-3 749,47)

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

Розглянемо таке завдання. Вас просять дати в борг 10 000 рублів і обіцяють повернути за рік 2 000 рублів, за два роки - 4 000 рублів. Через три роки – 7 000 рублів. За якої річної процентної ставки ця угода вигідна?

У наведеному розрахунку в комірку в комірку В7 введено формулу

= НПЗ (В6; В2: В4)

Спочатку в комірку В6 вводиться довільний відсоток, наприклад, 3%. Після цього вибираємо команду Сервіз, Підбір параметра і заповнюємо діалогове вікно Підбір параметра, що відкрилося.

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

= НПЗ (В6; В2: В4)

У полі Значення вказуємо 10000 – розмір позички. У полі Змінюючи значення осередку даємо посилання на осередок В6, у якому обчислюється річна відсоткова ставка. Після натискання кнопки ОК засіб підбору параметрів визначить, за якої річної процентної ставки чистий поточний обсяг вкладу дорівнює 10000 рублів. Результат обчислення виводиться в комірку В6.

У нашому випадку річна облікова ставка дорівнює 11,79%.

Висновок: якщо банки пропонують велику річну відсоткову ставку, то запропонована угода не є вигідною.

ІНДИВІДУАЛЬНЕ ЗАВДАННЯ: Вас просять дати в борг Р рублів і обіцяють повернути Р1 руб. через рік, Р2 руб. - за два роки і т.д. і, нарешті, РН руб. через Н років. За якої річної процентної ставки ця угода має сенс? (ЧПС (ставка; значення 1; значення 2; ...). Для уточнення процентної ставки використовувати метод підбору параметра.

ЗАВДАННЯ №3

ПС(ставка; кпер; плт; бс; тип) або ПЗ(ставка; кпер; плт; бс; тип)

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

Наприклад, якщо отримано позику на автомобіль під 10 відсотків річних і робляться щомісячні виплати, то процентна ставка за місяць становитиме 10%/12 або 0,83%. Як значення аргументу ставка потрібно ввести формулу 10%/12 або 0,83% або 0,0083.

Наприклад, якщо отримана позичка на 4 роки під автомобіль та робляться щомісячні платежі, то позичка має 4*12 (або 48) періодів. Як значення аргументу кпер у формулу потрібно ввести число 48

ПЛТ - виплата, що виробляється в кожен період і не змінюється за весь час виплати ренти. Зазвичай виплати включають основні платежі та відсоткові платежі, але не включають інших зборів або податків. Наприклад, щомісячна виплата за чотирирічною позикою в 10 000 руб. під 12 відсотків річних становитиме 263,33 руб. Як значення аргументу виплата потрібно запровадити у формулу число -263,33.

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

приклад

Результат виходить негативний, оскільки він представляє гроші, які необхідно виплатити, вихідний грошовий потік. Якби за ануїтет вимагалося заплатити 60 000, ця інвестиція була б не вигідною, оскільки наведена вартість (59 777,15) ануїтету менша за цю суму.

· Примітка. Щоб отримати місячну відсоткову ставку, поділіть річну ставку на 12. Щоб дізнатися кількість виплат, помножте кількість років кредиту на 12.

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

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

Розглянемо таке завдання. Припустимо, що у вас просять у борг 10000 рублів і обіцяють повертати по 2000 рублів протягом 6 років. Чи буде вигідна ця угода за річної ставки 7%?

У розрахунку, що проводиться в осередок В5 введена формула

= ПЗ (В4; ​​В2; -В3)

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

ІНДИВІДУАЛЬНЕ ЗАВДАННЯ. Вас просять дати у борг Р руб. та обіцяють повертати по А руб. щорічно протягом Н років. За якої процентної ставки ця угода має сенс.

Для вирішення задачі використовувати функцію

(ПС(ставка; кпер; плт; бс; тип) чи ПЗ(ставка; термін; -ежегод.выплаты)). У функції спочатку береться довільна ставка, потім уточнюється шляхом підбору параметра.

ЗАВДАННЯ № 4

ПРПЛТ (ставка; період; кпер; пс; бс; тип)

Повертає суму платежів відсотків по інвестиції за цей період на основі сталості сум періодичних платежів та сталості процентної ставки.

ставка - процентна ставка за період.

період - це період, для якого потрібно знайти платежі за відсотками; повинен бути в інтервалі від 1 до «кпер».

кпер - загальна кількість періодів платежів по ануїтету.

приклад

	Опис
	Річна процентна ставка
	Період, для якого потрібно знайти відсотки
	Строк позики (у роках)
	Вартість позики на даний момент
	Опис (результат)
ПРПЛТ (A2/12; A3*3; A4; A5)	Виплати за відсотками за перший місяць на наведених вище умовах (-22,41)
ПРПЛТ (A2; 3; A4; A5)	Виплати за відсотками за останній рік на наведених вище умовах (нарахування відсотків здійснюються щорічно) (-292,45)

ОСПЛТ(ставка; період; кпер; пс; бс; тип)

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

ставка - процентна ставка за період.

період - задає період, значення має бути в інтервалі від 1 до "кпер".

кпер - загальна кількість періодів платежів по ануїтету.

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

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

тип - число 0 або 1, що означає, коли має здійснюватися виплата.

нотатки

Переконайтеся, що ви послідовні у виборі одиниць виміру для завдання аргументів «ставка» та «кпер». Якщо ви робите щомісячні виплати за чотирирічною позикою з розрахунку 12 відсотків річних, то використовуйте 12%/12 для завдання аргументу «ставка» та 4*12 для завдання аргументу «кпер». Якщо ви робите щорічні платежі з тієї ж позики, то використовуйте 12% для завдання аргументу «ставка» і 4 для завдання аргументу «кпер».

приклад

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

еxcel таблиця формула

щорічна плата обчислюється в осередку В3 за формулою

=ППЛАТ(відсоток; термін; - розмір позички)

За перший рік плата за відсотками у осередку В7 обчислюється за формулою

=D6*$B$1

Основна плата $B$3-B7

Залишок боргу в осередку D7 обчислюється за формулою

=D6-C7

У роки, що залишилися, ці плати визначаються за допомогою протягування маркера заповнення виділеного діапазону В7:D7 вниз по стовпцях.

Зазначимо, що основну плату та плату за відсотками можна було безпосередньо знайти за допомогою фукцій ОСНПЛАТ та ПЛПРОУ відповідно.

ІНДИВІДУАЛЬНЕ ЗАВДАННЯ. Обчислити щорічні основні платежі, плату за відсотками, загальну річну виплату та залишок боргу з прикладу позички Р крб. під річну ставку i% терміном Н років.

Використовувати функції

(ПЛТ(ставка; кпер; пс; бс; тип), ПРПЛТ(ставка; період; кпер; пс; бс; тип), ОСПЛТ(ставка; період; кпер; пс; бс; тип))

ППЛАТ (ставка; термін; -суду), ПЛПРОЦ (ставка; період; термін; - позичка), ОСНПЛАТ (ставка; період; термін; -суду).

Залишок боргу = борг - ОСНПЛАТ

ЗАВДАННЯ № 5

КПЕР (ставка; пт; пс; бс; тип)

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

ставка - процентна ставка за період.

ПЛТ - виплата, що проводиться в кожен період; це значення не може змінюватись протягом усього періоду виплат. Зазвичай платіж складається з основного платежу та платежу за відсотками та не включає податків та зборів.

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

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

Наприклад, якщо ви позичаєте 1000 рублів при річній ставці 1% і збираєтеся виплачувати по 100 рублів на рік, то кількість виплат обчислюється таким чином:

КПЕР(1%; -100; 1000)

У результаті одержуємо відповідь: 11.

ІНДИВІДУАЛЬНЕ ЗАВДАННЯ. Ви берете в борг Р руб. під річну ставку i% і збираєтеся виплачувати за руб. на рік. Скільки років заберуть ці виплати? Знайти 2 способами

1-й спосіб - використовувати функції

ПС (ставка; кпер; пт; бс; тип) або ПЗ (ставка; термін; - ежегод.вклад)

2 - й спосіб - використовувати функцію КПЕР(ставка; -ежег.вклад; позичка)

ЗАВДАННЯ №6

БС(ставка; кпер; пт; пс; тип) або БЗ(ставка; кпер; пт; пс; тип)

Повертає майбутню вартість інвестиції на основі періодичних постійних (рівних за величиною сум) платежів та постійної процентної ставки.

ставка - процентна ставка за період.

кпер - це загальна кількість періодів платежів.

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

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

тип - число 0 або 1, що означає, коли має здійснюватися виплата. Якщо аргумент «тип» опущений, він вважається рівним 0.

Примітка. Річна процентна ставка ділиться на 12, тому що нарахування складних відсотків провадиться щомісяця.

Наведемо приклад використання функції БЗ. Припустимо, ви хочете зарезервувати гроші для спеціального проекту, який буде здійснено за рік. Припустимо, ви збираєтеся вкласти 1000 рублів за річної ставки 6%. Ви збираєтеся вкладати по 100 рублів на початку кожного місяця протягом року. Скільки грошей буде на рахунку наприкінці 12 місяців?

За допомогою формули

БЗ(6%/12; 12; -100; -1000; 1)

отримуємо відповідь 2301,4 руб.

ІНДИВІДУАЛЬНЕ ЗАВДАННЯ. Ви збираєтеся вкладати по А у. е. протягом Н років при річній ставці I%. Скільки грошей буде на рахунку за n років?

Використати функцію

БС (ставка; кпер; пт; пс; тип)) або БЗ (ставка; термін; - виплата)

ЗАВДАННЯ №7

Скласти звітну відомість реалізації товарів N магазинами з місяця А по місяць В. Знайти місце магазину за сумарним виторгом (функція РАНГ()), середній виторг магазину на місяць (СРЗНАЧ(масив виручки по місяцях)), відсоток прибутку магазину в загальній виручці(сумарний) виручку магазину / сумарний виторг всіх магазинів). Побудувати 2 діаграми (1 – відсоток прибутку до загальної виручки, 2 – обсяги реалізації продукції).

Вартість товару для кожного магазину є різною.

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

				Вартість товарів	Обсяги реалізації (тис. Шт.)
		вересень		44,45,46,47,201,202	24,25,26,27,36,38
	вересень
				39,38,40,41,49, 36	25,27,28,22,23,29
		вересень
				201,205,305,205,11,14,22	70,71,72,73,74,99,85
	вересень
	вересень			420,430,401,400, 300
		вересень

Розміщено на Allbest.ru

Подібні документи

Складання звітної відомості "Магазин" в Excel 2013. Робота з таблицями сімейства Microsoft Office. Побудова кругової діаграми та гістограми, графіків. Розробка процедур для табулювання функцій. Програмування функцій користувача VBA.

курсова робота , доданий 03.04.2014


Введення, редагування та форматування даних у табличному редакторі Microsoft Excel, форматування вмісту осередків. Обчислення в таблицях Excel за допомогою формул, абсолютні та відносні посилання. Використання стандартних функцій під час створення формул.

контрольна робота , доданий 05.07.2010


Створення електронних таблиць у MS Excel, введення формул з допомогою майстра функций. Використання відносної та абсолютної посилань у формулах. Логічні функції у MS Excel. Побудова діаграм, графіків та поверхонь. Сортування та фільтрація даних.

контрольна робота , доданий 01.10.2011


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

курсова робота , доданий 21.11.2012


Обчислення в Excel. Формули та функції: Використання посилань та імен, переміщення та копіювання формул. Відносні та абсолютні посилання. Поняття та типи функцій. Робоча книга Excel. Зв'язок між робочими листами. Побудова діаграм у EXCEL.

лабораторна робота , доданий 28.09.2007


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

курсова робота , доданий 25.04.2013


Процесор електронних таблиць Microsoft Excel – прикладна програма, призначена для автоматизації процесу обробки економічної інформації, представленої у вигляді таблиць; застосування формул та функцій для виробництва розрахунків; побудова графіків.

реферат, доданий 03.02.2013


Можливість використання формул та функцій у MS Excel. Відносні та абсолютні посилання. Типи операторів. Порядок виконання дій у формулах. Створення формули із вкладенням функцій. Формування та заповнення відомості витрати пального водієм.

контрольна робота , доданий 25.04.2013


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

лабораторна робота , доданий 15.11.2010


Функції, які застосовуються для обчислення параметрів позики та аналізу кредитів та вкладів, які представлені в Microsoft Excel. Аналіз економічної проблеми, на вирішення якої використовується фінансова функція КПЕР. Опис прикладів можливого використання.

В Excel, і торкатися вона аналізу ефективності компанії сумарно за показниками БДР, ОПУ і ОДДС. На відміну від решти розділів аналізу, тут практично не буде жодних вихідних даних – суто фінансові розрахунки. Як приклад використовуємо реальний проект, який вже працює у вигляді бізнесу, проте цифри там будуть умовні.

Почнемо з того, що визначимо, які показники ми хочемо виділити та розрахувати. В даному випадку це: EBIT, активів, чиста, активів, рентабельність персоналу, рентабельність основних засобів та рентабельність продажів. Всі ці показники потрібно буде окремо розрахувати – підкреслимо, саме розрахувати, тому що. вихідні дані з наявної звітності щодо EBIT та EBITDA не відображають точну картину.

Отже, створюємо таблицю в аркуші фін аналіз в Excel, і вписуємо туди перераховані показники.

Далі по порядку все розраховуємо. Рентабельність продажів розраховується легко: прибуток з відрахуванням прибуток ділений на виручку. Цей показник дає розуміння ефективності бізнесу загалом – з урахуванням усіх витрат. Загалом треба сказати, що показників рентабельності лише кілька сотень, але в нашому випадку достатньо взяти лише найголовніші, щоб можна було дати загальну оцінку актуальності інвестицій.

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

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

Тепер розраховуємо оборотність активів. Цей показник дуже важливий в оцінці бізнесу, тому що вказує на мобільність наявних активів і як часто вони використовуються. Що оборотність капіталу, то ефективнішим вважається . Формула розрахунку проста: EBIT/Total assets. Окремо варто розрахувати показники EBIT та EBITDA. Як перший можна застосувати показник чистого прибутку мінус податки, EBITDA розраховується ще простіше: EBIT+Depreciation+Interests. Краще обидва ці показники зробити у цій же таблиці – для зручності подальших розрахунків. Нарешті, для розрахунку чистого прибутку можна відібрати від EBIT усі податкові відрахування. У результаті всі фінансові показники повинні бути зібрані в таблиці, показаній вище.

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

Ну і нарешті, залишилося розрахувати NPV компанії, щоб було розуміння будь-якої актуальності в інвестиціях взагалі. На початку визначимося зі ставкою дисконтування – чи потрібно її розраховувати окремо через модель CAPM або досить просто взяти норму альтернативної прибутковості, якою цілком може погодитися, наприклад, державна облігація. Нині прибутковість по ОФЗ становить близько 10%. Саме цьому проекті загальний NPV вийшов на 2.2 млрд. рублів, термін дисконтованої окупності інвестицій – 31 місяць, отже дохідність вийшла близько 40% у річному вираженні, що свідчить про те, що інвестувати у цю компанію – доцільно.

На цьому, власне, і все. Це була остання стаття із серії про фінансовий аналіз у Excel.

Будьте в курсі всіх важливих подій United Traders - передплатіть наш