 |
АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомДругоеЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция
Лабораторна робота №3
Тема. Оптимальний план витрат на рекламу.
Мета. Навчитись визначати оптимальні суми витрат для збільшення обсягу продаж та отримання найбільшого прибутку. Засвоїти методику та технологію оптимізації планів в табличному процесорі Excel за допомогою програми Поиск решения (Solver). Створити лабораторну модель.
& Теоретичні відомості
При розробці річного фінансового плану діяльності фірми необхідно визначити витрати на рекламу для отримання найбільшого прибутку.
Визначення проблемної системи. Попередньо проблемна система повинна включати:
§ показники скороченого балансу витрат, доходів та прибутків фірми, що плануються;
§ моделі (формули) причинно-наслідкових зв’язків обсягів продажу, доходів та прибутку в залежності від витрат на рекламу.
Уточнення, обмеження проблеми, описова модель. Спочатку спростимо задачу річного планування та визначимо витрати на рекламу для отримання найбільшого прибутку в першому кварталі року. Передбачається, що збільшення реклами викликає збільшення продажів без часового лага (запізнення, післядії, аргументу відхилення), тобто в цьому ж кварталі.
Таблична модель
Зазвичай план-прогноз руху засобів розробляється у вигляді таблиці. Спочатку структура документа складається в чорновому варіанті на папері чи відразу у вигляді електронної таблиці.
Шукані показники таблиці окреслені жирною рамкою (див. табл. 3.1). Це витрати на рекламу та виробничий прибуток. Ми повинні при розробці плану зміною обсягу витрат на рекламу домогтися найбільшого прибутку. Вхідні дані окреслені пунктиром. Це коефіцієнт сезонної зміни обсягів продажу, витрати на торговий персонал, ціна та собівартість виробу.
Формули табличної моделі
Після складання планової таблиці необхідно зв’язати причинно-наслідкові показники формулами для обчислень. Формули та числа вхідних даних показані в таблиці 3.1.
Якщо в комірки введені числа, то це вхідні (зовнішні, екзогенні) дані. Якщо в комірці формула, то це показник, що обчислюється (ендогенний).
Керування експериментами
Здійснюється вручну чи за допомогою Таблицы подстановки та програми оптимізації Поиск решения. Керуючими даними є суми витрат на рекламу в комірці В11. Керування здійснюється з метою максимізації прибутку в комірці В15. Для розширення експериментів можна змінювати число періодів планування, динаміку прогнозу фіксованих витрат, ціни та собівартість, вводити обмеження для показників тощо.
Таблиця 3.1
| Назва показника | Формула | Пояснення | |
| Стовпець | (не вноситься в таблицю) | ||
| Рядок | А | В | |
| 1 квартал | |||
| Сезонність | Фіксоване значення | Сезонна поправка обсягу продажів (наприклад, 0,9 в 1-му кварталі) | |
| Число продажів, шт. | =35*В3*(В11+3000)^0,5 | Очікуване число продажів: в рядку 3 – сезонна поправка; в рядку 11 відображені витрати на рекламу | |
| Виручка від реалізації | =В5*$B$18 | Добуток числа продажів (5 рядок) на ціну виробу (комірка В18) | |
| Собівартість | =В5*$B$19 | Добуток числа продажів (5 рядок) і витрат на виріб (комірка В19) | |
| Валовий прибуток | =В6-В7 | Різниця виручки від реалізації (рядок 6) та собівартості (рядок 7) | |
| Торговий персонал | Фіксоване значення | Витрати на торговий персонал (наприклад, 8 000 грн. в 1-му кварталі) | |
| Реклама | Фіксоване значення | Кошти на рекламу (наприклад, 30 000 грн. в 1-му кварталі) | |
| Непрямі витрати | =0,15*В6 | В фонд корпорації: 15% виручки від реалізації (рядок 6) | |
| Сумарні витрати | =СУММ(В10:В12) | Витрати на персонал (10 рядок), рекламу (11 рядок) та непрямі витрати (12 рядок) | |
| Виробничий прибуток | =В8-В13 | Валовий прибуток (8 рядок) за винятком сумарних витрат (13 рядок) | |
| Норма прибутку | =В15/В6 | Відношення прибутку (15 рядок) та виручки від реалізації (6 рядок) | |
| Ціна виробу | Фіксоване значення | (Наприклад, 40 грн.) | |
| Собівартість виробу | Фіксоване значення | Витрати на виріб (наприклад, 25 грн.) |
Прогнозиста та плановика завжди переслідує відчуття невпевненості. Майбутнє невизначене. Але вони його передбачають, готують варіанти вирішень. Менеджери приймають рішення. Ви спроектували планову таблицю, але ви не впевнені, чи достатньо факторів включено в модель, чи правильно відображені причинно-наслідкові зв’язки в модельних формулах елементів. Ви не математик. Ви – економіст. Навіть при наявності готової формули ви не відчуваєте поведінку показників. Чи буде показник мати екстремум? Чи зустрінемо ми унімодальність чи багатоекстремальність? Наскільки сильний вплив факторів? Тому плановик, щоб відчути модель, перевіряє поведінку її окремих елементів та показників.
Оптимальність витрат визначається шляхом аналізу математичних залежностей на основі дослідження побудованих графіків (графічний метод) і використаних формул (аналітичний метод).
Хід роботи
1. Проведення попереднього аналізу елементів моделі.
Створити файл Лаб3_ПрізвищеСтудента в табличному процесорі Excel.
На першому листі книги створити лабораторну табличну модель, яка наведена в першій таблиці на рис. 3.1, з урахуванням формул, значень і пояснень табл. 3.1.
Поиск по сайту: