Програмування Excel для фінансистів. Фінансовий аналіз та інвестиційна оцінка підприємства Excel фінанси

Освоїти Excel легко!Якщо ви дотримуєтеся протилежної думки, вам не траплявся класний посібник для вивчення програми.

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

Отже, ось мій вистражданий перелік з 10 найкращих книг з Excel:

1. Джон Уокенбах "Microsoft Excel 2013. Біблія користувача"

2. Джон Уокенбах "Формули в Microsoft Excel 2013"

3. Джон Уокенбах "Excel 2013. Професійне програмування на VBA"

4. Білл Джелен і Майкл Александер "Зведені таблиці в Microsoft Excel"

5. Куртіс Фрай "Microsoft Excel 2013. Крок за кроком"

6. Грег Харвей "Microsoft Excel 2013 для чайників"

7. Конрад Карлберг "Бізнес аналіз з використанням Excel"

8. Шимон Беннінг "Основи фінансів з прикладами в Excel"

10. Микола Павлов “Microsoft Excel. Готові рішення – бери та користуйся!”

Посібниками, які я рекомендую в цій статті, користуюсь досі. Всі вони принесли величезну користь і обов'язково принесуть її вам. Безумовно, краще придбати паперові екземпляри, щоб робити позначки і залишати закладки. Так зручніше! Ось побачите, ці книги прискорять вашу роботу з Excel і вдихнуть її нове життя. Якщо ви так не вважаєте, із задоволенням посперечаюсь з вами в коментарях! =)

" № 28/2011

ОлександрЗеляєв,заступник начальника відділу
проектного планування управління інвестиційних програм ВАТ «АВТОВАЗ»

Павло Зирянов,начальник управління інвестиційних програм
ВАТ «АВТОВАЗ»

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

Встановлення обмежень

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

Всю інформацію заносять до однієї таблиці, наприклад, як показано на скріншоті (див. малюнок). У прикладі умов кілька. По-перше, це вільні суми, які є у компанії на рахунках, та час, протягом якого ці гроші не потрібні в обігу. На скріншоті це діапазони осередків H12: K12 та H11: K11.

По-друге, треба внести ставки за депозитами, які пропонують банки (D17: G25). Нарешті, по-третє, належить запровадити відомості про обсяги невикористаних лімітів у кожному з банків (С17:С25).

Налаштування файлу для розрахунків

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

У блоці «Суми до розміщення на депозитах за термінами днями, руб.» (H17:K25) відобразяться результати. Excel тут заповнить все сам.

У розділи «Разом розміщено, руб.» (С17: С25)) та «Дохід від коштів, розміщених на депозитах, за термінами в днях, руб.» (M17:P25) потрібно вручну ввести формули. Вони потрібні, щоб підсумувати депозити та обчислити дохід від вкладу. Підсумковий результат буде в осередку «Прибутковість від розміщення на депозитах разом, у рублях».

Пошук рішення

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

І в цьому ж діалоговому вікні необхідно встановити обмеження. Вони у нас такі

– всі вільні гроші мають бути розміщені на депозитах (Н27: К27 = Н12: К12);

- Сума вкладів в одному банку з урахуванням ліміту (L17: L25<= C17:C25).

Потім треба натиснути кнопку "Виконати". Програма знайде найвигідніше рішення.

ДО РЕЧІ. В Excel налаштування «Пошук рішення» за замовчуванням вимкнено. Включають її так: кнопка «Office», потім «Параметри Excel» > «Надбудови» > «Перейти», у вікні поставити галочку «Пошук рішення» і натиснути «Ок».

Завантажити файл з готовими формулами та інструкцію користувача.

Підготовлено у співпраці з редакцією журналу

Існують сотні онлайнових фінансових планувальників. Всі вони прості у використанні, але обмежені за функціональністю. MS Excel з їхньої фоні - справжній комбайн. У ньому є 53 фінансові формули на всі випадки життя, а для контролю та планування бюджету корисно знати три з них.

Функція ПЛТ

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

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

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

Функція СТАВКА

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

СТАВКА(кпер;плт;пс;бс;тип;прогноз)

• Кпер- загальна кількість періодів платежів для щорічного платежу.
• Плт- Виплата, що проводиться у кожний період; це значення не може змінюватись протягом усього періоду виплат. Зазвичай аргумент "плт" складається з основного платежу та платежу за відсотками, але не включає інших податків та зборів. Якщо його опущено, аргумент «пс» є обов'язковим.
• Пс- Наведена (поточна) вартість, тобто загальна сума, яка на даний момент рівноцінна ряду майбутніх платежів.
• Бс (необов'язковий аргумент)- Значення майбутньої вартості, тобто бажаного залишку коштів після останньої виплати. Якщо аргумент «бс» опущений, передбачається, що він дорівнює 0 (наприклад, майбутня вартість позики дорівнює 0).
• Тип (необов'язковий аргумент)- Число 0 (нуль), якщо платити потрібно в кінці періоду, або 1, якщо платити потрібно на початку періоду.
• Прогноз (необов'язковий аргумент)- Передбачувана величина ставки. Якщо аргумент "прогноз" опущений, передбачається, що його значення дорівнює 10%. Якщо функція СТАВКА не відповідає, спробуйте змінити значення аргументу «прогноз». Функція СТАВКА зазвичай збігається, якщо значення цього аргументу між 0 і 1.

Функція ЕФЕКТ

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

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

Курс «Excel для фінансиста» допоможе Вам:

• спростити аналітичну роботу;
• навчитися автоматизувати рутинні операції; легко та швидко виконувати складні обчислення.
• організувати управління корпоративними фінансами вашої компанії.

Курс орієнтований нафінансових директорів, спеціалістів фінансових служб, користувачів MS Excel інших спеціальностей та всіх зацікавлених осіб.

Навчання проводить сертифікований викладач Microsoft.

ПРОГРАМА КУРСУ:

1. Фінансові можливості MS Excel:

• функція БС – повертає майбутню вартість інвестиції на основі постійної процентної ставки.
• функція ПЛТ – повертає суму періодичного платежу для ануїтету на основі сталості сум платежів та постійної процентної ставки.
• функція КПЕР – повертає загальну кількість періодів виплати для інвестиції на основі періодичних постійних виплат та постійної процентної ставки.
• функція ВСД - повертає внутрішню ставку доходності низки потоків грошових коштів, представлених їх чисельними значеннями
• функція ЧПС – повертає величину чистої наведеної вартості інвестиції, використовуючи ставку дисконтування, а також послідовність майбутніх виплат (негативні значення) та надходжень (позитивні значення).
• функція МВСД - повертає модифіковану внутрішню ставку доходності низки періодичних грошових потоків і враховує як витрати на залучення інвестиції, і відсоток, одержуваний від реінвестування коштів.
• функція ПРПЛТ - повертає суму платежів за відсотками для інвестиції за цей період на основі сталості сум періодичних платежів та сталості відсоткової ставки.
• функція ДОХІД - повертає прибутковість цінних паперів, якими виробляються періодичні виплати відсотків і використовують із обчислення прибутковості облігацій.
• функція ПС – повертає наведену (до поточного моменту) вартість позики або інвестиції на основі постійної процентної ставки.
• функція СТАВКА - повертає процентну ставку за ануїтетом за період.
• функція ЕФЕКТ - повертає фактичну (ефективну) річну процентну ставку, якщо задані номінальна річна відсоткова ставка та кількість періодів на рік, за які нараховуються складні відсотки.
• функція БЕЗРАСПИС - повертає майбутню вартість первісної основної суми після застосування ряду (плану) ставок складних відсотків та використовується для обчислення майбутньої вартості інвестиції зі змінною процентною ставкою.
• функція ЧИСТНЗ – повертає чисту наведену вартість для грошових потоків, які не обов'язково є періодичними. Щоб обчислити чисту наведену вартість для низки періодичних грошових потоків
• функція ОСПЛТ - повертає величину платежу на погашення основної суми з інвестиції за цей період на основі постійних періодичних платежів та постійної процентної ставки.

2. Статистичні можливості MS Excel:

• МАКС, МІН, СРЗНАЧ, СРЗНАЧА, МЕДІАНА
• функція ПЕРЕДСКАЗ - обчислює або передбачає майбутнє значення за існуючими значеннями. Цю функцію можна використовувати для прогнозування майбутніх продажів, потреб у обладнанні чи тенденцій споживання.
• Функція ДОВЕРИТ - повертає довірчий інтервал для середньої генеральної сукупності з нормальним розподілом.
• функція РОСТ - розраховує прогнозоване експонентне зростання на основі наявних даних.
• функція ТЕНДЕЦІЯ - повертає значення відповідно до лінійного тренду.
• функції СТАНДОТКЛОНУ, СТАНДОТКЛОНУ,В та СТАНДОТКЛОН.Г- оцінює стандартне відхилення за заданими параметрами.

3. Фінансове моделювання та інвестиційний аналіз за допомогою MS Excel

• Аналіз ЩО-ЯКІ (диспетчер сценаріїв, таблиця даних, підбір параметрів)
• Надбудова Solver (для вирішення багатопараметричних завдань)
• Побудова прогнозів
• Побудова діаграми ПЛАН_ФАКТ
• Розрахунки кредитів

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

Тривалість: 16 ак.

Увага! У розкладі можливі зміни. Просимо уточнювати інформацію щодо електронній пошті [email protected]

У вартість включено:

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

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

Перехід до цього набору інструментів найлегше зробити через Майстер функцій.

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

Є також спосіб переходу до потрібного фінансового оператора без запуску початкового вікна Майстра. Для цих цілей у тій же вкладці Формулиу групі налаштувань "Бібліотека функцій"на стрічці клацаємо по кнопці «Фінансові». Після цього відкриється список всіх доступних інструментів даного блоку. Вибираємо потрібний елемент і натискаємо на нього. Відразу після цього відчиниться вікно його аргументів.

ДОХІД

Одним із найбільш затребуваних операторів у фінансистів є функція ДОХІД. Вона дозволяє розрахувати дохідність цінних паперів за датою угоди, датою набрання чинності (погашенням), ціною за 100 рублів викупної вартості, річною процентною ставкою, сумою погашення за 100 рублів викупної вартості та кількістю виплат (частота). Саме ці параметри є аргументами цієї формули. Крім того, є необов'язковий аргумент «Базис». Всі ці дані можуть бути введені з клавіатури прямо у відповідні поля вікна або зберігатися в осередках аркушах Excel. В останньому випадку замість чисел та дат потрібно вводити посилання на ці комірки. Також функцію можна ввести рядок формул або область на аркуші вручну без виклику вікна аргументів. При цьому слід дотримуватися наступного синтаксису:

ДОХІД(Дата_сог;Дата_вступ_в_силу;Ставка;Ціна;Погашення»Частота;[Базис])

БС

Головним завданням функції БС є визначення майбутньої вартості інвестицій. Її аргументами є процентна ставка за період ( «Ставка»), загальна кількість періодів ( «Кол_пер») та постійна виплата за кожен період ( «Плт»). До необов'язкових аргументів належить наведена вартість ( «Пс») та встановлення терміну виплати на початку або наприкінці періоду ( "Тип"). Оператор має наступний синтаксис:

БС(Ставка;Кол_пер;Плт;[Пс];[Тип])

ВСД

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

ВСД(Значення;[Припущення])

МВСД

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

МВСД (Значення; Ставка_фінансир; Ставка_реінвестір)

ПРПЛТ

Оператор ПРПЛТрозраховує суму процентних платежів за вказаний період. Аргументами функції виступає процентна ставка за період ( «Ставка»); номер періоду ( «Період»), величина якого не може перевищувати загальну кількість періодів; кількість періодів ( «Кол_пер»); наведена вартість ( «Пс»). Крім того, є необов'язковий аргумент – майбутня вартість ( «Бс»). Цю формулу можна застосовувати лише тому випадку, якщо платежі у кожному періоді здійснюються рівними частинами. Синтаксис має таку форму:

ПРПЛТ (Ставка; Період; Кол_пер; Пс; [Бс])

ПЛТ

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

ПЛТ (Ставка; Кол_пер; Пс; [Бс]; [Тип])

ПС

Формула ПСзастосовується до розрахунку наведеної вартості інвестиції. Дана функція зворотна оператору ПЛТ. У неї такі самі аргументи, але тільки замість аргументу наведеної вартості ( «ПС»), яка власне та розраховується, вказується сума періодичного платежу ( «Плт»). Синтаксис відповідно такий:

ПС(Ставка;Кол_пер;Плт;[Бс];[Тип])

ЧПС

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

ЧПС(Ставка;Значення1;Значення2;...)

СТАВКА

Функція СТАВКАрозраховує ставку відсотків за ануїтетом. Аргументами цього оператора є кількість періодів ( «Кол_пер»), величина регулярної виплати ( «Плт») та сума платежу ( «Пс»). Крім того, є додаткові необов'язкові аргументи: майбутня вартість ( «Бс») та вказівку на початку або в кінці періоду буде здійснюватися платіж ( "Тип"). Синтаксис набуває такого вигляду:

СТАВКА(Кол_пер;Плт;Пс[Бс];[Тип])

ЕФЕКТ

Оператор ЕФЕКТведе розрахунок фактичної (чи ефективної) відсоткової ставки. Ця функція має лише два аргументи: кількість періодів на рік, для яких застосовується нарахування відсотків, а також номінальна ставка. Синтаксис її виглядає так:

ЕФЕКТ (Ном_ставка; Кол_пер)

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