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».