Задание 2. Форматирование таблиц

1. Перейти на лист «Форматирование».

2. Выполнить форматирование первой таблицы вручную:

• расположить заголовок по центру таблицы;

• выделить заголовок шрифтом Arial Black размером 11 пт, задать двойное подчеркивание;

• выполнить обрамление таблицы: внешние границы – двойные линии, внутренние – пунктирные;

• установить высоту строк в таблице равную 25 пунктам;

• задать выравнивание ячеек по горизонтали и по вертикали – по центру;

• выбрать наиболее подходящий числовой формат:

для денежных величин – «Денежный» или «Финансовый», для отображения процентов – «Процентный», для числовых – установить число десятичных знаков равное двум.

3. Отформатировать вторую таблицу с помощью Автоформата.

4. Создать стиль ячеек (имя стиля – фамилия студента).

Стиль должен включать:

• числовой формат с нулевым количеством цифр после запятой;

• шрифт Monotype Corsiva, размером 12 пунктов;

• узор ячеек.

5. Вставить диалоговое окно «Стиль» после третьей таблицы.

6. Применить созданный стиль к третьей таблице листа «Форматирование».

7. Сохранить рабочую книгу.

8. Создать колонтитул, как в задании 1.

9. Распечатать лист «Форматирование».

Задание 3. Построение диаграмм

1. На имеющемся рабочем листе «Диаграммы» построить разные типы стандартных диаграмм (рис. 5):

• гистограмму (ряды данных в столбцах);

• линейчатую диаграмму по этим же рядам данных (ряды данных в строках);

• круговую диаграмму по одному из столбцов с расчетными данными.

2. В диаграммах должны присутствовать:

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

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

• название осей;

• значения и доли (для круговой диаграммы)

3. Выполнить форматирование диаграмм:

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

4. Создать колонтитулы, как в задании 1.

5. Распечатать рабочий лист «Диаграммы».

Рис. 5. Гистограмма, линейчатая и круговая диаграммы

6. Построить нестандартную диаграмму любого вида (рис. 6). При размещении диаграммы выбрать пункт: Поместить диаграмму на отдельном листе.

7. Отменить режим добавления легенды.

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

+

Рис. 6. Нестандартная диаграмма

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

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

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

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

Таблица 3

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица 5

Должность						% надбавки
директор, гл. бухгалтер		Стаж работы >= 15 лет				25
директор, гл. бухгалтер		Стаж работы < 15 лет				20
остальные сотрудники		Стаж >= 10 лет или оклад <3000				15
								Таблица 6
№ п/п	Ф.И.О. сотрудника		Должность	Стаж работы в организации (лет)	Количество детей		Оклад				Совокупный доход					Среднедневной заработок
1	A		директор	25			15 000				176 538					680
2	B		гл. бухгалтер	12	1		10 000				62 308					250
3	C		бухгалтер	7	2		8 000				53 169					210
4	D		бухгалтер	16			8 000				51 920					210
5	F		продавец	8			4 000				37 800					140
6	E		продавец	11	1		4 000				40 230					160
7	G		продавец	9	1		3 000				33 231					130
8	H		уборщица	7			2 000				15 508					60

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

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

Р = 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. Общая сумма процентных денег за весь период начисления .