Excel фінанси. Корисні формули Excel для контролю фінансів. Навчальна програма курсу

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

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

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

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

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

ДОХІД

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

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

БС

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

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

ВСД

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

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

МВСД

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

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

ПРПЛТ

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

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

ПЛТ

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

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

ПС

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

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

ЧПС

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

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

СТАВКА

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

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

ЕФЕКТ

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

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

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

Фінансовий аналіз у Excel з прикладом

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

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

Приклад фінансового аналізу підприємства у Excel

Завдання - вивчення результатів фінансової діяльності та стану підприємства. Цілі:

• оцінити ринкову вартість фірми;
• виявити шляхи ефективного розвитку;
• проаналізувати платоспроможність, кредитоспроможність.

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

Аналіз фінансового стану підприємства має на увазі

• аналіз балансу та звіту про прибутки та збитки;
• аналіз ліквідності балансу;
• аналіз платоспроможності, фінансової стабільності підприємства;
• аналіз ділової активності, стан активів.

Розглянемо прийоми аналізу балансового звіту Excel.

Спочатку складаємо баланс (наприклад – схематично, не використовуючи всі дані з форми 1).

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

За допомогою найпростіших формул ми відобразили динаміку за статтями балансу. Так само можна порівнювати баланси різних підприємств.

Які результати дає аналітичний баланс:

1. Валюта балансу наприкінці звітного періоду стала більшою порівняно з початковим періодом.
2. Необоротні активи прирощуються із вищими темпами, ніж оборотні.
3. Власний капітал підприємства більше, ніж позиковий. Причому темпи зростання власного перевищують динаміку позикового.
4. Кредиторська та дебіторська заборгованість прирощуються приблизно в однаковому темпі.

Статистичний аналіз даних у Excel

Задля реалізації статистичних методів у програмі Excel передбачено величезний набір коштів. Частина – вбудовані функції. Спеціалізовані способи обробки даних доступні у надбудові «Пакет аналізу».

Розглянемо найпопулярніші статистичні функції.

У прикладі більшість даних вище середнього, т.к. асиметрія більша за «0».

ЕКСЦЕС порівнює максимум експериментального з максимумом нормального розподілу.

У прикладі максимум розподілу експериментальних даних вище за нормальний розподіл.

Розглянемо, як із метою статистики застосовується надбудова «Пакет аналізу».

Завдання: Згенерувати 400 випадкових чисел із нормальним розподілом. Оформити повний перелік статистичних характеристик та гістограму.

Після натискання ОК з'являються основні статистичні параметри по даному рядку.

Завантажити приклад фінансового аналізу в Excel

Це третій кінцевий результат роботи у цьому прикладі.

Завантажити приклади фінансових моделей в Excel

Колекція готових ефективних рішень для аналізу. Графіки, формули та функції для аналізів та складних фінансових розрахунків.

Фінансові моделі

Звіт руху коштів.
Завантажити приклад детального звіту щодо руху коштів на підприємстві з фінансовим аналізом та графіками.

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

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

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

Приклади розв'язання задачі з розрахунку ТБ.
Приклади завдань, що дозволяють змоделювати обчислення точки беззбитковості за певних умов та розрахунків.

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

Розрахунок цін за калькуляції.
Розрахунок цін за калькуляції собівартості з нормами.

Калькуляція собівартості та накладні витрати.
Розрахунок відсотків накладних витрат за калькуляції собівартості продукції.

Динамічні діаграми завантажити приклад.
Приклади побудови динамічних діаграм та графіків. Створення діаграми Ганта.

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

Excel для фінансистів. Excel допоможе обрати компанії найвигідніший внесок у банку

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

Калькуляція собівартості продукції зразок в Excel безкоштовно завантажити.
Шаблон для поетапного розрахунку планової собівартості продукції, що виробляється.

Клієнтська база в шаблон Excel завантажити безкоштовно.
Шаблон клієнтської бази даних для ведення обліку за сегментами.

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

Завантажити особистий бюджет шаблон на місяць в Excel.
Шаблон для складання бюджету та обліку особистих фінансових видатків.

Завантажити табель обліку робочого часу бланк Т-13.
Стандартний бланк документа табеля обліку робочого дня форми Т-13.

Завантажити старий табель обліку робочого часу бланк Т-12.
Старий формат табеля обліку робочого дня форми Т-12.

Фінансовий аналіз підприємства у MS Excel

Добірка фінансового аналізу у таблицях excel підприємства від різних авторів.

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

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

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

Книга "Excel для фінансового директора"

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

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

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

Доброго часу доби, шановний читачу!

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

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

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

Функція ЯКЩО вExcel

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

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

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

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

Детально про те як працює ця функція ви можете.

Функція ВПР вExcel

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

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

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

Детально про те як працює описувана функція в Excel ви можете.

Функція СУМІСЛИ вExcel

Представляю вашій увазі третю дуже потрібну функцію, функція СУМІСЛІ, як бачите, складається з 2 частин і тобто логічно ви бачите, що формула підсумовуватиме певне значення за певним критерієм. Це особливо актуально, коли потрібно вибрати та підсумувати з великого діапазону лише певне значення, наприклад, скільки було списано сировини у виробництво всього, якщо вам дали загальне списання по підприємству днями. Вам потрібно просто вказати, що саме вас цікавить і де це взяти, а формула зробить все за вас, ну не все, звичайно, саму формулу ви вже самі писатимете.

Функція СУМЕСЛІ в Excel хороша ще тим, що, спокійно працює з значень, що значно спрощує рутинні обчислення. Але варто пам'ятати, що функція є чутливою до точності написання критеріїв і навіть в один знак не дасть вам правильний результат.

Детальніше про те, як працює СУМІСЛИ в Excel ви можете .

Функція СУМІСЛИМН вExcel

Ну ось тепер перейдемо до ще складнішого, жартую, варіанту, функція СУМЕСЛІМН. Ви вже ознайомилися та знаєте про функціях СУМ, ЯКЩО, СУМІСЛІ, а ось тепер з'єднаємо все це в безлічі, як ви зрозуміли з останніх двох літер функції, і отримаємо потрібну нам функцію. І тепер ви зможете робити вибірку за 127 критеріями, очманіти, я навіть не можу придумати, навіщо мені, скільки критеріїв, хоча для вас це може стати панацеєю.

Великим плюсом того, як працює функція СУМЕСЛІМН в Excel, це робота з символами підстановки, а також з операторами відносин типу «більше», «менше», «рівно». Також не варто забувати, що для зручності роботи з функцією варто використовувати, що дозволить вам зручніше використовувати таку корисну функцію.

В цілому при роботі з великими масивами даних функція СУМЕСЛІМН буде для вас неоціненним помічником.

Детально про те, як працює функція нашого топ списку, ви можете .

Функція СУМПРОВИЗВ вExcel

П'ятою функцією нашого топ-списку стане функція СУММПРОИЗВ, яка є, мабуть, найголовнішою функцією для економіста. Вона дозволяє втілити в собі практично всі попередні можливості, які мають , , , а також проводити свої обчислення в 255 масивах, А це, я вам скажу, ох як багато.

Функція СУММПРОИЗВ в Excel дозволить вам впоратися практично з будь-яким економічним завданням, де фігурують масиви. Підібравши правильні критерії чи умови, за допомогою формул чи іншим способом, будь-які завдання зможуть капітулювати перед легкістю, з якою ця функція їх вирішуватиме.

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

Детально про те, як працює функція СУММПРОИЗВ в Excel ви можете .

А на цьому все в мене! Я дуже сподіваюся, щоСписок найважливіших ТОП-5 функцій для економіста або бухгалтера ми розглянули . Буду дуже вдячний за залишені коментарі, тому що це показник читання та надихає на написання нових статей! Діліться з друзями прочитаним та ставте лайк!

То не біда, якщо за карбованець дають піврубля; а то буде біда, коли за рубль даватиму в морду.
Михайло Салтиков-Щедрін

Або. Щоб скористатися автоматичною установкою, Вам потрібні права адміністратора. Якщо прав адміністратора немає – нічого страшного, ручне встановлення не викличе проблем. Інструкція зі встановлення нижче:

Для тих, хто має права адміністратора

1. Завантажте надбудову
2. Запустіть розархівований файл (Встановлення та видалення надбудови) з папки «Автоматична установка»
3. Готово

Для тих, хто не хоче зайвий раз турбувати сисадміна:)

1. Завантажте надбудову
2. Розархівуйте збережений файл
3. Закрийте всі програми MS Office (Word, Excel, Outlook та ін.), попередньо зберігши відкриті файли
4. Запустіть розархівований файл Excel «Встановлення та видалення надбудови» з папки «Ручне встановлення»
5. Дозвольте запуск макросів під час запуску файлу та клацніть на кнопку «Встановити надбудову Фінансист»
6. Перезавантажте Excel (закрийте та заново відкрийте)
7. Готово

Якщо не вийшло, можна діяти так:

Як оновити надбудову

Автоматичне оновлення. Для тих, хто має права адміністратора на комп'ютері

1. Завантажте надбудову
2. Розархівуйте збережений файл у нову папку
3. Закрийте всі програми MS Office (Word, Excel, Outlook та ін.), попередньо зберігши відкриті файли
4. Запустіть розархівований файл «Встановлення та видалення надбудови» (з папки «Автоматична установка»)
   1. у вікні вибираємо опцію «Видалити надбудову» -> кнопка «Виконати»
   2. запускаємо цей же файл («Встановлення та видалення надбудови») повторно, опція «Додати надбудову» -> кнопка «Виконати»
5. Перезавантажте Excel (закрийте та заново відкрийте)
6. Готово

Ручне оновлення. Для тих, хто не хоче зайвий раз турбувати сисадміна:)

1. Завантажте надбудову
2. Розархівуйте збережений файл
3. Закрийте всі програми MS Office (Word, Excel, Outlook та ін.), попередньо зберігши відкриті файли
4. З папки «Ручне встановлення» запустіть розархівований файл Excel «Встановлення та видалення надбудови»
5. Дозвольте запуск макросів під час запуску файлу і клацніть по кнопці «Видалити надбудову Фінансист» (файл видалить стару версію)
6. Клацніть по кнопці «Встановити надбудову Фінансист»
7. Перезавантажте Excel (закрийте та заново відкрийте)
8. Готово

Відповіді на запитання

Що таке надбудова «Фінансист»?Надбудова «Фінансист» - це програма, яка додає до Excel додаткові команди та функції, які будуть корисні у роботі фінансовому директору та іншим співробітникам фінансової служби. У стандартній версії 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» > «Надбудови» > «Перейти», у вікні, що з'явиться, поставити галочку «Пошук рішення» і натиснути «Ок».

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

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