Хід роботи

1. Проведення попереднього аналізу елементів моделі.

Створити файл Лаб3_ПрізвищеСтудента втабличному процесорі Excel.

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

Визначення залежності продажів від витрат на рекламу

В нашому прикладі аналітики, статистики та економетристи задали плановикам формулу залежності обсягу продажів від витрат на рекламу в рядку Число продажів як

= 35*ВЗ*(В11+3000)^0,5 (1).

Щоб зрозуміти та відчути силу впливу факторів, скористаємось графіком.

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

Плануючи експеримент, встановимо початкове значення витрат на рекламу 6000 грн. та будемо задавати наступні числа арифметичною прогресією з кроком 4000 грн. Для цього введемо в комірку Е23 число 6000, а в F23 – 10000. Виділимо обидві комірки і протягнемо прямокутну рамку курсору за правий нижній кут з хрестиком вздовж рядка.

Ми отримали ряд чисел арифметичної прогресії. Це план однофакторного імітаційного експерименту. В комірку D24 введемо формулу (1) нашої функції. Вона відображається в рядку формул листа Excel вгорі рис. 3.1.

Рис. 3.1. Залежність кількості продажів від витрат на рекламу

Виділимо діапазон з числами та формулою (комірки D23:K24). Виконаємо команду ДанныеТаблица подстановки. З’явиться діалогове вікно Таблица подстановки (рис. 3.2).

Рис. 3.2. Діалогове вікноТаблица подстановки

Введемо в поле Подставлять значения по столбцам в: комірку В11, тобто ми будемо змінювати витрати на рекламу в нашій головній таблиці. Натиснемо ОК. Числами заповнюється рядок Число продажів. Відомим з курсу інформатики способом побудуємо над таблицею графік.

Спостерігаємо та аналізуємо залежність продажів від витрат на рекламу. Продажі ростуть, екстремуму немає. Отже, оптимальний розв’язок в цій системі "реклама-продажі" неможливий. Крутизна залежності з ростом витрат спадає, тобто спостерігається спадна ефективність фактору.

Для кількісної оцінки спадної ефективності фактору введемо в рядок 25 формули оцінки приросту продажів на кожному кроці експерименту відніманням із рядка 24 попереднього значення з наступного (значення в F25 = F24-E24). На інтервалі дослідження приріст продажів спадає від 600 до 300.

Визначення залежності прибутку від витрат на рекламу

Нескінченне збільшення витрат на рекламу буде нескінченно збільшувати обсяг продажів. Але зміна прибутку, ймовірно, буде мати максимум, оскільки збільшення витрат на рекламу в кінці кінців “з’їсть” весь прибуток. Дослідимо цю залежність.

Скопіюємо першу таблицю з рис. 3.1 на новий лист робочої книги. Методом побудови прогресії заповнимо рядок витрат на рекламу в таблиці 3.2 (аналогічно до попереднього пункту розташуємо її в комірках С23:J24).

Таблиця 3.2

Витрати на рекламу, грн.	6000	10000	14000	18000	22000	26000	30000
Прибуток, грн.	12895	14324	14964	15083	14825	14278	13500

Кожне число копіюємо в комірку реклами (В11) та після перерахунку таблиці копіюємо число виробничого прибутку (В15) у відповідну комірку рядка прибутку таблиці 3.2 за допомогою команд Правка – Копировать / Правка – Специальная вставка – Значения.

За даними цієї таблиці побудуємо над нею графік (рис. 3.3).

Рис. 3.3. Залежність прибутку від витрат на рекламу

Залежність прибутку від витрат на рекламу має яскраво виражений максимум. Менеджер отримає оптимальний план, якщо він визначить витрати на рекламу у 18 000 грн. При цьому буде отриманий прибуток 15 000 грн.

Ми відчули вплив факторів на критеріальні показники і тепер можемо впевнено використовувати швидкі комп’ютерні алгоритми програмної оптимізації планів.