Задания для выполнения практической работы
Вариант 1
1. Решить систему линейных уравнений А3Х = В и вычислить значение квадратичной формы Z = YтАтА2Y, где
2. Используя мастер Поиск Решения, решить задачу оптимального использования ресурсов на максимум общей стоимости. Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в соответствующей таблице.
| Тип сырья | Нормы расхода сырья на одно изделие | Запасы сырья | |||
| А | Б | В | Г | ||
| I | |||||
| II | |||||
| III | |||||
| Цена изделия |
3. Исходные данные транспортной задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.
4. Построить график временного ряда, выделить тренд и построить прогноз на два шага вперед. Исходные данные по временному ряду за 10 недель приведены в таблице.
| Неделя | ||||||||||
| Количество машин |
5. В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период. Требуется: а) построить однофакторную модель регрессии; б) оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке; в) отобразить на графике исходные данные и результаты моделирования.
| Номер наблюдения | ||||||||||
| Прибыль (У) | ||||||||||
| % ставка (Х) |
Вариант 2
1. Решить систему линейных уравнений А2АтХ = В и вычислить значение квадратичной формы Z = YтА3Y, где
2. Используя мастер Поиск Решения, решить задачу оптимального использования ресурсов на максимум общей стоимости. Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в соответствующей таблице.
| Тип сырья | Нормы расхода сырья на одно изделие | Запасы сырья | |||
| А | Б | В | Г | ||
| I | |||||
| II | |||||
| III | |||||
| Цена изделия |
3. Исходные данные транспортной задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.
4. Построить график временного ряда, выделить тренд и построить прогноз на два шага вперед. Исходные данные по временному ряду за 10 недель приведены в таблице.
| Неделя | ||||||||||
| Количество машин |
5. В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период. Требуется: а) построить однофакторную модель регрессии; б) оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке; в) отобразить на графике исходные данные и результаты моделирования.
| Номер наблюдения | ||||||||||
| Прибыль (У) | ||||||||||
| % ставка (Х) |
Вариант 3
1. Решить систему линейных уравнений ААтАХ = В и вычислить значение квадратичной формы Z = YтАтА3Y, где
2. Используя мастер Поиск Решения, решить задачу оптимального использования ресурсов на максимум общей стоимости. Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в соответствующей таблице.
| Тип сырья | Нормы расхода сырья на одно изделие | Запасы сырья | |||
| А | Б | В | Г | ||
| I | |||||
| II | |||||
| III | |||||
| Цена изделия |
3. Исходные данные транспортной задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.
4. Построить график временного ряда, выделить тренд и построить прогноз на два шага вперед. Исходные данные по временному ряду за 10 недель приведены в таблице.
| Неделя | ||||||||||
| Количество машин |
5. В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период. Требуется: а) построить однофакторную модель регрессии; б) оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке; в) отобразить на графике исходные данные и результаты моделирования.
| Номер наблюдения | ||||||||||
| Прибыль (У) | ||||||||||
| % ставка (Х) |
Вариант 4
1. Решить систему линейных уравнений А2А2АХ = В и вычислить значение квадратичной формы Z = YтАтААтY, где
2. Используя мастер Поиск Решения, решить задачу оптимального использования ресурсов на максимум общей стоимости. Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в соответствующей таблице.
| Тип сырья | Нормы расхода сырья на одно изделие | Запасы сырья | |||
| А | Б | В | Г | ||
| I | |||||
| II | |||||
| III | |||||
| Цена изделия |
3. Исходные данные транспортной задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.
4. Построить график временного ряда, выделить тренд и построить прогноз на два шага вперед. Исходные данные по временному ряду за 10 недель приведены в таблице.
| Неделя | ||||||||||
| Количество машин |
5. В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период. Требуется: а) построить однофакторную модель регрессии; б) оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке; в) отобразить на графике исходные данные и результаты моделирования.
| Номер наблюдения | ||||||||||
| Прибыль (У) | ||||||||||
| % ставка (Х) |
Вариант 5
1. Решить систему линейных уравнений ААтА2Х = В и вычислить значение квадратичной формы Z = YтА3АтY, где
2. Используя мастер Поиск Решения, решить задачу оптимального использования ресурсов на максимум общей стоимости. Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в соответствующей таблице.
| Тип сырья | Нормы расхода сырья на одно изделие | Запасы сырья | |||
| А | Б | В | Г | ||
| I | |||||
| II | |||||
| III | |||||
| Цена изделия |
3. Исходные данные транспортной задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.
4. Построить график временного ряда, выделить тренд и построить прогноз на два шага вперед. Исходные данные по временному ряду за 10 недель приведены в таблице.
| Неделя | ||||||||||
| Количество машин |
5. В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период. Требуется: а) построить однофакторную модель регрессии; б) оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке; в) отобразить на графике исходные данные и результаты моделирования.
| Номер наблюдения | ||||||||||
| Прибыль (У) | ||||||||||
| % ставка (Х) |
Вариант 6
1. Решить систему линейных уравнений А3АтХ = В и вычислить значение квадратичной формы Z = YтА2АтАY, где
2. Используя мастер Поиск Решения, решить задачу оптимального использования ресурсов на максимум общей стоимости. Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в соответствующей таблице.
| Тип сырья | Нормы расхода сырья на одно изделие | Запасы сырья | |||
| А | Б | В | Г | ||
| I | |||||
| II | |||||
| III | |||||
| Цена изделия |
3. Исходные данные транспортной задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.
4. Построить график временного ряда, выделить тренд и построить прогноз на два шага вперед. Исходные данные по временному ряду за 10 недель приведены в таблице.
| Неделя | ||||||||||
| Количество машин |
5. В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период. Требуется: а) построить однофакторную модель регрессии; б) оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке; в) отобразить на графике исходные данные и результаты моделирования.
| Номер наблюдения | ||||||||||
| Прибыль (У) | ||||||||||
| % ставка (Х) |
Вариант 7
1. Решить систему линейных уравнений АтА3Х = В и вычислить значение квадратичной формы Z = YтААтА2Y, где
2. Используя мастер Поиск Решения, решить задачу оптимального использования ресурсов на максимум общей стоимости. Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в соответствующей таблице.
| Тип сырья | Нормы расхода сырья на одно изделие | Запасы сырья | |||
| А | Б | В | Г | ||
| I | |||||
| II | |||||
| III | |||||
| Цена изделия |
3. Исходные данные транспортной задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.
4. Построить график временного ряда, выделить тренд и построить прогноз на два шага вперед. Исходные данные по временному ряду за 10 недель приведены в таблице.
| Неделя | ||||||||||
| Количество машин |
5. В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период. Требуется: а) построить однофакторную модель регрессии; б) оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке; в) отобразить на графике исходные данные и результаты моделирования.
| Номер наблюдения | ||||||||||
| Прибыль (У) | ||||||||||
| % ставка (Х) |
Вариант 8
1. Решить систему линейных уравнений ААтА2Х = В и вычислить значение квадратичной формы Z = YтА2АтАY, где
2. Используя мастер Поиск Решения, решить задачу оптимального использования ресурсов на максимум общей стоимости. Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в соответствующей таблице.
| Тип сырья | Нормы расхода сырья на одно изделие | Запасы сырья | |||
| А | Б | В | Г | ||
| I | |||||
| II | |||||
| III | |||||
| Цена изделия |
3. Исходные данные транспортной задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.
4. Построить график временного ряда, выделить тренд и построить прогноз на два шага вперед. Исходные данные по временному ряду за 10 недель приведены в таблице.
| Неделя | ||||||||||
| Количество машин |
5. В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период. Требуется: а) построить однофакторную модель регрессии; б) оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке; в) отобразить на графике исходные данные и результаты моделирования.
| Номер наблюдения | ||||||||||
| Прибыль (У) | ||||||||||
| % ставка (Х) |
Вариант 9
1. Решить систему линейных уравнений АтААтХ = В и вычислить значение квадратичной формы Z = YтААтААтY, где
2. Используя мастер Поиск Решения, решить задачу оптимального использования ресурсов на максимум общей стоимости. Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в соответствующей таблице.
| Тип сырья | Нормы расхода сырья на одно изделие | Запасы сырья | |||
| А | Б | В | Г | ||
| I | |||||
| II | |||||
| III | |||||
| Цена изделия |
3. Исходные данные транспортной задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.
4. Построить график временного ряда, выделить тренд и построить прогноз на два шага вперед. Исходные данные по временному ряду за 10 недель приведены в таблице.
| Неделя | ||||||||||
| Количество машин |
5. В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период. Требуется: а) построить однофакторную модель регрессии; б) оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке; в) отобразить на графике исходные данные и результаты моделирования.
| Номер наблюдения | ||||||||||
| Прибыль (У) | ||||||||||
| % ставка (Х) |
Вариант 10
1. Решить систему линейных уравнений А2АтАХ = В и вычислить значение квадратичной формы Z = YтААтААтY, где
2. Используя мастер Поиск Решения, решить задачу оптимального использования ресурсов на максимум общей стоимости. Ресурсы сырья, норма его расхода на единицу продукции и цена продукции заданы в соответствующей таблице.
| Тип сырья | Нормы расхода сырья на одно изделие | Запасы сырья | |||
| А | Б | В | Г | ||
| I | 0,5 | ||||
| II | |||||
| III | |||||
| Цена изделия | 7,5 |
3. Исходные данные транспортной задачи приведены схематически: внутри прямоугольника заданы удельные транспортные затраты на перевозку единицы груза, слева указаны мощности поставщиков, а сверху – мощности потребителей. Найти оптимальный план закрепления поставщиков за потребителями.
4. Построить график временного ряда, выделить тренд и построить прогноз на два шага вперед. Исходные данные по временному ряду за 10 недель приведены в таблице.
| Неделя | ||||||||||
| Количество машин |
5. В таблице заданы два временных ряда: первый из них представляет нарастающую по кварталам прибыль коммерческого банка (У), второй ряд – процентную ставку этого банка по кредитованию юридических лиц (Х) за тот же период. Требуется: а) построить однофакторную модель регрессии; б) оценить прибыль банка при заданной (принимается пользователем самостоятельно) процентной ставке; в) отобразить на графике исходные данные и результаты моделирования.
| Номер наблюдения | ||||||||||
| Прибыль (У) | ||||||||||
| % ставка (Х) |
КОНТРОЛЬНЫЕ ВОПРОСЫ
1. Основные функции для работы с матрицами.
2. Особенности обработки формул при матричных вычислениях.
3. Основные этапы решения системы уравнений с помощью обратной матрицы.
4. Функция для работы с матрицей МОБР.
5. Функция для работы с матрицей МОПРЕД.
6. Функция для работы с матрицами МУМНОЖ.
7. Функция для работы с матрицей ТРАНСП.
8. Формирование данных на листе Excel при решении задач линейного программирования.
9. Основные этапы решения задачи оптимального использования ресурсов на максимум общей стоимости.
10. Основные этапы решения задачи нахождения оптимального плана закрепления поставщиков за потребителями.
11. Мастер Поиск решения. Настройка инструмента.
12. Параметры диалогового окна Параметры поиска решения.
13. Встроенная математическая функция СУММПРОИЗВ.
14. Процедура Добавление ограничения.
15. Виды сохранения результатов поиска решения.
16. Добавления линии тренда.
17. Параметры диалогового окна Формат линии тренда.
18. Типы линии тренда.
19. Способ задания прогноза на несколько периодов.
20. Какой показатель является определяющим для выбора типа линии тренда в качестве прогнозной.
21. Встроенная статистическая функция ТЕНДЕНЦИЯ.
22. Основные этапы решения задач регрессионного анализа.
23. Формулы для вычисления параметров линейной регрессии.
24. Расчет таблицы остатков.
25. Формула для вычисления величины отклонения от линии регрессии.
26. Расчет верхней и нижней границ прогноза.
27. Встроенная статистическая функция НАКЛОН.
28. Встроенная статистическая функция ОТРЕЗОК.
29. Встроенная статистическая функция ЛИНЕЙН.
30. Инструмент мастера Анализа данных - Регрессия.
Глава 3. Оценка эффективности денежных вложений
Сложные проценты
Кредит – необходимое условие для существования рынка. Перед тем как предложить что-либо к продаже в качестве товара, это надо создать, или купить, или придумать, ил быть готовым изготовить. Для этого нужно сначала потратить некое количество денег на изготовление, покупку или обучение, то есть использовать кредит. После продажи кредит обычно возвращается, а дополнительное количество денег, полученное в результате продажи, и составляет интерес продавца. Ссудив деньги заемщику, кредитор лишает себя возможности самому произвести нужный товар, продать его и увеличить, таким образом, имеющееся у него количество денег. За отказ от этой возможности он требует оплаты – кредит возвращается обычно в большем размере, чем исходно взятая сумма. Превышение возвращенной суммы над суммой взятого кредита называется процентами и составляет интерес кредитора. Обычно кредит выдается на определенное время, и величина процентов заранее оговаривается.
Например, в кредит выдается 1 000руб. на срок 6 месяцев под 18% годовых. Это означает, что через полгода заемщик должен вернуть кредитору 1 090руб. (если бы срок кредита составлял 1 год, то через год надо было бы вернуть 1 180руб.). Такие расчеты называются расчетами по формуле простых процентов, и большинство краткосрочных кредитов выдается и возвращается по этим правилам.
Однако не все кредиты можно описать с помощью формулы простых процентов, рассмотренной в первом параграфе первого раздела. Представим, что заемщик вовремя не смог рассчитаться с кредитором, не возвратив по истечении времени Т взятую в долг сумму. Представим также, что кредитора такой вариант развития события не очень волнует и он не беспокоится о сохранности своих денег, абсолютно доверяя заемщику.
В этой ситуации кредитор и заемщик могут договориться о пролонгации кредита на тех же условиях (под те же проценты и на такой же срок), что и раньше, за исключением следующего факта. Поскольку на момент возврата кредита заемщик был должен уже не S0, а S, то новой суммой, взятой в долг, будет уже S. Новая возвращаемая сумма Snew будет вычисляться как Snew = S(1+(i∙t)/T)= S0(1+(i∙T)/T)∙(1+(i∙t)/T). Если и по окончании второго срока займа он не возвращается, то к концу третьего периода итоговая возвращаемая сумма уже будет вычисляться как Snew = S0(1+(i∙T)/T)2∙(1+(i∙t)/T). Если же заем возвращен спустя только m-1 таких «пролонгаций» (или m периодов Т), то итоговая сумма «к получению» кредитором вычисляется по формуле: S = S0(1+(i∙T)/T)m.
Эту формулу и называют формулой сложных процентов. «Сложность» их состоит в том, что за текущий период времени проценты начисляются как на саму исходную сумму займа, так и на начисленные на нее ранее проценты.
В договорах, как правило, указываются годовая ставка r и количество начислений процентов m в течение года. Это означает, что базовый период составляет год, деленный на m, а ставка сложных процентов для периода равна r/m. Формула для сложных процентов приобретает вид: S+P(1+r/m)T=0, где Т – периоды, Р – сумма кредита, S – возвращаемая сумма. Если начисление происходит k лет, то формула приобретает вид S+P(1+r/m)km=0.
Пример 1. Ссуда в 20 000 руб. дана на полтора года под ставку 28% годовых с ежеквартальным начислением. Определить сумму конечного платежа (рис. 3.1).
Рис. 3.1. Вычисление суммы конечного платежа с использованием функции БС
Сумма конечного платежа составит 30 014,61руб.
Пример 2. Банк принимает вклад на срок 3 месяца с объявленной годовой ставкой 100% или на 6 месяцев под 110%. Как выгоднее вкладывать деньги на полгода: дважды на три месяца или один раз на 6 месяцев?
Вложенный 1руб. по первой схеме «превратится » за три месяца в 1,56руб. (рис. 3.2), а вложенный 1руб. по второй схеме на шесть месяцев в 1,55руб. (рис. 3.3).
Рис. 3.2. Расчет возвращаемой суммы по схеме
«на 3 месяца под 100% годовых»
Рис. 3.3. Расчет возвращаемой суммы по схеме
«на 6 месяцев под 110% годовых»
Следовательно, первая схема предпочтительнее.
Пример 3. Рассчитать будущее значение вклада 1000 руб. через 0, 1, 2, 3, 4, 5 лет при годовых процентных ставках 10%, 20%, 30%, 40%, 50%. Дополнительные поступления и выплаты отсутствуют. Расчет выполняется с использованием финансовой функции БС. В ячейку В3 введена соответствующая формула (рис. 3.4.), которая затем копируется на диапазон В3:G7.
Рис. 3.4. Расчет будущего значения вклада
Пример 4. Сумма 2 000 руб. размещена под 9% годовых на 3 года. Проценты начисляются раз в квартал. Какая сумма будет на счете? Расчет показан на рис. 3.5.
Рис. 3.5. Вычисление будущей стоимости вклада
по схеме сложных процентов
Через три года на счете будет 2 612,10руб.
Если срок начисления процентов не составляет целое число лет, то на практике применяют два метода. Согласно общему методу расчет ведется по формуле сложных процентов. Смешанный метод предполагает начисление процентов за целое число лет по формуле сложных процентов и по формуле простых процентов за дробную часть периода.
Пример 5. Какова сумма долга через 26 месяцев, если его первоначальная величина 500 000руб., проценты сложные, ставка - 20% годовых, начисление поквартальное? Провести вычисления по общему (рис. 3.6) и смешанному (рис. 3.7) методам.
Рис. 3.6. Расчет суммы долга по общему методу
Рис. 3.7. Расчет суммы долга по смешанному методу
Сумма долга по общему методу расчета составит 763 151,21руб., по смешанному методу – 763 351,98руб.