Институт управления, экономики и финансов

КАЗАНСКИЙ ФЕДЕРАЛЬНЫЙ УНИВЕРСИТЕТ

ИНСТИТУТ УПРАВЛЕНИЯ, ЭКОНОМИКИ И ФИНАНСОВ

Кафедра экономико-математического моделирования

 

И.А. КОДОЛОВА, Ю.В. СТЕПАНОВА

 

 

Учебно-методическое пособие

по дисциплине "Информационные технологии"

для самостоятельной работы студентов,

обучающихся по направлению 080100.62 "Экономика"

по теме «Информационные технологии создания систем поддержки принятия решений и методы прогнозирования»

 

 

 

Казань 2016

Принято на заседании кафедры экономико-математического моделирования. Протокол № 5 от 25.01. 2016 г.

Рецензент

Кандидат физико-математических наук,

доцент кафедры экономико-математического моделирования Института управления, экономики и финансов КФУ Н.А. Опокина

Кодолова И.А., Степанова Ю.В.

Учебно-методическое пособие по дисциплине "Информационные технологии" для самостоятельной работы студентов, обучающихся по направлению 080100.62 "Экономика" по теме «Информационные технологии создания систем поддержки принятия решений и методы прогнозирования» / И.А. Кодолова, Ю.В. Степанова. – Казань: Казан. ун-т, 2016. – 62 с.

 

Учебно-методическое пособие подготовлено в соответствии с программой дисциплины «Информационные технологии» и поможет более эффективно организовать самостоятельную работу студентов по освоению методов прогнозирования и анализа при решении экономических задач средствами MS Excel.

Настоящее учебно-методическое пособие адресовано студентам, обучающимся по направлению 080100.62 «Экономика».

 

 

© Кодолова И.А., 2016

© Степанова Ю.В., 2016

© Казанский университет, 2016

 

Содержание

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

Требования к выполнению самостоятельной работы ………………………..……..5

1. Элемент информационных технологий создания систем поддержки принятия решений «Диспетчер сценариев»………….…………………………….…….……....5

1.1. Пример применения «Диспетчера сценариев»..……..……….……………..6

1.2. Задание для самостоятельного выполнения № 1. Создание сценариев для получения аналитических отчетов …………………………………………….…….10

2. Элемент информационных технологий создания систем поддержки принятия решений «Подбор параметра» ……………………………………………..….….….12

2.1. Пример применения «Подбора параметра».……………………….…….....12

2.2. Задание для самостоятельного выполнения № 2. Применение

«Подбора параметра» в решении экономической задачи ………………..……..….15

3. Элемент информационных технологий создания систем поддержки принятия решений «Поиск решения»……………. …………………………….……………....16

3.1. Пример применения «Поиска решения».………….……………………..…17

3.2. Задание для самостоятельного выполнения № 3. Нахождение вариантов оптимальной производственной программы по выпуску продукции с использованием «Поиска решения» …………..……………………..………….….22

4. Элемент информационных технологий создания систем поддержки принятия решений «Таблица данных» ……………………………………………………..….52

4.1. Пример применения «Таблиц данных» ……………………………….…...51

4.2. Задание для самостоятельного выполнения № 4. Применение «Таблиц

Данных» в экономических расчетах …………………………………..………...….56

Рекомендуемая литература ……………………………………………………….…59

Приложения ………………………………………………………………………..…60

Введение

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

Учебно-методическое пособие подготовлено в соответствии с программой дисциплины «Информационные технологии» и поможет более эффективно организовать самостоятельную работу студентов по освоению методов прогнозирования и анализа при решении экономических задач средствами MS Excel.

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

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

Требования к выполнению самостоятельной работы

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

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

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

2. Результат работы представляется в электронном формате и в виде компьютерных распечаток в среде MS Excel. Распечатки нумеруются и располагаются в последовательности, указанной в задании.

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

4. Титульный лист оформляется в соответствии с образцом, приведенным в Приложении 1.

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

 

Элемент информационных технологий создания систем

Поддержки принятия решений «Диспетчер сценариев»

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

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

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

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

Пример применения «Диспетчера сценариев»

С помощью «Диспетчера сценариев» необходимо создать два сценария, показывающих, как изменится объем прибыли от реализуемой продукции, если удвоить объем производства и как изменится объем прибыли от реализуемой продукции в случае удвоения цены реализации. Создаваемые сценарии назвать соответственно «Удвоение объема производства» и «Удвоение цены».

Исходные данные представлены на рис. 1.

1. Ярлычок листа рабочей книги назовем «Сценарии».

2. Создадим на листе «Сценарии» таблицу как показано на рис. 1, 2.

Рис.1. Исходные данные. Режим чисел

На рис. 2 показаны формулы, по которым рассчитывается таблица.

Рис. 2. Исходные данные. Режим формул

3. Присвоим соответствующим ячейкам таблицы имена: Цена, Объем, Прибыль (Контекстное меню ячейки – Присвоить имя).

4. Создание и сохранение сценариев.

4.1. Вкладка Данные – группа Работа с данными - кнопка Анализ «что-если» - Диспетчер сценариев. Появится диалоговое окно «Диспетчер сценариев» (рис. 3).

Рис. 3. Диалоговое окно «Диспетчер сценариев»

4.2. Щелкнем по кнопке Добавить, чтобы открыть диалоговое окно «Добавление сценария» (рис. 4).

Рис. 4. Диалоговое окно «Добавление сценария»

4.3. В поле «Название сценария» введем название первого сценария «Удвоение объема».

4.4. В поле «Изменяемые ячейки» введем ссылку на изменяемую ячейку - C6. Если изменяемых ячеек несколько, то можно сразу ввести все изменяемые ячейки. Несколько ссылок отделяются друг от друга точками с запятой. Несмежные ячейки добавляются при нажатой клавише Ctrl.

4.5. В поле «Примечание» укажем, за счет изменения какого параметра найдена прибыль в этом сценарии. Нажмем кнопку ОК.

4.6. Появится диалоговое окно «Значения ячеек сценария», в котором отображаются текущие значения изменяемых ячеек. Введем удвоенное значение объема равное 140. Нажмем кнопку ОК.

4.7. Опять появится окно «Диспетчер сценариев» с именем только что созданного сценария.

Нажмем кнопку Добавить и аналогичным образом построим сценарий «Удвоение цены».

4.8. Закроем окно «Диспетчер сценариев».

5. Создадим итоговый отчет о выполнении сценариев.

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

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

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

Рис. 5. Диалоговое окно «Отчет по сценарию»

 

В группе параметров «Тип отчета» выберем переключатель «Структура» и убедимся, что выделена ячейка с результатом выполнения сценариев. Нажмем кнопку ОК.

MS Excel добавит в рабочую книгу лист, содержащий итоговый отчет с названием «Структура сценария» (рис. 6).

Рис. 6. Отчет типа «Структура сценария»

 

6. Запуск сценариев на выполнение.

Сценарий запускается на выполнение после анализа отчета, в случае, если именно он выбирается как наилучший. Кнопка «Вывести» в окне «Диспетчер сценариев» изменяет значения исходной анализируемой таблицы в соответствии с выбранным сценарием.

6.1.Установим курсор на лист, в котором были созданы сценарии и в окне «Диспетчер сценариев» щелкнем на имени сценария, который нужно запустить на выполнение.

6.2. Нажмем кнопку «Вывести». В изменяемых ячейках листа появятся значения, соответствующие выбранному сценарию.

 

1.2. Задание для самостоятельного выполнения № 1. Создание сценариев для получения аналитических отчетов

Проанализируйте с помощью сценариев влияние на объем прибыли от реализуемой продукции различных значений цены реализации и объема производства. Исходные данные для каждого варианта приведены в таблице 1. Затраты на производство единицы продукции во всех вариантах - 500 рублей.

По данным своего варианта (см. таблицу 1) составьте три сценария:

· «Увеличение объема производства на 20%»;

· «Увеличение цены реализации на 20%»;

· «Увеличение цены и объема на 20%».

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

Получите следующие распечатки:

1) таблица с исходными данными в режиме чисел (Распечатка 1);

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

3) структура сценария (Распечатка 3);

4) таблица после вывода выбранного сценария (Распечатка 4).

Таблица 1

Варианты для выполнения задания № 1

№ варианта	Объем производства, шт.	Цена реализации, руб.	№ варианта	Объем производства, шт.	Цена реализации, руб.
		1500,00			900,00
		1000,00			850,00
		1300,00			3500,00
		1400,00			4000,00
		3000,00			2500,00
		1300,00			800,00
		1100,00			4500,00
		15000,00			10000,00
		992,00			11000,00
		1700,00			20000,00
		1000,00			500,00
		1200,00			1125,00
		17000,00			18000,00
		1000,00			950,00

Пример применения «Подбора параметра»

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

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

Х = С/(Z-V)

 

Исходные данные представлены на рис. 7.

Рис. 7. Исходные данные. Режим формул

Вычислим новое значение критического объема производства, увеличенное на 20%. Режим формул приведен на рис. 8.

Рис. 8. Вычисление нового значения критического объема. Режим формул

Режим чисел приведен на рис. 9.

Рис. 9. Вычисление нового значения критического объема. Режим чисел

Выберем вкладку Данные – группа Работа с данными - кнопка Анализ «что-если» - Подбор параметра.

В диалоговом окне «Подбор параметра» в поле «Установить в ячейке» укажем адрес ячейки, содержащей формулу вычисления критического объема производства. В поле «Значение» введем новое значение критического объема. В поле «Изменяя значение ячейки» укажем адрес ячейки, содержащей цену реализации единицы изделия (рис. 10).

Рис. 10. Диалоговое окно «Подбор параметра»

В результате выполнения «Подбора параметра» в таблицу будет выведено значение цены реализации, при котором мы получим значение критического объема, увеличенное на 20% (рис. 11).

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

2.2. Задание для самостоятельного выполнения № 2. Применение «Подбора параметра» в решении экономической задачи

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

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

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

3. Определите, как можно добиться прибыли в 15000 рублей за счет изменения переменных издержек на единицу продукции. Какими будут в этом случае переменные издержки?

4. Определите, как можно добиться прибыли в 15000 рублей за счет изменения постоянных издержек. Какими они должны быть в этом случае?

Для вычисления прибыли используйте следующую формулу:

P = (X*Z*(1 – V/Z)) – C

где: Х – критический объем производства;

С – постоянные издержки (руб.);

Z – цена реализации единицы продукции (руб.);

V – переменные издержки за единицу продукции (руб.).

Получите следующие распечатки:

1) после выполнения пункта 1 данного задания сделайте распечатки в режиме чисел и в режиме формул с координатной сеткой и заголовками строк и столбцов (Распечатка 5, Распечатка 6);

2) после выполнения пунктов 2, 3, 4 сделайте распечатки в режиме чисел (Распечатка 7, Распечатка 8, Распечатка 9).

Таблица 2

Варианты для выполнения задания № 2

№ варианта	Постоянные издержки, руб. (С)	Объем производства, шт. (Х)	Цена реализации ед. продукции, руб. (Z)	Переменные издержки за ед. продукции, руб. (V)
	20000,00		180,00	140,00
	12000,00		150,00	70,00
	30000,00		150,00	80,00
	20000,00		100,00	60,00
	20000,00		120,00	80,00
	13000,00		80,00	40,00
	2000,00		70,00	30,00
	12000,00		60,00	40,00
	18000,00		80,00	55,00
	8000,00		50,00	30,00
	1800,00		60,00	30,00
	18000,00		65,00	35,00
	8000,00		60,00	30,00
	8000,00		70,00	40,00
	9000,00		50,00	30,00
	7000,00		96,00	42,00
	3700,00		58,00	37,00
	3400,00		125,00	100,00
	9000,00		125,00	75,00
	8300,00		130,00	94,00
	8600,00		90,00	47,00
	10000,00		156,00	78,00
	10000,00		150,00	100,00
	9500,00		150,00	85,00
	8000,00		135,00	90,00
	9000,00		135,00	80,00
	8500,00		140,00	67,00
	9000,00		300,00	140,00

 

Пример применения «Поиска решения»

1. Создадим на листе «Поиск решения» таблицы согласно Приложению 2. Исходные данные занимают на листе строки с 1 по 29.

2. В ячейку С33 введем целевую функцию (формулу определения прибыли в краткосрочный период), Приложение 2.

3. Для упрощения ввода ограничений при использовании Поиска решения введем формулы, Приложение 2:

3.1. В ячейки B36, C36, D36 введем формулы для подсчета затрат производственных мощностей, требуемых для выполнения производственной программы, т.е. времени, необходимого для изготовления искомого объема каждого вида продукции.

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

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

4. Выберем вкладку Данные - Поиск решения.

Если кнопка Поиск решения отсутствует, то нужно выбрать вкладку Файл- Параметры – Надстройки – кнопка Перейти – установить флажок Поиск решения.

5. В диалоговом окне «Параметры поиска решения» в поле «Оптимизировать целевую ячейку» введем адрес ячейки С33, содержащей формулу определения прибыли (рис. 12).

Рис.12. Диалоговое окно «Параметры поиска решения»

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

Если требуется найти заданное значение прибыли, то в окне «Поиск решения» в поле «Значения» нужно ввести конкретное значение прибыли.

7. В поле «Изменяя ячейки переменных» укажем диапазон ячеек, в которых должен быть найден объем выпускаемой продукции. Это ячейки Е6, Е7, Е8.

8. Введем ограничения. Например, для ввода ограничения на трудовые ресурсы выполним следующие действия:

- щелкнем по кнопке Добавить. Появится окно «Добавление ограничения» (рис. 13).

- в поле «Ссылка на ячейку» введем адрес ячейки, в которой подсчитывается рабочее время персонала, требуемое для выполнения производственной программы. Это ячейка В37.

- выберем нужный знак сравнения. В данном случае это «меньше или равно».

- в поле «Ограничение» укажем адрес ячейки, в которой находится ограничивающее число. В данном случае это ячейка В23, в которую введен фонд времени персонала. Таким образом, мы задали условие, что время, требуемое персоналу для выполнения всего объема производства, не может превышать фонд рабочего времени персонала.

Рис. 13. Диалоговое окно «Добавление ограничения»

- щелкнем по кнопке Добавить. Введем следующее ограничение. После ввода всех ограничений щелкнем по кнопке ОК.

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

Ограничения на производственные мощности:

$B$36<=$F$14; $C$36<=$F$15; $D$36<=$F$16

Ограничение на сырье:

$B$38<=$E$29+$F$29

Ограничения на выпускаемую продукцию:

$E$6<=$C$14; $E$6>=$B$14; $E$7<=$C$15; $E$7>=$B$15;

$E$8<=$C$16; $E$8>=$B$16

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

$E$6=целое; $E$7=целое; $E$8=целое

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

Рис. 14. Окно «Параметры поиска решения» после ввода ограничений

 

9. После ввода всех ограничений окно «Поиск решения» примет вид, представленный на рис. 14.

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

Рис. 15. Диалоговое окно «Результаты поиска решения»

11. Установим в этом окне флажок «Сохранить найденное решение». При этом в изменяемых ячейках (Е6:Е8) будут выведены найденные значения объема производства.

12. Создадим отчет по результатам поиска решения. Для этого в окне «Результаты поиска решения» в поле «Отчеты» выберем Результаты. Нажмем кнопку ОК. В рабочей книге появится лист с именем «Отчет о результатах».

 

3.2. Задание для самостоятельного выполнения № 3. Нахождение вариантов оптимальной производственной программы по выпуску продукции с использованием «Поиска решения»

Назовите лист рабочей книги «Поиск решения» и выполните на нем задания в соответствии со своим вариантом (стр. 24 - 51).

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

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

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

Получите следующие распечатки:

1) лист «Поиск решения» с исходными данными и расчетными формулами в режиме формул с координатной сеткой и заголовками строк и столбцов (Распечатка 10);

2) после выполнения пункта 1 данного задания выполните распечатку в режиме чисел (Распечатка 11) и отчет «Результаты» (Распечатка 12).

3) после выполнения пункта 2 задания выполните распечатку в режиме чисел (Распечатка 13) и отчет «Результаты» (Распечатка 14).

 

 

Варианты для выполнения задания № 3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Поддержки принятия решений «Таблица данных»

 

Пример применения «Таблиц данных»

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

1) Составим план погашения кредита, выданного банком на заданный срок.

2) Рассчитаем выплаты по процентам в зависимости от размера кредита, выданного на заданный срок.

Возьмем следующие исходные данные: размер кредита 400000 рублей; ставка 11% годовых; срок кредита 5 лет.

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

В MS Excel для определения, какая часть выплат идет на погашение основной задолженности, воспользуемся функцией ОСПЛТ из категории «финансовые функции». Рассмотрим аргументы данной функции:

ОСПЛТ(ставка, период, кпер, пс, бс, тип ), где

ставка – процентная ставка за период;

период – порядковый номер периода, для которого производится расчет. Этот номер лежит в интервале от 1 до кпер;

кпер – общее количество периодов платежей;

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

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

тип – тип ренты, если тип=0 или опущен, то рента постнумерандо (выплата в конце периода), если тип=1, то рента пренумерандо (выплата в начале периода).

В MS Excel часть выплат для обслуживания процентов по основному долгу вычисляется с помощью функции ПРПЛТ из категории «финансовые функции». Данная функция имеет следующий вид:

ПРПЛТ (ставка, период, кпер, пс, бс, тип)

Аргументы функции такие же, как и у рассмотренной выше функции ОСПЛТ.

Решение задачи 1

1. Назовем лист рабочей книги «Таблица данных1» и разместим на нём исходные данные (рис. 16).

 

Рис. 16. Размещение исходных данных на рабочем листе

2. В ячейку В7 введем формулу расчета процентов по основному долгу; в ячейку В8 – формулу расчета платежа в погашение основной задолженности; в ячейке D8 рассчитаем размер годовой выплаты; в ячейки C12, D12 введем формулы суммирования по соответствующему столбцу (рис. 17).

Рис. 17. Расчет платежа по кредиту

3. Для расчета платежей за весь срок кредита применим Таблицу данных с одной переменной. Для этого выделим диапазон A7:D11, выберем вкладку Данные – группа Работа с данными - кнопка Анализ «что-если» - Таблица данных.

4. В рассматриваемой задаче переменной является период (год, за который рассчитываются выплаты). Во введенных формулах это ячейка A7. Для того, чтобы рассчитать выплаты, в ячейку А7 нужно подставлять соответствующие значения периода (год). Значения периода находятся в строках таблицы в ячейках с А7 по А11. Поэтому в диалоговом окне «Таблица данных» в поле «Подставлять значения по строкам в» укажем ячейку А7, затем нажмем кнопку ОК (рис. 18).

 

Рис. 18. Диалоговое окно «Таблица данных»

5. В результате в таблице получаем расчет платежей за весь срок кредита (рис. 19). Значение в ячейке С12 должно получиться равным размеру кредита.

Рис. 19. Результат решения задачи 1

Решение задачи 2

1. Назовем новый лист рабочей книги «Таблица данных 2» и разместим на нем исходные данные (рис. 20).

Размер кредита в ячейке С1 не введен, т.к. по условию задачи это переменное значение. Размер кредита введен в ячейки B7:E7. В ячейке С3 введен период 1. Это тоже переменная величина. С точки зрения технологии решения задачи не имеет значения, какой период введен в ячейку С3. Ячейка не оставлена пустой (как ячейка С1), т.к. применяемые функции не позволяют ссылаться на пустые ячейки.

2. В ячейку А7 введем формулу расчета платежа по проценту (рис. 20).

Рис. 20. Размещение исходных данных на рабочем листе

3. Для расчета платежей по процентам применим Таблицу данных с двумя переменными. Для этого выделим диапазон A7:Е12, выберем вкладку Данные – группа Работа с данными - кнопка Анализ «что-если» - Таблица данных.

4. В рассматриваемой задаче переменными являются период и размер кредита. Во введенной формуле это ячейки С3 и С1 соответственно. Для того, чтобы рассчитать выплаты, в ячейку С3 нужно подставлять соответствующие значения периода. Значения периода находятся в строках таблицы в ячейках с А8 по А12. Поэтому в диалоговом окне «Таблица данных» в поле «Подставлять значения по строкам в» укажем ячейку С3.

Значения размера кредита находятся в столбцах таблицы в ячейках с В7 по Е7. Поэтому в диалоговом окне «Таблица данных» в поле «Подставлять значения по столбцам в» укажем ячейку С1 и нажмем кнопку ОК (рис. 21).

Рис. 21. Диалоговое окно «Таблица данных»

В результате в таблице получаем расчет выплат по процентам (рис. 22).

Рис. 22. Результат расчета выплат по процентам

4.2. Задание для самостоятельного выполнения № 4. Применение «Таблиц данных» в экономических расчетах

Выполните следующие задания в соответствии со своим вариантом (таблица 3).

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

2) Назовите лист рабочей книги «Таблица данных 2». Рассчитайте выплаты по процентам в зависимости от размера кредита, выданного на заданный срок.

САМОСТОЯТЕЛЬНАЯ РАБОТА

 

по теме

«Информационные технологии создания систем поддержки

КАЗАНСКИЙ ФЕДЕРАЛЬНЫЙ УНИВЕРСИТЕТ

ИНСТИТУТ УПРАВЛЕНИЯ, ЭКОНОМИКИ И ФИНАНСОВ