Статья знакомит читателя с комбинированным методом MS Excel (выпадающие списки + пользовательская функция) расчета дохода дилера.
Понимание проблемы
Дилер — это компания / физическое лицо, которое заключает соглашение с производителем или поставщиком с целью продажи его товаров.
Доход дилера равен разнице цены продажи и покупки товара (иначе - торговая наценка). Указанная разница в ценах продажи и покупки товаров формируется за счет предоставления покупателям дилерской скидки.
Итак, доход дилера зависит от следующих показателей:
D – количество товара
F - цена покупки
G - цена продажи
H – форма расчетов, покупка (наличные/ безналичные)
I – форма расчетов, продажа (наличные/ безналичные)
M - комиссия 1
N - комиссия 2
При этом, в зависимости от сочетания форм расчетов при покупке и продаже в формуле расчета дилерского дохода применяется либо комиссия 1, либо комиссия 2.
Таким образом, множество условий неизбежно вызовет проблемы расчета, если воспользоваться формулой вложенных ЕСЛИ(). При этом подходе необходимо для каждой сделки создавать 4 строки, чтобы проанализировать все варианты рассчитанного дохода при различных сочетаниях форм расчетов при покупке и продаже.
Указанная проблема расчета дилерского дохода решается путем применения комбинированного метода MS Excel за счет сочетания (1) инструмента выпадающих списков и (2) макроса VBA Excel – в форме пользовательской функции.
Выпадающие списки позволяют выбрать форму расчетов для каждого товара, а пользовательская функция – рассчитать доход дилера в зависимости от выбранных условий.
Комбинированный метод расчета дилерского дохода
Исходные данные и условный расчет дилерского дохода приведены на Рис. 1.
Столбец 2 содержит выпадающие списки, что удобно при назначении комиссии для разного вида товаров.
Столбцы 4, 6, 7, 8, 9, 13, 14 и 15 выделены цветом – они участвуют в расчете дилерского дохода и являются входными данными для пользовательской формулы 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, а также аналитикам баз данных.