Laboratornye_raboty_semestr2_modul2

61

Рис.32 Расчет суммы вклада и коэффициента наращивания

Для создания таблицы с данными необходимо записать заголовки столбцов, затем

– в ячейку В8 ввести формулу =В4, в ячейку С8 – формулу =В5. Для расчета процента от 10% до 20% с шагом 1% в ячейку А8 введем значение 10% (или формулу =В3); в ячейку А9 – формулу =A8+0,01 и копируем ее вниз по столбцу, пока не получим значение 20%. Далее выделяем диапазон ячеек A8:С18,

выбираем команду Данные/Таблица подстановки и в поле Подставлять значения по строкам в: вводим адрес ячейки (В3) с процентной ставкой (Рис.33).

Рис.33 Диалоговое окно Таблица подстановки (с одной переменной)

После нажатия на ОК получим таблицу, представленную на Рис.34.

Рис.34 Таблица подстановки с одной переменной

II. Использование таблицы подстановки с двумя переменными

Рассмотрим пример использования надстройки Таблица подстановки, для прогнозирования суммы вклада при вариации процентной ставки и срока вклада. Исходные данные и формулы для расчета суммы вклада и коэффициента наращивания так же, как в предыдущем примере, приведенном на Рис.32. Для

62

создания таблицы данных для расчета суммы выплат по заданному проценту и заданному сроку вклада нужно по столбцу проставить значения процентов в нужном диапазоне, а по строке – значения сроков вклада. Это будут переменные величины. Для расчета процента от 10% до 20% с шагом 1% в ячейку А10 введем значение 10%; в ячейку А11 – формулу =A10+0,01 и копируем ее вниз по столбцу, пока не получим значение 20%. В ячейку В9 введем значение 5 (сроки вкладов будут варьироваться от 5 до 15 лет с шагом 5). Затем на пересечении строки и столбца записать формулу =В5, где в ячейке В5 записана формула для расчета суммы вклада (функция БЗ). Выделяется диапазон ячеек A9:D20 команда

Данные/Таблица подстановки. В поле Подставлять значения по столбцам в

вводим адрес ячейки (В2) – срок вклада, а в поле Подставлять значения по строкам в вводим адрес ячейки (В3) с процентной ставкой (Рис.35).

Проставляем адреса ячеек и нажимаем ОК

Рис.35 Диалоговое окно Таблица подстановки (с двумя переменными)

Получим результат в виде таблицы данных (Рис.36).

Рис.36 Таблица подстановки с двумя переменными

63

Задание

1.Вас просят дать в долг Р грн. и обещают возвращать по А грн. в течение n лет. Используя функцию ПС и сценарии определить максимальную процентную ставку (с точностью до одного знака после запятой) под которую выгодно дать деньги в долг.

Вариант	n	Р	А	Вариант	n	Р	А
1	7	170000	30000	16	9	250000	40000
2	8	200000	31000	17	12	370000	35000
3	9	220000	33000	18	8	420000	65000
4	10	300000	34000	19	9	650000	80000
5	11	350000	41000	20	4	220000	80000
6	7	210000	45000	21	4	150000	40000
7	8	250000	37000	22	7	240000	40000
8	9	310000	40000	23	9	360000	50000
9	10	320000	42000	24	10	450000	60000
10	11	360000	41000	25	12	550000	60000
11	15	480000	50000	26	15	1E+06	100000
12	7	140000	22000	27	5	400000	90000
13	5	230000	50000	28	7	300000	50000
14	13	450000	55000	29	7	100000	20000
15	10	280000	30000	30	10	175000	25000

2.Используя надстройку Таблица подстановки, создать систему для прогнозирования суммы вклада при вариации процентной ставки.

3.Используя надстройку Таблица подстановки, создать систему для прогнозирования суммы вклада при вариации процентной ставки и срока вклада.

Вариант	Размер	Срок	Процентная ставка	Границы сроков
	вклада	вклада
1	350 000	10	От 10% до 20% с	От 10 до 14 с шагом 1
			шагом 1%
2	185 000	9	От 9% до 18% с	От 9 до 17 с шагом 2
			шагом 0,9%
3	336 000	9	От 9% до 14% с	От 9 до 13 с шагом 1
			шагом 0,5%

64

Вариант	Размер	Срок	Процентная ставка		Границы сроков
	вклада	вклада
4	183 600	8	От 8%	до 18% с	От 8 до 16 с шагом 2
			шагом 1%
5	322 000	8	От 8%	до 16% с	От 8 до 20 с шагом 3
			шагом 0,8%
6	182 200	7	От 7%	до 14% с	От 7 до 15 с шагом 2
			шагом 0,7%
7	308 000	7	От 7%	до 17% с	От 7 до 11 с шагом 1
			шагом 1%
8	180 800	6	От 6%	до 12% с	От 6 до 14 с шагом 2
			шагом 0,6%
9	294 000	6	От 6%	до 16% с	От 6 до 18 с шагом 3
			шагом 1%
10	179 400	5	От 5%	до 10% с	От 5 до 13 с шагом 2
			шагом 0,5%
11	280 000	7	От 5%	до 15% с	От 7 до 11 с шагом 1
			шагом 1%
12	178 000	6	От 4%	до 14% с	От 6 до 14 с шагом 2
			шагом 1%
13	266 000	8	От 4% до	9% с шагом	От 8 до 12 с шагом 1
			0,5%
14	176 600	7	От 3%	до 13% с	От 7 до 15 с шагом 2
			шагом 1%
15	252 000	9	От 5%	до 12% с	От 9 до 21 с шагом 3
			шагом 0,7%
16	175 200	8	От 4%	до 15% с	От 8 до 16 с шагом 2
			шагом 1,1%
17	238 000	10	От 7%	до 14% с	От 10 до 14 с шагом 1
			шагом 0,7%
18	173 800	9	От 6%	до 12% с	От 9 до 21 с шагом 3
			шагом 0,6%
19	224 000	9	От 8%	до 19% с	От 9 до 17 с шагом 2
			шагом 1,1%
20	172 400	8	От 7%	до 15% с	От 8 до 16 с шагом 2
			шагом 0,8%
21	210 000	8	От 10% до 15% с		От 8 до 12 с шагом 1
			шагом 0,5%
22	171 000	7	От 9%	до 19% с	От 7 до 15 с шагом 2
			шагом 1%
23	196 000	4	От 11% до 22% с		От 4 до 16 с шагом 3
			шагом 1,1%
24	169 600	3	От 11% до 21% с		От 3 до 7 с шагом 1
			шагом 1%

65

Вариант	Размер	Срок	Процентная ставка	Границы сроков
	вклада	вклада
25	182 000	5	От 10% до 16% с	От 5 до 13 с шагом 2
			шагом 0,6%
26	168 200	4	От 9% до 16% с	От 4 до 16 с шагом 3
			шагом 0,7%
27	168 000	6	От 10% до 14% с	От 6 до 14 с шагом 2
			шагом 0,4%
28	166 800	5	От 8% до 18% с	От 5 до 17 с шагом 3
			шагом 1%
29	154 000	8	От 4% до 18% с	От 8 до 16 с шагом 2
			шагом 1,4%
30	165 400	7	От 7% до 13% с	От 7 до 19 с шагом 3
			шагом 0,6%
31	350 000	10	От 10% до 20% с	От 10 до 14 с шагом 1
			шагом 1%
32	185 000	9	От 9% до 18% с	От 9 до 17 с шагом 2
			шагом 0,9%
33	336 000	9	От 9% до 14% с	От 9 до 13 с шагом 1
			шагом 0,5%
34	183 600	8	От 8% до 18% с	От 8 до 16 с шагом 2
			шагом 1%

Лабораторная работа №23

Использование финансовых функций для расчета амортизации основных средств в EXCEL

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

В Excel имеется пять функций для вычисления величины амортизации. Функция АПЛ вычисляет величину амортизации по линейному методу;

функция АСЧ использует метод «суммы (годовых) чисел» для вычисления суммы амортизации; функции ДДОБ и ПУО используют метод ускоренного начисления амортизации; функция ФУО вычисляет величину амортизации методом фиксированного уменьшения остатка.

66

Условие задачи (исходные данные) будет одинаковым для всех 5 примеров. Изменяться будет только метод расчета амортизации.

Пример 1

Стоимость актива составляет 10 000 грн., срок эксплуатации актива – 5 лет, а ликвидационная стоимость – 2 000 грн. Создайте таблицу начисления износа, в которой бы указывались суммы ежегодной амортизации и значения остаточных стоимостей в конце каждого года, применяя один из методов начисления амортизации:

–линейный метод;

–суммы (годовых) чисел;

–фиксированного уменьшения остатка;

–двойного уменьшаемого остатка;

–двойного процента со снижающегося остатка.

1.Для того решения задачи с использованием линейного метода начисления амортизации, необходимо использовать функцию АПЛ.

Функция АПЛ возвращает величину амортизации актива за один период, рассчитанную линейным методом. Линейный метод еще называют методом равномерного начисления износа. Он заключается в том, что из суммы стоимости актива вычитается его остаточная стоимость; полученное значение делится на количество лет эксплуатации. Таким образом, величина амортизации на протяжении всего периода эксплуатации актива постоянна (Рис.37).

67

Рис.37. Исходные данные и решение задачи с использованием функции АПЛ

Функция АПЛ имеет следующий синтаксис:

АПЛ (нач_стоимость; ост_стоимость; время_эксплуатации).

Аргумент нач_стоимость – это первоначальная стоимость актива. Аргумент ост_стоимость – это остаточная стоимость актива.

Аргумент время_эксплуатации – это период амортизации или срок эксплуатации актива.

На Рис.37 (ячейки B9:B13) приведен расчет величины амортизации за каждый период по формуле =АПЛ($D$3;$D$4;$D5$).

Остаточная стоимость актива определяется как разность между первоначальной стоимостью и величиной амортизации, начисленной за предыдущие периоды. Например, в конце первого года эксплуатации остаточная стоимость актива вычисляется по формуле

=$D$3-СУММ($В$9:В9): в конце второго года по формуле

=$D$3-СУММ(В$9:В10) и т. д.

Как видно из Рис.37, уменьшение остаточной стоимости актива происходит по линейному закону.

2. Чтобы решить задачу с использованием метода «суммы (годовых) чисел», необходимо использовать функцию АСЧ.

68

Функция АСЧ возвращает величину амортизации актива за данный период, рассчитанную методом «суммы (годовых) чисел». В этом методе суммируются порядковые номера лет, в течение которых актив находится в эксплуатации. Допустим, период эксплуатации актива составляет 5 лет, тогда сумма всех лет эксплуатации актива равна 15 (или 1+2+3+4+5). Затем разница между первоначальной

иостаточной стоимостью актива делится на это число и умножается на количество оставшихся лет периода эксплуатации. Например, в первый год это будет число 5, во второй – 4 и т. д. (Рис.38).

Синтаксис функции АСЧ имеет следующий вид:

АСЧ(нач_стоимость;ост_стоимость;время_эксплуатации; период)

Аргументы нач_стоимость и ост_стоимость – это соответственно первоначальная

иостаточная стоимость актива. Аргумент время_эксплуатации – это период амортизации актива или полный срок его эксплуатации. Аргумент период – это текущий период.

На Рис.38 величина амортизации за первый период (ячейка В9) вычисляется по формуле = АСЧ($D$3;$D$4;$D$5;A9).

Рис.38. Исходные данные и решение задачи с использованием функции АСЧ

Чтобы вычислить величину амортизации за другие периоды, скопируйте эту формулу в ячейке В10:В13. Остаточная стоимость актива определяется как разность

69

между первоначальной стоимостью и величиной амортизации, начисленной за предыдущие периоды. Например, в конце первого года эксплуатации остаточная стоимость актива вычисляется по формуле =$D$3-СУММ(В$9:В9), в конце второго года по формуле =$D$3-СУММ(В$9:В10) и т. д. В конце срока эксплуатации актива его стоимость становится равной остаточной стоимости. Ячейка С13 содержит формулу =$D$3-СУММ(В$9:В13), которая возвращает остаточную стоимость актива.

3. Чтобы решить задачу с использованием метода фиксированного уменьшения остатка, необходимо использовать функцию ФУО.

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

При вычислении амортизации по методу фиксированного уменьшения остатка используется значение суммарной амортизации за предшествующие периоды

(рис. 5.3).

Величина амортизации за текущий период вычисляется как разность между первоначальной стоимостью актива и суммарной амортизацией за предшествующие периоды, умноженная на фиксированную процентную ставку, которая вычисляется по формуле

Ставка=1–((остаточная стоимость/первоначальная стоимость)(1/время эксплуатации)).

Функция ФУО имеет следующий синтаксис:

ФУО(нач_стоимость;ост_стоимость;время_эксплуатации;период;месяцы).

Аргументы нач_стоимость и ост_стоимость – это соответственно первоначальная и остаточная стоимость актива. Аргумент время_эксплуатации – это период амортизации актива или полный срок его службы. Аргумент период – это текущий период, для которого необходимо вычислить величину амортизации. Последний аргумент месяцы задает количество месяцев первого года. Этот аргумент является необязательным и его следует задавать только в том случае,

70

когда покупка актива была совершена не в начале года, а позже. По умолчанию значение этого аргумента принимается равным 12.

На Рис.39 величина амортизации за первый период (ячейка В9) вычисляется по формуле =ФУО($D$3;$D$4;$D$5;A9). Чтобы найти величину амортизации за другие периоды, скопируйте эту формулу в диапазон В10:В13. Остаточная стоимость актива определяется по тем же формулам, что и ранее. Например, ячейка С13 содержит следующую формулу: =$D$3-СУММ($В$9:В13). Обратите внимание, что остаточная стоимость актива в ячейке С13 на 3 грн. больше, чем остаточная стоимость, определенная ранее (на Рис.39 точное значение остаточной стоимости содержится в ячейке D4). Так получилось потому, что фиксированная ставка, которую вычисляет функция ФУО, округляется до трех цифр, т. е. вместо значения 0,27522, вычисленного по формуле =1-((D4/D3)^(1/D5)) (которая находится в ячейке В19 на Рис.39), в вычислениях используется ставка 0,275.

Рис.39. Использование функции ФУО для решения задачи

Использование аргумента месяцы функции ФУО при вычислении величины амортизации актива позволяет учесть время покупки актива. Так, если покупка сделана не в январе, а, например, в июне, то для вычисления величины