ЗАВДАННЯ  ДО  ЛАБОРАТОРНИХ   РОБІТ 13   ВИКОРИСТАННЯМ MICROSOFT  EXCEL


Повернутися на початок книги
1 2 3 4 5 6 7 8 9 10 11 12 13 14 
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 
30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 
45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 
60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 
75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 
90 91 92 93 94 95 96 97 

Загрузка...

ЛАБОРАТОРНА РОБОТА № 1

Тема. Аналіз продажів і маркетингу: використання файлу демографічних даних

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

Файл Mapstats.xls знаходиться в папці

\Program Files\Common Files\Microsoft Shared\Datamap\Data

Яка користь від інформації, що міститься в Mapstats.xls? Вона дозволяє представити дані у визначеному контексті: ви — менеджер по маркетингу комерційного банку, що має представництва в 23 штатах; ви можете визначити 5 регіонів продажів, кожен з яких містить кілька штатів і контролюється регіональним менеджером з маркетингу. Поставлено завдання — проаналізувати результати роботи з регіонів за минулий рік.

1.         Створити нову книгу Excel з ім'ям demograf.xls.

2.         Заповнити осередку Al, Bl, Cl у такий спосіб: А1 — Штат, В1 — Регіон, С1 — Обсяги продажів.

3.         Відкрити файл Mapstats.xls.

 

4.         У файлі Mapstats.xls вибрати робочий лист USA. У відкри-тому робочому листі виділити такі назви штатів: IL, IN, MI, MN, MO, OH, WI, CT, MA, NJ, NY, PA, AZ, CA, OR, WA, CO, OK, TX, AL, FL, KY, LA (всего 23 штата).

5.         Скопіюйте виділені штати в осередки А2:А24 книги demograf.xls.

6.         Закрити файл Mapstats.xls.

7.         Регіональні представництва Швнічного регіону представ-лені штатами — IL, IN, MI, MN, MO, OH, WI; Швнічно-Західного регіону — CT, MA, NJ, NY, PA; Атлантики — AZ, CA, OR, WA;

Південного регіону — CO, OK, ТХ; Південно-Західного регіону — AL, FL, KY, LA. Використовуючи цю інформацію, заповніть осе-редку В2:У24 книги demograf.xls.

8.         Згрупувати штати за регіонами.

9.         Загальні обсяги продажів за минулий рік по кожному штату:

 

IL —   $3 599 010,     IN -     $519 119,        МІ-      $2 747 343,    MN     — $147 770,

МО     - $971 831,     ОН      - $573 379,     WI -    $594 869,       ст-       $1 865 171,

МА      - $2 238 257,  NJ -    $1 402 489,     NY      - $2 587 015,  РА-     $2 425 601,

AZ       - $1 530 112,  СА      - $4 587 410,  OR      - $939 534,     WA     - $2 116 468

СО      - $562 513,     ОК      - $183 722,     ТХ-     $619 361,        AL -    $286 987,

FL -     $1 089 367,     KY      - $1 174 760,  LA -    $1 054 786.               

10.       Встановити необхідний формат даних у кожному стовпчи-ку книги demograf.xls.

11.       Установити в шапці таблиці такі параметри шрифту: тип — Times New Roman, розмір — 12, напівжирний, темно-синій колір.

12.       Затінити діапазон осередків А1:К1 яскраво-блакитним ко-льором, діапазон осередків А2:А24 — ясно-жовтим.

13.       У діапазоні осередків В2:С24 установити параметри шриф-та: тип — Агіаі Суг, розмір — 10, звичайний.

14.       Отцентрувати вміст осередків стовпця «Штат».

15.       Зберегти всі зміни, зроблені на цей момент у книзі demograf.xls.

16.       Зробити аналіз регіональних продажів шляхом створення зведеної таблиці: створити зведену таблицю суми по полю «Про-дажів» для кожного регіону, розмістивши її на існуючому листі в діапазоні осередків E1:F8 (формат діапазону осередків для діа-логового вікна має вид $E$1:$F$8).

17.       Встановити такий формат стовпця «Разом», щоб відобра-жався (не відображався) значок $.

18.       Зберегти книгу demograf.xls.

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

20.       Відновити відображення продажів по всіх регіонах.

21.       За даними зведеної таблиці побудувати зведену діаграму.

22.       Встановити такий тип діаграми: об'ємна кругова розріза-на. Установити як підписи ці значення.

23.       Встановити такі параметри шрифту в підписах даних: тип — Times New Roman, розмір — 12, напівжирний.

24.       У легенді діаграми установити невидиму рамку; шрифт — Times New Roman, напівжирний, 12; розміщення — унизу, посе-редині.

25.       Встановити відображення діаграмою тільки продажі Пів-нічно-Західного і Швденного регіонів.

26.       Відновити відображення всіх регіонів.

27.       Перейти на Листі і зберегти книгу demograf.xls.

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

29.       Зняти деталі Швденно-Західного регіону.

30.       Зберегти зміни в книзі demograf.xls.

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

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

33.       Повернутися до Листаі. Зберегти книгу demograf.xls.

34.       Закрити майстра зведених таблиць.

35.       Загальний підсумок у кожній таблиці виділити напівжир-ним шрифтом і затінити яскраво-жовтим кольором.

36.       Зберегти підсумковий варіант у папці «КАРТИ» і закрити книгу demograf.xls.

КОНТРОЛЬНІ ЗАПИТАННЯ

1.         Оцінити відносний внесок Південного регіону в дохід вашого ба-нку.

2.         Назвати першу й обов'язкову умову для складання зведеної таб-лиці.

3.         Які існують варіанти розміщення зведеної таблиці в робочій кни-зі Excel?

4.         Охарактеризувати принцип роботи «Майстра зведених таб-лиць».

5.         Як зробити активним «Майстра зведених таблиць»?

6.         Призначення використання «Майстра зведених таблиць»?

 

 

646

РЕЗУЛЬТАТИ ВИКОНАННЯ ЛАБОРАТОРНОЇ РОБОТИ:

 

 

ЛАБОРАТОРНА РОБОТА № 2

Тема. Аналіз продажів і маркетингу: аналіз відхилень, використання описової статистики

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

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

Поставлено завдання — перевірити, чи є результати досто-вірними?

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

1.         Визначте, чи є в меню «Сервіс» команда «Аналіз даних», якщо є, то не виконуйте завдання № 2.

2.         Активізувати засіб «Аналіз даних»: установити прапорець «Пакет аналізу» у списку «Список надбудов». У результаті в Me¬mo «Сервіс» з'явиться нова команда — «Аналіз даних», що надає доступ до різних фінансових, статистичних та інженерних функ-цій, а також засобів аналізу.

3.         Відкрийте книгу demograf.xls.

4.         Для проведення аналізу згрупуйте дані книги demograf.xls на новому листі в такий спосіб: обсяги продажів для кожного з п'яти регіонів в окремому стовпці, у першому рядку якого міс-титься назва регіону.

5.         Затінити назви регіонів ясно-зеленим кольором, отцентру-вати, виділити напівжирним. Зберегти зміни в книзі demograf.xls.

6.         Зробити активним діалогове вікно «Однофакторний диспер-сійний аналіз».

7.         Встановите в діалоговому вікні такі параметри: як «Вхід-ний інтервал» — діапазон осередків А1:Е9; перемикач «по стов-пцях» у групі «Групування»; прапорець «Мітки в першому ряд-ку»; значення «Альфа» — за замовчуванням 0,05 (фіксоване статистичне значення дисперсійного аналізу); параметри виснов-ку — «Новий робочий лист».

8.         Зробити активним робочий лист і3 вихідною інформацією. Перша частина результатів (осередку А:Е9) — це звіт описової статистики для кожного регіону. Друга частина (осередку A13:G17) — дані, що стосуються до питання про значимість ре-зультатів продажів, що спостерігаються. Стрижневе значення — це значення Р (значення імовірності).

9.         Необхідно визначити, яка імовірність того, що і згодом будуть спостерігатися розходження такі ж, що й у фактичних показниках за поточний рік (тобто розходження, що мають на увазі лише випадковість)? Існує ряд випадкових факторів, що впливають на обсяги продажів: тимчасові коливання довіри споживачів, плинність кадрів банку, модифікація самої проду-кції, технологічні зміни й інші події. Щоб розібратися з усім цим, вам доведеться відповісти на таке запитання: «чи є розходження в результатах продажів у різних регіонах сис-тематичними чи зовсім випадковими?» Дисперсійний аналіз дозволяє оцінити імовірність появи розбіжностей між фактичними і передбачуваними значеннями за умови, що розходження, які спостерігаються, викликані випадковими подіями.

10.       За показником рівня імовірності Р визначити процент-

не співвідношення випадковості розходження в обсягах про-

дажів і процентне співвідношення того, що розходження

в обсягах продажів невипадкові і викликані конкретними об-

ставинами.

Наприклад, якгцо значення імовірності Р = 0,09, то процент-не співвідношення випадкової розбіжності в обсягах прода-жів no регіонах — 9 %, а процентне співвідношення того, гцо вони викликані конкретними невипадковими обставинами — 91 %. Звідси висновок — розходження в обсягах продажів мають невипадковий характер, тому варто розробити мар-кетингові заходи гцодо реорганізації регіональної збутової мережі.

11.       Виділити отримане значення імовірності жирним черво-ним кольором і зберегти книгу demograf.xls.

12.       Повернутися до робочого листа з інформацією про обсяги продажів для кожного з п'яти регіонів.

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

14.       Для Швнічного і Північно-Західного регіонів розрахувати середнє значення обсягів продажів; для Південно-Західного — ма-ксимальне і мінімальне значення; для Атлантики і Півдня загальну суму від продажів.

15.       У діапазоні осередків А10:ЛЗ створити таблицю, що міс-тить значення найбільшого і найменшого обсягу продажів по кож-ному регіону, використовуючи інструмент аналізу даних «Описо-вої статистики».

16.       Встановити необхідні межі стовпців даних. Зберегти кни-гу demograf.xls.

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

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

19.       У діапазоні осередків A16:D18 створити таблицю, що міс-тить значення найбільшого обсягу продажів для Південного і Пів-денно-Західного регіонів. Встановити необхідні границі стовпців, затінити таблицю світло-блакитним кольором. Зберегти книгу demograf.xls.

20.       Для таблиці найбільших продажів Швденного і Пів-денно-Західного регіонів (прив'язати до осередку D16) вста-вити примітку такого змісту: «Головний менеджер регіонів — Климова Ирина» так, щоб зміст містився на одному рядку.

21.       Встановити розмір примітки по ширині і довжині змісту.

22.       Встановити параметр примітки, що фіксував би його роз-ташування.

23.       Встановити параметр примітки, що забезпечував би йо-го появу у випадку вказівки курсором миші на осередок із при-міткою.

24.       Повернути фіксоване розташування примітки.  Зберегти книгу demograf.xls.

25.       Зняти в меню «Сервіс» команду «Аналіз даних».

26.       Зберегти підсумковий варіант книги demograf.xls у папці «КАРТИ» на диску С: і закрити книгу demograf.xls.

КОНТРОЛЬНІ ЗАПИТАННЯ

1.         Назвати деякі засоби «Пакета аналізу» Excel.

2.         Як і для чого використовується засіб «Аналіз даних»?

3.         Як зробити засіб «Аналіз даних» активним? Як забрати його зі списку команд меню «Сервіс»?

4.         Яке повинне бути значення імовірності Р, щоб зробити висновок про вплив випадкових факторів на розбіжності фактичних і пе-редбачуваних обсягів продажів банку?

5.         Яким значком відбивається в таблиці наявність примітки?

651

РЕЗУЛЬТАТИ ВИКОНАННЯ ЛАБОРАТОРНОЇ РОБОТИ:

 

 

 

ЛАБОРАТОРНА РОБОТА № 3

Тема. Обчислення межі ноі ашення постійних витрат і доходів

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

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

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

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

На основі цих показників можна прийняти такі рішення:

>          як збільшити рентабельність;

>          яким чином управляти збутом продукції;

>          як оптимізувати ресурси для скорочення витрат і збільшен-ня доходів.

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

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

Нехай АКБ «1» пропонує «продукт 1». Собівартість вироб-ництва однієї одиниці «продукту 1» складається з таких ста-тей:

^> $ 5 — матеріал;

^> $ 1 — пакувальні матеріали;

^> $ 0,50 — зарплата (на кожну одиницю продукту).

1. Створити «Книгуі» Microsoft Excel. Стовпець А робочого листа 1 заповнити такою інформацією, з огляду на порядок і зазначений формат рядків:

 

Обсяг продажів (1000 од. продукту по $ 10)

Мінус: витрати на виробництво

Оплата праці (1000 CD no $ 0,50):

Вартість матеріалів (1000 CD no $ 5):

Вартість упакування (1000 CD no $ 1):

Разом перемінні витрати:

Межа погашення витрат:

 

Рядок 1 — напівжирний шрифт, вирівняти по лівому краю

Рядок   3   —  звичайний  шрифт, вирівняти по лівому краю

Рядок   5  —  звичайний  шрифт, вирівняти по правому краю

Рядок  7  —  звичайний  шрифт, вирівняти по правому краю

Рядок  9 — звичайний  шрифт, вирівняти по правому краю

Рядок 11 — напівжирний шрифт, вирівняти по лівому краю

Рядок 13 — напівжирний шрифт, вирівняти по лівому краю

 

2.         У стовпців В и С встановити формат даних грошовий ($ Ан-глійський СІПА, 0 десяткових знаків). Розрахувати, викорис-товуючи панель формул значення доходу від продажів (результат розмістити в осередку С1), витрати на оплату праці (результат ро-змістити в осередку В5), вартість матеріалів (результат розмістити в осередку В7), вартість упакування (результат розмістити в осе-редку В9), загальні перемінні витрати (результат розмістити в осе-редку СП), межа погашення витрат (результат розмістити в осере-ДкуСІЗ).

3.         Осередок A18 заповнити таким змістом «Перемінні витрати на одиницю виробу» (яскраво-синій напівжирний курсив на яск-раво-жовтому тлі).

4.         В осередку C18 розрахувати значення середніх перемінних витрат, установивши формат осередку грошовий ($ Англійський США, 2 десяткових знаки).

5.         Зберегти «Книгуі» під ім'ям pred_pog_izd.xls у каталозі

С:\Мои документьі\Ехсе1_Рі1е8.

6.         Нехай тепер оплата праці — значення умовно-перемінне

чи середне, тобто зарплата складає $ 0,50 за кожну продану

одиницю продукту при виробництві до 1000 одиниць на день,

$ 0,60 за кожну одиницю при виробництві від 1000 до 2000

одинць на день і т.п. Необхідно врахувати розмір середніх ви-

трат при розрахунку межі погашення постійних витрат.

7. Заповнити стовпець А1 робочого листа 2 «Книгиі» таким змістом, з огляду на порядок і зазначений формат рядків:

 

Кількість проданих одиниць продукту:

Обсяг продажів ($ 10 за 1 од. продукту)

Мінус: перемінні виробничі витрати

Оплата праці (напівзмінні):

Вартість матеріалів (змінні):

Вартість упакування (змінні):

Разом перемінні витрати:

Межа погашення витрат:

 

Рядок 1 — звичайний шрифт, ви-рівняти по правому краю

Рядок  3  —  напівжирний  шрифт, вирівняти по лівому краю

Рядок 5 — звичайний шрифт, ви-рівняти по лівому краю

Рядок 7 — звичайний шрифт, вирів-няти по правому краю

Рядок 9 — звичайний шрифт, вирі-вняти по правому краю

Рядок 11 — напівжирний шрифт, вирівняти по правому краю

Рядок 13 — напівжирний шрифт, вирівняти по лівому краю

Рядок 15 — напівжирний шрифт, вирівняти по лівому краю

 

8.         Кількість проданих одиниць продукту — 4510 оди-ниць. Ввести це значення в осередок В1, привласнивши йому ім'я «Обьемпродаж».

9.         Для стовпця С встановити формат даних грошовий ($ Анг-лійський США, 2 десяткових знаки). В осередку СЗ розрахувати обсяг продажів у грошовому вираженні.

 

10.       Осередок D1 заповнити таким змістом «Розрахунок ви-трат на оплату праці», накреслення тексту — напівжирний курсив.

11.       Задати три пойменованих діапазони: осередок В1 — Обьемпродаж, діапазон осередків D3:D8 — ПроизвПрод, діапа-зон осередків ЕЗ:Е8 — Себестоимпрод.

12.       У діапазоні осередків D3:D8 задати варіанти виробництва від 0 до 5000 одиниць. У діапазоні осередків ЕЗ:Е8 задати відпо-відну собівартість по оплаті праці ($0,50; $0,60 і т.д.), задавши формат діапазону осередків грошовий ($ Англійський СПІА, 2 десяткових знаки).

13.       Осередок D2 заповнити змістом «ПроизвПрод», осередок Е2 — «Себестоимпрод», осередок D10 — «Стоимматериалов:», осередок D12 — «Стоимупаковки:», осередок F1 — «Оплата праці».

14.       Осередку E10 привласнити ім'я «СтоимМатериалов» і зна-чення $ 5,00. Осередку E12 привласнити ім'я «СтоимУпаковки» і значення $ 1,00.

15.       Для діапазону осередків F3:F8 задати формат даних гро-шовий ($ Англійський СІІІА, 2 десяткових знаки) і логічну фор-мулу ЕСЛИ такого вигляду:

=ЕСЛИ(ОбьемПродаж>ПроизвПрод;МШ(1000; (Обьемпродаж-произвпрод))*Себестоимпрод;0)

У цій формулі застосовується функція МІН, оскільки вироб-ництво повинне складати не більше, наприклад, 1000 одиниць (при більшій кількості — новий тариф заробітної плати). Розра-хувати оплату праці при кожному обсязі випуску.

Примітка. У діапазоні осередків F3:F8 повинні бути отримані зна-чення: 0-1000 — $ 500,00; 1000-2000 — $ 600,00; 2000-3000 — $ 700,00; 3000-4000 — $ 800,00; 4000-5000 — $ 459,00. Останнє зна-чення — $ 0,00.

16.       Діапазонам осередків В7:В11 і С13:С15 привласнити фор-мат даних грошовий ($ Англійський США, 2 десяткових знаки).

17.       В осередку В7, використовуючи панель формул, розра-хувати витрати на оплату праці при обсязі виробництва 4510 одиниць продукту.

18.       В осередку В9, використовуючи панель формул і при-власнені раніше імена відповідних осередків, розрахувати вартість матеріалів при обсязі виробництва 4510 одиниць про-дукту.

19.       В осередку В11, використовуючи панель формул і при-власнені раніше імена відповідних осередків, розрахувати вартість упакування при обсязі виробництва 4510 одиниць продукту.

20.       В осередку C13, використовуючи панель формул, розра-хувати підсумкові перемінні витрати при обсязі виробництва 4510 одиниць продукту.

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

22.       Зберегти результати розрахунків робочого листа 2 «Кни-гиі».

23.       Діапазон осередків F1:F8 затінити блідо-жовтим кольо-ром, шрифт — яскраво-червоний напівжирний, лінії сітки в діа-

пазоні присутні. Такі самі параметри встановити в осередках СЗ, С13,С15,В7,В9, ВП.

24.       Зберегти зміни в оформленні робочого листа 2 «Книгиі».

25.       Вміст осередку F1 скопіювати в осередок G1. Для діапа-зону осередків G3:G8 задати формат даних грошовий ($ Англій-ський США, 2 десяткових знаки) і логічну формулу ЕСЛИ для розрахунку оплати праці при кожному обсязі випуску, викорис-товуючи не поіменні, а звичайні діапазони необхідних осередків. Зробити розрахунок.

26.       Результати розрахунку в осередках Gl :G8 оформити в та-кий спосіб: шрифт напівжирний яскраво-синій на блідо-зеленому тлі, фіксована примітка «У діапазоні осередків G3:G8 зроблений розрахунок аналогічний діапазону F3:F8 з тією лише різницею, що у формулі використовувалися не поіменні, а звичайні діапа-зони осередків». Формат примітки: напівжирний курсив, розмір шрифту — 10, розташування — по центру.

27.       Зберегти зміни в робочому листі 2 «Книгиі».

28.       Якими стануть межа погашення витрат, загальні перемін-ні витрати, вартість упакування, вартість матеріалів, загальні ви-трати на оплату праці, якщо обсяги випуску збільшити до 6510 одиниць виробів, до 10000 одиниць виробів?

29.       Закрити файл pred_pog_izd.xls, не зберігаючи змін. Завер-шити роботу з Microsoft Excel.

Повинні бути отримані такі значення: оплата праці — $ 3 059,00; вартість матеріалів — $ 22 550,00; вартість упаку-вання — $ 4 510,00; загальні перемінні витрати — $ 30 119,00; межа погашення витрат — $ 14 981,00.

КОНТРОЛЬНІ ЗАПИТАННЯ

1.         Чим відрізняється використання поімених діапазонів осередків від використання звичайних діапазонів у панелі формул?

2.         Як розрахувати межу погашення постійних витрат підприємства?

3.         Як перевірити, чи використовувалася формула для одержання значення осередку?

4.         Пояснити, як розрахувати вартість упакування і вартість мате-ріалів, використовуючи панель формул і привласнені раніше імена відповідних осередків, імена яких осередків використову-валися?

5.         Пояснити, чому осередок F8 за результатами виконання завдання № 15 містить нульове значення?

 

РЕЗУЛЬТАТИ ВИКОНАННЯ ЛАБОРАТОРНОЇ РОБОТИ:

ЛАБОРАТОРНА РОБОТА № 4

Тема. Аналіз використання межі ноі ашення постійних витрат і доходів

При аналізі межі погашення постійних витрат у лабораторній роботі № 4 враховувалися валові змінні витрати і валовий дохід. Тепер варто проаналізувати, яку суму на одиницю продукції (чи яку частку в продажах) складають ці значення. Аналіз показників на одиницю продукції і визначення складових різниці між собі-вартістю і продажною ціною допоможе краще зрозуміти такі мо-менти:

& внесок кожного продукту в загальний дохід;

& джерело найбільшої частини змінних витрат;

& відношення між розмірами змінних витрат.

Відомо, що вартість матеріалу для виробництва 1 од. про-дукту — $ 5, вартість упакування для 1 од. продукту — $ 1.

1.         Відкрити файл pred_pog_izd.xls. Зробити поточним лист 2.

2.         Діапазони осередків А1:С15, F1:F8 листа 2 скопіювати в Ta¬xi самі діапазони листа 3. Установити потрібну ширину стовпців листа 3.

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

4.         Перевірити вміст осередку В1 — чи привласнене йому ім'я «Обьемпродаж»? Якщо ні, очистити вміст цього осередку і вста-вити в неї існуюче ім'я «Обьемпродаж». При введенні цього іме-ні з панелі формул, в осередок В1 буде введене поточне значення обсягів продажів — 4510 одиниць.

5.         Заповнити осередок D2 таким змістом «Середні значення (за 1 од. продукту)», задавши необхідну ширину стовп-ця. Формат тексту — Times New Roman, напівжирний курсив, 12 кегль, яскраво-блакитне заливання осередку, видимі межі осе-редку.

6.         Розрахувати відповідні середні значення в стовпці D по відповідним рядках (результат: середній дохід від продажів — $ 10,00, оплата праці на одиницю продукту — $ 0,68, вартість матеріалів на одиницю продукту — $ 5,00, вартість упакування на одиницю продукту — $ 1,00, загальні витрати на одиницю

продукту — $ 6,68, межа погашення постійних витрат — $ 3,32). Отцентрувати результати в стовпці D. Зберегти результа-ти розрахунків.

7.         Заповнити осередок Е2 таким змістом « % погашення пос-тійних витрат», задавши необхідну ширину стовпця. Формат тек-сту — Times New Roman, напівжирний курсив, 12 межі, яскраво-блакитне заливання осередку, видимі границі осередку.

8.         У діапазоні осередків ЕЗ:Е15 встановити формат даних — процентний, число десяткових знаків — 2.

9.         Процент погашення постійних витрат показує, яку час-тку в загальному доході на одиницю продукції (у ціні) складають усі види змінних виробничих витрат на одиницю продукції. У ві-дповідних рядках стовпця Е (крім рядка no підсумкових змінних витратах) розрахувати % погашення постійних витрат (резуль-тат: оплата праці — 6,78 %, вартість матеріалів — 50,00 %, вартість упакування — 10,00 %, межа погашення постійних ви-трат — 33,22 %). Отцентрувати результати в стовпці Е. Зберегти результати розрахунків.

10.       Як вплине збільшення обсягів випуску на 1000 одиниць

на межу погашення витрат, підсумкові змінні витрати, обсяг про-

дажів у грошовому вираженні, загальні витрати на оплату праці,

загальні витрати на матеріали, загальні витрати на упакування?

Якими стануть значення перерахованих величин? Результат офор-

мити в діапазоні осередків А18:У24 листа 3 у вигляді такої таб-

лиці:

 

Кількість проданих одиниць продукту:      5510

Межа погашення витрат     

Підсумкові змінні витрати  

Обсяг продажів у грошовому вираженні  

Загальні витрати на оплату праці  

Загальні витрати на матеріали       

Загальні витрати на упакування    

11. Встановити такий формат створеної таблиці: тип шрифту — Times New Roman; накреслення — звичайне; розмір — 12; діапа-зон А18:В18 — затінений ясно-зеленим кольором, шрифт напів-жирний чорний, вирівнювання по центру; діапазон А19:А24 —

темно-синій шрифт, вирівнювання по лівому краю; діапазон В19:В24 — темно-синій шрифт, вирівнювання по правому краю; діапазон А18:В24 — межі зовнішні і внутрішні, колір меж — чер-воний, тип ліній — подвійні. Зберегти результати.

12. Визначити суму загального виторгу від реалізації, витрати на оплату праці, матеріали, упакування, загальні змінні витрати і межу погашення витрат для наступних варіантів обсягів випус-ку: 6510, 7510, 8510, 9510, 10510. Результат оформити в діапазоні осередків А1:Н7 на листі 4 у вигляді поданої нижче таблиці. Зна-чення обсягів продажів виділити напівжирним шрифтом, вирів-няти по центру. В діапазоні осередків В2:Н7 встановити формат даних грошовий ($ Англійський США, 2 десяткових знаки).

 

            4510    5510    6510    7510    8510    9510    10510

Продаж ($)                                                                          

Оплата праці ($)                                                                             

Вартість матеріалів ($)                                                                                

Вартість упакування ($)                                                                             

Загальні змінні витрати ($)                                                                        

Межа погашення витрат ($)                                                                                 

13.       За даними таблиці листа 4 побудувати прогнозну динамі-ку розрахованих значень у міру збільшення обсягів реалізації у вигляді циліндричної діаграми виду «Гистограмма зі стовпцями у вигляді циліндрів».

14.       Розмістити зону побудови діаграми на зоні діапазону осе-редків А9:Н25. Задати такий формат осей значень: тип шрифту — Агіаі, накреслення — напівжирний курсив, розмір — 8. Встановити ясно-зелене заливання стінок і підстави діаграми. Параметри легенди: рамка невидима; шрифт — Times New Roman, накреслення — звичайне, розмір — 10, колір — темно-синій; ро-зміщення — праворуч, посередине.

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

16.       Задати заголовок діаграми такого змісту «Прогнозна ди-наміка витрат виробництва продукту». Формат заголовка: шрифт

661— Times New Roman, накреслення — напівжирний курсив, роз-мір — 10.

17.       У ряду даних «Межа погашення витрат» встановити такий формат: заливання — яскраво-червона, фігура — конус. Зберегти результати.

18.       Зробити поточним значенням обсяг продажів 4510 оди-ниць.

19.       Нехай тепер АКБ «1» хоче збільшити межу погашення постійних витрат і доходів з 33,22 % до 35 %. Визначити можливі шляхи досягнення цієї мети і розрахувати необхідні цільові пока-зники (умовно змінні значення не розглядати).

20.       Результат оформити в діапазоні осередків А1:С7 листа 5 у виді поданого нижче оперативного звіту. Формат таблиці: шрифт — Times New Roman, розмір — 12; діапазони осередків А1:С1, АЗ:СЗ — затінені ясно-зеленим кольором; шрифт осередків А1, A3 напівжирний курсив червоного кольору; шрифт діапазону осередків В1:С1 напівжирний курсив чорного кольору; вирівнювання в осере-дку А2 і діапазоні А4А7 по лівому краю; вирівнювання в діапазо-нах В2:С2 і В4:С7 по правому краю; вирівнювання в осередках А1, A3, В1, С1 по центру; формат діапазону В2:С2 процентний, число десяткових знаків — 2; формат діапазону В4:С6 грошовий ($ Анг-лійський СІПА), число десяткових знаків — 2; формат діапазону В7:С7 числовий, кількість десяткових знаків — 0; межі таблиці — зовнішні і внутрішні.

 

Ціль: зниження межі погашення витрат    Поточний показник  Цільовий показник

Межа погашення витрат      33,22 %          35,00 %

Шляхи досягнення              

1.                    

2.                    

3.                    

4.                    

21.       Зберегти результати.

22.       Закрити файл pred_pog_izd.xls. Завершити роботу з Micro¬soft Excel.

КОНТРОЛЬНІ ЗАПИТАННЯ

1.         Чому в ході розрахунку оплати праці в міру збільшення об-сягів випуску, починаючи з 6510 одиниць, розрахункові ви-трати на оплату праці не змінювалися (залишалися рівними $ 4500)?

2.         Що забезпечує взаємозв’язок показників межі, що розраховують-ся, погашення витрат, середніх значень, відсотка погашення ви-трат з вихідними?

3.         Чи є збільшення межі погашення постійних витрат виробництва позитивною для банку обставиною?

4.         Який економічний зміст має збільшення межі погашення постій-них витрат?

5.         Які шляхи підвищення (зниження) межі погашення постійних ви-трат?

РЕЗУЛЬТАТИ ВИКОНАННЯ ЛАБОРАТОРНОЇ РОБОТИ:

 

 

 

ЛАБОРАТОРНА РОБОТА № 5

Тема. Оперативний фінансовий звіт. Аналіз даних оперативного фінансового звіту, їх графічна інтерпретація

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

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

1. Створити нову «Книгу» у Microsoft Excel. Ha листі 1 створити оперативний фінансовий звіт, зміст якого наведений в таблиці.

 

Операшивний фінансовий звіш                            

            Разом  Заод.   % погашення пост. вит.

Продажі                                

Мінус:                        

Матеріали                             

Оплата праці                        

Перемінні накладні витрати                                  

Межа погашення постійних витрат                                  

                                  

                                  

Обсяг продажів                               

Оформлення звіту: осередок А1 — шрифт — Агіаі Суг, роз-мір — 14, накреслення — напівжирний курсив; діапазон осеред-ків A1:D1 і осередок B11 затінити темно-блакитним кольором; діапазон осередків B2:D2 і осередок All затінити яскраво-блакитним кольором; шрифт діапазону осередків A2:D11 — Arial Суг, розмір — 10, накреслення — діапазон осередків B2:D2 — напівжирний курсив, осередку A3, А8, А11, В11 — напівжирний,

діапазон осередків B8:D8 — напівжирний, накреслення інших осередків — звичайне; вирівнювання в осередках А1 — по центру, A3, А4, А8, B11 — по лівому краю, діапазон осередків А5:А7 — по правому краю, діапазон осередків B2:D2 — no центру, діапазон осередків B3:D11 — no правому краю; у діапа-зоні осередків A1:D11 установити межі формату — яскраво-синій колір, тип ліній зовнішніх границь — подвійний тонкі, тип ліній внутрішніх межі — одна тонка лінія. Зберегти створений звіт у файлі, створивши за необхідності зазначену папку: С: \ Мої документи \ Files_Excel \ oper_fm_otchet.xls.

2.         У відповідних осередках задати необхідні для розрахунку формули та встановити необхідний формат числових значень (для грошових одиниць — $ Англійський СІПА, 2 десяткових знаки).

3.         Нехай продажна ціна однієї одиниці продукту — $ 20, се-редні витрати на матеріали — $ 4, середні витрати на оплату пра-ці— $ 9, середні витрати на накладні витрати — $ 3. Поточний обсяг продажів — 100 од. За зазначеними вихідними даними знайти розрахункові значення оперативного фінансового звіту. Зберегти отримані результати.

4.         Визначити, яка залежність між продажною ціною і витрата-ми на матеріали, продажною ціною і оплатою праці, продажною ціною і накладними витратами. Встановлені залежності оформити графічно так, щоб на осях графіка значення виводилися в заданому грошовому форматі. Формат графічного оформлення: зміна ціни від $ 10 до $ 55 із кроком $ 5; діапазон осередків А14:К17 відвести під таблицю, заповнюючи таблицю, використовувати маркер авто-заповнення; діапазон осередків A18:D33 відвести під ділянку діаг-рами; формат графіка — осі категорій — шрифт — Агіаі Суг, роз-мір — 8, накреслення — напівжирне, вирівнювання 0 градусів, цінову вісь підписати — «Ціна» з тими самими параметрами шри-фту, розмістити назву осі наприкінці ряду значень; формат зоні побудови — рамка невидима, заливання світло-блакитна; формат ліній сітки — лінії мінімальної товщини, червоного кольору, дріб-ний пунктир; формат лінії «Витрати на оплату праці» — тип лінії — звичайна, товщина — середня, колір — чорний, маркер — кру-жечок, біле тло, чорний контур, розмір — 3 пт; формат лінії «Ви-трати на матеріали» — тип лінії — звичайна, товщина — середня, колір — червоний, маркер — кружечок, синє тло, без контуру, ро-змір — 3 пт; формат лінії «Накладні витрати» — тип лінії — зви-чайна, товщина — середня, колір — жовтий, маркер — кружечок, зелене тло, без контуру, розмір — 3 пт; формат легенди — рамка невидима, шрифт — Агіаі Суг, розмір — 8, накреслення — зви-

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

5.         Нехай тепер межу погашення постійних витрат необхідно збільшити до 40,19 %. Визначити відпускну ціну продукту, що забезпечує таке збільшення. Який грошовий еквівалент збільше-ної загальної і середньої межі погашення постійних витрам?

6.         Яка ціна забезпечить ріст загальної межі погашення постій-них витрат до $ 1980? Який при цьому відсотки погашення пос-тійних витрат?

7.         Зробити поточної початкову ціну.

8.         Визначити, яка зміна середніх витрат на матеріали збіль-шить межа погашення постійних витрат до 33,30 %. Який грошо-вий еквівалент збільшеної загальної і середньої межі погашення постійних витрат?

9.         Визначити, яка зміна середніх витрат на матеріали змен-шить загальна межа погашення постійних витрат до $ 247. Який при цьому відсоток погашення постійних витрат?

 

10.       Зробити поточним початкове значення середніх витрат на матеріали.

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

12.       Як уплине на загальну межу погашення постійних витрат збільшення середніх витрат на оплату праці до $ 12?

13.       Зробити поточним початкове значення середніх витрат на оплату праці.

14.       Визначити середнє значення накладних витрат, що забез-печить зростання загальної межі погашення постійних витрат до 29,90 %.

15.       До чого призведе збіпьшення середніх накладних витрат до $ 7?

16.       Зробити поточним початкове значення середніх накладних витрат.

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

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

19.       Зробити поточним початкове значення обсягу продажів.

20.       Нехай тепер початкове значення середніх витрат на оплату праці — $ 9. 3 огляду на залежність між середніми витратами на оплату праці і межею погашення постійних витрат, побудувати

на листі 2 графік такої залежності, збільшуючи середні витрати на оплату праці на $ 0,5 до $ 14. На листі 2 діапазон осередків A1:L2 відвести під таблицю, дапазон осередків A4:G19 під зону діаграми. Формат графічного оформлення аналогічний формату завдання 4, відмінності:

& назва горизонтальної осі категорій — «Середні витрати на оплату праці», розмістити під віссю;

& вирівнювання значень горизонтальної осі 0 градусів;

& значення відповідають розподілам на горизонтальній осі;

& формат лінії «Межа погашення постійних витрат» — тип лінії — звичайна, товщина — середня, колір — червоний, маркер — кружечок, синє тло, без контуру, розмір — 3 пт.

21.       Визначити інтервал неприпустимих середніх витрат на оплату праці. Результат оформити у вигляді підписів непуипус-тимих значень на лінії ряду даних про межу погашення постій-них витрат. Неприпустимі значення відобразити в такий спосіб: шрифт — Агіаі Суг, розмір — 8, накреслення — напівжирне, ко-лір — червоний.

22.       Зберегти отримані результати.

23.       Встановити вихідні значення середніх накладних витрат, витрат на матеріали, на оплату праці.

24.       На листі 3 побудувати графіки залежності між ціною і ва-ловим доходом, ціною і межею погашення постійних витрат, ці-ною і витратами на матеріали. Діапазон осередків А1:К4 відвести під таблицю значень, діапазон осередків А6:Н20 під область діа-грами. Встановлені залежності оформити графічно так, щоб на осях графіка значення виводилися в заданому грошовому форматі. Використовувати формат графічного оформлення з завдання 4.

25.       Зберегти отримані результати.

26.       Закрити файл oper_fm_otchet.xls. Завершити роботу з Microsoft Excel.

КОНТРОЛЬНІ ЗАПИТАННЯ

1.         Для чого призначений оперативний фінансовий звіт?

2.         Яким чином забезпечується зв'язок даних оперативного фінансо-вого звіту?

3.         Що означає зростання показника межі погашення постійних ви-трат?

4.         Які обов'язкові показники повиннені містити оперативний фі-нансовий звіт?

5.         Від яких значень залежить межа погашення постійних витрат, від яких не залежить?

 

 

РЕЗУЛЬТАТИ ВИКОНАННЯ ЛАБОРАТОРНОЇ РОБОТИ:

 

 

ЛАБОРАТОРНА РОБОТА № 6

Тема. Елементи інвестиційної діяльності комерційного банку

Практичні завдання

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

Стрижневим поняттям є альтернативна вартість або ціна можливості. {opportunity cost) Це ставка доходу, який повинна приносити інвестиція для того, щоб бути реальною, вигідною альтернативою іншим аналогічним вкладенням59. Як показано нижче, при розрахунку чистої приведеної вартості альтернативна вартість інвестиції використовується як коефіцієнт (ставка) дис-контування. При розрахунку внутрішньої ставки дохідності роз-рахована норма прибутку порівнюється з альтернативною вартіс-тю капіталовкладення і таким чином оцінюється його реальна вартість.

Наступні завдання слід виконувати на «Лист 1» Microsoft Excel.

Приведена вартість і чиста приведена вартість

Обидва поняття — приведена вартість, ПС (present value, PV) та чиста приведена вартість, ЧПС (net present value, NPV) — визначають поточну вартість очікуваних у майбутньому гро-шових надходжень.

Як приклад розглянемо оцінку інвестиції комерційного банку, яка забезпечить дохід $ 100 на рік у кінці поточного року та ще чотирьох наступних років. Нехай, це серія з п'яти платежів по $ 100 кожний гарантована.

59 У фінансовій літературі можна зустріти багато синонімів терміну «альтернатие-на вартість», таких як «ставка дисконтування» (discount rate), «процентна ставка» (interest rate) або «вартість капіталу» (cost of capital)

Якби банк платив річний процент у розмірі 10 % при депозиті на п'ять років, то ці 10 % якраз і склали б альтернативну вартість інвестиції — еталонну норму прибутку, з якою ми порівнювали б вигоду від інвестиції.

Розрахуємо вартість інвестиції шляхом дисконтування грошо-вих надходжень від неї з використанням альтернативної вартості як ставки дисконтування:

 

Приведена вартість (ПС) обсягом $ 379,08 є поточною вар-тістю інвестиції.

Нехай, ця інвестиція продається за $400, тоді вона не варта ці-єї ціни, оскільки — за умови альтернативного доходу (облікової ставки) у розмірі 10 % — реальна вартість цього капіталовкла-дення складає лише $ 379,08. Тут доречно ввести поняття чистої приведеної вартості (ЧПС). Нехай облікова ставка для цієї ін-вестиції г, тоді ЧПС розраховується за формулою:

Д   CF,

NPV = CFn + У         —,

°     U(l + r)'

 

де CFt — грошове надходження від інвестиції в момент t; CFo — потік грошових засобів на поточний момент. Отже отримуємо:

Термінологічна примітпка. Термінологія Microsoft Excel гцодо дисконтованих грошових потоків дегцо відрізняється від стандартної фінансової термінології, а саме: скорочення ЧПС (NPV) визначає приведену вартість, а не чисту приве-дену вартість серії грошових надходжень. Для того, гцо роз-рахувати чисту приведену вартість серії грошових надхо-джень у звичайному розумінні фінансової теорії, необхідно спочатку розрахувати приведену вартість майбутніх грошо-вих потоків (функція Excel — ЧІІС або NPV), а потім відняти від цього числа грошовий потік на початковий момент часу (зазвичай, це значення збігається з вартістю активу, гцо розг-лядається).

Внутрішня ставка дохідності і таблиці повернення засобів

Продовжуємо роботу над попереднім прикладом. Нехай, ми дійсно сплатили $ 400 за цю серію грошових потоків. Внутріш-ня ставка дохідності, ВСД (internal rate of return, IRR), визнача-ється як складна ставка дисконтування г, за якою ЧПС = 0:

NPV = CF0 + У]        '— = 0

673

Ця задача вирішується за допомогою функції ВСД (IRR). Слід зауважити, що функція ВСД приймає як аргументи всі грошові потоки цього капіталовкладення, у т. ч. перше (в цьому випадку від'ємне) надходження - $ 400:

 

Значення ВСД є складною нормою прибутку від капіталов-кладення. Для більш детального розуміння доцільно побудувати таку таблицю:

 

У цій таблиці повернення грошових засобів кожне грошове надходження від активу поділяється на дві складові: проценти на суму інвестиції в повернення (погашення) основної суми. Проце-нтна складова на кінець кожного року дорівнює добутку ВСД на суму вкладеного капіталу на початок того самого року. Зверніть увагу, що основна сума на початок останнього року ($ 92,65 у цьому прикладі) дорівнює складової повернення основної суми на кінець того самого року.

674

За допомогою цієї таблиці можна фактично розрахувати внут-рішню ставку дохідності. Розглянемо інвестицію, яка зараз кош-тує $ 1000 та повністю окупається на кінець 5-го року. Нехай ВСД цієї інвестиції орієнтовно 15 %.

 

Зверніть увагу на осередок (В43), яку додану до цього прикла-ду. Якщо процентна ставка в осередку (В32) дійсно є ВСД, то в осередку (В43) повинно стояти число 0. Для розрахунку ВСД слід використати інструмент «Подбор параметра», який містить пункт меню «Сервис»:

 

31 33   Вартість інвєстиції                В         с          D         Е                                            

 

           

            -(1 000,00      

           

           

                        ПодОор параметра              

 

            ВСД?  1             15.00%                                                       Установить в ячейке:

Значение;

Изменяя значение ячейкі-              

 

            !\ =-взі I                      і =В32*В38 1            

            \ІВ$43 3J

34                                                                             

           

           

 

           

           

                       

            I          

           

           

           

 

35        Тлолиця         поверненя грошових ^асобів                      1  Розподіл надходжень      

           

           

 

           

           

           

           

           

            ■    $Б$32|      ІІ

36                               L                    

           

           

           

 

           

                                  

           

           

           

                       

37        Рік       Основна сума на початок    /Сума на кінець /        року   ДОЇІД (150,00і          Повернення               ОК      1          Отмена

 

           

           

           

           

           

           

           

38        1          (1 000,00        (300,00          

            (150,00і           \          

39        2          (550,00і           ;               (200,00       (127,50           (72,50  \          

40        3          (777,50           \             (150,00         (116,53           (33,38  \          

41        4          (744,13           \            (600,00          (111,02           (408,38           \          

42        5          (255,74           \          (000,00            (38,36  (851 ,54          \          

43        6          -(005,83          \                                  \          

44                               1                                                        

 

           

           

                       

           

           

                       

 

45                                                                                                                 

Результат виконання зображений на такому рисунку:

 

            A         в          С         D         О         F          G

31        Вартість інвестиції    -(1 000.00                                                     

32        ВСД?  24,44%                                                         

33                               Л =-взі 1                     і =В32*В38 I             

34                                                                             

 

           

           

                       

            1         

           

 

35        Тлолнця         повепненяфОШОВІ х йасооів                    I   Розподіл надходжень МІЖ Д0Х0Д0М 3 інвестицій г                                            основноі суми      і ПОБЄрНЄННЯМ

ЗВ       /                      

           

 

37        РІЕ      Основна сума на початок    fcyria на кінець /        Р0КУ   ДохіД  Повернення              

зв        1          (1 000,00        (300,00           (244,364         (55,64і \          

39        2          J944.351         ^               (200,00      (230,76           -130,76           \          

40        3          (975,13           \             (150,00         (238,28           -(88,28            \          

41        4          (1 063,41        \           (000,00           (259,86           (340,14           \          

42        5          1723,25           \          (900,00            (176,74           (723,26           \          

43        0          (0,00    \                                  \          

44                               1                                 \          

 

           

           

                       

           

                       

 

45                                                                                                     

Звісно, є можливість спростити собі життя, скориставшись функцієюВСД(//г/г) Ь©:

 

            A         I       в  С         D

40        Прлмий          юзрахунок БСД                    

47                              

4В       Рік       Грошові                     

49        0          -1000             

50        1          300                 

51        2          200                 

52        3          150                 

1                      4          600                 

 

                        5          900                 

 

           

           

           

           

 

                                              

           

           

                       

5Б                               /          

57        всд      24,44%'                     

Неоднозначність рішення для внутрішньої ставки дохідності

Для вирішення наступного завдання спочатку треба засвоїти техніку створення таблиць даних у середовищі Microsoft Excel.

Для виконання наступних завдань перейдіть на «Лист 2» Microsoft Excel.

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

Розглянемо процес створення одно- та двомірних таблиць да-них на листах Microsoft Excel.

Розглянемо інвестиційний проект комерційного банку, по-чатковою вартістю $ 1150 та сім очікуваних щорічних надхо-джень грошових засобів. Вони зростають із коефіцієнтом g протягом 1-7-го років так, що грошове надходження, або потік ІДП), у році номер t дорівнює ffTIt = ДП^Х х (1 + g). Знаючи ставку дисконтування г, отримуємо чисту приведену вартість проекту (ЧПС):

.,„„       дп,     дпАі + g)   дпАі + g)        дпАі + g)

ЧПС - —1150 Н—!!——г-\ —^--\  -!— + ...Н       -          -*—

(l + r/        (1 + г)2       (1 + г)3           (1 + г)7

Внутрішня ставка дохідності (ВСД) і — це ставка дисконту-вання, за якою ЧПС = 0:

11        ДП,       ДПАі + g)    ДПАі + g)         ДПАі + g)

0 = -50Н         ~т-\     —^-Л  TJ— + ...-\      -^—

(l + rj        (l + rf          (l + r)3 (l + r)7

Наведений розрахунок легко виконується в Microsoft Excel. В наступному прикладі перше грошове надходження дорівнює $ 234, темп зростання g = 10 %, ставка дисконтування г = 15 %.

Зверніть увагу на адреси осередків, що містять темп зростан-ня, ставку дисконтування, ЧПС та ВСД. Вони знадобляться у по-дальших розрахунках.

 

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

 

Фактична робота таблиці (без заголовків рядків і стовпців) позначена жирною рамкою. Числа, що стоять безпосередньо під мітками «ЧПС» та «ВСД», отримані за відповідними формулами з попереднього рисунку. Так, якщо осередок В9 містить розраху-нок ЧПС, то осередок під буквами «ЧПС» (C17) містить форму-лу =В9. Аналогічно, якщо осередок B10 містить розрахунок ВСД, то осередок під заголовком «ВСД» (D17) містить формулу =В10.

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

1.         Основний приклад.

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

Тепер виконайте такі дії.

1.         Виділіть зону таблиці, обведену жирною рамкою.

2.         Виберіть у меню «Данньіе» команду «Таблица подстанов-ки». Відкриється діалогове вікно, де слід вказати підстановку значень за стовпцями чи/або за рядками.

 

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

 

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

 

У верхньому лівому куті таблиці знаходиться формула =В9, яка містить посилання на основний приклад.

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

Тепер знов скористаємося командою «Таблица подстанов-ки». На цей раз заповнюємо як поле «Подставлять значения no столбцам» — вказуємо в ньому осередок ВЗ, що містить ставку-дисконтування, так і поле «Подставлять значения no стро-кам» — вказуємо в ньому осередок В2, що містить темп зростан-ня грошових надходжень.

 

 

Таким чином, опановано техніку створення таблиць даних у Microsoft Excel.

Наступні завдання слід продовжувати виконувати на «Лист 1» Microsoft Excel.

Повернемося до основного завдання — знаходження не-однозначного рішення для внутрішньої ставки дисконту-вання.

Іноді для грошових потоків існує кілька значень ВСД. В наступному прикладі можна точно сказати, що послідов-ність грошових надходжень у осередках С68:С73 має дві мож-ливих ВСД, оскільки графік значення ВСД двічі перетинає вісь х.

            A         в          c          D         E          F          G         H

BO                                                                                        

61        НЕОДНОЗНАЧНІСТЬ РІШЕННЯ ДПЯ Ві:Д                     ТДБЛИЦЯ ДАНИХ 

62                                                                                         

63                                                       Ставка дисконтування         ЧПС   

64        Ставка дисконтування         6 00%    /    -Lba+4IIL(Lb4;Lby:L>J)|                       -(3,99 

65        ЧПС                -13,99'            0%       -(20.00           

66                                                                   3%       -(10,51           

67                    Рік       Грошов  ПОТОКИ                           6%       -(3,99 

66                               -1146 00                                 9%       10,24  

69                    1          (100 00                                   12%     (2,69   

70                    2          tioooo                          15%     13,77  

71                    3          (100 00                                   18%     (3,80   

72                    4          (100 00                                   21%     (3,02   

73                    5          -(275 00                                 24%     11,62  

74                                                                   27%     -(0,24 

76                                                                   30%     -12,44

76                                                                   33%     -(4,90 

77                                                                   36%     -(7,53 

70                                                                   39%     -(10,27           

79                                                                                         

ao                                                                                                                 

 

                        Дві ВСД                    

 

81                   

                       

62                   

                       

83                   

                       

84                                                                  

 

           

            (0 -15

У -(10 -(15 -(20         00

0 00

00

00

00 <                %        

           

 

85                   

           

           

           

                       

ВЬ                  

           

            I       I   АГ    \       \       \       \       \      ■f-O       '       I

1і      3%      Щґ   9%      13%     19%     18%     21В4    24%    27%    ЗЙ4    33%    38%    39

~/ ~Т    Г    1   1    ~Т    Т    Г   1    "Т    Т    Г^

-А—h-н—і—і—h-н—і—і—г-н—h-

^_l_LLI           1    _L    ^    L   1    ^    ^    L

1      1      1      1      1      1      1      1      1      1      1      1   

                       

87                   

           

           

           

                       

88                   

           

           

           

                       

89                   

           

           

           

                       

90                   

           

           

           

                       

91                   

           

           

           

                       

92                   

           

           

           

                       

93                   

           

           

           

                       

94                   

           

           

           

                       

                       

           

           

           

                       

96                                                                  

97                                          

98                   

                       

 

                                                                                 

 

99                    'Iі                 '  1                                 

100      Визнпчення двох ВСД                                           

101      Пєрша БСД    8,78%  I           ,                                 

102      Дрїга ВСД      26.65%'                                             

Функція ВСД (IRR) програми Microsoft Excel дозволяє добави-ти аргумент, за допомогою якого можна знайти обидва значення ВСД. Для цього замість запису ВСД (С68:С73) слід записати ВСД (С68:С73, начприближ). Аргумент начприближ — це почат-кове наближення для алгоритму, за яким Microsoft Excel розрахо-вує ВСД. Варіюючи початкове наближення, можна визначити обидва шуканих значення. Це ілюструють осередки B101 та B102.

Зробимо два зауваження стосовно цієї процедури.

1. Аргумент начприближ повинен бути достатньо наближе-ний до ВСД, оскільки рішення не одне. Наприклад, якщо встано-вити початкове значення наближення 0,1 або 0,5, отримаємо ті самі значення ВСД, що першого разу:

 

Виконайте відповідні розрахунки ВСД із вказаними набли-женнями (0,1 та 0,5), поверніть результати попередніх розра-хунків.

2. Щоб визначити порядковий номер і приблизне числове зна-чення ВСД, зручно будувати графік ВСД цієї інвестиції як функ-цію від різних ставок дисконтування (як було зроблено вище). Внутрішні ставки дохідності (ВСД) будуть відповідати точкам, в яких графік перетинає вісь x, а наближені координати точок слід задавати як початкові наближення функції ВСД (на графіку точки перетину з віссю х відповідно 10 % і 27 % — це і є значення наближень, тобто 0,1 та 0,27). Якщо не задавати початкове наближення, Microsoft Excel за замовченням прийме його за 0. Тоді у нашому прикладі вираз ВСД(C68:C73) надасть 8,78 %.

Із технічної точки зору послідовність потоків грошових засо-бів може мати кілька ВСД тільки у тому випадку, якщо наявні хоча б дві зміни знаку. Розглянемо, наприклад, придбання облі-гації з 10 % купоном, номінальною вартістю $ 1000 та восьмиріч-ним терміном сплати. Якщо поточна ринкова ціна облігації скла-дає $ 800, то потік грошових засобів змінює знак лише один раз (з від'ємного у нульовому році на позитивний у роках із першого по восьмий). Для такого випадку існує лише одне значення ВСД:

 

Завдання для самостійного виконання

Завдання для самостійного виконання оформлюються на «Лист 3» Microsoft Excel. Оформлення повинно містить назву та номер завдання, опис умови та результати із відповідними пояс-неннями у вигляді приміток.

Завдання 1. Комерційному банку запропонували придбати ак-тив вартістю $ 600, який надаватиме грошові надходження $ 100 на кінець року протягом наступних десяти років. Якщо ставка дисконтування цього активу складає 8 %, чи варто його придба-ти? Яка ВСД цього активу?

Завдання 2. Комерційному банку запропонували зробити ін-вестицію за таких умов: сума інвестиції складає $ 1000; інвести-ція надає грошове надходження у розмірі $ X на кінець першого року; кожен рік протягом 11 років це надходження зростає на 10 %. Нехай ставка дисконтування складає 15 %. Розрахувати мі-німальне значення X, яке б робило інвестицію (придбання акти-ву) доцільною.

Завдання 3. В наступній схемі грошових надходжень є дві можливі ВСД. Чому?

 

За допомогою засобів Microsoft Excel побудувати графік ЧПС для цього потоку грошових засобів як функції від ставки дискон-тування. За допомогою функції ВСД (IRR) визначити обидва значення внутрішньої ставки дохідності. Чи здійснив би комер-ційний банк капіталовкладення у цей проект, якщо б його альте-рнативна вартість (ціна можливості) складала б 20 %?

Завдання 4. Необхідно розрахувати внутрішню ставку дохід-ності методом послідовних наближень. Вартість інвестиції $ 800, вона надає комерційному банку грошові надходження у розмірі $ 300, $ 200, $ 150, $ 122, $ 133 протягом 1-5 років. Побудувати таблицю повернення грошових засобів, враховуючи ВСД? = 10 %. Вона повинна показати, що розмір 10 % перевищує ВСД (оскільки повернення основної суми на кінець 5-го року менше, ніж основна сума на початок року). Зробіть значення осередку

ВСД? таким, що дорівнює 3 %. Це повинно довести, що це зна-чення менше за шукане ВСД, оскільки повернення основної суми на кінець 5-го року перевищує основну суму на початок року. Варіюючи значення осередку ВСД?, знайдіть внутрішню ставку дохідності цієї інвестиції.

Завдання 5. Виконайте завдання 4 альтернативним способом, а саме: визначення ВСД— норма прибутку, яка робить основну суму інвестиції на початок 6-го року такою, що дорівнює 0. Знайдіть шукане значення за допомогою функції Microsoft Excel «Подбор параметра».

Завдання 6. Маємо проект комерційного банку, поточна вар-тість якого $ 500. За ним очікуються грошові надходження про-тягом наступних п'яти років у розмірі $ 100, $ 100 х (1 + g), $ 100 х (1 + gf> ■■■> $ ЮО х (1 + gf 3a допомогою таблиці даних про-аналізуйте чутливість ЧПС проекту, варіюючи ставку дискон-тування у діапазоні 0, 3, ..., 21 %, а темп зростання грошових на-дходжень — у діапазоні 0, 3, ..., 12 %.