No Image

Экономические задачи в экселе

СОДЕРЖАНИЕ
1 просмотров
22 января 2020

Возможности табличного процессора 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 в экономике не ограничивается функциями просмотра. Табличный процессор предлагает пользователю возможности и побогаче.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Чему равен результат побитового сдвига 110 2

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

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

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

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

Устанавливая рекомендуемое программное обеспечение вы соглашаетесь
с лицензионным соглашением Яндекс.Браузера и настольного ПО Яндекса .

Практическая работа № 14

Тема: ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MS EXCEL

Цель: изучение технологии экономических расчетов в табличном

Задание 1. Оценка рентабельности рекламной кампании фирмы.

Запустите редактор электронных таблиц MS Excel и создайте новую электронную книгу.

Создайте таблицу оценки рекламной кампании по образцу (рис.1). Введите исходные данные: Месяц, расходы на рекламу А(0) (р.), Сумма покрытия В(0) (р.), Рыночная процентная ставка ( j ) = 13,7%.

Выделите для рыночной процентной ставки, являющейся константой, отдельную ячейку – С3, и дайте этой ячейке имя «Ставка».

Рис. 1. Исходные данные для Задания 1

Краткая справка: присваивание имени ячейке или группе ячеек:

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

выполните команду Формулы/Определенные имена/Присвоить имя;

Помните, что по умолчанию имена являются абсолютными ссылками.

Произведите расчеты во всех столбцах таблицы.

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

Формулы для расчета:

A ( n ) = A (0) * (1 + j /12) (1- n ) ,

в ячейке С6 наберите формулу

= B6 * (1 + ставка/12) ^ (1- $A6)

Примечание: ячейка А6 в формуле имеет комбинированную адресацию: абсолютную адресацию по столбцу и относительную по строке, и записывается в виде $А6 .

При расчете расходов на рекламу нарастающим итогом надо учесть, что первый платеж равен значению текущей стоимости расходов на рекламу, значит в ячейку D 6 введем значение =С6, но в ячейке D 7 формула примет вид = D 6 + C 7. Далее формулу ячейки D 7 скопируйте в ячейки D 8: D 17.

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

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

Для расчета текущей стоимости покрытия скопируйте формулу из ячейки C 6 в ячейку F 6. В ячейке F 6 должна быть формула

= Е6 * (1 * ставка/12)^(1 — $А6).

Далее с помощью маркера автозаполнения скопируйте формулу в ячейки F 7: F 17.

Сумма покрытия нарастающим итогом рассчитывается аналогично расходам на рекламу нарастающим итогом, поэтому в ячейку G 6 поместим содержимое ячейки F 6 (= F 6), а в G 7 введем формулу = G 6 + F 7

Далее формулу из ячейки G 7 скопируем в ячейки G 8: G 17. В последних трех ячейках столбца будет представлено одно и то же значение, ведь результаты рекламной кампании за последние три месяца на сбыте продукции уже не сказывались.

Рис. 2. Рассчитанная таблица оценки рекламной кампании

Сравнив значения в столбцах В и G , уже можно сделать вывод о рентабельности рекламной кампании, однако расчет денежных потоков в течение года (колонка H ), вычисляемый как разница колонок G и D , показывает, в каком месяце была пройдена точка окупаемости инвестиций. В ячейке H 6 введите формулу + G 6 – D 6, и скопируйте её на всю колонку.

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

В ячейке Е19 произведите расчет количества месяцев, в которых сумма покрытия имеется (используйте формулу «Счет» ( Формулы/Другие функции/Статистические) , указав в качестве диапазона «Значение 1» интервал ячеек Е7:Е14). После расчета формула в ячейке Е19 будет иметь вид =СЧЕТ(Е7:Е14).

В ячейке Е20 произведите расчет количества месяцев, в которых сумма покрытия больше 100 000 р. (используйте функцию СЧЕТЕСЛИ, указав в качестве диапазона «Значение» интервал ячеек Е7:Е14, а в качестве условия >100 000). После расчета формула в ячейке Е20 будет иметь вид = СЧЕТЕСЛИ(Е7:Е14) (рис.3).

Постройте графики по результатам расчетов (рис.4):

«Сальдо дисконтированных денежных потоков нарастающим итогом» по результатам колонки H ;

«Реклама: расходы и доходы» по данным колонок В и G (диапазоны D 5: D 17 и G 5: G 17 выделяйте, удерживая нажатой клавишу [ Ctrl ]).

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

Комментировать
1 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
Adblock detector