Информационные системы и технологии в финансах. Практическое занятие . Расчет амортизации

Цель занятия: освоить приемы работы с функциями Excel, предназначенными для расчета амортизации и способами организации элементов пользовательского интерфейса на рабочих листах.

План занятия:

Создать на листе Excel исходную таблицу.

Заполнить расчетный блок формулами и настроить ячейки для выбора данных в соответствии с указаниями, приведенными ниже.

Самостоятельно создать расчетные блоки для функций расчета амортизации.

Построить динамический график для сравнения методов, применяемых в исследуемых функциях.

Порядок выполнения работы

Создайте новый документ Exсel и в нем заполните приведенную ниже таблицу, начиная с ячейки А1.

Инвентарный номер

Наименование

Начальная стоимость

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

Дата приобретения

Дата постановки на учет

Остаточная стоимость

10010

ПК

17 770р.

20,0%

30.09.99

01.10.99

0

20010

ПК

18 700р.

20,0%

01.04.96

01.10.00

0

12000

ПК

2 300 000р.

10,0%

01.05.96

01.09.96

0

20001

Здание

120 000р.

1,0%

09.05.00

10.05.00

0

25001

Сооружение

125 000р.

5,0%

01.04.00

01.06.00

0

56789

Машина

250 000р.

10,0%

01.05.00

01.05.00

0

Начиная с ячейки А9, создайте таблицу для ввода параметров функции АМР:

Инвентарный номер

Наименование

Стоимость

Ликвидационная стоимость

Время амортизации

АМР

В ячейке В9 создадим раскрывающийся список для ввода данных об инвентарном номере. Для этого необходимо выделить ячейку В9 и выбрать команду меню Данные – Проверка. Указать тип данных – Список. Источник – блок ячеек А2-А7, содержащий инвентарные номера (см. рис).

Для остальных ячеек ввести расчетные формулы:

В10: ПРОСМОТР(B9;A2:A7;B2:B7) – Возвращает значение из строки, из столбца или из массива. В данном случае она показывает название компонента с данным инвентарным номером.

В11: ПРОСМОТР(B9;A2:A7;C2:C7) – возвращает цену выбранного компонента.

В12: ПРОСМОТР(B9;A2:A7;G2:G7) – возвращает ликвидационную стоимость из исходной таблицы

В13: 1/ПРОСМОТР(B9;A2:A7;D2:D7) – возвращает величину, обратную норме амортизации. Эта величина используется для вычисления значения функции АМР.

В14: АМР(B11;B12;B13) – вычисляет амортизацию.

Проверьте работу расчетного блока, выбирая в ячейке В9 различные инвентарные номера.

Создадим расчетный блок для вычисления функции АМОРУВ.

Объедините ячейки А18 и В18 и введите в них заголовок: Функции АМОРУВ и АМОРУМ.

Создайте заготовку таблицы, начиная с ячейки А19.

Инвентарный номер

Наименование

Стоимость

Ливидационная стоимость

Дата приобретения

Первый период

Норма амортизации

Базис

Период расчета

В ячейке В19 создадим раскрывающийся список для ввода данных об инвентарном номере. Для этого необходимо выделить ячейку В19 и выбрать команду меню Данные – Проверка. Указать тип данных – Список. Источник – блок ячеек А2-А7, содержащий инвентарные номера (см. рис).

В остальные ячейки поместите расчетные формулы:

В20: ПРОСМОТР(B19;A2:A7;B2:B7)

В21: ПРОСМОТР(B19;A2:A7;C2:C7)

В22: ПРОСМОТР(B19;A2:A7;G2:G7)

В23: ПРОСМОТР(B19;A2:A7;E2:E7)

В24: КОНМЕСЯЦА(B23;12-МЕСЯЦ(B23)) – Функция МЕСЯЦ возвращает месяц в дате, заданной в числовом формате. Месяц возвращается как целое число в диапазоне от 1 (январь) до 12 (декабрь). Возвращает числовой формат последнего дня месяца, отстоящего на указанное количество месяцев от начальной даты. Функция КОНМЕСЯЦА используется для вычисления даты вступления в силу или даты платежа, которая приходится на конец месяца.

В25: ПРОСМОТР(B19;A2:A7;D2:D7)

В26: Создадим раскрывающийся список. Выберите команду меню Данные – Проверка, укажите тип данных – Список, источник – значения 0, 1, 3, 4.

Создадим таблицу для вычисления амортизации по периодам для искомых функций, начиная с ячейки А27. Таблица будет иметь вид:

Период расчета

1

2

3

4

5

6

7

8

Аморув

Аморум

В строку АМОРУВ добавьте формулу

АМОРУВ($B$21;$B$23;$B$24;$B$22;B27;$B$25;$B$26)

И размножьте ее по всем ячейкам строки.

В строку АМОРУМ добавьте формулу

АМОРУМ($B$21;$B23;$B$24;$B$22;B27;$B$25;$B$26)

И размножьте ее по всем ячейкам строки.

Проверьте работу расчетного блока, выбирая в ячейке В19 различные инвентарные номера.

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

Нажмите кнопку добавления диаграммы.

Выберите тип: Обычная гистограмма.

Выберите вкладку РЯД.

Нажмите кнопку ДОБАВИТЬ. В поле ЗНАЧЕНИЯ укажите ряд значений функции АМОРУВ. В поле ПОДПИСИ ПО ОСИ Х укажите ряд номеров периодов (1, 2, …).

Нажмите кнопку ДОБАВИТЬ. В поле ЗНАЧЕНИЯ укажите ряд значений функции АМОРУМ. В поле ПОДПИСИ ПО ОСИ Х укажите ряд номеров периодов (1, 2, …).

Нажмите кнопку ДАЛЕЕ и введите подписи по осям. Выберите размещение диаграммы на текущем листе.

Разместите диаграмму таким образом, чтобы она была видна одновременно с расчетными данными.

Проверьте автоматическое изменение диаграммы при выборе в ячейке В19 различных инвентарных номеров.

Сделайте вывод о различии алгоритмов функций АМОРУВ и АМОРУМ.

Контрольные вопросы:

Как использовать команду "Проверка"?

Каков синтаксис функции "Просмотр"?

Какие функции служат для вычисления амортизации и каков их синтаксис?

Метки: