Математическая модель

Каждый оклад является линейной функцией от оклада диспетчера и рассчитывается по формуле: А Ÿ С+В

где C - оклад диспетчера;

A - коэффициент, который определяет во сколько раз оклад какой-либо должности больше оклада диспетчера;

B - коэффициент, который определяет, на сколько оклад какой-либо должности больше оклада диспетчера.

Задав количество человек на каждой должности, можно составить уравнение:

N1 Ÿ (A1 Ÿ C+B1) +N2 Ÿ (A2 Ÿ C+B2) +...+N8 Ÿ (A8 Ÿ C+B8)=10000

где N1 - количество диспетчеров, N2 - количество ремонтных рабочих, и т.д.;

A1...A8 и B1...B8 - коэффициенты для каждой должности.

В этом уравнении нам известны A1...A8 и B1...B8, но не известны C и N1...N8.

Решить такое уравнение можно путем подбора.

Взяв первоначально какие-либо приемлемые значения неизвестных, подсчитаем сумму. Если фонд заработной платы превышен, то можно снизить оклад диспетчера, либо отказаться от услуг какого-либо работника, и т.д., пока эта сумма не будет равна установленному фонду оплаты труда. Проделать такую работу вручную трудно. Для создания данной модели используется MS Excel 2010 (2013).

Компьютерная модель

1. Создайте таблицу и сохраните её в свою папку под именем Штатное расписание:

A	B	C	D	E	F	G	H
коэф. А	коэф. В	Должность	Зарплата сотрудника	Кол-во сотрудников	Суммарная зарплата		Зарплата диспетчера
1	0	Диспетчер		7			150,00
1,5	0	Ремонтный рабочий		9
3	0	Водитель		10
3	30	Механик		3
2	0	Зав. гаражом		1
1,5	40	Зам. директора		1
4	0	Гл. инженер		1
4	20	Ген. директор		1
		Суммарный месячный фонд зарплаты:

2. В столбце D следует вычислить заработную плату для каждой должности.

В постановке задачи было объяснено, что заработная плата вычисляется по формуле А Ÿ С+В. В нашей таблице коэффициенты А и В находятся в столбцах А и В, а С - зарплата диспетчера указана в ячейке Н2. Обратите внимание, что формулы вычисления зарплаты сотрудников должны содержать абсолютный адрес ячейки Н2.

· В ячейку D2 введите формулу =A2*$H$2+B2

· Скопируйте формулу из ячейки D2 в ячейки D3:D9.

· При копировании адрес ячейки с зарплатой диспетчера остался постоянным (абсолютным), а адреса A2 и B2 перенастраиваются (они относительные).

3. В столбце F следует вычислить заработную плату всех сотрудников каждой должности.

В столбце Е указано количество сотрудников каждой должности. Данные в ячейках E2:E4 могут изменяться в пределах штатного расписания, а количество сотрудников на других должностях неизменно (см. постановку задачи).

· В ячейку F2 введите формулу =D2*E2 (т.е. "зарплата" * "количество сотрудников").

· Скопируйте формулу из ячейки F2 в F3:F9.

4. В ячейке F10 найдите суммарный месячный фонд заработной платы всех сотрудников, т.е. сумму значений ячеек F2:F9.

5. Оформите таблицу:

6. Составьте штатное расписание: вносите изменения в зарплату диспетчера в ячейке H2 или меняйте количество сотрудников в ячейках E2:E4 (см. постановку задачи) до тех пор, пока полученный суммарный месячный фонд заработной платы не будет равен заданному (т.е. в ячейке F10 необходимо получить значение приблизительно равное 10000).

7. Сохраните таблицу и предъявите преподавателю файл работы Штатное расписание с 1 листом: Модель

 

Задание № 2.

4. Компьютерный эксперимент:

1. Составьте штатное расписание с использованием функции автоматизации расчетов – Подбор параметра.

Функция Подбор параметра удобное средство Excel для анализа “Что - если ”. При этом подбирается такое значения для ячейки с изменяемым параметром, чтобы число в целевой ячейке стало равно заданному.

· Выберите команду меню Данные, Анализ ”Что - если”, Подбор параметра и:

· укажите в окне. У становить в ячейке адрес целевой ячейки F10 (Фонд заработной платы);

· введите в окно Значение - 10000;

· укажите в окне. И зменяя значение ячейки адрес ячейки H2 (зарплата диспетчера), т.е. адрес именно той ячейки, от которой зависит расчет всей таблицы;

· нажмите OK.

· Начнется процесс подбора параметра. На рисунке показан результат подбора параметра.

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

2. Создайте лист и переименуйте его в Варианты.

3. Составьте 4 варианта штатного расписания и оформите их в виде таблицы: