Вариант 9

1. Определить расходы на рекламу для получения наибольшей производственной прибыли в 1-м квартале года, для которого коэффициент сезонного изменения продаж равен 0,84, затраты на торговый персонал – 86600 р. Цена изделия – 434 р., себестоимость 280 р. Доля косвенных затрат от выручки – 15%. Установлено, что формула зависимости объема продаж p_i от затрат на рекламу x_i и коэффициента сезонности k_i имеет вид:

.

2. Определить расходы на рекламу в каждом квартале года для получения наибольшей производственной прибыли с исходными данными:

Показатели	1 кв.	2 кв.	3 кв.	4 кв.
Сезонность	0,84	1,15	0,75	1,25
Торговый персонал	86600	86600	93000	93000

Цена изделия – 434 р., себестоимость – 280 р. Доля косвенных затрат от выручки – 15%. Формула расчета объема продаж та же, что в задаче 1.

3. Решить задачу 2 при ограничении: годовые затраты на рекламу не должны превышать 9000000 р.

4. Определить расходы на рекламу в каждом месяце 1-го полугодия для получения наибольшей производственной прибыли с исходными данными:

Показатели	январь	февраль	март	апрель	май	июнь
Сезонность	0,84	0,84	0,86	0,95	1,1	1,25
Торговый персонал	83000	86600	87000	90000	92000	93000

Цена изделия – 434 р., себестоимость – 280 р. Доля косвенных затрат от выручки – 15%. Формула расчета объема продаж та же, что в задаче 1. Полугодовые затраты на рекламу не должны превышать 7000000 р.

Лабораторная работа №5. Оптимизация решений

Для оптимизации решений в Excel могут быть использованы такие методы, как подбор параметра, поиск решения, диспетчер сценариев.

1. Подбор параметра.

В случае функции подбора параметра речь идет о простой форме анализа данных типа «что если», то есть нахождение оптимального желаемого решения за счет изменения одного из параметров. Значение определенной (целевой) ячейки является результатом вычисления формулы, которая прямо или косвенно ссылается на одну пли несколько влияющих ячеек. В целевую ячейку следует ввести нулевое значение, которое будет использоваться как результат вычисления формулы. Функция подбора меняет значение влияющей ячейки так, чтобы получить в целевой ячейке заданную величину. Определенное функцией подбора параметра значение можно сохранить на рабочем листе.

Задание 1. Пусть предприятие выпускает столы. Данные о реализации продукции и затратах на ее производство имеют значение, представленные в таблице 1.

Таблица 1

	A	B	C
1	Прибыль от реализации продукции
2	Объем производства	Шт.	18000
3	Цена реализации	Руб.	800
4	Выручка	Тыс.руб.
5	Затраты на материалы	Тыс.руб.	2800
6	Заработная плата	Тыс.руб.	6400
7	Начисление на зарплату	Тыс.руб.	2688
8	Прочие затраты	Тыс.руб.	1800
9	Итого затрат	Тыс.руб.
10	Прибыль	Тыс.руб.

Допустим, что нужно повысить уровень прибыли до 2 млн. руб. Это можно осуществить тремя способами :

• повысить цену реализации;

• увеличить объем производства;

• снизить затраты, прежде всего заработанную плату.

Надо найти такую цену реализации, чтобы прибыль была 2000 тыс.руб.

Порядок выполнения задания 1:

1. Открыть рабочую книгу, лист 1 переименовать в Подбор.

2. Занести в этот лист таблицу 1.

3. В ячейке С4 определить выручку, как произведение Цены реализации на Объем производства.

4.. В ячейках С9 и С10 определить Итоговые затраты и Прибыль.

5. Скопировать таблицу 1 на лист 3 (для задания 3).

6. Выделить ячейку с целевым назначением (С10).

7. С помощью меню Сервис/Подбор параметра откроите диалоговое окно Подбор параметра, в поле Значение укажите нужное значение (2000).

8. В поле Изменение значения ячейки задается ссылка на соответствующую влияющую ячейку (С3) или, поместив в это поле курсор ввода, делается щелчок на ячейке С3.

9. Закрывается окно диалога кнопкой ОК. В результате в окне диалога Результат подбора параметра и непосредственно на рабочем листе будет отражен результат работы функции: значение прибыли 2000 тыс. руб., если цена реализации повысится до 871,56 руб.

10.Для сохранения результата на рабочем листе нажимается кнопка ОК в окне Результат подбора параметра, либо кнопка Отмена, чтобы сохранить на рабочем листе исходные значения.

Так как Excel находит решение с помощью функции подбора параметра методом итераций, то программа иногда требует для решения много времени, и вычисление можно прервать. Для этого в окне диалога Результат подбора параметра нажимается кнопка Пауза. Используя кнопки Шаг или Продолжить, можно продолжить вычисления.

По команде Сервис/Параметры на вкладке Вычисления можно изменить параметры, используемые программой по умолчанию при выполнении итераций (после 100 итераций или же приращения зависимых величин меньше 0,001).

Подбор параметра можно выполнить на диаграмме. Для этого необходимо :

- на основе табличных данных построить диаграмму;

- выделить один из столбиков на диаграмме, в результате чего он будет обрамлен восьмью маленькими квадратиками;

- поставить курсор па верхний средний квадратик, и при помощи левой кнопки мыши увеличить или уменьшать высоту столбика.

Как только клавиша будет отпущена, то при первом выполнении данной операции, откроется диалоговое окно Подбор параметра, а затем - Результат подбора параметра. При этом данные в целевой ячейке также будут изменяться. В последующем подбор параметра с помощью диаграммы будет происходить без открытия диалоговых окон.

11. Построить диаграмму на рабочем листе Подбор по таблице 1, поместив её под таблицей. Для этого: .

• выделить таблицу;

• выбрать Мастер Диаграмм;

• в процессе диалога с Мастером Диаграмм выбрать: тип диаграммы - гистограмма, вид гистограммы - 1; считать 1 столбец метками оси X; название диаграммы - Прибыль от реализации продукта.

• Растянуть диаграмму так, чтобы появились все подписи под осью X.

12. Выделить столбик Цена реализации и изменять её величину.

13. Проделать тоже со столбиком Объём производства, а затем со столбиком Заработная плата.

14. Проанализировать полученные результаты.