Главная | Обратная связь | Поможем написать вашу работу!

Вказівки щодо виконання

Лабораторна робота №2.

Тема: Планування рекламної компанії засобами MS Excel

Мета: Отримати практичні навики використання засобів MS Excel для планування рекламної компанії випуску продукції на підприємстві.

Завдання. Фірма щотижня аналізує стан справ зі збуту одного із видів своєї продукції й дає оцінку: відмінно (" в " – стан 1), добре (" д " – стан 2), або задовільно (" з " – стан 3). Оцінки обгрунтовуються перехідними ймовірностями та отриманими прибутками. Перехідні ймовірності задані матрицями р¹ та р². Прибутки, отримані за тиждень, задані матрицями R¹ та R².

Необхідно прийняти рішення про доцільність рекламування цієї продукції на наступні три тижні з метою розширення її збуту.

Надрукувати:

1. Текст завдання;

2. Результати обчислень та вхідні дані;

3. Формули, за якими велись обчислення;

4. Пояснення щодо використання вбудованих функцій MS Excel;

5. Пояснення щодо отриманих результатів про доцільність рекламування продукції.

Вказівки щодо виконання

1. Створимо файл електронної таблиці.

2. Замінимо «Лист 1» на «Реклама».

3. Позначимо варіанти розрахунків

1. - без реклами

2. – при наявності реклами

4. В діапазонах B3:D5 та B6:D8 вкажемо елементи матриць р¹ та р², що визначають ймовірності переходу без реклами та при її наявності протягом кожного тижня, відповідно.

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

Відповідні прибутки задані матрицями R¹ та R² в діапазонах E3: G5 та E6:G8.

Відмітимо, що елементи матриці R² враховують витрати на рекламу. Вигляд заповненої таблиці наведений на рис.1

Рис.1 Таблиця із вхідними даними для планування рекламної компанії

Необхідно спланувати оптимальну рекламну компанію на наступні три тижні.

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

Нехай f_n(i) - оптимальний очікуваний прибуток за етапи n, n+1, …, N при умові, що система знаходиться в стані і на початку n -го тижня. (В нашому випадку станів m =3: „ в”, „д”, „з” та три наступних тижні).

Тоді значення величини f_n(i) можна визначити за формулою:

, n є [1, N], (1)

де:

k =1,2 (без реклами, з рекламою);

при всіх i.

Щоб спростити формулу (1), позначимо:

Тоді значення оптимальний очікуваний прибуток для остання тижня (в нашому випадку N=3 - це третій тиждень) визначається за формулою:

(2)

Оптимальний очікуваний прибуток решти періодів (тижнів) визначається за формулою:

, n є [1, N-1] (3)

5. Тепер за формулами (2) та (3) проведемо обчислення на робочому листі „Реклама” книги MS Excel.

5.1 Обчислення для третього тижня. Використовується формула (2):

- В клітинку І3 занесемо формулу: =СУММПРОИЗВ(В3:D3;E3:G3). Функція СУММПРОИЗВ() визначає суму добутків відповідних елементів масивів або діапазонів. В нашому випадку це величина:

B3*E3+C3*F3+D3*G3. Далі формула копіюється в діапазон І4:І8 для обчислення величин: .

- В клітинки діапазону І10:І15 послідовно вводимо формули:

=І3

=І6

=І4

=І7

=І5

=І8

Тим самим упорядковуємо очікувані прибутки такими парами: перший стан без реклами та при їх наявності, другий стан без реклами та при її наявності і третій стан без реклами та при її наявності.

- В клітинки діапазону В11:В13 занесемо формули:

=МАКС(І10:І11);

=МАКС(І12:І13);

=МАКС(І14:15),

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

- В клітинках діапазону С11:С13 за формулами

=ПОИСКПОЗ(В11; І10:І11;0),

=ПОИСКПОЗ(В12; І12:І13;0),

=ПОИСКПОЗ(В13; І14:І15;0)

визначаємо оптимальний варіант дій. Якщо результат застосованої формули дорівнює 1, то гроші на рекламу не тратити, а якщо 2 – то тратити. Функція ПОИСКПОЗ() повертає відносну позицію елемента масиву, який відповідає указаному значенню. Пояснимо синтаксис вказаної функції:

12 Следующая ⇒