2. Пояснения.
Создайте таблицу, состоящую из граф: «№ п/п» (1), «Год» (2), «Количество зарегистрированных фирм» (3), «Количество плательщиков» (4), «Поступления налогов в бюджет» (5).
Все графы таблицы заполните данными за предыдущие пять лет.
Используя статистические функции ТЕНДЕНЦИЯ и РОСТ, спрогнозируйте количество зарегистрированных фирм на следующие три года, определите количество налогоплательщиков в этих фирмах и рассчитайте количество поступлений налогов в бюджет от налогоплательщиков.
Проанализируйте результат, построив различные графики.
1. Постановка задачи.
Дано изменение курсов четырех ценных бумаг в течение 25 недель, начиная с 7 января 2005 года. Необходимо спрогнозировать стоимость ценных бумаг на следующие три недели и оценить предсказуемость курса ценной бумаги с использованием коэффициента корреляции.
2. Пояснения.
Создайте таблицу, состоящую из граф: «№ п/п» (1), «Дата» (2), «Курс 4-х ценных бумаг» (3-6).
Рассчитайте курсы четырех ценных бумаг в соответствие с условиями, заданными в табл. 5.7. При моделировании курсов ценных бумаг используйте функции СЛЧИС, СЛУЧМЕЖДУ, полученные значения округлите до целого.
Полученную таблицу значений ценных бумаг скопируйте на другой лист (используя команду «Специальная вставка»), так как функции СЛЧИС и СЛУЧМЕЖДУ будет пересчитываться с каждой операцией, и цифры все время будут изменяться.
Постройте график изменения стоимости каждой ценной бумаги, добавьте линии тренда и выведите величины достоверности аппроксимации. По коэффициентам достоверности определите, какую прогнозную функцию следует использовать – ТЕНДЕНЦИЯ или РОСТ. Отметим, что выбор функции зависит от того, какой тренд – линейный или экспоненциальный – более точно подходит для данного ряда цифр.
Спрогнозируйте курсы ценных бумаг на 3 недели вперед.
Скорректируйте график изменения курсов ценных бумаг с учетом спрогнозированных значений.
Для каждой ценной бумаги рассчитайте коэффициент корреляции, показывающий степень связи между курсами ценных бумаг и датами.
Таблица 5.7.
Изменения курсов ценных бумаг
| 1 ЦБ | |
| Курс на 7.01 | 100 |
| Как изменяется | Курс равен случайному числу от 98 до 105 |
| 2 ЦБ | |
| Курс на 7.01 | 500 |
| Как изменяется | Курс случайным образом изменяется на шаг от -3 до +6 |
| 3 ЦБ | |
| Курс на 7.01 | 200 |
| Как изменяется | Курс случайным образом изменяется от 99 до 103% к курсу предыдущей недели |
| 4 ЦБ | |
| Курс на 7.01 | 600 |
| Как изменяется | Курс с вероятностью в 50% возрастает на 5 единиц, с вероятностью 30% уменьшается на 3 единицы, и с вероятностью 20% остается неизменным. |
Расчет денежных средств на расчетном счете пенсионного фонда
1. Постановка задачи.
Руководство фирмы решило заключить договор с пенсионным фондом о дополнительном ежегодном взносе на личный счет каждого сотрудника. Согласно договору каждый сотрудник вначале года вносит определенную сумму на свой личный пенсионный счет и рассчитывает на 11 % годовых.
Задания:
· определить, сколько денежных средств накопится на пенсионном счете каждого сотрудника к его пенсионному возрасту?
· рассчитать, на сколько лет хватит накопленных денежных средств, если будущий пенсионер предполагает за их счет иметь дополнительную фиксированную ежемесячную прибавку к пенсии.
· получить различные сведения о сотрудниках фирмы.
2. Пояснения.
Создайте таблицу, состоящую из граф: «№ п/п» (1), «ФИО сотрудника» (2), «Дата рождения» (3), «Пол» (4), «Отдел» (5), «Должность» (6), «Возраст» (7), «Сумма ежегодного взноса» (8), «Количество лет до пенсии» (9), «Начальная сумма на счете» (10), «Накопленная сумма» (11), «Дополнительная ежемесячная прибавка» (12), «Срок получения прибавки» (13).
Данные для граф 1- 6, 8, 10 и 12 задайте самостоятельно.
Значение графы 7 рассчитайте с использование функций даты и времени.
Значение графы 9 рассчитайте, исходя из возраста выхода на пенсию мужчин и женщин, используя функцию ПРОСМОТР или ЕСЛИ.
Расчет денежных средств на расчетном счете в пенсионном фонде (графа 11) производится с помощью финансовой функции БС.
При расчете графы 13 используйте функцию КПЕР.
Для получения различных сведений о сотрудниках фирмы требуется использовать функции баз данных.
С помощью функции БДСУММ подсчитайте общую сумму ежегодных взносов сотрудников в пенсионный фонд.
С помощью функции БСЧЕТ подсчитайте количество сотрудников фирмы по отделам (например, в первом).
С помощью функции ДСРЗНАЧ определите средний возраст сотрудников фирмы, среднюю сумму ежегодного взноса бухгалтеров.
С помощью функции БИЗВЛЕЧЬ определите сотрудника, возраст которого > 30, ежегодный взнос >1000 руб.
С помощью функций ДМАКС, ДМИН определите фамилии сотрудников, имеющих максимальный, минимальный ежегодный взнос, среди ежегодных взносов, превышающих 1000 руб.
Используя автофильтр, получите список сотрудников, которым осталось до пенсии менее 10 лет.
Используя расширенный фильтр, создайте таблицу с графами «ФИО» и «Возраст» и поместите в нее сведения о сотрудниках, чей возраст превосходит средний.
1. Постановка задачи.
Фирма решила взять кредит размером 200 000$ сроком на 5 лет, погашать который (основной долг и проценты) намерена равномерными платежами в конце каждого года. Запросы на финансирование фирма направила в три банка, из которых пришли ответы с соответствующими условиями.
Рассчитайте на листе КРЕДИТ согласно условиям реальные суммы, которые может получить фирма по кредиту и размер ежегодных выплат.
На листе ПОГАШЕНИЕ составьте план погашения кредита по годам.
2. Пояснения.
На листе КРЕДИТ создайте таблицу, состоящую из граф: «№ п/п» (1), «Наименование банка» (2), «Сумма кредита» (3), «Плата за риск и обработку данных» (4), «Плата за издержки» (5), «Процентная ставка» (6), «Срок выплаты кредита» (7), «Сумма дополнительных выплат банку» (8), «Сумма годового платежа по кредиту» (9).
Данные для граф 1, 2, 3, 6 и 7 задайте самостоятельно.
Значение графы 4 составляет фиксированный процент от суммы кредита, задаваемый банком.
Значение графы 5 — фиксированная сумма, определенная банком за издержки при оформлении кредита.
Значение графы 8 определяется как сумма граф 4 и 5.
Значение графы 9 рассчитывается с помощью финансовой функции ПЛТ.
Определите, в каком банке выгоднее взять кредит фирме.
На листе ПОГАШЕНИЕ составьте план погашения кредита, предусмотрев следующие графы: «Номер периода» (1), «Сумма основного долга» (2), «Сумма процентов по кредиту» (3), «Накопленный долг» (4), «Накопленный процент» (5), «Баланс на конец периода» (6).
Значение граф 2-5 рассчитайте с помощью финансовых функций ОСПЛТ, ПРПЛТ, ОБЩДОХОД, ОБЩПЛАТ соответственно.
Значение графы 6 определяется как разность между суммой кредита и суммой накопленного долга.
Анализ поступления средств на счета вкладчиков коммерческого банка
1. Постановка задачи.
Вкладчики на свои счета вносят определенные суммы. Необходимо:
· рассчитать, через сколько лет текущий счет вкладчика вырастет до определенного значения при заданной фиксированной ставке;
· сформировать документ, отражающий следующую информацию:
| Показатель | Значение | Вкладчик |
| Минимальный размер выплат | ||
| Максимальный размер выплат | ||
| Минимальная сумма на счете в конце периода | ||
| Максимальная сумма на счете в конце периода | ||
| Минимальный срок | ||
| Максимальный срок |
· отобразить информацию только о тех вкладчиках, у которых ожидаемая сумма на счете в конце срока платежа, например, больше или равна 100 тыс. рублей и меньше 150 тыс. рублей;
· отобразить информацию только о тех вкладчиках, у которых размер регулярных платежей, выплачиваемых вкладчиком, больше некоторой постоянной величины Const1, количество периодов выплат меньше постоянной величины Const2, а размер годовой процентной ставки, например, равен 15%;
· сформировать документ, отражающий информацию только о тех вкладчиках, у которых сумма на счете в конце срока платежа больше среднего значения этого показателя по всем вкладчикам;
· распределить вкладчиков по группам в зависимости от срока платежа, исходя из следующей таблицы:
| Группа | Срок |
| 1 группа | До 1 года |
| 2 группа | От 1 года до 3 лет |
| 3 группа | От 3 лет до 5 лет |
| 4 группа | От 5 лет до 10 лет |
| 5 группа | Свыше 10 лет |
· подсчитать ожидаемую сумму на счете по каждой группе;
· для анализа поступлений средств на счета вкладчиков сформировать сводную таблицу ожидаемой суммы на счете во взаимосвязи со сроком и процентной ставкой:
| Группа (от срока платежа) | Процентная ставка |
| - Сумма на счете | |
| - Структура ожидаемой суммы по процентной ставке (по строке) | |
| - Структура ожидаемой суммы по группам (по столбцу) |
2. Пояснения.
Создайте таблицу, состоящую из граф: «№ п/п» (1), «Вкладчики» (2), «Размер регулярных платежей» (3), «Годовая процентная ставка» (4), «Периодичность выплат» (5), «Ожидаемая сумма на счете в конце срока платежа» (6), «Количество периодов выплат (лет)» (7).
Данные граф 1-6 задаются самостоятельно.
Значение графы 7 рассчитывается с помощью финансовой функции КПЕР.
Для решения поставленных задач необходимо использовать функции баз данных, средства автофильтра и расширенного фильтра, а также технологию построения сводных таблиц.