Практическая работа по табличному процессору Excel

Практическая работа по табличному процессору Excel

Использование методов анализа данных

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

Фирма «Маква» решила расширить производство. С этой целью ей необходимо закупить новое оборудование. Производитель оборудования готов выполнить поставку оборудования через 10 месяцев. Стоимость оборудования составляет 900 тыс. долл.

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

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

Фирма «Маква» нашла инвестиционную компанию «Октопус», которая заключает договоры на продажу своих акций. Цена 1 акции составляет 9 тыс. долл. Договор заключается не более чем на 1,5 года.

Эмитент (организация, которая выпускает акции) установил размер ставки роста стоимости 1 акции (ставки наращивания) в 0,15 % за день по отношению к стоимости акции за предыдущий день. В зависимости от срока возврата (срока с даты закупки акций до даты возврата акций эмитенту) фирма-эмитент возвращает покупателю различные суммы.

До начала реализации данного проекта руководство фирмы «Маква» должно произвести расчеты с целью определения:

- срока возврата, за который фирма получит необходимую сумму для расчета с производителем оборудования, при условии, что фирма «Маква» купила акции на определенную сумму;

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

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

 

A. Представление в табличной форме исходных и расчетных значений данных.

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

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

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

 

Выполнение лабораторной работы

 

Подготовка к выполнению работы

1.1. Запустить табличный процессор Excel.

1.2. На листе с именем «Лист1» ввести заголовок отчета о работе: «Использование методов анализа данных» и в соответствии с требованиями к заголовкам форматировать его в пределах столбцов A:I.

1.3. Пропустив 1 пустую строку, ввести выходные данные отчета:

«Практическое занятие по табличному процессору Excel

Выполнил <фамилия и инициалы>

Начало выполнения: <дата>

Продолжение выполнения: <дата>

Задание выполнено: <дата>».

 

1.4. Создать в своей именной папке файл с именем Podbor_param.

1.5. Убедиться с помощью приложения Проводник, что в Вашей именной папке появился файл Podbor_param.

1.6. Переименовать лист с именем «Лист 1», присвоив ему имя «Прямая_задача».

1.7. Устно сформулировать сравнительную оценку двум формулировкам постановки задачи: первая формулировка представлена в начале задания, вторая формулировка представлена ниже.

Постановка задачи
Фирме Маква необходимо новое оборудование
Условия поставщика оборудования: - стоимость оборудования - 900 тыс. долл.; - аванс - 100 тыс. долл.; - срок поставки - через 10 месяцев после оплаты аванса; - окончательный расчет - 800 тыс. долл. - после поставки оборудования (через 10 месяцев после оплаты аванса).
Маква имеет 150 тыс. долл.
Свободные деньги после оплаты аванса - 50 тыс. долл.
Условия инвестиционной компании Октопус (эмитента акций): - цена продажи эмитентом 1 акции - 9 тыс. долл.; - ставка наращивания стоимости 1 акции (рост стоимости 1 акции за 1день по сравнению с предыдущим днем) - 0,15%; - максимальный срок возврата покупателем акций эмитенту - 1,5 года.
Вопросы, на которые необходимо ответить руководству фирмы Маква: - какое количество акций можно купить на свободные деньги? - какой будет возвратная стоимость купленных акций через 10 месяцев? - за какой срок возвратная стоимость акций будет равна 800 тыс. долл? - сколько акций необходимо купить (какую сумму необходимо иметь для закупки акций), чтобы через 10 месяцев их возвратная стоимость была равна 800 тыс. долл?

 

Выбранную формулировку ввести в отчёт о работе.

Подбор значения параметра

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

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

 

A	B	C	D	E	F	G	H	I
Исходные данные для функционирования надстройки "Подбор параметра"
							Таблица 8
Имя ячейки	Название параметра	Значение или результат	Введенный в ячейку текст формулы
Стоим_1_акц	Стоимость 1 акции (тыс. долл.)
Сумм_закуп	Сумма на закупку акций (тыс. долл.)
Количество	Количество закупленных акций (шт)
	Закупочная стоимость акций (тыс. долл.)
Ставка_наращ	Ставка наращивания стоимости акции в день(%)	0,15%
Срок_возврата	Срок возврата акций эмитенту (дни)
Коэфф_наращ	Коэффициент наращивания стоимости 1 акции	1,0000
	Возвратная стоимость акций (тыс. долл.)

 

Рис. 10

 

Как правило, все обратные задачи решаются методом последовательных приближений (итеративно, путем подбора значений). По заданному значению функции и формуле делается грубая оценка начального значения аргумента. Проводится расчет по формуле и определяется первое значение функции. Это значение сравнивается с заданным значением. По результатам сравнения определяется новое значение аргумента, и проводится очередной расчет. Такие процедуры проводятся до тех пор, пока при прямом расчете не получится значение функции, наиболее близкое к заданному. Очевидно, что для выполнения такой процедуры Excel необходимо 2 ячейки: ячейка, в которую пользователь вводит заданное значение функции, и ячейка, в которуюExcel последовательно вводит значения аргумента.

Очевидно, что для решения этой задачи также необходимо иметь исходные значения данных и формулу. С этой целью на новом Рабочем листе должна будет создана представленная на рис. 10 таблица «Исходные данные для функционирования надстройки «Подбор параметра».

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

В этом разделе должны быть решены 3 задачи:

a) Тестовая задача - проверка правильности выдачи задания надстройке «Подбор параметра» путем проведения пробного расчета с известным значением функции и аргумента;

b) Расчет для 5 акций - определение срока возврата 5 акций, которые фирма Маква имеет возможность купить, чтобы возвратная стоимость этих акций равнялась необходимой сумме – 800 тыс. долл.;

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

7.1. Ввести после листа «Таблица_подстан» новый лист и присвоить ему имя «Подбор_парам». В первой строке этого листа написать заголовок: «Подбор значения параметра» и создать представленную на рис. 10 Таблицу 8.

7.2. Присвоение ячейкам собственных имен и ввод формул.

7.2.1. В Таблице 8присвоить ячейкам столбца «Значение или результат» имена, представленные в первом столбце этой таблицы.

7.2.2. Убедиться в том, что Вы присвоили необходимым ячейкам необходимые имена. С этой целью в поле «Имя» (при наведении на это поле графического указателяпоявляется всплывающая подсказка в виде имени этого поля – «имя») строки формул открыть выпадающее меню, выбирать последовательно по алфавиту имена ячеек и определять, какая ячейка при этом становится текущей.

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

7.2.4. Сравнить записи формул в Таблице 5 и Таблице 8. Устно привести отрицательные и положительные стороны каждой из записей.

7.3. Проведение с помощью надстройки «Подбор параметра» пробного расчета с известным значением функции и аргумента.

На рис. 11 представлено окно «Подбор параметра», которое выдает надстройка «Подбор параметра»после её запуска. 2 ячейки со значениямифункции и аргумента, которые должны быть введены в поля этого окна, должны принадлежать Таблице 8. В этом диалоговом окне необходимо задать 3 параметра. Первый параметр:значение функции (поле «Значение»), которое интересует пользователя. Второй параметр связан с первым:адрес ячейки, в которую надстройка «Подбор параметра» запишет заданное пользователем значение функции (поле «Установить в ячейке»).Третий параметр: адрес ячейки, в которой надстройка «Подбор параметра» будет хранить подбираемые значения аргумента (поле «Изменяя значения ячейки»).Эти ячейки существуют в Таблице 8.

Практическая работа по табличному процессору Excel