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

 

Тема: “Оптимизация штатного расписания торгового предприятия”

Цель: Исследовать с помощью электронных таблиц Microsoft Excel возможность оптимизации штатного расписания торгового предприятия методами надстройки Поиск решения.

Немного теории:

В предыдущей ЛР рассмотрена задача поиска значения параметра, позволяющего достичь конкретной цели.

Решаемые задачи могут быть более сложными. Например, поиск нескольких параметров, обеспечивающих некоторый наперед заданный результат (к примеру, затраты на содержание персонала не должны превышать фонда заработной платы и, в тоже время, заработная плата работников не должна быть меньше, чем минимальный размер оплаты труда (МРОТ), установленный в стране).

Кроме того, иногда интересует не конкретный результат, а минимально или максимально возможный. Например, как минимизировать затраты на содержание персонала или максимизировать прибыли от реализации продукции?

Такие задачи в Excel решают с помощью надстройки Поиск решения.

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

Познакомимся с решением этих задач на следующем примере.

Постановка задачи «Оптимизация штатного расписания торгового предприятия»:

Усложним рассмотренную в предыдущей ЛР задачу. Пусть известно, что для нормальной работы магазина необходимо N₁(min) ÷ N₁(max) кассиров-операционистов, N₂(min) ÷ N₂(max) продавцов, N₃ старших продавцов, N₄ администраторов торгового зала, N₅ заведующих отделами, N₆ заместителей директора магазина, N₇ директоров магазина. Общий месячный фонд зарплаты не должен превышать S. Необходимо определить, какими должны быть оклады сотрудников магазина, при условии, что оклад кассира-операциониста не должен быть меньше МРОТ – М руб.

Численность сотрудников каждой должности (N₁… N₇) и МРОТ (M) по вариантам задачи представлены в таблице 1. Коэффициенты (K₂… K₆), L₄, L₇ и значение S аналогичны условиям ЛР-1.

Таблица 1

Номер варианта	N1(min) – N1(max)	N2(min) – N2(max)	N3	N4	N5	N6	N7	M тыс. руб.
	3 – 5	6 – 8						3,0
	3 – 6	6 – 9						3,5
	4 – 6	7 – 9						4,0
	4 – 7	7 – 10						4,5
	5 – 7	8 – 10						5,0
	5 – 8	8 – 11						5,5
	6 – 8	9 – 11						6,0
	6 – 9	9 – 12						6,5
	7 – 9	10 – 12						7,0
	7 – 10	10 – 13						7,5
	3 – 5	4 – 6						3,1
	3 – 6	4 – 7						3,6
	4 – 6	5 – 7						4,1
	4 – 7	5 – 8						4,6
	5 – 7	6 – 8						5,1
	5 – 8	6 – 9						5,6
	6 – 8	7 – 9						6,1
	6 – 9	7 – 10						6,6
	7 – 9	8 – 10						7,1
	7 – 10	8 – 11						7,6
	3 – 5	6 – 8						3,15
	3 – 6	6 – 9						3,65
	4 – 6	7 – 9						4,15
	4 – 7	7 – 10						4,65
	5 – 7	8 – 10						5,15
	5 – 8	8 – 11						5,65
	6 – 8	9 – 11						6,15
	6 – 9	9 – 12						6,65
	7 – 9	10 – 12						7,15
	7 – 10	10 – 13						7,65

Построим модель решения этой задачи:

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

Запишем ее так:

N₁*A₁*C+N₂*(A₂*C+B₂)+...+N₇*(A₇*C+B₇) = S.

В этом уравнении нам неизвестно число кассиров-операционистов (N₁), продавцов (N₂) и оклад кассира-операциониста (С).

Используя надстройку Excel Поиск решения, найдем их.

Рекомендации к решению:

1) Откройте новую книгу Excel и скопируйте на ее Лист 1, созданную в предыдущей лабораторной работе таблицу с Листа 1 (файл фамилия_3фик_ЛР-1а.xlsx)

2) Внесите изменения в таблицу Листа 1, в соответствие с постановкой задачи и данными таблицы 1 задания.

3) Сохраните вновь созданный файл с именем – фамилия_3фик_ЛР-2.xlsx

4) В меню Данные активизируйте команду Поиск решения.

5) В окне Оптимизировать целевую функцию укажите ячейку, где указана зарплата всего персонала, содержащую модель.

6) Поскольку необходимо оптимизировать зарплату всего персонала в пределах фонда заработной платы, то активизируйте, соответственно, одну из кнопок: Максимум, Минимум или Значения.

7) Используя кнопку Добавить опишите ограничения задачи:

- N₁(min) ≤ кассиров-операционистов ≤ N₁(max),

- N₂(min) ≤ продавцов ≤ N₂(max),

- оклад кассира-операциониста ≥ M.

8) В окне Изменяя ячейки переменных укажите ячейки, где указано количество кассиров-операционистов, продавцов и оклад кассира-операциониста.

9) Выберите метод решения Поиск решения нелинейных задач методом ОПГ.

10) Установите Параметры поиска по умолчанию.

11) Щелкните на кнопке Найти решение.

12) Проанализируйте полученный результат и сохраните найденное решение.

13) Создайте отчеты Результаты, Устойчивость, Пределы и сохраните найденное решение.

14) Проанализируйте листы отчетов Результаты, Устойчивость, Пределы.

15) Сформулируйте выводы относительно метода решения задачи, полученного результата, содержания отчетов и запишите выводы на том же листе Excel ниже расчетов.

16) Сохраните свою работу.

Контрольное задание

По аналогии с предыдущей задачей на Листе 2 той же книги Excel постройте расчетную модель плана выгодного производства:

Предположим, что мы решили производить несколько видов конфет. Назовем их условно "A", "B" и "C". Известно, что реализация 10-и килограммов конфет "А" дает прибыль 900 руб., "В" - 1000 руб. и "С" - 1600 руб. Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько десятков килограмм необходимо произвести, чтобы общая прибыль от реализации была максимальной.

Условные нормы расхода сырья на производство 10 кг конфет каждого вида приведены в таблице 2.

Таблица 2

Сырье	Нормы расхода сырья	Запас сырья
	А	В	С
Какао
Сахар
Наполнитель
Прибыль

 

Введите исходные данные и формулы в электронную таблицу, как указано ниже на рисунке 1.

Рисунок 1

 

Решите задачу средствами Поиск решения и сформулируйте выводы ниже расчетов.

Сохраните результаты.

Контрольные вопросы

На Листе 3 той же книги Excel ответьте на следующие контрольные вопросы:

1Назначение и возможности надстройки Поиск решения в MS Excel.

2 Как активировать надстройку Поиск решения в MS Excel?

3 Какими предельными показателями ограничивается размер задачи, которую можно решить с помощью надстройки Поиск решения в базовой версии MS Excel?

4 Назовите основные этапы процедура поиска решения с помощью надстройки Поиск решения в MS Excel.

5 Перечислите Параметры средства Поиск решения.

Отчет по работе

Файл с именем, например, фамилия_3фик_ЛР-2.xlsx, содержащий Листы 1…3 с таблицами расчетов, выводами и ответами на контрольные вопросы отправить по электронной почте преподавателю.