Задание 4. Работа со встроенными функциями MS Excel

1. Добавить в книгу новый рабочий лист. Присвоить имя «Табель».

2. Построить табель учета отработанных часов сотрудниками магазина за первую половину месяца (табл. 3). Продолжить заполнение граф «Числа месяца» обозначениями: «8» – рабочий день, «б» – дни по болезни, «о» – отпуск, «п» – дни прогула.

3. Для ввода фамилий сотрудников использовать автозаполнение, создав предварительно список фамилий для Автозаполнения. Фамилия первого сотрудника – это Ф.И.О. студента, выполняющего задание.

Таблица 3

4. Определить количество:

- отработанных часов (функция СУММ);

- дней, пропущенных по болезни (функция СЧЕТЕСЛИ, критерий − текст " б");

- прогулов (функция СЧЕТЕСЛИ, критерий − текст " п");

- дней отпуска (функция СЧЕТЕСЛИ, критерий − текст " о");

- отработанных дней (функция СЧЕТ);

- общее количество дней отпуска каждой категорией работников (функция СУММЕСЛИ).

5. Отформатировать таблицу.

6. Создать колонтитул.

7. Ввести на новый рабочий лист данные для расчета заработной платы сотрудников за первую половину месяца (табл. 4). Таблица 4

8. Построить на новом рабочем листе дополнительные таблицы (табл. 5, табл. 6). Рассчитать заработную плату каждого сотрудника, используя следующие данные:

- Начислено = Оклад / Количество рабочих дней * Количество отработанных дней. Данные о количестве рабочих дней и количестве отработанных рабочих дней находятся на листе «Табель».

- 20 % РК (районный коэффициент) = Начислено * 20 %.

- 30 % ДВК (дальневосточный коэффициент) = Начислено * 30 %.

- Индивидуальная надбавка = Начислено * % надбавки. Индивидуальная надбавка зависит от должности, стажа работы и оклада. (Для вычисления индивидуальной надбавки использовать функции ЕСЛИ, И, ИЛИ).

Дополнительные таблицы для решения задачи:

Таблица 5

Должность

% надбавки

директор, гл. бухгалтер

Стаж работы > = 15 лет

директор, гл. бухгалтер

Стаж работы < 15 лет

остальные сотрудники

Стаж > = 10 лет или оклад < 3000

Таблица 6

№ п/п

Ф.И.О. сотрудника

Должность

Стаж работы в организации (лет)

Количество детей

Оклад

Совокупный доход

Среднедневной заработок

A

директор

15 000

176 538

B

гл. бухгалтер

10 000

62 308

C

бухгалтер

8 000

53 169

D

бухгалтер

8 000

51 920

F

продавец

4 000

37 800

E

продавец

4 000

40 230

G

продавец

3 000

33 231

H

уборщица

2 000

15 508

- Отпуск = Среднедневной заработок * Количество дней отпуска

- ПВНТС (больничный) за счет средств работодателя = кол-во дней по болезни * среднедневной заработок * Р.

Р = 100 %, если стаж работы ≥ 8, Р = 80 %, если 5 ≤ стаж < 8, Р = 60 %, если стаж работы < 5 лет. Для вычисления ПВНТС использовать вложенные функции ЕСЛИ.

- НДФЛ (налог) = (Итого по кредиту счета 70 – Вычет 1 – Вычет 2 * Количество детей) * 13%. Вычет 1 предоставляется на работающего до момента достижения суммы совокупного годового дохода = 20 000. Вычет 2 предоставляется на каждого ребенка до достижения суммы совокупного дохода = 40 000 (табл. 7). Для вычисления НДФЛ использовать функцию ЕСЛИ. Таблица 7

Сумма, не облагаемая налогом

на работающего

на ребенка

400, 00р.

600, 00р.

- Аванс = Итого по кредиту счета 70 * 40 %

- Итого по кредиту счета 70 = Начислено + 20 % РК + 30 % ДВК + Персональная надбавка + Отпуск + ПВНТС за счет средств работодателя.

- Итого по дебету счета 70 = НДФЛ + Аванс.

- К выдаче = Итого по кредиту счета 70 – Итого дебету по счета 70. В формуле предусмотреть округление полученных сумм к выдаче с точностью до копеек с помощью функции ОКРУГЛ.

- Рассчитать итоговую сумму к выдаче, итого по кредиту счета 70, итого по дебету счета 70 по всему предприятию (СУММ).

- Отсортировать и отформатировать таблицу «Ведомость учета оплаты труда».

- Для денежных величин задать «Финансовый формат».

9. Присвоить рабочим листам имена «Ведомость» и «Дополнительные таблицы». Создать колонтитул, поместив в него Ф.И.О. и дату создания.

10. По столбцам «Итого по кредиту сч. 70» и «Итого по дебету сч. 70» построить коническую и цилиндрическую диаграммы. Коническую диаграмму построить по столбцам выделенного диапазона, цилиндрическую – по строкам.

11. На отдельном листе построить график по столбцу «К выдаче».

В диаграммах должны присутствовать следующие элементы:

- заголовок диаграммы;

- заголовки осей;

- легенда с именами рядов данных;

12. Показать работу преподавателю.

 

Приложение 1

Вариант 1

Ведомость предприятия «Спутник»

Определить:

1. Прибыль по коммерческой деятельности ,

где Сн – ставка НДС (принять Сн=20 %)

2. Прибыль от реализации

3. Валовая прибыль , где Пврд − прибыль от внереализационной деятельности (принять Пврд =10 000 руб.)

Вариант 2

Ведомость учета по предприятиям в 2005 году

Определить:

1. Возвращенная сумма %), где К – продолжительность года в днях (принять К =365).

2. Сумма процентных денег, выплачиваемых за год .

3. Продолжительность периода начисления в годах .

Вариант 3

Ведомость расчета рентабельности по предприятиям

Определить:

1. Рентабельность реализованной продукции .

2. Рентабельность активов .

3. Доля заемных средств .

Вариант 4

Ведомость учета по предприятиям в 2005 году

Определить:

1. Размер займа , где К – продолжительность года в днях (принять К =365).

2. Период начисления .

3. Величина дисконта .

Вариант 5

Ведомость учета стоимости заказов по магазинам

Определить:

1. Годовая стоимость заказа .

2. Годовая стоимость капиталовложений в запасы %, где S − стоимость каждой единицы товара (принять S = 3, 5 тыс. руб.).

3. Полная нетто-стоимость запасов (за вычетом покупной цены) , где N – налоговые вычеты (принять N=40 %), П – процентная ставка (принять П=20 %).

Вариант 6

Ведомость оборотных средств предприятия «Луч»

Определить:

1. Потребность в оборотных средствах по производственным запасам Ннз=Nпз*Спз.

2. Норматив оборотных средств на незавершенное производство Ннп=Nнп*Свп.

3. Общий норматив оборотных средств Нос=Нпз+Ннп

Вариант 7

Ведомость рентабельности рекламирования товаров

Определить:

1. Дополнительный товарооборот под воздействием рекламы , где Д − количество дней учета товарооборота в рекламном и послерекламном периодах (принять Д=30).

2. Рентабельность рекламирования товара .

3. Экономический эффект рекламирования , где Нд − торговая надбавка на товар, в % к цене реализации (принять Нд=12 %).

Вариант 8

Ведомость распределения прибыли за прошедший отчетный год

Определить:

1. Налог на доход по ценным бумагам , где n – ставка налога по ценным бумагам (принять n=10 %).

2. Налогооблагаемая прибыль .

3. Прибыль, остающаяся в распоряжении предприятия .

Вариант 9

Ведомость совместного предприятия «Амур-Сакура»

Определить:

1. Оптовая цена предприятия .

2. Отпускная цена предприятия , где Сn – ставка налога на добавленную стоимость (принять Сn = 20 %).

3. Сумма капиталовложений в развитие производства , где n − процент прибыли, направляемой на развитие производства

(принять n = 30 %).

Вариант 10

 

Ведомость учета денежных потоков по акциям в текущем году

Определить:

1. Годовой дивиденд %.

2. Стоимость акции .

3. Ожидаемый доход по акциям .

Вариант 11

Ведомость предприятий по налоговым суммам

Определить:

1. Итоговый коэффициент к=к1+к2.

2. Величина вмененного дохода ВД=БД*N*к.

3. Сумма единого налога ЕН=ВД*15 %.

Вариант 12

Ведомость квартиросъемщиков по ул. Мира

 

Определить:

1. Процент от дохода семьи Д*18 %.

2. Оплата социальной нормы ОСН =С*N.

3. Размер субсидии .

Вариант 13

Ведомость учета доходов за месяц

Определить:

1. Среднее количество кликов .

2. Среднее количество регистраций .

3. Средний доход за месяц .

Вариант 14

Ведомость учета транспорта на предприятии «Заря»

 

Определить:

1. Время перемещения груза , где V – скорость перемещения транспорта с грузом (принять V=1, 5 м/с).

2. Время кругооборота , где Тв, Тп, Тр – время соответственно возврата пустого транспорта, погрузки и разгрузки груза (принять Тв=30 с, Тп=5 с, Тр=5 с).

3. Количество транспорта необходимого для перемещения грузов в цехе .

Вариант 15

Ведомость оборотных средств предприятия «Юпитер»

 

 

 

Определить:

1. Оборачиваемость оборотного капитала .

2. Коэффициент оборачиваемости .

3. Коэффициент закрепления оборотных средств .

Вариант 16

Ведомость учета сроков и сумм кредитов по предприятиям в 2006 году

Определить:

1. Возвращенная сумма %, где К – продолжительность года в днях (принять К =365).

2. Продолжительность периода начисления в годах .

3. Общая сумма процентных денег за весь период начисления .