Методичні рекомендації до лабораторної роботи 9 на тему Інформаційна технологія розв'язку задач маркетингової діяльності підприємства, ХНТУ
« Назад
Лабораторна робота 9. Інформаційна технологія розв'язку задач маркетингової діяльності підприємства
Мета. Проробити на персональному комп'ютері основні питання організації автоматизованої обробки даних на АРМ маркетолога в умовах функціонування інтегрованої системи обробки економічної інформації на підприємствах споживчої кооперації; розвинути у студентів навики в застосуванні отриманих теоретичних знань при розв'язку на персональних комп'ютерах комплексу економічних завдань розглядуваного АРМ Завдання для лабораторних занять і самостійної роботиЗавдання 1. На підставі таблиць 32-33 здійснити аналіз попиту населення на взуття в цілому по торгівельному о'єднанню (ОСС) за звітний період (2008-ий рік) засобами програми МЗ ЕхсеІ. Таблиця 32 ДАНІ ТОРГОВЕЛЬНОЇ СТАТИСТИКИ ПОПИТУ (ФОРМА 3-ТОРГ) ФРАГМЕНТ, ДЛЯ ОДНОЇ ГРУПИ ТОВАРІВ)
ЗАВДАННЯ 2. На підставі даних про основні показники діяльності торговельного об'єднання (ОСС), які наведені в таблиці 34, розробити прогноз попиту населення на взуття на плановий період ('(2001 рік). Застосувати метод кореляційно-регресійного аналізу та програмні засоби М8 Ехсеї. Перевірити правильність розрахунків оцінок параметрів регресії. З надійністю р = 0,95, використовуючи г-статистику, оцінити адекватність прийнятої статистичної залежності спостережуваним даним. Побудувати з надійністю р = 0,95 прогноз попиту на взуття та його довірчий інтервал. Побудувати графіки статистичних даних та лінії регресії. Таблиця 34 ОСНОВНІ ПОКАЗНИКИ ДІЯЛЬНОСТІ ОСС
Методичні вказівки до завдання 1Початковими даними для розв'язку задачі є статистичні дані форми 3-торг і дані вибіркового обстеження попиту на контрольно-асортиментних пунктах (КАП). Припустимо, що ми маємо як дані групової статистики для певної групи товарів (табл.. 32), так і дані вибіркового обстеження попиту на всі товари цієї групи на достатній кількості асортиментних пунктів (табл.. 33). В табл. 32-33 наведено фрагменти даних попиту на групу товарів за рік. Дані носять навчальний характер і, по суті, призначені для визначення необхідних форматів. Розв'язок задачі розбивається на послідовність етапів. Етап 1. На підставі даних з табл. 32-33 побудуємо електронні таблиці. Утворимо електронну таблицю (Ріїе —» №лу) і запишемо на диск під іменем «Попит» (операція Ріїе —> 8ауе—>Попит). При цьому на диску в певному каталозі створюється файл ПОПИТ.ХЬ8. Якщо не міняти інших параметрів, то для цієї електронної таблиці буде прийнято формат книги (ЖогкЬоок). Стартова кількість листків книги, як і стандартний каталог для розміщення файлів електронних таблиць, задається параметрами пакету М8 Ехсеї (операція ТооІ8 -> Орпош). Занесемо початкові дані на окремі листки книги (з іменами «3-торг» та «Товар»): Етап 2. Виходячи з даних групової статистики, побудуємо квартальну групову статистику попиту. Зверніть увагу на те, що форма 3-торг пропонує групову статистику по кварталах наростаючим підсумком з початку року (табл. 32). Утворимо новий листок електронної таблиці (операція Іп8егг —> \Уогк8пеет) і назвемо його, наприклад, «Кварт»: Рогтаї —> 8Ьее1 —> Кепаше —> Кварт. Скопіюємо таблицю з листка «3-торг» на листок «Кварт» через універсальний буфер \Уіпсіолу8: відмітимо початкову таблицю, операцією Е(Ш —> Сору скопіюємо її в буфер (при цьому система переходить в режим Сору), натисненням миші на листок «Кварт» активізуємо цей листок, активізуємо клітинку А1 і операцією Е(Ш —> Ра8їе витягнемо на листок вміст буфера. Дещо змінимо заголовки стовпчиків і пронумеруємо квартали. Така номерація може бути полегшена операцією РІІІ: для цього в клітинку С4 занесемо номер 1, виділимо мишею блок клітинок С4:С7 і виконаємо операцію Е(Ш —> РШ —> 8егіе8 —> ->ОК: В клітинку Б4 за допомогою миші занесемо посилання на відповідну клітинку з листка «3-торг» (тобто, формулу виду ='3-Торг'!Б4). Аналогічно в клітинку Б5 занесемо формулу, яка визначає залишки на кінець II кварталу через різницю значень сусідніх клітинок з листка «3-торг»: ='3-Торг'!Б5-'3-Торг'!Б4. Аналогічно розраховуються і клітинки Б6і Б7. Розмножимо побудовані формули через універсальний буфер \Уіпсіолу8: активізуємо клітинку Б5, скопіюємо її вміст в буфер, виділимо блок Б5:Б7. Вміст блоку клітинок Б4:Б7 скопіюємо на блок Е4:Е7. Етап 3. Виходячи з даних квартальної статистики, побудуємо місячну статистику. Для цього пропорційно розділимо квартальні дані між місяцями кварталів. Утворимо новий листок таблиці (наприклад, з іменемнем «Міс») і скопіюємо на нього дані квартальної статистики. Для того, щоб при копіюванні дані не спотворювалися формульними залежностями, рекомендуємо замість операції Разіе скорис татися операцією Разіе 8ресіа1 з перемикачем Уашез: Ес(1і > Сору, Е(Ш —» Ра8Іе 8рееіа1 —> УаІие»,— і замість формул скопіювати їх результати: Потроїмо кожен рядок з даними. Для кожного рядка виконаємо ряд однакових дій: двома операціями Іп8егї; —> Ко\у вставимо перед рядком з даними два пустих рядки; відмітимо рядок з даними і занесемо його в буфер операцією Е(Ш —> Сору; відмітимо два пустих рядки; операцією Е(Ш —» Ра8Ї;е скопіюємо на них вміст буфера; графу Залишки на кінець місяця (клітинка Б4) розрахуємо за формулою =Кварт!$Б$4 і тиражуємо її у клітинки Б5, Б6 за допомогою курсору автозаповнення. Аналогічно розраховуються наступні клітинки та графа Продано за місяць. Накінець дещо змінимо заголовок та шапку таблиці як показано на рисунку. Етап 4. Таблицю даних вибіркового обстеження попиту (листок «Товар») профільтруємо за роками (даними в стовпчику «Рік»). На листку «Товар» активізуємо одну з клітинок заголовку таблиці і виконаємо операцію Баїа —> РШег —> АиіоРШег. При цьому в кожній клітинці заголовку таблиці з'явиться список вибору. Потрібно відкрити такий список в клітинці «Рік» і вибрати один з наведених в ньому років: Відмітимо весь листок таблиці (швидка клавіша Сггі +А), скопіюємо її в буфер (операція Е(Ш —> Сору), утворимо новий листок таблиці (операція Іп8егг —> \Уогк8пеет) і назвемо його «ТСорт». Виберемо з буфера на листок відфільтровані за роками дані (Е(Ш -> Ра8Іе). Етап 5. Впорядкуємо дані. На листку «ТСорт» активізуємо одну з клітинок заголої таблиці і виконаємо операцію Баїа —» 8ог1 У вікні параметрів сортування виберемо три ключі (Товар, Місяць, КАП), які доз лять впорядкувати дані спочатку за назвами товарів (алфавіті потім за місяцями, а при співпадінні обох — за зростанням ноі ру КАПа: Етап 6. Побудуємо проміжні підсумки. На листку «ТСорт» активізуємо одну з клітинок заголовку таблиці і виконаємо операцію Баїа —> 8иЬ1о1аІ8. На екрані з'явиться вікно параметрів підумків. Виберемо в ньому з першого списку (Аі васк скапдв іп) назву стовпчика «Місяць», з другого списку (17хв /ипсііоп) — назву стовпчика «8ит», в третьому списку (АМ 8иЬіоіаї іо) піднімемо прапорці біля назв стовпчиків «Товарні запаси», «Продано», «Незадоволений попит». Це дозволить побудувати підсумки в стовпчиках «Товарні запаси», «Продано», «Незадоволений попит» після кожної зміни місяця — тобто по всіх КАПах за місяць:
Методичні вказівки до завдання 2В період становлення ринкової економіки важливою умовою успішного функціонування вітчизняного підприємства є його переорієнтація на засади маркетингу. Це означає, що підприємство переорієнтовується на проектування, виробництво і збут товарів, які задовольняють попит споживачів і відповідають їхнім певним запитам. Прийняття управлінських рішень, в тому числі маркетингових рішень, базується на повній і достовірній інформації про ринок, попит на товари, уподобання споживачів, ринкові ціни, дії конкурентів, про показники діяльності об'єкта управління. Значні обсяги інформації, потреба оперативних розрахунків, пошуку та надання маркетологам відповідної інформації для прийняття рішень зумовлюють необхідність використання засобів комп'ютерної техніки та інформаційних технологій, в тому числі автоматизованих робочих місць (АРМ) спеціалістів. За допомогою АРМ маркетолога розв'язуються такі завдання, як вивчення реалізованого і незадоволеного попиту, його прогнозування, аналіз ринку і його кон'юнктури, визначення місткості і сегментації ринку. Управління маркетингом починається, насамперед, з постановки цілей, які в основному впливають на вибір шляхів розвитку маркетингової діяльності, зміст плану маркетингу й на організацію його виконання. На основі вироблених цілей здійснюється прогнозування умов і результатів розвитку маркетингової діяльності підприємства. Прогноз — це наукове передбачення ймовірних шляхів розвитку соціально-економічних явищ і процесів для більш-менш віддаленого майбутнього. Прогнозна інформація стає науковою базою планових вирішень. Розробка наукових прогнозів попиту на взуття базується на застосуванні методу вивчення великих кібернетичних систем. Особливе значення моделювання для дослідження великих систем пов'язано з обмеженням експериментування над ними. В цих умовах приходиться здійснювати досліди з моделлю системи, яка повинна бути простішою досліджуваної системи у всіх своїх аспектах. Модель — це відображення певної системи, із допомогою якої відтворюються її суттєві ознаки. Іншими словами, модель — це спрощене подання деякої системи, яке більш доступне й зручне для вивчення. Міняючи характеристики системи і досліджуючи її поведінку, модель дозволяє експериментувати із системою. Важливе місце в системі економіко-математичних моделей займають моделі прогнозування кооперативної торгівлі як галузі. Серед цих моделей особливу роль відіграють моделі прогнозування попиту, значення яких визначаються тим, що розрахунки, отримані на їх основі, є основою визначення перспективних планів, що від ступеня точності прогнозів попиту залежить обгрунтованість замовлень торгівлі на виробництво і постановку товарів народного споживання. Прогнозування попиту здійснюється за допомогою таких методів, як кореляцій-но-регресійний аналіз, трендових і авторегресивних моделей, коефіцієнта еластичності. Економіко-математична модель попиту базується на застосуванні кореляційно-регресійного аналізу, яка враховує вплив факторів, формуючих попит. В цій моделі попит виражається як функція багатьох змінних: де у — залежна змінна, яка виражає величину попиту; хих2,...,хп — незалежні змінні, які ввідповідають факторам попиту. Кореляційно-регресійний аналіз складається з таких основних етапів: ♦ побудова системи факторів, що найсуттєвіше впливають на результативну ознаку; ♦ розробка моделі, яка відображає загальний зміст взаємозв'язків, що вивчаються, та кількісна оцінка її параметрів; 1. перевірка якості моделі; 2. оцінка впливу окремих факторів. На першому етапі здійснюється відбір факторів, що суттєво впливають на результативну змінну. Для отримання надійних оцінок у модель не слід включати надто багато факторів. їхня кількість не повинна бути більшою однієї третини обсягу даних, що аналізуються. При використанні персональних комп'ютерів відбір факторів здійснюється безпосередньо в процесі створення моделі методом послідовної регресії. Суть цього методу полягає у послідовному включенні додаткових факторів у модель та оцінці впливу доданого фактора. Використовується також підхід, за якого на фактори, що включаються у попередній склад моделі, не накладається особливих обмежень і лише на наступних стадіях проводиться їхнє оцінювання та відбір. Відбір факторів, тобто встановлення тісноти зв'язку, яка існує між показниками, наприклад, попитом і грошовими доходами, здійснюють на основі обчислення прямолінійного коефіцієнта кореляції: де у — середньодушовий попит на товари народного споживання; х — величина фактора, впливаючого на величину попиту (наприклад, середньодушовий дохід, ціна товару і т.д.); х, у — середні значення корелюючих величин по вивчаючій сукупності; хі , Уі— значення факторів х,у в і-ому спостереженні (і = 1, 2,..., п). Розрахований прямолінійний коефіцієнт кореляції, здобутий за вибірковими даними, є випадковою величиною, яка залежить від вибірки. Тому доцільно зробити перевірку гіпотези про відсутність кореляційного зв'язку між випадковими величинами х та у. Перевіряється нульова гіпотеза Н0 : г[х, у] = 0 і альтернативна гіпотеза Ні : г[х, у] Ф 0. Якщо випадкові величини х і у розподілені за нормальним законом, то обчислюється і-статистика за формулою. Ця формула має розподіл Стьюдента з к=п-2 ступенями свободи. Для заданої ймовірності р і ступенів свободи к знаходиться табличне значення і рк — статистики. Якщо і> ірк, то із заданою надійністю р приймається гіпотеза Н1 про наявність кореляційного зв'язку між випадковими величинами х та у (між попитом і грошовими доходами). Якщо і <ір к, то приймається гіпотеза Н0. В цьому випадку можна говорити, що з надійністю р кореляційний зв'язок між випадковими величинами х, у відсутній. Парний коефіцієнт кореляції характеризує зв'язок між залежною змінною і одним із факторів (наприклад, між попитом і доходами, без урахування цін і товарообороту з 1 м складської площі). В цьому випадку необхідно визначити частинний коефіцієнт кореляції. Частинний коефіцієнт кореляції характеризує кореляційний зв'язок між залежною змінною і одним із факторів при виключенні впливу інших факторів. Для лінійної двофакторної регре-сійної моделі коефіцієнт х2гух1 характеризує вплив на у фактора х1 при виключенні впливу х2 і розраховується за формулою: Сукупність впливу всіх вибраних факторів на попит визначають за допомогою коефіцієнта множинної кореляції, який завжди додатній і менший від 1. Коефіцієнт множинної кореляції у випадку двох чинників визначають через коефіцієнти парної кореляції за формулою: Істотність коефіцієнта множинної кореляції перевіряють за Р-критерієм Фішера: Розрахункове значення Р' порівнюють з теоретичним, яке визначають з таблиці при заданому рівні надійності р і числа ступенів свободи У1 = ті V2=п-т-1. Якщо розрахункове значення Рр перевищує табличне, то гіпотезу про неістотність коефіцієнта множинної кореляції відхиляють. Для оцінки зв'язку при нелінійній формі залежності застосовується множинне кореляційне відношення, яке визначається за формулою: де У1 у емпіричне і теоретичне значення попиту в і-му спостереженні. Значимість множинного кореляційного відношення перевіряють за і-критерієм, який характеризується розподілом Стьюдента із V= п- т- 1 ступенями свободи де ац середньоквадратична помилка множинного кореляційного відношення, яку визначають за формулою: Другий етап починається з розробки моделі, яка відображає загальний зміст взаємозв'язків, що вивчаються. Регресійна модель — це рівняння або система рівнянь, що показує, які фактори мають бути залучені до взаємозв'язків, що підлягають аналізу. Регресійне рівняння дає уявлення про форму зв'язку, в основу виявлення якої покладено використання наступних математичних функцій: де у — розрахункове значення середньодушового попиту на товари народного споживання; х — величина фактора, який впливає на величину попиту (наприклад, середньодушовий дохід, ціна товару і т.д.); а ,Ь,с — параметри рівняння. Параметри рівняння найчастіше обчислюються методом найменших квадратів. Критерієм оцінки форми рівняння служить близькість теоретичних і фактичних значень попиту. Ця умова подається наступним чином: де уі — фактичне значення попиту на взуття; уі — його розрахункове значення. Для рівняння виду у=а+Ьх випадку незгрупованих даних параметри визначаються таким чином: Інтервальні оцінки параметрів парної лінійної регресії здійснюється за допомогою середнього квадратичного відхилення. Так, середнє квадратичне відхилення а [а] параметра а обчислюється за формулою: Якщо відхилення розподілені за нормальним законом, то довірчий інтервал для параметра а визначають як [а - д«, а + Аа}, Аа = іс& де іак — значення і-критерію, який визначається за даним значенням рівня значущості а = \-р і числом ступенів свободик = п-2. Середнє квадратичне відхилення параметра Ь обчислюється за формулою: Довірчий інтервал для параметра Ь визначається як [Ь-АЬ, Ь + АЬ],де &Ь = іак а [Ь]. Прогнозне значення попиту на взуття буде знаходитись в певних межах, величини яких визначаються за допомогою середнього квадратичного відхилення: Надійний інтервал для уі , визначається за формулою. При визначенні дисперсії показника ур необхідно врахувати розсіяння навколо лінії регресії, яка визначається за формулою: Замінюючи а його точковою оцінкою £, запишемо межі довір- чих інтервалів індивідуальних прогнозних значень: Розрахунок лінійного коефіцієнту кореляції та оцінки параметрів парної лінійної регресії можна здійснювати двома методами, застосувавши програмні засоби М8 ЕхсеІ. Перший метод полягає у можливості М8 ЕхсеІ тиражувати формули з абсолютними, відносними і змішаними адресами. Для розрахунку коефіцієнта кореляції побудуйте таблицю наступного виду (табл. 35): Побудувавши таблицю, виконайте наступне: 1. Внесіть заголовки таблиці, стовпчиків, введіть дані і відформатуйте 2. В клітинки В23, В24 занесіть формули для визначення середніх • Для цього в клітинки В23 і В24 введіть, відповідно, формули ЛУБКЛОБ (В2:В20), =ЛУБКЛОБ (С2:С20). • В клітинку Б2 введіть формулу =В2 — В$23, в клітинку Е2 — формулу =С2 — В$24,в клітинку Р2 - формулу =Б2 * Е2, в клітинку О2 — формулу =Б2 * Б2, в клітинку Н — формулу = Е2 * Е2. • Скопіюйте ці формули у решту клітинок відповідних стовпчиків, рядків 3 — 20. • Застосуйте інструмент автосумування і розрахуйте в клітинці Р21 суму добутку центрованих величин показника і фактора. Суми квадратів центрованих величин розрахуйте в клітинках О21 та Н21. Розрахуйте суми фактора і показника і їх відхилення від середніх в клітинках В21, С21, Б21, Е21. • В клітинці В25 розрахуйте лінійний коефіцієнт кореляції, використавши математичну функцію 8((КТ (КОРЕНЬ). Для цього внесіть в клітинку В25 формулу =Р21 / 8((КТ(О21*Н21). Інший метод розрахунку лінійного коефіцієнта кореляції полягає у використанні статистичної функції СОККЕЬ (КОРРЕЛ). Надбудова ЛпаГузіз ТооІРак входить в стандартну інсталяцію М8 ОШсе. Якщо при інсталяції пакета вона була включена в робочий комплект, то при виборі з меню операції ТооІ8 —» ЛЙЙ-ІП8 на екрані з'являється вікно монітора надбудов. В ньому перечислен! всі заінстальовані на комп'ютері надбудови пакету М8 БхсеІ. Серед них бачимо ЛпаГузіз ТооІРак та 8о1уєг. Кожна з таких надбудов може бути активною (під 'єднаною) або неактивною (від'єднаною). У вікні монітора надбудов активні надбудови відмічені галочками (піднятими прапорцями, сквкЬох оп). Будь-яку з перелічених в ньому надбудов можна активізувати, відмітивши її галочкою. 3. Поставте табличний курсор на клітинку В26. Операцією Іп8егї —» РипсТіоп або інструментом /х відкрийте вікно конструктора функцій і виберіть функцію СОККЕЬ (КОРРЕЛ) з групи 8іаіІ8Ііса1 (Статистические):
4.Для перевірки значимості коефіцієнта кореляції в клітинку В27 занесіть формулу обчислення розрахункового значення ґ-статистики. Для знаходження критичного /ак-розподілу використайте статистичну функцію ТШУ (СТЬЮДРАСП):
Для оцінки параметрів парної лінійної регресії побудуйте наступну таблицю: Перший метод оцінки параметрів регресії базується на здатності М8 Ехсеї тиражуваті формули з абсолютними, відносними і змішаними адресами. Для оцінки параметрів виконайте такі дії: 1. В клітинки Б2 і Е2 введіть формули =В2 * С2 і =В2 * В2 та розмножте їх на блоки Б3:Б20, ЕЗ:Е20. 2. За допомогою функції 81ЛМ (СУММ) в клітинці В22 побудуйте п підсумок хі розмножте цю формулу в клітинки С22:022. і=1 3. В клітинку В23 введіть формулу для визначення розміру вибірки: =СОІЛЧТ(А2:А20). В клітинку В24 введіть формулу =(В23 * Б22 - В22 * С22) / (В23 * Е22 - В22 * В22) для оцінки параметра а. 4. Для знаходження параметра Ь в клітинку В25 введіть формулу =(С22 — В24*В22) / В23. Другий метод оцінки параметрів полягає у використанні статистичної функції ЬШЕ8Т (ЛИНЕЙН). Для цього необхідно: 1. Виділити блок Б24:Е28, де мають знаходитись розрахункові дані: 2. Відкрити діалогове вікно конструктора функцій, вибрати функцію ЬПЧЕЗТ (ЛИНЕЙН) з категорії ЗШізІісаІ (Статистические) і натиснути на клавішу ОК (Далее). 3. У другому діалоговому вікні ввести: в перший рядок (в перше поле) адресу блока даних попиту (С2:С20 або ім'я цього блока даних); у другий рядок — адресу блока даних факторів (ім'я цього блока); в третій рядок — слово ТКІЛЕ (ИСТИНА або 1), якщо Ь не дорівнює нулю і слово РАЬЗЕ (ЛОЖЬ або 0), якщо Ь дорівнює нулю; в четвертий рядок — слово ТКІЛЕ (ИСТИНА або 1), якщо необхідно знайти не лише параметри лінії регресії, а й додаткову регресійну статистику. Якщо необхідно знайти лише параметри лінії регресії, то вводимо слово РАЬЗЕ (ЛОЖЬ або 0), і натискаємо на клавішу ОК (Готово) для одержання результатів. 4. Для занесення у блок функцій, які формують не одне значення, а Опишемо результати: • У першому рядочку клітинок (В24, Е24) знаходяться значення параметрів лінійної регресії, відповідно, а і Ь. • У другому рядку блока в клітинках (В25, Е25) знаходяться середні квадратичні відхилення параметрів аЬ ,аа. • У третьому рядку блока в клітинці Б26 знаходиться коефіцієнт детермінації, а в клітинці Е26 — середнє квадратичне відхилення показника у відносно лінії регресії. • У четвертому рядку блока в клітинці Б27 знаходиться розрахункове значення ^"-статистики, а в клітинці Е27 число ступенів свободи (К). • У п'ятому рядку блоку в клітинці Б28 знаходиться сума квадратів відхилень розрахункових значень показника від його середнього значення, а в клітинці Е28 — сума квадратів залишків. Таким чином, для розрахунку параметрів регресії і додаткової регресійної статистики необхідно в блок Б24:Е28 внести формулу =ЬПЧЕ8Т(С2:С20; В2:В20, 1, 1). Для знаходження розрахункових значень попиту необхідно: 1. В клітинку Р2 ввести формулу = $Б$24 * В2 + $Е$24. 2. Для введення цієї формули активізуйте клітинку Е2, поставте знак =, активізуйте клітинку В24, де знаходиться значення параметра а, і натисніть на клавішу Р4. Створена абсолютна адреса клітинки $Б$24 при тиражуванні по стовпчику Р3: Е22 залишатиметься без зміни. Аналогічним способом вводиться абсолютна адреса клітинки $Е$24, де знаходиться оцінка параметра Ь. Визначення довірчого пів інтервалу де (і = 1, 2,... п), здійснюється за допомогою статистичних функцій АУЕКАОЕ (СРЗНАЧ), УАКР (ДИСПР), ТПЧУ (СТЬЮДРАСП). Критичне значення розподілу Стьюдента визначається з використанням статистичної функції ТШУ: в клітинку В20 вводиться формула =ТПЧУ(0,05; В23-2). При застосуванні конструктора функцій у першому полі діалогового вікна вказується рівень значимості а = 1-Р (р — довірча ймовірність), у другому полі — число ступенів свободи к= 19 - 2 = 17. В клітинку В30 вноситься формула =УАКР(Б2:Б19). При обчисленні дисперсії у діалоговому вікні конструктора функцій вказується діапазон клітинок, в яких знаходяться значення величини, дисперсію якої необхідно знайти. В клітинку Н2 заносимо формулу =$В$30 * $Е$26 * ЗСОКТ (В23 * (1+(В2 — $В$27)Л2/$В$30)) і копіюємо її в решту клітинок блоку НЗ:Н19. При побудові формули необхідно використати математичну функцію ЗССКТ (КОРЕНЬ). В клітинки 12, 12 вносяться відповідно формули =Р2 - Н2 і =Р2 + Н2, з допомогою яких визначаються нижні і верхні межі довірчої зони базисних даних попиту і його прогнозу. Ці формули копіюємо на діапазони клітинок 13:121, 13:121. Попит покупця, як показують дані досліджень, формується під впливом багатьох факторів. При цьому сила і напрямок їх дії бувають різноманітними. Кількісний взаємозв'язок попиту і факторів, що на нього впливають, описується такими рівняннями множинної регресії: Вибір факторів, що формують попит, здійснюється за допомогою графіків функцій, парних та частинних коефіцієнтів кореляції. Параметри рівняння регресії визначають методом найменших квадратів. Для лінійної множинної регресії параметри Ь, т1, тп визначаються методом найменших квадратів шляхом розв'язку такої системи нормальних рівнянь: Визначити параметри множинної регресії можна через систему нормальних рівнянь у матричній формі: [X ]-Б = у , де [X] — це матриця коефіцієнтів нормальної системи рівнянь, а — вектор її змінних, у — вектор її правих частин, Якщо визначник матриці, елементами якої є коефіцієнти при невідомих а0, аіі_і ат, відмінний від нуля, то система нормальних рівнянь має єдиний розв'язок. Якщо визначник матриці X відмінний від нуля, то існує матриця, обернена до X і система нормальних рівнянь має єдиний розв'язок. Домноживши матричне рівняння зліва на обернену матрицю, одержимо вектор оцінок параметрів а . Для двофакторної лінійної моделі у = Ь + тххх + т2х2 параметри ті і т2 визначаються за формулами: де , ох , ах1, оХ2— середні квадратичні відхилення результатної змінної у та факторів хі і х2; гх1у, гх2у, гх1х2 — парні коефіцієнти кореляції, які характеризують близькість зв'язку між попитом у і доходом на душу населення х і, між у і товарообігом х2 і між самими факторами. хіі х2. Параметри множинної лінійної регресії можна розрахувати за допомогою статистичної функції ЬПЧЕЗТ (ЛИНЕЙН). Розрахунок рекомендується здійснити за такою схемою: 1. Побудуйте електронну таблицю за наступним зразком: 2. Введіть початкові дані, відформатуйте таблицю. Для того, щоб подальші маніпулювання з даними були більш наочними, надайте їм імена, які наведені нижче:
Для присвоєння імен клітинкам чи діапазонам, скористайтеся командою Ітеті —> Мате —> Ве$іпе(Вставка —> Имя —> Присвоить). 3. Виділіть діапазон клітинок, в якому буде розташовано результати 4. Виконайте команду Ітеті —> Рипсііоп (Вставка —> Функция). В 5. У другому діалоговому вікні конструктора функцій введіть Замість вводу імен діапазонів з клавіатури можна вибирати їх зі списку імен операцією Ітеті —> Иаше —> Раяіе. На екрані з'явиться список всіх імен таблиці; з цього списку потрібно вибрати ім'я діапазону і натиснути на клавішу ОК. Для завершення вводу параметрів функції натисніть на клавішу ОК. 6. В результаті на екрані відображається виділений блок зі значенням 7. Натисніть на комбінацію клавіш Сіті+Зкі/і+Епіет. В результаті Для відображення всього масиву виділений блок повинен мати п'ять рядків і п+і стовпчик, де п — кількість факторів. Зверніть увагу на те, що функція ЬШЕЗТ (ЛИНЕЙН) повертає коефіцієнти регресії у послідовності, зворотній щодо їх послідовності в моделі. 8. Для визначення розрахункових і прогнозних значень попиту 10. Ділення коефіцієнтів регресії на їх стандартні похибки дає значення стандартизованих (нормованих) змінних І (-статистики). Стандартизовані змінні показують відстані від нуля відповідних коефіцієнтів регресії у частках стандартних помилок. Для обчислення значень стандартизованих змінних у клітинку Р13 введіть формулу =Р2/Р3 і розмножте на блок 013:Н13. 12. Для визначення значимості стандартного відхилення ско- =ТПЧУ(АВ8(Р13); Ступсвободи) і розмножте її на блок клітинок 014:Н14. Функція АВ8 використовується у цій формулі для того, щоб значення першого параметра функції ТПЧУ було невід 'ємним.
Контрольні питання
З повагою ІЦ “KURSOVIKS”! |