Реализация в электронных таблицах информационных технологий в управление и экономике

ФЕДЕРАЛЬНОЕ АГЕНСТВО ПО ОБРАЗОВАНИЮ

Государственное образовательное  учреждение высшего  профессионального  образования 

Северо – Западный государственный заочный технический университет 
 

Кафедра информатики и  прикладной математики 
 
 

КУРСОВАЯ  РАБОТА

ПО

ИНФОРМАТИКЕ 
 

                                 Тема: Реализация в электронных таблицах

                                 информационных технологий  в управление и  экономике. 
 

Выполнила студентка:

Институт: ИУП и  ИП

Курс: первый

Специальность: 080502.65 (а)

Шифр:  

Проверил преподаватель: 

Оценка: 

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

Дата: 

Санкт-Петербург

2009

Оглавление

1. ОЦЕНКА ИНВЕСТИЦИОННЫХ ПРОЕКТОВ (ИП) 3

1.1 Задание 3

1.2 Основные параметры для оценки инвестиционных проектов 3

1.3 Аналитические расчеты 4

1.4 Функции электронных таблиц (ЭТ) для оценки ИП 6

1.5 Оценка ИП в ЭТ 7

1.6 Выбор ИП 8

2. ОПТИМИЗАЦИЯ УПРАВЛЕНЧЕСКИХ РЕШЕНИЙ 9

2.1 Задание 9

2.2 Построение математической модели 9

2.3 Построение начального плана решения 10

2.4 оптимизация решения 14

3. ЗАДАЧА ПЛАНИРОВАНИЯ ВЫПУСКА ПРОДУКЦИИ 16

3.1 Задание 16

3.2 Построение балансовой модели 16

3.3 Решение задачи в ЭТ 18 
 
 
 
 
 
 
 
 
 
 
 
 
 

1. ОЦЕНКА  ИНВЕСТИЦИОННЫХ ПРОЕКТОВ (ИП)

1. Задание

     Предполагаются  три инвестиционных проекта, сроком на 5 лет. Инвестиционные вложения по этим проектам составляют 2 000 рублей. Предполагаемые денежные потоки показаны в табл. 1.

                 Таблица 1

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

2. Основные  параметры для  оценки инвестиционных проектов

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

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

     Например, фирме предлагают проект, требующий  вложения 4 млн. руб. По прогнозам через 2 года возврат должен составить 1 млн. руб., через 3 года – 4 млн. руб., а через 4 года – 2 млн. руб. Банковский процент равен 15%. Выгодно ли такое вложение денег?

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

1. Чистый  приведенный доход (NVP) рассчитывается по формуле

,                                                                (1)

где P_k – сумма денежных поступлений за период K;

       IC – сумма первоначального вложения;

       r – процентная ставка (коэффициент дисконтирования);

       n – количество лет, в течение которых будут приходить денежные поступления.

Экономический смысл NVP:

Если NVP > 0, то проект прибыльный;

          NVP < 0, то проект убыточный;

          NVP = 0, то проект ни прибыльный, ни убыточный.

2. Индекс рентабельности (PI) рассчитывается по формуле:

                                                                     (2)

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

Экономический смысл PI:

Если PI > 1, то проект рентабельный;

          PI < 1, то проект нерентабельный.

3. Внутренняя норма прибыли инвестиции (IRR) представляет собой процентную ставку, при которой NVP = 0. Таким образом, IRR находится из уравнения

.                                                                (3)

Экономический смысл IRR:

Если IRR > CC, то проект следует принять;

          IRR < CC, то проект следует отвергнуть;

          IRR = CC, то проект ни прибыльный, ни убыточный.

4. Срок окупаемости инвестиций (PP) обычно рассчитывается прямым подсчетом числа лет, в течение которых поступающие денежные потоки превысят сумму первоначальных вложений. Общая формула расчета PP имеет вид

.                                             (4)

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

     В более упрощенном виде, наиболее выгодным является проект, у которого первые три показателя (NVP, PI, IRR) наибольшие, а последний (PP) наименьший.

3. Аналитические расчеты

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

     Доход от проекта А:

D_A = 1 000 + 1 000 + 1 500 + 500 + 1  300 – 2 000 = 3 300 (рублей).

     Доход от проекта В:

D_B = 1 500 + 1 000 + 1 200 + 1 000 + 500 – 2 000 = 3 200 (рублей).

     Доход от проекта С:

D_C = 500 + 1 000 + 1 000 + 2 000 + 2 500 – 2 000 = 5 000 (рублей).

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

     Для проекта А:

     P₁ = 1 000, P₂ = 1 000, P₃  = 1 500, P₄  = 500, P₅  = 1 300, n = 5, r = 0,20, IC = 2 000 

     = 833,33 + 694,44 + 868,06 + 241,13 + 522,44 – 2 000 = 1 159,40

     Итак, за счет неравномерности поступления  средств проект A принесет прибыль не 3 300 руб., а 1 159,40 руб.

     Для проекта В:

     P₁ = 1 500, P₂ = 1 000, P₃  = 1 200, P₄  = 1 000, P₅  = 500, n = 5, r = 0,20, IC = 2 000 

     = 1 250 + 694,44 + 694,44 + 482,26 + 200,94 – 2 000 = 1 322,08

     Итак, проект B принесет прибыль не 3 200 руб., как рассчитано в п. 1.3.1, а 1 322,08 руб.

     Для проекта С:

     P₁ = 500, P₂ = 1 000, P₃  = 1 000, P₄  = 2 000, P₅  = 2 500, n = 5, r = 0,20, IC = 2 000 

     = 416,67 + 694,44 + 578,70 + 964,51 + 1 004,69 – 2 000 = 1 659,01

     Проект С принес прибыль не 5 000 руб., как рассчитано в п. 1.3.1, а 1 659,01 руб.

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

1.3.3     Рассчитаем индекс рентабельности PI по формуле (2).

     Для проекта А: 

     Для проекта В: 

     Для проекта С: 

     Очевидно, что вновь следует отдать предпочтение проекту С (PI_A = 1,5797 < PI_В = 1,66104 < PI_C = 1,829505).

1.3.4     Рассчитаем срок окупаемости проектов PP по формуле (4)

Для проекта А он равен 2 года:

1 000  + 1 000 = 2 000

Для проекта В он равен 2 года:

1 500  + 1 000 = 2 500 > 2 000

Для проекта C он равен 3 года:

500  + 1 000 +1 000 = 2 500 >2 000

Вывод.

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

4. Функции электронных таблиц (ЭТ) для оценки ИП

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

     В Excel Функция ЧПС (чистая приведенная стоимость) используется для оценки чистого приведенного дохода NVP и имеет синтаксис:

     =ЧПС  (Ставка; Значение).

       В программе OpenOffice.org Cale для оценки чистого приведенного дохода используется функция NVP и имеет синтаксис:

     =NVP (Процент; Значение).

В этих функциях аргументы:

     ставка (процент) – процентная ставка;

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

     Функция ВСД (внутренняя ставка доходности) используется в Excel для расчета внутренней доходности и имеет синтаксис:

     =ВСД  (Значение; Предположение).

     В программе  OpenOffice.org Cale для расчета внутренней доходности используется функция IRR. Она имеет синтаксис:

     =IRR (Значение; Предположение).

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

5. Оценка  ИП в ЭТ

     Создаем ЭТ (табл.2 – режим показа формул, табл. 3 – режим показа вычислений). Для перехода к показу формул необходимо выполнить команды: Сервис – Параметры – Формулы. Для отображения при печати выполняем команды: Файл – Параметры страницы – Лист – сетка – имена строк и столбцов. 

Таблица 2. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Таблица 3. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

6. Выбор ИП

     Так как проект С принесет больший чистый приведенный доход и больший индекс рентабельности, отдается предпочтение проекту С.

2. ОПТИМИЗАЦИЯ УПРАВЛЕНЧЕСКИХ РЕШЕНИЙ

1. Задание

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

     Составить план перевозок так, чтобы затраты  были минимальными.

     Таблица 4.

2. Построение  математической модели

     Обозначим

     X_ij – количество продукции, отправляемой со склада i в  магазин j;

     C_ij – стоимость перевозки единицы продукции со склада i в магазин j.

     2.2.1 Определение ограничений

     Математическая  модель будет состоять из ряда ограничений:

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

     X_ij ≥ 0; C_ij ≥ 0.                                                                                 (5)

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

                                                                  (6)

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

                                                                           (7)

     2.2.2 Определение целевой  функции

     Общая стоимость перевозок (целевая функция) равна

                    (8)

     Необходимо  определить такие неотрицательные  значения переменных Х, которые удовлетворяют условиям (5), (6) и (7) и обращают в минимум целевую функцию Z (8). В такой постановке задача является транспортной задачей линейного программирования.

     2.2.3 Проверка баланса

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

      ,                                                                                   (9)

где - суммарное количество продукции на складах (при этом – количество продукции на одном складе, i = 1, 2, 3);

        - суммарное количество продукции, требуемой в магазинах (причем – количество продукции, которое требуется j-ому магазину, j = 1, 2, 3, 4).

В нашем  случае 
 

следовательно, задача с балансом.

3. Построение  начального плана  решения

     Построим  начальный план решения в ЭТ. Начальный  план в режиме показа формул представлен  в таблице 5, а в режиме вычислений – в таблице 6.

1. Подготовим блок ячеек с исходными данными.

     В ячейках В4:В7 помещаем сведения о наличии продукции на складах. В ячейках С9:F9 – сведения о потребностях магазинов. В ячейках С5:F7 – данные о стоимости перевозок единицы продукции со складов в магазины.

2. Построим начальный план перевозок.

     Считаем, что с каждого склада в каждый магазин везут по 1 единицы товара (ячейки С11:F13 заполним единицами).

3. Вычислим количество перевозимой продукции.

а) В ячейку В11 введем формулу для вычисления количества продукции, вывозимой со склада:

= СУММ(С11:F11).

     Аналогично  в ячейки В12, В13 введем формулы для  вычисления количества продукции, вывозимой  со второго и третьего складов:

= СУММ(С12:F12);

= СУММ(С13:F13).

     Для начального плана перевозок все  суммы равны 4.

     Очевидно, что при работе с программой OpenOffice.org Cale в ячейках В11:В13 будет использована функция SUM.

б) В ячейку С15 введем формулу для вычисления количества продукции, которую везем в первый магазин:

=СУММ(С11;С13).

     Аналогично  в ячейки D15:F15 введем формулы для вычисления количества продукции, которую везем во 2-й, 3-й и 4-й магазины.

В ячейку D15                                       = СУММ(D11:D13).

В ячейку E15                                       = СУММ(E11:E13).

В ячейку F15                                         = СУММ(F11:F13).

     При работе с программой OpenOffice.org Cale в ячейках C11:F13 будет использована функция SUM.

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

     Для определения стоимости перевозок  в 1-й магазин

     Z₁ = X_1.1C_1.1 + X_1.2C_1.2+Х₁₃C_1.3

введем в  ячейку С16 формулу

     =СУММПРОИЗВ(С5:С7;С11:С13).

     В ячейке D16 вычислим стоимость перевозок во 2-й магазин

     Z₂ = X_1.2C_1.2 + X_2.2C_2.2+Х_3.2C_3.2, т. е. введем формулу

     =СУММПРОИЗВ(D5:D7;D11:D13).

     Аналогично  в ячейке Е16:F16 введем формулы для вычисления стоимости перевозок в остальные магазины.

     В ячейку Е16  =СУММПРОИЗВ(E5:E7;E11:E13).

     В ячейку F16  =СУММПРОИЗВ(F5:F7;F11:F13).

     При работе с программой OpenOffice.org Cale в ячейках С16:G16 будет использована функция SUMPRODUCT.

5. Определим общую стоимость перевозок (целевую функцию ЦФ):

Z = Z₁ + Z₂ + Z₃ +Z₄.                                                                   (10)

Для этого  введем в ячейку В17 формулу

=СУММ(С16:F16).

При работе с программой OpenOffice.org Cale в ячейку В17 вводим функцию SUM.

     Для нашего начального плана получиться стоимость, равная 113 денежным единицам. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

     Таблица 5. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

     Таблица 6. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

4. оптимизация решения

     Используем  режим Поиск решения Excel.

1. После выполнения команд Сервис - Поиск решения открывается диалоговое окно Поиск решения (рис1).
2. Введем данные:

Установить целевую  ячейку

                                                                  Рис. 1

Равной:           минимальному значению

3. Щелкнуть по кнопке Добавить для ввода ограничений.
4. В открывшемся окне Добавление ограничений (рис.2) ввести ограничения.

C11:F13>=0,

C11:F13 = целое,

B11:B13 >= B5:B7,

C9:F9 = C15:F15.

     Левая часть каждого ограничения вводится в поле Ссылка на ячейку, правая часть ограничения – в поле Ограничения, знак выбирается в средней части окна. После ввода каждого ограничения нужно нажать кнопку Добавить, после ввода последнего ограничения нажать кнопку Ок.

                                                                Рис. 2

5. Для запуска режима Поиск решения щелкнуть по кнопке Выполнить (см. рис.1). Появиться окно с сообщением о том, что решение найдено. Щелкнув по кнопке Ок, получаем решение (табл. 7).

Таблица 7. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

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

3. ЗАДАЧА  ПЛАНИРОВАНИЯ ВЫПУСКА  ПРОДУКЦИИ

1. Задание

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

     Таблица 8.