Разработка учетных приложений в MS Office

Размещено на http://www.allbest.ru/

Федеральное государственное образовательное бюджетное учреждение

высшего профессионального образования

Финансовый университет при правительстве Российской федерации

(Финансовый университет)

Кафедра: «Информатика и программирование»

МЕТОДИЧКА

на тему: «Разработка учетных приложений в MS Office»

П.Б. Лукьянов

Москва 2013

Практическое задание

Используя MS Excel, требуется создать учетное приложение, в котором должна быть отражена петля управления производством и представлен анализ эффективности бизнеса через расчет показателей эффективности (прибыль, уровень рентабельности) и построение наглядных графиков и диаграмм.

В качестве примера относительно простого учетного приложения рассматривается типовая задача учета движения ТМЦ (товароматериальных ценностей). После построения учетного приложения выполняется собственно первичный учет (регистрация операций прихода/расхода различных товаров) и затем на основании зарегистрированного массива данных рассчитываются показатели эффективности по отдельному товару и предпринимательской деятельности в целом, а также строятся графики по динамике закупок или выпуску товара, динамике продаж, активности Клиентов, эффективности работы менеджеров и т.д. для последующей обоснованной корректировки в ведении бизнеса.

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

Таким образом, пользуясь только стандартными средствами MS Office, может быть решена значительная часть типовых профессиональных задач экономиста.

Выбор прикладной области

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

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

Удобство в использовании справочников заключается в значительном упрощении регистрации прихода/расхода ТМЦ, так как вместо многократного ввода в поля таблицы одних и тех же значений появляется возможность реализовать выбор этих значений из заранее заполненных таблиц. Грамотно спроектированная таблица учета движения ТМЦ позволяет оператору оперативно вводить сотни и тысячи записей в течение одного рабочего дня, формируя необходимый фундамент для последующего анализа всей хозяйственной деятельности, на основании чего могут быть сформулированы выводы и предложения по коррекции ведения бизнеса (рис. 1).

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

У Вас есть ассортимент выпуска: 3-4 товарные группы, в каждой из которых 5-10 товаров, известны данные по затратам, объему выпуска, продажам и т.д. В течение определенного периода времени выполняется многократная регистрация двух элементарных операций: приход товара на склад и списание товара со склада. Длительность анализируемого периода (производственного цикла) - 1 месяц.

Рис. 1. Структура учетного приложения

бухгалтерский учет таблица график

Перечень базовых справочников

При анализе задачи выделим несколько базовых справочников:

1. Справочник должностей сотрудников. Структура справочника: одно текстовое поле с названием должности.

2. Справочник сотрудников организации. Структура справочника: текстовое поле с указанием ФИО сотрудника; текстовое поле должности, занимаемой сотрудником, причем ввод должности сотрудника должен выполняться выбором должности из справочника должностей.

3. Справочники Поставщиков и Клиентов (потребителей продукции). Справочники схожи по регистрируемым показателям: название организации, ее адрес, ФИО контактного лица, телефон, электронная почта.

4. Справочник товарных групп. Состоит из столбца с названием товарной группы и кодом группы (целочисленное значение).

5. Справочник товаров (ТМЦ). Регистрируется:

1) товарная группа,

2) наименование товара,

3) артикул - уникальное число, характеризующее товар. Артикул составляется на основе кода товарной группы, к которой относится товар. Например, если коды товарных групп равны 1000, 2000, 3000 и т.д., то артикулы первой товарной группы могут быть 1001, 1002, 1003 и т.д., артикулы второй товарной группы 2001, 2002, 2003 и т.д.

4) единица измерения (шт., кг, коробка, упаковка, …),

5) цена реализации за единицу измерения,

6) цена закупки за единицу измерения (или при наличии собственного производства переменные затраты на производство единицы измерения продукции),

7) масса единицы измерения (вес одной коробки, вес одной штуки),

8) цена закупки (или себестоимость производства) за 1 кг продукции,

9) процент брака (при производстве), процент боя (при закупке).

10) цена утилизации брака, руб. / кг (весь брак продается на переработку по низкой цене)

Ввод значений в поле «Название товарной группы» выполняется выбором соответствующего значения из справочника «Товарные группы».

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

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

1) Название статьи затрат

2) Затраты по статье в течение месяца, руб.

Часть товаров и товарных групп задается студентом самостоятельно, исходя из его интересов и предпочтений. Вместе с тем, часть товаров должна быть одинакова для всех, чтобы легко можно было проверить правильность работы расчетных формул. В справочнике товарных групп должна быть товарная группа «Хлебобулочные изделия». В справочнике товаров должны быть товары со следующими характеристиками (см. Таблицу 1):

Таблица 1. Товары и их характеристики

Блок учета хозяйственной деятельности

Основная таблица учета движения ТМЦ

Больше всего времени при работе с учетным приложением тратится на заполнение таблицы движения ТМЦ, поэтому, чем удобнее будет организован ввод в эту таблицу, тем больше пользы принесет разработанное учетное приложение.

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

Таблица 2. Формат журнала движения ТМЦ

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

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

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

Обратите внимание, что каждая строчка таблицы отражает только ОДНУ ОПЕРАЦИЮ из трех возможных - это или приход товара на склад, или его списание со склада (расход), или списание бракованного товара.

Таким образом, если регистрируется приход товара, то поля «Объем продаж товара», «Выручка за товар» не заполняются. Наоборот, если регистрируется расход, то поля «Объем выпуска (закупки) товара», «Затраты на производство товара (закупочная стоимость)» остаются пустыми. Таким образом, беглого взгляда на таблицу достаточно, чтобы увидеть позиции прихода и расхода.

Для выполнения автоматических расчетов затрат по выпуску (закупке) и выручке необходимо «вытаскивать» необходимые поля (цена закупки, цена продажи, вес единицы измерения) из справочников. Для этого также нужно использовать функцию ВПР().

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

Для реализации этого механизма необходимо подсчитывать текущий остаток по каждому товару, для этого удобно использовать справочник товаров, дополнив его столбцом «Наличие на складе, шт», значения в котором должны обновляться после каждой операции прихода-расхода. Для выполнения сравнения понадобится функция СУММEСЛИ().

Требования к итоговой версии таблицы учета движения ТМЦ

1. Обеспечение удобства ввода данных. Использовать выпадающие списки для выбора типа операции, товара, клиента, поставщика, ответственного.

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

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

Используя формулу ЕСЛИ(), выводить или не выводить соответствующую единицу измерения товара. Так, если выбрана операция «Приход», выводить единицу измерения после поля регистрации прихода и не выводить ее для расхода, и наоборот.

4. Вычисление суммарной прибыли и уровня рентабельности всей хозяйственной деятельности. Прибыль и рентабельность должны пересчитываться по итогам каждой операции прихода-расхода. Требуется вычислять суммарную прибыль и рентабельность по данным движения всех товаров.

5. Вычисление прибыли и рентабельности по каждому товару.

Для расчета этих показателей требуется знать способ распределения постоянных затрат на выпускаемую и (или) производимую продукцию.

Учет постоянных затрат при производстве или закупке товара

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

Очевидно, чем дороже товар и чем больше его произведено, тем большая доля постоянных затрат должна быть отнесена к этому товару. Для расчета затрат по i-му товару (i = 1,…. N) будем использовать формулу:

Затраты i = Затр_перем i + Затр_пост i (1)

где Затр_пост i - доля постоянных затрат, отнесенная к i-му товару

Затр_пост i = k * Затр_перем i (2)

Формула (2) отражает тот факт, что вклад постоянных затрат должен быть прямо пропорционален переменным затратам, т.е. чем больше мы тратим средств на производство (приобретение) товара, тем большая доля постоянных затрат должна приходиться на этот товар.

Из (1) и (2) имеем:

Затраты i = Затр_перем i * (1 +k) (3)

Затр_перем i = Сi * Vi (4)

где Сi - себестоимость производства 1 кг товара в случае выпуска, или закупочная цена 1 ед. измерения товара в случае его закупки, и соответственно Vi - объем произведенного товара (в кг) в случае собственного производства, или количество единиц измерения закупленного товара.

По условиям задачи общие постоянные затраты нам известны. Для определения k используем следующие соотношения:

Затр_пост 1 + Затр_пост 2 + … + Затр_пост N = Затр_пост

k * (Затр_перем 1 + Затр_перем 2 + … + Затр_перем N ) = Затр_пост

k = Затр_пост / Затр_перем

Отметим важный смысл коэффициента k: через этот коэффициент определяется вклад постоянных затрат Вклад_пост_затр (в процентах) в себестоимость товара. Для оценки этого вклада запишем:

Вклад_пост_затр, % = Затр_пост i / Затраты i * 100 %

Вклад_пост_затр, % = k / (1+k) * 100 % (5)

Формула (5) важна тем, что дает понимание, за счет каких показателей добиваться повышения эффективности производства. Если k << 1, соответственно вклад постоянных затрат мал, то нужно сокращать переменные затраты. Если же значение k большое, то кроме сокращения переменных затрат имеет смысл подумать о способах сокращения постоянных затрат.

Блок анализа хозяйственной деятельности. Построение графиков

1. Движение товара за период учета

На основании заполненных данных таблицы учета движения ТМЦ построить графики движения товаров во времени: по оси Х откладываются даты совершения сделок, по оси Y - наличие товара на складе на дату совершения сделки. Для создания такого графика необходимо дополнить таблицу учета движения ТМЦ еще одним вычисляемым полем: «Текущее наличие, шт».

На одном графике нужно отразить динамику движения одного (любого) товара, на другом графике показать динамику движения нескольких товаров одной (любой) товарной группы.

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

2. Обороты по Клиентам, Поставщикам, Сотрудникам

Следующий график должен показать обороты по Клиентам. Смысл графика - увидеть самых активных контрагентов. По оси Х откладываются Клиенты, по оси Y - итоговая сумма сделок с каждым Клиентом.

Аналогичный график строится по Поставщикам.

Графики необходимы для определения самых активных Поставщиков и Клиентов. На основании графиков можно проводить переговоры с крупными Поставщиками о дополнительных скидках на приобретаемый товар и обсуждать с активными Клиентами меры по стимулированию их работы, предлагая им дополнительные скидки и бонусы.

Похожий график нужно построить по сотрудникам, выполнявшим сделки, но в этом случае по оси Y откладывается количество сделок каждого сотрудника, по оси Х - сотрудники. Лучшие сотрудники (выполнившие больше всего сделок в течение месяца) могут быть поощрены премиями, худшие - оштрафованы или уволены.

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

3. Анализ структуры брака в процентном и денежном выражении

На основании данных о списании брака по каждому товару за отчетный период (период ведения журнала движения ТМЦ), требуется построить две круговые диаграммы и две столбиковые.

На первой круговой диаграмме должна быть показана доля брака от суммарного количества закупленного (произведенного) товара по каждому товару за период учета движения ТМЦ, а на второй диаграмме нужно показать финансовые потери (упущенную выручку) от наличия брака по каждому товару.

Откуда взялся брак? На этот вопрос отвечает столбиковая диаграмма, где по оси X откладываются Поставщики, продавшие бракованный товар, а по оси Y откладывается величина финансовых потерь (упущенная выручка) от закупки товара у этого Поставщика. График нужен для того, чтобы в последующем отказаться от Поставщиков, продающих бракованный товар.

Кто на нашей фирме закупает бракованный товар? Как правило, менеджеры по закупкам - достаточно самостоятельные сотрудники, их задача - постоянно искать надежных поставщиков и закупать у них товар или сырье по минимальным ценам.

Если в результате закупок менеджера на фирму поступает бракованный товар, то необходимо выяснить, какие именно менеджеры поставляют брак, и нет ли здесь личного интереса менеджера в поставке такого товара (например, выплаты ему премиальных со стороны Поставщика за закупку брака).

Для этого нужен график, где по оси Х располагаются фамилии менеджеров, а по оси Y - упущенная выручка в результате их закупок.

4. Сравнение плановой и фактической выручки по товарам

За период ведения журнала движения ТМЦ часть приобретаемого или выпускаемого товара успешно продается, некоторая часть остается на складах. Графический анализ должен показать, сколько мы недополучаем выручки из-за нереализованного товара.

Итак, по каждому товару можно посчитать разность между планируемой и фактической выручкой. Расчет нужно вести по формуле:

Недополученная выручка = Остатки товара на складе * Цена реализации

По оси X располагаются товары, по оси Y - недополученная выручка. Товары с максимальными столбиками - это те товары, которые продаются хуже всего, и соответственно, или их закупку следует ограничить, или снизить цену реализации на эти товары.

Порядок сдачи учетного приложения № 1

Студент сдает преподавателю самостоятельно разработанное учетное приложение по учету и анализу движения ТМЦ (представляет файл в формате Excel), в результате сдачи работы получает итоговый балл, складывающийся из оценок по разделам, представленным в Таблице 3.

При сдаче работы студенту могут быть заданы уточняющие вопросы по этим разделам с целью выяснения его уровня знаний.

Таблица 3. Показатели, учитываемые при сдаче учетного приложения

Размещено на Allbest.ru