- •Кафедра информационных систем управления
- •Предисловие
- •Раздел 4. Excel2007
- •Краткая справка по работе с программой Запуск Excel 2007. Рабочая книга
- •Ввод и редактирование данных
- •Перемещение и копирование значений
- •Листы рабочей книги.
- •Сохранение рабочей книги.
- •Задание формул в электронных таблицах
- •Виды адресации в Excel (использование ссылок на ячейки или «адресных ссылок»)
- •Сортировка записей
- •Промежуточные итоги
- •Фильтры
- •Консолидация данных
- •Типы функций
- •Оформление таблиц
- •Построение диаграмм
- •Практические работы
- •Ведомость
- •Технология работы
- •Технология работы
- •Технология работы
- •Расчетная ведомость за март
- •Технология работы
- •Переоценка основных средств производства
- •(Подбор параметра)
- •Приложение «Горячие» клавиши Excel 2007. Горячие клавиши с использованием клавиши ctrl
- •Функциональные клавиши
- •Другие полезные сочетания клавиш
- •Литература
- •Оглавление
Расчетная ведомость за март
№
|
Фамилия |
Оклад |
Отработано дней |
Начислено за текущий месяц по видам оплат |
Удержано |
Сумма к выплате, руб. | |||
рабочих |
выходных и праздничных | ||||||||
зарплата |
доплата |
всего |
подоходн. налог | ||||||
1 |
Иванов |
9000 |
22 |
1 |
|
|
|
|
|
2 |
Петров |
1000 |
20 |
2 |
|
|
|
|
|
3 |
Орлов |
6000 |
22 |
0 |
|
|
|
|
|
4 |
Сидоров |
10000 |
21 |
3 |
|
|
|
|
|
5 |
Титкин |
7000 |
22 |
0 |
|
|
|
|
|
6 |
Николаев |
4000 |
22 |
2 |
|
|
|
|
|
7 |
Луков |
900 |
22 |
1 |
|
|
|
|
|
|
Итого: |
|
|
|
|
|
| ||
Всего рабочих дней 22 | |||||||||
Процент доплат 200% |
Для расчета граф используйте следующие соглашения:
Зарплата=Оклад/Всего рабочих дней* Отработано рабочих дней.
Доплата=Отработано выходных и праздничных дней* Процент доплат*(Оклад/ Всего рабочих дней)
Всего= Зарплата+Доплата.
Для расчета подоходного налога создайте таблицу на новом листе.
Расчет подоходного налога за март | |||||||||
№ п/п |
Фамилия И.О. |
Всего начислено |
Доход за предыдущие месяцы |
Доход с 1 января |
Количество детей |
Удержано | |||
|
|
|
|
|
|
вычет на работника |
вычеты на детей |
необлагаемый минимум |
подоходный налог |
1 |
Иванов |
|
18000 |
|
1 |
|
|
|
|
2 |
Петров |
|
2000 |
|
3 |
|
|
|
|
3 |
Орлов |
|
12000 |
|
0 |
|
|
|
|
4 |
Сидоров |
|
20000 |
|
2 |
|
|
|
|
5 |
Титкин |
|
14000 |
|
1 |
|
|
|
|
6 |
Николаев |
|
8000 |
|
0 |
|
|
|
|
7 |
Луков |
|
1800 |
|
2 |
|
|
|
|
|
Итого: |
|
|
|
|
|
|
|
|
Соглашения для расчета граф:
Всего начислено скопируйте со связью (Правой кнопкой: Копировать, на новом листе правой кнопкой: Специальная вставка -Вставить связь) из таблицы «Расчетная ведомость за март», графа Всего.
Доход с 1 января= Всего начислено + Доход за предыдущие месяцы.
Вычет на работника = 400 руб.
Вычет на каждого ребенка 300 руб. (введите в отдельную ячейку).
Необлагаемый минимум=Вычет на работника+Вычеты на детей
Подоходный налог=Если Доход с 1 января>20000, то 13% от Всего начислено, в противном случае: Если Всего начислено>Необлагаемого минимума, то 13% от (Всего начислено-Необлагаемый минимум), иначе подоходный налог не берется (=0)
Скопируйте со связью ( см. п.5) графу Подоходный налог в Расчетную ведомость за март.
Рассчитайте по формуле графу Сумма к выплате = Всего начислено - Удержано
8. На листе Платежная ведомость создайте таблицу:
Платежная ведомость | |||
№п/п |
Фамилия И.О. |
Сумма, руб. |
Подпись в получении |
1 |
Иванов |
8739,18 |
|
2 |
Петров |
1090,91 |
|
3 |
Орлов |
5272,00 |
|
4 |
Сидоров |
10972,73 |
|
5 |
Титкин |
6181,00 |
|
6 |
Николаев |
4259,27 |
|
7 |
Луков |
981,82 |
|
|
Итого: |
37496,91 |
|
9.Скопируйте со связью значения из графы Сумма к выплате, руб. из Расчетной ведомости за март в графу Сумма, руб.
10. Проверьте ответы
Платежная ведомость | |||
№ п/п |
Фамилия И.О. |
Сумма, руб. |
Подпись в получении |
1 |
Иванов |
8 541,82р. |
|
2 |
Петров |
1 090,91р. |
|
3 |
Орлов |
5 272,00р. |
|
4 |
Гагарин |
10 677,27р. |
|
5 |
Титов |
6 090,00р. |
|
6 |
Николаев |
4 164,73р. |
|
7 |
Попович |
981,82р. |
|
|
ИТОГО |
36 818,55р. |
|
Практическая работа № 9
Декларация расходов и доходов предприятия за год
(работа с процентами; копирование таблиц )
Постановка задачи:
Исходные данные по предприятию за 1-й квартал представлены в таблице. Заполните таблицу соответственно рис. 4.10.
Рис. 4.10. Декларация расходов и доходов
Стоимость основных производственных фондов (млн. руб.) = 200. Это значение поместите в отдельную ячейку и обращайтесь к ней, используя абсолютную адресацию. По столбцу за январь вычислите:
№ п/п 2 – 31,6% от фонда оплаты труда
3 – 5,4%от фонда оплаты труда
4 – 2,5% стоимости основных фондов
7 – 1,5% от фонда оплаты труда
8 – Сумма всех предыдущих строк
10 – объем реализации – себестоимость
11 – 32% от валовой прибыли
12 – валовая прибыль - налог
13 – 40% от чистой прибыли
14 – 60% от чистой прибыли
В каждом последующем месяце фонд оплаты труда увеличивается на 50% от фонда потребления предыдущий месяц, если чистая прибыль превышает 12 млн. руб. Если чистая прибыль меньше 12 млн. руб., то фонд оплаты труда в следующем месяце увеличивается на 40% фонда потребления предыдущего месяца.
Скопировать на 2-й, 3-й, 4-й листы таблицу и изменить исходные данные в каждой из этих трех таблиц.
по показателям 5, 6 и 9 соответственно приведенным ниже таблицам;
номер квартала в названии таблицы;
название месяцев в шапке таблицы.
№ п/п |
апрель |
май |
июнь |
5 |
48 |
51 |
56 |
6 |
16 |
18 |
19 |
9 |
175 |
190 |
210 |
№ п/п |
июль |
август |
сентябрь |
5 |
59 |
63 |
67 |
6 |
21 |
21 |
23 |
9 |
250 |
290 |
340 |
№ п/п |
октябрь |
ноябрь |
декабрь |
5 |
71 |
75 |
80 |
6 |
25 |
26 |
28 |
9 |
385 |
410 |
490 |
На 5-м листе создать таблицу «Динамика доходов и расходов предприятия за год». В эту таблицу перенести названия статей доходов и расходов, а также из 4 предыдущих листов перенести значения из столбца «Всего» с учетом автоматических изменений.
№ п/п
Статьи
Квартал
Всего
1-й кв.
2-й кв.
3-й кв.
4-й кв.
По последней таблице «Динамика расходов и доходов» построить следующие графики:
круговая диаграмма по чистой прибыли;
динамика чистой прибыли;
динамика фонда оплаты труда и чистой прибыли;
динамика себестоимости и объема реализации.
Для проверки правильности выполнения задания приведена итоговая таблица (продолжение на следующей странице):
Декларация доходов и расходов предприятия за год | ||||||
№ п/п |
Статьи |
Квартал |
Всего | |||
1-й кв. |
2-й кв. |
3-й кв. |
4-й кв. | |||
1 |
Фонд оплаты труда |
156,33 |
179,61 |
240,39 |
377,14 |
953,48 |
2 |
Отчисления в Пенсионный фонд |
49,40 |
56,76 |
75,96 |
119,18 |
301,30 |
3 |
Социальное страхование |
8,44 |
9,70 |
12,98 |
20,37 |
51,49 |
4 |
Амортизационные отчисления |
15,00 |
15,00 |
15,00 |
15,00 |
60,00 |
5 |
Материалы |
95,00 |
155,00 |
189,00 |
226,00 |
665,0 |
6 |
Энергоносители |
36,00 |
53,00 |
65,00 |
79,00 |
233,00 |
7 |
Услуги банка |
2,35 |
2,69 |
3,61 |
5,66 |
14,30 |
8 |
Себестоимость |
362,52 |
471,76 |
601,94 |
842,34 |
2278,57 |
9 |
Объем реализации продукции |
410,00 |
575,00 |
880,00 |
1285,00 |
3150,00 |
10 |
Валовая прибыль |
47,48 |
103,24 |
278,06 |
442,66 |
871,43 |
11 |
Налог на прибыль |
15,19 |
33,04 |
88,98 |
141,65 |
278,86 |
12 |
Чистая прибыль |
32,28 |
70,20 |
189,08 |
301,01 |
592,58 |
13 |
Фонд потребления |
12,91 |
28,08 |
75,63 |
120,40 |
237,03 |
14 |
Фонд накопления |
19,37 |
42,12 |
113,45 |
180,61 |
355,55 |
Практическая работа № 10
Экзаменационная ведомость
(использование логических функций И, ЕСЛИ)
Постановка задачи:
Учет результатов экзаменационной сессии ведется с использованием электронных ведомостей. Типовые ведомости создаются для групп и содержат списки студентов (фамилия, имя, отчество, № зачетной книжки). При вводе учетных данных выполняется проверка полноты заполнения ведомости, правильности ввода оценок 2, 3, 4, 5, н/я (неявка), вычисляется средний балл по дисциплине.
Для назначения на стипендию вычисляется средний балл по результатам сдачи экзаменов по каждому студенту. При этом, учитывая, что сданы все экзамены, соблюдаются условия:
если средний балл не менее 4.5, выплачивается 50%-ная надбавка к минимальной стипендии;
если средний балл от 3 до 4.5 (включительно), выплачивается минимальная стипендия;
если средний балл меньше 3, стипендия не выплачивается.
Требуется подготовить для каждой группы ведомость назначения студентов на стипендию по результатам экзаменационной сессии, в которой также подсчитывается сумма стипендиального фонда для группы (рис. 4.11).
Рис. 4.11. Форма экзаменационной ведомости (цифры оценок должны получиться в результате вычислений).
Указание. Значение "н/я" ввести по формату так же, как и оценки, т.е., по центру и без пробелов.