LINEBURG


<< Пред. стр.

страница 53
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

видационной стоимости имущества.
Функция АМОРУВ использует параметры:
• первоначальная стоимость имущества;
• дата приобретения (постановки на учет) имущества;
• первый период — дата окончания первого учетного периода эксплуатации иму­
щества;
• остаточная (ликвидационная) стоимость имущества в конце срока полной
амортизации;
• ставка (годовая норма амортизации), величина, обратная нормативному сро­
ку эксплуатации имущества;
• базис — используемый способ вычисления длительности учетного года (табл. 6.3).
Таблица 6 . 3 . Типы базисов

Базис Система дат
360 дней (метод NASD, принятый в США)
0
1 Фактический
3 365 дней в году
4 360 дней в году (Европейский метод)
440 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


ВНИМАНИЕ
Результат расчета функции АМОРУВ зависит от выбранного базиса, а также от установ­
ки в команде меню Сервис • Параметры на вкладке Вычисления параметра Система дат.
Если сделана установка Система дат — 1904, то дате 01.01.2002 соответствует число —
35795. Если эта установка не сделана, дате 01.01.2002 соответствует число — 37 257.

Последовательность действий.
1. Открыть рабочую книгу Анализ.xls с помощью команды меню Файл • Открыть.
2. Создать именованный блок ячеек для столбцов Дата постановки на учет на
листе ОС. Блок ячеек выделить вместе с заголовком столбца и выполнить ко­
манду меню Вставка • Имя • Создать по тексту в строке выше.
3. Вставить, если необходимо, новый лист с помощью команды меню Вставка •
Лист.
4. Переименовать новый лист с помощью команды меню Формат • Лист • Пе­
реименовать, лист — АМОРУВ.
5. Разместить на рабочем листе АМОРУВ параметры функции АМОРУВ и вспо­
могательные данные (рис. 6.2).

1 Инвентарный номер ОС
2 Наименование
3 Стоимость
4 Ликвидационная
стоимость
5 Дата приобретения
6 Первый период
7 Норма амортизации
8 Базис
9 Период расчета
10 АМРУВ

Р и с . 6 . 2 . Параметры функции АМОРУВ


6. Для параметра функции АМРУВ Инвентарный номер подготовить поле со
списком значений с помощью команды меню Данные • Проверка. Указать тип
данных — Список, источник — блок Инвентарный номер.
7. Для остальных параметров функции АМРУВ ввести расчетные формулы,
В1 — адрес ячейки, содержащей параметр Инвентарный номер ОС:
О Наименование — формула вывода наименования ОС:
=ПР0СМ0ТР(В1;Инвентарный_номер_ОС;Наименование)
О Стоимость — формула вывода начальной стоимости ОС:
=ПР0СМ0ТР(В1;Инвентарный_номер_0С; Первоначальная стоимость)
О Ликвидационная стоимость — формула вывода остаточной стоимости ОС:
=ПР0СМ0ТР(В1;Инвентарный_номер_0С; Остаточная стоимость)
О Дата приобретения — формула вывода даты постановки на учет ОС:
=ПР0СМ0ТР(В1;Инвентарный_номер_0С; Дата постановки на учет)
441
Среда моделирования Microsoft Excel


О Первый период — формула вывода времени завершения первого учетного
периода для ОС:
=КОНМЕСЯЦА(В5;12-МЕСЯЦ(В5))
где В5 — адрес ячейки, содержащей параметр Дата приобретения
О Норма амортизации — формула вывода ставки (нормы амортизации) для
ОС:
=ПР0СМ0ТР(В1;Инвентарный_номер_0С; Годовая норма амортизации)
О Базис — выполнить команду меню Данные • Проверка, указать тип дан­
ных — Список, источник — 0; 1; 3; 4.
8. Для параметра Период расчета данные заполняются по строке, начиная с 1.
Выделить ячейки в строке, выполнить команду меню Правка • Заполнить •
Прогрессия, указать тип прогрессии — Арифметическая, шаг 1, предельное
значение — 10.
9. Для ячейки результата ввести формулу вида:
=АМ0РУВ($В$3;$В$5;$В$6;$В$4;В9;$В$7;В8)
10. Для автоматического вычисления функции АМОРУВ для каждого учетного
периода (от 1 до последнего) следует выделить ячейку с формулой и скопи­
ровать ее в ячейки строки.

ВНИМАНИЕ
В формуле функции АМОРУВ для параметра Период расчета (ячейка В9) используется
относительная ссылка, поскольку при тиражировании формулы номер учетного периода
изменяется. Функция АМОРУВ выдает значение 0, когда номер учетного периода превы­
шает число периодов полной амортизации имущества.


11. Закрыть рабочую книгу с сохранением с помощью команды меню Файл •
Закрыть.

Функция АМОРУМ
Сумма амортизации имущества для каждого очередного учетного периода про­
порционально уменьшается на один и тот же коэффициент — постоянная дег-
рессивная амортизация. Для предпоследнего учетного периода норма амор­
тизации имущества вырастает до 50 процентов и для последнего учетного пе­
риода — до 100 процентов. Общая сумма амортизации, как правило, не равна
разности первоначальной и ликвидационной стоимости имущества. Функция
АМОРУМ использует такие же параметры, что и функция АМОРУВ. При рас­
чете АМОРУМ применяются коэффициенты, учитывающие общий срок экс­
плуатации имущества.
Последовательность действий.
1. Открыть рабочую книгу Анализ.xls с помощью команды меню Файл • Открыть.
2. На листе АМОРУВ добавить в новую строку формулу:
=АМ0РУМ($В$3;$В$5;$В$6;$В$4;В9;$В$7;$В$8)
442 Глава 6 Специальные информационные технологии анализа данных в Microsoft Excel


3 Размножить формулу по строке для вычисления функции АМОРУМ для всех
учетных периодов.
4. Закрыть рабочую книгу с сохранением с помощью команды меню Файл •
Закрыт.

Функция АМГД
Функция АМГД вычисляет для каждого учетного периода сумму амортизации
стоимости имущества, которая неравномерно снижается по мере увеличения но­
мера учетного периода. Расчетная формула суммы амортизации за учетный пе­
риод:
„, „ „ (Первоначальная стоимость - Ликвидационная стоимость)
АМГД = х
Время эксплуатации
(Время эксплуатации - Период +1) • 2
(Время эксплуатации +1)
Более упрощенный вариант расчета суммы амортизации очередного г'-го учетно­
го периода:
АМГД, =AMP-k,.
Сумма амортизации имущества за учетный период, соответствующая результа­
ту функции AMP, умножается на коэффициент, который зависит от порядково­
го номера (i) учетного периода. При этом сумма этих коэффициентов равна
нормативному количеству периодов эксплуатации имущества:


Годовая норма амортизации
;


Последовательность действий.
1. Открыть рабочую книгу Анализ-xls с помощью команды меню Файл • Открыть.
2. На листе АМОРУВ добавить в новую строку формулу:
=АМГД($В$3;$В$4;1/$В$7;В9)
3. Размножить формулу по строке для вычисления функции АМГД для всех
учетных периодов.
4. Закрыть рабочую книгу с сохранением с помощью команды меню Файл •
Закрыть.
Сравнение результатов расчета АМОРУВ, АМОРУМ и АМГД выполнено на приме­
ре (рис. 6.3).
Результат расчетов представлен в табл. 6.4 и на рис. 6.4.
443
Среда моделирования Microsoft Excel


1 Инвентарный номер 12000
ПК
2 Наименование
2300000
3 Стоимость
4 Ликвидационная
стоимость 0
5 Дата приобретения 01 09 96
6 Первый период 31 1296
7 Норма амортизации 0,1
8 Базис 1
9 Период расчета 1
Рис. 6.3. Данные для расчета функций АМОРУВ, АМОРУМ, АМГД

Таблица 6.4. Результаты функций АМОРУВ, АМОРУМ, АМГД

АМГД
АМОРУВ АМОРУМ
Период расчета
230 000,00р. 418 181,82р.
1 527 476,00р.
2 230 000,00р. 395 607,00р. 376 363,64р
230 000,00р. 334 545,45р.
3 296 705,00р.
4 230 000,00р. 222 529,00р. 292 727,27р.
5 230 000,00р. 166 897,00р. 250 909,09р.
6 230 000,00р. 125 173,00р. 209 090,91р.
7 230 000,00р. 93 879,00р. 167 272,73р.
230 000,00р.
8 140 819,00р. 125 454,55р.
230 000,00р 140819
9 83 636,36р.
10 153 333,00р. 0 41 818,18р.
2 109 904,00р. 2 300 000,00р.
ИТОГО 2 223 333,00р.


АМОРУВI ]АМГД АМОРУМ
6О0000,0Ср

500000,0ф -fv

400000,00?
\
ЗООООО.ООр

200000,0»)

ч
yflii п
ЮОООО.ООр

- р




Рис. 6.4. Результаты функций АМОРУМ, АМОРУВ, АМГД

Сравнение функций АМГД и AMP (рис. 6.5) осуществляется путем вычисления
для каждого учетного периода показателей:
444 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


Q (АМГД-АМР) — разность сумм амортизации для функций АМГД и AMP;
• (АМГД/АМР) - отношение АМГД к AMP.

| е = з АМГД-АМР Коэффициенг

250000,00р
2О0О00 00Р
150 000,00р
100 000,00р
50 000,00р
- Р
-SOOOOOOp
-100 000 0 *
-150000,0*
-200000,00?
-250000,0*


Рис. 6.5. Сравнение результатов функций АМГД и AMP

Функция ДДОБ
Функция ДДОБ вычисляет сумму амортизации за любой указанный период. Рас­
чет ведется по методу увеличенного процента амортизации со снижающегося
остатка стоимости имущества по формуле:
ДДОБ = {{Первоначальная стоимость - Ликвидационная стоимость) -
_, . Коэффицент
- Сумма амортизации накопленная) ^- .
Время эксплуатации
Для расчета суммы амортизации для указанного учетного периода учитывается
накопленная сумма амортизации.
Последовательность действий.
1. Открыть рабочую книгу Анализ.xls с помощью команды меню Файл • Открыть.
2. Вставить, если необходимо, новый лист с помощью команды меню Вставка •
Лист.
3 Переименовать новый лист с помощью команды меню Формат • Лист • Пе­
реименовать, лист — ДДОБ.
4. Разместить на рабочем листе ДДОБ параметры для функций ДДОБ и ПДОБ
и вспомогательные данные (рис. 6.6).

1 Инвентарный номер ОС
2 Наименование
3 Стоимость
4 Ликвидационная
стоимость
5 Норма амортизации
6 Коэффициент
7 Период расчета
8 Без переключений
Рис. 6.6. Параметры функции ДДОБ
445
Среда моделирования Microsoft Excel


5. Для параметра функции ДДОБ Инвентарный номер подготовить поле со спи­
ском с помощью команды меню Данные • Проверка. Указать тип данных —
Список, источник — блок Инвентарный номер.
6. Для остальных параметров функции ДДОБ ввести расчетные формулы, В1 —
адрес ячейки, содержащей параметр Инвентарный номер ОС:
О Наименование — формула вывода наименования ОС:
=ПР0СМ0ТР(В1;Инвентарный_номер_ОС;Наименование)
О Стоимость — формула вывода начальной стоимости ОС:
=ПР0СМ0ТР(В1;Инвентарный_номер_ОС; Первоначальная стоимость)
О Ликвидационная стоимость — формула вывода остаточной стоимости ОС:
=ПР0СМ0ТР(В1;Инвентарный_номер_0С; Остаточная стоимость)
О Норма амортизации — формула вывода ставки (годовой нормы амортиза­
ции) ОС
=ПР0СМ0ТР(В1;Инвентарный_номер_ОС; Годовая норма амортизации)
О Коэффициент — подготовить поле со списком с помощью команды меню
Данные • Проверка, указать тип данных — Список, источник — 0; 1; 1,5; 2;
2,5; 3.
О Период расчета — данные заполнять по строке. Первое значение — 1, вы­
делить ячейки строки, команда меню Правка • Заполнить • Прогрессия, тип —
Арифметический, шаг 1, предельное значение — 100.
О Без переключения — подготовить поле со списком значений с помощью
команды меню Данные • Проверка, указать тип данных — Список, источ­
ник — 0; 1.
7. Для ячейки результата ввести формулу вида:
=ДДОБ($В$3;$В$4;1/$В$5;В7;В6)
8. Размножить формулу по строке для автоматического вычисления функции
ДДОБ всех учетных периодов.
9. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Функция ПДОБ
Функция ПДОБ вычисляет накопленную сумму амортизации стоимости иму­
щества за указанный интервал учетных периодов, используя метод двойного
процента со снижающегося остатка стоимости. С помощью параметра функции
Без переключения выбирается алгоритм расчета:
• 1 — если не используется переключение на метод равномерного начисления амор­
тизации, даже если амортизация больше вычисленного снижающегося остатка;
• 0 — в противном случае.
Последовательность действий.
1. Открыть рабочую книгу Анализ-xls с помощью команды меню Файл • Открыть.
446 Глава 6 Специальные информационные технологии анализа данных в Microsoft Excel


2. На лист ДДОБ добавить в новую строку формулу для расчета накопленной
суммы амортизации, начиная с момента ввода имущества в эксплуатацию
•=ПДОБ($В$3;$В$4;1/$В$5;0;В7; $В$б;$В$8)
3. Размножить формулу по строке для автоматического вычисления функции
ПДОБ для всех учетных периодов.
4. Заполнить таблицу исходными данными (рис. 6.7) для сравнения функций
ДДОБ и ПДОБ.

Инвентарный номер 12000
Наименование ПК
Стоимость 2300000
Ликвидная стоимость 0
Норма амортизации 0,1
Коэффициент 2
Период расчета 1
Без переключений 1

Рис. 6.7. Исходные данные для функций ДДОБ и ПДОБ

5. Вычислить функции ДДОБ и ПДОБ для различных значений параметров
Коэффициент и Без переключения.
6. Вычислить в новой строке разность между значениями функции ПДОБ для
двух смежных периодов и сравнить результат со значением функции ДДОБ
для этого же периода при сохранении условий расчета:
ДД0Бп=ПД0Бп+1 - ПДОБп (п - номер учетного периода).
7 Изобразить графически результаты функций ПДОБ и ДДОБ (рис. 6.8).
8 Закрыть рабочую книгу с сохранением с помощью команды меню Файл •
Закрыть.

|е=1ДДОБ ПДОБ |
ЗОООООООр 1
250 000,00р -
200 00000р -
150000 00р -
ЮООООООр -
50 000,00р -
II О П п п „ .
О.ООр -
1 2 3 4 5 6 7 8 9 10

Рис. 6.8. Сравнение функций ДДОБ и ПДОБ


Информационная технология подбора
параметра
Команда меню Сервис • Подбор параметра обеспечивает вычисление значения
аргумента (параметра) для заданного значения функции методом последова-
447
Среда моделирования Microsoft Excel


тельных итераций. Предельное число итераций и относительная погрешность
результата подбора устанавливаются на вкладке Вычисления диалогового окна
Параметры, вызываемого с помощью команды Сервис • Параметры.
Подбор параметра является основным методом исследования области допусти­
мых значений для параметров модели. Если функционал имеет несколько пара­
метров, подобный анализ выполняется последовательно для каждого параметра
в отдельности, при этом задаваемое значение функции остается неизменным.
После подбора можно сравнить полученные результаты подбора с точки зрения
их реалистичности. Например, получено отрицательное значение числа перио­
дов амортизации или ставка процентов существенно превышает предельно до­
пустимую.
При подборе параметра допускается использование каскада взаимосвязанных
формульных выражений, связывающих значения различных ячеек, в том числе
находящихся на других листах или в других рабочих книгах. Существенное ог­
раничение для применения этого подхода — в каждый момент времени исследу­
ется только один параметр функции.
С помощью команды меню Сервис • Зависимости • Влияющие ячейки для ячей­
ки, содержащей формулу, можно установить состав исходных ячеек, которые
непосредственно или через другие ячейки влияют на конечный результат.
Информационная технология подбора параметра рассматривается на примере
встроенных функций по расчету кредитов и займов. Типовые расчеты по ставке
сложных процентов основаны на следующей формуле:

— (1 + г • type) + pv • (1 + r)h + fa = 0,
pmt • -
r
где pmt — сумма периодического платежа; г — процентная ставка за один учет­
ный период; type — тип выплат: 1 — в начале, 0 — в конце учетного периода; п —
число рассматриваемых учетных периодов; pv — «настоящая» стимость вклада;

<< Пред. стр.

страница 53
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Copyright © Design by: Sunlight webdesign