Открытый урок Excel_с задачей оптимизации

Слайд #1

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ БЮДЖЕТНОЕ
УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ 
«ФИНАНСОВЫЙ УНИВЕРСИТЕТ ПРИ ПРАВИТЕЛЬСТВЕ
РОССИЙСКОЙ ФЕДЕРАЦИИ»
(Финуниверситет)
 
Московский финансовый колледж


Учебно-методическая разработка по проведению
мультимедийного урока по дисциплине
«Информатика»
Решение экономических задач в среде табличного процессора MS Excel

Задача: Анализ прибыльности предприятия
Преподаватель Московского финансового колледжа
Матиев А.Ш.
2020.

Слайд #2

Анализ прибыльности предприятия
Молокоперерабатывающий завод имеет 3 цеха и выпускает 3 вида продукции: молочный цех – пастеризованное молоко в упаковке 1л., цех по выпуску масла - сливочное масло в упаковке 180гр., цех по выпуску творога - творог 9% в упаковке 300гр. Составить для каждого цеха отдельную таблицу, в которой отразить исходные данные и определить при какой цене на данную продукцию результат деятельности принесет прибыль.

Слайд #3

Молочный цех

Слайд #4

1. Молочный цех:
объем продаж - 30000 бут. молока в месяц;
цена продажи находится в интервале цен конкурентов: Конкурент1 – 45 руб., Конкурент2 – 50 руб.;
вычислить выручку (доходы) = цена продажи* объем продаж;
вычислить переменные расходы, состоящие из:
материальные затраты – 1 200 000 руб.,
оплата труда – 50 000 руб.,
начисления на оплату труда – 30% от размера оплаты труда;
постоянные расходы – 120 000 руб.;
вычислить всего расходы = переменные расходы + постоянные расходы;
вычислить прибыль = доходы - всего расходы;
вычислить налог на прибыль = (ЕСЛИ прибыль > 0, то налог на прибыль – 20% от прибыли, иначе 0);
вычислить чистую прибыль = прибыль - налог на прибыль;
отразить результат деятельности = (ЕСЛИ чистая прибыль > 0, то результат – «прибыль», иначе – «убыток».

Слайд #5

Таблица к п.1 задачи

Слайд #6

Цех по выпуску сливочного масла

Слайд #7

2. Цех по выпуску сливочного масла:
объем продаж - 18000 упаковок сливочного масла в месяц;
цена продажи находится в интервале цен конкурентов: Конкурент1 – 65 руб., Конкурент2 – 70 руб.;
вычислить выручку (доходы) = цена продажи* объем продаж;
вычислить переменные расходы, состоящие из:
материальные затраты – 1 020 000 руб.,
оплата труда – 40 000 руб.,
начисления на оплату труда – 30% от размера оплаты труда;
постоянные расходы – 120 000 руб.;
вычислить всего расходы = переменные расходы + постоянные расходы;
вычислить прибыль = доходы - всего расходы;
вычислить налог на прибыль = (ЕСЛИ прибыль > 0, то налог на прибыль – 20% от прибыли, иначе 0);
вычислить чистую прибыль = прибыль - налог на прибыль;
отразить результат деятельности = (ЕСЛИ чистая прибыль > 0, то результат – «прибыль», иначе – «убыток».

Слайд #8

Таблица к п.2 задачи.

Слайд #9

Цех по выпуску творога

Слайд #10

3. Цех по выпуску творога:
объем продаж – 9 000 упаковок творога в месяц;
цена продажи находится в интервале цен конкурентов: Конкурент1 – 95 руб., Конкурент2 – 100 руб.;
вычислить выручку (доходы) = цена продажи* объем продаж;
вычислить переменные расходы, состоящие из:
материальные затраты – 720 000 руб.,
оплата труда – 36 000 руб.,
начисления на оплату труда – 30% от размера оплаты труда;
постоянные расходы – 120 000 руб.;
вычислить всего расходы = переменные расходы + постоянные расходы;
вычислить прибыль = доходы - всего расходы;
вычислить налог на прибыль = (ЕСЛИ прибыль > 0, то налог на прибыль – 20% от прибыли, иначе 0);
вычислить чистую прибыль = прибыль - налог на прибыль;
отразить результат деятельности = (ЕСЛИ чистая прибыль > 0, то результат – «прибыль», иначе – «убыток».

Слайд #11

Таблица к п.3 задачи

Слайд #12

Задача оптимизации
Планирование производства всегда сопряжено с поиском ответа на вопросы: какую продукцию, и в каких количествах выгоднее всего производить из имеющегося сырья, и т.п. Подобные задачи относятся к классу задач линейного программирования – оптимального планирования при условии заданных ограничений. Как правило, это минимизация затрат, максимизация выгоды и оптимизация решений. На сегодняшний день для создания экономических моделей и решения подобных задач существуют инструменты, позволяющие учитывать множество различных факторов при поиске решения. Один из таких инструментов – надстройка MS Excel «Поиск решения». Для Молокоперерабатывающего завода найти такое соотношение продукции, которое обеспечит получение максимальной прибыли с учетом заданных ограничений. Задача определена, теперь необходимо определиться с исходными данными.

Слайд #13

Каждая единица продукции имеет набор характеристик:
Цена – рыночная цена, за которую покупатель приобретает продукцию;
Переменные затраты на изготовление – сумма денег, которую предприятие затрачивает на производство одной единицы продукции. В переменные затраты принято включать не только стоимость сырья, но и заработную плату производственного персонала, начисления на заработную плату и др. – все расходы, которые меняются прямо пропорционально изменению объемов производства;
Себестоимость – затраты на изготовление и реализацию единицы продукции;
Прибыль, ед. – разница между ценой и себестоимостью единицы продукции;
Ограничение по продажам – максимальное количество изделий, которое может быть реализовано за определенный промежуток времени в условиях определенного рынка

Слайд #14

Вне зависимости от специфики товара, каждое предприятие также обладает некоторыми свойствами:
Ограничения производства – максимальное количество изделий, которое предприятие может произвести вне зависимости от номенклатуры;
Объем продаж – количество изделий определенной номенклатуры, которое требуется реализовать для получения максимальной прибыли;
Сумма продаж (доходы) – это выручка за все продажи определенной продукции (объем продаж умноженный на цену);
Переменные затраты – это та сумма, которую предприятие потратило на изготовление реализованной продукции (объем продаж умноженный на переменные затраты за единицу изделия);
Постоянные затраты - административные и управленческие расходы, расходы по реализации продукции, электроэнергия, коммунальные платежи, амортизационные отчисления – все расходы, которые напрямую не зависят от объемов производства;
Расходы предприятия – сумма переменных и постоянных затрат;
Прибыль – это чистая выручка компании (доходы за вычетом расходов).
 

Слайд #15

Предположим, что нам известна вся необходимая информация: цены, расходы предприятия, ограничения по продажам и ограничения производства. Заполним таблицу имеющимися значениями (Рисунок 1)
Рисунок 1. Условие задачи

Слайд #16

Строка «Объем продаж» – это те значения, которые мы будем оптимизировать, так как нам нужно составить оптимальный план производства: определить набор товаров, которые выгоднее всего производить при заданных ограничениях.
Когда условия обозначены, мы можем приступать к решению задачи при помощи надстройки «Поиск решений».
При поиске решения создается математическая модель, состоящая из целевого значения, изменяемых параметров и ограничений. Целевое значение – это тот показатель, который мы стремимся оптимизировать путем подбора значений изменяемых параметров в условиях заданных ограничений. В нашем случае целевое значение – прибыль, которая должна быть максимальной, изменяемые параметры – объем продаж, а ограничения – заранее известные ограничения по продажам, ограничения производства и некоторые другие.

Слайд #17

Запускаем инструмент «Поиск решения». Заполняем все необходимые поля по образцу (Рисунок 2)
Рисунок 2. Параметры поиска решения

Слайд #18

Нажимаем кнопку «Найти решение» и смотрим результаты (Рисунок 3)
Рисунок 3. Результат оптимизации

Слайд #19

Спасибо за внимание. Успехов Вам