Tools —» Macro -» Macros —> КонвЗТоргКварт—> Run

запустимо макрос КонвЗТоргКварт(або натиснемо на «шви­дку клавішу» Ctrl+Shift+E). В результаті виконання макроса утвориться новий листок «Кварт» з квартальною груповою ста­тистикою. Активною клітинкою на цьому листку в цей момент буде клітинка «Товарна група» заголовку.

Запустимо макрос ВсіКвартМіс(через меню або за допомо­гою комбінації клавіш Ctrl+Shift+D). В результаті буде створено листок «Міс» з місячною груповою статистикою.

Етап 3.Активізуємо листок «Товар» і клітинку «Рік» на заго­ловку таблиці з даними вибіркового обстеження попиту. Запус­тимо макрос ПідсТов(через меню або клавішами Ctrl+Shift+F). В результаті буде створено новий листок «ТСорт» з підсумкови­ми даними вибіркового обстеження. Активною знову буде кліти­нка «Рік» на заголовку таблиці.

За допомогою макроса КопВсіПідс{Ctrl+Shift+C) підсумки доповнюються підсумковою інформацією. Знову активною стане клітинка «Рік» на заголовку таблиці.

Етап4. Макрос ФормПідс(Ctrl+Shift+G) відфільтровує під­сумки і переписує на новий листок «ТПідс» підсумкові значення (усуваючи формули). Макрос ПідсРез (Ctrl+Shift+H) утворює листок «ТРез», на який переписує відфільтровані значення, і усу­ває зайвий тепер стовпчик КАП.

Таким чином, основні рутинні процедури задачі оцінки попи­ту на товари ефективно автоматизуються за допомогою шести основних макросів, наведених в таблиці IIonnT.XLS.На листку «Інстр» цієї таблиці наведена коротка підказка про призначення описаних макросів.

Методичні вказівки до завдання 2

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

10*

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

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

За допомогою АРМ маркетолога розв'язуються такі завдання, як вивчення реалізованого і незадоволеного попиту, його прогно­зування, аналіз ринку і його кон'юнктури, визначення місткості і сегментації ринку.

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

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

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

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

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

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

де у — залежна змінна, яка виражає величину попиту;

х₁,х₂,...,х_п — незалежні змінні, які ввідповідають факторам попиту.

Кореляційно-регресійний аналіз складається з таких основних етапів:

1) побудова системи факторів, що найсуттєвіше впливають на результативну ознаку;

2) розробка моделі, яка відображає загальний зміст взаємо­зв'язків, що вивчаються, та кількісна оцінка її параметрів;

3) перевірка якості моделі;

4) оцінка впливу окремих факторів.

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

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

Ця формула має розподіл Стьюдента з к=п-2 ступенями сво­боди. Для заданої ймовірності р і ступенів свободи к знаходиться

табличне значення t _рк — статистики. Якщо t>t_pk, то із зада­ною надійністю р приймається гіпотеза Н_х про наявність коре­ляційного зв'язку між випадковими величинами х тау (між попи­том і грошовими доходами).

Якщо t < t _к, то приймається гіпотеза Н₀. В цьому випадку

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

Парний коефіцієнт кореляції характеризує зв'язок між залеж­ною змінною і одним із факторів (наприклад, між попитом і до­ходами, без урахування цін і товарообороту з 1 м² складської площі). В цьому випадку необхідно визначити частинний коефі­цієнт кореляції.

Частинний коефіцієнт кореляції характеризує кореляційний зв'язок між залежною змінною і одним із факторів при виклю-

Розрахунок лінійного коефіцієнту кореляції та оцінки параме­трів парної лінійної регресії можна здійснювати двома методами, застосувавши програмні засоби MS Excel та ППП «АРМ мене­джера торговельного підприємства».

Перший метод полягає у можливості MS Excel тиражувати формули з абсолютними, відносними і змішаними адресами.

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

Побудувавши таблицю, виконайте наступне: '

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

2. В клітинки В23, В24 занесіть формули для визначення се­редніх значень фактора (доходів на душу населення) і показника (реалізованого попиту, тобто товарообігу взуття), застосувавши статистичну функцію AVERAGE (СРЗНАЧ).

2.1. Для цього в клітинки В23 і В24 введіть, відповідно, фор­мули =AVERAGE (B2:B20), =AVERAGE (C2:C20).

2.2. В клітинку D2 введіть формулу =В2 — В$23, в клітинку Е2 — формулу =С2 — В$24,в клітинкуР2- формулу =D2 * Е2, в клітинку G2 — формулу =D2 * D2, в клітинку Н — формулу = = Е2 * Е2.

2.3. Скопіюйте ці формули у решту клітинок відповідних сто­впчиків, рядків 3 — 20.

2.4. Застосуйте інструмент автосумування і розрахуйте в клі­тинці F21 суму добутку центрованих величин показника і факто­ра. Суми квадратів центрованих величин розрахуйте в клітинках G21 та Н21. Розрахуйте суми фактора і показника і їх відхилення від середніх в клітинках В21, С21, D21, Е21.

2.5. В клітинці В25 розрахуйте лінійний коефіцієнт кореляції, використавши математичну функцію SQRT (КОРЕНЬ). Для цього внесіть в клітинку В25 формулу =F21 / SQRT(G21*H21).

Інший метод розрахунку лінійного коефіцієнта кореляції по­лягає у використанні статистичної функції CORREL (КОРРЕЛ).

Надбудова Analysis ToolPak входить в стандартну інсталяцію MS Office 97. Якщо при інсталяції пакета вона була включена в робочий комплект, то при виборі з меню операції Tools—> Add-Insна екрані з'являється вікно монітора надбудов. В ньому пере-числені всі заінстальовані на комп'ютері надбудови пакету MS Excel. Серед них бачимо Analysis ToolPak та Solver. Кожна з та­ких надбудов може бути активною (під'єднаною) або неактивною (від'єднаною). У вікні монітора надбудов активні надбудови від­мічені галочками (піднятими прапорцями, chekbox on). Будь-яку з перелічених в ньому надбудов можна активізувати, відмітивши її галочкою.

3. Поставте табличний курсор на клітинку В26. Операцією Insert —> Functionабо інструментом f_x відкрийте вікно констру­ктора функцій і виберіть функцію CORREL (КОРРЕЛ) з групи Statistical (Статистические).

3.1. В перше поле введіть блок даних фактора х (В2:В20), у друге поле занесіть дані попиту (С2:С20).

3.2. Натисніть на клавішу ОК (Готово) і в активній клітинці (В26) отримаєте значення коефіцієнта кореляції.

4.Для перевірки значимості коефіцієнта кореляції в клітинку В27 занесіть формулу обчислення розрахункового значення t-статистики. Для знаходження критичного і_ак -розподілу викорис­тайте статистичну функцію TINV (СТЬЮДРАСП).

4.1. Поставте табличний курсор на клітинку В28, де буде зна­ходитись критичне значення t_ak -розподілу.

4.2. Викличіть конструктор функцій і виберіть функцію TINV з групи Statistical (Статистические).

4.3. Введіть у перше поле рівень значимості а = і-р (р — це довірча ймовірність 0,95), в друге поле — число ступенів свободи (п - 2), тобто 17, і натисніть на клавішу ОК (Готово).

4.4. В активній клітинці (В28) одержите критичне значення t_ak-розподілу. Порівняйте його з ^-розрахунковим (клітинка В28) і зробіть відповідні висновки.

Для оцінки параметрів парної лінійної регресії побудуйте на­ступну таблицю.

Перший метод оцінки параметрів регресії базується на зда­тності MS Excel тиражуваті формули з абсолютними, віднос­ними і змішаними адресами. Для оцінки параметрів виконайте такі дії:

1. В клітинки D2 і Е2 введіть формули =В2 * С2 і =В2 * В2 та розмножте їх на блоки D3:D20, E3:E20.

Таблиця 41

РОЗРАХУНОК ПАРАМЕТРІВ ТА СТАТИСТИКИ ПАРНОЇ ЛІНІЙНОЇ РЕГРЕСІЇ

К

Позначення:

х — товарообіг РСС на душу населення, грн;

у — товарообіг взуття, грн;

N— номер підприємства.

и³⁴⁶

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

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

Для двофакторної лінійної моделі у = b + m_lx_l +m₂x₂ парамет­ри т/ і пі2 визначаються за формулами:

6. В результаті на екрані відображається виділений блок зі значенням коефіцієнта при факторі т₂ лінійної моделі в його по­чатковій клітинці; в рядку формули відображається формула з функцією LINEST та її параметрами. Натисніть на клавішу F2 для переходу курсора в рядок формули; при цьому режим роботи змінюється на Edit (режим роботи пакету відображається на па­нелі статусу).

7. Натисніть на комбінацію клавіш Ctrl+Shift+Enter. В ре­зультаті створюється масивно-значна функція (тобто функція, ре­зультатом якої є мАсив). Обчислений функцією масив значень заноситься у виділений блок.

Для відображення всього масиву виділений блок повинен ма­ти п'ять рядків і п+\ стовпчик, де п — кількість факторів. Звер­ніть увагу на те, що функція LINEST (ЛИНЕЙН) повертає коефі­цієнти регресії у послідовності, зворотній щодо їх послідовності в моделі.

8. Для визначення розрахункових і прогнозних значень попи­
ту клітинкам, в яких знаходиться регресійна статистика, надайте
їм такі імена:

Ім'я	Блок клітинок
т_2	F2
т_1	G2
ь_о	Н2
Ступінь свободи	G5

9. У клітинку J2 введіть формулу = т_2 * Площа + т_1 * До­хід + Ь_0. Цю формулу скопіюйте на блок клітинок J3:J20, в яких будуть обчислюватися розрахункові значення попиту.

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

11. Ділення коефіцієнтів регресії на їх стандартні похибки дає значення стандартизованих (нормованих) змінних t (^-статис­тики). Стандартизовані змінні показують відстані від нуля відпо­відних коефіцієнтів регресії у частках стандартних помилок. Для обчислення значень стандартизованих змінних у клітинку F13 введіть формулу =F2/F3 і розмножте на блок G13:H13.

12. Для визначення значимості стандартного відхилення ско-рикористайтеся функцією TINV (СТЬЮДРАСП). Вона визначає ймовірність отримання значення стандартизованої змінної за умови, що дійсне значення відповідного коефіцієнта регресії до­рівнює нулю. Для кожного коефіцієнта та вільного члена регресії в клітинку F14 введіть формулу

=TINV(ABS(F13); Ступсвободи)

і розмножте її на блок клітинок G14:H14. Функція ABS вико­ристовується у цій формулі для того, щоб значення першого па­раметра функції TINV було невід'ємним.

Методичні вказівки до завдання З

Побудова багатофакторних кореляційно-регресійних моделей вимагає детальної інформації про фактори, які впливають на ре­зультатну змінну (в нашому прикладі це попит). Оскільки торго­вельна статистика такої інформації не надає, тому на її базі по­трібно побудувати трендові моделі, які дадуть змогу екстра­полювати величину попиту попередніх періодів (1, 2, ..., п) на майбутні (п + 1, п + 2, ...). Цей метод дає задовільні значення ли­ше в разі короткочасного прогнозування (на квартал, рік), оскіль­ки аналізує виключно тенденцію зміни попиту в часі і не врахо­вує впливу на нього жодних чинників.

Екстраполюючи динамічний ряд попиту тією або іншою фун­кцією Дґ), можна представити його у вигляді суми двох складо­вих: тренду {у = f{t)) і випадкової величини є,, яка акумулює вплив різних факторів: y-f{t) + z_t.

Динаміку попиту звичайно моделюють такими математични­ми залежностями:

в клітинку В27 — формулу =SQRT(B25/B24). Попередньо в клітин­ці В24 за формулою =H17/(G18-1) обчисліть а²_у.

11. Перевірку значимості моделі г\ здійсніть порівнянням роз­рахункового і табличного (критичного) значення /-статистики, які обчислюються в клітинках В29 і ВЗО відповідно. Для знахо­дження критичного значення t_ak скористайтеся вбудованою ста­тистичною функцією TINV.

12. В клітинці В28 обчисліть відносну помилку апроксимації є , занесіть для цього в клітинку формулу =1/G18*Q17*100.

13. В клітинки блоку S2:T16 занесіть формули для обчислення значень нижньої і верхньої межі довірчої зони емпіричних даних і прогнозу попиту, методика розрахунку яких була описана вище.

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

Рис. 37. Лінійна діаграма динаміки попиту на цукор

На рис.37 наведено лінійну діаграму емпіричних та теоретич­них значень попиту на цукор, розрахованих за лінійною моделлю на базі торговельної статистики за 15 минулих років. На рис.38 наведено аналогічну лінійну діаграму, побудовану на базі емпі­ричних та теоретичних значень попиту на цукор за минулі роки та прогнозних значень на наступні 3 роки.

Для побудови цих діаграм рекомендується така послідов­ність дій:

1. Виділіть блок клітинок таблиці з даними про фактичну і розрахункову реалізацію цукру і застосуйте операцію Insert —> Chart, яка викликає конструктор діаграм (його можна також ви­кликати інструментом Chart Wizard).

2. У першому діалоговому вікні конструктора діаграм (Step 1 of 4) виберіть один із запропонованих класичних типів діаграми (Standard Types) або діаграму нестандартного типу (Custom Types). Для ілюстрації розв'язку даної задачі виберіть плоску лінійну ді­аграму (Lines).

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

Рис. 38. Лінійна діаграма прогнозу попиту на цукор

4. У другому діалоговому вікні конструктора діаграм (Step 2 of 4) є дві сторінки параметрів: Data Range (Дипазон данных) і Series (Ряд). Ці параметри задаються автоматично вибором обла­сті даних та типу графіка, однак, користувач може змінити їх, оскільки стандартний вибір параметрів годиться далеко не для всіх випадків і, можливо, не є найкращим.

12^М6

Поле Date Range визначає адресу області даних графіку. Пе­ремикач Series in (Ряды в) визначає орієнтацію рядів даних: дані розташовуються в рядках (Rows, Строках) чи стовпчиках (Columns, Столбцах). У полі Data Range задаються адреси рядів даних, кожен з яких відповідає лінії лінійної діаграми.

На сторінці Series (Ряд) за допомогою клавіш Add (Добавить) і Remove (Удалить) можна додати нові та знищити існуючі ряди даних. Після вибору в полі Series одного з рядів стають доступ­ними поля Name та Values, в яких можна задати параметри цього ряду. В полі Values розташовується адреса блоку даних цього ря­ду, а в полі Name — назва ряду або адреса клітинки з цією на­звою.

Поле Category (X) axis labels (Подписи оси X) призначене для адреси блоку, в якому знаходяться мітки горизонтальної осі (на­приклад, роки чи дати). За замовчуванням це поле пусте, а мітка­ми осі X служать їх номери.

5. У третьому діалоговому вікні конструктора діаграм (step З
of 4) розташовано 6 сторінок з параметрами зовнішнього вигляду
діаграми: Titles, Axes, Gridlines, Legend, Data Labels, Data Table.
Опишемо деякі важливі параметри діаграми.

Діаграма за замовчуванням ілюструється легендою — прямо­кутною рамкою з назвами рядів, яка розташовується на полях ді­аграми. Параметри легенди зібрані на сторінці Legend (Легенда): прапорець Show Legend (Добавить Легенду) визначає, чи буде ле­генда відображатися на діаграмі, перемикач Placement (Размеще­ние) визначає, на якому з полів діаграми буде відображатися ле­генда: Bottom — нижньому, Тор — верхньому, Right — правому, Left — лівому. Положення перемикача Corner означає, що леген­да буде відображатися в правому верхньому кутку діаграми.

На діаграмі можна розташувати заголовки (за замовчуванням їх немає). Параметри заголовків діаграми зібрані на сторінці Titles (Заголовки). Поле Chart Title (Название диаграммы) визна­чає назву діаграми, поле Category (X) axis — назву осі X, поле Value (Y) axis — назву осі Y.

6. На останньому, четвертому кроці конструктора діаграм (Step 4 of 4) виберіть один з двох можливих способів розташу­вання діаграми: на окремому листку (As new Sheet, Поместить ди­аграмму на отдельном листе) або на робочому листкуу вигляді зовнішнього об'єкта (As object in, Поместить диаграмму на лис­те).

7. Для завершення побудови діаграми в останньому вікні на­тисніть на клавішу Finish (Готово).

8. При побудові діаграми на робочому листку таблиці система MS Excel сама вибирає розташування та розміри вікна діаграми. Оскільки діаграма створюється як зовнішній об'єкт, то розташу­вання, розміри та інші параметри цього об'єкта можна змінити стандартними засобами Windows.

9. Для зміни параметрів побудованої діаграми можна скорис­татися операціями з меню Chart, яке стає доступним після активі­зації зовнішнього об'єкта або після активізації сторінки діаграми.

Методичні вказівки до завдань 4 та 6

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

Для цього рекомендуємо викликати MS Access з параметром — повним шляхом до бази даних. Найпростіше скопіювати вказі­вник (shortcut) на MS Access; в його полі Target (командній стрі­чці) знаходиться шлях до головного програмного модуля MS Access, наприклад, «C:\Msoff97\Office\Msaccess.exe». Скопійова­ний вказівник слід відкоректувати, доповнивши командну стріч­ку шляхом до бази даних, наприклад,

«C:\Msoff97\Office\Msaccess.exe C:\ARMMTP\ARMMTP97.MDB».