| Рис. 4.1 Описание продаж и выплат |
При выпуске различных произведений расчет авторских отчислений может оказаться достаточно сложным, если автору выплачивается процент от проданных экземпляров произведения. Рассмотрим простой пример Определим выплаты гонораров авторам нескольких книг, которое произвело издательство.
Вся нужная информация размещается на двух листах рабочей книги Excel. Вначале заполним лист с информацией о продаже книг и о выплатах авторам (Рис 4.1). На этом листе никаких формул нет, просто ведутся два списка. Списки можно расширить, например, добавив дату операции и количество проданных книг Однако для.расчетов эта информация не нужна, и мы не будем ее добавлять, чтобы не усложнять таблицу.
Введите заголовки столбцов, после чего введите в строки, начиная с пятой, исходные данные. В списке продаж указывается название книги и общая стоимость проданной партии книг. В списке выплат вводится фамилия и инициалы автора, а также выплаченная ему сумма. Переименуйте текущий лист Excel, назвав его Операции. Далее перейдите на другой лист рабочей книги, чтобы ввести дополнительные исходные данные и формулы для расчетов.
Кроме заголовка, на втором листе располагается информация обо всех авторах, работающих с издательством. В нашем примере описаны два автора (Рис.4.2), но их количество может быть любым. В ячейки с A3 по A7 введите названия полей. В ячейку ВЗ введите фамилию и инициалы одного из авторов, а в ячейку В4 введите сальдо на начало периода. Если автор должен издательству, сальдо вводится отрицательным, а если издательство должно автору, сальдо будет положительным. Отметим, что для каждой книги далее будет введено собственное начальное сальдо, а пока нужно ввести долги, не связанные с перечисленными книгами, например за книгу, полностью распроданную в предыдущем периоде.
В ячейку В5 введите формулу =CУMM(G10:G12). Внашем примере у автора три книги. Если в вашем случае количество иное, нужно просуммировать другое количество ячеек, например G10:G15. Далее в ячейку В6 введите формулу
=СУММЕСЛИ(Операции!$D$5:$D$100;ВЗ;Операции!$Е$5:$Е$100)
Функция условного суммирования СУММЕСЛИ проверяет на листе Операции ячейки $ D$5:$D$100. В этих ячейках расположена фамилия в списке выплат. Если значение в какой-то строке равно значению из ячейки ВЗ, то есть фамилии автора, значение из соответствующей строки столбца Е используется при суммировании. То, что используется именно столбец Е описано выражением Операции!$Е$5:$Е$100. Врезультате суммируются все выплаты данному автору, внесенные в список на листе Операции. В этой и других формулах считается, что проверяются строки до сотой включительно. Если у вас списки большего размера, вставьте в формулу большее число.
В ячейку В7 введите формулу =В4+В5-В6. К начальному сальдо добавляются все начисления и вычитаются произведенные ранее выплаты. Получаем сумму, которую нужно выплатить автору. Теперь нужно рассчитать конкретные суммы начислений.
| Рис. 4.2Расчет вознаграждений |
Введите в девятую строку таблицы заголовки столбцов. Далее нужно ввести строки с описанием расчетов по определенным книгам. Вначале введите названия книг в столбце А. Будьте внимательны, так как, если названия будут отличаться от названий книг в списке продаж, расчеты будут неверными. Далее в ячейки столбца В введите сумму фиксированной выплаты за книгу. Эта сумма не зависит от тиража, стоимости и тому подобного. В следующем столбце вводится процент авторского вознаграждения от отпускной цены издательства. Этот процент выплачивается после продажи соответствующей части тиража. В ячейки столбца D введите сумму аванса. В отличие от фиксированной выплаты, аванс является частью выплаты процентов. До тех пор, пока проценты от проданных книг не превысят аванс, автор не будет получать деньги за проданные книги. После того, как аванс полностью погашен, автор получает выплаты процентов за каждую проданную партию. В следующую ячейку вводится начальное сальдо. В следующие два столбца вводятся расчетные формулы. Введите в ячейку F10 формулу
=(СУММЕСЛИ(Операции!$А$5:$А$100;А10;
Операции!$В$5:$В$100)) *С10
Естественно, формула вводится в одной строке. Функция СУММЕСЛИ суммирует значения из ячеек Операции!$В$5:$В$100, то есть суммы продаж партий книг. При этом для суммирования отбираются только строки, в которых значение в столбце А листа Операции совпадает со значением в ячейке А10, то есть с названием нужной книги. Это как раз и описано с помощью первого и второго аргумента функции. Результатом суммирования является общая сумма всех проданных книг заданного наименования. Выражением * С10 данная сумма умножается на процент, причитающийся автору. В итоге получается начисленное авторское вознаграждение без учета аванса, фиксированной выплаты и сальдо на начало периода.
Для вычисления окончательной суммы введите в ячейку G10 формулу
=ЕСЛИ(F10<D10;D10;F10)+E10+B10. Если выплаты по процентам меньше аванса, начисляется аванс. В противном случае начисляются выплаты с учетом проданных книг. К полученной сумме добавляются сальдо на начало периода и фиксированная выплата. В итоге получается реально начисленная сумма по одной из книг. Выделите ячейки F10 и G10, протяните формулы из этих ячеек на расположенные ниже строки, чтобы вычислить начисления по всем книгам данного автора.
Все расчеты для одного автора закончены. В ячейке B7 выводится сумма, причитающаяся автору. Выдав ему нужную сумму, опишите эту операцию в списке на листе Операции. После этого сумма к выдаче будет равна нулю. По мере продажи книг и внесения соответствующих записей в список продаж, сумма к выдаче будет увеличиваться.
Чтобы рассчитать выплаты другому автору, скопируйте строки с третьей по двенадцатую в нижнюю часть листа. После этого нужно изменить исходные данные, описав другого автора. Если изменится количество книг, нужно будет также изменить формулу в ячейке Всего начислено. Суммировать нужно начисленные суммы по всем книгам. Если книга написана в соавторстве, учет должен вестись для каждого автора отдельно. При этом указывается процент выплат по книге для каждого из авторов.
Описанные приемы можно использовать при всех расчетах, в которых нужно выплачивать определенный процент от полученного дохода. Ведя списки продаж и выплат, вы легко сможете получить н нужную сумму, воспользовавшись функцией условного суммирования СУММЕСЛИ.
ПРАКТИЧЕСКАЯ РАБОТА №5
