ФИНАНСОВЫЕ ФУНКЦИИ лаб работы. Лабораторная работа 1 Определение будущей стоимости на основе постоянной и переменной процентной ставки Цель работы
 Скачать 1.61 Mb.
|
Практические работы для аспирантовВАРИАНТ № 1 Расчет распределения прибыли по итогам работы за год 1. Постановка задачи В конце отчетного года организация имеет некоторую величину денежных средств N, которую необходимо распределить между сотрудниками в качестве премии. Распределение производится на основе оклада сотрудника и в соответствии со стажем его работы в данной организации. 2. Пояснения Требуется создать таблицу, состоящую из граф: «№ п/п» (1), «ФИО сотрудника» (2), «Дата приема на работу» (3), «Стаж работы» (4), «Оклад сотрудника» (5), «Модифицированный оклад» (6), «Премия сотрудника» (7). Данные граф 1, 2, 3 и 5 задайте самостоятельно. Значение графы 4 рассчитайте различными способами – с помощью функций даты и времени (ДНЕЙ360, ДОЛЯГОДА, ГОД и СЕГОДНЯ). Для приведения сотрудников к одному знаменателю рассчитывается промежуточный показатель – модифицированный оклад, зависящий от стажа работы сотрудника (если стаж работы не менее пяти лет, то размер модифицированного оклада равен двойному окладу, в противном случае модифицированный оклад равен окладу). При расчете графы 6 используйте функцию ЕСЛИ. Отдельно рассчитайте коэффициент распределения (К) как отношение всей суммы премиальных средств N к сумме всех модифицированных окладов. Данный коэффициент показывает, сколько рублей премии приходится на рубль модифицированного оклада. Премия каждого сотрудника определяется путем умножения величины модифицированного оклада на коэффициент распределения. Полученную премию следует округлить до целого. При расчете графы 7 используйте функцию ОКРУГЛ и абсолютную ссылку. Постройте круговую диаграмму процентного распределения премии между сотрудниками и точечный график зависимости премии от оклада. Скопировав таблицу на другой лист, обеспечьте показ формул в таблице. ВАРИАНТ № 2 Остатки денежных средств на валютных счетах 1. Постановка задачи Филиал банка работает с частными вкладчиками, имеющими валютные счета в четырех валютах. Необходимо получить на конец месяца остатки по всем валютным счетам в рублевом эквиваленте. 2. Пояснения Требуется создать таблицу, состоящую из граф: «№ п/п» (1), «ФИО вкладчика» (2), «Расчетный счет» (3), «Сумма в валюте» (4), «Код валюты» (5), «Остаток в рублях» (6). Данные граф 1, 2, 3 и 4 задайте самостоятельно. Вкладчики банка могут иметь от одного до четырех валютных счетов. Состояние счета оценивается и в валюте, и в рублях. Код валюты определяется исходя из расчетного счета клиента, состоящего из 20 символов. При этом код валюты занимает место с 6-го по 8-й символ в счете клиента. Для извлечения кода валюты из счета клиента используйте пять способов. Первый способ – с помощью функций ЦЕЛОЕ и ОСТАТ; второй – с применением функции ПСТР; третий – с помощью функций ПРАВСИМВ и ЦЕЛОЕ; четвертый – с использованием функций ЛЕВСИМВ и ОСТАТ; пятый – с помощью функций ПРАВСИМВ и ЛЕВСИМВ. Для расчета графы 6 используются функция ЕСЛИ и «Информационный справочник Банка России». Выдержка из данного документа приведена в таблице 16. Отдельно, с помощью функции СУММЕСЛИ, рассчитайте рублевый эк вивалент по каждой валюте. Таблица 16 – Выдержка из «Информационного справочника Банка России»
1Полные текущие сведения о курсе валют можно найти на странице официального сайта ЦБ РФ www.cbr.ru/currency_base/daily.asp, раскрыв гиперссылку «Официальные курсы валют на заданную дату, устанавливаемые ежедневно». Таблицу с курсами валют можно выделить и скопировать, вставив ее в документ Excel. ВАРИАНТ № 3 Обменный пункт валюты 1. Постановка задачи Для операциониста обменного пункта валют требуется разработать таб лицу купли-продажи валют и рассчитать прибыль обменного пункта. 2. Пояснения Создайте таблицу, состоящую из граф: «Код валюты» (1), «Наименование валюты» (2), «Количество валюты купленной» (3), «Количество валюты проданной» (4), «Официальный курс валюты ЦБ» (5), «Курс покупки» (6), «Курс продажи» (7), «Стоимость проданной валюты» (8), «Стоимость купленной валюты» (9), «Стоимость проданной валюты по официальному курсу» (10), «Стоимость купленной валюты по официальному курсу» (11), «Прибыль от покупки валюты» (12), «Прибыль от продажи валюты» (13), «Общая прибыль» (14), «Налог на прибыль» (15), «Прибыль обменного пункта» (16). Данные для граф 1, 3 и 4 задайте самостоятельно. Значение граф 2 и 5 определите с использованием дополнительной таб лицы «Информационный справочник Банка России» (см. таблицу 16). Графы 6 и 7 рассчитайте в соответствии с инструкцией (маржа составляет не более 10 %). Предположите, что курс покупки и курс продажи меняется в течение дня 4 раза. При расчете графы 14 следует учесть, что доходом является положительная разница продажи, отрицательная разница покупки; расходом является положительная разница покупки, отрицательная разница продажи. Общую прибыль рассчитать с использованием функции ЕСЛИ. Графа 15 «Налог на прибыль» считается как 20 % от общей прибыли. Подсчитайте общую сумму по графам 14, 15 и 16. ВАРИАНТ № 4 Налоговые отчисления в пенсионный фонд 1. Постановка задачи Рассчитайте обязательные взносы в пенсионный фонд за первый квартал текущего года по каждому сотруднику и по предприятию в целом. 2. Пояснения Создайте таблицу, состоящую из граф: «№ п/п» (1), «ФИО сотрудника» (2), «Дата рождения» (3); «Оклад» (4); «База для начисления страховых взносов за квартал» (5); Отчисления: «Для лиц 1966 г. р. и старше» (6); «Для лиц 1967 г. р. и моложе – страховая часть трудовой пенсии» (7); «Для лиц 1967 г. р. и моложе – накопительная часть трудовой пенсии» (8); «Итого отчислений» (9). Данные граф 1, 2, 3 и 4 задаются самостоятельно. Значение графы 5 вычисляется как утроенное значение графы 4. Графы 6, 7 и 8 вычисляются с помощью функции ЕСЛИ, ставки налогов содержатся в приложении 13. ВАРИАНТ № 5 Динамика налоговых поступлений в бюджет 1. Постановка задачи Требуется получить прогноз поступлений налогов в бюджет на ближайшие три года, используя известные значения предыдущих лет. Сумма поступаемых в бюджет налогов зависит от количества налогоплательщиков данного региона, которое зависит от количества зарегистрированных фирм. 2. Пояснения Создайте таблицу, состоящую из граф: «№ п/п» (1), «Год» (2), «Количество зарегистрированных фирм» (3), «Количество плательщиков» (4), «Поступления налогов в бюджет» (5). Все графы таблицы заполните данными за предыдущие пять лет. Выяснить с помощью соответствующих линий тренда и коэффициентов достоверности аппроксимации (детерминации) какая из статистических функций ТЕНДЕНЦИЯ или РОСТ лучше подходит для прогноза. Спрогнозируйте количество зарегистрированных фирм на следующие три года, определите количество налогоплательщиков в этих фирмах и рассчитайте количество поступлений налогов в бюджет от налогоплательщиков с помощью найденных уравнений регрессии. Проанализируйте результат, построив различные графики. ВАРИАНТ № 6 Анализ временных рядов 1. Постановка задачи Дано изменение курсов четырех ценных бумаг в течение 25 недель, начиная с 7 января текущего года. Необходимо спрогнозировать стоимость ценных бумаг на следующие три недели и оценить предсказуемость курса ценной бумаги с использованием коэффициента корреляции. 2. Пояснения Создайте таблицу, состоящую из граф: «№ п/п» (1), «Дата» (2), «Курс четырех ценных бумаг» (3-6). Рассчитайте курсы четырех ценных бумаг в соответствии с условиями, заданными в таблице. При моделировании курсов ценных бумаг используйте функции СЛЧИС, СЛУЧМЕЖДУ, полученные значения округлите до целого. Полученную таблицу значений ценных бумаг скопируйте на другой лист (используя команду «Специальная вставка»), так как функции СЛЧИС и СЛУЧМЕЖДУ будут пересчитываться с каждой операцией, и цифры будут изменяться. Постройте график изменения стоимости каждой ценной бумаги, добавьте линии тренда и выведите величины достоверности аппроксимации. По коэффициентам достоверности определите, какую прогнозную функцию следует использовать – ТЕНДЕНЦИЯ или РОСТ для каждой ценной бумаги. Отметим, что выбор функции зависит от того, какой тренд – линейный или экспоненциальный – более точно подходит для данного ряда цифр. Спрогнозируйте курсы ценных бумаг на три недели вперед. Скорректируйте график изменения курсов ценных бумаг с учетом спрогнозированных значений. Для каждой ценной бумаги рассчитайте коэффициент корреляции, показывающий степень связи между курсами ценных бумаг и датами. Изменения курсов ценных бумаг:
ВАРИАНТ № 7 Расчет денежных средств на расчетном счете пенсионного фонда 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, ежегодный взнос больше 1 000 руб. С помощью функций ДМАКС, ДМИН определите фамилии сотрудников, имеющих максимальный, минимальный ежегодный взнос, среди ежегодных взносов, превышающих 1 000 руб. Используя расширенный фильтр, создайте таблицу с графами «ФИО» и «Возраст» и поместите в нее сведения о сотрудниках, чей возраст превосходит средний. ВАРИАНТ № 8 Выдача и погашение кредита 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). Значение граф 3-5 рассчитайте с помощью финансовых функций ПЛТ, ПРПЛТ, ОСПЛТ соответственно. Значение графы 6 определяется как разность между суммой кредита и суммой основного платежа. ВАРИАНТ № 9 Анализ поступления средств на счета вкладчиков коммерческого банка 1. Постановка задачи Вкладчики на свои счета вносят определенные суммы. Необходимо:
2. Пояснения Создайте таблицу, состоящую из граф: «№ п/п» (1), «Вкладчики» (2), «Размер регулярных платежей» (3), «Годовая процентная ставка» (4), «Периодичность выплат» (5), «Ожидаемая сумма на счете в конце срока платежа» (6), «Количество периодов выплат (лет)» (7). Данные граф 1–6 задаются самостоятельно. Значение графы 7 рассчитывается с помощью функции КПЕР. Для решения поставленных задач используйте функции баз данных, средства расширенного фильтра и технологию построения сводных таблиц. ВАРИАНТ № 10 Анализ структуры кредитных вложений коммерческого банка 1. Постановка задачи По имеющимся таблицам кредитных вложений на основе качества залога по трем филиалам необходимо проанализировать структуру кредитных вложений в целом по банку. А также: • сформировать по каждому филиалу следующий выходной документ:
2. Пояснения На трех листах рабочей книги для каждого филиала создайте таблицу, состоящую из граф: «Кредитные вложения банка» (1), «Сумма» (2). Данные граф 1 и 2 задаются самостоятельно. Для решения задачи используются следующие кредитные вложения:
При выполнении задания должны быть использованы команды расширенного фильтра, подведение промежуточных итогов и функции баз данных. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||