Warning: session_start() [function.session-start]: Cannot send session cookie - headers already sent by (output started at /var/www/nelvin/data/www/ebooktime.net/index.php:6) in /var/www/nelvin/data/www/ebooktime.net/index.php on line 7

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent (output started at /var/www/nelvin/data/www/ebooktime.net/index.php:6) in /var/www/nelvin/data/www/ebooktime.net/index.php on line 7
ЗАВДАННЯ  ДО  ЛАБОРАТОРНИХ   РОБІТ 13   ВИКОРИСТАННЯМ MICROSOFT  EXCEL : Банківський маркетинг підручник : Бібліотека для студентів

ЗАВДАННЯ  ДО  ЛАБОРАТОРНИХ   РОБІТ 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), яку додану до цього прикла-д