Методика выполнения практической части работы

- Если стаж меньше или равен 5 лет, надбавка составляет 5 %, если стаж больше 5 лет, надбавка составляет 10 %.

- Сумма надбавки на одного ребенка составляет 1000 тг.

- Если количество отработанных дней меньше или равно 20, то премия 12 %, если больше 20, то 20 %.

- Пенсионный фонд-10 % от начисленной заработной платы.

- Если (начисленная з/п – пенсионные отчисления) меньше МЗП то облагаемый доход равен 0, а если превышает МЗП то вычисляется (начисленная з/п – пенсионные отчисления– МЗП).

- Подоходный налог составляет 10% от облагаемого дохода.

- Сумму на руки определить самостоятельно.

- Выполнить сортировку по отделам, затем по Ф.И.О.

- Подсчитать по отделам сумму на руки, сумму премии, сумму надбавки на детей, сумму надбавки на стаж при использовании команды итоги.

Практическая работа № 4. Работа со сводными таблицами

Задание 1. Заполните таблицу 3.12 данными по ценным бумагам эмитентов.

Стоимость предложения рассчитывается как произведение эмиссии и номинальной стоимости.

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

Для построения сводной таблицы необходимо установить курсор в область исходной таблицы, зайти в меню Данные и выполнить команду Сводная таблица.

Далее вам нужно указать в качестве типа источника данных для построения сводной таблицы базу данных (список) Excel. На следующем шаге расположите поля базы данных ценных бумаг в областях макета следующим образом: по строкам - код вида ценных бумаг; по столбцам - код эмитента.

Область Данные должна содержать итоговое поле Стоимость предложения. Вам необходимо переименовать его в Предложение по ЦБ и применить операцию Сумма. Стоимость спроса переименовать в Спрос по ЦБ, и так же выбрать операцию Сумма.

Поместите созданную таблицу на новом листе Excel и присвойте ему имя «Сводная таблица».

Установите курсор в область готовой сводной таблицы и используйте панель инструментов «Сводные таблицы» выполните команду Формулы, Вычисляемое поле. Укажите имя нового вычисляемого поля - Дефицит/Избыток.

Таблица 3.12– База данных ценных бумаг

Формула расчета: =Стоимость предложения - Стоимость спроса.

Добавьте в сводную таблицу новые поля в область Данные:

Стоимость предложения - с именем Структура предложения по эмитентам, операция - сумма, дополнительные вычисления - Доля от суммы по строке;

Стоимость спроса - с именем Структура спроса на ЦБ эмитентов, операция - сумма, дополнительные вычисления - Доля от суммы по строке;

Стоимость предложения - с именем Структура предложений эмитента, операция - сумма, дополнительные вычисления - Доля от суммы по столбцу.

Включите следующие параметры сводной таблицы с помощью команды контекстного меню Параметры:

- общая сумма по столбцам;

- общая сумма по строкам;

- автоформат;

- сохранять форматирование;

- сохранять данные с таблицей;

- обновить при открытии.

Задание 2. Проанализируйте деятельность банков по городам и на основании таблицы 3.13 с помощью мастера сводных таблиц получите таблицу 3.14.

Таблица 3.13 – База данных по банкам

В анализе деятельности банка заемный капитал равен 10 % от собственного капитала.

Текущая задолженность составляет разницу между текущими активами и заемным капиталом.

Прибыльность текущих активов равняется частному от деления чистой прибыли на среднюю сумму текущих активов.

Рентабельность основных фондов определяется путем деления чистой прибыли на среднюю стоимость основных фондов.

Прибыльность собственного капитала равна чистой прибыли, деленной на собственный капитал.

Таблица 3.14 – Анализ деятельности банков по городам

Задание 3. На основании исходной ведомости таблицы 3.15 с помощью мастера сводных таблиц получить таблицу 3.16.

Таблица 3.15 – Ведомость реализованных товаров

Таблица 3.16 - Ведомость анализа реализованных товаров

Поле «Сумма реализации» является вычисляемым. Поля «Удельный вес расходов на оплату» и «Удельный вес в стоимости» вычисляются с помощью дополнительных вычислений (поставьте долю от суммы по столбцу).

Практическая работа № 5. Работа с промежуточными итогами. Расширенным фильтром. Мастером построения диаграмм. Автофильтром и многоуровневой сортировкой, сводными таблицами.

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

Таблица 3.17 – Переоценка основных средств

Методика выполнения практической части работы

1 Алгоритм вычисления:

ОС=БС-ИО;

ВП=БС*К, где К=3 если БС> 500 и К=2 если БС< =500

ВО=ОС*К, где К=3 если БС> 500 и К=2 если БС< =500

2 Добавить в ведомость новый столбец Вид объекта и присвоить всем объектам Цех № 1 –8 вид Основной, а всем остальным объектам присвоить вид Вспомогательный.

3 Выполнить сортировку ведомости по возрастанию Видов объектов, а внутри каждого вида – по возрастанию Наименования объектов.

Выполнить фильтрацию ведомости, оставив в ней только вспомогательные объекты (Данные - Автофильтр). После анализа результатов фильтрации вернуть в таблицу в исходное состояние, когда она содержала все виды объектов.

4 Рассчитать общую (суммарную) балансовую стоимость, износ и общую остаточную стоимость всех основных и вспомогательных видов объектов с помощью команды Данные - Итоги. После анализа результатов вернуть таблицу в исходное состояние.

5 С помощью команды Расширенный фильтр сформировать накопительную ведомость по тем объектам, балансовая стоимость которых больше 500 млн. тенге.

6 Показать на графике (гистограмме) структуру балансовой, остаточной и восстановительной полной стоимостей для всех объектов основного вида. Вывести на графике легенду и название графика «Переоценка основных производственных средств».

7 Построить на отдельном рабочем листе Excel смешанную диаграмму (график/гистограмму2), в которой необходимо показать значения балансовой и остаточной стоимостей для всех вспомогательных объектов в виде гистограммы, а значения восстановительной (полной) стоимости - в виде графика на той же диаграмме.

8 На основании исходной накопительной ведомости «Переоценка основных средств» сформировать следующую сводную таблицу с данными средней балансовой стоимости, максимального износа, минимального износа, среднего значения остаточной стоимости, и количество объектов по видам.