Переоценка основных средств производства
1. Сформировать и заполнить накопительную ведомость по переоценке основных средств производства, которая приведена ниже в таблице. Значения балансовой, остаточной и восстановительной стоимостей объектов, а также стоимость износа считать в тыс. тенге.
Таблица 1 – Переоценка основных средств производства
Наименование объекта Балансовая стоимость Износ Остаточная стоимость Восстанови-тельная полная стоимость Восстанови-тельная остаточная стоимость
Заводо-управление
299,8 56,4
Диспетчерская 435,6 85,4
Цех №1 8456,2 658,0
Цех №2 184,6 77,8
Цех №3 806,4 245,0
Цех №4 968,4 240,6
Цех №5 125,4 48,9
Склад №1 387,2 162,4
Склад №2 628,7 91,7
Склад №3 543,9 220,5
Склад №4 219,5 83,2
Склад №5 320,5 94,1
Итого
Используя значения балансовой стоимости (БС) и износа объекта (ИО), рассчитать:
остаточную стоимость объекта (ОС) по следующей формуле:
ОС= БС – ИО;
восстановительную полную стоимость объекта (ВП) и восстановительную остаточную стоимость объекта (ВО) по следующим формулам:
ВП= БС*К; ВО= ОС*К;
где К = 3,0, если БС> 500 тыс. тенге,
К = 2,0, если БС 500 тыс.тенге.
Решение.
1) Создадим файл Excel
2) Переименуем «Лист1» в «1» и заполним соответственно табл. 1.
3) Рассчитаем OC.
Напишем в ячейку D2 формулу «=B2-C2» и нажмём Enter
Маркером потащим ячейку D2 до ячейки D13
4) Рассчитаем ВП.
Напишем в ячейку E2 формулу «=B2*ЕСЛИ(B2>500;3;2)» и нажмём Enter
Маркером потащим ячейку E2 до ячейки E13
5) Рассчитаем ВO.
Напишем в ячейку F2 формулу «=D2*ЕСЛИ(B2>500;3;2)» и нажмём Enter
Маркером потащим ячейку F2 до ячейки F13
6) Рассчитаем Итого.
Напишем в ячейку B14 формулу «=СУММ(B2:B13)» и нажмём Enter
Маркером потащим ячейку B14 до ячейки F14
Получим заполненную накопительную ведомость по переоценке основных средств производства
2. На новом листе в ведомость добавить новую графу Вид объекта и присвоить всем объектам Цех№1–5 вид основной, а всем остальным объектам вспомогательный. Выполнить сортировку ведомости по возрастанию видов объектов.
Решение.
1) Создадим копию листа «1»
нажимаем по ярлыку листа
выбираем «переместить или скопировать»
в открывшемся окне выбираем «переместить в конец» и «создать копию»
2) Назовём его «2»
3) Добавим в ячейку G2 заголовок нового столбца «Вид объекта»
4)Напротив Цех№1–5 заполним столбец G значениями «основной», а всем остальным «вспомогательный».
5) Выделим всю таблицу- ячейки A2:G13
6) На вкладке «Данные» нажмём кнопку «Сортировка»
7) В окне сортировка выберем «Сортировать по» «Вид объекта» и нажмём OK
8) В итоге получим
3. На новых листах выполнить сортировку каждого вида объекта отдельно по возрастанию балансовой стоимости. Подсчитать долю балансовой и остаточной стоимости каждого объекта. Построить диаграмму.
1) Создадим две копии листа «2»
2) Назовём их «3а» и «3б»
3) Оставим на листе «3а» все вспомогательные объекты
выделим ячейки A9:G13
нажмём по выделенному фрагменту правой кнопкой мыши и выберем удалить
в окне удаления выберем «строку» и нажмём OK
4) Добавим столбцы «Доля БС» и «Доля ОС»
5) Напишем в ячейку H2 формулу «=B2/$B$9», а ячейку I2 «=D2/$D$9»
6) Выделим ячейки H2 и I, маркером потащим до ячеек H8 и I8 и выберем для выделенного диапазона «Формат ячеек» – Процентный
7) Выделим ячейки с помощью CTRL A1:A8 и H1:I8 на вкладке «Вставка» нажмём «Гистограмма» и выберем «Гистограмма с группировкой»
8) Оставим на листе «3б» все основные объекты
выделим ячейки A2:G8
нажмём по выделенному фрагменту правой кнопкой мыши и выберем удалить
в окне удаления выберем «строку» и нажмём OK
9) Добавим столбцы «Доля БС» и «Доля ОС»
10) Напишем в ячейку H2 формулу «=B2/$B$7», а ячейку I2 «=D2/$D$7»
11) Выделим ячейки H2 и I, маркером потащим до ячеек H6 и I6 и выберем для выделенного диапазона «Формат ячеек» – Процентный
12) Выделим ячейки с помощью CTRL A1:A6 и H1:I6 на вкладке «Вставка» нажмём «Гистограмма» и выберем «Гистограмма с группировкой»
4. Рассчитать общую балансовую стоимость, износ и общую остаточную стоимость всех основных и вспомогательных видов объектов с помощью команды Итоги и разместить на новом листе.
Решение
1) Создадим копию листа «2»
2) Назовём его «4»
3)Выделим ячейки A2:G13 и нажмём на вкладке «Данные» кнопку «Промежуточный итог»
4) В окне «Промежуточные итоги» заполним следующим образом
5) Получим следующую таблицу
5. С помощью команды Расширенный фильтр на новом листе сформировать ведомость по тем объектам, балансовая стоимость которых > 500 тыс. тенге. Включить в новую ведомость следующие графы:
наименование объекта
балансовая стоимость
остаточная стоимость
восстановительная полная стоимость.
Решение
1) Создадим копию листа «1»
2) Назовём его «5»
3) Скроем ненужные столбцы, выделив их и нажав «Скрыть», оставив
наименование объекта
балансовая стоимость
остаточная стоимость
восстановительная полная стоимость.
4) Заполним диапазон условий $B$19:$B$20 следующими значениями
5) Выделим ячейку A1:B13 и нажмём кнопку «Дополнительно» в пункте «Сортировка и фильтр» на вкладке «Данные»
6) Заполним «Расширенный автофильтр» следующими значениями и нажмём OK
6) В итоге получим
6. Показать на графике структуру балансовой, остаточной и восстановительной (полной) стоимостей для всех объектов основного вида и назвать Переоценка основных средств производства.
Решение
1) Создадим копию листа «2»
2) Назовём его «6»
3) Выделим ячейки с помощью CTRL A1:B2 , D1, E1 , A9:B13, D9:D13 и E9:E13 и на вкладке «Вставка» нажмём «Гистограмма» и выберем «Гистограмма с группировкой»
4) Выберем диаграмму и на вкладке «Конструктор» выберем в «макет1»
5)Поменяем название на «Переоценка основных средств производства.»
7. Построить на отдельном рабочем листе смешанную диаграмму, в которой необходимо показать значения балансовой и остаточной стоимостей для всех вспомогательных объектов в виде гистограмм, а значения восстановительной полной стоимости всех вспомогательных объектов представить в виде линейного графика на той же диаграмме и назвать Оценка основных средств производства (вспомогательные объекты).
Решение
1) Создадим копию листа «2»
2) Назовём его «7»
3) Выделим ячейки с помощью CTRL A1:B2 , D1, E1 , A2:B8, D2:D8 и E2:E8 и на вкладке «Вставка» нажмём «Гистограмма» и выберем «Гистограмма с группировкой»
4) Выберем диаграмму и на вкладке «Конструктор» выберем в «макет1»
5)Поменяем название на «Оценка основных средств производства (вспомогательные объекты).»
7) Поменяем тип диаграммы для «Восстановительной полной стоимости»
выберем нужный столбец на диаграмме
правой кнопкой откроем меню и нажмём «Изменить тип диаграммы ряда»
В окне выберем тип диаграммы «График»
7) Получим диаграмму
8 На основании исходной ведомости с помощью функций рассчитать и сформировать следующий документ:
Тип объекта Основной Вспомогательный
Средняя балансовая стоимость
Максимальный износ
Минимальный износ
Максимальная остаточная стоимость
Средняя остаточная стоимость
Количество объектов
1) Создадим копию листа «2»
2) Назовём его «8»
4) Заполним ячейки A20:C26 соответственно заданной таблице
4) Заполним таблицу, введя формулы для каждой ячейки
Тип объекта Основной Вспомогательный
Средняя балансовая стоимость =СУММ(B9:B13)
=СУММ(B2:B8)
Максимальный износ =МАКС(C9:C13)
=МАКС(C2:C8)
Минимальный износ =МИН(C9:C13)
=МИН(C2:C8)
Максимальная остаточная стоимость =МАКС(D9:D13)
=МАКС(D2:D8)
Средняя остаточная стоимость =СРЗНАЧ(D9:D13)
=СРЗНАЧ(E2:E8)
Количество объектов =ЧСТРОК(A9:A13)
=ЧСТРОК(A2:A8)
5) В результате получим