Курсова робота на тему:

«Моделювання бізнес-ситуації: інвестування запропонованих до реалізації проектів і автоматизація маркетингових досліджень і фінансово-економічних розрахунків в бізнес-плануванні»

Вступ

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

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

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

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

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

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

1. Середовище, що історично склалося, і тенденції зміни ринку.

2. Інформація про можливі зміни у конкурентів.

3. Основні цілі бізнесу (чи плануєте вивести на ринок новий продукт абож скоротити витрати, скажімо, на 5%).

Структура роботи

1. Вхідні дані.

2. Складання плану доходів і витрат підприємства на перший рік діяльності поквартально.

3. Розробка кредитного плану підприємства і побудова кредитного контурапроекту.

4. Розробка плану надходжень і виплат поквартально.

5. Складання балансового плану на кінець першого року діяльності підприємства.

6. Отримання підсумкового зведення за джерелами і використанням засобів на кінець першого року діяльності підприємства.

7. Визначення точки беззбитковості (аналітично і графічно).

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

9. Побудова звіту по різних фінансових сценаріях для аналізу чутливості.

10. Зміст пояснювальної записки.

1. Вхідні дані

Вхідні дані, приведені в таблиці 1, є необхідними компонентами для дослідження аналізу ділових пропозицій, пов'язаних:

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

· потенціалом зростання продукції;

· придбанням нового устаткування;

· додатковим штатом для роботи на новому устаткуванні;

· аналізом можливого стану «зовнішнього середовища» (рівнем цін на виробничі ресурси і готову продукцію).

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

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

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

Таблиця 1.Вхідні дані

Таблиця 2.Відсоток від доходу по статям.

2. Складання плану доходів і витрат підприємства на перший рік діяльності поквартально

План доходів і витрат на перший рік діяльності поквартально представлений в таблиці3. Дані в рядку 1 визначаються маркетинговими дослідженнями і приведені в таблиці 1, 2; в рядку 2- розрахунком витрат виробництва нової продукції і її реалізації.

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

рядок 3 = рядок 1 - рядок 2.

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

рядок 5 = рядок 3 - рядок 4.

Податок на прибуток 4% і чистий прибуток 6% в сумі складають залишок прибутку підприємства до сплати податку: рядок 5 = рядок 6 + рядок 7.

Чистий прибуток визначається після сплати податку різницею між залишком прибутку до сплати податку і сумою податку: рядок 7 = рядок 5 - рядок 6.

Таблиця 3.План доходів і витрат на перший рік діяльності підприємства поквартально в тис. грн.

3. Розробка кредитного плану підприємства і побудова кредитного контуру проекту

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

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

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

Таблиця 4.Складання кредитного плану фірми. Вхідні дані для складання кредитного плану

Таблиця 5. План погашення кредиту.

Для розробки кредитного плану підприємства в оточенні електронних таблиць ЕХСЕ1. застосовують фінансові функції для аналізу інвестицій: ПЗ, ППЛАТ, ОСНПЛАТ, ПЛПРОЦ, ОБЩДОХОД, ОБЩПЛАТ, які мають наступні аргументи:

6удущее_значение - майбутній об'єм вкладень в кінці терміну (0, якщо аргумент опущений);

число_периодов - термін погашення кредиту;

нач_период - номер першого періоду, що бере участь в обчисленнях (періоди виплат нумеруються починаючи з 1);

кон_период - номер останнього періоду, що бере участь в обчисленнях;

выплата - постійні періодичні виплати;

период - номер конкретної періодичної виплати;

текущее_значение - поточна вартість суми кредиту (інвестиції);

ставка - банківська процентна ставка;

тип - число, що позначає, коли повинна виконуватись виплата:

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

=ПЗ(ставка; число_периодов; виплата; будущее_значение; тип).

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

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

=ППЛАТ(ставка; число_периодов; текущее_значение; будущее_значение; тип).

Функція ПЛПРОЦ визначає платежі по відсотках за заданий період при постійних періодичних виплатах і постійній процентній ставці. Ця функція має наступний синтаксис:

=ПЛПРОЦ(ставка; період; число_периодов; текущее_значение; будущее_значение; тип).

Функція ОСНПЛАТ нараховує основні платежі за заданий період при постійних періодичних виплатах і постійній процентній ставці. Якщо обчислити платежі по відсотках (ПЛПРОЦ) і основні платежі (ОСНПЛАТ) за один і той же період, то сума цих результатів дасть загальний розмір виплати (ППЛАТ). Функція ОСНПЛАТ має наступний синтаксис:

=ОСНПЛАТ(ставка; період; число_периодов; текущее_значение; будущее_значе-ние;тип).

Функція ОБЩДОХОД розраховує основні виплати по позиці між двома періодами. Якщо ця функція недоступна, слід встановити надбудову «Пакет аналізу», а потім підключити її за допомогою команди «Надбудов» меню «Сервіс». Функція ОБЩДОХОД має наступний синтаксис:

=ОБЩДОХОД(ставка; число_периодов; текущее_значение; нач_период;кон_ період;тип).

При використанні функції ОБЩДОХОД переконаєтеся, що Ви послідовні у виборі одиниць виміру аргументів «Ставка і число_периодов». Якщо по чотирирічній позиці при ставці 12 відсотків річних робляться щомісячні виплати, то потрібно використовувати значення 12%/12 для завдання аргументу «ставка» і значення 4*12 - для завдання аргументу «число_периодов». Якщо по тій же позиці робляться щорічні виплати, то потрібно використовувати значення 12% для завдання аргументу «ставка» і значення 4 - для завдання аргументу «число_периодов».

Якщо ставка < 0, число_периодов < 0 або текущее_значение < 0, то функція ОБЩДОХОД повертає значення помилки #ЧИСЛО!.

Якщо нач_период < 1, кон_период < 1 або нач_период > кон_период, то функція ОБЩДОХОД повертає значення помилки #ЧИСЛО!.

Якщо «тип» є будь-яким числом, окрім 0 і 1,то функція ОБЩДОХОД повертає значення помилки #ЧИСЛО!.

Функція ОБЩПЛАТ обчислює накопичений дохід по позиці між двома періодами виплат і має наступний синтаксис:

=ОБЩПЛАТ(ставка; число_периодов; текущее_значение; нач_период;кон_ період;тип).

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

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

На другому етапі складання кредитного плану з використанням графічних засобів ЕХСЕL побудувати кредитний контур (див. мал. 1), що відображає залежність погашення заборгованості за вказаний період часу.

4. Розробка плану надходжень і виплат поквартально

План надходжень і виплат поквартально, тис. грн., приведений в таблиці 6. У перший рядок таблиці 6 заносяться дані з першого рядка таблиці 3 таким чином: 60% - в поточному кварталі, 40% - в наступному.

Рядок 2а заповнюється за даними, приведеними в таблиці 1.

Статті в рядках 26, 2в, 2г, 2е, 2л, 2м оплачуються на 80% в поточному кварталі, 20%-, що залишилися, в наступному. При покупці нового устаткування вводяться податкові пільги, які передбачають сплату податку з прибутку в наступному кварталі, після придбання устаткування. Стаття «зарплата» в рядку 2д визначається сумою статей в рядках 2а і 4в таблиці 3.

Рядок 2д = рядок 2а табл. 3 + рядок 4в табл. 3.

Рядки 2ж, 2і заповнюються за даними рядків 4д і 4ж таблиці 3.

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

Стаття «виплата боргу і відсотки» в рядку 2к складає величину платежу за кредит і заповнюється за даними таблиці 4. У першу колонку рядка 4 заноситься сума банківського кредиту з таблиці 1, яка разом зі вступами від продажів повинна покрити витрати. Величину інвестиції можна коректувати. Залишок грошових коштів на кінець кварталу розраховується сумою між надходженнями від продажу і залишком на початок кварталу мінус сума всіх платежів за поточний квартал і не може бути негативним числом на кінець першого року діяльності підприємства: Рядок 3 = рядок 4 + рядок 1 - рядок 2.

В цьому випадку приріст грошової готівки визначається різницею між залишками грошової маси на кінець і початок кварталу і не може бути негативним числом на кінець першого року діяльності підприємства: Рядок 5 - рядок 3 - рядок 4.

План надходжень і виплат поквартально, тис. грн. Таблиця 6.

Мал. 1. Кредитний контур.

5. Складання балансового плану на кінець першого року діяльності підприємства

Балансовий план на кінець першого року діяльності підприємства приведений в таблиці 7.

Стаття «Грошова готівка» (рядок 2) заповнюється з таблиці 6 (5 рядок, 4 колонка).

Стаття «Рахунки до отримання» (рядок 3) заповнюється з таблиці 6 (1 рядок 5 колонка).

Стаття «Устаткування» (рядок 6) заповнюється з таблиці 6 (рядок 2а, 1 колонка).

Стаття «Амортизація» (рядок 7) заповнюється з таблиці 3 (рядок 4з, 5 колонка «Разом»).

Стаття «Рахунки до оплати» (рядок 8) визначається сумою рядків 2б, 2в, 2г, 2е, 2з, 2л, 2м з таблиці 6, колонка 5 - «Залишок».

Стаття «Довгострокові зобов'язання» (рядок 9) розраховується таким чином:

Рядок 9 = рядок 4, колонка 1, таблиця 6 (сума рядка 2к, таблиці 6 і рядки 4е, колонка «Разом», таблиця 3).

Стаття «Нерозподілений прибуток» (рядок 11) заповнюється з таблиці 3 (рядок 7, колонка «Разом»).

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

Стаття «Неліквідні активи» визначається різницею статей «Устаткування» і «Амортизація» (рядок 6 - рядок 7).

Таблиця 7.Балансовий план на кінець першого року діяльності, тис. грн.

6. Отримання підсумкового зведення за джерелами і використанням засобів на кінець першого року діяльності підприємства

Зведення за джерелами і використанням засобів на кінець першого року діяльності підприємства приведене в таблицю 8. Стаття «Позика під нерухомість» містить дані з таблиці 6, рядок 4,1 колонка.

Чистий прибуток переносять з таблиці 3, рядок 7, колонка 5 «Разом».

Стаття «Амортизація» заповнюється поданим таблиці 3, рядок 4з, колонка 5 «Разом».

Засоби, витрачені на покупку устаткування для нової технологічної лінії, переносять з таблиці 6, рядок 2а, колонка 1.

Приріст запасів визначається сумою статей «Запаси готової продукції і незавершене виробництво» і «Запаси матеріалів» за даними таблиці 7, сума рядків 4 і 5.

Стаття «Погашення позики» (рядок 8) розраховується за даними таблиці 6 з різницею сум рядка 2к (колонка 5, таблиця 6) і рядки 4е (колонка 5 «Разом», таблиця 3).

В результаті чистий приріст оборотного капіталу (рядок 9) за перший рік діяльності визначається різницею грошових коштів, що поступили на рахунок підприємства з різних джерел, і сумою використаних коштів (таблиця 8):

Рядок 9 = рядок 1 – рядок 5.

Таблиця 8. Зведення по джерелам і використанню засобів на кінець першого року, тис. грн.

7. Визначення точки беззбитковості (аналітично і графічно)

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

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

Сукупні доходи = Сукупним витратам.

Сукупні фіксовані витрати (СФВ) - це не що інше, як постійні витрати на виробництво продукції. СФВ = 399 тис. грн. (4 рядок, колонка 5 - «Разом», таблиця 3).

Питомі змінні витрати (ПЗВ) обчислюють з розрахунку на один виріб по формулі:

Собівартість реалізованої продукції / Кількість продукції, ПЗВ = 1,33 тис. грн. (2 рядок, колонка 5 - «Разом», таблиця 3) ділити на 1000 виробів.

Тоді сукупні витрати розраховують по формулі: СВ = СФВ + ПЗВ * К (кількість виробів);

СВ = 399+1,33*1000=1729 тис. грн.

Ціна виробу (Ц) - це доходи від продажів з розрахунку на одиницю продукції:

Ц = 1330/1000=1,33 тис. грн. (1 рядок, колонка 5 - «Разом», таблиця 3) ділити на 1000 виробів.

Сукупні доходи (СД) визначають добутком продажної ціни (Ц) на кількість виробів (К) по формулі: СД = Ц*К.

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

· в одиницях продукції;

· у грошовому вираженні;

· з врахуванням очікуваного розміру прибутку.

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

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

Точка беззбитковості (ТБ) в кількості одиниць продукції = Сукупні фіксовані витрати/(Продажна ціна одиниці продукції - Змінні витрати на одиницю продукції);

ТБ = СФВ/(Ц-ПЗВ).

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

Сукупні фіксовані витрати складають 399тис. грн. (4 рядок, колонка 5 «Разом», таблиця 3). Сукупні доходи (1 рядок, колонка 5 «Разом», таблиця 3) визначаються добутком ціни виробу на кількість виробів. Тоді продажна ціна одного виробу -1,33 тис. грн. Змінні витрати на одиницю продукції складають число 0,798 (2 рядок, колонка 5 «Разом», таблиця 3), ділене на кількість виробів. Можна обчислити точку беззбитковості в одиницях продукції по такій формулі:

Точка беззбитковості(кількість одиниць продукції) = 399/(1,33-0,798).

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

Розглянемо це відношення під іншим кутом. Нехай, ви знаєте, що ваші сукупні фіксовані витрати збільшаться на 40тис. грн. за рік -с 399 до 439 тис. грн. Ви не хочете змінювати продажну ціну виробу і змінні витрати на одиницю продукції. Скільки ж одиниць продукції ви повинні продати для підтримки рівноваги? Для цього перетворимо формулу для визначення точки беззбитковості в одиницях продукції:

Сукупні фіксовані витрати = Точка беззбитковості (одиниці продукції) * (Продажна ціна - Змінні витрати на одиницю продукції);

СФВ=ТБ*(Ц-ПЗВ).

Для швидкого визначення нової точки беззбитковості в одиницях продукції скористайтеся засобом Ехсеl «Підбір параметра». На Мал. 2 показаний зразок оформлення робочого аркуша.

У комірці А2 міститься наступна формула:

= D2*(В2-С2).

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

Виділите комірку А2, потім виконаєте команду Сервіс/Подбор параметра. Адреса комірки А2 з'явиться в полі Встановити у комірці.

2. У полі Значення введіть число 439. Це нові сукупні фіксовані витрати.

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

4.Натискуйте на кнопку ОК.

Мал. 2. Використання засобу «Підбір параметра» для визначення нової точки беззбитковості.

Тепер засіб «Підбір параметра» змінюватиме значення у комірці D2 до тих пір, поки значення у комірці А2 не стане рівним 439. У нашому випадку результат дорівнює 825 - це кількість виробів, яка вам необхідно продати для покриття сукупних фіксованих витрат.

На практиці для визначення точки беззбитковості часто застосовують наступну формулу:

Кількість виробів * (Ціна одиниці продукції - Питомі змінні витрати) - Постійні витрати: ТБ = К* (Ц - ПЗВ) - СФВ.

Потім ви можете використовувати засіб «Підбір параметра», аби встановити значення формули в нуль для визначення точки беззбитковості (кількість одиниць продукції).

Використовуйте засіб «Підбір параметра» для визначення нової точки беззбитковості в наступних випадках:

1. Валові постійні витрати збільшилися на 30%.

2. Валові постійні витрати зменшилися на 20%.

3. Ціна виробу збільшилася на 25%.

4. Ціна виробу зменшилася на 5%.

5. Результати обчислень представити графічно (мал. 3). Для цього необхідно побудувати діаграму для визначення точки беззбитковості при зміні постійних витрат і нанести на неї нові точки беззбитковості для п.1 і 2.

6.Побудувати діаграму для визначення точки беззбитковості при зміні ціни виробу і нанести на неї нові точки беззбитковості для пунктів 3 і 4 (мал. 4).

Визначення точки беззбитковості проекту при збільшенні ціни виробу на 50%

Мал. 3. Визначення точки беззбитковості проекту

Визначення точки беззбитковості при зміні постійних витрат.

а)

Визначення точки беззбитковості при зміні ціни продукції.

Мал. 4. Визначення беззбитковості проекту.

а)ТБ2 - при збільшенні постійних витрат на 30%;

ТБЗ - при зменшенні постійних витрат на 20%;

б)ТБ2 - при збільшенні ціни виробу на 25%;

ТБЗ - при зменшенні ціни виробу на 5%.

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

Для того, щоб відповідати новим умовам розробки бізнес-ситуації, вам необхідно застосовувати метод, що дозволяє одночасно змінювати всі регульовані витрати. Для цього використовується засіб Ехсе1 «Підбір параметра». За допомогою цього засобу процес знаходження конкретного значення параметра набагато прискорюється в порівнянні з методом проб і помилок. Проте засіб «Підбір параметра» має обмеження - для варіювання доступне лише одна змінна комірка. Крім того, вона не може містити формулу, а повинна обов'язково включати значення.

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

Аби надбудова «Пошук рішення» стала доступною, виконаєте наступні дії:

1. Виконаєте команду Сервіс/Надстройки. На екрані з'явиться діалогове вікно Надбудови.

2. Встановите прапорець опції Пошук рішення і натискуйте на кнопку ОК. Якщо ця надбудова вже активізована, натискуйте на кнопку «Відміну».

3. Завантаження налаштування «Пошук рішення» займе трохи часу. Після закінчення завантаження в меню Сервіс з'явиться нова команда Пошук рішення.

Отже, скористаємося засобом «Пошук вирішення» для визначення точки беззбитковості в одиницях продукції, якщо виробничі витрати, включаючи сировину і матеріали, збільшилися на 30%, а собівартість продукції виросла на 20%. Для цього підготуємо вихідні дані, приведені на мал. 5.

У комірціD4 занесена формула для визначення точки беззбитковості:

=Е4 * (В4 - С4) - А4.

Виділите коміркуD4, що містить показник точки беззбитковості. Після цього виконаєте команду Сервіс/Пошук рішення і введіть необхідні дані в діалогове вікно «Пошук рішення» таким чином (мал. 6, 7):

1.Встановите перемикач Значення і введіть значення 0 в текстове поле

(праворуч від нього).

2. Натискуйте кнопку в полі Змінюючи комірку і виділите на робочому аркуші комірки В4: С4, які містять ціну виробу і питомі змінні витрати, і, утримуючи натиснутою клавішу <СТРL>, виділите несуміжнукоміркуЕ4, що містить кількість виробів в точці беззбитковості.

3. Натискуйте кнопку Додати. У діалоговому вікні Додавання обмежень (мал.6) натискуйте кнопку в полі Ссилка і виділите комірку В4 в робочому аркуші. Цякомірка містить ціну виробу.

4.Натискуйте на кнопку для розкриття списку Обмеження і виберіть оператора =.

5. У полі (справа) введіть значення 1,596 (ціна виробу, збільшена на 20%).

6. Натискуйте на кнопку Додати.

7. Виконаєте дії, описані в пп. 4- 7, для комірок С4, Е4, аби ввести обмеження для питомих змінних витрат і кількості виробів, яка має бути позитивним.

8. Закінчивши заповнення комірки Е4в якості обмежувача, натискуйте на кнопку ОК. Ви повернетеся в діалогове вікно Пошук рішення (мал. 7).

9.Натискуйте на кнопку Виконати.

В результаті цих дій надбудова «Пошук рішення» починає підбирати значення, вказані як змінні комірки, аби набути значення 0 для точки беззбитковості. Оскільки ви вказали як мінімальний допустимого значення для комірки Е4 значення 0, надбудова «Пошук рішення» не стане розглядати негативні значення у комірці Е4.

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

Надбудова «Пошук рішення» може змінювати певні комірки для досягнення необхідного результату. З цієї причини зручніше зберігати різні рішення у вигляді сценаріїв. Для цього натискуйте на кнопку Зберегти сценарій діалогового вікна Результати пошуку рішення. При цьому на екрані з'явиться діалогове вікно Збереження сценаріїв. У текстовому полі Ім'я сценарію наберіть ім'я сценарію. Для того, щоб повернутися в діалогове вікно Результати пошуку рішення, натискуйте на кнопку ОК. Встановите перемикач Відновити вихідні значення в діалоговому вікні Результати пошуку рішення і натискуйте на кнопку ОК для відновлення вихідних значень параметрів робочого аркуша.

Мал. 5. Вхідні дані для застосування «Пошуку рішень»

Мал. 6. Обмеження для ціни, збільшеної на 20%.

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

Мал. 7. Використання надбудови «Пошук рішення» для оптимізації кількості виробів в точці беззбитковості.

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

1. Ціна виробу збільшилася на 20%, сукупні фіксовані витрати зросли на 25%, кількість виробів, що випускаються, - величина позитивна.

2. Ціна виробу збільшилася на 20%, змінні витрати зросли на 30%, кількість виробів, що випускаються, - величина позитивна.

3. Ціна виробу збільшилася на 20%, питомі змінні витрати зросли на 30%, сукупні фіксовані витрати - на 25%, кількість виробів (шукана величина), що випускаються, не може бути негативним значенням.

4. Ціна виробу збільшилася на 50%, питомі змінні витрати, постійні витрати і кількість виробів, що випускаються, при оптимізації рішення повинні залишатися позитивними.

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

9. Побудова звіту по різних фінансових сценаріях для аналізу чутливості

Побудуйте п'ять окремих сценаріїв, що відображають різні пропозиції. Найбільш ефективний метод порівняння сценаріїв - підсумовування результатів даних сценаріїв в одному робочому аркуші. Це можна здійснити, виконавши команду Сервіс/Сценарії. У діалоговому вікні, що розкрилося, Диспетчер сценаріїв натискуйте на кнопку Звіт. На екрані відображуватиметься діалогове вікно Звіт за сценарієм (мал. 8).У діалоговому вікні Звіт за сценарієм вводяться необхідні для звіту вічка результатів, адреси яких встановлені за умовчанням. Якщо ви хочете ввести більшу кількість результатів, то їх можна додати уручну. Потім натискуйте на кнопку ОК, і Ехсе1 створить в активній робочій книзі новий робочий аркуш, що містить звіт про сценарії, доступні для активного робочого аркуша (мал. 9).

Мал. 8. Сумування адреси результатів сценаріїв робочого листа.

Мал. 9. Звіт по 5 сценаріях, що використовуються для аналізу бізнес-ситуації.

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

1. Активізуйте робочий аркуш, що містить звіт про сценарії.

2. Виконаєте команду Сервіс/Аналіз даних. Якщо в меню Сервіс відсутній команда Аналіз даних, перейдіть до п. 3. Інакше пропустите його і перейдіть до п. 4.

3. Виконаєте команду Сервіс/Надстройки. У діалоговому вікні Надбудови, що розкрилося, активізуйте в списку надбудов опцію Пакет аналізу. Натискуйте на кнопку ОК.

4. У діалоговому вікні Пакет аналізу виберіть із списку Інструменти аналізу інструмент Описова статистика, а потім натискуйте кнопку ОК.

5. На екрані відображуватиметься діалогове вікно Описова статистика (мал. 10).

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

7. Встановите перемикач Групування в положення по рядках.

8. Встановите прапорці опцій Підсумкова статистика і Рівень надійності. Натискуйте на кнопку ОК.

Мал. 10. Інструмент Описова статистика, що дозволяє розширити аналіз чутливості.

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

Таблиця 9. Результати роботи інструменту Описова статистика для п'яти сценаріїв

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

1. У комірку В18 - текст «Середнє + рівень надійності».

2. У комірку С18 - формулу = С4 + С17.

3. Скопіюйте введену формулу в діапазон комірок D18:F18.

4.У комірку В19 введіть текст «Середнє - рівень надійності».

5. У комірку С19 - формулу = С4 - С17.

6. Скопіюйте введену формулу в діапазон комірок D19:F19.

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

Тому з вірогідністю 95% ми можемо стверджувати наступне:

1)сукупні фіксовані витрати лежать в діапазоні від 371,1 до 506,7 тис. грн.;

2)ціна виробу вагається в межах від 1,38 до 1,9 тис. грн.;

3)змінні витрати на одиницю продукції знаходяться в діапазоні від 0,69 до 1,4 тис. грн.;

4)кількість виробів в точці беззбитковості при розглянутих вище різних вихідних припущеннях вагається від 589 до 858 одиниць продукції.

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

1 0 . З міст пояснювальної записки

1. Вміст розділів практикуму.

2. Вступ. Бізнес-планування, розробка планів фінансової діяльності підприємства. Опис розробки бізнес-ситуації і моделі Ехсеl для її реалізації.

3.Постановка завдання (вхідні дані для розробки фінансового планупідприємства).

4.Створення наступних макросів для заповнення таблиць.

4.1. План доходів і витрат на перший рік діяльності поквартально.

4.2. План погашення кредиту.

4.3. План надходжень і виплат поквартально.

4.4. Балансовий план на кінець першого року діяльності підприємства.

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

5.Метод визначення точки беззбитковості (у одиницях продукції).

5.1. Використання засобу «Підбір параметра» для визначення і побудови точки беззбитковості при зміні постійних витрат.

5.2.Вживання засобу «Підбір параметра» для визначення і побудови точки беззбитковості при зміні ціни продукції.

5.3.Використання засобу «Підбір параметра» для визначення і побудови точки беззбитковостіпри збільшенні ціни виробу на 50%.

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

7. Дослідження бізнес-ситуації. Побудова звіту по різних фінансових сценаріях для аналізу чутливості.

8. Роздрук вхідних і результуючих таблиць і графіків.

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

10. Використана література.

Список рекомендованої літератури

1. Беспалов В.М. Інформатика для економістів. Київ, ЦУЛ, 2003р.

2. Білик В.М., Костирко В.С. Інформаційні технології та системи: Навч. посібник.– К.: Центр навчальної літератури, 2006.– 232 с.

3. Вартанян В. М., Воляк Е. А. Финансово-экономические расчеты с использованием Excel. Практическое руководство.- Харьков: Центр «Консульт», 2008.- 192 с.

4. Войтюшенко Н.М., Остапець А.І. Інформатика і комп’ютерна техніка: Навч. посібник. – К.: Центр навчальної літератури, 2006.– 568 с.

5. Гарнаев А. Самоучитель VBA.-http://www.proklondike.com / contentview.php?content=496. - 2008.

6. Глинський Я. М. Iнформатика: 10-11 класи. Навчальний посiбник.-Ч 1. Алгоритмiзацiя i програмування. 6-те видання.- Львiв, 2006.- 256с.

7. Глинський Я.М. Практикум з інформатики: Навч. Посібник. - Львів: "Підприємство Доеол" 1999.

8. Економічна інформатика / Григорків В.С., Маханець Л.Л., Білоскурський Р.Р., Якутова О.Ю., Верстяк А.В.: Навчальний посібник. – Чернівці: Книги – ХХІ, 2008. – 460 с.

9. Засадна Х.О., Кравчук Г.Т., Шевчук Т.В. Операційні системи та файлові менеджери: Навчальний посібник. – Львів: ЛБІ НБУ, 2006. – 228 с.

10. Інформатика і комп’ютерна техніка. Комп’ютерні технології. Посібник за редакцією д.е.н., проф. Пушкаря О.І. – К.: Видавничий центр „Академія”, 2001. – 695 с.

11. Інформатика і комп'ютерна техніка: навчальний посібник для студ. вищ. навч. закладів / Л. М. Дибкова. - Київ : Академвидав, 2005. - 416 с. - (Альма-матер).

12. КастроЭ. Создание Web-страниц с помощью HTML. – М.: НТ Пресс, 2005. – 144 с.

13. Коноплянко З.Д,, Чаплига В.М., Чаплига М.В. Багатозначні структури та кодування систем економічної кібернетики. -Львів: ЛБІ НБУ, 2004. -314 с.

14. Макарова Н., Матвеев Л.В. и др. Информатика. Учебник.-3-е перераб. изд.,-М.: Финансы и статистика,2000.-768 с.:ил.

15. Макарова Н.В., Кулышев Е.И. и др., Информатика. Практикум по технологии работы на компьютере., -3-е перераб. изд.,-М.:Финансы и статистика,2000.-256 с.:ил

16. Пикуза В., Гаращенко А. Экономические и финансовые расчеты в Excel. Самоучитель. – СПб.: Питер; К.: Издательская группа BHV, 2006. – 397 с.

17. Руденко В.Д., Марчук А.М. Патланжоглу М.А. Курс інформатики/ під ред. Мадзіганова.

18. Семчук А.Р., Юрченко І.В. Економічна інформатика: Навчальний посібник. –5-е вид. –Чернівці: МВІЦ”Місто”, 2006. –424с.

19. Семчук А.Р., Юрченко І.В. Економічна інформатика: теорія, навчальний практикум.– Чернівці: МВІЦ "Місто", 2005.– 416 с.

20. Симонович С.В. и др. Информатика. Базовый курс. – СПб: Издательство „Питер”, 2000. – 640 с.

21. Солоницын Ю.А. Windows XP. Только практика. – СПб.: Питер, 2006. – 240 с.

22. Страхарчук А.Я., Страхарчук В.П. Методичні рекомендації щодо виконання практичних робіт із дисципліни „Інформатика і комп’ютерна техніка” (тема „Автоматизація роботи Excel із використанням мови програмування Visual Basic for Application”). Львів: ЛБІ НБУ, 2003. – 40 с.

23. Хан Х. Эффективный самоучитель работы в Интернет. – К.: Издательство „ДиаСофт”, 2001. – 448 с.

24. Эйткен П. Интенсивный курс программирования в Excel за выходные.: Пер. с англ. – М.: Издательский дом „Вильямс”, 2004. – 432 с.