Расчет остаточной стоимости основных средств с использованием MS Excel
Федеральное государственное
образовательное
бюджетное учреждение высшего
профессионального образования
ФИНАНСОВЫЙ УНИВЕРСИТЕТ ПРИ
ПРАВИТЕЛЬСТВЕ РОССИЙСКОЙ ФЕДЕРАЦИИ
Заочный учетно-статистический
факультет
Кафедра прикладной информатика
"ИНФОРМАЦИОННЫЕ РЕСУРСЫ И
ТЕХНОЛОГИИ В ЭКОНОМИКЕ" (ИНФОРМАЦИОННЫЕ РЕСУРСЫ И СИСТЕМЫ В
УПРАВЛЕНИИ")
КОНТРОЛЬНАЯ РАБОТА
Вариант № 4
Выполнил:
Студент Силкин
Арутр Андреевич
Факультет Бакалавр
экономики
Зачетная
книжка № 11ФЛБ01356
Руководитель:
Картошова О.В.
Ярославль 2013
Оглавление
ВВЕДЕНИЕ
.
ПОСТАНОВКА ЗАДАЧИ
.1
Цель решения задачи
.2
Условие задачи
.
КОМПЬЮТЕРНАЯ МОДЕЛЬ И РЕШЕНИЕ ЗАДАЧИ
.1
Информационное обеспечение задачи
.2
Аналитическая модель решения задачи
.3
Технология решения задачи
.
РЕЗУЛЬТАТЫ КОМПЬЮТЕРНОГО ЭКСПЕРИМЕНТА И ИХ АНАЛИЗ
.1
Результаты компьютерного эксперимента
.2
Анализ полученных результатов
ЗАКЛЮЧЕНИЕ
СПИСОК
ЛИТЕРАТУРЫ
ВВЕДЕНИЕ
В настоящей работе приводится вариант решения задачи «Расчет остаточной
стоимости основных средств» с использованием MS Excel.
Показаны приемы ввода, данных, формирования запросов и отчетов с
использованием этого пакета.
Многие вычисления, связанные с повседневной деятельностью человека,
удобно и привычно выполнять в виде таблиц. К таким вычислениям относятся, например,
бухгалтерские расчеты, расчеты оборота материалов и продукции на заводе,
товаров на складе, различные инженерные и статистические расчеты и т.д. В виде
таблиц можно оформлять деловые документы: счета, накладные, ведомости и проч.
Вообще, представление данных в виде прямоугольных таблиц является чрезвычайно
удобным и привычным.
Для оперирования с табличными данными предназначены современные
программы, называемые электронными таблицами. Электронная таблица - это
попросту матрица из строк и столбцов, образующих отдельные ячейки. В эти ячейки
могут записываться данные (числа, текст, логические переменные), а также
формулы, по которым производятся различного рода вычисления. С помощью
электронных таблиц можно составить, к примеру, смету личных расходов вычислить
сложное тригонометрическое выражение или решить логическую задачу.
Однако перечисленными задачами возможности электронных таблиц не
исчерпываются. Возможности и тенденции развития электронных таблиц рассмотрим
на примере MS Excel, который является лидером на рынке программ обработки
электронных таблиц, определяет тенденции развития в этой области.
Целью настоящей работы является освоение пакета Excel.
Задача настоящей работы - практическое освоение программы MS Excel на конкретных примерах.
1. ПОСТАНОВКА
ЗАДАЧИ
1.1 Цель решения задачи
Главный бухгалтер организации заинтересован в правильном учете основных
средств. Ошибки в учете основных средств могут привести к неверному расчету
налогов на имущество организации. Кроме того, неверное исчисление амортизации
основных средств приводит к ошибкам в расчете себестоимости продукции. Неверное
исчисление этих показателей приводит либо к переплате налогов, либо к
занижению, и, как следствие, штрафным санкциям со стороны налоговой инспекции.
Задача, которая будет решаться в программной среде MS Excel ежемесячно,
называется «Учет стоимости основных средств на предприятии».
Цель решения данной задачи состоит в достоверном учете основных средств
предприятия и снижение излишних затрат за счет оптимизации налогов.
1.2 Условие задачи
Определите стоимость основных средств после ввода в эксплуатацию новых
объектов. Для этого просуммируйте остаточную стоимость объектов, имеющихся в
организации, и стоимость новых объектов, введенных в эксплуатацию.
Остаточная стоимость объектов основных средств рассчитывается как
разность между их первоначальной стоимостью и суммой накопленной амортизации.
Входной оперативной информацией является ведомость "Основные
средства", содержащая следующие реквизиты (условная форма):
код подразделения;
инвентарный номер объекта основных средств;
первоначальная стоимость объектов основных средств, эксплуатируемых в
организации;
сумма накопленной амортизации по эксплуатируемым объектам;
первоначальная стоимость новых объектов.
Источники данных: документы справочники «Подразделения», «Объекты
основных средств», «Инвентарная карточка объекта основных средств», «Ведомость
начисления амортизации по объектам основных средств».
Условно-постоянной (справочной информацией) служат следующие реквизиты:
код подразделения;
инвентарный номер объекта основных средств.
Результирующая информация: код подразделения, инвентарный номер объекта
основных средств, остаточная стоимость эксплуатируемых объектов основных
средств, общая стоимость основных средств организации после ввода в
эксплуатацию новых объектов.
Итоговые документы: справочники «Подразделения», «Объекты основных
средств», документы «Инвентарная карточка объекта основных средств», «Ведомость
учета остаточной стоимости объектов основных средств», «Бухгалтерский баланс»
(форма № 1), «Приложение к бухгалтерскому балансу» (форма № 5).
2.
КОМПЬЮТЕРНАЯ МОДЕЛЬ И РЕШЕНИЕ ЗАДАЧИ
2.1 Информационное обеспечение задачи
В качестве входной информации используется документ «Основные средства».
На основании этого документа создается следующий машинный документ:
Таблица 1.
Документ «Основные средства»
ОСНОВНЫЕ СРЕДСТВА
|
Код подразделения
|
Инвентарный номер
|
Первоначальная стоимость
ОС, эксплуатируемых в организации
|
Сумма амортизации по
эксплуатируемым объектам
|
Первоначальная стоимость
новых ОС
|
|
p
|
i
|
PEpi
|
AEpi
|
PNpi
|
Структура первичного документа описывается с помощью следующей таблицы:
Таблица 2.
Описание структуры первичного документа «Основные средства»
|
Имя реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ сортировки
|
Способ ввода реквизита
|
|
|
|
Целые
|
Дробные
|
|
|
|
Код подразделения
|
Kod_podr
|
С
|
10
|
|
1
|
Вручную
|
|
Наименование подразделения
|
Name_podr
|
С
|
50
|
|
|
Автоматически из
справочника
|
|
Инвентарный номер
|
Inv_nomer
|
С
|
10
|
|
2
|
Вручную
|
|
Наименование ОС
|
Name_os
|
С
|
50
|
|
|
Автоматически из справочника
|
|
Первоначальная стоимость
эксплуатируемых ОС
|
Ps_eks
|
X
|
12
|
2
|
|
Вручную
|
|
Износ эксплуатируемых ОС
|
Isnos_eks
|
Ч
|
12
|
2
|
|
Вручную
|
|
Первоначальная стоимость
новых ОС
|
Ps_nov
|
Ч
|
12
|
2
|
|
Вручную
|
Для решения задачи используются два справочника:
- Справочник подразделений (ПОДРАЗД), который служит для расшифровки кодов
подразделений;
- Справочник основных средств (ОСНОВНЫЕ), который служит для
расшифровки основных средств.
Таблица 3.
Справочник подразделений (ПОДРАЗД)
|
Имя реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ сортировки
|
|
|
|
целые
|
дробные
|
|
|
Код подразделения
|
Kod_podr
|
С
|
10
|
|
1
|
|
Наименование подразделения
|
Name_podr
|
С
|
50
|
|
|
Таблица 4.
Справочник основных средств (ОСНОВНЫЕ)
|
Имя реквизита
|
Идентификатор
|
Тип данных
|
Длина
|
Ключ сортировки
|
|
|
|
целые
|
дробные
|
|
|
Инвентарный номер
|
Inv_nomer
|
С
|
10
|
|
1
|
|
Наименование основного
средства
|
Name_os
|
С
|
50
|
|
|
Структура и описание результирующих документов:
Таблица 5.
Структура результирующего документа «Ведомость учета остаточной стоимости
объектов основных средств»
|
Наименование цеха
|
Остаточная стоимость ОС
|
Общая стоимость ОС
|
|
Стоимость по инвентарному
номеру
|
OSpi
|
STpi
|
|
Стоимость по цеху
|
OSp
|
STp
|
|
Стоимость общая
|
OS
|
ST
|
Описание структуры результирующего документа «Ведомость учета остаточной
стоимости объектов основных средств»
Таблица 6.
|
Имя реквизита
|
Иденти-фикатор
|
Тип данных
|
Длина
|
Ключ сортировки
|
|
|
|
целые
|
дробные
|
|
|
Наименование подразделения
|
Name_podr
|
C
|
50
|
|
1
|
|
Наименование основного
средства
|
Name_os
|
C
|
50
|
|
2
|
|
Общая стоимость основного
средства
|
STpi
|
Ч
|
12
|
2
|
|
|
Остаточная стоимость основного
средства
|
OSpi
|
Ч
|
12
|
2
|
|
|
Общая стоимость основных
средств по подразделению
|
STp
|
Ч
|
12
|
2
|
|
|
Остаточная стоимость
основных средств по подразделению
|
OSp
|
Ч
|
12
|
2
|
|
|
Общая стоимость основных
средств по предприятию
|
ST
|
Ч
|
12
|
2
|
|
|
Остаточная стоимость
основных средств по предприятию
|
OS
|
Ч
|
12
|
2
|
|
Построим информационную модель задачи
Рис 1. Информационная модель задачи.
2.2 Аналитическая модель решения задачи
Для получения "Ведомости учета остаточной стоимости объектов
основных средств" необходимо рассчитать следующие показатели:
- общая стоимость основного средства;
- остаточная стоимость основного средства;
- общая стоимость основных средств по подразделению;
- остаточная стоимость основных средств по подразделению;
- общая стоимость основных средств по предприятию;
- остаточная стоимость основных средств по предприятию.
Расчеты выполняются по следующим формулам:
Расчет по объекту основных средств:
= Ps_ekspi +Ps_novpi= STpi - Isnos_ekspi
Расчет по подразделению:
Расчет
по предприятию
2.3 Технология решения задачи
При использовании ППП MS Excel выполняется проектирование исходных
таблиц, в которые будут вноситься данные для решения задачи. Затем, проектируются
отчеты.
Создание таблицы.
1. При запуске Excel открывается окно в котором будем создавать таблицу.
Рис. 2. Начало работы с программой MS Excel
2. Создаваемой таблице присвоить имя,
выбрав пункт меню Файл / Сохранить как …
3. В появившемся окне ввести имя таблицы
(Основные средства) и нажать ''Сохранить''.
4. Далее присваиваем имя листу 1. Для
этого щелкаем мышкой по надписи Лист 1 и вводим имя, например, Подразделения.
5. Создаем таблицу подразделений и
вводим в нее данные. Затем выделяем графу кодов и выбираем пункт меню Данные /
Проверка
Рис. 3. Задание проверки кодов справочника
6. Задаем параметры проверки кода
изделия.
сообщение для ввода
Рис. 4. Задание сообщения для ввода
и сообщение для выдачи ошибки
Рис. 5. Задание сообщения об ошибке
Затем обводим таблицу подразделений и выбираем пункт меню Вставка / Имя /
Присвоить вводим имя Подразделения
Рис. 6. Присвоение имени области справочника
7. Присваиваем имя листу 2. Для этого
щелкаем мышкой по надписи Лист 2 и вводим имя Объекты ОС.
8. Выполняем те же действия, что и для
справочника подразделения (присваиваем имя диапазону и условия проверки)
9. Добавляем новый лист и называем его Основные средства.
Составляем таблицу для ввода данных:
Рис. 7. Создание входного документа
В графы Наименование вводим формулы для поиска наименований в
соответствующих справочниках.
Для ввода формул выбираем пункт меню Вставка / Функция, выберем категорию
Ссылки и массивы и функцию ВПР
Рис. 2.8. Ввод формулы поиска соответствия кода в справочнике
Для ввода искомого значения подразделения щелкаем мышью на А3, для выбора
таблицы подразделения щелкаем на закладку с именем листа Подразделения и
выбираем диапазон клеток с именем Подразделения. Задаем номер столбца 2 и
интервальный просмотр 0.
Рис. 9. Ввод параметров формулы поиска соответствия кода в справочнике
Копируем формулу вниз, для всех введенных строк данных.
Аналогично вводим формулы для расшифровки инвентарных номеров.
В результате получим входной документ:
Рис. 10. Входной документ
. Создадим отчет. Для этого вставим новый лист и назовем его Ведомость.
Выберем пункт меню Данные / Сводная таблица.
Рис. 11. Создание сводной таблицы.
Задаем диапазон данных на листе Входной документ и нажимаем кнопку
Готово.
Перетаскиваем поле Наименование изделия в область Боковик а поле
Трудоемкость годовой программы в область Данные.
Рис. 12. Ввод описания сводной таблицы
Получим сводную таблицу
|
Наименование подразделения
|
Инвентарный номер
|
Наименование ОС
|
Первоначальная стоимость
|
Остаточная стоимость ОС
|
|
|
Заготовительный цех
|
|
|
|
|
|
|
6
|
|
|
|
|
|
|
Здание заготовительного
цеха
|
65000000
|
27000000
|
|
|
6 Итог
|
|
65000000
|
27000000
|
|
|
10
|
|
|
|
|
|
|
Гильотина по металлу Q11
|
9000000
|
9000000
|
|
|
10 Итог
|
|
9000000
|
9000000
|
|
|
Заготовительный цех Итог
|
|
|
74000000
|
36000000
|
|
|
Механический цех
|
|
|
|
|
|
|
5
|
|
|
|
|
|
|
Здание механического цеха
|
75000000
|
30000000
|
|
|
5 Итог
|
|
75000000
|
30000000
|
|
|
8
|
|
|
|
|
|
|
Станок токарный Корвет 400
|
11800000
|
11800000
|
|
|
8 Итог
|
|
11800000
|
11800000
|
|
|
9
|
|
|
|
|
|
|
Станок фрезерный 675П
|
10600000
|
10600000
|
|
|
9 Итог
|
|
10600000
|
10600000
|
|
|
Механический цех Итог
|
|
|
97400000
|
52400000
|
|
|
Управление
|
|
|
|
|
|
|
1
|
|
|
|
|
|
|
Копьютер ASUS CP6230
|
12450
|
3850
|
|
|
1 Итог
|
|
12450
|
3850
|
|
|
2
|
|
|
|
|
|
|
Mонитор 27'' Samsung
S27C450D
|
4800
|
2200
|
|
|
2 Итог
|
|
4800
|
2200
|
|
|
3
|
|
|
|
|
|
|
EPSON Printer PLQ 22
(C11CB01001)
|
3400
|
1600
|
|
|
3 Итог
|
|
3400
|
1600
|
|
|
4
|
|
|
|
|
|
|
Здание управления
|
50000000
|
20000000
|
|
|
4 Итог
|
|
50000000
|
20000000
|
|
|
|
|
|
|
Автомобиль Тойота Раум
|
850000
|
610000
|
|
|
7 Итог
|
|
850000
|
610000
|
|
|
Управление Итог
|
|
|
50870650
|
20617650
|
|
|
Общий итог
|
|
|
222270650
|
109017650
|
|
После завершения ввода данных в таблицу Оборотные средства переходим на
лист Ведомость, нажимаем правой кнопкой мыши на таблицу и выбираем пункт
контекстного меню Обновить. В ведомости появятся результаты с учетом вновь
введенных данных.
3. РЕЗУЛЬТАТЫ
КОМПЬЮТЕРНОГО ЭКСПЕРИМЕНТА И ИХ АНАЛИЗ
3.1 Результаты компьютерного эксперимента
Для тестирования правильности решения задачи заполним входные документы и
справочники, а затем рассчитаем результаты.
Ведомость
|
Код подразделения
|
Наименование подразделения
|
Инвентарный номер
|
Наименование ОС
|
Первоначальная стоимость
эксплуатируемых ОС
|
Износ эксплуатируемых ОС
|
Первоначальная стоимость
новых ОС
|
|
1
|
Управление
|
1
|
Копьютер ASUS CP6230
|
12450,00
|
8600,00
|
0,00
|
|
1
|
Управление
|
2
|
Mонитор 27'' Samsung
S27C450D
|
4800,00
|
2600,00
|
0,00
|
|
1
|
Управление
|
3
|
EPSON Printer PLQ 22
(C11CB01001)
|
3400,00
|
1800,00
|
0,00
|
|
1
|
Управление
|
4
|
Здание управления
|
50000000,00
|
30000000,00
|
0,00
|
|
2
|
Механический цех
|
5
|
Здание механического цеха
|
75000000,00
|
45000000,00
|
0,00
|
|
3
|
Заготовительный цех
|
6
|
Здание заготовительного
цеха
|
65000000,00
|
38000000,00
|
0,00
|
|
1
|
Управление
|
7
|
Автомобиль Тойота Раум
|
850000,00
|
240000,00
|
0,00
|
|
2
|
Механический цех
|
8
|
Станок токарный Корвет 400
|
0,00
|
0,00
|
11800000,00
|
|
2
|
Механический цех
|
9
|
Станок фрезерный 675П
|
0,00
|
0,00
|
10600000,00
|
|
3
|
Заготовительный цех
|
10
|
Гильотина по металлу Q11
|
0,00
|
0,00
|
9000000,00
|
|
Справочник цехов
|
|
Код подразделения
|
Наименование
|
|
1
|
Управление
|
|
2
|
Механический цех
|
|
3
|
Заготовительный цех
|
|
Справочник изделий
|
|
Инвентарный номер
|
Наименование ОС
|
|
1
|
Копьютер ASUS CP6230
|
|
2
|
Mонитор 27'' Samsung
S27C450D
|
|
3
|
EPSON Printer PLQ 22
(C11CB01001)
|
|
4
|
Здание управления
|
|
5
|
Здание механического цеха
|
|
6
|
Здание заготовительного
цеха
|
|
7
|
Автомобиль Тойота Раум
|
|
8
|
Станок токарный Корвет 400
|
|
9
|
Станок фрезерный 675П
|
|
10
|
Гильотина по металлу Q11
|
|
|
|
Наименование подразделения
|
Инвентарный номер
|
Наименование основного
средства
|
Первоначальная стоимость
|
Остаточная стоимость ОС
|
|
Заготовительный цех
|
|
|
|
|
|
6
|
Здание заготовительного
цеха
|
65000000
|
27000000
|
|
10
|
Гильотина по металлу Q11
|
9000000
|
9000000
|
|
Заготовительный цех Итог
|
|
|
74000000
|
36000000
|
|
Механический цех
|
|
|
|
|
|
5
|
Здание механического цеха
|
75000000
|
30000000
|
|
8
|
Станок токарный Корвет 400
|
11800000
|
11800000
|
|
9
|
Станок фрезерный 675П
|
10600000
|
10600000
|
|
Механический цех Итог
|
|
|
97400000
|
52400000
|
|
Управление
|
|
|
|
|
|
1
|
Копьютер ASUS CP6230
|
12450
|
3850
|
|
2
|
Mонитор 27'' Samsung
S27C450D
|
4800
|
2200
|
|
3
|
EPSON Printer PLQ 22
(C11CB01001)
|
3400
|
1600
|
|
4
|
Здание управления
|
50000000
|
20000000
|
|
7
|
Автомобиль Тойота Раум
|
850000
|
610000
|
|
Управление Итог
|
|
|
50870650
|
20617650
|
|
Общий итог
|
|
|
222270650
|
109017650
|
В результате решения задачи ведомости, полученные с помощью компьютера
совпадают с тестовыми.
3.2 Анализ полученных результатов
запрос стоимость основной средство
Таким образом, формирование сводных таблиц на основе документа «Ведомость
основных средств» позволяет решить поставленную задачу - рассчитывать
остаточную стоимость основных средств.
Создание различных диаграмм (гистограмм, графиков) на основе данных
сводных таблиц средствами MS Excel дает возможность не только наглядно
представлять результаты обработки информации для проведения анализа и принятия
управленческих решений, но и осуществлять манипуляции по их построению в целях
наиболее удобного представления результатов визуализации по задаваемым
пользователем (аналитиком) параметрам.
ЗАКЛЮЧЕНИЕ
В данной работе была предпринята попытка создания таблицы расчета
остаточной стоимости основных средств. Для реализации был пакет MS Excel.
В целом, работа соответствует заданию. Программа позволяет ввести
справочные данные (справочник подразделений, справочник основных средств),
исходные данные, выполнить расчет остаточной стоимости, рассчитать ведомость,
просмотреть ее на экране и выдать на принтер.
СПИСОК
ЛИТЕРАТУРЫ
1. В.Пасько,
А. Колесников Самоучитель работы на персональном компьютере: 2-е изд., доп. -
К.: Издательство Питер, 2005. - 400 с.
2. Киселева
С.В., Куранов В.П. Оператор ЭВМ: Учеб. для нач. проф. образования. - 2-е изд.,
стереотип. - М.: ИРПО; Изд.центр «Академия», 2003. - 424 с.
. Ефимова
О., Морозов В., Угринович Н. Курс компьютерной технологии с основами
информатики. Учебное пособие для старших классов. - М.: ООО «Издательство АСТ»;
ABF, 2003. - 424 с.: ил.
. Информатика:
Базовый курс / С.В. Симинович и др. - СПб.: Питер, 2003. - 640 с.: ил.
5. Википедия. <http://www.ru.wikipedia.org>