Контрольная работа по «Информационным системам в экономике»

 

 

    В столбец А вносим исходные данные, в столбец В момент открытия, дни поступления и дата погашения. Столбец В должен иметь формат ячеек  «Дата». Установить с помощью последовательности «Формат» - «Ячейки» - «Дата»

   В столбце С для определения кол-ва дней между поступлением платежей использовать функцию «ДНЕЙ360»  категории «Дата и время» мастера функций.

    В столбец D вносятся суммы внесенных платежей соответственно датам погашения в столбце В.

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

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

Порядок тиражирования формул в  таблице производится по строкам. При  этом вычисляем значение в ячейке H3, потом набираем формулу в ячейку G4, затем F4  и E4. Затем поочередно тиражируем указанные формулы на строку с номером 5 и т.д. Таким образом формула в ячейке Е4 выполняется расчет по:

    условие F3<H3 (если накопленные платежи на 13.02 были меньше % на эту дату);

    если это условие справедливо, то в ячейку Е4 заносится сумма ячеек Е3+С4, т.е. количество дней от последнего списания увеличивается;

    если это условие не выполняется, то в ячейку Е4 заносится С4 означает, что зачет в предыдущий платеж был и ячейка Е4= С4.

         Обращая внимание к данным рассчитанным в таблице в ячейке G11 мы видим сумму основного долга на момент погашения кредита 10 сентября, а в ячейке Н11 сумму процентов на остаток основного долга. Итоговая сумма остаток долга - это сумма результатов ячеек G11+Н11 она равна 44440 рублей 78 копеек.

   По данным столбца G  с помощью МАСТЕР ДИАГРАММ строится

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

 

 

 

 

 

 

 

 

 

 

Тема 3. Распределение инвестиций.

 

 

1. Сущность задачи

 

1. Наименование задачи: Распределение инвестиций.

 

2. Цель решения задачи: при помощи средств MS Excel научиться составлять оптимальный план распределения инвестиций по проектам и определять границы эффективности проектов.

 

3. Путь решения задачи:

1.    Составить модель линейного программирования.

2. Используя средство «ПОИСК РЕШЕНИЯ» в «EXCEL» найти оптимальный план распределение капитала по проектам.

3.  Найти границы эффективности проектов, при которых вложения в проект А меняется на вложения в проект В  и наоборот, (т.е. начиная с какой прибыли копеек на рубль менее эффективный проект становится более эффективным)

4.    Кратко описать действия в EXCEL.

   

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

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

 

4.  Периодичность  решения задачи: ежегодно в течение 4 лет.

 

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

 

2. Описание  исходных данных

 

 

Денежные средства 8000 рублей могут быть использованы для финансирования 2-х проектов А и В. Период инвестиций в проект А кратен 1 году, а в проект В – 2 годам. Известно, что проект А гарантирует прибыль на вложенный рубль 60 копеек, а проект В – 130 копеек. Как следует распорядиться данным капиталом, чтобы через 4 года капитал был максимальным?

 

3. Описание  алгоритма решения задачи

 

Составляем модель линейного  программирования проект A гарантирует 60 коп, а проект B - 130 коп. на рубль прибыли и имеется 8000 руб.

 

1,6*X4A +2,3*X3B ---à MAX                         целевая функция

 

X1A + X1B<= 8000                                     ограничение на начало 1 года

X2A+X2B<=1,6*X1A                                 ограничение на начало 2 года

X3A+X3B<=1,6*X2A + 2,3*X1B            ограничение на начало 3 года

X4A+X4B<=1,6*X3A+ 2,3*X2B             ограничение на начало 4 года

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

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

 

1		Переменные
2		X1A	X1B		X2A	X2B	X3A		X3B		X4A		X4B
3	значение	8 000,00	0,00		12 800,00	0,00	20 480,00		0,00		32 768,00		0,00	ЦФ
4	коэф ЦФ	0	0		0	0	0		2,3		1,6		0	52428,8
5		Ограничения												лев часть	знак	правая часть
6	1-й год	1		1	0	0	0	0		0		0		8000	<=	8000
7	2-й год	-1,6		0	1	1	0	0		0		0		0	<=	0
8	3-й год	0		-2,3	-1,6	0	1	1		0		0		0	<=	0
9	4-й год	0		0	0	-2,3	-1,6	0		1		1		0	<=	0

 

 

В ячейке J4 используя мастер функций категория "Математические" функция "сумма произведений" записываем формулу:         =СУММПРОИЗВ(B$3:I$3;B4:I4), где строка 3 со значениями ячеек с В3 по I3 фиксируется знаками "$", как неизменное значение при использовании свойства программы "тиражирование формул". Далее копируем эту формулу в ячейки J6, J7, J8, J9 и получаем формулы:  

в ячейке J6 формула:=СУММПРОИЗВ(B$3:I$3;B6:I6)

в ячейке J7 формула: =СУММПРОИЗВ(B$3:I$3;B7:I7)

в ячейке J8 формула: =СУММПРОИЗВ(B$3:I$3;B8:I8)

в ячейке J9 формула: =СУММПРОИЗВ(B$3:I$3;B9:I9)

После заполнения таблицы  данными вызывается «СЕРВИС» -> «ПОИСК РЕШЕНИЯ»

В поле «установить целевую ячейку» внести адрес   «$J$4»

В поле «изменяя ячейки»  внести адреса   «B$3:I$3»

Курсор в поле «добавить». Появится диалоговое окно «Добавление ограничения», в поле «ссылка на ячейку»  ввести адрес «$J$6». Курсор в правое окно «ограничение» и ввести адрес «$L$6», на кнопку «добавить». На экране опять появится диалоговое окно «Добавление ограничения» и аналогично ввести другие ограничения вниз по столбцам J и L. После ввода последнего ограничения ввести «ОК» и переходим в окно “Поиск решения” в окне «Ограничения» появятся неравенства, показывающие, что левая часть неравенств меньше либо равна правой части, т.е.

$J$6 <= $L$6

$J$7 <= $L$7

$J$8 <= $L$8

$J$9 <= $L$9

Нажимаем на кнопку «Параметры» и щелкаем левой клавишей мыши в окнах «Линейная модель» и «Неотрицательные значения», затем кнопку

«ОК» из окна «Параметры поиска решения» переходим в окно «Поиск решения» и щелкаем левой клавишей мыши  на «Выполнить» и на экране окно «Результаты поиска решения».

 

По результатам решения таблица заполняется автоматически по строке "значения" и столбцу J и имеет следующий вид

 

По полученным результатам  можно сделать следующие выводы. Эффективнее использовать проект В, увеличение капитала на третий год вложения инвестиций с 25000 рублей до 35000 рублей (ячейка G3), а через 4 года капитал максимально увеличится до суммы 49000 рублей (ячейка J4). Проект А для вложения инвестиций лучше не использовать.

Для определения границы  эффективности проекта А мы последовательно увеличиваем прибыль (копеек на рубль), т.к. он менее эффективен. Для этого меняем начальные данные проекта А в таблице EXCEL и выполняем действия "Сервис"-> "Поиск решения"-> "выполнить". Данные меняем до тех пор пока средства не будут вкладываться в менее эффективный проект А и капитал будет увеличиваться.

 

По результатам решения  можно сделать следующие выводы. При изменении прибыли проекта А с 15 копеек на вложенный рубль до 19 копеек на вложенный рубль, сумма вложенного капитала увеличивается через год с 25000 рублей до 29750 рублей (ячейка D3), через 2 года – 35402 рублей 50 копеек (ячейка F3), через 3 года - 42129 рублей (ячейка H3). Максимальный капитал при использовании проекта А, если гарантированная прибыль будет 19 копеек на вложенный рубль, через 4 года составит 50133 рублей 48 копеек (ячейка J4) .

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Список используемой литературы.

 

1. «Информационные системы в экономике», «Информационные системы в управлении социально-трудовой сферой», «информационные технологии управления», «Информационные системы маркетинга»: Методические указания по выполнению контрольной работы для самостоятельной работы студентов III курса (первое и второе высшее образование) специальностей 080105 (060404) «Финансы и кредит» и 080109 (060500) «Бухгалтерский учет, анализ и аудит», для студентов IV курса специальностей 080104 (060200) «Экономика труда», 080507 (061100) «Менеджмент организации» (первое и второе высшее образование), 0805504 (061000) «ГМУ», 080111 (061500) «Маркетинг» (первое и второе высшее образование). – М.: Вузовский учебник, 2007. 80с.
2. Информационные системы в экономике: Учебное пособие/ Под ред. проф. А.Н. Романова, проф. Б.Е. Одинцова – М.: Вузовский учебник, 2008. - 411с.