Электронные таблицы Excel

Лабораторная работа № 1

Организация управленческого учета в среде Excel

Немного теории

Электронные таблицы Excel

Excel — программа, не требующая для ее использования специальной подготовки. Руководители предприятий, не имеющие экономического образования, легко и быстро ориентируются в электронных таблицах, и нередка ситуация, когда главный бухгалтер готовит различные отчетные выборки именно в этом формате. Плохо лишь то, что в бухгалтерской программе просто не учитывается ряд данных:

аналитика по движению денег, — а движение денег по «директорской» кассе и вовсе не отражается в бухучете;

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

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

Тем не менее, если отдельные листы электронных таблиц посвящены именно этим недостающим данным, ячейки связаны между собой формульно, то построить модель управленческого учета и бюджетирования в Excel — это реально.

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

об организационной структуре предприятия;

о финансовой структуре предприятия;

об управленческой учетной политике;

о бюджетных регламентах и т.д.

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

в разрезе покупателей (кто входит в 20% покупателей, дающих 80% дохода/прибыли),

в разрезе товаров (какой товар входит в 20% «звезд», дающих 80% дохода/прибыли),

то же по отношению к менеджерам или поставщикам.

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

Самая серьезная проблема электронных таблиц — необходимость извлечения вручную данных оперативного учета, сложности с перерасчетом данных, если хоть одна цифра изменилась, крайняя сложность построения баланса, потому что в электронных таблицах нет контроля двойными записями. Но самое главное — залогом достоверности данных может служить только профессионализм и добросовестность специалистов, работающих с программой Excel.

Итак, плюсы использования электронных таблиц в управленческом учете:

не требует затрат на приобретение ПО (входит в пакет программ Microsoft Office);

нет необходимости в обучении персонала работе на данном ПО;

широкие возможности форматирования отчетов;

построения любых графиков.

Минусы:

Большая вероятность появления в расчетах ошибок, непроизвольного удаления данных. «Полуавтоматизированный» режим работы. Для получения консолидированной отчетности требуется достаточно много времени для сведения данных. Заложенная архитектура в файлах Excel не позволяет получать отчеты за любой период времени.

Задание 1. Работа с таблицами

1. Открыть файл Excel и разместить на листе таблицу из выбранного варианта.

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

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

 

Рассчитать суммы распределения прибыли в НПО.

 

Показатели	Нормативы распределения, %	Сумма отчислений прибыли, млн. руб.
Прибыль, всего Отчисления в бюджет Отчисления на собственные нужды: в фонд развития производства в фонд мат. поощрения в фонд соц. развития		35,4 ?   ? ? ?

 

 

Вариант 2. Выполнить расчет движения материальных ценностей по складу по балансовым счетам 051, 052, 055 (в тыс. руб.).

 

Показатели, тыс. руб.	Номер счета	Всего по складу
Остаток на начало года Приход за год Расход за год Остаток на конец года в % к началу года	? ?	? ?	? ?	? ? ? ? ?

 

 

Вариант 3. Рассчитать, какой процент занимают расходы на продукты в общей сумме расходов (коэффициент Энгеля).

 

Виды расходов, руб.	Группа 1	Группа 2	Группа 3	Группа 4	Группа 5
Продукты
Жилье
Комм. услуги
Одежда
Другие расходы
Итого	?	?	?	?	?
К-т Энгеля, %	?	?	?	?	?

 

 

Вариант 4. Определить экономические показатели фирмы «Геркулес» в отчетном периоде.

Сегмент рынка	Себестоимость единицы продукции, руб.	Цена единицы продукции, руб.	Объем реализации, тыс. шт.	Затраты на производство, тыс. руб.	Выручка от реализации, тыс. руб.	Прибыль, тыс. руб.
A B C D				? ? ? ?	? ? ? ?	? ? ? ?
Итого	?	?	?	?

 

 

Вариант 5. Рассчитать ежедневный предполагаемый доход от деятельности киносети.

 

Кинотеатры	Вместимость зрительного зала, мест	Средний % посещаемости	Средняя цена билета, руб.	Количество сеансов в день	Доход, тыс.руб.
Родина Сибиряк Аэлита Авангард					? ? ? ?
Ср. знач.	?	?	?	?	?
Итого		?

 

Вариант 6. Проанализировать динамику поступления товаров от поставщиков.

 

Поставщики	2010 г., млн. руб.	2011 г., млн. руб.	Превышение	В % к 2010 г.	Удельный вес в 2010 г.	Удельный вес в 2011 г.
ООО «Прима» АОЗТ «Томь» ЧП «Сантик» ОАО «Гермес» Всего	15,5 23,4 0,96 7,5 ?	16,9 32,1 1,2 6,4 ?	? ? ? ? ?	? ? ? ? ?	? ? ? ? ?	? ? ? ? ?

 

 

Вариант 7. Составить таблицу начисления заработной платы работникам МП «МП-Траст».

 

Ф.И.О.	Тарифный разряд	% выполнения плана	Тарифная ставка	Заработная плата с премией
Пряхин Н.П. Суховейко А.Д. Лоханкин В.А. Пферд Л.Ф. Севрюгов Л.А. Гигиенишвили Г.С. Птибурдуков А.И.			? ? ? ? ? ? ?	? ? ? ? ? ? ?

Примечание 1. Тарифная ставка определяется в зависимости от разряда: 1-й разряд – 12000 руб.; 2-й разряд – 15000 руб.; 3-й разряд – 20000 руб.

Примечание 2. Размер премиальных определяется в зависимости от выполнения плана:

- ниже 100% – премия не начисляется;

- 100% – премия 20% от тарифной ставки;

- 101…110% – премия 30%;

- 111…115% – премия 40%.

Вариант 8. Рассчитать поступление и расходование денежных средств избирательных фондов зарегистрированных кандидатов в депутаты на должность главы администрации.

 

Показатели	Ф.И.О. зарегистрированного кандидата
Полесов В.М.	Чарушников М.П.	Кислярский М.Б.
Сумма, руб.	% к всего	Сумма, руб.	% к всего	Сумма, руб.	% к всего
Поступило средств всего, в том числе: от избирательной комиссии собственные средства кандидата пожертвования юридических лиц пожертвования физических лиц	?             —	?     ?   ?   ?	?             —	?     ?   ?   ?	?	?     ?   ?   ?
Израсходовано средств всего, в том числе: радио и телевидение печатные издания публичные мероприятия канцелярские расходы Аренда помещений и автотранспорта Прочие расходы	?     —     —   —	?   ?   ?   ?     ? ?	?	?   ?   ?   ?     ? ?	?	?   ?   ?   ?     ? ?
Остаток неизрасходованных средств	?	?	?

Вариант 9. Рассчитать доход от реализации колбасных изделий ОАО «Царицыно».

 

Наименование изделий	Объем производства, т	Цена за кг, руб	Торгово-сбытовая скидка, %	Цена со скидкой	Сумма
Колбаса пермская п/к, 1с. Колбаса одесская п/к, 1с. Колбаски охотничьи, п/к, в/с, Колбаса польская п/к, 2с. Колбаса таллиннская п/к, в/с.			8,5   7,8   8,5	?   ?   ?   ?   ?	?   ?   ?   ?   ?
Всего	?				?

 

 

Вариант 10. Рассчитать плановую сумму прибыли ЧП «Московские баранки».

 

Показатели	Год	2011 г в % к 2009г.
1. Объем продаж, т 2. Цена единицы продукции, руб./кг 3. Выручка от реализации продукции, руб. 4. Постоянные расходы, руб. 5. Переменные расходы (60% от ст. 3), руб. 6. Общая себестоимость продукции, руб. 7. Удельная себестоимость продукции, руб. 8. Валовая прибыль, руб.	?   ?   ?   ? ?	?   ?   ?   ? ?	?   ?   ?   ? ?	?   ?   ? —   —   ?   ? ?

Вариант 11. Рассчитать структуру розничной цены продукции (руб.), исходя из следующих данных:

 

Составляющие	Виды продукции
А	В	С	D
1. Себестоимость 2. Рентабельность (25% от п.1) 3. Оптовая цена предприятия 4. Акциз (70% от оптовой цены) 5. Наценка посредника 6. НДС (20% от отпускной цены фирмы) 7. Торговая наценка 8. Розничная цена	? ? ?   ? ?	? ? ?   ? ?	? ? ?   ? ?	? ? ?   ? ?

 

 

Вариант 12. Используя рекламную прессу (газета «Реклама»), проанализировать затраты на рекламу производителей товаров и услуг в Томске по различным направлениям. Стоимость рекламных объявлений в газете «Реклама» (в рублях за 1 кв. см): первая полоса - 29, последняя полоса - 18, полоса с программой ТВ - 15, обычная полоса - 9,8 (эти данные оформить отдельной таблицей и ссылаться на них через адреса ячеек).

 

 

Секторы рынка	Площадь объявлений, кв. см	Затраты на рекламу, тыс. руб.	Место по затратам
Первая полоса	Последняя полоса	Полоса с программой ТВ	Обычная полоса
Информационные технологии и услуги	430,8	135,0	208,8	5363,5	?	?
Продовольственные товары	41,3			4191,3	?	?
Строительно-хозяйственные товары и услуги	149,3	138,5	488,3	3697,8	?	?
Предметы гигиены и санитарии				949,0	?	?
Одежда и обувь			108,0	534,5	?	?
Мебель и торговое оборудование	134,0			2071,3	?	?
Лекарства и медицинские услуги			42,3	568,0	?	?
Автотовары и автоуслуги	50,0			2648,1	?	?
Бытовая техника и ее обслуживание		420,0		1680,3	?	?
Недвижимость	76,3			2087,3	?	?
Итого	?	?	?	?	?	?

 

Вариант 13. Рассчитать сумму, которая будет на счету в конце каждого месяца, если в начале года внесено 10000 руб., для различных годовых норм банковского процента i.

 

Годовая норма i,%

Период, месяц

? 4

 

Примечание. Формула для расчета сложных процентов имеет вид:

,

где P₀ – первоначально вложенная сумма;

P_m – сумма, которая будет получена через N лет;

m – количество начислений процентных платежей в течение годового периода;

i – годовая норма процента.

 

Вариант 14. Одним из показателей, определяющих эффективность планируемых инвестиций, является показатель чистой текущей стоимости, вычисляемый по формуле:

,

где R_t – доходы (за вычетом налогов) от реализации проекта в t- ом году, руб.;

С_t – инвестиции в проект в t- ом году, руб.;

К – норма дохода по проекту;

n – период реализации проекта в годах.

Из четырех предлагаемых проектов выбрать наиболее эффективный, используя функцию MAX. Норма доходности инвестиций – 10%. Расчеты оформить в виде таблицы, добавив к таблице исходных данных необходимое число строк или столбцов.

 

Период	1-й проект	2-й проект	3-й проект	4-й проект
Rt	Ct	Rt	Ct	Rt	Ct	Rt	Ct
1-й год   2-й год   3-й год   4-й год   5-ый год
Чистая текущая стоимость	?	?	?	?