Решение экономических задач с использованием электронных таблиц MS Excel

Цели урока:

1. Образовательные:

- формирование умений применять имеющиеся математические знания и знания из курса информатики к решению практических задач;
- ознакомление с задачами оптимизации и способами их решения с помощью ms Excel;

2. Развивающие:

– развитие внимания, познавательной активности, творческих способностей, логического мышления.

3. Воспитательные:

– воспитание интереса к предмету;
– самостоятельности в принятии решения;
– формирование культуры общения.

Методы обучения:

– частично-поисковый;
– проблемный.

Тип урока: Комбинированный.

Дидактическое и методическое оснащение урока:

– план выполнения работы в ms Excel, инструкции по выполнению практической работы.

1. Организация начала урока.

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

На предыдущих уроках мы рассмотрели основные понятия электронных таблиц. Сегодня вы должны показать, насколько хорошо владеете понятиями: ячейка, абсолютный и относительный адрес, умеете решать различные задачи в электронных таблицах.

Подготовительный этап.

- С какой целью используют Excel? (Электронные таблицы позволяют сформировать данные в виде таблицы, рассчитать содержимое ячейки с помощью формулы, представить числовую информацию в графическом виде).

Электронные таблицы – это мощная программа по обработке числовых данных. Excel позволяет не только производить расчеты, но и решать сложные задачи в различных сферах деятельности, такие как задачи оптимизации, прогнозирования.

Часто при решении практических задач возникают ситуации, когда необходимо достичь какой-то конкретной цели. Например, необходимо чтобы себестоимость продукции составляла 20 рублей. Специфика таких задач состоит в том, что в Вашем распоряжении есть математическая модель исследуемого процесса, например, закон ценообразования, но Вы не знаете, при каком значении входящего в нее параметров можно достичь поставленную цель. Решение таких задач можно искать методом перебора. Однако в лучшем случае на это уходит много времени. Можно предложить другие способы решения. В Excel они реализованы как поиск значения параметра формулы, удовлетворяющего ее конкретному значению.

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

Познакомимся с этой процедурой на примере составления штатного расписания предприятия.

2. Изучение нового материала.

a. Использование надстройки “Поиск решения” при решении прикладных задач.

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

• Продавец получает в 1,5 раза больше подсобного рабочего (А₂=1,5; В₂=0);
• Рабочий-специалист – в 3 раза больше подсобного рабочего (А₃=0;В₃=0);
• Менеджер - на 30 у.е. больше, чем рабочий-специалист(А₄=3; B4=30);
• Заведующий производством - в 2 раза больше грузчика(А₅=2; В₅=0);
• Зав. складом - на 40 у.е. больше продавца (А₆=1,5; В₆=40);
• Бухгалтер - в 4 раза больше подсобного рабочего (А₇=4; В₇=0);
• Директор - на 20у.е. больше бухгалтера (А₈=4; В₈=20);

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

- Как вы думаете, что мы возьмем за основу для расчета зарплаты работников предприятия? (оклад подсобного рабочего) Почему? (Все другие оклады рассчитываются исходя из оклада подсобного рабочего). За основу возьмем оклад подсобного рабочего, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше.

Введем обозначения коэффициентов: А – показывает, во сколько раз оклад по должности больше оклада подсобного рабочего; А₁—для подсобного рабочего, А₂- для продавца и т.д.

В – коэффициент, который показывает, на сколько больше. В₁, В₂ и т.д.

- Что еще известно в задаче? (количество работников каждой должности) Обозначим количество работников через N: N₁,N_2,…N₈
- Чтобы рассчитать зарплату для каждой должности, мы должны знать оклад подсобного рабочего. Обозначим его С.

Мы знаем количество человек на каждой должности, коэффициенты и то, что фонд заработной платы =10000 у.е. Каким образом мы можем записать математическую модель решения этой задачи? Нашу модель можно записать как уравнение

N₁*A₁*C+N₂*(A₂*C+B₂)+...+N₈*(A₈*C+B₈) = 10000, проверим по таблице

В этом уравнении нам известны A₁...A₈, B₁...B₈ и N₁... N₈, а С неизвестно.

Анализ уравнения показывает, что задача составления расписания свелась к решению линейного уравнения относительно С. Решим его.

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

Предположим, что оклад подсобного рабочего равен 150 у.е.

- Сколько столбцов нам необходимо построить?

Введем исходные данные в рабочий лист электронной таблицы, как показано:

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

Вспомните правила набора формулы в строке формул.

Записываем формула, начиная со знака “=”. Какую формулу мы должны ввести, чтобы рассчитать зарплату подсобного рабочего? (Для ячейки D4 формула расчета имеет вид =B4*$H$8+C4). Почему нам необходима абсолютная ссылка на ячейку H8? ( параметры этой ячейки не должны изменяться)

Нужно ли вводить формулы в каждую ячейку столбца D? (Можно воспользоваться автозаполнением).

В столбце F вычислите заработную плату всех рабочих данной должности. Какую формулу введем в ячейку F4 (формула расчета имеет вид =D4*E4).

В ячейке F12 вычислите суммарный фонд заработной платы предприятия. Какой функцией воспользуемся? (СУММ или пиктограммой “Автосумма”). Рабочий лист электронной таблицы будет выглядеть, как показано ниже.

Что же получилось? Взяв оклад подсобного рабочего за 150 у.е., мы превысили месячный фонд зарплаты. Определим оклад подсобного рабочего так, чтобы расчетный фонд был равен заданному. Как решить поставленную задачу? (уменьшить оклад подсобного рабочего, скажем, до 120 у.е.). Для решения этой задачи воспользуемся процедурой “Подбор параметра”.

• активизируем команду Подбор параметра из меню Сервис;
• в поле "Установить в ячейке" появившегося окна введем ссылку на ячейку F12, содержащую формулу;
• в поле "Значение" наберем искомый результат 10000;
• в поле "изменяя значение ячейки" введем ссылку на изменяемую ячейку H8 и щелкните на кнопке ОК.

Как видите, программа нашла оптимальное решение.

Анализ задачи показывает, что с помощью Excel можно решать линейные уравнения. Конечно, такое уравнение может решить любой школьник. Однако, благодаря этому простому примеру стало, очевидным, что поиск значения параметра формулы, удовлетворяющего ее конкретному значению, - это не что иное, как численное решение уравнений. Другими словами, используя Excel, можно решать любые уравнения с одной переменной.

- Для чего используется процедура “Подбор параметров”?

b. Задачи оптимизации.

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

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

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

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

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

Составление штатного расписания

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

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

Нужно ли менять уравнение, составленное нами для решения предыдущей задачи? Запишем ее так:

N₁*A₁*C+N₂*(A₂*C+B₂)+...+N₈*(A₈*C+B₈) = Минимум.

В этом уравнении нам не известно число подсобных рабочих (N1), продавцов (N2), и оклад подсобного рабочего(С).

Используя Поиск решения, найдем их. В меню Сервис активизируем команду Поиск решения. В окне Установить целевую ячейку укажем ячейку F12, содержащую модель. Поскольку необходимо минимизировать общий месячный фонд зарплаты, то активизируем радиокнопку Минимальному значению. Используя кнопку Добавить, опишем ограничения задачи. Какие ограничения следует добавить?Окончательно окно Поиска решения будет выглядеть так:

Опишем Параметры поиска. Щелкнем на кнопке ОК, а затем - Выполнить.

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

Задачи, в которых необходимо найти оптимальное значение параметров, называются задачами оптимизации.

Для закрепления пройденного материала решим следующую задачу.

3. Закрепление. Практическая работа.

Решим подобную задачу самостоятельно. Прочитайте условия задачи.

План выгодного производства

Предположим, что мы решили производить несколько видов конфет. Назовем их условно "A", "B" и "C". Известно, что реализация 10-и килограмм конфет "А" дает прибыль 9 у.е.., "В" - 10 у.е. и "С" - 16 у.е.

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

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

Какие формулы нам нужно будет ввести, чтобы рассчитать общую прибыль от производства? Чтобы рассчитать прибыль от производства каждого сорта конфет (Количество нужно умножить на прибыль от производства 10 кг конфет). Как рассчитать расход каждого вида сырья? (количество выпускаемых конфет умножить на нормы расхода каждого вида сырья). Какая ячейка будет содержать математическую модель?

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

4. Рефлексия.

- Что вы можете сказать о сегодняшнем уроке?
- Как вы считаете, полезны ли для вас знания и умения, которые вы получили сегодня на уроке?
- Владея технологией работы в электронных таблицах, вы сможете не только грамотно организовать предприятия, но и оптимизировать его производство.