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

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

Задание 1 (1 балл)

Переименовать первый лист книги MS Excel в ТитЛист, оформить титульный лист – указать свою фамилию, имя, группу, придумать название фирмы, описать направления ее хозяйственной деятельности, разработать логотип.

Задание 2 (5 баллов)

Переименовать второй лист книги MS Excel в Справочники-1. Для каждого справочника:

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

• форматировать как таблицу (Главная)

• отделить название от списка пустой строкой;

• в первой строке списка привести полужирным шрифтом имена полей (присвоить имена таблицам в режиме Конструктора);

• закрепить строку имен полей, чтобы она не исчезала при перемещении по экрану (Вид / Закрепить области);

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

Создать и заполнить справочники:

• «Должности сотрудников организации» – одно текстовое поле с названием «должность». Заполнить 3-5 строк, предусмотреть возможность расширения до 10 позиций.

• «Сотрудники организации»

1. текстовое поле «сотрудник» с указанием Ф.И.О. сотрудника – вводится пользователем (5–10 сотрудников).

2. текстовое поле «должность» занимаемой сотрудником должности – организовать с помощью создания раскрывающегося списка из диапазона ячеек справочника Должностей:

• присвоить имя «должность» диапазону должностей (предусмотреть место для новых данных);

• в заполняемом поле выполнить Данные / Проверка данных / Тип данных: список; Источник: =ДВССЫЛ("должности[должность]").

• «Поставщики» – текстовые поля: «поставщик» (указать название организации), «адрес», «ФИО контактного лица», «телефон», «эл. почта».

Справочник заполняется пользователем с помощью Формы. Предварительно следует вывести кнопку Форма на панель быстрого доступа: правая кнопка мыши на ленте / Настройка панели быстрого доступа / Выбрать команды из: Все команды / Форма / Добавить >>.

Ввести данные для 4–5 поставщиков. Так как по условиям задачи имеется собственное производство хлебобулочных изделий, то одним из поставщиков должно собственное производство.

• «Клиенты» (потребители продукции) – текстовые поля: «клиент» (указать название организации), «адрес», «ФИО контактного лица», «телефон», «эл. почта». Справочник заполняется пользователем с помощью Формы, ввести данные для 4–5 клиентов.

• «Пустой» одно текстовое поле с названием «пустой», одна ячейка ---.

---

Переименовать третий лист книги MS Excel в Справочники-2. Создать и заполнить справочники:

• «Товарные группы» – столбец «товарная_группа» (3–5 названий товарных групп); код группы (целочисленное значение, например, 100, 200 и т.д.). Обязательная товарная группа «хлебобулочные изделия» с кодом 100.

• «Постоянные затраты»: фиксируются все постоянные затраты в течение месяца (аренда склада, кондитерского цеха, автотранспорта, зарплата персоналу, реклама оплата электроэнергии и т.д.). Структура справочника: «статья затрат»; «затраты, руб./месяц» – вводятся пользователем.

• «Единицы измерения»: столбец «ед_измерения» (шт., кг, коробка, упаковка, …).

• «Тип операции»: столбец «тип_операции» (приход, расход, списание).

Переименовать четвертый лист книги MS Excel в Товары. Создать и заполнить справочник «Товары (ТМЦ)». Регистрируемые поля:

№ поля	Имя поля	Источник данных	Примечания
1	наименование товара	вводится пользователем	5 товаров для каждой товарной группы
2	товарная группа	справочник товарных групп	выпадающий список
3	артикул	вводится пользователем	уникальное число, характеризующее товар, составляется на основе кода товарной группы, к которой относится товар. Например, если коды товарных групп равны 100, 200, 300 и т.д., то артикулы первой товарной группы могут быть 101, 102, 103 и т.д., артикулы второй товарной группы 201, 202, 203 и т.д.
4	ед. измерения	справочник единиц измерения	выпадающий список
5	цена реализации, руб./ед.	вводится пользователем
6	цена закупки, руб./ед.	вводится пользователем или, если известна себестоимость производства 1 кг продукта, вычисляется: ЦЗед=ЦЗкг*М	при наличии собственного производства переменные затраты на производство единицы измерения продукции
7	масса ед. измерения (М)	вводится пользователем	вес одной коробки, вес одной штуки, упаковки
8	цена закупки, руб./кг	вводится пользователем или, при известной цене закупки за единицу измерения, вычисляется: ЦЗкг=ЦЗед/М	при наличии собственного производства себестоимость производства за 1 кг продукции
9	цена утилизации брака, руб./кг	вводится пользователем	весь брак продается на переработку по низкой цене за 1 кг, вне зависимости от единицы измерения товара

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

Наименование товара	Единица измерения	Вес единицы измерения, кг	Цена реализации за ед. измерения, руб.	Цена закупки (затраты на производство), руб./кг	Цена утилизации брака, руб./кг
Батон	шт.	0,7	15	6	2
Торт	шт.	1,2	1000	500	2
Сушка	упаковка	0,5	40	30	2
Крекер	коробка	0,3	45	90	2

---

Замечание. Переставьте первые два столбца местами.

Задание 3 (7 баллов)

Переименовать пятый лист книги MS Excel в Движение ТМЦ. Организовать ввод данных в журнал движения ТМЦ:

№ поля	Имя поля	Источник данных	Примечания
1	дата операции	вводится пользователем	*организовать вызов календаря
2	тип операции	справочник типов операции	выпадающий список
3	наименование продукта (товара)	справочник товаров	выпадающий список
4	артикул	справочник товаров	ВПР(); предусмотреть автозаполнение после ввода наименования товара
5	товарная группа	справочник товаров	ВПР(); предусмотреть автозаполнение после ввода наименования товара
6	поставщик (наименование)	справочник поставщиков (указывать название организации)	выпадающий список; для операции расхода не указывать; *использовать пустой список для организации запрета ввода * использовать функцию ЕСЛИ, снять  игнорировать пустые ячейки
7	клиент (наименование)	справочник клиентов (указывать название организации)	выпадающий список; для операций прихода и списания не указывать; *использовать пустой список для организации запрета ввода
8	сотрудник, ответственный за сделку	справочник сотрудников	выпадающий список
9	наличие товара (до операции), ед.	расчет по предшествующим операциям прихода, расхода и списания	для подсчета общего прихода (расхода/списания) использовать СУММЕСЛИ(); предусмотреть автозаполнение после ввода наименования товара =СУММЕСЛИ($E$2:E2;[@[Наименование товара]];$J$2:J2)-СУММЕСЛИ($E$2:E2;[@[Наименование товара]];$M$2:M2)
10	объем выпуска (закупки) товара (ОВ)	вводится пользователем	организовать запрет ввода для операций расхода и списания: Данные / Проверка данных / Тип данных: Другой; Формула: =ЕСЛИ(<тип операции>="приход"; ИСТИНА; ЛОЖЬ) / Сообщение об ошибке: «не указывается для операций расхода и списания»
11	единица измерения (для выпуска/закупки)	справочник товаров	ВПР(); предусмотреть автозаполнение для операции прихода после ввода наименования товара
12	затраты на производство (или закупку) товара, руб.	расчет по формуле: ЗП=ЦЗед*ОВ	с использованием ВПР(); предусмотреть автозаполнение для операции прихода после ввода наименования товара
13	объем продаж (списания) товара	вводится пользователем	организовать запрет ввода для операции прихода, а также запрет ввода значения, превышающего наличие на складе: Данные / Проверка данных / Тип данных: Другой; Формула: использовать функции ЕСЛИ(), ИЛИ(); вывести сообщение об ошибке
14	единица измерения (для продаж)	справочник товаров	ВПР(); предусмотреть автозаполнение для операции расхода/списания после ввода наименования товара
15	выручка за товар, руб.	расчет по формуле: В=ЦР*ОП (операция расхода); В=ЦУ*М*ОП (операция списания)	расчет с использованием ВПР(); предусмотреть автозаполнение для операции расхода/списания после ввода наименования товара
16	текущий остаток, ед.	расчет по проведенным операциям прихода, расхода и списания	= наличие товара (до операции)+объём выпуска(закупки)товара - объём продаж(списания)товара

---

Задание 4 (1 балл)

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

Для выполнения задания нужно создать свои правила для каждого цвета. Выделить всю таблицу; выбрать Условное форматирование / Создать правило / Использовать формулу для определения форматируемых ячеек / ввести формулу: =<тип операции>="приход"; задать желаемый формат.

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

=$<тип операции>="приход"

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

Задание 5 (1 балл)

Переименовать шестой лист книги MS Excel в Итоги.

По соответствующим столбцам журнала движения ТМЦ рассчитать общую выручку В и величину переменных затрат ЗПер фирмы за месяц. По справочнику постоянных затрат определить постоянные затраты ЗПост фирмы за месяц.

Определить прибыль (ПР) и уровень рентабельности (Р) в целом по результатам хозяйственной деятельности фирмы:

Прибыль = Выручка – (ЗатратыПер + ЗатратыПост)

Рентабельность = Прибыль / (ЗатратыПер + ЗатратыПост) * 100 %

Сделать выводы об эффективности производства.

Вычислить коэффициент постоянных затрат:

k = ЗатратыПост / ЗатратыПер

Определить вклад постоянных затрат в себестоимость товара:

ВкладПостЗатр = k / (1+k) * 100 %

Проанализировать коэффициент и вклад постоянных затрат: за счет каких показателей следует добиваться повышения эффективности производства?

Задание 6 (2 балла)

Рассчитать значения прибыли (ПР), уровня рентабельности (Р) и процента брака (Б) по каждому товару:

Прибыль (i) = Выручка (i) – ЗатратыПер (i) *(1+k)

Рентабельность (i) = Прибыль (i) / (ЗатратыПер (i)*(1+k)) * 100 %

Брак (i) = Объем списания товара (i) / Объем выпуска товара (i) *100%

Расчеты выполнить в Справочнике товаров, дополнив его столбцами «Прибыль по товару», «Уровень рентабельности» и «Процент брака». Величину выручки, переменные затраты и объема выпуска по каждому товару найти по журналу движения ТМЦ с помощью функции СУММЕСЛИ(). Для подсчета объема списания товара использовать функцию СУММЕСЛИМН(). Предусмотреть автозаполнение столбцов после ввода наименования товара (ЕСЛИ).

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

Задание 7 (8 баллов)

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

---

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

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

Проанализировать полученный график: насколько востребован товар, насколько загружен склад (много ли остатков, которые «лежат мертвым грузом»).

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

Журнал движения ТМЦ дополнить столбцом «Текущее наличие, руб.», вычисления провести по формуле:

Текущее наличие, руб. = Текущий остаток, ед. * Цена закупки, руб./ед.

Величину цены закупки выбрать из справочника товаров с помощью функции ВПР(); предусмотреть автозаполнение столбца после ввода наименования товара (ЕСЛИ).

Для построения графика использовать Сводную таблицу и Мастер диаграмм (на диаграмме должно быть указано соответствие цветов и фактических данных). Например:

Проанализировать графики: какова востребованность товаров, загруженность склада, много ли остатков, которые «лежат мертвым грузом», есть ли товары, необеспеченные спросом.

7.3. Построить гистограммы и круговые диаграммы, отражающие активность Поставщиков: количество сделок по каждому Поставщику; суммы, затраченные на закупки ТМЦ у разных Поставщиков.

Использовать Сводную таблицу и Мастер диаграмм (на круговой диаграмме должны быть указаны доли каждого поставщика в общем количестве/сумме сделок). Например:

Аналогичные диаграммы построить по Клиентам.

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

7.4. По аналогии с заданием 7.3. построить диаграммы, отражающие активность менеджеров по продажам.

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

7.5. Построить линейчатую диаграмму по недополученной выручке. Например:

Для построения дополнить справочник товаров столбцами «текущий остаток товара на складе» и «недополученная выручка», предусмотреть автозаполнение после ввода наименования товара (ЕСЛИ).

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

---