Использование средств Microsoft Excel для решения экономических задач (стр. 5 )

При выполнении индивидуального задания Вам необходимо:

1. Внимательно прочитать условие задачи.

2. Открыть книгу Задания. xls.

3. Создать новый лист.

4. Появившийся Лист7 переименуйте в «Отчетные ведомости».

Сохранить полученные результаты.

Рис.4.8.8. Ввод имени в ячейку из диалогового окна Вставка имени

Задание:

Составить отчетную ведомость реализации товаров п магазинами с месяца А по месяц В, приведенную на рис. 4.8.7.

Таблица 1

Вариант	А	В	п
1	май	декабрь	3
2	июнь	январь	4
3	июль	октябрь	5
4	август	январь	6
5	сентябрь	декабрь	7
6	октябрь	март	8
7	ноябрь	март	9
8	декабрь	июль	10
9	январь	мюль	4
10	февраль	август	5

В качестве стоимостей товаров введите произвольные трехзначные числа, а в качестве объемов их реализации — произвольные двузначные числа.

Порядок отчета лабораторной работы:

При отчете лабораторной работы необходимо:

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

2)  Продемонстрировать выполненное индивидуальное задание, прокомментировать порядок его выполнения и объяснить полученные результаты;

3)  Ответить на контрольные вопросы.

Контрольные вопросы:

1)  Как создать список?

2)  Что находит функция срзнач?

3)  Что возвращает функция ранг?

4)  Как присвоить имя ячейкам?

5)  Что можно вычислить с помощью функции частота?

6)  Как установить команду Анализ данных?

7)  Что возвращает функция дата?

8)  Как строятся диаграммы в Microsoft Excel?

9)  Как осуществляются операции копирования и переноса в Microsoft Excel?

Список литературы:

Гарнаев MS Excel и VBA в экономике и финансах.-СПб.: БХВ - Санкт-Петербург, 199с., ил.

2.  Информатика. Серия «Учебники, учебные пособия». И57 // Под ред. . – Ростов н/Д: Феникс, 20с.

4.9. Лабораторная работа №9

Тема: Планирование рекламной кампании

Время выполнения - 4 часа

Цель работы: научиться использовать средства табличного процессора Microsoft Excel для решения финансовой задачи.

Последовательность выполнения:

1. Решить все описанные упражнения самостоятельно, руководствуясь методическими указаниями;

2. Выполнить задание;

3. Проверить свои знания по контрольным вопросам и сдать лабораторную работу.

Основные сведения по теме:

4.9.1. Планирование рекламной компании

В книге ФИНАНСОВЫЙ АНАЛИЗ создайте новый лист (меню ВСТАВКА\ЛИСТ). Все упражнения в данной лабораторной работе выполняйте на новом листе, которому дайте имя «Рекламная компания».

Прежде всего опишем функции макс (МAХ) и ПОИСКПОЗ (match), которые используются в дальнейшем при рассмотрении примера составления оптимального плана рекламной кампании.

Функция макс возвращает максимальный элемент массива. Функция ПОИСКПОЗ возвращает относительную позицию элемента массива, который соответствует указанному значению. Функция ПОИСКПОЗ используется вместо функций типа просмотр, если нужна позиция элемента, а не сам элемент.

Синтаксис: ПОИСКПОЗ(искомое значение; просматриваемый_массив; тип сопоставления)

Аргументы:

искомое_значение - значение, для которого ищется соответствие в аргументе просматриваемый_массив. Например, когда вы ищете номер телефона в телефонной книге, вы используете фамилию человека как искомое_значение. Оно может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую число, текст или логическое значение. Просматриваемый_массив - непрерывный интервал ячеек, который возможно содержит искомые значения. Просматриваемый_массив может быть массивом или ссылкой на массив. Тип_сопоставления - число: -1, 0 или 1. Тип_сопоставления указывает, как Excel сопоставляет искомое значение со значениями в аргументе просматриваемый_массив.

Рассмотрим подробнее возможные варианты:

• Если тип_сопоставления равен 1, то функция поискпоз находит наибольшее значение, которое равно или меньше, чем искомое_значение

(просматриваемый_массив ДОЛЖЕН БЫТЬ упорядочен ПО Возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА).

• Если тип_сопоставления равен 0, то функция поискпоз находит первое значение, которое в точности равно аргументу искомое_значение (просматриваемый_массив может быть расположен в любом порядке).

• Если тип_сопоставления равен -1, то функция поискпоз находит наименьшее значение, которое равно или больше, чем искомое_значение (просматриваемыи_массив должен быть упорядочен по убыванию).

• Если тип_сопоставления опущен, то предполагается, что он равен 1.

Теперь рассмотрим следующий пример. Фирма еженедельно анализирует, как обстоят дела со сбытом одного из видов своей продукции и дает оценку: отличную ("о" — состояние 1), хорошую ("х" — состояние 2) или удовлетворительную ("у" — состояние 3). Необходимо принять решение о целесообразности рекламирования этой продукции с целью расширения ее сбыта.

Приведенные на рис. 4.9.1. в диапазонах B3:D5 и B6:D8 матрицы P1 и P2 определяют переходные вероятности без рекламы и при ее наличии в течение любой недели.

Так, P122=0,5 и P123=0,5 означает, что если в предыдущую неделю сбыт был хорошим, то и без рекламы на текущей неделе с равной вероятностью он останется хорошим или станет удовлетворительным. Соответствующие доходы заданы матрицами R1 и R2 в диапазонах E3:G5 и E6:G8. Отметим, что элементы матрицы R2 учитывают затраты на рекламу. Необходимо спланировать оптимальную рекламную кампанию на последующие три недели.

Для общности предположим, что план составляется на N недель, а число состояний для каждого этапа равно т.

Рис.4.9.1. Планирование рекламной кампании

Пусть fn(i) — оптимальный ожидаемый доход за этапы п, п+1, ..., N при условии, что система находится в состоянии i в начале n-й недели.

Тогда:

,

гдеfN+1(j) = 0 при всех j. Пусть

тогда

.

В ячейку I5 введена формула:

=СУММПРОИЗВ(В5:D5;Е5:G5),

вычисляющая v11, которая протаскивается на диапазон I6:I10 для вычисления v21,...,v32 .

В ячейки диапазона I12:I17 последовательно введены формулы:

=I5

=I8

=I6

=I9

=I7

=I10,

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

В ячейки диапазона В13:В15 введены формулы:

=МАКС(I12:I13) =МАКС(I14:I15)

=МАКС(I16:I17),

определяющие максимальную ожидаемую прибыль на третьей неделе, если на предыдущей неделе система находилась в первом, втором или третьем состоянии, соответственно. В ячейках диапазона С13:С15 по формулам:

=ПОИСКПОЗ(В13;I12:I13;0)

=ПОИСКПОЗ(В14;I14:I15;0)

=ПОИСКПОЗ(В15;I16:I17;0)

определяется оптимальный вариант действий. Если 1, то деньги на рекламу не тратить, а если 2 — то тратить.

Перейдем ко второй неделе рекламной кампании. В ячейку J5 введена формула:

=I5+МУМНОЖ(В5:D5;$В$13:$В$15),

вычисляющая

которая протаскивается на диапазон J6: J10 для вычисления

В ячейки диапазона J12: J17 введены последовательно формулы:

=J5

=J8

=J6

=J9

=J7

=J10,

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

В ячейки диапазона D13:D15 введены формулы:

=МАКС(J12:J13) =МАКС(J14:J15)

=МАКС(J16 :J17),

определяющие максимальную ожидаемую прибыль на второй неделе, если на предыдущей неделе система находилась в первом, втором или третьем состоянии, соответственно. В ячейках диапазона Е13:Е15 по формулам:

=ПОИСКПОЗ(D13;J12:J13;0)

=ПОИСКПОЗ(D14;J14:J15;0)

=ПОИСКПОЗ(D15;J16:J17;0)

определяется оптимальный вариант действий. Аналогично проводятся расчеты для первой недели.

Из рис.4.9.1. видно, что на первой и второй неделях необходимо использовать рекламу, не считаясь с состоянием системы, однако, на третьей неделе рекламу следует использовать только тогда, когда система находится во втором или третьем состояниях. Суммарный ожидаемый доход фирмы составит 10736 при отличной оценке, 7923 — при хорошей и 4222 — при удовлетворительной оценке.

4.9.2. Задание

Задание выполняйте в книге Задания. xls. Скопируйте из книги ФИНАНСОВЫЙ АНАЛИЗ лист «Рекламная компания» и на нем путем корректировки рассчитанного примера выполните задание.

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

Порядок отчета лабораторной работы:

При отчете лабораторной работы необходимо:

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

2)  Продемонстрировать выполненное задание, прокомментировать порядок его выполнения и объяснить полученные результаты;

3)  Ответить на контрольные вопросы.

Контрольные вопросы:

1)  Что возвращает функция макс?

2)  Что возвращает функция ПОИСКПОЗ?

3)  Что такое Просматриваемый_массив?

4)  Опишите параметры функции СУММПРОИЗВ.

5)  Опишите параметры функции МУМНОЖ.

Список литературы:

1.Гарнаев MS Excel и VBA в экономике и финансах.-СПб.: БХВ - Санкт-Петербург, 199с., ил.

2.Информатика. Серия «Учебники, учебные пособия». И57 // Под ред. . – Ростов н/Д: Феникс, 20с.

3.А. В. Могилев, Пак, Хеннер. Информатика. М: Изд. центр «Академия», 2000г.- 816 с.

4.10. Лабораторная работа №10

Тема: Решение линейной оптимизационной задачи

Время выполнения - 4 часа.

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

Последовательность выполнения:

1.Решить самостоятельно оптимизационную задачу, руководствуясь методическими указаниями;

2. Выполнить задание;

3. Проверить свои знания по контрольным вопросам и сдать лабораторную работу.

Основные сведения по теме:

4.10.1. Задача линейного программирования

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

В математическую модель входят три составляющие:

1.  ЦФ – целевая функция или критерий оптимизации, показывает в каком смысле решение должно быть оптимальным. Целевая функция может стремиться к max или min. При ограничении на сырье целевая функция будет стремиться к min, то есть необходимо определить такой выпуск продукции, при котором расход сырья был бы min. Если назначить ограничение на прибыль, то целевая функции будет стремится к max, то есть необходимо определить такой выпуск продукции, при котором прибыль была бы максимальной.

2.  ОГР – ограничения, устанавливающие зависимости между переменными.

3.  ГРУ – граничные условия, показывающие, в каких пределах могут быть искомые переменные.

Решение задачи, удовлетворяющее всем граничным условиям и всем ограничениям, называется - допустимым. Если математическая модель задачи составлена правильно, то задача имеет целый ряд допустимых решений. Критерий выбирается человеком, который принимает решение. С помощью критерия можно оценивать качества желательные (прибыль, производительность) и не желательные (затраты, расход материалов). Тогда в первом случае ЦФ – max, а во втором случае ЦФ – min.

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

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

Наиболее распространенным методом решения задач линейного программирования является так называемый симплекс-метод. В простейшем случае, когда число переменных равно двум, удобен простой и наглядный графический метод. На ЭВМ задачи линейного программирования решаются в системах поддержки принятия решений (СППР). СППР, использующие экономико-математические методы, реализованы в специальных программах (Mathcad, Excel).

4.10.2  Решение задачи планирования производства

Рассмотрим следующую задачу планирования производства.

Небольшая фабрика выпускает два типа красок: для внутренних (I) и наружных (Е) работ. Продукция обоих видов поступает в оптовую продажу. Для производства красок используются два исходных продукта А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн, соответственно. Расходы продуктов А и В на 1 т соответствующих красок приведены в табл. 4.10.1. Изучение рынка сбыта показало, что суточный спрос на краску I никогда не превышает спроса на краску Е более чем на 1 т. Кроме того, установлено, что спрос на краску I никогда не превышает 2 т в сутки. Оптовые цены одной тонны красок равны: 3000 руб. для краски Е и 2000 руб. для краски I. Какое количество краски каждого вида должна производить фабрика, чтобы доход от реализации продукции был максимальным?

Таблица 4.10.1. Исходные данные задачи

Исходный продукт	Расход исходных продуктов на тонну краски, т	Максимально возможный запас, т
краска Е	Краска I
А В	1 2	2 1	6 8

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

1. Для определения, каких величин строится модель (т. е. каковы переменные модели)?

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

3. Каким ограничениям должны удовлетворять неизвестные?

Задача планирования производства в общем виде записывается следующим образом:

;

;

dj <= Xj <= Dj;

i=1,m; j=1,n;

где F – целевая функция; Cj - прибыль получаемая от реализации единицы продукции j-го типа, Хj – количество выпускаемой продукции j – го типа, - норма расхода i- ресурса для выпуска единицы продукции j-го типа, -количество располагаемого ресурса i – го вида, Dj – максимально возможный выпуск j - вида продукции, dj – минимально возможный выпуск продукции j - вида.

В нашем случае фабрике необходимо спланировать объем производства красок так, чтобы максимизировать прибыль. Поэтому переменными являются:

XI— суточный объем производства краски I и XE — суточный объем производства краски Е. Суммарная суточная прибыль от производства XI краски I и XE краски Е равна

Z = 3000*XE + 2000*XI .

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

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

XE, XI >=0

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

XE +2 XI <=6,

2 XE + XI <=8.

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

XI - XE <=1,

XI <=2.

Таким образом, математическая модель данной задачи имеет следующий вид:

Максимизировать

Z=3000* XE +2000* XI

При следующих ограничениях:

XE +2* XI <=6,

2* XE + XI <=8,

XI - XE <=1,

XI <=2,

XI, XE >=0

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

На листе книги создадим таблицу Исходные данные и отведем диапазон ячеек под решение.(рисунок 4.10.1).

В ячейку D13 введем функцию цели =E4*C11+E5*D11

В ячейки D16: D19 соответственно:

=C11+C4*D11

=B5*C11+D11

=D11-C11

=D11

В ячейки С11, D11 введем начальные значения, т. е. нулевые значения.

После этого выберем команду Сервис, Поиск решения (Tools, Solver) и заполним открывшееся диалоговое окно Поиск решения (Solver), как показано на рисунке 4.10.2.

Рисунок 4.10.1 - Диапазоны, отведенные под исходные данные

Рис. 4.10.2 - Диалоговое окно Поиск решениязадачи о планировании производства красок

После нажатия кнопки Выполнить (Solve) открывается окно Результаты поиска решения (Solver Results), которое сообщает, что решение найдено (рисунок 4.10.3).

Результаты расчета нашей задачи (оптимальный план производства и соответствующая ему прибыль) представлены на рисунке 1.1. Как видно из рисунка, оптимальным является производство 3,33 т краски Е и 1,33 т. краски I в сутки. Этот объем производства принесет фабрике 12666,66 тыс. руб. прибыли.

Рисунок 4.10.3 - Диалоговое окно Результаты поиска решения

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

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

В поле Изменяя ячейки указываются ячейки, которые должны изменяться в процессе поиска решения задачи, т. е. ячейки отведенные под переменные задачи. В нашем случае в поле Изменяя ячейки введем диапазон C11:D11.

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

Рисунок 4.10.4 - Диалоговое окно Добавление ограничений

В поле Ссылка на ячейку введите левую часть ограничения D16, а в поле Ограничение - правую часть, в нашем примере D4. с помощью раскрывающегося списка вводится тип соотношения между левой и правой частями ограничения. В нашем примере это >=.Таким образом, требование неотрицательности переменных задано.

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

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

Рисунок 4.10.5 - Диалоговое окно Параметры поиска решения

Рассмотрим элементы этого окна:

·  Поле Максимальное время (Max Time) служит для ограничения времени, отпускаемого на поиск решения задачи

·  Поле Предельное число итераций (Iteration) служит для ограничения числа промежуточных вычислений

·  Поля Относительная погрешность (Precision) и Допустимое отклонение

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

·  Флажок Линейная модель (Assume Linear model) служит для поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи. В случае нелинейной задачи этот флажок должен быть сброшен, в случае линейной задачи — установлен, т. к. в противном случае возможно получение неверного результата

·  Флажок Показывать результаты итераций (Show Iteration Results) служит для приостановки поиска решения и просмотра результатов отдельных итераций.

·  Флажок Автоматическое масштабирование (Use Automatic Scaling) служит для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине, например, при максимизации прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.

·  Группа Оценка (Estimates) служит для выбора метода экстраполяции.

·  Группа Производные (Derivatives) служит для выбора метода численного дифференцирования.

·  Группа Метод (Search) служит для выбора алгоритма оптимизации.

Контрольные вопросы:

1)  Какие задачи позволяет решать Microsoft Excel?

2)  Как вызывается нужная функция?

3)  Какие категории функций Вам известны?

4)  Что позволяет вычислить функция ПЛТ?

5)  Какие параметры у функции ПЛТ?

Список литературы:

1.  Гарнаев MS Excel и VBA в экономике и финансах.-СПб.: БХВ - Санкт-Петербург, 199с., ил.

2.  Информатика. Серия «Учебники, учебные пособия». И57 // Под ред. . – Ростов н/Д: Феникс, 20с.

3.  , Пак, Хеннер. Информатика. М: Изд. центр «Академия», 2000г.- 816 с.

СОДЕРЖАНИЕ

1. Предисловие……………………………………………………………...3

2. Правила выполнения лабораторных работ……………………………..4

3. Описание рабочего места………………………………………………..4

4. Лабораторные работы…………………………………………………...5

4.1 Лабораторная работа №1…………………………………………….5

4.2 Лабораторная работа №2…………………………………………….8

4.3 Лабораторная работа №3…………………………………………....13

4.4 Лабораторная работа №4…………………………………………....18

4.5 Лабораторная работа №5…………………………………………....21

4.6 Лабораторная работа №6…………………………………………....25

4.7 Лабораторная работа №7……………………………………………29

4.8 Лабораторная работа №8……………………………………………34

4.9 Лабораторная работа №9…………………………………………....46

4.10 Лабораторная работа №10…………………………………………51