Расчет авторских гонораров

Рис. 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