Понимание задачи

Дилер — это компания / физическое лицо, которое заключает соглашение с производителем или поставщиком с целью продажи его товаров.

Доход дилера равен разнице цены продажи и покупки товара (иначе - торговая наценка). Указанная разница в ценах продажи и покупки товаров формируется за счет предоставления покупателям дилерской скидки.

Итак, доход дилера зависит от следующих показателей:

D – количество товара

F - цена покупки

G - цена продажи

H – форма расчетов, покупка (наличные/ безналичные)

I – форма расчетов, продажа (наличные/ безналичные)

M - комиссия 1

N - комиссия 2

При этом, в зависимости от сочетания форм расчетов при покупке и продаже в формуле расчета дилерского дохода применяется либо комиссия 1, либо комиссия 2.

Таким образом, множество условий неизбежно вызовет проблемы расчета, если воспользоваться формулой вложенных ЕСЛИ().

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

Указанная проблема расчета дилерского дохода решается путем применения комбинированного метода MS Excel за счет сочетания: (1) инструмента выпадающих списков и (2) макроса VBA Excel – в форме пользовательской функции.

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

Комбинированный метод расчета дилерского дохода

Исходные данные и условный расчет дилерского дохода приведены на Рис. 1.

Рис. 1 - Расчет дохода дилера в зависимости от формы расчетов и соответствующей комиссии

Столбец 2 содержит выпадающие списки, что удобно при назначении комиссии для разного вида товаров. Столбцы 4, 6,7,8,9, 13 и 14 выделены цветом – они участвуют в расчете дилерского дохода и являются входными данными для пользовательской формулы GetValue. Столбцы 8 и 9 также содержат выпадающие списки при различных сочетаниях форм расчетов и, как следствие - назначение соответствующей комиссии в столбцах 13 и 14. Код пользовательской формулы для расчета дилерского дохода приведен ниже.

' GetValue - пользовательская функция расчета дохода дилера
' D - количество товара
' F - цена покупки
' G - цена продажи
' H - форма расчетов, покупка
' I - форма расчетов, продажа
' M - комиссия 1
' N - комиссия 2

Public Function GetValue(D As Variant, F As Variant, G As Variant, H As String, I As String, M As Variant, N As Variant) As Variant

With ActiveSheet

If H Like "Безнал" And I Like "Безнал" Then ' комбинация форм расчетов покупки-продажи
GetValue = ((G * D) - (F * D)) - ((G * D) * N - (F * D) * N) ' доход дилера

ElseIf H Like "Безнал" And I Like "Наличные" Then ' комбинация форм расчетов покупки-продажи
GetValue = ((G * D) - (F * D)) - ((G * D) * M - (F * D) * N) ' доход дилера

ElseIf H Like "Наличные" And I Like "Безнал" Then ' комбинация форм расчетов покупки-продажи
GetValue = ((G * D) - (F * D)) - ((G * D) * N - (F * D) * M) ' доход дилера

ElseIf H Like "Наличные" And I Like "Наличные" Then ' комбинация форм расчетов покупки-продажи
GetValue = ((G * D) - (F * D)) - ((G * D) * M - (F * D) * M) ' доход дилера
Else
GetValue = 0
End If
End With
End Function

Вывод

Приведенный метод расчета может быть использован при разработке моделей расчета дилерских доходов. Информация будет полезна аналитикам, изучающим методы и функции Excel, а также аналитикам баз данных.