Решение экономических задач в excel

Решение экономических задач в excel

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

В рамках данной статьи рассмотрим использование функции ВПР для решения экономических задач.

Описание и синтаксис функции

ВПР – функция просмотра. Формула находит нужное значение в пределах заданного диапазона. Поиск ведется в вертикальном направлении и начинается в первом столбце рабочей области.

Аргумент «Интервальный просмотр» необязательный. Если указано значение «ИСТИНА» или аргумент опущен, то функция возвращает точное или приблизительное совпадение (меньше искомого, наибольшее в диапазоне).

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

ВПР в Excel и примеры по экономике

Составим формулу для подбора стоимости в зависимости от даты реализации продукта.

Изменения стоимостного показателя во времени представлены в таблице вида:

Нужно найти, сколько стоил продукт в следующие даты.

Назовем исходную таблицу с данными «Стоимость». В первую ячейку колонки «Цена» введем формулу: =ВПР(B8;Стоимость;2). Размножим на весь столбец.

Функция вертикального просмотра сопоставляет даты из первого столбца с датами таблицы «Стоимость». Для дат между 01.01.2015 и 01.04.2015 формула останавливает поиск на 01.01.2015 и возвращает значение из второго столбца той же строки. То есть 87. И так прорабатывается каждая дата.

Составим формулу для нахождения имени должника с максимальной задолженностью.

В таблице – список должников с данными о задолженности и дате окончания договора займа:

Чтобы решить задачу, применим следующую схему:

  1. Для нахождения максимальной задолженности используем функцию МАКС (=МАКС(B2:B10)). Аргумент – столбец с суммой долга.
  2. Так как функция вертикально просматривает крайний левый столбец диапазона (а суммы находятся во втором столбце), добавим в исходную таблицу столбец с нумерацией.
  3. Чтобы найти номер предприятия с максимальной задолженностью, применим функцию ПОИСКПОЗ (=ПОИСКПОЗ(C12;C2:C10;0)). Тип сопоставления – 0, т.к. к столбцу с долгами не применялась сортировка.
  4. Чтобы вывести имя должника, применим функцию: =ВПР(D12;Должники;2).

Сделаем из трех формул одну: =ВПР (ПОИСКПОЗ (МАКС (C2:C10); C2:C10;0); Должники;2). Она нам выдаст тот же результат.

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

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

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

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

Попробуйте простыми расчетами решить такую задачу:

Компания занимается производством шкатулок ручной работы. В штате есть 4 мастера-надомника. Производительность мастеров в день представлена в таблице:

Мастер 1 — 3 шкатулки в день
Мастер 2 — 1,5 шкатулки в день
Мастер 3 — 2 шкатулки в день
Мастер 4 — 2,5 шкатулки в день

Эту задачу можно решить простыми расчетами, без использования поиска решений. Для начала так и поступим:

Рассчитаем, сколько шкатулок в день могут произвести все мастера:

3+1,5+2+2,5 = 9 шкатулок.

Теперь 100 шкатулок разделим на 9 шкатулок в день и получим 11,11 дней. Соответственно, сообщаем заказчику, что заказ будет готов за 12 дней

Распределим заготовки между мастерами с использованием округления:

Мастер 1: 3 х 11,11 = 33,33 Выдаем 34 заготовки
Мастер 2: 1,5 х 11,11 = 16,66 Выдаем 17 заготовок
Мастер 3: 2 х 11,11 = 22,22 Выдаем 21 заготовку
Мастер 4: 2,5 х 11,11 = 27,77 Выдаем 28 заготовок

А теперь дополним условие и введем индивидуальные тарифные ставки для мастеров за изготовление каждой шкатулки:

Мастер 1 — 1 500 руб
Мастер 2 — 950 руб
Мастер 3 — 1 100 руб
Мастер 4 — 1 150 руб

Рассчитаем для клиента, сколько стоит изготовить 100 шкатулок

34 х 1 500 = 51 000
17 х 950 = 16 150
21 х 1 100 = 23 100
28 х 1 150 = 32 200
Итого: 122 450

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

Таким образом, путем простых расчетов и логических рассуждений мы вывели основные отправные точки для диалога с клиентом:

Минимальный срок изготовления: 11,11 дней,
стоимость 122 450

Максимальный срок
изготовления: 66,66 дней,
стоимость 95 000

Разница в сроке составляет 55,55 дней, а в сумме 27 450.

Вполне логично предположить, что клиент может задать вопрос:
А если я дам Вам срок 20 дней или месяц, как изменится стоимость?

Создайте новый файл в программе Excel.
Проверьте, есть ли кнопка "Поиск решения" в закладке "Данные", если нет, то здесь инструкция, как ее установить.

Наша цель: как распределить заготовки, чтобы заказ был выполнен за 20 дней и стоимость была минимальной? Сколько будет стоить в этом случае выполнение заказа?

Попытайтесь сами ответить на вопросы и записать ответы:
1. Какие у нас есть константы?
2. Что будет переменными?
3. Целевая функция
4. Ограничения

1. Устанавливаем целевую ячейку E13. Это целевая функция , которая равна общей стоимости заказа. Переключаем цель, чтобы она была равной минимальному значению.

2. Вносим диапазон переменных D9:D12 в поле "Изменяя ячейки".

Читайте также:  Влияет ли датчик скорости на расход топлива

3. Вносим ограничения:
D13 (общее число заготовок) = 100 (внесем не значение 100, а ячейку D3, чтобы в дальнейшем можно было изменить количество шкатулок в задаче)
Диапазон переменных D9:D12 = целые
Диапазон переменных D9:D12 >= 0 (неотрицательные)
F13 ( срок выполнения заказа : в эту ячейку внесена формула, которая выбирает максимальное значение из сроков по каждому мастеру, она выглядит так =МАКС(F9:F12)) (внесем не значение 20, а ячейку D6, чтобы можно было изменять)

THS готовит план производства на следующий месяц. Максимально доступные (за месяц) объемы ресурсов приведены в таблице:
Материал А — 5 000 кг
Материал В — 5 400 кг
Работа оборудования — 3 000 часов
Квалифицированный труд — 4 500 часов

Задание: Определить оптимальный план производства, максимизирующий прибыль.

Колонка «Значение»
По строкам 1 и 2 в будет внесено оптимальное количество продуктов. Сейчас их оставляем пустыми, функция сама их заполнит. На эти ячейки ссылаются все последующие формулы.
По строке 3 «Целевая функция» — вносим формулу со ссылкой на ячейки Х и У выше. Пока она равна нулю, ПОИСК РЕШЕНИЯ будет ее максимизировать и заполнит значение.
Далее по строкам 4-10 вносим ограничения в виде формул. Например, 3х+2у, также со ссылками на ячейки Х и У.

Колонка «Ограничение»
Ограничения можно внести как в ячейки на листе, так и непосредственно в окне "Поиск решения". В данном случае они внесены в таблицу, чтобы можно было заполнить формулу в следующей колонке (которая называется "Излишек"), т.к. она участвует в финальной матрице.

Колонка «Излишек»
Здесь вносим формулу: Ограничение — Значение. После того, как функция рассчитает все значения, мы сразу сможем видеть ограниченные ресурсы и излишки.

Итак, таблица готова и выглядит, как приведено выше.

Максимальное значение прибыли 62 625 долл , достигается при выпуске оптимального ассортимента: Продукт Е — 375 ед, Продукт R — 750 ед.

Смотрим колонку "Избыток"
Имеется избыток Материала А и материала В в размере 2 375 ед и 1 650 ед, соответственно.
На продукт У имеется нереализованный спрос 750 ед.

Оборудование и труд являются ограничивающими ресурсами. Это значит, что если бы у нас имелись дополнительные единицы этих ресурсов, мы могли бы произвести еще продукт и получить дополнительную прибыль.

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

Теневая цена ограниченного ресурса -это дополнительная маржинальная прибыль, которая возникла бы, если бы имелась одна дополнительная единица ограниченного ресурса, либо потерянная маржинальная прибыль, которая возникла бы, если бы объем ограниченного ресурса был на единицу меньше.

Теневую цену смотрим из отчета по устойчивости:

Теперь объясню, что это значит на цифрах.

Еще одна дополнительная единица продукта, которую мы могли бы произвести, при наличии еще одной единицы ограниченного ресурса, представляет собой условную единицу. Это не одна единица E или R, а условная единица, в состав которой входят и Е, и R в той пропорции, в которой определен оптимальный план

Продукты E и R входят в оптимальный план в пропорции 375:750 или 1:2. Давайте рассмотрим минимальный набор оптимального плана, который включает 3 единицы: 1 единицу продукта Е и 2 единицы продукта R.

Вернемся к таблице с расчетом маржинальной прибыли:

Если мы приобретем все ресурсы, которые входят в состав по тем же ценам, что и прежде, то получим маржинальную прибыль 1 х 35 + 2 х 66 = 167$

Однако ресурс работы оборудования и рабочее время рабочих исчерпаны, а все остальные ресурсы есть. Значит, если мы можем арендовать дополнительную единицу оборудования и нанять еще рабочих, можно было бы доплатить. Сейчас оборудование стоит 7$ в час, труд – 10$ в час и мы и имеем маржинальную прибыль. Теневая цена показывает, что можно доплатить за 1 час работы оборудования — 10,75 (тогда он будет стоить 17,75), а за 1 час труда — 6,75 (будет стоить 16,75) — это максимальные суммы, при которых маржинальная прибыль будет равна нулю:

Разделы: Экономика

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

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

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

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

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

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

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

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

Читайте также:  Как скрыть братьев сестер вк

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

Подготовка к уроку: Занятия проводятся в группе по 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 =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%) нужно преобразовать в периодическую ежемесячную процентную ставку. Это две величины связаны между собой таким уравнением:

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

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

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

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

in= 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. Подготовленные документы сдаются на рассмотрение учителю.

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

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

Ссылка на основную публикацию
Размер области меньше минимума диспетчер виртуальных дисков
1. Для выполнения операций с разделами жесткого диска необходимо использовать учетную запись с правами администратора. 2. Если в процессе уменьшения...
Произошла ошибка при отрисовке pdf при печати
В формате PDF хранится множество самых разнообразных проектов, например, текстовые презентации, брошюры, открытки, книги. Время от времени каждый обладатель принтера...
Произошла ошибка проверки подлинности затребованный пакет безопасности
Некоторые пользователи, которые подключаются через удаленный доступ RDP в Windows 10/7, получают ошибку "Произошла ошибка проверки подлинности. Указанная функция не...
Размер слайда в гугл презентации
Приветствую, дорогие читатели блога. На связи Галиулин Руслан. Сегодня мы будем говорить о создании онлайн презентаций Гугл и я подготовил...
Adblock detector