Использование MS Excel для решения экономических задач

Всем известна востребованность экономических знаний сегодня. Самостоятельность в принятии решений, эффективный анализ проблемы, отстаивание собственной точки зрения – эти качества приобретёт каждый, работая в одной команде над решением экономической задачи.

В данном уроке представлены задачи оптимального планирования производства и оценки эффективности денежных вложений с помощью финансовых функций MS Excel.

Урок проводится в 11 классе при изучении технологий обработки числовой информации (по учебнику "Информатика. 11 класс", под ред. Семакина И.Г.). Учащиеся владеют основными экономическими понятиями и терминами из изучаемого курса экономики.

Тема: Математическое моделирование в планировании и управлении.

Тема урока: Использование MS Excel для решения экономических задач (2 часа).

Цели урока:

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

– повторение и обобщение изученного материала по темам: текстовый редактор, математическое моделирование, электронные таблицы, относительная и абсолютная адресация, встроенные функции Excel .

2. Познавательные:

– формирование учебного действия "подведение под понятие";
– формирование учебного действия по выведению следствия из факта принадлежности объекта понятию;
– формирование умений применять имеющиеся математические знания и знания из курса информатики к решению практических задач;
– развитие внимания, познавательной активности, творческих способностей, логического мышления.

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

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

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

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

Тип урока: Урок повторения и обобщения знаний, умений и навыков учащихся.

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

– дидактические материалы.

Подготовка к уроку: Занятия проводятся в группе по 12-15 человек. Учащиеся заранее делятся на 2 группы примерно с равным уровнем знаний.

План урока:

1. Мотивационно-ориентировочный этап: разъяснение учащимся целей учебной деятельности, задач и хода урока.
2. Подготовительный этап: актуализация опорных знаний учащихся.
3. Основной этап: работа в группах.
4. Заключительный этап: выводы по уроку и подведение итогов.

Ход урока:

1. Сегодня мы проведём несколько необычный урок. Каждый из вас на уроке попытается стать предпринимателем, открыть своё дело.

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

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

Уильям А. Уард однажды сказал: “Четыре шага к достижению: целеустремлённый план, тщательная набожная подготовка, положительные действия, постоянная настойчивость”. Воспользуемся его советом, переложим его слова применительно к нашему времени и попробуем составить бизнес-план вашего будущего предприятия. Это будет ваша задача на сегодняшний урок.

С какими же проблемами мы можем столкнуться при создании собственного предприятия? Какие два самых важных вопроса встанут перед вами?

– 1) Что это будет за предприятие?

– 2) Сколько средств необходимо вложить в своё дело на начальном этапе и в будущем?

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

– Стоимость оборудования;
– Аренда (покупка) помещения;
– Оплата энергетических ресурсов;
– Регулярные отчисления (налоги, выплаты);
– Покупка средств передвижения (машины).

Затем перед нами встанет задача оптимального планирования производства. Как организовать работу своего предприятия так, чтобы оно было прибыльным?

Итак, давайте начнём с выбора производства. Как вы думаете, от чего зависит выбор производства?

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

Мы разделимся на две группы. Каждая группа попытается составить свой бизнес-план создания и развития своего предприятия. Каждая группа получит полный список документов, которые необходимо представить для открытия и регистрации своего предприятия. И вы в группе сами распределите, кто каким видом деятельности будет заниматься. По окончании урока вы сдаёте полный пакет документов на рассмотрение учителя. В ходе подготовки необходимых документов вы можете воспользоваться любыми программными средствами MS Windows и MS Office2000.

2. Рассмотрим подробнее первый пакет необходимых документов.

Пакет № 1. Должен содержать титульный лист с названием и полной характеристикой вашего предприятия и рекламный проспект вашего предприятия.

Пакет № 2. Необходимо представить полное решение и оформление следующих задач:

1) Из какой суммы необходимо исходить для того, чтобы выгодно начать своё дело (чтобы прибыль составляла 100%)?

2) Какова должна быть себестоимость выбранной вами продукции?

Расчёты представить для 1-й рабочей смены (8 часов) в цеху и для 2 видов продукции в количестве 700 штук.

В отчёте отразить следующее:

Вид продукции	Пирожки	Булочки
Производительность труда
Отпускная цена продукции
Сумма реализации
Затраты:
– на производство продукции
– на оборудование
– на аренду помещения
– зар. плата рабочим
– электроэнергия
– налоги
Итого затрат
Прибыль
Чистая прибыль=Сумма реализации – Расходы
Всего затраты
Себестоимость продукции

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

Следующая важная проблема, с которой вы сталкиваетесь, – проблема оптимального планирования производства. Это очень важная проблема, и от её правильного решения зависит прибыльность вашего производства. Действительно, в этом случае справедлив так называемый закон Букера: “Даже маленькая практика стоит большой теории”. Вам придётся очень хорошо подумать над решением данной задачи. Она представлена вам в пакете № 3.

Пакет № 3. Дневной план производства.

Задача: Школьный кондитерский цех производит булочки и пирожные. В силу ограниченности ёмкости склада за день можно приготовить в совокупности не более 700 изделий. Рабочий день в кондитерском цехе длится 8 часов. Если выпускать только пирожные, за день можно произвести не более 250 штук, булочек же можно произвести 1000, если не выпускать пирожных. Себестоимость продукции известна (из предыдущего пакета №2). Требуется составить дневной план производства, обеспечивающий кондитерскому цеху наибольшую выручку.

Какими средствами вы воспользуетесь для решения данной задачи?

– Средствами математического моделирования.

Необходимо составить математическую модель данной задачи. Составить целевую функцию.

И к какой новой задаче мы должны прийти?

– Найти значения плановых показателей х и у, при которых целевая функция принимает максимальные значения.

Пакет № 4.Регулярные выплаты.

Вы знаете, что каждое предприятие ежемесячно отчисляет n-сумму средств.

Что же входит в понятие регулярных выплат?

– выплаты по ссуде;
– техобслуживание;
– арендная плата;
– зар. плата.

Какую функцию предоставляет Excel для работы с регулярными выплатами?

– БЗ();
– ПЗ();
– ППЛАТ().

Выберите, какую из функций вы будете использовать при решении следующей задачи:

Определите сколько денег должно быть в бюджете компании в начале года, чтобы она имела возможность ежемесячно выплачивать 600$ за оборудование, если бюджетные деньги обеспечивают компании прибыль по эффективной годовой ставке 5%?

Пакет № 5. Экономические альтернативы.

Для нужд вашего предприятия необходим грузовик. На каких условиях лучше купить грузовик, стоимостью 15000$: взять ссуду под 1,5% годовых или купить его со скидкой 1500$, выплачивая более высокие проценты по ссуде?

Здесь вы подошли к серьёзному испытанию – выбору наиболее благоприятного варианта. Вам придётся сделать свой выбор. Как лучше заключить сделку? Как выгоднее оформить ссуду? Вы должны обосновать правильность сделанного выбора.

Итак, что лучше?

1) Выплачивать полную стоимость ссуды, однако при этом будут начисляться низкие проценты по ссуде.

2) Выплачивать стандартную процентную ставку (9% годовых, начисляемых ежемесячно), получая при этом скидку.

Другая задача: Холодильник можно купить, воспользовавшись одним из таких вариантов:

– Холодильник стоит 60000$ и срок его эксплуатации в среднем составляет 10 лет: на техобслуживание такого холодильника придётся тратить 2200$ в год, а его ликвидационная стоимость составляет 12000$.
– Холодильник стоит 32000$ и в среднем имеет пятилетний срок эксплуатации; на техобслуживание такого холодильника придется затрачивать 2600$ в год, а его ликвидационная стоимость равна 0.

Какая сделка выгоднее?

1) Покупка дорого оборудования, требующего небольших затрат на техобслуживание и обладающего более длительным сроком эксплуатации.

2) Приобретение более дешевого оборудования, техобслуживание которого будет стоить дороже, а эксплуатация продлится меньше.

Для ответа на эти вопросы необходимо привести все денежные суммы к одному и тому же моменту времени, к текущему моменту. И найти величину ежемесячных выплат в каждом случае.

Подумайте, какую функцию вы будете использовать в данном случае?

– ППЛАТ().

3. Учащиеся приступили к решению задач и оформлению необходимых документов.

Приводим решение некоторых задач.

Задача из пакета №3.

Составим математическую модель задачи.

Плановыми показателями являются:

Х – дневной план выпуска булочек, У – дневной план выпуска пирожных. Для определённости будем считать, что стоимость пирожного вдвое больше, чем булочки (учащиеся возьмут свои, получившиеся в пакете №2, показатели себестоимости продукции). Из условия задачи следует, что на изготовление одного пирожного затрачивается в 4 раза больше времени, чем на изготовление одной булочки. Если обозначить время изготовления булочки – t мин, то время изготовления пирожного будет равно 4t мин. Значит, суммарное время на изготовление х булочек и у пирожных равно tх + 4tу = (х+4у)t. Но это время не может быть больше длительности рабочего дня. Отсюда следует неравенство: (х+4у)t<=8*60 или (х+4у)t<=480. Можно вычислить t – время изготовления одной булочки. Т.к. за рабочий день их может быть изготовлено 1000 штук, то на одну булочку затрачивается 480/1000=0,48 мин. Подставляя это значение в неравенство, получим: (х+4у)0,48<=480. Отсюда: х+4у<=1000. Ограничение на общее число изделий даёт неравенство: х+4у<=700. К двум полученным неравенствам следует добавить условие положительности значений величин х и у (не может быть отрицательного числа булочек и пирожных). В итоге мы получаем систему неравенств:

х+4у<=1000;

х+у<=700;

х>=0;

у>=0.

Выручка – это стоимость всей проданной продукции. Пусть цена одной булочки – r рублей. По условию задачи, цена пирожного 2r рублей. Отсюда стоимость всей произведённой за день продукции равна rх + 2rу=r(х+2у). Будем рассматривать записанное выражение как функцию от х и у. Получили целевую функцию: f(x,y)= r(х+2у). Т.к. r – константа, то максимальное значение функции будет достигнуто при максимальной величине выражения (х+2у). Поэтому в качестве целевой функции можно принять f(x,y)=х+2у.

Теперь подготовим электронную таблицу к решению задачи оптимального планирования: рис. 1.

Выполним поиск решения.

Результаты решения задачи: рис. 2.

Получили следующий оптимальный план дневного производства: нужно выпускать 600 булочек и 100 пирожных. При этом достигается получение максимальной прибыли – 1600 рублей.

Задача из пакета № 4.

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

Эффективную годовую ставку (5%) нужно преобразовать в периодическую ежемесячную процентную ставку. Это две величины связаны между собой таким уравнением:

где

i_n – периодическая процентная ставка,

i_э – эффективная годовая процентная ставка,

N – количество периодов начисления процентов за год.

Подставив в формулу, получим, что эффективная годовая процентная ставка, равная 5%, эквивалентна ежемесячной процентной ставке 0,4074%:

i_n= 0,004074, или 0,4074% в месяц.

Сумму, которую нужно оставить в бюджете на выплату счетов за оборудование, находим с помощью функции ПЗ().

Так как на сумму, которая находится в бюджете, начисляются проценты, на выплату всех двенадцати ежемесячных платежей в начале года достаточно будет отложить чуть больше 7000$.

Задача из пакета № 5.

Найдём величину ежемесячных выплат в каждом из описанных случаев.

1) Годовая процентная ставка составляет 1,5% (в ячейке В3 содержится формула В3:=1,5%/12, или 0,125% в месяц), количество месяцев равно 48. Принимая во внимание, что приведенная стоимость автомобиля – 15000$, находим ежемесячные выплаты: ППЛАТ=$322,16.

Или:

2) Годовая процентная ставка составляет 9% (в ячейке В3 содержится формула В3:=9%/12, или 0,750% в месяц ), количество месяцев равно 48. Принимая во внимание, что приведённая стоимость автомобиля со скидкой – 135000$, находим ежемесячные выплаты: ППЛАТ=$335,95.

Ссуда, предоставляемая по годовой процентной ставке, равной 1,5%, обеспечивает более низкие выплаты, хотя разница очень мала. В данной задаче первый вариант предпочтительнее, хотя при увеличении суммы скидки ситуация может измениться.

4. Подготовленные документы сдаются на рассмотрение учителю.

(Выставляется общая оценка группе, ошибки и недостатки работ подробно разбираются на следующем занятии)

Пройдёт совсем немного времени, и вы выйдете в большую взрослую жизнь. Я думаю, вам нечего бояться предстоящих трудностей, вы справитесь с ними так же, как и с предложенными вам сегодня задачами. Упорство, вооруженное знаниями, высокая работоспособность, я верю, приведут вас к победе. Успехов вам!