Технології параметричного аналізу методами «що-якщо»

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

Типи інструментальних моделей.

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

— можливість уводити в клітини робочого аркуша не тільки дані, а й формули для оброблення цих даних;

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

— можливість відображати поняття відповідної предметної області та зв’язки між цими поняттями у зручній для людини формі;

— наявність значної кількості засобів та інструментів для проведення аналізу даних;

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

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

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

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

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

Наведені вище властивості середовища Excel роблять його майже ідеальним інструментом для реалізації моделей, основним методом подання яких є формульне відображення зв’язків між змінними. Серед багатьох засобів Excel для роботи з формулами слід виокремити такі:

¾ виправлення найзагальніших помилок під час введення фор­мул. Природно, користувач може погодитись або відкинути запропоноване коригування;

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

¾ ідентифікація діапазонів клітин, на які є посилання у формулі, кольоровими рамками під час редагування клітини з формулою, натисканням клавіші F2. За встановлення командою Сервіс / Параметри на вкладці Правка режиму Виправлення прямо в комірці, крім натискання клавіші F2, можна також використовувати подвійне клацання лівою кнопкою миші по відповідній клітині. Якщо цей режим вимкнуто, то подвійне клацання мишею приведе не до редагування вмісту клітини, а до виділення діапазонів клітин, на які є посилання у формулі;

¾ відслідковування й вилучення непотрібних циклічних посилань. Коли вводиться формула, яка змушує посилатися прямо або опосередковано на саму себе, то з’являється вікно попередження та автоматично відкривається панель інструментів Циклічні посилання. На цій панелі знаходиться список Знайти циклічне посилання, а також інструменти Залежні комірки та Комірки, які впливають, що дає змогу локалізувати клітини, які входять до циклічного посилання;

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

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

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

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

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

Варто наголосити, що спочатку необхідно забезпечити доступ до цього інструмента. Якщо в меню Сервіс відсутня команда Пошук рішення, то треба вибрати команду Сервіс / Надбудова, прокрутити список надбудов і встановити прапорець Пошук рішення. Якщо ця надбудова не була інстальована разом з MS Office, то її треба інсталювати окремо.

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

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

Моделі прогнозування поділяють на некаузальні та каузальні. Некаузальні моделі пропонують метод прогнозу за минулими значеннями без пояснення механізму генерації прогнозованого значення. Каузальні моделі використовують взаємозв’язки між змінними і намагаються пояснити їх поведінку.

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

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

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

Оскільки пакет аналізу — це надбудова, то спочатку необхідно забезпечити доступ до нього: вибрати команду Сервіс / Надбудова, прокрутити список надбудов і встановити прапорець Пакет аналізу. Після цього його можна використовувати так:

1. У меню Сервіс вибирається команда Аналіз даних.

2. У списку Інструменти аналізу вибирається потрібний рядок.

3. Уводиться вхідний та вихідний діапазони і встановлюються потрібні параметри.

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

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

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

Для генерації випадкових чисел, необхідних для проведення імітаційного моделювання, можна використовувати декілька засобів Excel: функції СЛЧИС(), СЛУЧМЕЖДУ() та інструмент пакета аналізу Генерація випадкових чисел.

Функція СЛЧИС() повертає рівномірно розподілене випадкове число між 0 та 1. Нове випадкове число повертається цією функцією кожного разу, коли перераховується робочий аркуш. Якщо потрібно, щоб отримане випадкове число не змінювалося, можна ввести =СЛЧИС() у рядок формул і натиснути клавішу F9. Щоб отримати випадкове дійсне число між а і b, можна використати таку формулу: СЛЧИС() * (b – а) + а.

Функція СЛУЧМЕЖДУ (нижня межа, верхня межа) повертає розподілене ціле випадкове число між двома заданими числами. Вона має два аргументи: нижня межа — це найменше ціле число, що повертається цією функцією, а верхня межа — це найбільше ціле число, що повертається функцією.

Інструмент Генерація випадкових чисел виводить діалогове вікно, в якому потрібно вказати кількість змінних (кількість стовпців значень випадкових чисел, які треба розмістити у вихідному діапазоні); кількість випадкових чисел (кількість випадкових значень, які потрібно вивести для кожної змінної); вид розподілення (рівномірне, нормальне, Бернуллі, біноміальне, Пуассона, модельне або дискретне); параметри вибраного розподілення; випадкове розсіювання та вихідний діапазон, в якому будуть розміщені згенеровані випадкові числа.

Одне з типових застосувань електронних таблиць — виконання аналізу «що-якщо». Суть аналізу «що-якщо» полягає в отриманні відповідей на питання: як вестиме себе досліджуваний об’єкт у різних ситуаціях, кожна з яких визначається певними значеннями вхідних даних? Так, за оцінювання конкретної маркетингової ситуації часто потрібно отримати відповіді на питання такого типу: що буде, якщо збільшити різницю між собівартістю і продажною ціною або збільшити заробітну плату співробітникам на 1 %? Яке зростання необхідне, якщо до початку наступного року треба досягти збільшення обсягів продажу на певну суму? Як зміниться прибуток, якщо змінити різницю між собівартістю та ціною продажу? Як відіб’ється на собівартості продукції підвищення цін на певні матеріали або на електроенергію?

Для спрощення аналізу «що-якщо» в Excel є декілька інструментів, до яких належать: Добір параметра. Таблиця підстановки даних. Диспетчер сценаріїв.

Дата добавления: 2014-01-11