ЗАДАНИЕ 2

ПРОЕКТИРОВАНИЕ И ОФОРМЛЕНИЕ ЭТ

(ex_2_Name.xls)

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

Разработать план на текущее полугодие предприятия, которое имеет два отделения. Первый лист – титульный. Второй и третий листы - данные по отделениям фирмы. Четвѐртый лист - данные о предприятии в целом.

Титульный лист содержит заголовок, даты, ФИО автора, основные параметры, константы и оглавление отчета в виде гиперссылок на все остальные листы. На листах обратные гиперссылки. Имена листов должны соответствовать их цели и назначению.

Отчѐт по отделению должен содержать:

Данные по месяцам и суммарные итоги за полугодие группируются по следующим статьям:

10

Константы, зависящие от места расположения производства (К_накл_расход, Стоимость аренды 1 м2, Площадь помещения) разместить в отдельных ячейках и присвоить ячейкам имена.

Для отображения данных отчѐта построить:

диаграмму статей доходов и расходов по месяцам;

лепестковую и круговую процентную диаграмму по структуре расходной части бюджета отделения за полугодие.

Итоговый отчѐт по фирме должен содержать:

таблицу с данными по месяцам о прибыли отделений и фирмы в целом

таблицу по месяцам о размерах нормы прибыли для отде-

лений и фирмы Норма приб. отделения = Прибыль/ Расходы

Норма приб. предприятия = (Прибыль отд1 + Прибыль отд2) / (Расходы отд1 + Расходы отд2)

Для визуализации данных построить:

Диаграмму по прибыли предприятия и отделений по месяцам; График динамики нормы прибыли отделений и предприятия.

Исходные данные для расчѐтов по предприятию брать из отчѐтов по отделениям, осуществляя связывание рабочих листов с помощью формул [Книга1.xls]Лист1!A1. Для удобства ввода в формулы

11

ЗАДАНИЕ 3

БУХГАЛТЕРСКИЕ РАСЧЕТЫ (ex_3_Name.xls)

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

ЗАДАЧА:

Составить ведомости расчѐта зарплаты и налоговых отчислений сотрудников предприятия за каждый месяц квартала и итоговую ведомость за квартал. Каждую ведомость разместить на отдельном листе книги. Первый лист - титульный. В нѐм должна содержаться информация о назначении книги, и все константы, необходимые для расчѐтов:

Необлагаемые налогом вычеты начисляются в том случае, ЕСЛИ совокупный годовой доход составляет менее пороговой величины СовДоход=40000 р/год. Тогда (необлагаемые подоходным налогом Подоход=13%) вычеты складываются из расчета ВычетРаб=400 р/мес на работника и плюс ВычетИжд=1000 р/мес на каждого иждивенца, иначе вычеты равны 0. Все значения должны быть больше нуля. Для проверки напишите функцию:

=Если(СовДоход>0;”Правильно”;”Ошибка”)

Ведомость расчѐта зарплаты и налогов за все месяцы первого квартала представить в виде таблицы, в которой для каждого сотрудника отведена отдельная строка, а в столбцах расположены следующие данные:

ИСХОДНЫЕ ДАННЫЕ:

Порядковый номер, Фамилия И.О., Количество иждивенцев (КолИжд), Ставка в день (СтДн), Количество отработанных дней

(КолДн),

13

РАССЧЕТНЫЕ ЗНАЧЕНИЯ:

Начислено (Нач), Совокупный годовой доход (СовДох), Необлагаемые налогом вычеты (НалВыч), Текущий доход, облагаемый налогом (ТекДох), Налогооблагаемый совокупный годовой доход

(НалСовДох), Подоходный налог (ПодНал), Совокупный подоходный налог (СовПодНал), Выдать на руки (ВыдНаРук).

ФОРМУЛЫ ДЛЯ РАСЧЕТА

Нач = СтДн * КолДн

СовДох(t)= Нач + СовДох(t-1) - t-текущий месяц; (t-1)-предыдущий месяц Для расчѐта суммы налоговых вычетов необходимо использовать

логическую функцию

ЕСЛИ(логическое условие; выражение ДА; выражение НЕТ)

НалВыч= ЕСЛИ(СовДох(t)<=СовДоход;ВычетРаб+ВычетИжд*КолИжд;0) ТекДох = Нач-НалВыч

НалСовДох = ТекДох + НалСовДох(t-1) ПодНал = ТекДох * Подоход СовПодНал=ПодHал + СовПодНал (t-1) ВыдНаРук = Нач – ПодНал

1.Рассчитать ведомость за январь с учетом того, что все совокупные значения за предыдущие месяцы = 0.

2.В формулах использовать именованные ячейки с константами на титульном листе.

3.Изменить имена листов и отформатировать таблицу по предложенному образцу (см. рис. на следующей странице).

4.Определить, путем обратного пересчета по данным за ФЕВРАЛЬ, количество дней которые отработал каждый работник в ЯНВАРЕ.

5.Скопировать типовую таблицу ЯНВАРЯ на лист ФЕВРАЛЯ.

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

7.Скопировать типовую таблицу ФЕВРАЛЯ на лист МАРТА, исправить формулы.

8.Самостоятельно разработать Итоговую ведомость, которая должна содержать суммарные данные за квартал для каждого сотрудника со следующими колонками:

14

Порядковый номер, Фамилия И. О., Совокупный годовой доход, Налогооблагаемый совокупный годовой доход, Совокупный подоходный налог, Всего выдано.

9.Построить диаграмму по совокупному годовому доходу и совокупному подоходному налогу сотрудников предприятия за квартал.

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

САМОСТОЯТЕЛЬНАЯ РАБОТА: Рассчитайте сумму премиальных выплачиваемых в феврале мужчинам к празднику «День защитника Отечества» и в марте женщинам к празднику «День 8 марта».

Добавьте на лист каждого месяца после колонки ВЫДАТЬ НА РУКИ колонки ПОЛ (М/Ж) и ПРЕМИЯ. Формула расчета премии включает условие и функцию И (AND):

=ЕСЛИ(И(Месяц=Февраль; Пол=М); 1000; ЕСЛИ(И(Месяц=Март;

Пол=Ж); 1000;0)),

а альтернативная формула ИЛИ (OR), И (AND):

=ЕСЛИ(ИЛИ(И(Месяц=Февраль; Пол=М) ;И(Месяц=Март; Пол=Ж)); 1000;0)

Формулу в колонке Начислено необходимо изменить: Нач = СтДн * КолДн + ПРЕМИЯ

15