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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Статистика	СФВ	Ціна продукції	ПЗВ	ТБ у одиницях продукції
Середнє	438,900	1,642	1,027	724
Стандартна помилка	24,434	0,094	0,121	48
Медіана	399,001	1,596	1,037	714
Мода	399,000	1,596	1,037	#Н/Д
Стандартне відхилення	54,635	0,211	0,272	108
Дисперсія вибірки	2985,001	0,044	0,074	11725
Експрес	-3,333	3,247	1,569	1
Асиметричність	0,609	1,500	1,228	1
Інтервал	99,750	0,569	0,665	268
Мінімум	399,000	1,426	0,798	625
Максимум	498,750	1,995	1,463	893
Сума	2194,501	8,209	5,134	3618
Рахунок	5,000	5,000	5,000	5
Рівень надійності (95,0%)	67,839	0,262	0,337	134
Середнє + рів. надійності	506,739	1,903	1,364	857,995
Середнє - рів. надійності	371,062	1,380	0,689	589,099

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

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

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

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 с.