Задача 4.5. Расчет квартальной премии бригады

Предметная область: учет заработанного дохода рабочими бригады. Элементы предметной области: фамилии рабочих бригады (15 человек), тариф – оплата за 1 день (определенный для каждого рабочего), фактическое число ежемесячно отработанных дней, ежемесячная норма рабочих дней (одинаковая для всех).

Отношения:

• заработанный доход начисляется по числу фактически отработанных дней:

= тариф * число фактически отработанных дней;

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

• при наличии в предыдущем месяце премии тариф рабочего в текущем месяце повышается на 15% по сравнению со значением тарифа прошлого месяца. При отсутствии премии тариф текущего месяца сохраняется на уровне предыдущего;

• по итогам трех месяцев начисляется квартальная премия:

= средний заработок за квартал * суммарное число сверхурочных дней;

• средний заработок вычисляется по формуле:

= суммарный заработок за квартал / фактическое число отработанных дней;

• со всего заработанного дохода ежемесячно взимаются налоги согласно шкале:

Заработанный доход (грн)	ПС налога
До 17	0
От 17 до 70	10%
От 70 до 150	15%
От 150	20%

Требования:

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

• начиная со второго листа, использовать формулы с двумерными ссылками для «вытягивания» фамилий рабочих и их тарифов с предыдущего листа;

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

• на пятом рабочем листе с помощью формул с двумерными ссылками сделать для каждого рабочего сводку месячных и квартальной премий и выполнить графическую интерпретацию этих данных (гистограмма с накоплением);

• выполнить числовое и стилевое форматирование данных.

Задача 4.6. Поток товарно-материальных запасов

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

Отношения:

• цены реализации на свежие морепродукты устанавливаются в зависимости от сроков хранения по следующей схеме:

• при продаже товара в день поставки цена реализации на 70% выше цены поступления;

• если товар не продан в день поступления, то цена его реализации снижается каждый день на 12% по сравнению с ценой реализации предыдущего дня;

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

• выручка – это произведение цены реализации на количество проданного товара;

• валовой доход – это произведение количества проданного товара на разность между ценой реализации и закупочной ценой; если товар изъят из продажи из-за истечения срока хранения, то в валовом доходе фиксируются убытки:

= – закупочная цена * количество начального запаса.

Требования:

• разработать и реализовать табличную модель учета движения товарно-материального запаса на протяжении 5 дней, реализовав ее на 5 листах рабочей книги;

• выполнить анализ продаж и валового дохода за весь период (в отдельной рабочей книге);

• выполнить числовое и стилевое форматирование данных рабочих листов;

• выполнить графическую интерпретацию полученных результатов.

Методические указания к решению

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

1. Во вновь созданную рабочую книгу добавить рабочие листы.

2. Сгруппировать пять листов и ввести текст и данные диапазона А1:М6 рисунка 28.

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Учет движения товаров по состоянию на

?

2

Процентная ставка надбавки (ПСН)

70%

3

Процентная ставка скидки (ПСС)

12%

4

5

Название товара

Дата поставки

Закупочная цена

Срок хранения

Цена реализации

Остатки на начало дня

Продажи

Остатки на конец дня

Валовой доход

6

Допустимый

Фактический

Кол-во

Сумма

Кол-во

Выручка

Кол-во

Сумма

7

Рис. 28. Макет таблицы для задачи «Поток товарно-материальных запасов»

3. Не снимая объединения листов, ввести в ячейки E7, F7, H7, J7, K7, L7, M7 формулы:

   • фактический срок хранения (E7):

= текущая дата³ – дата поставки;

• цена реализации (F7):

= если (фактический срок хранения <= допустимого срока;

закупочная цена *(1+ПСН)*(1–ПСС)^Фактический срок хранения; 0);

• сумма остатков на начало (H7):

= цена реализации * количество остатков на начало;

• выручка (J7):

= цена реализации * количество остатков на начало;

• количество остатков на конец (К7):

= количество остатков на начало – количество проданного;

• сумма остатков на конец (L7):

= цена реализации * количество остатков на конец;

4. Разгруппировать листы, и на каждом листе, начиная со второго, ввести даты в ячейки J1 и формулы, «вытаскивающие» необходимые данные с предыдущего листа (A7:D7 и G7):

   • название товара (А7)

= имя предыдущего листа ! А7;

• количество остатков на начало (G7):

= имя предыдущего листа ! K7;

5. Сгруппировать листы, выделив диапазон А7:M7, скопировать формулы строки 7 вниз на 10 строк.

6. Разгруппировать листы, и на первом листе ввести названия товаров, даты поставок, закупочные цены и допустимые сроки хранения. Убедиться, что эти данные автоматически отобразились на остальных листах.

7. На каждом листе для каждого товара ввести проданное количество.

8. Выполнить остальные требования задачи.

9. Исследовать и обосновывать полученные результаты.