Задача 2. Использование Excel для работы с данными

Цель задания: научить использовать расширенные возможности Excel при обработке статистических рядов наблюдений и разработки математических моделей в управлении проблемными ситуациями.

Этап 1. Работа с финансовыми функциями в Excel.

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

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

Решение:

1. Запишем условия в определенном порядке, как это показано на рисунке. Вводим текст в столбец А и исходные значения в ячейки от В1 до В5.

2. В ячейке В6 вводим формулу для расчета числа периодов погашения кредита: =В4*В5.

3. В ячейке В7 вводим формулу для расчета ставки: =В3/В5.

4. Используем стандартную функцию ПЛТ, обязательными аргументами которой являются величины: кпер – общее число периодов платежей, ставка – процентная ставка по ссуде, пс – общая сумма кредита.

Для этого в пункте Формулы выберем кнопку Вставить функцию. В группе финансовых функций выберем функцию ПЛТ и заполним ее аргументы так, ка показано на рисунке.

Изменяйте условия задачи в столбце B для значений A2, A3, A4, A5, что позволит подобрать наиболее выгодные условия для погашения кредита.

Задача 3. Инструментальные средства MS Excel в задачах оптимизации

Цель задания: освоить правила работы с надстройкой Excel – Поиск решения

Постановка задачи:

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

Исходные данные оптимизационной задачи сведены в таблице.

Таблица с исходными данными

Создание модели в Excel

Открыть табличный процессор Excel.

Подготовить начальную таблицу для размещения описаний задачи, исходных данных, ограничений, коэффициентов целевой функции, место для проведения вычислений и сохранения результатов. Начальная таблица, как она будет выглядеть в Excel, представлена на рисунке ниже. Столбец D введен для занесения результатов в ячейки D5:D10. В этих ячейках будут отображаться результаты вычислений, т.е. сколько на самом деле будет задействовано единиц каждого вида ресурса для выпуска всей номенклатуры продукции. Добавить в таблицу новые обозначения, которые понадобятся для ввода начальных значений и вывода результатов. Для этой цели:

- в строке 12 (ячейки E12:G12), ввести коэффициенты для целевой функции ее название - «Прибыль от реализации единицы продукции»;

-  в строке 13 создать заголовок – «Значения X_j при решении задачи», ячейки E13:G13 понадобятся для ввода формул;

- ячейку E14 можно выделить, в которой будет формироваться результат, поэтому в строке 14 сделана запись – «Конечная прибыль от реализации продукции», это и есть значение целевой функции.

Рис. 2. Начальная таблица в Excel

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

Таблица. Перечень формул для установки в ячейках таблицы Excel

2. Работа с надстройкой Excel – Поиск решения

- Подключение: Кнопка «Office» в левом верхнем углу окна программы – Параметры Excel – Надстройки – Перейти, и поставить отметку против строки Поиск решения (см. рис.).

Рис. Подключение инструмента для решения задач оптимизации

- Ввести начальные значения X_j в ячейки E13:G13. Например, по двадцать единиц каждого вида изделия.

- Выделить курсором ячейку E14с целевой функцией.

- Выбрать команду в менюДанные - Поиск решения.

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

Рис. Всплывающее диалоговое окно с введенными данными

- Установить диапазон ячеек в строке всплывающего окна: Изменяя ячейки, в которых будет отображаться результат с количеством номенклатуры X_j изделий. В рассматриваемом примере, это будут ячейки E13:G13, которые должны быть фиксированными (перед координатами ячеек ставится знак $).

- Отметить селекторную кнопку: Равной максимальному значению, т.к. определяется максимальное использование ресурсов.

- В окно с наименованием Ограничения последовательно ввести все ограничения для уравнений модели. В данном примере ячейки C5:C10 содержат количество деталей на складе, которые потребуются для выпуска всей номенклатуры продукции, а в ячейки D5:D10 были внесены формулы модели по каждому виду комплектующей, следовательно, суммарное количество используемых деталей не должно превышать величину, указанную в правой части уравнения. Для ввода ограничений, необходимо нажать на кнопку . После выполненного действия будет открыто диалоговое окно с наименованием Добавление ограничений, которое показано на рисцнук ниже. В окне видно, что вычисляемое значение в ячейке D5 должно быть меньше или равно установленной величины в ячейке C5.

Рис. Диалоговое окно для ввода ограничений

- Если требуется ввести еще ограничения, то нажать на кнопку , в противном случае нажать на кнопку .

- Ввести ограничения на выпуск номенклатуры продукции в ячейки E13:G13 (в примере всего три вида продукции). Так, в качестве примера, на рисунке ниже показано диалоговое окно для добавления ограничений, в котором указано, что вычисляемое значение в ячейке G13 должно быть более 1 единицы (это условие записано в исходной таблице для изделия – Компьютеры).

Рис. Диалоговое окно с ограничением на выпускаемую продукцию

- Ввести ограничения на форму представление результатов. В данной постановке задачи подразумевается, что количество изделий не может быть дробной величиной, а должны отображаться только целыми числами, следовательно, при выполнении расчетов, это обстоятельство необходимо учитывать. На рисунке ниже показано диалоговое окно для добавления ограничений, в котором для ячейки E13 (в ней отображается количество единиц изделия), установлено условие ‘цел’, что означает целочисленное решение. В раскрывающемся списке выбирается необходимое условие.

Рис. Установка условия целочисленного решения в диалоговом окне

- Провести вычисления, для чего в диалоговом окне Поиск решения, нажать на кнопку . В том случае, если все данные введены правильно, а в вычисляемых ячейках существуют формулы, описанные в данной методике, то появится диалоговое окно с наименованием: Результаты поиска решения, которое представлено на рисунке.

Рис. Диалоговое окно с сообщением о завершении поиска оптимального решения по поставленной задаче

Программа формирует три типа отчетов: Результаты, Устойчивость и Пределы. Если отметить любой из них или все вместе, а затем вызвать, то можно провести анализ исходных данных и конечных результатов, об этом будет сказано ниже. В том случае, если в окне Результаты поиска решения появится сообщение: «Ошибка в модели. Проверьте правильность значений в ячейках и ограничениях», то отчеты получить невозможно, а следует открыть окно Поиск решения (см. рис.), и проверить правильность установки знаков ограничений, наименования ячеек, в которых должна быть вычислена целевая функция и установлены начальные значения изменяемых ячеек. На рисунке ниже показан лист Excel, который содержит исходные данные и результаты решения оптимизационной задачи методом линейного программирования по заданным начальным значениям и тем условиям, которые были установлены, в соответствии с постановкой задачи.

Рис. Результаты решения оптимизационной задачи методом линейного программирования

Анализ результатов

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

- Принятие решения о производственной программе может быть направлено на то, что следует изменить некоторые условия, например, предприятие производитель имеет обязательства перед потребителем о поставке в текущем периоде 15 телевизоров. Следовательно, условие в ячейке E13 должно быть изменено (вместо 1 следует установить 15).

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

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

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

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

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

Рис. Внешний вид отчета, который создается системой

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

Приложение 1. Оформление титульного листа контрольной работы

МИНОБРНАУКИ РОССИИ

Федеральное государственное бюджетное образовательное

 учреждение высшего образования

«Ижевский государственный технический университет имени М.Т. Калашникова»

(ФГБОУ ВО «ИжГТУ имени М.Т. Калашникова»)

Институт непрерывного профессионального образования

Кафедра «Бухгалтерский учет и анализ хозяйственной деятельности»

Работа защищена с оценкой

« __________________»

Дата «__»______________2018 г.

Контрольная работа

по дисциплине «Экономическая информатика»

Вариант № __

Выполнил:

Студент гр. Б01-522-1 з                                                А.А. Аверина 

                                                                                                                                                                     Фамилия И. И

Проверил:                                                                     

к.ф.-м.н., доцент

кафедры «Бухг.учет и АХД»                                         Е.В. Касаткина

Рецензия:

Степень достижения поставленной цели работы________________________________________

Полнота разработки темы___________________________________________________________

Уровень самостоятельности работы обучающегося______________________________________

Недостатки работы_________________________________________________________________

__________________________________________________________________________________