Лабораторная работа №4 «Решение экономических и управленческих задач в табличном процессоре MS Excel. ».

Теоретический минимум:

· Для решения экономических и финансовых задач в табличном процессоре существует специальный набор встроенных функций – Финансовые.

· Для того чтобы вставить функцию необходимо:

1. Выделить ячейку для вставки функции.

2. На вкладкеФормулы,в разделеБиблиотека функцийвыбрать необходимую группу функций (например,Финансовые).

3. В данной группе выбрать функцию необходимую для решения поставленной задачи.

4. В открывшемся окне Аргументы функции указать диапазон данных необходимый для вычисления данной функции.

Практикум:

Задача №1 Составление штатного расписания.

В штате больницы состоит 6 санита­рок, 8 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующий хозяйством и заведующий больницей. Общий месячный фонд зарплаты составляет 10 ООО у. е. Необходимо определить, какими должны быть оклады сотрудников больницы.

За основу возьмем оклад санитарки, а остальные оклады будем вычислять, исходя из него: во сколько-то раз или на сколько-то больше.

А_i*C+B_i , где С – оклад санитарки, А_i и B_i – коэффициенты (А_i- во сколько больше, B_i- на сколько больше).

Нам известно количество сотрудников и общий месячный фонд зарплаты. Неизвестен оклад санитарки.

Технология работы:

1. Запустите табличный процессор MS Excel. (Пуск/Программы/MS office /MS Excel 2007.

2. Наберите условие задачи рис.1. Причем в ячейку G2 (зарплата санитарки) введем пока произвольное значение, например 150.

Рисунок 1

3. Вычислите зарплату сотрудника для санитарки. Для этого в ячейку D2 введите формулу: =B2*$G$2+C2.

4. Скопируйте эту формулу в диапазон ячеек D3:D9.

5. Вычислите суммарную зарплату для санитарок. Для этого в ячейку F2 введите формулу: = D2*Е2.

6. Скопируйте эту формулу в диапазон ячеекF3:F9.

7. Вычислите суммарный фонд заработной платы, используя функцию автосумма. Для этого: в ячейку F10введите формулу: =СУММ(F2:F9).

8. Просмотрите результат (рис.2).

Рисунок 2

Как видите, взяв оклад санитарки 150, мы превысили месячный фонд заработной платы. Определите оклад санитарки, так, чтобы расчетный фонд был равен заданному. Для этого:

- На вкладкеДанные,в разделеРабота с даннымивыбрать Анализ «Что-если»/Подбор параметра.

- в поле «Установить в ячейке» появившегося окна введите ссылку на ячейку F10, содержащую формулу;

- в поле «Значение» наберите искомый результат 10000.

- В поле «Изменяя значение ячейки» введите ссылку на из­меняемую ячейку G2 (рис.3) и щелкните кнопкой ОК.

Рисунок 3

Просмотрите полученную таблицу, зарплата санитарки равна 143,79562, установите для необходимых ячеек денежный формат (в У.Е).

Сохраните изменения в вашем файле.

Задача №2 Составление штатного расписания (часть 2: оптимизация). Пусть известно, что для нормальной работы больницы необходимо 5-7 санитарок, 8-10 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующий хозяйством и заведующий боль­ницей. Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников больницы, при условии что оклад санитарки не должен быть мень­ше прожиточного минимума - 80 у. е.

Технология работы:

1. В таблице с штатным расписанием изменим кол-во сотрудников и оклад санитарки рис.4. Установим по минимуму.

Рисунок 4

2. Необходимо найти оптимальное число санитарок, медсестер, врачей и оклад санитарки.

3. Используем команду Поиск решения. Для этого:

- На вкладке Данные в разделе Анализ выберите Поиск решения (Если эта вкладка отсутствует необходимо открыть кнопку office/Параметры Excel/Надстройки/Поиск решения).

- Установите настройки (рис.5).

Рисунок 5

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

5. Сохраните изменения в документе.

Задача №3 План выгодного производства. Предположим, что мы решили производить несколько видов конфет. Назовем их условно «А», «В», «С». Известно, что реализа­ция 10 килограммов конфет «А» дает прибыль 9 у. е., «В» - 10 у. е., «С» - 16 у. е. Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько десятков килограммов необходимо произ­вести, чтобы общая прибыль от реализации была максимальной.

Нормы расхода сырья на производство 10 кг конфет каждого вида приведены ниже.

Технология работы:

1. Перейдите на второй лист документа.

2. Наберите условие задачи рис.6.

3. Вставьте в ячейки необходимые формулы:

Рисунок 6

4. На вкладке Данные в разделе Анализ выберите Поиск решения.

5. Установите значения, так как показано на рис7.

Рисунок 7

6. Откройте в окне Поиск решения вкладку Параметры и установите Линейную модель.

7. Нажмите выполнить. Просмотрите результат. Из решения видно, что оптимальный план выпуска преду­сматривает изготовление 80 кг конфет «В» и 20 кг конфет «С». Конфеты «А» производить не стоит. Полученная вами прибыль составит 400 у. е.

8. Переименуйте все листы табличного документа, согласно названию задач.

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

Не нашли, что искали? Воспользуйтесь поиском по сайту: