Функция ФУО — ONLYOFFICE

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

Цель работы: научиться применять изученные в лабораторных работах №1- №6 финансовые функции для решения экономических задач при выполнении индивидуальных заданий.

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

1. Определить номер своих индивидуальных заданий;
2. Создать копию книги финансовый анализ и назвать ее индивидуальные задания;
3. Выполнить задания;
4. Проверить свои знания по контрольным вопросам и сдать лабораторную работу.

Индивидуальные задания:

Задание 1

Вычислить n-годичную ипотечную ссуду покупки квартиры за P руб. с годовой ставкой i% и начальным взносом А%. Сделать расчет для ежемесячных и ежегодных выплат.

Таблица 1

Задание 2

Вас просят дать в долг Р руб. и обещают вернуть Р1 руб. через год, Р 2руб. — через два года и т. д., наконец, Рn руб. — через п лет. При какой годовой процентной ставке эта сделка имеет смысл?

Таблица 2

Задание 3

Вас просят дать в долг Р руб. и обещают возвращать по А руб. в течение п лет. При какой годовой процентной ставке эта сделка имеет смысл?

Таблица 3

Задание 4

Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды Р руб. под годовую ставку i% на срок п лет.

Таблица 4

Задание 5

Вы берете в долг Ppy6. под годовую ставку i% и собираетесь выплачи­вать по А руб. в год. Сколько лет займут эти выплаты?

Таблица 5

Задание 6

Вы берете в долг Рруб. под годовую ставку i’% и собираетесь отдавать по А руб. в год. Сколько лет займут выплаты?

Таблица 6

Задание 7

Вы собираетесь вкладывать по А руб. в течение п лет при годовой ставке i’%. Сколько денег будет на счете через п лет?

Таблица 7

Задание 8

Определить процентную ставку для n — летнего займа в Р руб. с ежегодной выплатой в А руб.

Таблица 8

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

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

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

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

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

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

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

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

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

5) Что позволяет вычислить функция ЧПС? Какие у нее параметры?

6) Что позволяет вычислить функция ВСД? Какие у нее параметры?

7) Что позволяет вычислить функция ПС? Какие у нее параметры?

8) Что позволяет вычислить функция ПРПЛТ? Какие у нее параметры?

9) Что позволяет вычислить функция ОСННПЛАТ? Какие у нее параметры?

10) Что позволяет вычислить функция БС? Какие у нее параметры?

11) Что позволяет вычислить функция КПЕР? Какие у нее параметры?

12) Что позволяет вычислить функция СТАВКА? Какие у нее параметры?

13) Что позволяет вычислить функция АПЛ? Какие у нее параметры?

14) Что позволяет вычислить функция АСЧ? Какие у нее параметры?

15) Что позволяет вычислить функция ФУО? Какие у нее параметры?

16) Что позволяет вычислить функция ДДОБ? Какие у нее параметры?

17) Как Вы определили свой вариант индивидуальных заданий?

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

1. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах.-СПб.: БХВ- Санкт-Петербург, 1999.- 336 с., ил.

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

Лабораторная работа №8

Тема: Создание отчетных ведомостей в MICROSOFT EXCEL

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

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

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

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

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

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

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

Примеры простейших отчетных ведомостей

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

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

Рис.4.8.1. Примеры списков

Для создания любого из приведенных списков, за исключением столбца G, достаточно ввести в ячейку первый элемент списка, выделить ячейку, установить указатель мыши на маркер заполнения ячейки и протащить его вдоль столбца (строки) до тех пор, пока не будет создан требуемый ряд. На вкладке Списки (Custom Lists) диалогового окна Параметры (Options) (рис.4.8.2), открываемого командой Сервис, Параметры (Tools, Options), приведены встроенные в Excel списки, которые представляют собой последовательности названий месяцев и дней недели.

Рис.4.8.2. Вкладка Списки диалогового окна Параметры

Используя вкладку Списки (Custom Lists), можно создавать пользовательские списки. Элементы списка пользователя надо ввести в поле Элементы списка (List Entries), причем каждый элемент вводится с новой строки. Если нажать кнопку Добавить (Add), то созданный список будет занесен в библиотеку списков. Список можно также добавить и непосредственно с рабочего листа, указав в поле Импорт списка из ячеек (Import List from Cells) диапазон, из которого импортируется список.

Кроме стандартных списков, занесенных в библиотеку. Excel позволяет легко создавать по приведенному выше алгоритму последовательности с текстом и порядковыми номерами (рис.4.8.1, столбцы Е, F и Н). Если номера меняются с шагом, отличным от единицы, необходимо в две соседние ячейки ввести первые два члена последовательности, например, экспо 87 в G1 и экспо 89 в G2, затем выделить диапазон G1:G2, установить указатель мыши на маркер заполнения диапазона и протащить его вдоль столбца до тех пор, пока не будет создан требуемый ряд.

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

Функция сумм (SUM) находит сумму чисел из указанного диапазона ячеек.

Синтаксис:

СУММ(число1; число2; …), где число1, число2, … — числа, которые суммируются.

Функция срзнач (average) находит среднее значение чисел из указанного диапазона ячеек.

Синтаксис:

СРЗНАЧ(число1; число2; …). Аргументы — те же, что и у функции сумм.

Функция ранг (rank) возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией).

Синтаксис:

РАНГ(число; ссылка; порядок)

Аргументы:

число — число, для которого определяется ранг; ссылка — массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются; порядок — число, определяющее способ упорядочения. Если порядок равен 0 или опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если порядок — любое ненулевое число, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Отметим, что функция ранг присваивает повторяющимся числам одинаковый ранг.

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

Первый пример.

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

Сначала обсудим, как создать отчетную ведомость о результатах работы сети магазинов, приведенную на рис.4.8.2.

Рис.4.8.2. Отчетная ведомость о работе сети магазинов за июнь — август

В ячейку Е4 введем формулу:

=СУММ(В4:D4),

которую с помощью маркера заполнения протащим на диапазон Е4:Е9. В ячейку В 10 введем формулу:

=СУММ(В4:В9),

которую протащим на диапазон В10:Е10. В ячейку G4 введем формулу:

=СРЗНАЧ(В4:D4),

которую протащим на диапазон G4:G9. В ячейку Н4 введем формулу:

=Е4/$Е$10,

которую протащим на диапазон Н4:Н9. После чего диапазону ячеек H4:H9 назначим формат с помощью кнопки на панели инструментов:

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

Для ввода в формулу абсолютного адреса ячейки достаточно после ввода ее относительного адреса нажать клавишу <F4>. Если бы в ячейку Н4 была введена формула =Е4/Е10, то ее протаскивание на ячейки Н5:Н9 привело бы к неверному результату. Присвоение ячейке имени с помощью команды Вставка / Имя / Присвоить (Inset, Name, Define) дает другой способ абсолютной адресации ячейки. Например, если бы ячейке Е10 было присвоено имя итого, то в ячейку Н4 можно было бы ввести формулу:

=Е4/Итого

которую затем протаскиваем на диапазон Н4:Н9. Для нахождения места магазина по объему продаж введем в ячейку F4 формулу:

=РАНГ(Е4;$Е$4:$Е$9),

которую протаскиваем на диапазон F4:F9.

С помощью функции частота (frequency) подсчитаем для данного множества суммарных выручек магазинов, сколько значений попадает в интервалы от 0 до 1000, от 1001 до 1100, от 1101 до 1200 и свыше 1201 млн. руб. С этой целью в диапазон ячеек 14:16 введем верхние границы этих интервалов: 1000, 1100 и 1200, соответственно, а в диапазон ячеек J4:J7 введем формулу:

{=ЧАСТОТА(Е4:E9;I4:I6)}

Фигурные скобочки не вводите вручную. После того как Вы наберете функцию нажмите одновременно три клавиши: Ctrl Shift Enter и скобки появяться автоматически. Если в последующем Вы решите внести изменения в функцию, то после обязательно, нажмите эти же клавиши, иначе у Вас появиться сообщение об ошибке.

Данная формула выведет в ячейку J4, сколько значений находится в интервале от 0 до 1000, в ячейку J5 — от 1001 до 1 100, в ячейку J6 — от 1 101 до 1 200, в ячейку J7 — сколько значений будет не меньше 1201.

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

Синтаксис: ЧАСТОТА(массив_данных; массив_карманов).

Аргументы:

массив_данных — массив или ссылка на множество данных, для которых вычисляются частоты; если массив_данных не содержит значений, то функция частота возвращает массив нулей; массив_карманов- массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных; если массив_карманов не содержит значений, то функция частота возвращает количество элементов в аргументе массив данных.

Частоты можно также вычислить, воспользовавшись диалоговым окном Анализ данных (Data Analysis), которое открывается командой Сервис / Анализ данных (Tools, Data Analysis). Средство анализа данных является одной из надстроек Excel. Если в меню Сервис (Tools) отсутствует команда Анализ данных (Data Analysis), то для ее установки нужно выполнить команду Сервис / Надстройки / Analysis ToolPak (Tools, Add-ins, Analysis ToolPak).

После выбора пункта Гистограмма (Histogram) в диалоговом окне Анализ данных (Data Analysis) откроется диалоговое окно Гистограмма (Histogram) (рис.4.8.3).

Рис.4.8.3. Диалоговое окно Гистограмма

В поле Входной интервал (Input Range) введем диапазон Е4:Е9, по которому строим гистограмму. В поле Интервал карманов (Bin Range) введем диапазон I4:I6 со значениями верхних границ интервалов. Гистограмма строится на новом рабочем листе или на текущем листе с указанием диапазона ячеек для результата. В данном случае в поле ввода Выходной интервал (Output Range) введем диапазон L4:L7. На рис. 4.8.4 приведен результат построения гистограммы.

Рис. 4.8.4. Результат построения гистограммы

Второй пример.

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

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

В ячейки А22:С22 введены стоимости трех различных товаров, а в ячейки B25:D27 — объемы их реализации по месяцам. Для того чтобы вычислить суммарную стоимость реализованных товаров по месяцам, введем в ячейки Е25:Е27 формулу:

{=МУМНОЖ(В25:D27;ТРАНСП(А22:С22)}

Отметим, что данную таблицу можно было заполнить и без привлечения матричных формул. Можно ввести в ячейку Е27 формулу:

=СУММПРОИЗВ(В25:D25;$А$22:$С$22)

и протащить ее на диапазон Е25:Е27. Функция суммпроизв (sumproduct) вычисляет сумму произведений элементов указанных диапазонов ячеек.

Рис. 4.8.5. Расчет итоговой выручки по объему реализации

При построении гистограммы (рис. 4.8.5) в поле ввода первого диалогового окна Мастер диаграмм (Cart Wizard) введите диапазоны А25:А27; Е25:Е27. Напоминаем, что для одновременного выделения диапазонов, которые не примыкают друг к другу, сначала необходимо выделить первый диапазон, а потом при нажатой клавише <Ctrl> — второй.

Пример отчетной ведомости по расчету просроченных платежей

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

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

Дата переучета введена в ячейку F2 с помощью формулы:

=ДАТА(98;7;31)

Функция дата (date) возвращает дату в числовом формате.

Синтаксис: ДАТА(год; месяц; день)

Рис.4.8.6. Расчет просроченных платежей

Аргументы:

год — число от 1900 до 2078; месяц — число, представляющее номер месяца в году. Если оно больше 12, то прибавляется к первому месяцу указанного года. Например, дата (96; 14;2) возвращает числовой формат даты 2 февраля 1997 года; день — число, представляющее номер дня в месяце. Если оно больше числа дней в указанном месяце, то прибавляется к первому дню указанного месяца

В ячейку Е2 введена формула, определяющая срок просрочки:

=ЕСЛИ(D2=0;$F$2-С2;»») ,

которая протаскивается на диапазон ЕЗ:Е20. В ячейки G8, G9 и G10 введены следующие формулы:

(=СУММ((Е2:Е20>0)*(Е2:Е20<=29)*(В2:В20))} {=СУММ((Е2:Е20>=30)*(Е2:Е20<=39)*(В2:В20))} {=СУММ((Е2:Е20>=40)*(В2:В20))},

вычисляющие суммарные стоимости просроченных оплат сроком до 29 дней, от 30 до 39 дней и свыше 40 дней.

Дадим пояснения к третьей из этих формул. Excel в формуле массива возвращает условие (Е2:Е20>=40) в виде массива, состоящего из 0 и 1, где о стоит на месте ячейки со значением меньше 40 и 1 — на месте ячейки со значением не меньше 40. Следовательно, данная формула вычисляет сумму произведений элементов массива (Е2:Е20>=40) (с единицами в случае просрочки на указанный срок и нулями — в противном случае) и массива В2:В20 (с ценами процессоров). Таким образом, третья формула возвращает суммарную стоимость заказов, просроченных не менее чем на 40 дней.

В ячейки G2, G3 и G4 введены формулы:

{=СУММ((Е2:Е20>0)*(Е2:Е20<=29))}

{=СУММ((Е2:Е20>=30)*(Е2:Е20<40))}

=СЧЁТЕСЛИ(Е2:Е20;»>=40″),

вычисляющие количество просроченных оплат сроком до 29 дней, от 30 до 39 дней и свыше 40 дней.

Функция счётесли (COUNTIF) возвращает количество ячеек внутри указанного интервала, удовлетворяющих заданному критерию.

Синтаксис: СЧЁТЕСЛИ(интервал; критерий)

Аргументы:

интервал — интервал, в котором нужно подсчитать ячейки; критерий — критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать (например, критерий может быть выражен следующим образом: 17, «17», «>17″,»Компьютер»)

Пример отчетной ведомости по расчету затрат на производство

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

Рассмотрим пример составления отчетной ведомости по расчету затрат на производство товара (рис.4.8.7).

Рис.4.8.7. Расчет затрат на производство товара

Предположим, что фирма производит CD-диски. Упаковка диска обходится фирме в 1 руб./шт., стоимость материалов — 4 руб./шт. Готовые диски фирма продает по цене 10 руб./шт. Технические возможности фирмы позволяют выпускать до 5 тысяч дисков в день. Оплата труда рабочих является сдельной и зависит от количества выпущенных дисков. За первую тысячу дисков оплата труда рабочих составляет 0,3 руб./шт., за вторую тысячу дисков — 0,4 руб./шт., за третью тысячу дисков — 0,5 руб./шт., за четвертую тысячу дисков — 0,6 руб./шт. и свыше 4000 дисков — 0.7 руб./шт.

Фирме поступил заказ на изготовление 4500 CD-дисков. Необходимо подсчитать суммарные издержки и прибыль от выполнения данного заказа.

Для упрощения чтения формул присвоим с помощью команды Вставка / Имя / Присвоить (Insert, Name, Define) диапазонам D2:D7, E2:E7, F2:F7 и ячейке В1, соответственно, имена:

ДискиШт ОплатаРубШт ОплатаРуб ЗаказШт

Зарплата рабочих, в зависимости от объема выпущенных дисков, находится в диапазоне F2:F7 по формуле:

{=ЕСЛИ(ЗаказШт-1000>ДискиШт;1000*ОплатаРубШт;

ЕСЛИ(ЗаказШт>ДискиШт;(ЗаказШт-ДискиШт)*ОплатаРубШт;0))}

Заметим, что имя диапазона или ячейки удобнее вводить в формулу из диалогового окна Вставка имени (Paste Name), которое открывается командой Вставка / Имя / Вставить (Insert, Name, Paste), что помогает избегать ошибок при вводе с клавиатуры (рис.4.8.8).

Стоимость упаковки и материалов вычисляются в ячейках В6 и В7 по формулам:

=В1*ВЗ

=В1*В4

Зарплата, общие издержки и прибыль вычисляются в ячейках В8, В9 и В 10 по формулам:

=СУММ(ОплатаРуб)

=СУММ(В6:В8)

=В1*В2-В9

Расчет прибыли и затрат на производство закончен.

4.8.2. Индивидуальное задание

Номер варианта определяется согласно списку группы в файле с рейтингом, если у Вас номер более 10, то от номера отнимаете число 10 и получаете номер своего варианта.

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

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

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

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

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

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

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

Задание:

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

Таблица 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах.-СПб.: БХВ- Санкт-Петербург, 1999.- 336 с., ил.

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

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

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

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

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

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

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

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

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

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

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

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

Прежде всего опишем функции макс (М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 матрицы P¹ и P² определяют переходные вероятности без рекламы и при ее наличии в течение любой недели.

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

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

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

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

Тогда:

,

гдеf_{N 1}(j) = 0 при всех j. Пусть

тогда

.

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

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

вычисляющая v₁¹, которая протаскивается на диапазон I6:I10 для вычисления v₂¹,…,v₃² .

В ячейки диапазона 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 — при удовлетворительной оценке.

Задание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах.-СПб.: БХВ- Санкт-Петербург, 1999.- 336 с., ил.

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

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

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

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

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

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. Исходные данные задачи

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

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

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

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

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

;

;

dj <= Xj <= Dj;

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

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

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

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

Z = 3000*X_E 2000*X_I .

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

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

X_E, X_I >=0

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

X_E 2 X_I <=6,

2 X_E X_I <=8.

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

X_I — X_E <=1,

X_I <=2.

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

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

Z=3000* X_E 2000* X_I

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

X_E 2* X_I <=6,

2* X_E X_I <=8,

X_I — X_E <=1,

X_I <=2,

X_I, X_E >=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 в экономике и финансах.-СПб.: БХВ- Санкт-Петербург, 2022.- 336 с., ил.

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

3. А.В. Могилев, Пак, Хеннер. Информатика. М: Изд. центр «Академия», 2022г.- 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