Вариант 2. Лабораторная работа по Прикладной информатике

4 Лабораторные работы, вариант - 2

ПОЛНОЕ ЗАДАНИЕ В ДЕМО ФАЙЛЕ

ЧАСТЬ ДЛЯ ПОИСКА ДУБЛИРУЮ НИЖЕ

Содержание

          Введение  …………………………………………………………….. ………….3

 Часть 1 ……………………………………………………………………………. 41. Лабораторная работа  №1 - Построение диаграмм с помощью MSExcel … 4

 2. Лабораторная работа   № 2–Решение задач сглаживания

экспериментальных зависимостей …………………………………………   7

 3. Лабораторная работа № 3 – Технологии финансовых расчетов.

            Финансовые функции ………………………………………………………… 17                                 

4. Лабораторная работа N4 - Технология реализации методов оптимизации

             и  математического программирования. …………………………………… 20

           Приложения …………………………………………………………………… 36

           Приложение А ……………………………………………………………….. . 36

       Приложение Б ………………………………………………………………… 38

Введение

           Учебным планом дисциплины «Информационные системы и технологии» па кафедре «Информационные системы в экономике» предусмотрено выполнение лабораторных работ в течение двух семестров обучения. 

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

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

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

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

         Списки использованных источников приведены в пределах каждой из лабораторных работ. (Для лабораторной работы №7 предусматривается предоставление студенту источника [2] в виде отдельного pdf-файла из фонда электронных источников кафедры ИСЭ).

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

Оформление результатов работы может осуществляться различными способами. Основным вариантом оформления отчета является обычный вариант отчета по лабораторной работе, сформированный средствами редактора World (с обязательным титульным листом, выполненным в соответствии с СТП АлтГТУ, и внедренными рисунками, отображающими необходимые копии фрагментов экрана компьютера). Отчеты по некоторым работам могут представлять собой распечатанный лист табличного процессора MSExcel (с указанием фамилии студента и текстовыми пояснениями).

         Каждый отчет должен быть представлен как в бумажной, так и в электронной форме.

      Для выполнения работ используется различное программное обеспечение.

Для лабораторных работ первой части предполагается использование программы Microsoft Excelc надстройками.

          Для лабораторных работ второй части предусматривается использование нескольких программ, реализующих CASE-технологии (как правило, бесплатные и учебные версии, допускается использование бесплатных аналогов). Для последней работы предполагается использование учебной версии программы 1:С Предприятие 8.3.

                                           ЧАСТЬ 1

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

                  Построение диаграмм с помощью MSExcel

1 Цель работы

Изучение технологии построения диаграмм как средства визуализации решений экономических задач.

2 Краткие теоретические сведения

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

Основные положения технологии построения диаграмм приведены в Приложении А. Рекомендуется также использовать средства помощи, встроенные в пакет прикладных программ MS Excel.   

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

В процессе выполнения работы необходимо познакомиться с технологиями редактирования каждого из элементов диаграммы.

3 Порядок выполнения работы

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

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

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

 Все диаграммы должны иметь заголовки. Оси системы координат диаграмм должны иметь подписи.

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

 Диаграммы строятся последовательно по каждому из возможных видов. 

Первым видом диаграмм должны быть гистограммы.

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

Вторым видом диаграмм должны быть диаграммы вида «график».

Третьим видом диаграмм должны быть «диаграммы с областями».

Четвертым видом диаграмм должны быть гистограммы вида «линейчатые».

 После этого должны быть построены круговые диаграммы. При этом нужно использовать данные одного из столбцов таблицы.

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

Желательно выполнение диаграмм и других видов.

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

4 Перечень вариантов заданий исходных данных

       Вариант 1. Численность населения мира, млн. чел.

1900

1913

1929

1938

1950

1960

1970

1980

1990

2000

США

76,4

97,6

122,2

130,5

153

176

200,5

227

247

277

Германия

45,7

54,7

58,7

62,3

67

72

77

78,5

79

82

Франция

40,8

41,8

42

42

42

46

50,5

54

56,5

59

Япония

44

51,6

63,2

71,8

83

93

104

116,8

123,5

127

СССР

123

158

171,5

186,5

205,5

226,5

247

285,5

290

290

    Вариант 2. Численность занятых в мировой экономике, млн. чел.

1900

1913

1929

1938

1950

1960

1970

1980

1990

2000

Германия

18,5

23,5

25

26,5

29

31

34

35

37

38,5

Франция

20

20

20

19,5

19

21

23

25

26,5

27,5

Великобритания

16,6

18,5

20

20,5

22,5

24

25

25,5

26

26,5

Италия

15

16,5

17

18

18,5

20

22

24

24,5

25

     Вариант 3. Промышленное производство : добавленная стоимость , в ценах и по ППС нац. валют 2000 г., млрд долл

1900

1913

1929

1938

1950

1960

1970

1980

1990

2000

Германия

29

51

59

478

94

244

420

510

575

625

Франция

28

46

57

52

63

93

190

275

310

355

Великобритания

33

73

84

105

130

180

245

265

300

335

Советский Союз

40

70

80

105

205

480

725

935

1000

545

     Вариант 4. Мировое сельскохозяйственное производство: добавленная стоимость в ценах и по ППС 2000 г., млрд долл.

1900

1913

1929

1938

1950

1960

1970

1980

1990

2000

США

43

56

69

76,5

93,5

105

128,5

146

157,5

175

Германия

16

19

20

21,5

23

29

37

40,5

46,5

52,55

Франция

21,5

22

22,5

23

23,5

29,5

47

53

65

76,5

Япония

13,5

14,5

16

17

18,5

30,5

42

44,5

49

56

СССР

37

50,5

58,8

63

75

81,5

87,5

98

120

100

   Вариант 5. Мировой товарный экспорт, в ценах и по ППС 2000 г., млрд долл.

1900

1913

1929

1938

1950

1960

1970

1980

1990

2000

Германия

21,5

54

58

64,1

36,5

87,5

185

385

600

710

Франция

22

28,5

40,5

40

31,5

62,5

140

235

330

420

Великобритания

38,5

54,5

73

76

66

105

160

235

320

400

Бельгия

12,2

15,5

18,4

16,8

12,3

27,5

63

112

176

214

    Вариант 6. Динамика добычи нефти крупнейшими российскими компаниями, млн. тонн.

Компания

1996

1997

1998

1999

2000

2001

2002

2003

Сургутнефтегаз

33

33,9

35,2

37,6

41

44

48

52

ЛУКОЙЛ

53

57,1

64,2

73,5

75

78,3

81,4

84,7

Татнефть

25

25,5

25,8

26,3

23,1

24,6

24,7

26

Сибнефть

19

18,2

17,3

16,3

17,2

20,7

26,8

31,5

5 Оформление результатов работы

Отчет о выполненной работе может представляться в одной из двух форм: 1) на одном листе рабочей книги Excel, 2) в виде текста, набранного в редакторе World с вставкой в него диаграмм.

При любом представлении в отчете должны быть приведены исходные данные и все построенные диаграммы.

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

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

Список использованных источников

 1. Рудикова Л.В. Microsioft Excel для студента.–СПб.: БХВ-Перербург, 2005.-368 с.  

         2. Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0.– СПб.: BHV-Санкт-Петербург, 1997.-384 с.

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

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

         1Цель работы

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

2 Краткие теоретические сведения    

          На практике часто приходится решать задачи определения зависимости одной величины от другой. Такие зависимости могут быть как теоретическими, так и статистическими[1,2] .

          Второй вид зависимостей определяется как набор nпар значений (xi, yi) (i =1,2, …, n).

          В таких парах значений первая переменная отвечает значению независимой переменной, а вторая – значению зависимой переменной, определенной по результатам проведенного опыта (измерения, эксперимента) Зависимость представляется таблицей вида                   

Аргумент

x1

x2

   …

xn

Функция

y1

y2

   …

yn

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

Di= yi– f(xi)

будут отличны от нуля.

           В этих условиях ставится задача нахождения такой функции y=f(х), которая бы описывалась как некоторая аналитическая функция и была бы наиболее близка к экспериментальной зависимости, определенной по результатам опыта и описываемой таблицей.

           Такая задача называется задачей сглаживания экспериментальной зависимости.

           Функция y=f(х) может быть построена различными способами. Так, например, можно потребовать, чтобы найденная аналитическая функция совпадала во всех точках со статистической (табличной) функцией. Однако во многих случаях такая замена является неудовлетворительной.

          Для решения рассматриваемой задачи обычно применяется метод, называемый методом наименьших квадратов[1-3] . 

 В этом методе требование наилучшего согласования кривой y=f(х) с набором экспериментальных точек, представляемых таблицей, сводится к тому, чтобы сумма квадратов отклонений экспериментальных точек от сглаживаемой кривой обращалась в минимум (сумма квадратов величин Di обращалась в минимум).

         При использовании этого метода сначала должен быть задан некоторый общей вид функции y=f(х). Она может быть линейной, логарифмической, полиномиальной, экспоненциальной, представлять собой обратно-пропорциональную зависимость и др.). Все указанные виды функций имеют некоторые параметры. (Например, линейная функция вида y=ax +b имеет два параметра – aиb). Метод наименьших квадратов позволяет найти значения параметров, однозначно определяющих в заданном классе функций такую функцию, которая наилучшим образом будет описывать зависимость, полученную в результате наблюдения в виде множества точек. 

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

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

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

        Решение задачи регрессионного анализа отличается от решения задачи сглаживания экспериментальных зависимостей тем, что в первом случае помимо нахождения функции регрессии решается еще и широкий круг вопросов оценки качества полученной функции регрессии [1-4] .  

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

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

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

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

В Excel предусмотрены следующие варианты функций:

         1. Линейная –у = ах+b. Обычно применяется в простейших случаях, когда экспериментальные данные возрастают или убывают с постоянной скоростью.

          2. Полиномиальная – y= a0+ a1x+ a2x2+ ...+ anxn , где до шестого порядка включительно (п≤6), аi — константы. Используется для описания экспериментальных данных, попеременно возрастающих и убывающих. Степень полинома определяется количеством экстремумов (максимумов или минимумов) кривой. Полином второй степени может описать только один максимум или минимум, полином третьей степени может иметь один или два экстремума, четвертой степени – не более трех экстремумов и т. д.

3. Логарифмическая – у = alnx + b, где а и b – константы, ln – функция натурального логарифма. Функция применяется для описания экспериментальных данных, которые вначале быстро растут или убывают, а затем постепенно стабилизируются.

        4. Степенная – y = bxa , где а и b — константы. Аппроксимация степенной функцией используется для экспериментальных данных с постоянно увеличивающейся (или убывающей) скоростью роста. Данные не должны иметь нулевых или отрицательных значений.

          5. Экспоненциальная – y = beax , где а и b – константы, е – основание натурального логарифма. Применяется для описания экспериментальных данных, которые быстро растут или убывают, а затем постепенно стабилизируются. Часто ее использование вытекает из теоретических соображений.

            Степень близости аппроксимации экспериментальных данных выбранной функцией оценивается коэффициентом детерминации (R2) [4] . Таким образом, если есть несколько подходящих вариантов типов аппроксимирующих функций, можно выбрать функцию с большим коэффициентом детерминации (стремящимся к 1).

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

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

         Дальнейшая последовательность действий различна для различных версий Excel. Приведем одну из них.   

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

           Рассмотрим несколько примеров решения задачи с помощью MS Excel..

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

Таблица 1

Год

Производство продукции

1997

17,1

1998

18

1999

18,9

2000

19,7

2001

19,7

Решение

1. Для построения диаграммы, прежде всего, необходимо ввести данные в рабочую таблицу. Вводим в ячейку А1 слово Год. Затем в ячейки А2:А6 последовательно вводим годы, начиная с 1997. Далее в ячейку В1 заносим слово Продукция и устанавливаем табличный курсор в ячейку В2. Здесь должно оказать ся значение 17,1 соответствующее значению года в ячейке А2. Аналогично заполняем ячейки ВЗ:В6.

   2. Далее по введенным в рабочую таблицу данным необходимо построить диаграмму. Поскольку здесь необходимо строить динамику изменений производства продукции, не привязываясь к конкретному году, а от отвлеченных переменных –выберем диаграмму График. На закладку Вставить выбираем График. На появившемся объекте графика «кликаем» правой кнопкой мыши и в контекстном меню выбираем пункт – Выбрать данные. В окне выборе источника данных добавляем новый ряд графика, указываем в качестве данных ячейки: B2:B6, диапазон подписей горизонтально ячейки: А2:А6. Получен график экспериментальных данных.

3. Осуществим аппроксимацию полученной кривой поли­номиальной функцией второго порядка, поскольку кривая до­вольно гладкая и не сильно отличается от прямой линии. Для это­го указатель мыши устанавливаем на одну из точек графика и

щелкаем правой кнопкой мыши. В появившемся контекстном меню вы­бираем пункт Добавить линию тренда. Появляется диалоговое окно Линия тренда (рисунок.1)

В этом окне на вкладкеТип выбираем тип линии тренда— Полиномиальная и устанавливаем степень – 2. Также устанавливаем флажки в поля По­казывать уравнение на диаграмме и Поместить на диаграмму ве­личину достоверности аппроксимации (R2). После чего нужно щелкнуть на кнопке Закрыть. В результате получим на диаграмме аппроксимирующую кривую (рисунок 2).

Как видно из рисунка 2, уравнение наилучшей полиноми­альной аппроксимирующей функции для некоторых отвлеченных значений х (1, 2, 3, ...) выглядит какy = - 0,1357x2 +1,5043x +15,66. При этом точность аппроксимации достаточно высока —R2 = 0,984.

        4. Попробуем улучшить качество аппроксимации выбором другого типа функции (возможно более адекватного). Здесь воз­можным вариантом представляется логарифмическая функция. Для этого повторяем операции п.                3. за исключением того, что в окне Линия тренда на вкладке Тип выбираем тип линии тренда — Логарифмическая. В результате получим другой вариант аппроксимации — логарифмической кривой (рисунок 3).

Как можно видеть, уравнение наилучшей ло­гарифмической аппроксимирующей функции несколько уступает по точности аппроксимации полиномиальной кривой:R - 0,9716 < 0,986. Поэтому если нет каких-либо теоретических со­ображений, то можно считать, что наилучшей аппроксимацией является аппроксимация полиномиальной функцией второй сте­пени (из двух рассмотренных вариантов).

Рисунок 1 - Вкладка Тип диалогового окна Линия тренда

Рисунок 2 - Экспериментальные данные, аппроксимированные  полиномиальной кривой

Рисунок 3 - Экспериментальные данные, аппроксимированные логарифмической кривой

Пример 2. После выброса ядовитого вещества его концентрация (мг/л) в водоеме изменялась в соответствии со следующей таблицей:

           Таблица 2

Время выброса (час)

Концентрация вещества (мг / л)

1

8,0

3

2,8

5

1,0

8

0,3

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

Решение

1.           Для построения диаграммы, прежде всего, необходимо ввести данные в рабочую таблицу. Вводим в ячейку А1 слово Время. Затем в ячейки А2:А5 последовательно вводим время: 1, 3, 5, 8. Далее в ячейку В1 заносим слово Концентрация и в диа­пазон В2:В5 вводим соответствующие концентрации вещества.

2.        Далее по введенным в рабочую таблицу данным необхо­димо построить диаграмму. Поскольку здесь необходимо строить динамику изменений концентрации вещества в соответствии с изменениями времени – будем строить диаграмму Точечная. Вставляем на лист точечную диаграмму с маркерами. Указываем для данной диаграммы источник данных. Имя ряда – «Концентрация», значения X – A1:A5, значения Y – В1:В5. Получен график экспериментальных данных.

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

В этом окне на вкладке Тип выбираем тип линии тренда – Экспоненциальная. Также уста­навливаем флажки в поля показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (Ra2), Кроме этого, для того, чтобы оценить концентрацию веще­ства в водоеме в момент выброса в поле Прогноз назад на уста­навливаемравным 1. В ре­зультате получим на диаграмме аппроксимирующую кривую (рисунок4).

Как видно из рисунка 4, уравнение наилучшей экспонен­циальной аппроксимирующей функции для зависимости концен­трации от времени выглядит как

y = 11,844e-0.47x                                                                                                                                                                                                                                                                          (1)

При этом точность аппроксимации очень высокая – R\2 = 0,9951, что позволяет считать описание процесса изменения концентрации вещества в водоеме экспоненциальной функцией адекватным. Расчетная оценка концентрации вещества в момент выброса, как видно из графика, составляет около 12 мг/л. Более точные цифры могут быть получены из уравнения (12) при х = 0 (у0 = 11,84 мг/л).

Рисунок 4 - Экспериментальные данные, аппроксимированные экспоненциальной функцией

3 Задания на выполнение работы

1. Построить функцию, наилучшим образом отражающую данную зависимость:

Таблица 1

X

1,0

1,5

3,0

4,5

5,0

Y

1,25

1,4

1,5

1,75

2,25

2. В 80-е годы уровень дефицита бюджета в СССР и США складывался следующим образом:

 Таблица 2

СССР

2,9

2,3

3,1

2,2

2,0

2,7

6,5

8,0

9,1

США

2,8

2,6

4,1

6,3

5,0

5,4

5,3

3,4

3,2

Построить функции, наилучшим образом отражающие зави­симости дефицита бюджета от времени в обеих странах.

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

Таблица 3

x

1,6

2,0

2,5

3,0

4,0

7,0

y

8,5

9,0

11,0

13,0

22,0

70,0

Запишите аналитическую зависимость между х и у. Проана­лизируйте полученный ответ. Каковы перспективы предприятия? Какая будет прибыль, если вложить 10,0 единиц? Сколько надо вложить средств, чтобы получить прибыль 100,0 единиц?

4 Оформление результатов работы

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

Отчет о выполненной работе может представляться в одной из двух форм: 1) на одном листе рабочей книги Excel, 2) в виде текста, набранного в редакторе World с вставкой в него диаграмм.

При любом представлении в отчете должны быть приведены исходные данные и все построенные диаграммы.

                        Список использованных источников

          1. Математическая статистика: Учебник / Иванова В.М, Калинина ВюИю, Нешумова Л.А.– 2-е изд. перераб. и доп. –М.: Высш. школа, 1981.-371 с.

         2. Вентцель Е.С. Теория вероятностей: учеб. для вузов.–6-е изд._–М.: Высш. шк., 1999.-576 с.

          3. Гурский В.И. Теория вероятностей с элементами математической статистики :Учеб. пособие для втузов. -М.: Высшая школа, 1971. -328 с. 

          4. Гармаш А.Н, Орлова И.В, Концевая Н.В., Горбатенко Е.Н. Большаков В.А. Экономико-математические методы в примерах и задачах : Учеб. пособие / Под ред. А.Н. Гармаша.–М.: Вузовский учебник: ИНФРА-М, 2014.– 416 с.

         5. Рудикова Л.В. Microsioft Excel для студента.–СПб.: БХВ-Перербург, 2005.-368 с.  

         6. Козлов А.Ю. Шишов В Ф Пакет анализа MS Excel в экономико-статистических расчетах: учеб. пособие для вузов / Под ред. проф. В.С. Мхитаряна.–М.: ЮНИТИ-ДАНА, 2003.-139 с. 

          7. Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0.– СПб.: BHV-Санкт-Петербург, 1997.-384 с.

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

Технологии финансовых расчетов. Финансовые функции

1 Цель работы

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

2 Теоретическая часть

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

Особенностями всех финансовых расчетов является временная ценность денег, т.е. принцип неравноценности денег в разные моменты времени [1] .

С учетом этого рекомендуется перед выполнением лабораторной работы ознакомиться с основными понятиями финансовой математики и ее математическими моделями по рекомендуемым источниками[1 - 4]. 

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

В MS Excel используются следующие средства: финансовые функции MS Excel, подбор параметра, диспетчер сценариев и таблица подстановки [1].

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

Финансовые функции MS Excel предназначены для вычисления базовых величин, необходимых при проведении сложных расчетов. В [1] кратко охарактеризованы 52 такиефункции.     

Финансовые функции могут быть разделены на три группы:

- финансовый анализ инвестиций;

 - вычисление амортизации;

- анализ ценных бумаг.

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

3 Общая технология использования финансовых функций

Работа с финансовыми функциями предполагает использование следующей методики [2]:

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

2. Перейти к ячейке, в которую вводится формула, использующая встроенную финансовую функцию.

3. Вызвать мастер функций командой Вставка/Функция /категория Финансовые и в списке функций выбрать необходимую финансовую функцию. 

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

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

Замечание 2. Описание аргументов по каждой функции приводится в окне Аргументы функции. Если необходима более подробная справка по использованию той или иной функции, следует руководствоваться ссылкой Справка по этой функции, которая открывает оконо Справки MicrosoftExcel.

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

 5. После ввода всех аргументов нажмите кнопку OK, и MS Excel произведет расчет по формуле. 

Замечание 3. В MS Excel возможен непосредственный ввод формулы без использования мастера функций.

 Имеются определенные правила задания аргументов [1,2].

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

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

4 Задания для изучения функций 

Задания для функций финансового анализа инвестиций.

Использование функции БС (БЗ)

Задание №1.

Выполнить пример по использованию функции БЭЗ, приводимый в приложении Б

Задание №2.

Определить, какая сумма окажется на счете, если вклад размером 1 000 000 руб. положен под 8% годовых на 10 лет, а проценты начисляются ежеквартально.

Задание № 3.

Рассчитать, какая сумма окажется на счете, если 27 тыс. руб. положены на 33 года под 13,5% годовых. Проценты начисляются каждые полгода.

Использование функции КПЕР.

Задание № 4

Через сколько лет вклад размером 500 000 руб. достигнет 1 000 000 руб. при ежемесячном начислении процентов и ставке 35,18% годовых?

Задание № 5

Ссуда размером 66 000 тыс. руб., выданная под 36% годовых, погашается обычными ежемесячными платежами по 6630 тыс. руб. Рассчитать срок погашения ссуды.   

 Использование функции СТАВКА(НОРМА)

 Задание № 6

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

Задания для функций вычисления амортизации

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

Варианты заданий:

1) X=10000 тыс. руб., Y=7 лет, Z=300 тыс. руб.;

2) X=8000 тыс. руб., Y= 8 лет, Z=400 тыс. руб.

По итогам вычислений необходимо построить таблицу, в которой для каждого года необходимо привести значение ежегодных амортизационных отчислений , подсчитанных с помощью функций АМР, АМГД, ДДОБ, ДОБ и ПДОБ.

5 Представление результатов работы

Работа должна быть представлена в виде отчета на листе Excel.

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

Список использованных источников

1. Фуфаев Э.В. Пакеты прикладных программ: учеб. пособие для сред. проф. образования / Э.В. Фуфаев, Л.И. Фуфаева.-М.: Изд. центр Академия, 2004.-352 с.

         2. Рудикова, Л.В. MicrosoftExcel для студента [ Текст]/Л.В. Рудикова . –СПб.: БХВ-Петербург, 2005. –368 с.:

          3. Гармаш А.Н, Орлова И.В, Концевая Н.В., Горбатенко Е.Н. Большаков В.А. Экономико-математические методы в примерах и задачах : Учеб. пособие / Под ред. А.Н. Гармаша.—М.: Вузовский учебник: ИНФРА-М, 2014.– 416 с.

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

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

Технологияреализации методов оптимизации и математического программирования

.

1 Цель работы

Изучение технологии реализации методов оптимизации и математического программирования

2 Теоретические положения

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

целевую функцию

и

ограничения

(непосредственные и функциональные) [1].

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

Выделяются три основных вида задач математического программирования: задачи линейного программирования, задачи нелинейного программирования и задачи дискретного программирования [2,3]. 

3 Технологии решения задач оптимизации

         3.1 Общее описание информационной технологии решения задач оптимизации

         Для решения таких задач в Excel используется надстройка

Поиск решения

.

          Информационная технология решения задач математического программирования в Excel c использованием указанной надстройки имеет несколько этапов.

           На

первом этапе

на листе табличного процессора строится специальная таблица, компактно отражающая условия решения задачи. Она имеет три логические части: описание переменных задачи, описание целевой функции и описание ограничений [4,5].

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

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

          Отводится некоторая ячейка для значения целевой функции. Над этой ячейкой вводится текст, идентифицирующий эту функцию. Рядом дополнительно в виде текста указывается вид экстремума (min или max).

         Далее различными способами описывается вид целевой функции.

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

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

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

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

          На

втором этапе

используется надстройка

Поиск решения. При этом в программе запускается окно «Поиск решения».

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

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

          На третьем этапе осуществляется решение задачи.

          Этот этап состоит из двух подтапов. На первом подэтапе задаются параметры поиска решения . Большинство параметров принимается по умолчанию. (Существенным является лишь выбор линейной или нелинейной модели).

         На втором этапе реализуется собственно процедура решения.   

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

         В ней добавляется следующая информация:

         - в ячейках, отведенных под переменные оптимизации, появляются оптимальные значения этих переменных (точка экстремума);

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

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

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

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

          3.2. Решение задачи линейного программирования.

          Сначала рассматривается постановка задачи.

 3.2.1. Пример постановки задачи линейного программирования.

         Имеется некоторое предприятие, которое может выпускать несколько видов продукции. Требуется определить, в каком количестве надо выпускать продукцию четырех типов Прод1, Прод2, Прод3, Прод4, чтобы получить максимальную прибыль.

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

                Рисунок 1 – Представление исходных данных задачи

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

      Составим математическую модель задачи оптимизации. Для этого введем следующие обозначения:                                                                        

x

j– количество выпускаемой продукции j-го типа, j =1,4;

bi – количество располагаемого ресурса i-го вида, i=1,2,3;

aij – норма расхода i-го ресурса для выпуска единицы продукции j-го типа ;

cj– прибыль, получаемая от реализации единицы продукции j-го типа.

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

        Сформируем далее ограничения. Как видно из рисунка 1, для выпуска единицы продукции Прод1 требуется 6 единиц сырья. Следовательно, для выпуска всей продукции Прод1 требуется 6x1 единиц сырья, где x1 – количество выпускаемой продукции Прод1. С учетом того, что для других видов продукции зависимости аналогичны, ограничение на использование сырья будет иметь вид:

                                     6x1+5x2+4x3+3x4 £ 110 .

        Аналогично можно составить ограничения для остальных ресурсов. Тогда математическая модель задачи оптимизации будет иметь вид:                                                    

z = 60x1 +70x2 + 120x3 +130 x4 ®max

x1 + x2 + x3 + x4 £ 16                                 

                           6x1 +5x2 + 4x3 +3x4 £ 110                                     (1)                         

                           4x1 +6x2 + 10x3 +13x4 £ 91

xj³ 0 ; j =1, …,4 

        (Последняя строка математической постановки задачи представляет собой условия неотрицательности переменных xj).                                             

          3.2.2. Последовательность решения задачи линейного программирования с помощью Excel

           В Excel решение задачи линейного программирования осуществляется симплекс-методом. Сначала осуществляется ввод условий задачи.

         3.2.2.1.  Ввод условий задачи

        Ввод условий задачи состоит из следующих основных шагов:

1. Создание формы для ввода условий задачи.

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

3. Ввод зависимостей из математической модели (для целевой функции и функциональных ограничений).

4. Ввод ограничений

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

1. Для задачи, приведенной на рисунке 1, на Excel-листе необходимо сделать форму для ввода условий задачи (рис. 2).

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

        Отметим также, что в рассматриваемой форме используются содержательные обозначения переменных оптимизации x1, x2 ,x3 ,x4 – Прод1, Прод2, Прод3 и Прод4.

При этом предполагается, что под значения переменных оптимизации отведены ячейки диапазона B3:E3.

Рисунок 2 - Вид формы для ввода условий задачи

           3.2.2.2.   Ввести исходные данные в форму, приводимую на рис. 2.  

          В диапазон ячеек B6:E6 вводятся коэффициенты целевой функции, в диапазон ячеек G9:G11 – знаки в ограничениях (в вычислениях они не участвуют), в диапазон ячеек H9:H11 – свободные члены в ограничениях-неравенствах, B9:E11 – коэффициенты в левых частях неравенств. В ячейки B4:E4 вводятся нижние границы значений переменных оптимизации. 

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

       3. 2.2.3.  Ввести зависимости из математической модели (1).

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

Excel-таблица с введенными данными приведена на рисунке 3, а та же таблица в режиме представления формул – на рисунке 4.

                              Рисунок 3 - Вид таблицы с введенными данными

                            Рисунок 4 - Вид таблицы (режим представления формул)

        3.2.2.4. Ввести зависимость для целевой функции:

Ø   Курсор в F6.

Ø   Курсор на кнопку Мастер Функций.

Ø   М1. (один щелчок левой кнопки мыши).

             На экране: диалоговое окно Мастер функций шаг 1 из 2.

Ø   Курсор в окно Категория на категорию Математические.

Ø   М1.

Ø   Курсор в окно Функции на СУММПРОИЗВ.

Ø   М1.

Ø   Далее.

        На экране: диалоговое окно (рис. 5).

                            Рисунок 5 - Построение целевой функции

Ø В массив 1 ввести B$3:E$3 (адреса ячеек, содержащих переменные оптимизации).

             Заметим, что: 1) переменные оптимизации первоначально равны нулю, 2) адреса ячеек могут быть введены в диалоговые окна либо с клавиатуры, либо путем “протаскивания” мыши по ячейкам, чьи адреса следует ввести (см. приложение).

Ø В массив 2 ввести B6:E6 (значения коэффициентов целевой функции).

Ø Готово.

Ø В ячейку G6 ввести комментарий, отражающий вид экстремума.

             На экране: рис .3, рис. 4 (ячейка F6 содержит значение целевой функции).

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

Ø Курсор в F6.

Ø Копировать в буфер.

Ø Курсор в F9.

Ø Вставить из буфера.

             На экране: в F9 введена функция, как это показано на рис.3.

Ø Скопировать F9 в F10:F11.

             На экране: в F10:F11 введены функции, как это показано на рис. 4.

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

        3.2.2.6. В ячейки В4, С4, В4, Е4 занести значения 0, отвечающее нижней границе возможных значений переменных оптимизации. (Такое занесение не является обязательным, поскольку в Excel незаполненная ячейка числовых данных содержит 0 ).

        На этом ввод данных в таблицы (рис. 3, рис. 4) заканчивается.

         3.2.2.7. Работа в диалоговом окне «Поиск решения»

       1. Сервис, Поиск решения…

           На экране: диалоговое окно «Поиск решения» (рис. 1.1.8).

       2. Назначить целевую функцию:

Ø Курсор в поле ввода Установить целевую.

Ø Ввести адрес: F6.

Ø Ввести с помощью переключателя направление (вид экстремума) целевой функции: Максимальному значению.

       3. Ввести адреса искомых переменных:

Ø Курсор в поле Изменяя ячейки.

Ø Ввести адреса: B3:E3.

      4. Добавить…

           На экране: диалоговое окно «Добавление ограничения» (рис.1.1 9).

      5. Ввести условия неотрицательности: x1 ³ 0,x2 ³ 0, x3 ³ 0, x4 ³ 0 в виде B3 >= B4, C3 >= C4, D3 >= D4, E3 >= E4 .

Ø В поле Ссылка на ячейку ввести В3.

Ø Курсор на стрелку.

Ø М1.

Ø На экране: знаки для ввода в ограничения.

                             Рисунок 6 – Окно Поиск решения

                          Рисунок 7 – Окно добавления ограничений

Ø Курсор на знак >=.

Ø М1.

Ø Курсор в правое поле.

Ø Ввести В4.

Ø Добавить…

          На экране: опять диалоговое окно «Добавление ограничения» (рис. 7).

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

       3.2.2.8. Аналогично ввести ограничения:

F9 <= H9, F10 <= H10, F11 <= H11.

Ø После ввода последнего ограничения вместо Добавить… ввести ОК.

       На экране: диалоговое окно Поиск решения с введенными условиями (рис. 6).

       Если при вводе задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делается с помощью команд Изменить…,. Удалить.

На этом ввод условий задачи заканчивается.

         3.2.2.9. Решение задачи.

         Перед началом решения задачи вводятся параметры, описывающие процедуру решения.

1. Параметры…

        На экране: диалоговое окно «Параметры поиска решения» (рис. 8).

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

        Максимальное время

Служит для назначения времени в секундах, выделяемого на поиск решения задачи. В поле можно ввести время, не превышающее 32767 с (более 9 часов). Значение 100, используемое по умолчанию, подходит для решения большинства задач.

       Предельное число итераций

       Служит для назначения числа итераций. Используемое по умолчанию значение 100 подходит для решения большинства задач.

       После этих пояснений продолжим решение задачи.

             Рисунок 8 – Задание параметров

       2. Установить флажок Линейная модель, что обеспечивает применение симплекс-метода к решению задачи линейного программирования.

      3. ОК.

      На экране: диалоговое окно «Поиск решения» (рис. 1.1.8).

      4. Выполнить.

      На экране: диалоговое окно «Результаты поиска решения». Решение найдено (рис. 9 и рис. 10).

              Рисунок 9 – Сообщение о получении решения

      Рисунок 10 - Исходная таблица с встроенным результатом решения

      Результат оптимального решения задачи приведен в Excel-таблице (рис. 10).

      Из рисунка видно, что в оптимальном решении переменные оптимизации имеют следующие значения (результаты выделены жирным шрифтом):

x1 = Прод1 = В3 = 11, 5 ,

x2 = Прод2 = С3 = 0  ,

x3 = Прод3 = D3 = 4, 5 ,

x4 = Прод4 = E3 = 0 .

       При этом максимальная прибыль будет составлять F6 = 1230, а количество использованных ресурсов будет равно:

                                       трудовых = F9 =16 ,

                                       сырья = F10 = 87 ,

                                       финансов = F11 = 91.

       Таково оптимальное решение рассматриваемой задачи распределения ресурсов.

          3.2.2.10. Графическое представление результатов решения

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

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

Рисунок 11 – Графическое представления оптимального плана

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

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

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

         3.3. Решение задачи целочисленного программирования.

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

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

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

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

3.3.1. Сделать форму для ввода условий задачи, взяв за основу таблицу на рис. 2 и добавив к ней в качестве комментария требования целочисленности. (Для упрощения ввода исходных данных рекомендуется воспользоваться таблицей, полученной в первой задаче, вставив в нее новую строку 6 и заполнив ее необходимыми комментариями).

       Ввести исходные данные аналогично п. 2 предыдущего примера. 

       Ввести зависимости из математической модели задачи аналогично п. 3 того же алгоритма.

       Результаты ввода приведены на рисунке 12.

                 Рисунок 12 – Форма ввода исходных данных

3.3.2. Дальнейшая работа выполняется в диалоговом окне «Поиск решения».

      1. Вызвать диалоговое окно «Поиск решения» командами

Сервис, Поиск решения…

      На экране: диалоговое окно «Поиск решения».

      2. Ввести:

Ø условия, которые были введены для задачи линейного программирования, решенной в подразделах 2.3- 2.5.

Ø требования целочисленности:

§ Добавить…

§ На экране: диалоговое окно «Добавление ограничений».

§   Курсор в поле Ссылка на ячейку.

§   Ввести адрес ячейки В3.

§   Курсор на стрелку.

§   Ввести целое.

§  Повторить ввод требования целочисленности для всех целочисленных переменных.

Ø После окончания ввода требований целочисленности вместо Добавить… нажать кнопку ОК.

           На экране: диалоговое окно Поиск решения с введенными условиями.

       3. Параметры…

           На экране: диалоговое окно «Параметры поиска решения»

         4. Установить флажок Линейная модель, что обеспечивает применение симплекс-метода.

        5. ОК.

            На экране: диалоговое окно «Поиск решения».

        6. Выполнить.

           На экране: диалоговое окно Результаты поиска решения.

         7. ОК.

        На экране: результат решения (рис. 13).  

             Рисунок 13 – Итоговая таблица с результатом решения 

         3.3.3. Сравнение результатов непрерывного решения и целочисленного решения.

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

                     Рисунок 14 – Сравнительные результаты решений

        3.2.3.1. Построение сравнительных гистограмм оптимальных решений.

        1. Выделить А1:Е3 в Excel-таблице, приведенной на рис. 14.

        2. Выполнить шаги 1-5 Мастера диаграмм (см. [1] и Приложение).

        На экране - гистограмма (рис. 15).

        Построение сравнительной диаграммы значений экстремумов.

        1. Выделить (используя клавишу Ctrl) А1:Е3 и F1:F3 в Excel-таблице, приведенной на рисунке 1.2.3.

         2. Выполнить шаги 1-5 Мастера диаграмм.

На экране - гистограмма (рис. 16).

             Рисунок 15 – Гистограмма сравнения оптимальных решений

                  Рисунок 16 - Гистограмма сравнения экстремумов

        Из диаграмм видно, что в целочисленном решении увеличилось значение Прод1, уменьшилось значение Прод3 и появился выпуск продукции Прод4 . Целевая функция в целочисленном решении уменьшилась. Это показывает, что требование целочисленности, как и любое дополнительное требование, ухудшает экстремальное значение целевой функции.

         4 Задание к лабораторной работе «Реализация методов оптимизации и математического программирования».

          В процессе выполнения лабораторной работы должны быть решены две задачи:

          - задача линейного программирования;

          - задача целочисленного линейного программирования.

Информационные технологии решения этих задач рассмотрены в примерах.

          4.1. Задание к лабораторной работе «задача линейного программирования».

В лабораторной работе «Решение задач линейного программирования» используется стандартная форма постановки задачи линейного программирования (ЛП), имеющая в матричной форме следующий вид :

z(X) = CX ® max

AX£B ,                                                   (2)                                                          

X³ 0 .                

Такую постановку имеет пример задачи ЛП, рассматриваемый в п. 1.1.1. раздела 1 настоящего практикума.

 Варианты задач ЛП сведены в таблицу 4.1. В этой таблице указан номер варианта, вид целевой функции z(X) и вид функциональных ограничений AX£B.

 Таблица 4.1 – Варианты заданий задачи ЛП

№ вари анта

Z(X)

         Ограничения

   1

  3x1 + 4x2 + 3x3 + x4

 2x1+4x2+3x3+8x4 £ 12

 5x1+2x2+3x3+6x4 £10

 4x1+6x2+4x3+3x4 £30

2

  2x1 + 3x2 + 4x3 + 5x4

x1+3x2 +4x3+6x4 £ 19

 3x1+2x2+2x3+5x4 £ 13

 2x1 + x2+5x3 +7x4 £ 26

3

  2x1 + 2x2 + x3 + x4

 5x1+5x2+2x3+4x4 £ 14

 3x1+6x2+2x3+5x4 £ 11

 2x1+7x2 +5x3+3x4 £ 25

4

  2x1 + 3x2 + 4x3 +5x4

x1+x2 +2x3 +2x4 £ 15

 2x1 +2x2 + x3 £ 13

 2x1 +6x2 + x3 + 2x4£ 27

5

  3x1 + 4x2 + 3x3 + x4

 2x1 + 4x3 + 5x4 £ 10

 7x1+ 2x2 +3x3 +7x4 £ 9

 5x1+6x2+4x3 +3x4 £ 15

6

   2x1 + 2x2 + x3 + x4

 5x1+6x2 +2x3+4x4  £ 18

 3x1+3x2+2x3 +5x4 £ 15

 2x1+4x2+5x3 +3x4 £ 33

7

  2x1 + 3x2 + 4x3 + 5x4

x1 + x2+2x3+2x4£ 10

 2x1 +2x2 +x3 +3x4 £ 8

 2x1 +6x2 +2x3+3x4 £ 14

8

  3x1 + 4x2 +3x3 + x4

 2x1 + 4x3 + 5x4 £ 10

 7x1+2x2 +3x3 +7x4 £ 9

 5x1+6x2+4x3+3x4 £ 16

9

   2x1 + 2x2 + x3 + x4

 5x1+6x2+2x3+4x4 £ 18

 3x1+3x2+2x3 +5x4 £ 13

 2x1+4x2 +7x3+8x4 £ 28

10

  2x1 + 3x2 + 4x3 +5x4

x1+x2+3x3+4x4 £ 10

 2x1+2x2+4x3+3x4 £ 8

 2x1+6x2+3x3 +5x4 £ 14

11

  3x1 + 4x2 + 3x3 + x4

 2x1 +3x3 + 5x4  £ 10

 7x1+2x2+3x3 +7x4 £ 9

 5x1 +6x2+3x3+3x4 £ 15

12

  2x1 + 2x2 + x3 + x4

 5x1+5x2+2x3+4x4 £ 14

 3x1+6x2+2x3+5x4 £ 12

 2x1+7x2+5x3+3x4 £ 25

    13

  2x1 + 3x2 + 4x3 + 5x4

  x1+x2+2x3+2x4 £ 10

 2x1+2x2+ x3 £ 13

 2x1+6x2+x3 +2x4 £ 23

    14

   x1 +2x2 + 3x3+ 4x4

 5x1+4x2+2x3+3x4 £ 13

 4x1+3x2+5x3+3x4 £ 10

 5x1+4x2+4x3+8x4 £ 19

    15

 2x1 + 2x2 + 3x3 + 4x4

 5x1+4x2+2x3+5x4 £ 13

 4x1+3x2+5x3+7x4 £ 21

 5x1+4x2+3x3+6x4 £ 22

    16

 4x1 + 3x2 + 2x3 + x4

10x1+3x2+5x3+3x4  £ 16

 8x1+7x2+6x3+5x4  £ 20

 7x1+5x2+5x3+2x4 £ 18

  17

   x1 + x2 + x3 + x4

 5x1+4x2+2x3+5x4 £ 13

 4x1+3x2+5x3+7x4 £ 20

 5x1+4x2+3x3+9x4 £ 25

  18

   x1 +2x2 + 3x3+ 4x4

 5x1+4x2+2x3+3x4 £ 8

 4x1+3x2+5x3+3x4 £ 13

 5x1+4x2+4x3+8x4 £ 13

  19

 2x1 + 2x2 + 3x3 + 4x4

 5x1+4x2+2x3+5x4 £ 13

 4x1+3x2+5x3+7x4 £ 27

 5x1+4x2+3x3+6x4 £ 22

  20                  

 4x1 + 3x2 + 2x3 + x4

10x1+3x2+5x3+3x4  £ 15

 8x1+7x2+6x3+5x4  £ 20

 7x1+5x2+5x3+2x4 £ 18

  21    

  x1 + x2 + x3 + x4

 5x1+4x2+2x3+5x4 £ 12

 4x1+3x2+5x3+7x4 £ 18

 5x1+4x2+3x3+9x4 £ 25

  22

  x1 +2x2 + 3x3+ 4x4

 5x1+4x2+2x3+3x4 £ 15

 4x1+3x2+5x3+5x4 £ 23

 5x1+4x2+3x3+8x4 £ 20

  23

 2x1 + 2x2 + 3x3 + 4x4

 5x1+4x2+2x3+5x4 £ 20

 4x1+3x2+5x3+7x4 £ 27

 5x1+4x2+3x3+6x4 £ 22

  24

   x1 + x2 + x3 + x4

 3x1+2x2+3x3+5x4 £ 17

 3x1+3x2+ x3+ x4 £ 18

 5x1+4x2+3x3+6x4 £ 28

  25

  x1 +2x2 + 3x3+ 4x4

 5x1+4x2+2x3+10x4 £ 13

 4x1+3x2+5x3+8x4 £ 18

 5x1+4x2+3x3+9x4 £ 20

  26    

 2x1 + 2x2 + 3x3 + 4x4

 5x1+4x2+5x3+5x4 £ 20

 4x1+3x2+4x3+7x4 £ 27

 5x1+4x2+5x3+6x4 £ 25

  27  

  4x1 + 3x2 + 2x3 + x4

10x1+8x2+5x3+5x4  £ 20

 9x1+9x2+6x3+2x4  £ 17

 8x1+5x2+3x3+ x4 £ 15

4.2. Задание к лабораторной работе ”Решение задачи целочисленного программирования”

При выполнении данной лабораторной работы следует руководствоваться положениями, содержащимися в подразделе 3.1..

         В данном подразделе используются постановки задачи, приведенные в подразделе 4.1, сведенные в таблицу 4.1. К имеющейся постановке (1) добавляется требованиецелочисленности всех переменных:

xj– целое,  ( j =1, …, 4) .                                      (3)

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

5 Содержание отчета по лабораторной работе

            Содержание отчета по лабораторной работе должно включать отчеты по решению двух задач.

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

            В отчете по лабораторной работе «Задача линейного программирования» должны быть приведены:

-    содержательная постановка задачи:

-    математическая постановка задачи (математическая модель задачи оптимизации);

-    фрагмент электронной таблицы, приводимый на рисунке 10;

-    встроенная диаграмма (гистограмма), отражающая оптимальный план;

-    решение задачи, включающее оптимальный план и значение экстремума

     В отчете по лабораторной работе «Решение задачи целочисленного программирования» должна быть приведены:

 -   содержательная постановка задачи;

-    математическая постановка задачи;

-    фрагмент электронной таблицы, приводимый на рисунке 13;

-    встроенная диаграмма, отражающая оптимальный план;

-    решение задачи (точка экстремума и значение оптимума);

-    сравнительная диаграмма (гистограмма) для значений переменных оптимизации, соответствующих непрерывному и целочисленному решению;

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

Список использованных источников

         1. Дробязко О.Н. Оптимизация в САПР, часть 1: Учеб пособие/Алт. гос. техн. ун-т им. И.И. Ползунова.–Барнаул, 2008.-70 с. 

        3. Курицкий, Б.Я. Поиск оптимальных решений средствами Excel 7.0 [Текст ]/Б.Я Курицкий. -СПб.: BHV – Санкт-Петербург, 1997.-384 с.

  4. Дробязко О.Н. Лабораторный практикум по дисциплине “Оптимизация в САПР” / Алт. гос. техн. ун-т им. И.И. Ползунова. - Барнаул: Изд-во АлтГТУ, 2000.-72 с.

       5. Рудикова, Л.В. MicrosoftExcel для студента [ Текст]/Л.В. Рудикова . – СПб.: БХВ-Петербург, 2005. –368 с.:

       6. Волков, В.Б. Понятный самоучитель Excel 2010 [Текст]/ В.Б. Волков. –СПб.: Питер, 2010. –256 с.

ПРИЛОЖЕНИЯ

                                            Приложение А

Методические указания по построению диаграмм

                Рассмотрим компоненты диаграммы MS Excel (рисунок А1) .

Рисунок А1 - Элементы диаграммы MSExcel

Для создания диаграммы в MS Excel нужно подготовить данные для построения диаграммы и определить ее тип.

        Важными понятиями при построении диаграммы являются понятия «ряд данных» и «категория». Эти понятия логически связываются с числом строк и столбцов в таблице исходных данных диаграммы.

При этом необходимо учитывать следующее:

       1. MS Excel предполагает, что количество рядов данных (Y) должно быть меньше, чем число категорий (X). Исходя из этого, определяется расположение рядов (в строках или столбцах), а также снабжены ли ряды и категории именами.

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

        3. Если диапазон ячеек имеет больше строк, чем столбцов, то рядами данных считаются столбцы.

        4. MS Excel предполагается, что названия, связанные с рядами данных, считаются их именами и составляют легенду диаграммы . Данные интерпретируемые как категории, считаются названиями категорий и выводятся вдоль оси X.

        5. Если в ячейках, которые MS Excel будет использовать как названия категорий, содержатся числа (не текст и не даты), то MS Excel предполагает, что в этих ячейках содержится ряд данных, и строит диаграмму без меток по оси категорий (X), вместо этого нумеруя категории.

        6. Если в ячейках, которые MS Excel намерен использовать как названия рядов, содержатся числа (не текст и не даты), то MS Excel предполагает, что в этих ячейках содержатся первые точки рядов данных, а в каждом ряду данных присваивается имя Ряд1, Ряд2 и т.д. 

         Типы диаграмм

         Основные типы диаграмм MS Excel приведены в таблице А1.

 Таблица А1 – Типы диаграмм

Название

Описание

Стандартные типы диаграмм

Гистограммы

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

Линейчатые диаграммы

Похожи на гистограммы (отличие – повернуты на 90° по часовой стрелке0. Используются для сопоставления отдельных значений в определенный момент времени. Не дают представления об изменении объектов во времени. Горизонтальное расположение полос позволяет подчеркнуть положительные или отрицательные отклонения от некоторой величины. Линейчатые диаграммы можно использовать ля отображения отклонений по разным статьям бюджета в определенный момент времени. Точки можно перетаскивать в любое положение.

Графики

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

Круговые диаграммы

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

Точечные диаграммы

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

Диаграммы

с областями

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

Кольцевые диаграммы

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

Лепестковые диаграммы

Используют обычно, чтобы показать соотношения отдельных рядов , а также – одного определенного ряда данных и всех остальных рядов. Каждая категория лепестковой диаграммы имеет собственную ось координат (луч). Точки данных располагаются вдоль луча . Линии, соединяющие точки данных одного ряда, охватывают площадь, характеризующую совокупность значений в этом ряду. На лепестковой диаграмме можно отобразить, например, динамику затрат времени на проект, включающий несколько задач. В этом случае каждой категории (лучу) соответствует определенная задача проекта, а точке на луче- затраты времени на нее к какому-то сроку. .

Поверхность

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

Пузырьковые диаграммы

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

Биржевая

Используется для отображения изменений информации о ценах на бирже. Отображает наборы данных из трех значений.

Цилиндрические и др.

Являются объемными вариантами гистограмм и линейчатых диаграмм

Нестандартные типы диаграмм

Нестандартные типы основаны на стандартных, но имеют некоторые улучшения в форматировании и отображении

Пользовательские форматы диаграмм

Добавляются в список дополнительных типов диаграмм. Пользовательские форматы создаются на основе базовых с применением различных средств форматирования

Приложение Б

             Описание некоторых финансовых функций

 1. Определение будущей стоимости вклада (займа)

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

1.1. Расчеты на основе постоянной процентной ставки.

Функция БС

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

Синтаксис: БС(ставка,кпер,плт,[пс],[тип])

Функция БС имеет аргументы, указанные ниже.

Ставка –обязательный аргумент. Процентная ставка за период. Например, если вы взяли ссуду под 10% годовых и делаете ежемесячные выплаты, то процентная ставка за месяц составляет 10%/12 или 0,83%. В качестве значения аргумента можно набирать 0,83% или 0,0083;

 Кпер –обязательный аргумент. Общее количество периодов платежей по аннуитету. Например, если вы получили ссуду на 4 года и делаете ежемесячные платежи, то ваша ссуда имеет 4*12, или 48, периодов выплат;

 Плт – обязательный аргумент. Выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент "плт" состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент "пс" является обязательным.

 Пс – обязательный аргумент. Приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент "пс" опущен, предполагается значение 0. В этом случае аргумент "плт" является обязательным.

 Тип – необязательный аргумент. Число 0 или 1, обозначающее срок выплаты. Если аргумент "тип" опущен, предполагается значение 0.

          Замечания

·  Убедитесь, что единицы измерения аргументов "ставка" и "кпер" используются согласованно. При ежемесячных выплатах по четырехгодичному займу из расчета 12 процентов годовых используйте значение 12%/12 в качестве аргумента "ставка" и 4*12 — в качестве аргумента "кпер". При ежегодных платежах по тому же займу используйте значение 12% в качестве аргумента "ставка" и 4 — в качестве аргумента "кпер".

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

   Для вычисления будущего значения единой суммы используется функция БС с аргументами ставка, кпер, пс: БС(ставка; кпер; пс)

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

Для расчета будущей стоимости серии фиксированных периодических платежей, если они вносятся в конце периода (так называемые обычные платежи, или постнумерандо), используется формула: БС(ставка; кпер; плт; ; 0)

Пример.

Рассчитать какая сумма окажется на счете, если 27 рублей положены на 33 года под 13,5% годовых. Проценты начисляются каждые полгода.

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

Выберем в окне Мастер функций позицию Финансовые функции и в приведенном списке выберем функцию БС.

Вызовем окно Аргументы функции и введем значения этих аргументов (рисунок 1).

Рисунок 1 - Вид окна Аргументы функции  

Укажем вводимые значения аргументов:

ставка = 13,5%/2  ,

кпер = 33*2 ,

пс = - 27 .

Формула имеет вид: =БС(0,135/2;33*2;;-27)

то есть, используя финансовые функции Excel имеем:2 012,07 р.

2 . Определение срока платежа и процентной ставки

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

К таким величинам относят:

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

2.    значение постоянной процентной ставки за один период для серии фиксированных периодических платежей (значение ставки процента по вкладу или займу).

2.1. Расчет срока платежа

Функция КПЕР

Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

Синтаксис: КПЕР(ставка, плт, пс,[бс],[тип])

       Аргументы:

Ставка – обязательный аргумент. Процентная ставка за период.

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

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

Бс – необязательный аргумент. Значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент "бс" опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).

Тип – необязательный аргумент. Число 0 или 1, обозначающее, когда должна производиться выплата.

2.2. Расчет процентной ставки.

Функция СТАВКА

         Возвращает процентную ставку по аннуитету за один период.

Функция СТАВКА вычисляется путем итераций и может давать нулевое значение или несколько значений. Если последовательные результаты функции СТАВКА не сходятся с точностью 0,0000001 после 20 итераций, функция СТАВКА возвращает сообщение об ошибке #ЧИСЛО!.

      Синтаксис:  СТАВКА(Кпер, Плт, пс, [бс], [тип], [прогноз])

Аргументы (Аргумент. Значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.):

Кпер  Обязательный. Общее число периодов платежей для ежегодного платежа.

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

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

Бс  Необязательный. Значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент "бс" опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).

Тип  Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.

Прогноз  Необязательный. Предполагаемая величина ставки. Если аргумент «прогноз»опущен, предполагается, что его значение равно 10 %.

           Замечания

Если функция СТАВКА не сходится, попробуйте изменить значение аргумента «прогноз». Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.

Убедитесь, что единицы измерения, выбранные для аргументов «прогноз» и «кол_пер» соответствуют друг другу. При ежемесячных выплатах по четырехгодичному займу под 12 процентов годовых используйте значение 12%/12 для аргумента «прогноз» и 4*12 — для аргумента «кол_пер». При ежегодных платежах по тому же займу используйте значение 12% для аргумента «прогноз» и 4 —для аргумента «кол_пер».