D
DA
I have 10 products rows with a cost in column BW of rows 21 thru 30,
i.e., cells BW21 down to BW30.
Those 10 products have the # of units sold each month, for 22 months,
shown in rows 41 thru 50, in columns E thru Z, so column E is month
#1 ... col Z is month #22. The total range is cells E40 thru Z40.
There is a cost factor vector that has six factors in row 70, cells
G70 thru L70. These are to be used ‘for all time periods’ and for all
products.
Any time there is a sale of a product in a certain month, I want to
enter six months of cost associated with producing the item, most of
it occurring in months before the month of the sale(s). The factor in
cell G6 is the "Multiplier" that I want to assign in a time period
that is 4 months before the sale, H6 is 3 months before the sale … L6
is one month after the sale. The Multiplier for each product will be
applied to its value in column BW times the number of unit sales in
the month in question as listed in rows 41 thru 50.
I would place these resultant cost values in row 51 thru 60, also in
columns E thru Z.
As an example:
If the cost vector were 10%, 20%, 10%, 20%, 30%, 10%
and I sold 2 units of product #1 (cost of $100 always) in month 7
(cell K41), then…
In row 51, in month #3 (column G, so cell G51), I would want to place
a cost of 2 * 10% * $100 = $20; then $40 in month 4 in cell H51, then
$20 in month 5 in cell I51, then $40 in month 6, then $60 in month 7
(the month of the sales) and $20 in month 8.
If the sale were in month 4 or earlier, some of the earliest cost
would not be counted and, if it were in month 22, the last month’s
cost (month 23) would not be counted either. That’s OK.
Can someone kindly write me an EXCEL formula that would do this?
Thanks so much!
Dean
i.e., cells BW21 down to BW30.
Those 10 products have the # of units sold each month, for 22 months,
shown in rows 41 thru 50, in columns E thru Z, so column E is month
#1 ... col Z is month #22. The total range is cells E40 thru Z40.
There is a cost factor vector that has six factors in row 70, cells
G70 thru L70. These are to be used ‘for all time periods’ and for all
products.
Any time there is a sale of a product in a certain month, I want to
enter six months of cost associated with producing the item, most of
it occurring in months before the month of the sale(s). The factor in
cell G6 is the "Multiplier" that I want to assign in a time period
that is 4 months before the sale, H6 is 3 months before the sale … L6
is one month after the sale. The Multiplier for each product will be
applied to its value in column BW times the number of unit sales in
the month in question as listed in rows 41 thru 50.
I would place these resultant cost values in row 51 thru 60, also in
columns E thru Z.
As an example:
If the cost vector were 10%, 20%, 10%, 20%, 30%, 10%
and I sold 2 units of product #1 (cost of $100 always) in month 7
(cell K41), then…
In row 51, in month #3 (column G, so cell G51), I would want to place
a cost of 2 * 10% * $100 = $20; then $40 in month 4 in cell H51, then
$20 in month 5 in cell I51, then $40 in month 6, then $60 in month 7
(the month of the sales) and $20 in month 8.
If the sale were in month 4 or earlier, some of the earliest cost
would not be counted and, if it were in month 22, the last month’s
cost (month 23) would not be counted either. That’s OK.
Can someone kindly write me an EXCEL formula that would do this?
Thanks so much!
Dean