Лабораторная работа ИТ использования встроенных функций по дисциплине Информационные технологии в профессиональной деятельности
Лабораторная работа №18
Информационные технологии использования встроенных функций
Цель:научиться находить оптимальное решение задачи при помощи встроенных функций MSExcel.
Теоретическая часть
Информационные технологии анализа данных на базе встроенных функций рассмотрены на примере амортизации имущества. Любое имущество (ОС, НМА, малоценные быстроизнашивающиеся предметы) имеют начальную стоимость, и приобретается в определенный момент времени, ставится на учет для правильного начисления амортизации. Стоимость имущества амортизируется в течение заданного нормативного срока его эксплуатации согласно определенному алгоритму восстановления первоначальной стоимости. Обычно сумма амортизации рассчитывается для каждого учетного периода в течение нормативного срока эксплуатации имущества, которое включает несколько учетных периодов.
Функция АМРпредназначена для расчета суммы амортизации за каждый учетный период. Сумма является постоянной величиной для всех периодов эксплуатации имущества. При расчете используется метод линейной (равномерной) амортизации по всем периодам учета. Сумма амортизации имущества за нормативный срок эксплуатации равняется разности первоначальной и ликвидационной стоимости. В модели расчета функции АРМ фактическая дата приобретения имущества не учитывается.
Параметры функции АМР:
первоначальная стоимость имущества;
остаточная стоимость имущества – ликвидационная стоимость имущества в конце периода амортизации (может быть любой, в том числе и 0);
нормативный срок эксплуатации имущества – количество учетный периодов за период полной амортизации имущества.
Сумму амортизации за учетный период можно рассчитать для выбираемого объекта или для всех сразу. В первом случае исходный список (база данных) MicrosoftExcel остается неизменным по структуре данных, но требуется обеспечить автоматизацию выбора всех связанных с объектом параметров для расчета суммы амортизации. Во втором случае существенно увеличивается объем информации, хранимой в рабочей книге, но при этом представлены результаты расчета по всем объектам учета.
Инвентарный номер ОС для расчета суммы амортизации выбирается из поля со списком. После его выбора автоматически выводятся другие параметры, и выполняется расчет функции АРМ.
Функция АМОРУВвычисляет величину суммы амортизации для каждого учетного периода. Эта сумма является постоянной величиной для всех периодов, кроме последнего. Сумма амортизации для последнего учетного периода пропорциональна времени эксплуатации имущества в течение первого года. Общая сумма амортизации, как правило, не равняется разности первоначальной и ликвидационной стоимости имущества.
Функция АМОРУВ использует параметры:
первоначальная стоимость имущества;
дата приобретения (постановки на учет) имущества;
первый период – дата окончания первого учетного периода эксплуатации имущества;
остаточная (ликвидационная) стоимость имущества в конце срока полной амортизации;
ставка (годовая норма амортизации), величина, обратная нормативному срокуэксплуатации имущества;
базис– используемый способ вычисления длительности учетного года (табл. 1).
Таблица 1. Типы базисов
Базис Система дат
0 360 дней (метод NASD, принятый в США)
1 Фактический
3 365 дней в году
4 360 дней в году (Европейский метод)
Функция АМОРУМ. Сумма амортизации имущества для каждого очередного учетного периода пропорционально уменьшается на один и тот же коэффициент– постоянная дегрессивная амортизация. Для предпоследнего учетного периода норма амортизации имущества вырастает до 50 процентов и для последнего учетного периода– до 100 процентов. Общая сумма амортизации, как правило, не равна разности первоначальной и ликвидационной стоимости имущества. Функция АМОРУМ использует такие же параметры, что и функция АМОРУВ. Прирасчете АМОРУМ применяются коэффициенты, учитывающие общий срок эксплуатации имущества.
Функция ДДОБ вычисляет сумму амортизации за любой указанный период. Расчет ведется по методу увеличенного процента амортизации со снижающегося остатка стоимости имущества по формуле:
Для расчета суммы амортизации для указанного учетного периода учитывается накопленная сумма амортизации.
Функция ПДОБ вычисляет накопленную сумму амортизации стоимости имущества за указанный интервал учетных периодов, используя метод двойного процента со снижающегося остатка стоимости. С помощью параметра функции Без переключения выбирается алгоритм расчета:
1– если не используется переключение на метод равномерного начисления амортизации, даже если амортизация больше вычисленного снижающегося: остатка;
0– в противном случае.
Практическая часть
Откройте Вашу рабочую книгу Excel.
Выполните переименование чистого листа на ОС.
На рабочем листе ОС подготовьте список (БД) – табл.2.
Выполните создание именованных блоков ячеек для столбцов Инвентарный номер, Наименование, Первоначальная стоимость, Годовая норма амортизации, Остаточная стоимость с помощью кнопки «Создать из выделенного фрагмента» - Создать имена из значений – в строке выше, выделив ячейки каждого столбца вместе с заголовком столбца.
Начиная с ячейки I2 разместите (на этом же листе) параметры функции АПЛ (рис. 1).
Для параметра функции АПЛ Инвентарный номер подготовьте поле со списком для выбора значений с помощью команды Проверка данных (вкладка Данные). Укажите тип данных – Список, источник – блок Инвентарный номер (рис. 2).
Таблица 2. Список основных средств
Рис. 1. Параметры функции АПЛ
Рис. 2.
Для остальных параметров функции АПЛ введите расчетные формулы, J2 – адрес ячейки, содержащий параметр Инвентарный номер ОС:
Наименование – формула вывода наименования ОС:
Стоимость – формула вывода начальной стоимости ОС:
Ликвидационная стоимость – формула вывода остаточной стоимости ОС:
Время – формула вывода длительности периода полной амортизации ОС:
Для ячейки результата АПЛ введите формулу: =АПЛ(J4;J5;J6), J4 – параметр Стоимость; J5 – параметр Ликвидационная стоимость; J6 – параметр Время.
При выборе инвентарного номера ОС автоматически рассчитывается годовая сумма амортизации.
Функция АМОРУВ
Создайте именованный блок ячеек для столбцов Дата постановки на учет на листе ОС.
Начиная с ячейки А10 разместите параметры функции АМОРУВ и вспомогательные данные (рис. 3).
Рис. 3. Параметры функции АМОРУВ
Для параметра функции АМОРУВ Инвентарный номер подготовьте поле со списком для выбора значений с помощью команды Проверка данных (вкладка Данные). Укажите тип данных – Список, источник – блок Инвентарный номер (см. пункт 6).
Для остальных параметров функции АМОРУВ введите расчетные формулы, В10 – адрес ячейки, содержащей параметр Инвентарный номер ОС:
Наименование – формула вывода наименования ОС:
Стоимость – формула вывода начальной стоимости ОС:
Ликвидационная стоимость – формула вывода остаточной стоимости ОС:
Дата приобретения – формула вывода даты постановки на учет ОС:
Первый период – формула вывода времени завершения первого учетного периода для ОС:
где В14 — адрес ячейки, содержащей параметр Дата приобретения
Норма амортизации – формула вывода ставки (нормы амортизации) для ОС:
Базис – выполните команду Проверка данных, укажите тип данных –Список, источник – 0; 1; 3; 4.
Для параметра Период расчета данные заполняются по строке, начиная с 1. Выделите ячейки в строке, выполните команду Прогрессия (кнопкаЗаполнить на вкладке Главная), укажите тип прогрессии - Арифметическая, шаг 1, предельное значение — 10.
Для ячейки результата введите формулу вида:
Для автоматического вычисления функции АМОРУВ для каждого учетного периода (от 1 до последнего) следует выделить ячейку с формулой и скопировать ее в ячейки строки.
ВНИМАНИЕВ формуле функции АМОРУВ для параметра Период расчета (ячейка В18) используется относительная ссылка, поскольку при тиражировании формулы номер учетного периода изменяется. Функция АМОРУВ выдает значение 0, когда номер учетного периода превышает число периодов полной амортизации имущества.
Функция АМОРУМ
14.На этом же листе: ОС добавьте в новую строку формулу:
15.Размножьте формулу по строке для вычисления функции АМОРУМ для всех учетных периодов.
Функция ДДОБ
Начиная с ячейки А22 разместите на этом же рабочем листе параметры для функции ДДОБ (рис. 5).
Рис. 5. Параметры для функции ДДОБ
Для параметра функции ДДОБ Инвентарный номер подготовьте поле со списком значений с помощью команды Проверка данных. Укажите тип данных — Список, источник – блок Инвентарный номер.
Для остальных параметров функции ДДОБ введите расчетные формулы, В22 –адрес ячейки, содержащей параметр Инвентарный номер, ОС:
Наименование – формула вывода наименования ОС:
Стоимость – формула вывода начальной стоимости ОС:
Ликвидационная стоимость – формула вывода остаточной стоимости ОС:
Норма амортизации – формула вывода ставки (годовой нормы амортизации) ОС:
Коэффициент – подготовьте поле со списком с помощью команды Проверка данных, укажите тип данных – Список, источник –0; 1; 1,5; 2; 2,5;3.
Период расчета – данные заполнять по строке. Первое значение – 1, выделите ячейки строки, выполните команду Прогрессия(кнопкаЗаполнить на вкладке Главная), тип – Арифметический, шаг 1, предельное значение – 100.
Без переключения – подготовьте поле со списком значений с помощью команды Проверка данных, укажите тип данных – Список, источник–0;1.
Для ячейки результата введите формулу вида:
20.Размножьте формулу по строке для вычисления функции ДДОБ для всех учетных периодов.
Отчитайтесь по проделанной работе преподавателю и оформите отчёт по данной лабораторной работе, ответив письменно на контрольные вопросы.
Контрольные вопросы:
Для чего предназначена функция АМР?
Назовите параметры, которые использует функция АМР?
Что вычисляет функция АМОРУВ?
Назовите параметры функции АМОРУВ?
Что вычисляет функция ДДОБ? Что вычисляет функция ПДОБ?
С помощью какого параметра выбирается алгоритм расчёта для функций ДДОБ и ПДОБ?