Вариант 8

Решить в Excel на отдельных листах задачи планирования численности персонала:

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

1.

Потребность в персонале, чел							Среднедневная оплата работника, руб
пн	вт	ср	чт	пт	сб	вс
18	17	23	19	14	19	20	130

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

3.

Потребность в персонале, чел							Среднедневная оплата работника, руб
пн	вт	ср	чт	пт	сб	вс
25	28	26	20	22	22	22	150

4. Решить задачу 3 при дополнительных условиях: в бригаде с выходными по субботам и воскресеньям должно быть не менее 4 человек, в бригаде с выходными по воскресеньям и понедельникам – не менее 6 человек .

Вариант 9

Решить в Excel на отдельных листах задачи планирования численности персонала:

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

1.

Потребность в персонале, чел							Среднедневная оплата работника, руб
пн	вт	ср	чт	пт	сб	вс
20	22	21	14	18	15	22	130

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

Лабораторная работа №4. Оптимальный план затрат на рекламу

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

	A	B
1		1 квартал
2
3	Сезонная поправка объема продаж	0,9
4
5	Число продаж, шт.	=35*B3*(B11+3000)^0,5
6	Выручка от реализации	=B5*$B$18
7	Себестоимость	=B5*$B$19
8	Валовая прибыль	=B6-B7
9
10	Торговый персонал	8000
11	Реклама	17093
12	Косвенные затраты	=0,15*B6
13	Суммарные затраты	=СУММ(B10:B12)
14
15	Производственная прибыль	=B8-B13
16	Норма прибыли	=B15/B6
17
18	Цена изделия	40
19	Себестоимость изделия	25

Исходные данные окружены пунктирной линией, искомые показатели обведены жирной линией. Результаты должны получиться следующие:

	A	B
1		1 квартал
2
3	Сезонная поправка объема продаж	0,9
4
5	Число продаж, шт.	4465
6	Выручка от реализации	178605
7	Себестоимость	111628
8	Валовая прибыль	66977
9
10	Торговый персонал	8000
11	Реклама	17093
12	Косвенные затраты	26791
13	Суммарные затраты	51884
14
15	Производственная прибыль	15093
16	Норма прибыли	8%
17
18	Цена изделия	40
19	Себестоимость изделия	25

Построим таблицу зависимости количества продаж от затрат на рекламу. Заполните ячейки G23:G25 поясняющим текстом. В ячейку H24 скопируйте формулу из ячейки B5. В ячейку I23 занесите число 6000, в ячейку J23 число 10000, выделите обе ячейки и протяните правый нижний угол по строке до столбца О для получения арифметической прогрессии. Выделите ячейки H23:O24, выберите команду Данные-Таблица подстановки. В появившемся диалоговом окне введите в поле Подставлять значения по столбцам в: ячейку B11. В ячейки J25:O25 введите формулу для вычитания предыдущего значения из последующего. Должны получиться следующие значения:

	G	H	I	J	K	L	M	N	O
23	Затраты на рекламу,руб.		6000	10000	14000	18000	22000	26000	30000
24	Число про- даж, шт.	5722	2988	3592	4107	4565	4981	5364	5722
25	Прирост продаж, шт.			603,2	515,5	457,7	415,8	383,7	358

Постройте график зависимости числа продаж от затрат на рекламу. Продажи растут, значит, оптимальное решение в системе «реклама – продажи» невозможно. Постройте график зависимости прироста продаж от затрат на рекламу. Вы увидите явную убывающую эффективность фактора.

Очевидно, увеличение затрат на рекламу в конце концов «съест» всю прибыль. Исследуем зависимость прибыли от затрат на рекламу. Для этого заполните строку затрат на рекламу тем же методом арифметической прогрессии, каждое число поочередно копируйте в ячейку первой таблицы и полученное в результате пересчета значение прибыли скопируйте в соответствующую ячейку строки прибыли в последней таблице:

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

Постройте график полученной зависимости. Вы увидите ярко выраженный максимум. Почувствовав влияние факторов, выполним компьютерный поиск оптимальных затрат на рекламу. Для этого выполним команду Сервис-Поиск решения. В качестве целевой ячейки задайте B15 – прибыль за I-й квартал. Установите переключатель для поиска максимального значения, в поле Изменяя ячейки укажите B11. Вы получите оптимальные затраты 17093 руб., прибыль составит 15093 руб. Сравните полученное решение с решением, найденным ранее графически.

Скопируйте первоначальную таблицу на лист 2. В столбцах C, D и E рассчитайте план для II, III и IV кварталов, используя предложенные данные и формулы, аналогичные формулам в столбце В. В столбце F рассчитайте итоговые значения. В качестве первоначальных значений возьмите вложения в рекламу, равные 10000 каждый квартал. Получится прибыль 69662 руб. Выполните команду Поиск решения, указав в качестве целевой ячейки F15 (общую прибыль за год), а в качестве изменяемых ячеек B11:E11. Поле ограничений оставьте пустым. Вы получите оптимальное решение:

	A	B	C	D	E	F
1		1 квартал	2 квартал	3 квартал	4 квартал	Всего
2
3	Сезонная поправка объема продаж	0,9	1,1	0,8	1,2
4
5	Число продаж, шт.	4465	6670	3528	7938	22601
6	Выручка от реализации	178605	266805	141120	317520	904050
7	Себестоимость	111628	166753	88200	198450	565031
8	Валовая прибыль	66977	100052	52920	119070	339019
9
10	Торговый персонал	8000	8000	9000	9000	34000
11	Реклама	17093	27016	12876	32721	89706
12	Косвенные затраты	26791	40021	21168	47628	135607
13	Суммарные затраты	51884	75036	43044	89349	259313
14
15	Производственная прибыль	15093	25016	9876	29721	79706
16	Норма прибыли	8%	9%	7%	9%	9%
17
18	Цена изделия	40
19	Себестоимость изделия	25

Добавьте ограничение на общие расходы на рекламу <=40000 и найдите новое оптимальное решение 71447 руб.(сравните с равным годовым бюджетом, но равномерными ежеквартальными вложениями 10000 руб.)

Измените последнее ограничение на <=50000 и получите новое оптимальное решение 74817 руб.