Hello everyone. Could you please help me? I need to do some sales analysis
and i'm stuk.
I've got a dataset that contains next information:
Sales_department; sales_manager; Client; transaction ID; Product (SKU),
Cost, Gross income, Profitability
Ineed to perfom an ABC analysis (by the Cost of sold products to this
client) to find the A clients of each manager, i'm doing it this way:
managers_ABC <- svod %>%
group_by(Sales_department, sales_manager, Client) %>%
summarise("Clients_cost" = sum(Cost,na.rm = TRUE)) %>%
arrange(sales_manager, desc(Clients_cost)) %>%
group_by(sales_manager) %>%
mutate("total_sales_of_a_manager" = sum(Clients_cost),
"Accumulated_sales" = cumsum(`Clients_cost`),
"Accumulated_sales_share"
Accumulated_sales/total_sales_of_a_manager)
%>%
ungroup() %>%
mutate("managers_ABC = if_else(Accumulated_sales_share < 0.8,
"A",
if_else(Accumulated_sales_share <
0.95, "B","C")))
So, here we come to the main problem: i need to add 3 more columnes with
the SKU statuses:
if the the SKU is in top 20% of clients sales by Cost, Gross_income and
Profitability, than it is a "cGSKU" (clients gold SKU)
if the SKU is not gold, but occurs in 50% of transaction IDs with cGSKU,
than it is a "cMSKU" (clients must have SKU).
So i dont know how to realize this. I think it must be some cind a cycle.
Could you please help me to write one?
And is there any more simple method perfom an ABC analysis by any criterion
(for example ABC by sales_manager or sales_department)
thank you!
[[alternative HTML version deleted]]