Перейдите на следующий лист вашей рабочей книги и присвойте ему название Расчет прибыли. Создайте таблицу по приведенному выше образцу и отформатируйте ее текстовую часть. В заголовке колонок вместо надписей Товар 1, Товар 2 и т.д. вставьте формулу, ссылающуюся на соответствующие ячейки с названиями товаров таблицы Итог поступления. Установите нужные форматы данных.
В строке % НДС для всех видов товара введите значение 20% (используя копирование). Для строки Отчисления на рекламу значения установите от 3% до 6% для каждого товара. Для строки % накладных расходов значения установите от 10% до 15% для каждого товара. Для расчета транспортных расходов на этом же листе (ниже таблицы расчетных коэффициентов) создайте таблицу расстояний до городов, в которых расположены фирмы-поставщики.
Таблица может иметь следующий вид:
| Товар | Товар 1 | Товар 2 | Товар 3 | Товар 4 |
| Страна | Голландия | Швеция | Германия | Москва |
| Расстояние | 3500 | 3000 | 2700 | 1000 |
Для строки Стоимость 1 км транспортировки значения установите следующим образом:
при расстоянии до поставщика до 1500 км используется автомобильный транспорт, тариф для которого 0,002% от стоимости единицы товара;
от 1500 до 2500 км — железнодорожный транспорт. Тариф 0,0025%;
свыше 2500 км — авиационный транспорт, тариф 0,0046%.
Для каждого из видов товара установите свой срок хранения на складе в пределах 1-6 суток. В строке Аренда помещения установите единое значение в размере от 2500 до 10000 рублей в месяц. Значение в строке Коммунальные услуги установите в размере 25 % от арендной платы за помещение. В строке Процент складских расходов установите единое значение 0,06% от стоимости товара за одни сутки хранения товара на складе.
Таблица расчета месячной прибыли фирмы «Дента» должна иметь вид:
| Закупка | |||||
| Наименование товара | Товар 1 | Товар 2 | Товар 3 | Товар 4 | Товар 5 |
| Закупочная цена | |||||
| Размер партии | |||||
| Стоимость партии | |||||
| Затраты: фиксированная стоимость на ед. товара | |||||
| Отчисления на аренду | |||||
| Отчисления на коммунальные услуги | |||||
| Отчисления на НДС | |||||
| Расходы на рекламу | |||||
| Прочие расходы | |||||
| Общая фиксированная стоимость на ед. товара | |||||
| Переменная стоимость на ед. товара | |||||
| Накладные расходы | |||||
| Транспортные расходы | |||||
| Складские расходы | |||||
| Расходы на упаковку | |||||
| Общая переменная стоимость на ед. товара | |||||
| Продажа | |||||
| Отпускная цена ед. товара | |||||
| Реализованная партия | |||||
| Стоимость реализованного товара | |||||
| Прибыль | |||||
| Прибыль за ед. товара | |||||
| Прибыль за партию | |||||
| Месячная прибыль | |||||
| Суммарная прибыль за месяц | |||||
Выполнение:
Значения во всех ячейках с данными вычисляются по формулам. Вычисления производятся в следующей последовательности:
В первую ячейку строки «Наименование товара» запишите формулу, ссылающуюся на первую ячейку с наименованием товара таблицы «Итог поступления». Формулу скопируйте в остальные ячейки строки. Аналогичным образом заполните строки «Закупочная цена» и «Размер партии». Внесите информацию о размере реализованной партии товара в раздел Продажа (размер проданной партии товара должен быть меньше или равным величине приобретенной партии).
В первую ячейку строки «Стоимость партии» запишите расчетную формулу.
Перейдите к таблице коэффициентов и присвойте имена ячейкам с числовыми данными (кроме строки «Срок хранения товара в сутках»). Для присвоения имени выделите как диапазон всю таблицу (включая колонку с названиями строк), из меню Вставка/Имя/Создать выберите команду в столбце слева. Аналогичным образом присвойте имена ячейкам таблицы расстояний. Перейдите на лист с таблицей расчета месячной прибыли и аналогичным образом присвойте имена ячейкам разделов Закупка и Затраты.
Во все остальные ячейки колонки, относящейся к первому товару, запишите расчетные формулы. В формулах вместо адресов ячеек используйте их имена.
Примерный вид расчетных формул (избегайте циклических ссылок при написании формул):
Отчисления на аренду помещения = Аренда_помещения / СУММ (Размер_партии);
Отчисления на коммунальные услуги=Коммунальные_услуги/СУММ(Размер_партии);
Отчисления на НДС = Закупочная_цена * НДС; Расходы на рекламу = Закупочная_цена * Реклама; Прочие расходы = 500 руб / Размер_партии;
Накладные расходы = Закупочная_цена * % накладных_расходов;
Транспортные расходы = Закупочная_цена * Расстояние * Стоимость_ 1км_ транспортировки_от_стоимости_товара ;
Складские расходы=Закупочная_цена*%_складских_расходов*Срок_хранения _товара;
Расходы на упаковку=3акупочная_цена*0,01;
Отпускная цена ед. товара = Закупочная_цена + Общая_ фиксированная_ стоимость_на_ед._товара+Общая_переменная_стоимость_на_ед._товара+торговая наценка;
Прибыль на единицу товара = Отпускная_цена_ед. _товара -Закупочная_цена .
Примечание. Во избежание ошибок типа «Циклическая ссылка» названия строк в таблице и имена ячеек должны отличаться.
При расчете отпускной цены единицы товара следует учесть величину торговой наценки, которая принимается в пределах 5—10% от закупочной цены товара.
Формулы, записанные в колонку для первого товара, скопируйте в колонки, описывающие остальные товары. Листу с таблицей присвойте имя Расчет прибыли. Создайте таблицу распределения прибыли фирмы между сотрудниками пропорционально их должностному окладу. Полученная фирмой прибыль должна распределиться следующим образом:
25% — отчисления в бюджет,
15% — на расширение предприятия,
оставшаяся часть — фонд заработной платы сотрудников.
Шаг 4. Для расчета заработной платы необходимо составить таблицу распределения прибыли. Последовательность ее составления следующая:
Скопируйте на новый лист книги таблицу «Кадры» и замените старое название
таблицы на новое «Распределение прибыли». Удалите столбцы «Пол», «Дата рождения» и «Домашний адрес». Дайте имя рабочему листу: «Распределение прибыли»; Подсчитайте сумму должностных окладов и занесите в последнюю ячейку колонки с данными должностных окладов.
Добавьте в таблицу новую строку под названием «Фонд зарплаты», в первую ячейку которой запишите формулу, которая определяет фонд зарплаты (см. пояснения к заданию). При составлении формулы используйте ссылку на ячейку с данными суммарной прибыли вашей фирмы (таблица расчета месячной прибыли). Вставьте в расчетную формулу функцию ОКРУГЛО.
Добавьте в таблицу новую колонку «Начислено». В ячейках новой колонки запишите формулу, распределяющую прибыль пропорционально должностному окладу каждого из сотрудников фирмы. Проверьте правильность выполненного расчета, используя встроенный в Microsoft Excel калькулятор. Значение, полученное на калькуляторе, должно быть равно значению ячейки «Фонд заработной платы».
Шаг 5. Используя таблицу распределения прибыли, составьте расчетно-платежную ведомость. Скопируйте полученную при выполнении предыдущего задания таблицу на новый лист рабочей книги. Дайте листу новое название «Ведомость». Создайте новые колонки — «Подоходный налог» и «Пенсионный фонд», объединенные под общим названием Вычеты, а также колонки «К получению» и «Роспись». Измените название таблицы на «Расчетно-платежная ведомость».
В колонку «Подоходный налог» запишите формулу для расчета подоходного налога: 13% от ЗП. В формуле используйте функцию округления до двух десятичных знаков. Отчисления в пенсионный фонд примите равными 3% от начисленной суммы. В колонку «К получению» запишите формулу расчета суммы, выдаваемой работнику на руки в качестве его заработной платы.
Колонки «Подоходный налог» и «Пенсионный фонд» скройте, используя меню Формат/Столбец/Скрыть. В ведомость добавьте колонку «Роспись».