Практическое занятие 6. Решение задач путем анализа данных Сценарии и подбор параметров Задание 1. Расчет ипотечной ссуды.
1.Создать таблицу для расчета ипотечной ссуды (рис. 126)
Рис. 126
Создать следующие сценарии (рис. 127):
-
Сценарии
1
2
3
Срок погашения ссуды, мес.
240
120
60
Процентная ставка, % за год
8
10
12
Рис. 127
3. Получить отчет по сценарию в виде структуры с отражением результатов: месячная плата, общая сумма выплат и суммы комиссионных.
4. Подобрать параметры срока погашения ссуды при месячной плате 1500 .
Задание 2. Расчет прибыли.
1. Создать таблицу для расчета прибыли (рис. 128).
Рис. 128
Создать следующие сценарии (рис. 129):
-
Сценарии
1
2
3
Стоимость услуг
50
60
70
Стоимость материалов
60
55
50
Рис. 129
3. Получить отчет по сценарию в виде структуры с отражением результатов: сумма прибыли (ячейка В5).
4. Подобрать параметры стоимости услуг при прибыли в 1000 руб. и при прибыли в 2000 руб.
Примечание:для большей информативности результатов расчетов присвоить ячейкам В3, В4 и В5 соответствующие имена.
Задание 3.
В примере из задания 2 занятия 1, использую процедуру подбора параметра, определить объем продаж, при котором прибыль равна нулю (определить точку безубыточности).
Поиск решения. Задание 4. Расчет оптимальной реализации продукции.
1. Создать таблицу расчета реализации (рис. 130) .
Рис. 130
2.Установить целевую ячейку, изменяемые ячейки, ограничения (рис. 131).
Рис. 131
3. Выполнить поиск решения и получить отчет.
Результаты оптимизации (поиска решения) приведены на рис. 131а
Рис. 131а
Задание 5. Расчет оптимальной цены реализации продукции.
В примере из задания 2 занятия 1, ввести зависимость между объемом продаж и ценой изделия (такая зависимость называется функцией спроса). Она имеет вид
q=b-a*p, где a=20, b=30000 (см. рис. 131б )
Рис. 131 б.
Если задать диапазон изменения цены в пределах от 300 до 1300, то получим оптимальную цену изделия, при которой прибыль будет максимальной. Графически эта зависимость представлена на рис. 131в.
Рис. 131 в.
Используя процедуру Поиск решения, найти оптимальную цену изделия, обеспечивающую максимальную прибыль.
Практическое занятие 7. Аппроксимация и прогнозирование в Excel.
Задание 1.
Имеются статистические данные о населении России, представленные в таблице (рис. 132):
|
Год |
Численность населения в млн. чел. |
|
1960 |
117,5 |
|
1970 |
130,1 |
|
1980 |
137,6 |
|
1990 |
147,4 |
|
1991 |
148,5 |
|
1992 |
147,7 |
|
1993 |
148,7 |
|
1994 |
148,4 |
|
1995 |
148,3 |
|
1996 |
148,1 |
|
1997 |
148,3 |
Рис. 132
Спрогнозировать численность населения в 2000 году.
Задание 2.
1.Создать таблицу прогноза прибыли, приведенную на рис. 133.
Рис. 133
Рассчитать прогнозируемые значения прибыли предприятия в 98 и 99 годах с помощью функций ПРЕДСКАЗ, РОСТ и ТЕНДЕНЦИЯ.