Лекція на тему Обробка і аналіз та прогнозування економічних даних засобами табличного процесора MS Excel, НУДПСУ

ЛЕКЦІЯ №9

Тема 4.1.3. Обробка, аналіз та прогнозування економічних даних засобами табличного процесора MS Excel

ПЛАН

1. Статистичний аналіз даних.

2. Фінансовий аналіз.

3. Регресійний аналіз та прогнозування економічних показників на основі статистичних даних.

4. Застосування засобів Подбор параметра та Таблица подстановки.

5. Створення сценаріїв аналізу даних та отримання за ними звітів.

6. Використання засобу Поиск решения для розв’язання економічних задач.

Література: 1, 2, 4, 8, 9, 17, 21, 26, 28, 41.

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

• вбудованих статистичних функцій, у тому числі функцій прогнозування;

• вбудованих логічних функцій;

• вбудованих функцій дати та часу;

• вбудованих математичних функцій, у тому числі функцій роботи з масивами;

• вбудованих фінансових функцій;

• засобу Таблица подстановки;

• можливості побудови сценаріїв і створенні звітів на основі сценаріїв;

• надбудові Пакет аналізу, у тому числі: засобам регресійного аналізу, гістограма, описательная статистика та інші;

• надбудові Подбор параметра;

• надбудові Поиск решения;

• графічному аналізу із застосуванням лінії тренду;

• інші засоби.

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

1. Статистичний аналіз — це аналіз сукупностей числових да­них, метою якого є визначення статистичних та ймовірнісних ха­рактеристик названих сукупностей, виявлення наявності зв'язків між ними і проведення їх аналізу, прогнозування розвитку дина­мічних процесів тощо.

Щоб отримати доступ до цих функцій можна, наприклад,  натиснути кнопку Вставить функцію на смузі введення функцій і обрати категорю функцій СТАТИСТИЧЕСКИЕ (рис.9.1). Серед статистичних функцій є такі, які вводяться в одну комірку. Введення таких функцій завешується натисканням клавіші Enter чи командної кнопки ОК у вікні майстра функцій. Натомість  є такі функції, що вводяться у масив комірок, і їх введення завершується натисканням сполучення клавіш Ctrl+Shift+Enter.

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

Приклад 1. Скласти звітну відомість за результатами  діяльності торгівельної фірми у весняно-літній період.

У звітній відомості треба визначити:

- сумарну і середню виручку кожної з філій за звітний період;

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

- місце, яке займає кожна з філій в сумарному об’ємі виручки;

- частку кожної з філій в сумарному об’ємі виручки;

- кількість філій, що мають сумарну виручку до 5000 тис. грн.., від 5000 тис. грн. до 7000 тис. грн., від 7000 тис. грн. до 10000 тис. грн. і понад 10000 тис. грн.;

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

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

1) Для знаходження сумарної виручки першої філії введіть в комірку, наприклад, І4

формулу

=СУММ(В4:G4)

2) Виділіть комірку  І4, розташуйте покажчик Миші на маркері заповнення і перемістіть його вниз на діапазон І5:І13. Це дозволить знайти сумарну виручку кожної філії.

3) Для знаходження сумарного об’єму виручки всіх філій за березень введіть в комірку А14 заголовок Всього, а в комірку В14 формулу

=СУММ(В4:В13).

4) Виділіть комірку  В14, розташуйте покажчик Миші на маркері заповнення і перемістіть його вправо на діапазон С14:G14. Це дозволить знайти сумарну виручку філій як за кожний місяць окремо.

5) У комірці І14 обчисліть   сумарну виручку в цілому, ввівши у комірку формулу: =СУММ(І4:І13).

6) Для знаходження середньої виручки першої філії введіть в комірку J3 заголовок Середня виручка, тис. грн., а в комірку J4 – формулу

=CP3HAЧ(B4:G4).

7) Виділіть комірку J4, розташуйте покажчик Миші на маркері автозаповнення і перемістіть його вниз на діапазон J5:J13. Це дозволить знайти середню виручку кожної філії.

8) Для визначення частки об’єму виручки першої філії по відношенню до сумарної виручки всієї мережі філій введіть в комірку К3 заголовок Частка, а в комірку К4 формулу

=І4/$І$14.

9) Виділіть комірку К4, розташуйте покажчик Миші на маркері автозаповнення і перемістіть його вниз на діапазон J5: J13. Це дозволить знайти частку об’єму виручки кожної філії по відношенню до сумарної виручки всієї мережі філій.

10) Виділіть комірку І14 і виконайте присвоєння цій комірці імені Разом командою Вставка Þ Имя| Присвоить Þввести ім’я у діалоговому вікні.

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

11) Виділіть комірку К4 і змініть формулу на

=І4/Разом.

12) Виконайте копіювання формули на діапазон комірок К5:К13 за допомогою маркера автозаповнення. По черзі виділіть кожну з комірок діапазону К4: К13 і за допомогою рядка формул проаналізуйте формули у цих комірках. Зробіть висновки.

13) Виділіть діапазон комірок І4:І14 і виконайте присвоєння цьому діапазону імені СумаПоФіліям командою Вставка Þ Имя| Присвоить Þввести ім’я у діалоговому вікні Присвоение имениÞОК.

14) Виділіть комірку К4 і змініть формулу на

=СумаПоФіліям/Разом.

15) Виконайте копіювання формули на діапазон комірок К5: К13 за допомогою маркера автозаповнення. По черзі виділіть кожну з комірок діапазону К4:К13 і за допомогою рядка формул проаналізуйте формули у цих комірках. Зробіть висновки.

16) Виділіть діапазон комірок К4:К13 і натисніть кнопку Процентный формат на панелі Форматирование. Це дозволить встановити процентный формат в комірках вибраного діапазону.

17) Для визначення місця першої філії у сумарній виручці всієї мережі філій введіть в комірку  L3 заголовок Рейтинг, а в комірку L4 --  формулу

=РАНГ(J4;$J$4:І$13$) або =РАНГ(J4;СумаПоФіліям)

18) Виділіть комірку L4, розташуйте покажчик Миші на маркері автозаповнення і перемістіть його вниз на діапазон L5:L13. Це дозволить знайти рейтинг кожної  філії в сумарній виручці всієї мережі філій.

19) Для визначення кількості філій, що що мають сумарну виручку до 5000 тис. грн.., від 5000 тис. грн.. до 7000 тис. грн.., від 7000 тис. грн.. до 10000 тис. грн.. і понад 10000 тис. грн.. спочатку потрібно у комірках M3 та N3 створити заголовки Межі виручки, тис. грн. та Кількість філій, а потім у комірку  M4 ввести число 5000, у комірку M5 – число 7000, у комірку M6 – число 10000.

20) Виділити діапазон комірок N4:N13 і ввести в нього формулу масиву з використанням майстра функцій

{=ЧАСТОТА(СумаПоФіліям;М4:М6)}

Не забудьте завершити її введення натисненням комбінації клавіш Ctrl+Shift+Enter.

21) Для обчислення найменшої місячної виручки за звітний період потрібно виділити певну комірку, наприклад, І15 і ввести формулу

=МИН(B4:G13). Бажано створити текстовий заголовок у комірці зліва Найменша місячна виручка, грн.

22) Для обчислення найбільшої  місячної виручки за звітний період потрібно виділити певну комірку, наприклад, І16 і ввести формулу

=МАКС(B4:G13). Бажано створити текстовий заголовок у комірці зліва Найбільша місячна виручка, грн.

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

При складанні звітної відомості використовувалися статистичні функції ранг і частота. Функція ранг (rank) повертає ранг числа в списку чисел. Ранг числа — це положення його величини у впорядкованому  списку. Якщо список відсортувати, то ранг числа буде його позицією у списку. Синтаксис:

РАНГ(число;   посилання;   порядок)

• число — число (або адреса комірки, яка містить число), для якого визначається ранг;

• посилання — масив комірок або посилання на список чисел. Нечислові значення в посиланні ігноруються;

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

Функція ранг присвоює однаковим числам однаковий ранг.

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

Синтаксис:

ЧАСТОТА (масив_даних; двійковий_масив)

• массив_данних — масив або посилання на  дані, для яких обчислюються частоти; якщо массив_данних не містить значень, то функція частота повертає масив нулів;

• двійковий__массив — масив або посилання на безліч інтервалів, в які

групуються  значення   аргументу  масив__даних.

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

Приклад 2. За заданим розподілом кількості проданих біржею акцій і розподілом цін (рис.9.5) необхідно створити звіт про  сумарну вартість проданих акцій за кожний тиждень звітного періоду.

При розв’язанні даної задачі, яка полягає у складанні звіту про сумарну вартість проданих акцій за кожний тиждень звітного періоду потрібно використати функцію масивів  СУММПРОИЗВ – це найшвидший спосіб виконання необхідних обчислень. При виконанні обчислень із застосуванням означеної функції потрібно дотриматись такого алгоритму:

1) У комірці, наприклад, Н3 створіть заголовок Сума по тижням, грн.

2) У комірку Н4 введіть формулу = СУММПРОИЗВ($A$12:$F$12;B4:G4).

3) За допомогою маркера автозаповнення скопіювати формулу у комірки діапазону Н5:Н7.

4) Буде обчислено сумарну вартість проданих акцій за кожний тиждень звітного періоду. Проаналізуйте формулу кожної комірки діапазону Н5:Н6.

Зауваження.

Відзначимо, що сумарну вартість проданих акцій за кожний тиждень звітного періоду можна обчислити із залученням формули масиву, ввівши в діапазон комірок  Н4:Н6 формулу

=СУММ (ВЗ:G3* $А$11:$F$11).

Щоб пересвідчитись у реальності такого способу обчислень, реалізуйте такий алгоритм для діапазону комірок  І4:І6.

Статистичний аналіз не можливий без застосування логічних функцій:

• ЕСЛИ;
• И;
• ИЛИ;
• НЕ;
• ИСТИНА;
• ЛОЖЬ.

Для роботи з даними, що мають тип дата, викає необхідність у застосуванні функцій категорії ДАТА И ВРЕМЯ.

2. Фінансовий аналіз

Табличний процесор Microsoft Excel в своєму арсеналі містить категорію функцій Финансовые. Вони орієнтовані на  розв’язання задач фінансового аналізу. Усі фінансові функції Microsoft Excel можна класифікувати на такі групи:

1. для аналізу дисконтування;

2. для аналізу інвестицій;

3. для аналізу амортизації;

4. для аналізу цінних паперів.

Розглянемо найважливіші фінансові функції робочого аркуша: функції для обчислення розміру платежів та функції для обчислення інтервалів платежів.

Зауваження. При використанні фінансових функцій потрібно узгоджувати одиниці вимірювання процентної ставки і загальну кількість  періодів виплат. Приклад. Якщо здійснюються щомісячні платежі з періодом 3 роки при річній процентній ставці 15%, то при заданні аргументів фінансових функцій потрібно задавати місячну процентну ставку 15%/12 і кількість періодів виплат теж задавати у місяцях 3*12=36. Якби за даних умов було задано, що виплати здійснюються поквартально, то квартальна процентна ставка становила б 15%/4, а кількість періодів виплат 3*4=12. Якщо здійснюються щорічні платежі, то потрібно задати процентну ставку 15%, а кількість періодів виплат 3.

Функція ПЛТ

Ця функція обчислює величину сплат на основний капітал і платежі процентів по займу на основі постійних сплат і  постійній процентній ставці.

Синтаксична формула

ПЛТ(ставка; кпер; нз; бз; тип), де

ставка – процентна ставка за період; кпер – загальна кількість періодів сплат; нз – поточне значення; тобто загальна сума, яку складатимуть майбутні сплати; бз – майбутня вартість. Якщо цей аргумент опущений, то його значення вважається рівним нулю, тобто майбутня вартість займу дорівнює нулю; тип – число, 0 або 1, якщо задано 0 або опущено цей аргумент, то сплата здійснюється в кінці періоду, а якщо 1, то на початку періода.

Задача 1.

Розрахувати розмір щомісячного внеску по займу 1200000 грн. при річній відсоткові ставці 18% і щомісячній сплаті протягом 36 місяців, причому в кінці кожного періода.

Алгоритм розв’язання.

В деяку комірку робочого аркуша, наприклад В5 (рис.9.7),  введіть формулу

= ПЛТ(18%/12;36;1200000) і натисніть клавішу Enter.

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

Відповідь: щомісячні сплати становлять 43382,87грн. Знак мінус свідчить про те, що це лати.

Функція БС

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

Синтаксис функції:

БС(норма; кпер; выплата; нз; тип), де

норма – процентна ставка за період;  кпер – загальна кількість періодів виплат; выплата – величина  періодичних постійних сплат; нз – поточна вартість, або загальна сума всіх майбутніх платежів з даного моменту; тип – число, 0 або 1, якщо задано 0 або опущено цей аргумент, то сплата здійснюється в кінці періоду, а якщо 1, то на початку періода.

Якщо аргумент нз відсутній, то його значення вважається рівним нулю.

Задача 2.

Планується на банківський рахунок щорічно вносити по 5 000 грн. в кінці року з розрахунку 15% річних. Яка сума буде на рахунку через 7 років?

Алгоримт розв’язання.

 В деяку комірку робочого аркуша, наприклад В32 (рис.9.8),  введіть формулу

=БС(15%;7;5000;;0.).

Можна скористатись  Майстром функцій: клацнути на кнопці  (вставка функції) на смузі введеня формул. З переліку категорій функцій обрати Финансовые  і вибрати зі списку функцій БС. У вікні Майстра задати аргументи функції (рис.9.9) і натиснути кнопку ОК.

Відповідь: на банківському рахунку буде 55 334 грн. Знак мінус означає, що ця сума повинна бути накопичена за рахунок щорічних сплат.

Функція ПС

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

Синтаксис функції:

ПС(норма; кпер; выплата; бз; тип), де

норма – процентна ставка за період;  кпер – загальна кількість періодів виплат; выплата – величина  періодичних постійних сплат; бз – майбутня вартість; тип – число, 0 або 1, якщо задано 0 або опущено цей аргумент, то сплата здійснюється в кінці періоду, а якщо 1, то на початку періода.

Задача 3.

Страхова фірма надає страховку, в якій пропонується сплата по 500 грн. щомісячно наприкінці місяця протягом 10 наступних років. Вартість страховки становить 50 000 грн., а сплачувані кошти принесуть 8% річних. Зясувати, чи буде означена сума, що сплачується на даний час, еквівалентною майбутній вартості і чи є цей механізм оптимальним інвестуванням з погляду нарощування капіталу.

Алгоритм розв’язання.

 На робочому аркуші створіть таблицю як на рис.9.10. 

Сума майбутніх платежів обчислена у комірці В25, а формула для обчислення міститься у рядку введення формул. Як бачимо сума становить 41 210 грн. 74 коп., що значно менше 50 000 грн., які потрібно сплатити за страховку. Отже така інвестиція не є оптимальною з погляду нарощування капіталу.

Функція КПЕР.

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

Синтаксис

КПЕР(норма; виплата; нз; бз; тип)

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

Функція застосовується у таких випадках:

1) коли розраховують загальну кількість періодів нарахування процентів, необхідних для того, щоб початкова сума розміром нз досягла значення бз. В цьому випадку функція має скорочену форму:

КПЕР(норма;; нз; бз;)

Задача 4.

Припустимо, що ви хочете покласти на банківський рахунок кошти із розрахунку 17% річних. Через який проміжок часу депозит у розмірі 30 000 грн. досягне розміру 50 000?

Алгоримт розв’язання.

На робочому аркуші створіть таблицю як на рис.9.11.

Потрібно в комірки робочого аркуша ввести вихідні значення необхідних для обчислення величин . Далі в  комірку G6 ввести формулу, яка містить функцію =КПЕР(С6;; -D6;E6;).

Відповідь: приблизно через 3 роки 3 місяці.

Задача 5.

Розрахувати, через скільки місяців банківський вклад досягне розміру 120 000 грн., якщо сплати в розмірі 3 000 грн.здіснюються щомісяця, а банківська процентна ставка становить 18% річних.

Алгоримт розв’язання.

На робочому аркуші створіть таблицю як на рис.9.12. Потрібно в комірки робочого аркуша ввести вихідні значення необхідних для обчислення величин Далі в комірку G6 ввести формулу: =КПЕР(С6/12; -B6;;E6).

Відповідь: приблизно через 31,57 місяців.

2) При погашенні займу розміром нз однаковими платежами в кінці розрахункового періоду, протягом якого відбудеться погашеня займу. Тоді функція має вигляд: КПЕР(норма;выплати; нз). Отримане значення можна використовувати як покажчик терміну окупності при аналізі інвестиційного проекту.

Задача 6.

Банк надав фірмі ссуду для інвестицій у виробництво розміром 150 000 грн. під 25% річних. Передбачається щомісячне погашення заборгованості платежами у 8 000 грн. Визначити термін погашення ссуди.

Алгоримт розв’язання.

На робочому аркуші створіть таблицю як на рис.9.13. В комірку G6 ввестио формулу  =КПЕР(С6; -B6;;E6).

Відповідь: термін погашення ссуди становить приблизно 16  місяців

Функція БЗРАСПИС

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

Синтаксис функції:

БЗРАСПИС (основной капитал; ставки), де

основной капітал – поточна вартість інвестиції,

ставки – масив процентних ставок по роках капіталізації.

Задача 7.

Фірма поклала на банківський рахунок вклад розміром 50 000 грн. терміном на 5 років. Протягом цього періоду ставки змінювались у відповідності з наведеними у таблиці 9.1 значеннями.

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

Алгоримт розв’язання.На робочому аркуші створіть таблицю як на рис.9.15 У комірку В16 ввести   функцію =БЗРАСПИС(А11;С11:С15).

Відповідь: сума капіталізації становитиме 87 323 грн. 36 коп.

3. Регресійний аналіз та прогнозування економічних показників на основі статистичних даних.

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

Кореляція — це взаємозв'язок між показниками, що полягає в зміні середнього значення одного з показників залежно від змі­ни іншого. Вирізняють дві форми кореляційних зв'язків: прямо­лінійну та криволінійну. Рівняння, за допомогою якого можна описати кореляційний зв'язок, називають рівнянням регресії. Графіком рівняння регресії є лінія регресії. Функція, що описує рівняння регресії, називається функцією апроксимації.

Залежно від кількості взаємозв'язаних показників існують такі типи регресії:

1) парна (залежність між двома показниками);

2) множинна (один показник залежить від п інших показників, які на нього впливають).

Функція апроксимації визначає форму регресії:

1) лінійну (залежність описується лінійною функцією);

2) експоненціальну (залежність описується експоненціальною функцією).

Основною метою проведення регресійного аналізу є:

• виявлення наявності кореляції та опис її рівняння регресії — інтерполяція (базується на методах регресійного аналізу);

•  прогноз значень залежного показника за межами множини значень незалежного показника — екстраполяція (базуєть­ся на продовженні на майбутнє тенденції, що спостерігалась у минулому).

Окремим випадком парної регресії є динамічні ряди, де неза­лежною змінною є час, а залежною — значення показника.

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

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

Використання методу екстраполяції для прогнозування дина­мічних рядів базується на двох припущеннях:

• динамічний ряд економічного показника справді має тренд, тобто переважну тенденцію;

• загальні умови, що визначають розвиток показника в мину­лому, залишаються без істотних змін протягом періоду про­гнозування.

Для визначення коефіцієнтів рівняння регресії використо­вують функції:

- для парної лінійної регресії:

НАКЛОН (изв_знач_у; изв_значя_х)

ОТРЕЗОК (изв_знач_у; изв_знач_х:)

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

ЛИНЕЙН (изв_знач_у; изв_знач_х; конст; статистика).

- для множинної експоненціальної регресії послідовність коефіцієнтів визначається з використанням формули масиву та функції ЛГРФПРИБЛ (изв_знач_у; изв_знач_х; конст; статистика), деизв_знач_у— діапазон, в якому міститься множина відомих значень у — фактичних значень залежного показника або факти­чних значень динамічного ряду; изв_знач_х — діапазон, в якому міститься множина відомих значень х:

- для парної регресії — множина фактичних значень незале­жного показника або точки часу (дати чи порядкові номе­ри), в які відбувалось спостереження фактичних значень динамічного ряду;

- для множинної регресії — множина фактичних значень не­залежних показників;

конст — логічна константа, яка встановлює, чи потрібно, щоб був нульовим коефіцієнт Ь, набирає значення:

- ЛОЖЬ (0) для b =0,    ИСТИНА (1, за замовчуванням) для b ¹ 0; статистика — логічна константа, яка визначає, чи треба виводи­ти інші статистичні характеристики, набирає значення: ЛОЖЬ (О, за замовчуванням) — тільки коефіцієнти регресії; ИСТИНА (1) - визначаються інші статистичні характери­стики.

Проведення екстраполяції

Для проведення прогнозування використовують для ліній­ної регресії функції ПРЕДСКАЗ і ТЕНДЕНЦИЯ та для експо­ненціальної регресії — функцію РОСТ. Синтаксис цих функ­цій такий:

- ПРЕДСКАЗ (X; Изв_знач_у; Изв знач х);

- ТЕНДЕНЦИЯ (Изв_знач_у; Изв_знач_х; Нов_знач_х; конст);

- РОСТ (Изв_знач_у; Изв_знач_х; Нов_знач_х; конст),

де Xта нов_знач__х — діапазон комірок, в якому міститься множина но­вих значень х;

- для парної регресії — множина значень незалежного пока­зника, для яких прогнозується значення залежного або точ­ки часу (дати чи порядкові номери), для яких прогнозуєть­ся значення динамічного ряду;

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

Для прогнозування значень парної регресії можна використо­вувати всі зазначені функції,а для множинної регресії функція ПРЕДСКАЗ не застосовується. Крім того, функції ТЕНДЕНЦІЯ та РОСТ дозволяють ураховувати, чи потрібно проходження лінії регресії через початок координат; у разі використання функції ПРЕДСКАЗ апріорі не потрібно проходження лінії тренду через початок координат.

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

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

Виконуючи засобами MS Excel інтерполяцію та екс­траполяцію динамічного ряду, ураховують,  в якому форматі на робочий лист занесені точки часу:

•  якщо мінімальний період спостережень — день, тобто точка часу занесена у форматі дата, то ар­гументом изв_знач_х; є діапазон, який містить дати фактичних спостережень; аргументом нов_знач_х є діапазон, який містить дати, в які прогнозується значення показника;

•  якщо мінімальний період спостережень — тиж­день, місяць, рік тощо, тобто точки часу занесені у звичайному числовому форматі (2005, 2006, 2007, 2008...) або в текстовому форматі (січень, лютий, березень...), то необхідно самостійно на робочому задати відповідний формат для  діапазону комірок,  в якому будуть зазначені порядкові номери цих точок ча­су та точок часу прогнозування, і вже такі діапа­зони використовувати як аргументи изв_знач_х: та нов_знач_х.

Аналітичне прогнозування динамічного ряду мож­на  проводити  з   використанням  засобу  автозаповнення:

1) виділити діапазон з фактичними значеннями по­казника; вибрати з контекстного меню тип апроксимації: линейное приближение, экспоненциальное прибли­жение.

У результаті комірки заповняться значеннями (не фор­мулами). При цьому вважається, що перша комірка по­передньо виділеного діапазону — це значення показ­ника в першій точці часу, друга комірка — у другій точці часу, n-на  комірка — у n-ный точці часу, а прогно­зується послідовно починаючи з n + 1-ї точки.

MS Excel дозволяє графічно проводити інтерполяцію та екст­раполяцію для динамічного ряду способом додавання лінії трен­ду на діаграмі (пласкій).

Для того, щоб додати лінію тренду на діаграмі, потрібно: відкрийте контекстне меню ряду даних і клацніть на команді Добавить линию тренда (або меню Диаграммаà  Добавить линию тренда). На екрані з’явиться діалогове вікно Линия тренда. На вкладці Тип (рис.9.16) діалогового вікна Линия трендапотрібно обрати тип лінії тренда. За замовчуванням активним є тип – Линейная.На вкладці Параметры (рис.9.17)діалогового вікна Линия тренда можна встановити параметри лінії тренда.  В групі Прогноз  можна вказати число періодів, на які лінія тренда або складає прогноз (вперед), або визначає історію процесу (назад). Натисніть кнопку ОК. Якщо встановлений прапорець у віконці показывать уравнение на диаграмме, то рівяння лінії тренда буде відображатись на діаграмі. При встановленому прапорці у віконці поместить на диаграмму величину достоверности аппроксимации (R^2) на діаграмі буде відображатись величина достовірності апроксимації статистичних даних рівнянням регресії: чим ближче R^2 до 1, тим краще апроксимуються дані. Якщо ця величина лежить в межах від 0,9 до 1, то лінію тренда можна використовувати для прогнозування.

4. Застосування засобів Подбор параметра та Таблиці підстановки.

Засоби Подбор параматра та Таблица подстановки застосовуються у факторному аналізі – методі дослідження в економіці, в основі якого лежить аналіз впливу різних факторів на результати економічної діяльності, для якої розроблена економіко-математична модель.  Якщо на кінцевий результат впливає лише один фактор, то за­дача зводиться до розв'язання звичайного математи­чного рівняння, і в MS Excel для цього використовують надбудо­ву Подбор параматра. Розглянемо його застосування на прикладі економічної задачі.

Приклад 1. 

Використовуючи  дані  таблиці 9.3, знайдіть, при якому значенні терміну вкладу сума виплат становитиме 12 000 грн.                                     

Алгоритм розв’язання

1) Виділіть комірку, в якій міститься значення суми  виплат, яке обчислене за формулою:

- Для заданої таблиці це буде комірка С16. Ця формула є математичною моделлю економічної задачі виплат по вкладах по складним відсоткам.

- Тоді у комірках В12:С16 буде побудована її таблична модель – комп’ютерна модель даної економічної задачі.

2) Меню Сервис à Подбор параметра.В діалоговому вікні Подбор параметра (рис.9.19):

а) перевірте , чи задана адреса комірки С16, яка містить значення суми до виплат, в текстовому полі Установить в ячейке;

б) встановіть текстовий курсор в текстове віконце Значение і введіть з клавіатури потрібне значення, у даному прикладі 12000;

в) встановіть текстовий курсор в текстове віконце Изменяя значение ячейки і клацніть на комірці, в якій міститься значення терміну вкладу, у даному прикладі С13;

г) клацніть на кнопці  ОК.

Знайдене значення у комірці С13 і буде тим шуканим значенням терміну вкладу, при якому значення суми виплат досягне заданого розміру ( у даному разі 12000 грн.).

Засіб "Таблица подстановки"використовується для дослідження впливу одного чи двох параметрів на значення певної величини, яка задана формулою, що містить ці параметри. Якщо параметр один, то список його значень і список значень досліджуваної величини утворюють таблицю, яка називається  таблицею підстановки з однією змінною, що відображає функціональну залежність y=f(x), чи з двома змінними z=f(x,y).

5. Створення сценаріїв аналізу даних та отримання за ними звітів

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

Сценарій — це іменований набір значень зазначених комірок аркуша робочої книги. Сценарний підхід забезпечує розв'язу­вання задач типу «ЩО — ЯКЩО», не обмежуючи кількість одночасно зміню­ваних параметрів для кожного такого набору. Сценарії використовуються для під­становки значень параметрів у комірки таблиці та обчислень залежних від них формул.

Обмежень на загальне число сценаріїв на одному аркуші робочої книги немає. Сценарії містять лише комірки одного аркуша робочої книги, при цьому допуска­ється об'єднання сценаріїв декількох аркушів і навіть сценаріїв інших робочих книг при підготовці звітів. У звіті може бути до 251 сцснарія.

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

Команда меню Сервис®Сценарии виводить діалогове вікно Диспетчер сценариев.

Вікно містить ряд текстових кнопок:

•  Вывести — введення значень параметрів вибраного сценарію;

• Закрыть — закривання діалогового вікна і припинення роботи зі сценаріями;

•  Добавить — додавання нового сценарію;

•  Удалить — вилучення зазначеного сценарію;

•  Изменить — зміна значень параметрів вибраного сценарію;

•  Объединить — об'єднання зі сценаріями інших аркушів чи робочих книг;

•  Отчет— виведення звіту зі сценаріїв.

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

Якщо комірки, які входять у сценарій, не суміжні, вони виділяються при натиснутій клавіші Ctrl, максимальне число комірок у одному сценарії — 32.

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

Після натискання кнопки ОК з'являється діалогове вікно Значение ячеек сценария, у якому задаються значення комірок, які відповідають параметрам моде­лі. Натискання кнопки ОК завершує введення сценаріїв, натискання кнопки Добавить забезпечує введення чергового сценарію.

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

6. Використання засобу Пошук рішення для розв’язання економічних задач.

Засіб-надбудова Поиск решения є частиною засобів розв’язання блоку задач, який називають аналізом "що-якщо".    Процедура пошуку рішення дозволяє знайти оптимальне значення формули, яка  міститься в комірці, яка називається цільовою коміркою.Ця процедура працює з групою комірок, прямо або опосередковано пов’язаних з формулою в цільовій комірці.Щоб одержати за формулою, що міститься в цільовій комірці,заданий результат, процедура змінює значення у впливаючих  на це значення комірках. Щоб звузити безліч значень, що використовуються в моделі, застосовуються обмеження.Ці обмеження можуть посилатися на інші впливаючі комірки.Процедуру пошуку рішення можна використовувати для визначення значення впливаючої комірки,  яке відповідає екстремуму залежної комірки — наприклад, можна змінити об’єм планованого бюджету реклами і побачити, як це вплине на проектовану суму витрат. Задач такого змісту існує дуже багато у фінансово-економічній сфері, сфері маркетингу та менеджменту.Ці задачі дуже важливі і тому важливо вміти їх розв’язувати.Середовище MS Excel  для розв’язання таких задач надає до послуг користувачів засіб - надбудову Поиск решения.Щоб викликати цей засіб - надбудову, потрібно відкрити меню Сервис і вибрати пункт Поиск решения,за умови, що при інсталяції програмного засобу MS Excel надбудови встановлені на вашому комп’ютері.При першому його використанні потрібно виконати активізацію засобу: меню Сервис=>Надстройки=>Поиск решения.

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

За результатами розв'язування створюються звіти. Отчет по результатам — це відомості про цільову функцію із вказівкою комірки, відомості про змінні із вказівкою списку комірок, початкових і кінцевих значень, відомості про обмежен­ня із вказівкою списку комірок, формул, обчислених значень, статусу і різниці (вільного залишку). Отчет по устойчивости — відомості про чутливість моделі (зміна цільової функції при зміні змінних і обмежень). Отчет по границам — відомості про ниж­ні і верхні границі значень змінних. Нижня межа — найменше значення змінної, верхній межа — найбільше значення змінної (значення всіх інших змінних фіксо­вані і задовольняють обмеженням). Деякі різновиди звітів не створюються, якщо задані додаткові умови, наприклад, значен­ня змінних — тільки цілі числа.

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

Завдання 1. Знайти оптимальний місячний план випуску продукції, при якому витрати на виготовлення будуть мінімальними. Кількість продукції кожного виду змінюється в межах від 15 до 35 одиниць, а загальна кількість одиниць - не менша 125 одиниць. Використати дані таблиці 9.4.

Алгоритм роз’язання

1) На робочому аркуші  створіть дані як у табл.9.4.

2) Виконайте необхідні обчислення у стовпці Витрати на партію товару, застосовуючи формули і копіюючи їх на потрібний діапазон комірок.

3) У рядку Всього обчисліть сумарну кількість одиниць продукції та сумарні витрати на партію продукції.

4) Усвідомте той факт, що цільовою коміркою в даній задачі буде комірка, в якій обчислено сумарні витрати на партію продукції.

5) Виділіть комірку, в якій обчислені сумарні витрати на партію продукції.

6) В меню Сервис клацніть на пункті Поиск решения.

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

8) Встановіть альтернативний перемикач групи Равной в положення минимальному значению.

9) Усвідомте, величини в яких комірках впливають на значення цільової комірки.

Оскільки витрати на виготовлення одиниці продукції в даних умовах змінюватись не можуть, то впливаючими величинами будуть кількость одиниць даного різновиду продукції, отже комірки F8:F13  - впливаючі.

10) У діалоговому вікні Поиск решения встановіть текстовий курсор в текстовому віконці  Изменяя ячейки і виділіть діапазон комірок F8:F13.

11) За умовою задачі сумарна кількість одиниць продукції повинна бути не меншою за 125, а кількість одиниць продукції кожного різновиду повинна знаходитись в межах між 15 і 35 одиницями. Ці умову і створюють систему обмежень на величини впливаючих комірок.

12) Натисніть кнопку Добавить в діалоговому вікні Поиск решения.

13) В діалоговому вікні Добавление ограничения (рис.9.23) становіть текстовий курсор в полі Ссылка на ячейку і клацніть на комірці, в якій обчислено сумарну кількість одиниць продукції. Потім виберіть із списку оператор порвняння >= , а далі встановіть текстовий курсор в текстовому полі Ограничение і введіть з клавіатури 125, після чого  клацніть кнопку Добавить цього ж діалогового вікна.

14) Далі введіть наступне обмеження на впливаючі комірки F8:F13, яке полягає в тому, що  кількість одиниць продукції кожного різновиду не перевищує  35 одиниць. Натисніть кнопку Добавить у діалоговому вікні Поиск решения. У діалоговому вікні Добавление ограничения створіть обмеження.

15) Введіть наступне обмеження на впливаючі комірки F8:F13, яке полягає в тому, що  кількість одиниць продукції кожного різновиду не менше 15 одиниць. Натисніть кнопку Добавить у діалоговому вікні Поиск решения. У діалоговому вікні Добавление ограничения створіть обмеження.

16) Введіть наступне обмеження на впливаючі комірки F8:F13, яке полягає в тому, що  кількість одиниць продукції кожного різновиду - ціле число (рис.9.26). Натисніть кнопку ОК.

17) Діалогове вікно Поиск решения,  матиме виглядяк на рис.9.27.

18) Натисніть кнопку Параметры діалогового вікна Поиск решения і вивчіть діалогове вікно Параметры поиска решения(рис.9.28), а також встановлені за замовчуванням значення та положення індикаторних і альтернативних перемикачів.

19) Натисніть кнопку Справка діалогового вікна Параметры поиска решения і вивчіть довідкову інформацію. Після вивчення закрийте вікно Справка і натисніть кнопку ОК діалогового вікна Параметры поиска решения. В діалоговому вікні Поиск решения натисніть кнопку Выполнить.

20) В діалоговому вікні Результаты поиска решения (рис.9.29),  якщо розв’язок знайдено, клацніть на кнопці ОК при положенні альтернативного перемикача Сохранить найденное решение.

21) Перегляньте звіти: Результаты, Устойчивость та Пределы. Проаналізуйте їх.

22) Перейдіть до таблиці значень і уважно вивчіть встановлені у комірках F8:F13 та G14 значення.

Значення величин у комірках F8:F13 та G14 і є розв’язком задачі.

З повагою ІЦ "KURSOVIKS"!