Порядок виконання роботи. Розрахувати фінансові показники використовуючи засобу MS Excel.

Розрахувати фінансові показники використовуючи засобу MS Excel.

Провести прогнозування показників з використанням різних засобів MS Excel.

Вивчити принципи роботи з OLE-технологією гіпертекстовими посиланнями.

Скласти звіт, використовуючи OLE-технології.

1.2. ФункціїMicroSoft Excel

Відповідно до отриманого від викладача завдання підготувати вихідні дані для розрахунку фінансових показників підприємства. На підставі форм звітності підприємства зробити розрахунок фінансових показників за ряд періодів засобами MS Excel.

Подальша робота виконується на ПК у середовищі Microsoft Office.

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

На наступному листі робимо обчислення тенденції зміни фінансового показника за допомогою додавання лінії тренда на діаграму. Для виконання завдання необхідно для розрахованого показника побудувати графік з маркерами, що позначають крапки даних. Після чого клацнути на лінії графіка правою клавішею миші і вибрати опцію «Додати лінію тренда». У вікні, що відкрилося, «Лінії тренда» пропонуються різнітипи ліній тренда. Виберіть, послідовно, кожну з доступних ліній, при цьому задаючи в закладці параметри період прогнозу на 2 періоди вперед, а також вказуючи опції: «показувати рівняння на діаграмі» та «помістити на діаграму величину вірогідності апроксимації R^2».

Створіть лінії тренда для п'яти типів наближеньна різних діаграмах (лінійна, логарифмічна, поліноміальна, статечна, експонентна).

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

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

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

Третій спосіб – прогнозування значень за допомогою функцій: «ПРЕДСКАЗ», «ТЕНДЕНЦИЯ», «РОСТ».

ПРЕДСКАЗ – обчислюєабо пророкує майбутнє значення за існуючим значенням.Значення, що пророкується - це y-значення, що відповідає заданому x-значенню. Відомі значення — це x- і y-значення, а нове значення передвіщається з використанням лінійної регресії. Цю функцію можна використовувати для пророкування майбутніх продажів, потреб в устаткуванні або тенденцій споживання.

ПРЕДСКАЗ(x;відомі_значення_y;відомі_значення_x)

x— це крапка даних, для якої передвіщається значення.

Відомі_значення_y— це залежний масив або інтервал даних.

Відомі_значення_x— це незалежний масив або інтервал даних.

ТЕНДЕНЦИЯ – повертаєзначення відповідно до лінійного тренда. Апроксимує прямою лінією (по методу найменших квадратів) масиви відомі_значення_y і відомі_значення_x. Повертає значення y, відповідно до цієї прямої для заданого масиву нові_значення_x.

ТЕНДЕНЦИЯ(відомі_значення_y;відомі_значення_x;нові_значення_x; конст )

Відомі_значення_y — безліч значень y, що уже відомі для співвідношення y = mx + b.

Відомі_значення_x — необов'язкова безліч значень x, що уже відомі для співвідношення y = mx + b.

Якщо відомі_значення_x опущені, то передбачається, що це масив {1;2;3;...} такого ж розміру, як і відомі_значення_y.

Нові_значення_x — нові значення x, для яких ТЕНДЕНЦІЯ повертає відповідні значення y.

Якщо нові_значення_x опущені, то передбачається, що вони збігаються з відомі_значення_x.

Якщо опущені обидва масиви відомі_значення_x і нові_значення_x, то передбачається, що це масив {1;2;3;...} такого ж розміру, що і відомі_значення_y.

Конст — логічне значення, що вказує, потрібно чи, щоб константа b була дорівнює 0.

Якщо конст має значення НЕПРАВДА, то b покладається рівним 0, і значення m підбираються таким чином, щоб виконувалося співвідношення y = mx.

РОСТ – розраховує прогнозований експонентний ріст на підставі наявних даних. Функція РІСТ повертає значення y для послідовності нових значень x, що задаються за допомогою існуючих x- і y-значень. Функція робочого листа РІСТ може застосовуватися також для для апроксимації існуючих x- і y-значень експонентної кривої.

РОСТ(відомі_значення_y;відомі_значення_x;нові_значення_x;конст )

Опис параметрів функції аналогічно функції «ТЕНДЕНЦИЯ».

Звіт повинний містити:

1. Опис виконання лабораторної роботи

2. Скомпоновані вихідні дані за всі періоди

3. Таблицю з розрахованими коефіцієнтами (як у режимі значень коефіцієнтів, так і в режимі формул)

4. Графіки з вихідними даними і побудованими лініями тренда

5. Прогнозування за допомогою лінійної й експонентної залежностей

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

Розрахунок фінансових показників використовуючи засоби MS Excel за.

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

Коефі-цієнт	Чисельник	Знаменник	Оптималь-не значення	Економічна характеристика
К1	Прибуток, Пр	Активи банку, А	1—4 %	Ефективність роботи активів
К2	Прибуток, Пр	Доходи банку, Д	8—20 %	Кількість отриманого прибутку з кожної гривні доходів
К3	Доходи, Д	Активи банку, А	14—22 %	Дохідність 1 грн активів
К4	Процентні доходи, Дп	Активи банку, А	10—18 %	Рівень ефективності та ризикованості вкладень
К5	Процентна маржа, Дп – Вп	Дохідні активи, Ад	1—3 %	Ефективність роботи дохідних активів
К6	Процентна маржа, Дп – Вп	Активи банку, А	1—4 %	Ефективність управління спредом
К7	Процентні доходи, Дп	Процентні витрати, Вп	110—125 %	Покриття процентних витрат процентними доходами
К8	Непроцентні доходи, Дн	Доходи банку, Д	5—15 %	Ефективність використання внутрішньобанківських резервів
К9	Непроцентні витрати, Вн	Доходи банку, Д	10—25 %	Якість управління затратами банку
К10	Процентні доходи, Дп	Основний капітал банку, Косн	1,2—1,7	Дохідність 1 грн основного капіталу банку
К11	Процентна маржа, Дп – Вп	Основний капітал банку, Косн	10—35 %	Величина доданої вартості та ефективність управління спредом
К12	Процентна маржа, Дп – Вп	Доходи банку, Д	6—8 %	Ефективність процентної політики банку
К13	Непроцентні доходи, Дн	Активи банку, А	1—3 %	Можливості використання внутрішньобанківських резервів
К14	Дохідні активи, Ад	Активи банку, А	0,75—0,85	Частка дохідних активів
К15	Дохідні активи, Ад	Платні пасиви, Пп	> 1,0	Кількість дохідних активів на 1 грн платних пасивів (має пріоритет перед К14)
К16	Дохідні активи, Ад	Основний та додатковий капітал, К	8—18 %	Величина дохідних активів на 1 грн власного капіталу
К17	Недохідні активи, Ан	Капітал банку, К	0,5—2,0	Пріоритетність напрямів використання капіталу
К18	Процентні доходи, Дп	Платні пасиви, Пп	Визначає банк	Дохідність використання ресурсної бази банку

Коефіцієнтний аналіз рівня дохідності ПАТ «Промінвестбанк»

Коефі-цієнт	Чисельник	Знаменник	Оптималь-не значення	31.12. 2011 р.	31.12. 2012 р.	31.12. 2013 р.
К1	Прибуток, Пр	Активи банку, А	1—4 %	-2,44	0,08	0,54
К2	Прибуток, Пр	Доходи банку, Д	8—20 %	-18,74	0,67	4,81
К3	Доходи, Д	Активи банку, А	14—22 %	13,03	11,68	11,31
К4	Процентні доходи, Дп	Активи банку, А	10—18 %	11,36	10,20	10,22
К5	Процентна маржа, Дп – Вп	Дохідні активи, Ад	1—3 %	4,77	4,32	3,57
К6	Процентна маржа, Дп – Вп	Активи банку, А	1—4 %	4,30	3,91	3,18
К7	Процентні доходи, Дп	Процентні витрати, Вп	110—125 %	160,85	162,13	145,17
К8	Непроцентні доходи, Дн	Доходи банку, Д	5—15 %	12,76	12,74	9,63
К9	Непроцентні витрати, Вн	Доходи банку, Д	10—25 %	34,30	36,51	30,79
К10	Процентні доходи, Дп	Основний капітал банку, Косн	1,2—1,7	0,74	0,74	0,80
К11	Процентна маржа, Дп – Вп	Основний капітал банку, Косн	10—35 %	28,14	28,21	24,80
К12	Процентна маржа, Дп – Вп	Доходи банку, Д	6—8 %	33,00	33,44	28,12
К13	Непроцентні доходи, Дн	Активи банку, А	1—3 %	1,66	1,49	1,09
К14	Дохідні активи, Ад	Активи банку, А	0,75—0,85	0,90	0,90	0,89
К15	Дохідні активи, Ад	Платні пасиви, Пп	> 1,0	1,16	1,14	2,43
К16	Дохідні активи, Ад	Основний та додатковий капітал, К	8—18 %	6,79	6,79	6,98
К17	Недохідні активи, Ан	Капітал банку, К	0,5—2,0	0,63	0,53	0,53
К18	Процентні доходи, Дп	Платні пасиви, Пп	Визначає банк	0,15	0,13	0,28

Обчислення тенденції розвитку фінансового показника за допомогою лінії тренда на діаграму

Побудуйте діаграму зміни значень фінансового показника.

Виділяемо необхідні ячейки, які містять дані, на основі яких будуватиметься тренд, для цього переходимо до закладки «Вставка», обираємо «Графік».

Після того як графік побудований, можна додати підписи і заголовок. Для початку кликнемо лівою кнопкою миші по межі графіка, щоб виділити його. Потім перейдемо на закладку "Конструктор" і виберемо "Макет 1". Далі друкуємо текст заголовка.

Переходимо до побудови лінії тренда. Для цього знову виділяємо графік і переходимо на закладку «Макет».

Натискаємо на кнопку «Лінія тренда» і вибираємо «лінійне наближення» або «експоненціальне наближення».

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

Натискаємо на кнопку «Лінія тренда» і вибираємо «Додаткові параметри та лінії тренда».

У вікні «Формат лінії тренду», ми ставимо прапорець навпроти «помістити на діаграму величину достовірності апроксимації R ^ 2 і натискаємо кнопку«закрити».

Бачимо на діаграмі коефіцієнт R ^ 2.

Скасовуємо зміни. Виділяємо графік, натискаємо на закладку "Макет", кнопку "лінія тренду" і вибираємо "Ні".

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

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

Для висвітлення моделі, за якою побудовано тренд, в окні «Формат лінії тренду» необхідно поставити прапорець навпроти «показати рівняння на діаграмі».

Наглядно на прикладі можна подивитись http://www.tech-office2010.ru/page/kak-postroit-liniju-trenda-v-excel-2010

3. Прогнозування лінійної та експоненціальної залежностей

Автоматичне прогнозування лінійної залежності

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

Для прогнозування лінійної залежності виконайте наступні дії.

Вкажіть не менше двох осередків, що містять початкові значення.

Якщо потрібно підвищити точність прогнозу, вкажіть додаткові початкові значення.

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

Наприклад, якщо комірки C1: E1 містять початкові значення 3, 5 і 8, то при проштовхуванні вправо значення будуть зростати, вліво - спадати.

Щоб керувати створенням ряду вручну або заповнювати ряд значень за допомогою клавіатури, скористайтеся командою Прогресія (вкладка Головна, група Редагування, кнопка Заповнити).

Автоматичне прогнозування експоненційної залежності

В експоненційних рядах початкове значення множиться на крок для отримання наступного значення в ряді. Одержаний результат і кожний наступний результат множаться на крок.

Для прогнозування експоненційної залежності виконайте наступні дії.

Вкажіть не менше двох осередків, що містять початкові значення.

Якщо потрібно підвищити точність прогнозу, вкажіть додаткові початкові значення.

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

Наприклад, якщо комірки C1: E1 містять початкові значення 3, 5 і 8, то при проштовхуванні вправо значення будуть зростати, вліво - спадати.

Щоб керувати створенням ряду вручну або заповнювати ряд значень за допомогою клавіатури, скористайтеся командою Прогресія (вкладка Головна, група Редагування, кнопка Заповнити).

Прогнозування значень з функцією

Використання функції ПРЕДСКАЗ Функція ПРЕДСКАЗ обчислює або пророкує майбутнє значення за існуючими значеннями. Пророкувати значення - це y-значення, відповідне заданому x-значенню. Відомий набір існуючих x- і y-значень; нове значення передбачається з використанням лінійної регресії. Цією функцією можна скористатися для прогнозування майбутніх продажів, потреб в обладнанні або тенденцій споживання.

Використання функцій ТЕНДЕНЦІЯ і РОСТ Функції ТЕНДЕНЦІЯ і РОСТ дозволяють екстраполювати y-значення, що продовжують пряму лінію або експоненційну криву, найкращим чином описує існуючі дані. Ці функції повертають y-значення, відповідні заданим x-значень. Використовуючи x-значення та y-значення, можна побудувати графік процесу.

Використання функцій ЛИНЕЙН і ЛГРФПРІБЛ Функції ЛИНЕЙН і ЛГРФПРІБЛ дозволяють обчислити пряму лінію або експоненційну криву для наявних даних. Функції ЛИНЕЙН і ЛГРФПРІБЛ повертають дані регресійного аналізу, включаючи нахил і зсув графіка відносно осі Y.

Функція ПРЕДСКАЗ

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