Вказівки до лабораторної роботи №9 - Інформаційна технологія розв'язку задач маркетингової діяльності підприємства
« Назад
Лабораторна робота 9. Інформаційна технологія розв'язку задач маркетингової діяльності підприємстваМета. Проробити на персональному комп'ютері основні питання організації автоматизованої обробки даних на АРМ маркетолога в умовах функціонування інтегрованої системи обробки економічної інформації на підприємствах споживчої кооперації; розвинути у студентів навики в застосуванні отриманих теоретичних знань при розв'язку на персональних комп'ютерах комплексу економічних завдань розглядуваного АРМ. Завдання для лабораторних занять і самостійної роботиЗавдання 1. На підставі таблиць 32-33 здійснити аналіз попиту населення на взуття в цілому по торгівельному о'єднанню (ОСС) за звітний період (2008-ий рік) засобами програми МЗ ЕхсеІ. Таблиця 32 ДАНІ ТОРГОВЕЛЬНОЇ СТАТИСТИКИ ПОПИТУ (ФОРМА 3-ТОРГ) (ФРАГМЕНТ, ДЛЯ ОДНОЇ ГРУПИ ТОВАРІВ)
Таблиця 33 Дані вибіркового обстеження попиту на товари (фрагмент, для одного товару)
ЗАВДАННЯ 2. На підставі даних про основні показники діяльності торговельного об'єднання (ОСС), які наведені в таблиці 34, розробити прогноз попиту населення на взуття на плановий період ('(2001 рік). Застосувати метод кореляційно-регресійного аналізу та програмні засоби MS Excel. Перевірити правильність розрахунків оцінок параметрів регресії. З надійністю р = 0,95, використовуючи г-статистику, оцінити адекватність прийнятої статистичної залежності спостережуваним даним. Побудувати з надійністю р = 0,95 прогноз попиту на взуття та його довірчий інтервал. Побудувати графіки статистичних даних та лінії регресії. Таблиця 34 ОСНОВНІ ПОКАЗНИКИ ДІЯЛЬНОСТІ ОСС
Методичні вказівки до завдання 1 Початковими даними для розв'язку задачі є статистичні дані форми 3-торг і дані вибіркового обстеження попиту на контрольно-асортиментних пунктах (КАП). Припустимо, що ми маємо як дані групової статистики для певної групи товарів (табл.. 32), так і дані вибіркового обстеження попиту на всі товари цієї групи на достатній кількості асортиментних пунктів (табл.. 33). В табл. 32-33 наведено фрагменти даних попиту на групу товарів за рік. Дані носять навчальний характер і, по суті, призначені для визначення необхідних форматів. Розв'язок задачі розбивається на послідовність етапів. Етап 1. На підставі даних з табл. 32-33 побудуємо електронні таблиці. Утворимо електронну таблицю (File - New) і запишемо на диск під іменем «Попит» (операція File —> Save—>Попит). При цьому на диску в певному каталозі створюється файл ПОПИТ.XLS. Якщо не міняти інших параметрів, то для цієї електронної таблиці буде прийнято формат книги (Workbook). Стартова кількість листків книги, як і стандартний каталог для розміщення файлів електронних таблиць, задається параметрами пакету MS Excel. (операція Tools -> Options). Занесемо початкові дані на окремі листки книги (з іменами «3-торг» та «Товар»). Етап 2. Виходячи з даних групової статистики, побудуємо квартальну групову статистику попиту. Зверніть увагу на те, що форма 3-торг пропонує групову статистику по кварталах наростаючим підсумком з початку року (табл. 32). Утворимо новий листок електронної таблиці (операція Insert —> Worksheet) і назвемо його, наприклад, «Кварт»: Format —> Sheet —> Rename —> Кварт. Скопіюємо таблицю з листка «3-торг» на листок «Кварт» через універсальний буфер Windows: відмітимо початкову таблицю, операцією Edit —> Сору скопіюємо її в буфер (при цьому система переходить в режим Сору), натисненням миші на листок «Кварт» активізуємо цей листок, активізуємо клітинку А1 і операцією Edit —> Paste витягнемо на листок вміст буфера. Дещо змінимо заголовки стовпчиків і пронумеруємо квартали. Така нумерація може бути полегшена операцією Fill: для цього в клітинку С4 занесемо номер 1, виділимо мишею блок клітинок С4:С7 і виконаємо операцію Edit —> Fill —> Series —>ОК. В клітинку D4 за допомогою миші занесемо посилання на відповідну клітинку з листка «3-торг» (тобто, формулу виду ='3-Торг'!D4). Аналогічно в клітинку D5 занесемо формулу, яка визначає залишки на кінець II кварталу через різницю значень сусідніх клітинок з листка «3-торг»: ='3-Торг'!D5-'3-Торг'!D4. Аналогічно розраховуються і клітинки D6 і D7. Розмножимо побудовані формули через універсальний буфер Windows: активізуємо клітинку D5, скопіюємо її вміст в буфер, виділимо блок D5:D7. Вміст блоку клітинок D4:D7 скопіюємо на блок Е4:Е7. Етап 3. Виходячи з даних квартальної статистики, побудуємо місячну статистику. Для цього пропорційно розділимо квартальні дані між місяцями кварталів. Утворимо новий листок таблиці (наприклад, з іменемнем «Міс») і скопіюємо на нього дані квартальної статистики. Для того, щоб при копіюванні дані не спотворювалися формульними залежностями, рекомендуємо замість операції Рaste скористатися операцією Paste Special з перемикачем Values: Edit > Сору, Edit —» Paste Special —> Values— і замість формул скопіювати їх результати. Потроїмо кожен рядок з даними. Для кожного рядка виконаємо ряд однакових дій: двома операціями Insert —> Row вставимо перед рядком з даними два пустих рядки; відмітимо рядок з даними і занесемо його в буфер операцією Edit —> Сору; відмітимо два пустих рядки; операцією Edit —» Рaste скопіюємо на них вміст буфера; графу Залишки на кінець місяця (клітинка D4) розрахуємо за формулою =Кварт!$D$4 і тиражуємо її у клітинки D5, D6 за допомогою курсору автозаповнення. Аналогічно розраховуються наступні клітинки та графа Продано за місяць. На кінець дещо змінимо заголовок та шапку таблиці як показано на рисунку. Етап 4. Таблицю даних вибіркового обстеження попиту (листок «Товар») профільтруємо за роками (даними в стовпчику «Рік»). На листку «Товар» активізуємо одну з клітинок заголовку таблиці і виконаємо операцію Data —> Filter —> AutoFilter. При цьому в кожній клітинці заголовку таблиці з'явиться список вибору. Потрібно відкрити такий список в клітинці «Рік» і вибрати один з наведених в ньому років. Відмітимо весь листок таблиці (швидка клавіша Сtrl +А), скопіюємо її в буфер (операція Edit —> Сору), утворимо новий листок таблиці (операція Insert —> Worksheet) і назвемо його «ТСорт». Виберемо з буфера на листок відфільтровані за роками дані (Edit -> Paste). Етап 5. Впорядкуємо дані. На листку «ТСорт» активізуємо одну з клітинок заголої таблиці і виконаємо операцію Data —» Sort У вікні параметрів сортування виберемо три ключі (Товар, Місяць, КАП), які дозволять впорядкувати дані спочатку за назвами товарів (алфавіті потім за місяцями, а при співпадінні обох — за зростанням номеру КАПа. Етап 6. Побудуємо проміжні підсумки. На листку «ТСорт» активізуємо одну з клітинок заголовку таблиці і виконаємо операцію Data —> Subtotals. На екрані з'явиться вікно параметрів підсумків. Виберемо в ньому з першого списку (At each change in) назву стовпчика «Місяць», з другого списку (Use function) — назву стовпчика «Sum», в третьому списку (Add subtotal to) піднімемо прапорці біля назв стовпчиків «Товарні запаси», «Продано», «Незадоволений попит». Це дозволить побудувати підсумки в стовпчиках «Товарні запаси», «Продано», «Незадоволений попит» після кожної зміни місяця — тобто по всіх КАПах за місяць.
Методичні вказівки до завдання 2 В період становлення ринкової економіки важливою умовою успішного функціонування вітчизняного підприємства є його переорієнтація на засади маркетингу. Це означає, що підприємство переорієнтовується на проектування, виробництво і збут товарів, які задовольняють попит споживачів і відповідають їхнім певним запитам. Прийняття управлінських рішень, в тому числі маркетингових рішень, базується на повній і достовірній інформації про ринок, попит на товари, уподобання споживачів, ринкові ціни, дії конкурентів, про показники діяльності об'єкта управління. Значні обсяги інформації, потреба оперативних розрахунків, пошуку та надання маркетологам відповідної інформації для прийняття рішень зумовлюють необхідність використання засобів комп'ютерної техніки та інформаційних технологій, в тому числі автоматизованих робочих місць (АРМ) спеціалістів. За допомогою АРМ маркетолога розв'язуються такі завдання, як вивчення реалізованого і незадоволеного попиту, його прогнозування, аналіз ринку і його кон'юнктури, визначення місткості і сегментації ринку. Управління маркетингом починається, насамперед, з постановки цілей, які в основному впливають на вибір шляхів розвитку маркетингової діяльності, зміст плану маркетингу й на організацію його виконання. На основі вироблених цілей здійснюється прогнозування умов і результатів розвитку маркетингової діяльності підприємства. Прогноз — це наукове передбачення ймовірних шляхів розвитку соціально-економічних явищ і процесів для більш-менш віддаленого майбутнього. Прогнозна інформація стає науковою базою планових вирішень. Розробка наукових прогнозів попиту на взуття базується на застосуванні методу вивчення великих кібернетичних систем. Особливе значення моделювання для дослідження великих систем пов'язано з обмеженням експериментування над ними. В цих умовах приходиться здійснювати досліди з моделлю системи, яка повинна бути простішою досліджуваної системи у всіх своїх аспектах. Модель — це відображення певної системи, із допомогою якої відтворюються її суттєві ознаки. Іншими словами, модель — це спрощене подання деякої системи, яке більш доступне й зручне для вивчення. Міняючи характеристики системи і досліджуючи її поведінку, модель дозволяє експериментувати із системою. Важливе місце в системі економіко-математичних моделей займають моделі прогнозування кооперативної торгівлі як галузі. Серед цих моделей особливу роль відіграють моделі прогнозування попиту, значення яких визначаються тим, що розрахунки, отримані на їх основі, є основою визначення перспективних планів, що від ступеня точності прогнозів попиту залежить обгрунтованість замовлень торгівлі на виробництво і постановку товарів народного споживання. Прогнозування попиту здійснюється за допомогою таких методів, як кореляцій-но-регресійний аналіз, трендових і авторегресивних моделей, коефіцієнта еластичності. Економіко-математична модель попиту базується на застосуванні кореляційно-регресійного аналізу, яка враховує вплив факторів, формуючих попит. В цій моделі попит виражається як функція багатьох змінних. де у — залежна змінна, яка виражає величину попиту; x1,x2,...,xn — незалежні змінні, які відповідають факторам попиту. Кореляційно-регресійний аналіз складається з таких основних етапів: побудова системи факторів, що найсуттєвіше впливають на результативну ознаку; розробка моделі, яка відображає загальний зміст взаємозв'язків, що вивчаються, та кількісна оцінка її параметрів; 1. перевірка якості моделі; 2. оцінка впливу окремих факторів. На першому етапі здійснюється відбір факторів, що суттєво впливають на результативну змінну. Для отримання надійних оцінок у модель не слід включати надто багато факторів. їхня кількість не повинна бути більшою однієї третини обсягу даних, що аналізуються. При використанні персональних комп'ютерів відбір факторів здійснюється безпосередньо в процесі створення моделі методом послідовної регресії. Суть цього методу полягає у послідовному включенні додаткових факторів у модель та оцінці впливу доданого фактора. Використовується також підхід, за якого на фактори, що включаються у попередній склад моделі, не накладається особливих обмежень і лише на наступних стадіях проводиться їхнє оцінювання та відбір. Відбір факторів, тобто встановлення тісноти зв'язку, яка існує між показниками, наприклад, попитом і грошовими доходами, здійснюють на основі обчислення прямолінійного коефіцієнта кореляції: де у — середньодушовий попит на товари народного споживання; х — величина фактора, впливаючого на величину попиту (наприклад, середньодушовий дохід, ціна товару і т.д.); х, у — середні значення корелюючих величин по вивчаючій сукупності; хі , yі— значення факторів х,у в і-ому спостереженні (і = 1, 2,..., n). Розрахований прямолінійний коефіцієнт кореляції, здобутий за вибірковими даними, є випадковою величиною, яка залежить від вибірки. Тому доцільно зробити перевірку гіпотези про відсутність кореляційного зв'язку між випадковими величинами х та у. Перевіряється нульова гіпотеза Н0 : r[х, у] = 0 і альтернативна гіпотеза Н1 : r[х, у] ≠ 0. Якщо випадкові величини х і у розподілені за нормальним законом, то обчислюється і-статистика за формулою. Ця формула має розподіл Стьюдента з к=n-2 ступенями свободи. Для заданої ймовірності р і ступенів свободи к знаходиться табличне значення tрк — статистики. Якщо t≥ tрк, то із заданою надійністю р приймається гіпотеза Н1 про наявність кореляційного зв'язку між випадковими величинами х та у (між попитом і грошовими доходами). Якщо t <tрк, то приймається гіпотеза Н0. В цьому випадку можна говорити, що з надійністю р кореляційний зв'язок між випадковими величинами х, у відсутній. Парний коефіцієнт кореляції характеризує зв'язок між залежною змінною і одним із факторів (наприклад, між попитом і доходами, без урахування цін і товарообороту з 1 м2 складської площі). В цьому випадку необхідно визначити частинний коефіцієнт кореляції. Частинний коефіцієнт кореляції характеризує кореляційний зв'язок між залежною змінною і одним із факторів при виключенні впливу інших факторів. Для лінійної двофакторної регресійної моделі коефіцієнт х2rух1 характеризує вплив на у фактора х1 при виключенні впливу х2 і розраховується за формулою. Сукупність впливу всіх вибраних факторів на попит визначають за допомогою коефіцієнта множинної кореляції, який завжди додатній і менший від 1. Коефіцієнт множинної кореляції у випадку двох чинників визначають через коефіцієнти парної кореляції за формулою. Істотність коефіцієнта множинної кореляції перевіряють за Р-критерієм Фішера. Розрахункове значення F' порівнюють з теоретичним, яке визначають з таблиці при заданому рівні надійності р і числа ступенів свободи V1 = m V2=n-m-1. Якщо розрахункове значення Fр перевищує табличне, то гіпотезу про неістотність коефіцієнта множинної кореляції відхиляють. Для оцінки зв'язку при нелінійній формі залежності застосовується множинне кореляційне відношення, яке визначається за формулою. де y1, у емпіричне і теоретичне значення попиту в і-му спостереженні. Значимість множинного кореляційного відношення перевіряють за і-критерієм, який характеризується розподілом Стьюдента із V= n- m- 1 ступенями свободи де ση середньоквадратична помилка множинного кореляційного відношення, яку визначають за формулою. Другий етап починається з розробки моделі, яка відображає загальний зміст взаємозв'язків, що вивчаються. Регресійна модель — це рівняння або система рівнянь, що показує, які фактори мають бути залучені до взаємозв'язків, що підлягають аналізу. Регресійне рівняння дає уявлення про форму зв'язку, в основу виявлення якої покладено використання наступних математичних функцій: - лінійна; - квадратична; - експоненціальна; - степенева; - де у — розрахункове значення середньодушового попиту на товари народного споживання; - х — величина фактора, який впливає на величину попиту (наприклад, середньодушовий дохід, ціна товару і т.д.); а ,b, с — параметри рівняння. Параметри рівняння найчастіше обчислюються методом найменших квадратів. Критерієм оцінки форми рівняння служить близькість теоретичних і фактичних значень попиту. Для рівняння виду у=а+bх випадку незгрупованих даних параметри визначаються таким чином. Інтервальні оцінки параметрів парної лінійної регресії здійснюється за допомогою середнього квадратичного відхилення. Так, середнє квадратичне відхилення σ [а] параметра а обчислюється за формулою. Якщо відхилення розподілені за нормальним законом, то довірчий інтервал для параметра а визначають як [а - ∆а, a+∆a] ∆а = tak де tак — значення і-критерію, який визначається за даним значенням рівня значущості α = 1-р і числом ступенів свободи k = n-2. Середнє квадратичне відхилення параметра b обчислюється за формулою. Довірчий інтервал для параметра b визначається як [b-∆b, b + ∆b],де ∆b= tаk σ [b]. Прогнозне значення попиту на взуття буде знаходитись в певних межах, величини яких визначаються за допомогою середнього квадратичного відхилення. Надійний інтервал для ŷі , визначається за формулою. При визначенні дисперсії показника ур необхідно врахувати розсіяння навколо лінії регресії, яка визначається за формулою. Замінюючи σ його точковою оцінкою S, запишемо межі довірчих інтервалів індивідуальних прогнозних значень. Розрахунок лінійного коефіцієнту кореляції та оцінки параметрів парної лінійної регресії можна здійснювати двома методами, застосувавши програмні засоби MS Excel. Перший метод полягає у можливості MS Excel тиражувати формули з абсолютними, відносними і змішаними адресами. Для розрахунку коефіцієнта кореляції побудуйте таблицю наступного виду (табл. 35): Таблиця 35 Розрахунок коефіцієнта кореляції
Побудувавши таблицю, виконайте наступне: 1. Внесіть заголовки таблиці, стовпчиків, введіть дані і відформатуйте таблицю. 2. В клітинки В23, В24 занесіть формули для визначення середніх • Для цього в клітинки В23 і В24 введіть, відповідно, формули AVERAGE (В2:В20), =AVERAGE (С2:С20). • В клітинку D2 введіть формулу =В2 — В$23, в клітинку Е2 — формулу =С2 — В$24,в клітинку F2 - формулу =D2 * Е2, в клітинку G2 — формулу =D2 * D2, в клітинку Н — формулу = Е2 * Е2. Скопіюйте ці формули у решту клітинок відповідних стовпчиків, рядків 3 — 20. Застосуйте інструмент автосумування і розрахуйте в клітинці F21 суму добутку центрованих величин показника і фактора. Суми квадратів центрованих величин розрахуйте в клітинках G21 та Н21. Розрахуйте суми фактора і показника і їх відхилення від середніх в клітинках В21, С21, D21, Е21. • В клітинці В25 розрахуйте лінійний коефіцієнт кореляції, використавши математичну функцію Sqrt (КОРЕНЬ). Для цього внесіть в клітинку В25 формулу =F21 / SQRT(G21*Н21). Інший метод розрахунку лінійного коефіцієнта кореляції полягає у використанні статистичної функції CORREL (КОРРЕЛ). Надбудова Analysis ToolPak входить в стандартну інсталяцію МS Office. Якщо при інсталяції пакета вона була включена в робочий комплект, то при виборі з меню операції Тооls —» Add-Ins на екрані з'являється вікно монітора надбудов. В ньому перечисленi всі заінстальовані на комп'ютері надбудови пакету MS Excel. Серед них бачимо Analysis ToolPak та Solver. Кожна з таких надбудов може бути активною (під'єднаною) або неактивною (від'єднаною). У вікні монітора надбудов активні надбудови відмічені галочками (піднятими прапорцями, checkbox on). Будь-яку з перелічених в ньому надбудов можна активізувати, відмітивши її галочкою. 3. Поставте табличний курсор на клітинку В26. Операцією Insert —» Function або інструментом fх відкрийте вікно конструктора функцій і виберіть функцію CORREL (КОРРЕЛ) з групи Statistical (Статистические): В перше поле введіть блок даних фактора х (В2:В20), у друге поле занесіть дані попиту (С2:С20). Натисніть на клавішу ОК (Готово) і в активній клітинці (В26) отримаєте значення коефіцієнта кореляції. 4.Для перевірки значимості коефіцієнта кореляції в клітинку В27 занесіть формулу обчислення розрахункового значення t-статистики. Для знаходження критичного tак-розподілу використайте статистичну функцію TINV (СТЬЮДРАСП): Поставте табличний курсор на клітинку В28, де буде знаходитись критичне значення tак -розподілу. Викличіть конструктор функцій і виберіть функцію ТINV з групи Statistical (Статистические). Введіть у перше поле рівень значимості α = і-р (р — це довірча ймовірність 0,95), в друге поле — число ступенів свободи (n - 2), тобто 17, і натисніть на клавішу ОК (Готово). В активній клітинці (В28) одержите критичне значення tак -розподілу. Порівняйте його з t-розрахунковим (клітинка В28) і зробіть відповідні висновки. Для оцінки параметрів парної лінійної регресії побудуйте наступну таблицю. Перший метод оцінки параметрів регресії базується на здатності М8 Ехсеї тиражуваті формули з абсолютними, відносними і змішаними адресами. Для оцінки параметрів виконайте такі дії: 1. В клітинки D2 і Е2 введіть формули =В2 * С2 і =В2 * В2 та розмножте їх на блоки Б3:Б20, ЕЗ:Е20. 2. За допомогою функції Sum (СУММ) в клітинці В22 побудуйте підсумок хі розмножте цю формулу в клітинки С22:G22. 3. В клітинку В23 введіть формулу для визначення розміру вибірки: =СOUNT(А2:А20). В клітинку В24 введіть формулу =(В23 * Б22 - В22 * С22) / (В23 * Е22 - В22 * В22) для оцінки параметра α. 4. Для знаходження параметра b в клітинку В25 введіть формулу =(С22 — В24*В22) / В23. Другий метод оцінки параметрів полягає у використанні статистичної функції Linest (ЛИНЕЙН). Для цього необхідно: 1. Виділити блок D24:Е28, де мають знаходитись розрахункові дані: Відкрити діалогове вікно конструктора функцій, вибрати функцію LINEST (ЛИНЕЙН) з категорії Statistical (Статистические) і натиснути на клавішу ОК (Далее). У другому діалоговому вікні ввести: в перший рядок (в перше поле) адресу блока даних попиту (С2:С20 або ім'я цього блока даних); у другий рядок — адресу блока даних факторів (ім'я цього блока); в третій рядок — слово TRUE (ИСТИНА або 1), якщо b не дорівнює нулю і слово FALSE (ЛОЖЬ або 0), якщо b дорівнює нулю; в четвертий рядок — слово TRUE (ИСТИНА або 1), якщо необхідно знайти не лише параметри лінії регресії, а й додаткову регресійну статистику. Якщо необхідно знайти лише параметри лінії регресії, то вводимо слово FALSE (ЛОЖЬ або 0), і натискаємо на клавішу ОК (Готово) для одержання результатів. 4. Для занесення у блок функцій, які формують не одне значення, а цілий масив різних даних, натискаємо клавішу Р2, потім комбінацію клавіш Сtrl + Shift + Еnter. Опишемо результати: • У першому рядочку клітинок (D24, Е24) знаходяться значення параметрів лінійної регресії, відповідно, а і b. У другому рядку блока в клітинках (D25, Е25) знаходяться середні квадратичні відхилення параметрів σb ,σа. У третьому рядку блока в клітинці D26 знаходиться коефіцієнт детермінації, а в клітинці Е26 — середнє квадратичне відхилення показника у відносно лінії регресії. У четвертому рядку блока в клітинці D27 знаходиться розрахункове значення F-статистики, а в клітинці Е27 число ступенів свободи (К). У п'ятому рядку блоку в клітинці D28 знаходиться сума квадратів відхилень розрахункових значень показника від його середнього значення, а в клітинці Е28 — сума квадратів залишків. Таким чином, для розрахунку параметрів регресії і додаткової регресійної статистики необхідно в блок D24:Е28 внести формулу =LINEST(С2:С20; В2:В20, 1, 1). Для знаходження розрахункових значень попиту необхідно: 1. В клітинку F2 ввести формулу = $D$24 * В2 + $Е$24. 2. Для введення цієї формули активізуйте клітинку F2, поставте знак =, активізуйте клітинку В24, де знаходиться значення параметра а, і натисніть на клавішу F4. Створена абсолютна адреса клітинки $D$24 при тиражуванні по стовпчику F3: Е22 залишатиметься без зміни. Аналогічним способом вводиться абсолютна адреса клітинки $Е$24, де знаходиться оцінка параметра b. Визначення довірчого півінтервалу, де (і = 1, 2,... n), здійснюється за допомогою статистичних функцій АVERAGE (СРЗНАЧ), VARP (ДИСПР), TINV (СТЬЮДРАСП). Критичне значення розподілу Стьюдента визначається з використанням статистичної функції ТШУ: в клітинку В20 вводиться формула =TINV(0,05; В23-2). При застосуванні конструктора функцій у першому полі діалогового вікна вказується рівень значимості α = 1-Р (р — довірча ймовірність), у другому полі — число ступенів свободи к‘= 19 - 2 = 17. В клітинку В30 вноситься формула =VARP(B2:B19). При обчисленні дисперсії у діалоговому вікні конструктора функцій вказується діапазон клітинок, в яких знаходяться значення величини, дисперсію якої необхідно знайти. В клітинку Н2 заносимо формулу =$В$30 * $Е$26 * SQRT (В23 * (1+(В2 — $В$27)^2/$В$30)) і копіюємо її в решту клітинок блоку НЗ:Н19. При побудові формули необхідно використати математичну функцію SQRT(КОРЕНЬ). В клітинки I2, J2 вносяться відповідно формули =F2 - Н2 і =F2 + Н2, з допомогою яких визначаються нижні і верхні межі довірчої зони базисних даних попиту і його прогнозу. Ці формули копіюємо на діапазони клітинок I3:I21, J3:J21. Попит покупця, як показують дані досліджень, формується під впливом багатьох факторів. При цьому сила і напрямок їх дії бувають різноманітними. Кількісний взаємозв'язок попиту і факторів, що на нього впливають, описується такими рівняннями множинної регресії: Вибір факторів, що формують попит, здійснюється за допомогою графіків функцій, парних та частинних коефіцієнтів кореляції. Параметри рівняння регресії визначають методом найменших квадратів. Для лінійної множинної регресії параметри b, m1, m2, …, mn визначаються методом найменших квадратів шляхом розв'язку такої системи нормальних рівнянь. Визначити параметри множинної регресії можна через систему нормальних рівнянь у матричній формі: де [X] — це матриця коефіцієнтів нормальної системи рівнянь, а — вектор її змінних, у — вектор її правих частин, Якщо визначник матриці, елементами якої є коефіцієнти при невідомих а0, a1, …, am відмінний від нуля, то система нормальних рівнянь має єдиний розв'язок. Якщо визначник матриці X відмінний від нуля, то існує матриця, обернена до X і система нормальних рівнянь має єдиний розв'язок. Домноживши матричне рівняння зліва на обернену матрицю, одержимо вектор оцінок параметрів а . Для двофакторної лінійної моделі у = b + mxxx + m2х2 параметри m1 і m2 визначаються за формулами: де , σx, σх1, σx2— середні квадратичні відхилення результатної змінної у та факторів х1 і х2; rх1у, rх2у, rх1х2 — парні коефіцієнти кореляції, які характеризують близькість зв'язку між попитом у і доходом на душу населення х і, між у і товарообігом х2 і між самими факторами x1, х2. Параметри множинної лінійної регресії можна розрахувати за допомогою статистичної функції LINEST (ЛИНЕЙН). Розрахунок рекомендується здійснити за такою схемою: 1. Побудуйте електронну таблицю за наступним зразком. 2. Введіть початкові дані, відформатуйте таблицю. Для того, щоб подальші маніпулювання з даними були більш наочними, надайте їм імена, які наведені нижче:
Для присвоєння імен клітинкам чи діапазонам, скористайтеся командою Insert —> Name —> Define(Вставка —> Имя —> Присвоить). 3. Виділіть діапазон клітинок, в якому буде розташовано результати 4. Виконайте команду Insert —> Function (Вставка —> Функция). В діалоговому вікні конструктора функцій виберіть функцію Linest(ЛИНЕЙН) з категорії Statistical(Статистические) і натисніть на клавішу ОК. 5. У другому діалоговому вікні конструктора функцій введіть Замість вводу імен діапазонів з клавіатури можна вибирати їх зі списку імен операцією Insert —> Name —> Рaste. На екрані з'явиться список всіх імен таблиці; з цього списку потрібно вибрати ім'я діапазону і натиснути на клавішу ОК. Для завершення вводу параметрів функції натисніть на клавішу ОК. 6. В результаті на екрані відображається виділений блок зі значенням коефіцієнта при факторі m2 лінійної моделі в його початковій клітинці; в рядку формули відображається формула з функцією LINSET та її параметрами. Натисніть на клавішу F2 для переходу курсора в рядок формули; при цьому режим роботи змінюється на Edit (режим роботи пакету відображається на панелі статусу). 7. Натисніть на комбінацію клавіш Сtrl+Shift+Еnter. В результаті Для відображення всього масиву виділений блок повинен мати п'ять рядків і n+1 стовпчик, де n — кількість факторів. Зверніть увагу на те, що функція LINSET (ЛИНЕЙН) повертає коефіцієнти регресії у послідовності, зворотній щодо їх послідовності в моделі. 8. Для визначення розрахункових і прогнозних значень попиту клітинкам, в яких знаходиться регресійна статистика, надайте їм такі імена:
9. У клітинку 12 введіть формулу = m_2 * Площа + m_1 * Дохід + b_0. Цю формулу скопіюйте на блок клітинок j3:j20, в яких будуть обчислюватися розрахункові значення попиту. 10. Для перевірки значимості коефіцієнтів регресії розгляньте гіпотезу про те, що ні дохід на душу населення, ні розмір торговельної площі залу не впливають на обсяги попиту на задану групу товарів, яка називається нуль-гіпотезою. Для її перевірки використайте розраховані функцією LINEST стандартні похибки. 11. Ділення коефіцієнтів регресії на їх стандартні похибки дає значення стандартизованих (нормованих) змінних t(-статистики). Стандартизовані змінні показують відстані від нуля відповідних коефіцієнтів регресії у частках стандартних помилок. Для обчислення значень стандартизованих змінних у клітинку F13 введіть формулу =F2/F3 і розмножте на блок G13:H13. 12. Для визначення значимості стандартного відхилення ско-
Контрольні питання
З повагою ІЦ "KURSOVIKS"! |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||