Использование программы MS Excel для решения бухгалтерских задач

При проведении расчетов по формулам перед вычислениями необходимо установить в столбцах необходимый тип данных. В числах целая часть от дробнойотделяется запятой, например, 5,7. В адресах ячеек используются буквылатинскогоалфавита.

 

Практическая работа 1

Цель работы: Научиться решать бухгалтерские задачи в MS Excel.

 

• Задание 1. Основываясь на данных таблицы 2.14, провести бухгалтерские вы­числения.

Порядок выполнения задания

1. Создайте новый файл под именем «Книга 2».

2. На Листе 1 создайте табл.2.14 по образцу(табл. 2.14). Обозначьтеграницы, параметры заливки, выравнивания, вид числа задайте произвольные.

3. Применить следующие формулы для расчета таблице:

- в столбце «Начислено/всего» используйте формулу = оклад + премия,в ячейке ЕЗ она будет выглядеть следующим образом: =C3+D3;

-в столбце «Удержано/Подоходный налог» используйте формулу = начислено / всего × подоходный налог, в ячейке F3 она будет выглядеть следующим образом: =ЕЗ*13%;

- в столбце «Удержано/Профсоюзный налог» используйте формулу = начислено / всего × профсоюзные взносы, в ячейке G3 она будет выглядеть следующим образом: =E3*1%;

- в столбце «Удержано, р./всего» используйте формулу = Удержано / Подоходный налог + Удержано / Профсоюзный налог, в ячейке НЗ она будет выглядеть следующим образом: =F3+G3;

- в столбце «На руки» используйте формулу = начислено / всего – удержано/ всего, в ячейке I3 она будет выглядеть следующим образом: =ЕЗ-Н3.

 

Подоходный налог = 13% и Профсоюзный налог = 1% можно поместить в ячейках памяти вне таблицы и в формулах использовать абсолютные адреса этих ячеек.

 

4. Для расчета остальных ячеек примените автозаполнение.

5. Для ячеек с результатами расчетов задать формат Финансовый. Для этого выделите блок данных, нажмите правую кнопку мыши и выберите в контекстном меню Формат ячеек. В открывшемся окне выберите вкладку Число и задайте параметры форматирования ячеек: числовой формат - финансовый, обозначение – нет, число десятичных знаков после запятой - 2.

 

Таблица 2.14

Таб. Номер	Ф.И.О.	Начислено	Удержано	На руки
оклад	премия	всего	подоходный	профс.	всего
	Алексин А.С.
	Баликов И.С.
	Борова В.Н.
	Иванов А.Т.
	Колзин Н.М.
	Мухин С.В.
	Новеев А.К.
	Прошина В.К.
	Петров И.И.
	Савкин А.А.
	Солодов М.Д.
	Степина Н.А.
	Трушкин М.Э
	Ялин С.М.
Итого:

Таблица 2.14. Итоговая

Таб. Номер	Ф.И.О.	Начислено	Удержано	На руки
оклад	премия	всего	подоходный	профс.	всего
	Алексин А.С.
	Баликов И.С.				1319,5	101,5
	Борова В.Н.
	Иванов А.Т.
	Колзин Н.М.
	Мухин С.В.				1189,5	91,5
	Новеев А.К.
	Прошина В.К.
	Петров И.И.				656,5	50,5
	Савкин А.А.
	Солодов М.Д.
	Степина Н.А.
	Трушкин М.Э				864,5	66,5
	Ялин С.М.
Итого:

6. Присвойте Листу 1 имя «Задача 1».

 

• Задание 2. Основываясь на данных таблицы 2.15 провести бухгалтерские вычисления и построить диаграмму.

Порядок выполнения задания

1. На Листе 2 введите данные, произведите необходимые вычисления по приведенному образцу (табл. 2.15).

2. Формулы для расчета:

- Фонд соц. страхования = Фонд зарплаты х 5,4%; =В5*5,4 %;

- Пенсионный фонд = Фонд зарплаты х 28,0%; =В5*28%;

- Фонд мед. страхования = Фонд зарплаты х 3,6%; =В5*3,6%;

- Фонд занятости = Фонд зарплаты х 1,5%; =В5*1,5%.

3. Чтобы рассчитать результат в строке «Итого» необходимо выделить каждый столбец без заголовка и выбрать вкладку Главная • Редактирование • Сумма «Σ».

Для выполнения автосуммы можно пользоваться кнопкой Автосуммирование (Σ) на панели инструментов или функцией СУММ. Выберите вкладку Формулы • Библиотека функций • Автосумма. В качестве первого числа выделите группу ячеек с данными для расчета суммы.

 

Таблица 2.15

Сотрудники	Фонд зарплаты	Отчисления
Фонд соц. страхования	Пенсионный фонд	Фонд мед. страхования	Фонд занятости
5,40%	28,00%	3,60№	1,50%
Инженеры	104542,0
Бухгалтеры	12401,7
Администрация	9184,6
Рабочие	25271,5
Мастера ПО	131939,4
Автомеханики	12464,3
Уборщицы	5237,6
Сторожа	8400,0
Итого

Таблица 2.15 Итоговая

Сотрудники	Фонд зарплаты	Отчисления
Фонд соц. страхования	Пенсионный фонд	Фонд мед. страхования	Фонд занятости
5,40%	28,00%	3,60№	1,50%
Инженеры	104542,0	5645,27
Бухгалтеры	12401,7	669,69
Администрация	9184,6	495,97
Рабочие	25271,5	1364,66
Мастера ПО	131939,4	7124,73
Автомеханики	12464,3	673,07
Уборщицы	5237,6	282,83
Сторожа	8400,0	453,60
Итого	309441,0	16709,81	866,44

4. Постройте диаграмму (рис. 2.19). Для этого выделите столбцы А («Сотрудники») и В (Фонд зарплаты) и выберите вкладку Вставка • Диаграммы • Круговая • Объемная разрезанная круговая.

5. Для внесения заголовка диаграммы и подписей данных выберите всплывающую вкладку Работа с диаграммами • Макет • Подписи • Подписи данных.

Рис. 2.19

6. Присвойте Листу 2 имя «Задача 2».

• Задание 3. На Листе 3 ввести данные, произвести необходимые вычисления (табл. 2.16) и построить диаграмму по приведенному образцу (рис. 2.20).

Порядок выполнения задания

1. Формулы для расчета:

- Стаж на текущий год = текущий год - год поступления; =$А$11 - С 2;

- Итоговый оклад = оклад, р- + оклад, р × повышение оклада/100; =D2+ D2*F2/100.

 

Таблица 2.16

№ п/п	Ф.И.О.	Год поступления	Оклад, р.	Стаж на текущий год	Повышение оклада, %	Итоговый оклад
	Лепин Н.И.
	Уланов Т.О.
	Петрова И.Г.
	Пименова Е.Н.
	Репина А.В.
	Никонов В.В.
	Сидоров У.Р.
Текущий год

Таблица 2.16. Итоговая

№ п/п	Ф.И.О.	Год поступления	Оклад, р.	Стаж на текущий год	Повышение оклада, %	Итоговый оклад
А	B	C	D	E	F	G
	Лепин Н.И.
	Уланов Т.О.
	Петрова И.Г.
	Пименова Е.Н.
	Репина А.В.
	Никонов В.В.
	Сидоров У.Р.
Текущий год

2. Постройте диаграмму. Удерживая нажатой клавишу Ctrl, выделите столбцы в (Ф.И.О.), D (Оклад, р) и С (Итоговый оклад) с данными расчета результатов и выберите вкладку Вставка • Диаграммы • Гистограмма • Объемная с группировкой.

3. Введите название диаграммы при помощи всплывающей вкладки Работа с диаграммами • Макет • Подписи • Подписи данных.

Рис. 2.20

4. Присвойте Листу 3 имя «Задача 3» и сохраните файл в «Книга 2».

 

Практическая работа 2

Цель работы: Основываясь на данных таблицы 2.17 провести бухгалтерские вычисления и построить диаграмму (рис. 2.21).

Порядок выполнения работы

1. Откройте файл под именем «Книга2». На листе 4 введите данные по образцу табл. 2.17.

 

Таблица 2.17.

Продажи за 1-е полугодие 20… г.
Месяц	Товар	Артикул	Цена за кг, р.	Количество	Сумма, р.
	Мясо
Молоко
Творог
Январь	Итого:
	Мясо
Молоко
Творог
Февраль	Итого:
	Мясо
Молоко
Творог
Март	Итого:
	Мясо
Молоко
Творог
Апрель	Итого:
	Мясо
Молоко
Творог
Май	Итого:
	Мясо
Молоко
Творог
Июнь	Итого:
ИТОГО:

 

2. В столбце Dскопируйте ячейки 3-5 за «Январь» и вставьте в этот же столбец в «Февраль», «Март», «Апрель», «Май», «Июнь»

Формула для расчета Суммы, р. = цена за кг, р. × количество.

4. При помощи автозаполнения произведите расчеты для каждого месяца отдельно.

5. Чтобы рассчитать результат в строке «Итого», необходимо выделить в столбце D ячейки за определенный месяц и выбрать вкладку Главная • Редактирование • Сумма «Σ».

6. Чтобы подсчитать «Итого» за все месяцы, необходимо при нажатой клавише Ctrl выделить содержимое последнего столбца «Итого» для каждого месяца и выбрать вкладку Главная • Редактирование • Сумма «Σ».

Таблица 2.17. Итоговая

Продажи за 1-е полугодие 20… г.
Месяц	Товар	Артикул	Цена за кг, р.	Количество	Сумма, р.
	Мясо
Молоко
Творог
Январь	Итого:
	Мясо
Молоко
Творог
Февраль	Итого:
	Мясо
Молоко
Творог
Март	Итого:
	Мясо
Молоко
Творог
Апрель	Итого:
	Мясо
Молоко
Творог
Май	Итого:
	Мясо
Молоко
Творог
Июнь	Итого:
ИТОГО:

7.Чтобы построить диаграмму, составьте новую табл. 2.17.11 Итоговая, используя данные табл. 2.17. Итоговая. Выделите табл. 2.17.1. Итоговая и постройте резанную круговую диаграмму (рис. 2.21).

 

 

Таблица 2.17.1 Итоговая

Продажи за 1-е полугодие 2011 г.
Январь
Февраль
Март
Апрель
Май
Июнь

Рис. 2.21

8. Присвойте Листу 4 имя «Задача 4» и сохраните файл «Книга 2».

 

Практическая работа 3

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

Порядок выполнения работы

1. Откройте файл «Книга 2».

2. Назовите Лист 5 «Доходы». На Лист 5 введите таблицу по образцу (табл. 2.18). Выберите границу и заливку произвольно.

 

Таблица 2.18

Семейные доходы
Месяц	Отец	Мать	Ребенок	Итого
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь
Итого
Среднее значение
Минимум
Максимум

3. Для расчета в столбце Е (Итого) выделите построчно ячейки в столбцах В, С, Dи выберите вкладку Главная • Редактирование • Сумма «Σ». При помощи автозаполнения произведите расчет в остальных ячейках.

4. Чтобы рассчитать результат в строке «Итого» по столбцам В, С, D, необходимо выделить ячейки в столбце и произвести автосуммирование.

5. Для подсчета значения в строке «Минимум» выделите ячейку для получения результата выберите вкладку Главная • Редактирование • Сумма «Σ» • Минимум и задайте область расчетов - ячейки с января по декабрь в столбце, по которому производиться вычисление.

6. Для подсчета значения в строке «Максимум» выделите ячейку для получения результата, выберите вкладку Главная • Редактирование • Сумма «Σ» • Максимум и задайте область расчетов - ячейки с января по декабрь в столбце, по которому производится вычисление.

 

 

Таблица 2.18. Итоговая

Семейные доходы
Месяц	Отец	Мать	Ребенок	Итого
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь
Итого
Среднее значение			333,3333	32083,3333
Минимум
Максимум

 

7. На листе 6 наберите таблицу по образцу (табл. 2.19). Выберите границу и заливку произвольно.

 

Таблица 2.19

Семейные расходы
Месяц	Квартира	Телефон	Транспорт	Питание	Одежда	Досуг	Прочее	Итого	Баланс
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь
Итого
Среднее значение
Минимум
Максимум

8. Для расчета в столбце I (Итого) выделите построчно ячейки в столбцах В-Н и выберите вкладку Главная • Редактирование • Сумма «Σ». При помощи автозаполнения произведите расчет в остальных ячейках.

9. Поизвести расчет в графе «Баланс» по формуле:

 

Баланс = 'Доходы'!Е3-'Расходы'!I3

Открыть Лист 6, в столбце «Баланс» поставить знак «=», войти в Лист 5, в столбец «Итого» таблицы «Семейные доходы».

Выделить первую строку в столбце «Итого», поставить знак «-» минус, войти на Лист 6, выделить первую строку в столбце «Итого» таблицы «Семейные расходы», нажать клавишу Enter, результат появится в ячейке. Тянуть за крестик (+) по всему столбцу «Баланс» до «Декабря».

10. Для подсчета в строке «Среднее значение» выделите ячейку для получения результата, выберите вкладку Главная • Редактирование • Сумма «Σ» • Среднее значение и задайте область расчетов - ячейки с января по декабрь в столбце, по которому производится вычисление.

11.Аналогично п. 5 и 6 рассчитайте Максимум и Минимум в табл. 2.19.

 

Таблица 2.19. Итоговая

Семейные расходы
Месяц	Квартира	Телефон	Транспорт	Питание	Одежда	Досуг	Прочее	Итого	Баланс
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь
Итого
Среднее значение	1641,667	445,5833	804,58333	14651,08	1204,167	258,3333		19190,42	12892,92
Минимум
Максимум

12. Удерживая нажатой клавишу Shift или Ctrl (зависит от настройки клавиатуры), выделите столбцы А (Месяц) и любой другой с данными расчета результатов и постройте резаную круговую диаграмму (рис. 2.22).

 

Рис. 2.22.

Диаграмма отражает изменение расходов на телефонную связь в течение одного года.

13. Сохраните файл.