Решение экономических задач в ms excel доклад
Обновлено: 05.07.2024
На сегодняшний день разработано большое количество специализированных программных продуктов для проведения экономических расчетов, однако сотрудники финансово-экономических служб чаще всего пользуются табличным редактором Excel. Причина популярности данного инструмента — обширный функционал Excel и постоянное его развитие практически в каждой новой версии табличного редактора.
В рамках одной статьи невозможно рассмотреть все достоинства Excel, которые экономисты могут применить в своей работе, поэтому остановимся на анализе лучших функций редактора, используемых для решения экономических задач.
Для удобства восприятия материала сгруппируем эти функции в три блока:
1. Функционал расчетных формул в Excel.
2. Функционал Excel для обработки табличных данных.
3. Инструменты Excel для моделирования и анализа экономических данных.
ФУНКЦИОНАЛ РАСЧЕТНЫХ ФОРМУЛ В EXCEL
Расчетные формулы являются изначальным и основополагающим функционалом табличного редактора Excel, поэтому рассмотрим их в первую очередь.
Пакет встроенных расчетных формул включает в себя десятки наименований, но самыми востребованными в работе экономистов являются следующие формулы: ЕСЛИ, СУММЕСЛИ и СУММЕСЛИМН, ВПР и ГПР, СУММПРОИЗВ, СЧЕТЕСЛИ.
Решение экономической задачи с помощью формулы ЕСЛИ
Формула ЕСЛИ — расчетная функция Excel, которую наиболее часто используют для решения несложных экономических расчетов. Она относится к группе логических формул и позволяет рассчитать необходимые данные по условиям, заданным пользователями.
С помощью формулы ЕСЛИ можно сравнить числовые или текстовые значения по прописанным в формуле условиям.
Запись расчетной формулы в заданной ячейке в общем виде выглядит так:
где логическое выражение — данные, которые нужно проверить/сравнить (числовые или текстовые значения в ячейках);
значение_если_истина — результат, который появится в расчетной ячейке, если значение будет верным;
значение_если_ложь — результат, который появится в расчетной ячейке при неверном значении.
Задача № 1. Предприятие реализует три номенклатурные группы продукции: лимонад, минеральная вода и пиво. С 01.09.2020 запланировано установить скидку в размере 15 % на пиво.
Чтобы сформировать новый прайс на продукцию, сохраняем ее перечень в виде таблицы Excel. В первом столбце таблицы отражена номенклатура всей продукции в алфавитном порядке, во втором — признак группы продукции.
Для решения задачи создаем в таблице третий столбец и прописываем в первой ячейке номенклатуры формулу: =ЕСЛИ(C4="пиво";15%;0).
Эту формулу продлеваем до конца перечня номенклатуры продукции. В итоге получаем сведения о продукции, на которую с сентября снизится цена (табл. 1).
В данном примере показано использование формулы ЕСЛИ для обработки текстовых значений в исходных данных.
Решение экономической задачи с помощью формулы СУММЕСЛИ
Формулы СУММЕСЛИ и СУММЕСЛИМН также используют для экономических расчетов, но они обладают более широкими возможностями для выборки и обработки данных. Можно задать не одно, а несколько условий отборов и диапазонов.
Задача № 2. На основе ведомости начисления заработной платы сотрудникам магазина нужно определить общую сумму зарплаты продавцов.
Чтобы решить эту задачу, сохраняем ведомость из учетной базы данных в виде таблицы Excel. В данном случае нам нужно не просто произвести выборку значений, но и суммировать их результат. Поэтому будем использовать более сложную разновидность формулы ЕСЛИ — СУММЕСЛИ.
Результат решения задачи — в табл. 2.
Решение экономической задачи с помощью формул ВПР и ГПР
Формулы ВПР и ГПР используют для решения более сложных экономических задач. Они популярны среди экономистов, так как существенно облегчают поиск необходимых значений в больших массивах данных. Разница между формулами:
- ВПР предназначена для поиска значений в вертикальных списках (по строкам) исходных данных;
- ГПР используют для поиска значений в горизонтальных списках (по столбцам) исходных данных.
Формулы прописывают в общем виде следующим образом:
=ВПР(искомое значение, которое требуется найти; таблица и диапазон ячеек для выборки данных; номер столбца, из которого будут подставлены данные; [интервал просмотра данных]);
=ГПР(искомое значение, которое требуется найти; таблица и диапазон ячеек для выборки данных; номер строки, из которой будут подставлены данные; [интервал просмотра данных]).
Указанные формулы имеют ценность при решении задач, связанных с консолидацией данных, которые разбросаны на разных листах одной книги Excel, находятся в различных рабочих книгах Excel, и размещении их в одном месте для создания экономических отчетов и подсчета итогов.
Задача № 3. У экономиста есть данные в виде таблицы Excel о реализации продукции за сентябрь в натуральном измерении (декалитрах) и данные о реализации продукции в сумме (рублях) в другой таблице Excel. Экономисту нужно предоставить руководству отчет о реализации продукции с тремя параметрами:
- продажи в натуральном измерении;
- продажи в суммовом измерении;
- средняя цена реализации единицы продукции в рублях.
Для решения этой задачи с помощью формулы ВПР нужно последовательно выполнить следующие действия.
Шаг 1. Добавляем к таблице с данными о продажах в натуральном измерении два новых столбца. Первый — для показателя продаж в рублях, второй — для показателя цены реализации единицы продукции.
Шаг 2. В первой ячейке столбца с данными о продажах в рублях прописываем расчетную формулу: =ВПР(B4:B13;Табл.4!B4:D13;3;ЛОЖЬ).
Пояснения к формуле:
В4:В13 — диапазон поиска значений по номенклатуре продукции в создаваемом отчете;
Табл.4!B4:D13 — диапазон ячеек, где будет производиться поиск, с наименованием таблицы, в которой будет организован поиск;
3 — номер столбца, по которому нужно выбрать данные;
ЛОЖЬ — значение критерия поиска, которое означает необходимость строгого соответствия отбора наименований номенклатуры таблицы с суммовыми данными наименованиям номенклатуры в таблице с натуральными показателями.
Шаг 3. Продлеваем формулу первой ячейки до конца списка номенклатуры в создаваемом нами отчете.
Шаг 4. В первой ячейке столбца с данными о цене реализации единицы продукции прописываем простую формулу деления значения ячейки столбца с суммой продаж на значение ячейки столбца с объемом продаж (=E4/D4).
Шаг 5. Продлим формулу с расчетом цены реализации до конца списка номенклатуры в создаваемом нами отчете.
В результате выполненных действий появился искомый отчет о продажах (табл. 3).
На небольшом количестве условных данных эффективность формулы ВПР выглядит не столь внушительно. Однако представьте, что такой отчет нужно сделать не из заранее сгруппированных данных по номенклатуре продукции, а на основе реестра ежедневных продаж с общим количеством записей в несколько тысяч.
Тогда эта формула обеспечит такую скорость и точность выборки нужных данных, которой трудно добиться другими функциями Excel.
Решение экономической задачи с помощью формулы СУММПРОИЗВ
Задача № 4. Есть реестр продаж различной номенклатуры продукции за сентябрь 2020 г. Нужно рассчитать из общего реестра данные о суммах реализации по основным номенклатурным группам продукции.
Выполнив указанные действия, получим искомое решение задачи в табл. 4.
Решение экономической задачи с помощью формулы СЧЕТЕСЛИ
Формула СЧЕТЕСЛИ используется не так широко, как предыдущие, но она выручает экономистов, если нужно минимизировать ошибки при работе с таблицами Excel. Эта формула удобна для проверки корректности вводимых данных и установке различного рода запретов, что особенно важно, если с данными работает несколько пользователей.
Задача № 5. Экономисту поручили провести корректировку справочника номенклатуры ТМЦ в учетной базе данных компании. Справочник долгое время не проверяли, данные в него вносили порядка 10 человек, поэтому появилось много некорректных и дублирующих наименований.
Экономист может решить эту проблему с помощью формулы СЧЕТЕСЛИ. Нужно выполнить следующие действия:
Такими экономическими задачами могут быть:
- расчет оптимального объема выпуска продукции при ограниченности сырья;
- минимизация транспортных расходов на доставку продукции покупателям;
- решение по оптимизации фонда оплаты труда.
Задача № 9. Туристической компании необходимо организовать доставку 45 туристов в четыре гостиницы города с трех пунктов прибытия при минимально возможной сумме затрат. Для решения задачи составляем таблицу с исходными данными:
1. Количество прибывающих с каждого пункта — железнодорожный вокзал, аэропорт и автовокзал (ячейки Н6:Н8).
2. Количество забронированных для туристов мест в каждой из четырех гостиниц (ячейки D9:G9).
3. Стоимость доставки одного туриста с каждого пункта прибытия до каждой гостиницы размещения (диапазон ячеек D6:G8).
Исходные данные, размещенные таким образом, показаны в табл. 8.1.
Далее приступаем к подготовке поиска решения.
1. Создаем внизу исходной таблицы такую же таблицу для расчета оптимального количества доставки туристов при условии минимизации затрат на доставку с диапазоном ячеек D15:G17.
2. Выбираем на листе ячейку для расчета искомой функции минимизации затрат (J4) и прописываем в ячейке расчетную формулу: =СУММПРОИЗВ(D6:G8;D15:G17).
- оптимизировать целевую функцию — ячейка J4;
- цель оптимизации — до минимума;
- изменения ячейки переменных — диапазон ячеек второй таблицы D15:G17;
- ограничения поиска решения:
– в диапазоне ячеек второй таблицы D15:G17 должны быть только целые значения (D15:G17=целое);
– значения диапазона ячеек второй таблицы D15:G17 должны быть только положительными (D15:G17>=0);
– количество мест для туристов в каждой гостинице таблицы для поиска решения должно быть равно количеству мест в исходной таблице (D18:G18 = D9:G9);
– количество туристов, прибывающих с каждого пункта, в таблице для поиска решения должно быть равно количеству туристов в исходной таблице (Н15:Н17 = Н6:Н8).
Далее даем команду найти решение, и надстройка рассчитывает нам результат оптимальной доставки туристов (табл. 8.2).
При такой схеме доставки целевое значение общей суммы расходов действительно минимальное и составляет 1750 руб.
Пакет расширенного анализа данных
Пакет расширенного анализа данных применяют, если нужно исследовать различного рода статистические анализы, ряды данных, спрогнозировать тренды и т. д.
Пакет является надстройкой к Excel, устанавливается в основное меню аналогично функции поиска решений (Файл → Параметры → Надстройки → Пакет анализа). Вызвать его можно командой Данные → Анализ данных через диалоговое окно, в котором отражены все заложенные в надстройке виды анализа (рис. 3).
Специализированные надстройки для финансово-экономической работы
В последние годы значительно расширился перечень специализированных надстроек к табличному редактору Excel, которые могут использовать в своей работе экономисты. Практически все они бесплатные, легко устанавливаются самим пользователем.
Не будем останавливаться на таких надстройках, как Power Query, Power Pivot, Power Quick, так как они в большей степени используются в целях бизнес-аналитики, чем для решения экономических задач.
- финансовые функции (ликвидность, платежеспособность, финансовая устойчивость, рентабельность, оборачиваемость, безубыточность продаж, отсрочка платежа, налоги и т. д.);
- работа с формулами;
- работа с текстом;
- работа с книгами и листами Excel;
- работа с ячейками Excel;
- поиск дубликатов и сравнение диапазонов данных;
- вставка дат и примечаний к диапазонам данных;
- загрузка курсов валют;
- создание выпадающих списков.
Надстройка PowerFin будет полезна прежде всего экономистам, которые работают с кредитами и инвестициями. Она без проблем устанавливается в меню надстроек Excel и имеет следующие функции:
Оптимальное управленческое решение в типовой хозяйственной ситуации. Задача об оптимальном использовании ограниченных ресурсов. Моделирование и специальная задача линейного программирования. Задача о назначениях. Экономико-математическая модель задач.
| Рубрика | Программирование, компьютеры и кибернетика |
| Вид | контрольная работа |
| Язык | русский |
| Дата добавления | 16.04.2013 |
| Размер файла | 2,3 M |
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
1. Предложить оптимальное управленческое решение в следующей типовой хозяйственной ситуации
1.1 Задача об оптимальном использовании ограниченных ресурсов
1.2 Экономико-математическая модель задачи
1.3 Описание компьютерной информационной технологии получения оптимального решения
1.5 Рекомендации лицу, ответственному за принятие решений
2. Провести моделирование и решить специальную задачу линейного программирования
2.1 Задача о назначениях
2.2 Экономико-математическая модель задачи
2.4 Рекомендации лицу, ответственному за принятие решений
3. Дополнительные варианты задач для выполнения лабораторной работы
3.1.1 Экономико-математическая модель задачи
3.1.3 Рекомендации лицу, ответственному за принятие решений
3.2.1 Экономико-математическая модель задачи
3.2.3 Рекомендации лицу, ответственному за принятие решений
1. Предложить оптимальное управленческое решение в следующей типовой хозяйственной ситуации
1.1 Задача об оптимальном использовании ограниченных ресурсов
Менеджер по ценным бумагам намерен разместить 100 000 ф.ст. капитала таким образом, чтобы получать максимальные годовые проценты с дохода. Его выбор ограничен четырьмя возможными объектами инвестиций - А, В, С и О. Объект А позволяет получать 6% годовых, объект В - 8%, объект С - 10%, а объект О - 9% годовых. Для всех четырех объектов степень риска и условия размещения капитала различны. Чтобы не подвергать риску имеющийся капитал, менеджер принял решение, что не менее половины инвестиций необходимо вложить в объекты А и В. Чтобы обеспечить ликвидность, не менее 25% общей суммы капитала нужно поместить в объект О. Учитывая возможные изменения в политике правительства, предусматривается, что в объект С следует вкладывать не более 20% инвестиций, тогда как особенности налоговой политики требуют, чтобы в объект А было вложено не менее 30% капитала.
Как распорядиться свободными денежными средствами?
1.2 Экономико-математическая модель задачи
Пусть х1 - сумма инвестиций для вклада в объект А, х2 - сумма инвестиций для вклада в объект В, х3 - сумма инвестиций для вклада в объект С, х4 - сумма инвестиций для вклада в объект О.
Ограничения задачи имеют вид:
x1+x2 ? 50 000 - ограничение по инвестициям в объекты А и В;
x1 ? 30 000 - ограничение по инвестициям в объект А;
x3 ? 20 000 - ограничение по инвестициям в объект С;
x4 ? 25 000 - ограничение по инвестициям в объект О;
x1+x2+x3+x4 ? 100 000 - ограничение по общей сумме инвестиций.
1.3 Описание компьютерной информационной технологии получения оптимального решения
Для решения данной экономической задачи была выбрана среда табличного процессора MS Excel.
Microsoft Office Excel является средством для создания электронных таблиц, которые обладают возможностями для проведения простых расчетов, как с использованием арифметических действий, так и с помощью встроенных функций; для построения разных типов диаграмм; для оформления полученных таблиц и т.д.
задача табличный процессор excel
1) фрагмент исходного рабочего листа Excel
2) диалоговое окно Поиск решения
Параметры Поиска решения
3) фрагмент рабочего листа Excel, содержащий результаты решения
Фрагменты Отчета по устойчивости и Отчета по результатам
1.5 Рекомендации лицу, ответственному за принятие решений
При вложении инвестиций в размере 100 000 ф.ст. капитала в возможные объекты инвестиций А, В, С и О следующим образом: в объект А - 30 000 ф.ст., в объект В - 20 000 ф.ст., в объект С - 20 000 ф.ст., в объект О - 30 000 ф.ст., можно получить максимальные годовые проценты с дохода в сумме 8 100 ф.ст.
2. Провести моделирование и решить специальную задачу линейного программирования
2.1 Задача о назначениях
Управляющему банком были представлены 4 проекта, претендующие на получение кредита в банке. Ресурс банка в каждый период, потребности проектов и прибыль по ним приведены в таблице (тыс. долл.).
| Проект | Потребность проекта в объемах кредитов | Прибыль | |||
| Период 1 | Период 2 | Период 3 | Период 4 | ||
| А | 8 | 8 | 10 | 10 | 21 |
| Б | 7 | 9 | 9 | 11 | 18 |
| В | 5 | 7 | 9 | 11 | 16 |
| Г | 9 | 8 | 7 | 6 | 17,5 |
| Ресурс банка | 22 | 25 | 38 | 30 | |
При выборе проектов следует принять во внимание потребность проектов в объемах кредитов и ресурс банка для соответствующих периодов.
Какие проекты следует финансировать, если цель состоит в том, чтобы максимизировать прибыль?
Введем по числу проектов переменную Xi, где i= А, Б, В, Г. переменная Xi=1, ели проект с номером i будет финансироваться и Xi=0- не будет финансироваться.
 |
Создаем форму для ввода условий задачи в Microsoft Excel:
Введем исходные данные:
Введем зависимость для целевой функции:
Введем зависимость для ограничений:
Вывод: В результате решение рассматриваемой задачи получено оптимальное решение, указывающее, что целесообразно финансировать проекты А и В.
Целевая функция (ожидаемая прибыль) = 54,5 тыс.долл
Лабораторная работа № 2 Вариант № 6
Задание: В распоряжении некоторой компании имеется 6 торговых точек и 6 продавцов. Из прошлого опыта известно, что эффективность работы продавцов в различных торговых точках неодинакова. Коммерческий директор компании произвел оценку деятельности каждого продавца в каждой торговой точке. Результаты этой оценки представлены в таблице.
| Продавец | Объемы продаж по торговым точкам, USD/тыс.шт. | |||||
| I | II | III | IV | V | VI | |
| A | 66 | 72 | 75 | - | 75 | 69 |
| B | 56 | 60 | 58 | 63 | 61 | 59 |
| C | 35 | 38 | 40 | 45 | 25 | 27 |
| D | 40 | 42 | 47 | 45 | 53 | 36 |
| E | 62 | 70 | 68 | 67 | 69 | 70 |
| F | 65 | 63 | 69 | 70 | 72 | 68 |
Как коммерческий директор должен осуществить назначение продавцов по торговым точкам, чтобы достичь максимального объема продаж?
Xij – факт назначения или не назначения i-го продавца на j-ый объем продаж по торговой точке
Xij =1, если i-ый продавец назначен на j-ый объем продаж по торговой точке, и равен 0, если i-ый продавец не назначен на j-ый объем продаж по торговой точке
825
825
0,35
Анализируя полученные сводные данные можно сделать вывод о том, что наибольший риск увеличения затрат и уменьшение прибыли получили при увеличении уровня условно-постоянных затрат. Повышение цены привело к снижению точки безубыточного ведения хозяйства, что, безусловно, выгодно, но есть риск уменьшения объема продаж при слишком завышенной цене.
Заключение
В этой работе мне удалось создать информационную модель решения простой экономической задачи, которая показала использование такой информационной технологии как электронная таблица.
В нашей стране сейчас много внимания уделяется развитию бизнеса и предпринимательства. И, кто знает, может и я свяжу свою жизнь с экономикой, стану предпринимателем, бухгалтером или менеджером. Овладевая знаниями в школе, мы уже сейчас должны понимать суть выполняемой работы на той или иной должности и меру ответственности перед людьми и предприятием. Я убедилась при выполнении этой работы, насколько необходимо успешному бизнесмену знать возможности информационных технологий и уметь применять их в своей деятельности.
Баврин И.И., Фрибус Е.А. Старинные задачи: Кн. Для учащихся.- М: Просвещение, 1994.-128с.
Информатика. 7-8 класс /Под ред. Н.В. Макаровой. — СПб: Питер Ком, 1999. — 368 с. (Раздел 6. Прикладная среда — табличный процессор, с. 238303.)
Кушниренко А.Г., Лебедев Г.В., Сворень Р.А. Основы информатики и вычислительной техники: Учеб, для среди, учеб, заведений. — М.: Просвещение, 1993.
Леонтьев В.П. Новейшая Энциклопедия персонального компьютера 2003.- М.: ОЛМА - ПРЕСС, 2003. - 920с.
Резник С.Д., Бобров В.А. Управление семейной экономикой.- Пенза: Экономика, 2003 .-440с.
Семакин И., Залогова Л., Русаков С., Шестакова Л. Информатика: уч. по базовому курсу. — М.: Лаборатория Базовых Знаний, 1998. (Глава 8. Табличные вычисления на компьютере, с. 163-176.)
Угринович Н. Информатика и информационные технологии. Учебное пособие для общеобразовательных учреждений. — М.: БИНОМ, 2001. — 464 с. (Глава 12. Технология обработки числовых данных в электронных таблицах, с. 311-328.)
Старт в науке
Читайте также: