I have 1 excel file containing following data in 2 different sheets:
Sheet 1
Column A: Product code
Column B : Total Qty sold to customer in year 2001
Column C : Total Value paid by customer for Qty in Column C
Column D : Avg price paid by customer in 2001
Sheet 2
Column A: Product code
Column B : Total Qty sold to customer in January 2002
Column C : Total Value paid by customer for Qty in Column C
Column D : Avg price paid by customer in January 2002
Sheet 1 and 2 only contain all product codes that have been sold to that customer in the specified period.
When calculating the weighted average price the customer paid for a range of items in 2001 and January 2002, it it likely that there will be differences: selling prices of individual items could have gone up or down, qty per item could have gone up or down or the customer didn't buy an item in Jan 2002 that he bought in 2001 or he bought a new item for the first time in Jan 2002.
To understand why weighted average selling price to the customers have gone up or down, we need to identify where the difference comes from: selling price and/or qty and/or change in product range? Most likely it will be a combination of the 3 measurable indicators.
Does anybody have a clue how to get to measure the impact of selling prices, qty and range in the total calculated difference?
Rudi HOLANS
Sheet 1
Column A: Product code
Column B : Total Qty sold to customer in year 2001
Column C : Total Value paid by customer for Qty in Column C
Column D : Avg price paid by customer in 2001
Sheet 2
Column A: Product code
Column B : Total Qty sold to customer in January 2002
Column C : Total Value paid by customer for Qty in Column C
Column D : Avg price paid by customer in January 2002
Sheet 1 and 2 only contain all product codes that have been sold to that customer in the specified period.
When calculating the weighted average price the customer paid for a range of items in 2001 and January 2002, it it likely that there will be differences: selling prices of individual items could have gone up or down, qty per item could have gone up or down or the customer didn't buy an item in Jan 2002 that he bought in 2001 or he bought a new item for the first time in Jan 2002.
To understand why weighted average selling price to the customers have gone up or down, we need to identify where the difference comes from: selling price and/or qty and/or change in product range? Most likely it will be a combination of the 3 measurable indicators.
Does anybody have a clue how to get to measure the impact of selling prices, qty and range in the total calculated difference?
Rudi HOLANS
