Минимизация фонда заработной платы фирмы.
Известно, что для нормальной работы фирме требуется 5-7 курьеров, 8-10 младших менеджеров, 10 менеджеров, 3 заведующих отделами, главный бухгалтер, программист, системный аналитик, генеральный директор фирмы.
Общий месячный фонд заработной платы должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников фирмы, при условии что оклад курьера не должен быть меньше 1400 руб.
А) В качестве модели решения возьмем линейную модель. Тогда условие задачи имеет вид:
N1 * A1 * x + N2 * (A2 * x + B2) + … + N8 * (A8 * x+ B8) = Минимум
где Ni – количество работников данной специальности;
x – зарплата курьера;
Ai и Bi– коэффициенты заработной платы сотрудников фирмы.
Б) Создайте таблицу на лист «Штатное расписание3». Выполните необходимые расчеты.
| Штатное расписание фирмы | |||||
| Зарплата курьера | ? | ||||
| Должность | Коэф. А | Коэф. В | Зарплата сотрудника | Кол-во штатных единиц | Суммарная зарплата |
| Курьер | ? | ? | |||
| Младший менеджер | 1,5 | ? | ? | ||
| Менеджер | ? | ? | |||
| Зав. отделом | ? | ? | |||
| Главный бухгалтер | ? | ? | |||
| Программист | 1,5 | ? | ? | ||
| Системный аналитик | ? | ? | |||
| Генеральный директор | ? | ? | |||
| Фонд заработной платы | ? |
В) В меню Сервис выберите команду Поиск решения, установите необходимые параметры:
- целевая ячейка, в которой будет подбираться результат (F14);
- значение, которое необходимо установить в целевой ячейке (максимальное, минимальное или какое-либо конкретное значение);
- изменяемые ячейки – ячейки, значение в которых будет изменяться для подбора значения в целевой ячейке (B3, E6, E7);
- условия ограничений, которые необходимо выполнять при подборе значений (B3>=1400, 5<=E6<=7, 8<=E7<=10);
- математические параметры поиска решений (окно появляется при выборе кнопки «Параметры» в окне «Поиск решения»).
Результаты расчета:
Составление плана выгодного производства.
Фирма производит несколько видов продукции из одного и того же сырья – А, В и С. Реализация продукции А дает прибыль 10 руб., В – 15 руб. и С – 20 руб. на единицу изделия. Продукцию можно производить в любых количествах, поскольку считается, что сбыт обеспечен, но ограничены запасы сырья. Необходимо определить, какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной.
Нормы расхода сырья на производство продукции каждого вида приведены в таблице:
| Сырье | Норма расхода сырья | Запас сырья | ||
| А | В | С | ||
| Сырье 1 | ||||
| Сырье 2 | ||||
| Сырье 3 | ||||
| Прибыль |
А) На новом рабочем листе создайте расчетную таблицу и введите исходные данные и формулы:
Расход сырья 1 = Количество ед. продукции А * Норма сырья 1 + Количество ед. продукции В * Норма сырья 1 + Количество ед. продукции С * Норма сырья 1
| План выгодного производства | |||||
| Сырье | Норма расхода сырья | Запас сырья | Расход сырья | ||
| А | В | С | |||
| Сырье 1 | ? | ||||
| Сырье 2 | ? | ||||
| Сырье 3 | ? | ||||
| Прибыль на ед. изделия | |||||
| Количество | ? | ? | ? | ||
| Общая прибыль | ? | ? | ? | ? |
Б) В меню Сервис выберите команду Поиск решения, установите необходимые параметры:
- целевая ячейка, в которой будет подбираться результат (итоговая сумма прибыли);
- значение, которое необходимо установить в целевой ячейке (максимальное, минимальное или какое-либо конкретное значение);
- изменяемые ячейки – ячейки, значение в которых будет изменяться для подбора значения в целевой ячейке (Количество производимых единиц продукции А, В и С);
- условия ограничений, которые необходимо выполнять при подборе значений (Количество производимых единиц продукции А, В и С не может быть отрицательным, Расход каждого вида сырья не должен превышать его запасов);
- математические параметры поиска решений (окно появляется при выборе кнопки «Параметры» в окне «Поиск решения»).
Результаты расчета:
По результатам проведенного поиска сформулируйте выводы:
оптимальный план выпуска, при котором прибыль ____________________, предусматривает изготовление ______ продукции В и ______ продукции С. Продукцию А производить не стоит.
3. Используя рабочий лист, созданный в предыдущем задании, определите план выгодного производства для следующих вариантов заданий:
| Вариант 1 | ||||
| Сырье | Норма расхода сырья | Запас сырья | ||
| А | В | С | ||
| Сырье 1 | ||||
| Сырье 2 | ||||
| Сырье 3 | ||||
| Прибыль на ед. изделия | ||||
| Количество | ? | ? | ? | |
| Общая прибыль | ? | ? | ? | ? |
| Вариант 2 | ||||
| Сырье | Норма расхода сырья | Запас сырья | ||
| А | В | С | ||
| Сырье 1 | ||||
| Сырье 2 | ||||
| Сырье 3 | ||||
| Прибыль на ед. изделия | ||||
| Количество | ? | ? | ? | |
| Общая прибыль | ? | ? | ? | ? |
| Вариант 3 | ||||
| Сырье | Норма расхода сырья | Запас сырья | ||
| А | В | С | ||
| Сырье 1 | ||||
| Сырье 2 | ||||
| Сырье 3 | ||||
| Прибыль на ед. изделия | ||||
| Количество | ? | ? | ? | |
| Общая прибыль | ? | ? | ? | ? |
| Вариант 4 | ||||
| Сырье | Норма расхода сырья | Запас сырья | ||
| А | В | С | ||
| Сырье 1 | ||||
| Сырье 2 | ||||
| Сырье 3 | ||||
| Прибыль на ед. изделия | ||||
| Количество | ? | ? | ? | |
| Общая прибыль | ? | ? | ? | ? |
| Вариант 5 | ||||
| Сырье | Норма расхода сырья | Запас сырья | ||
| А | В | С | ||
| Сырье 1 | ||||
| Сырье 2 | ||||
| Сырье 3 | ||||
| Прибыль на ед. изделия | ||||
| Количество | ? | ? | ? | |
| Общая прибыль | ? | ? | ? | ? |
Практическое занятие № 6
Тема занятия:Анализ финансового состояния предприятия на основании данных баланса в Excel.
Цель занятия:научиться производить анализ финансового состояния предприятия с помощью формул в Excel.