• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Proportioning/seasonalising weekly sales to the 1H and to the company target for that given week

M

melvin

Member
Hey guys,

I am trying to come to a proportionate formula which captures the target set for a given store and match to company target for the week. In this example in line 7, target is ~$1.322M. Used last years phasing to seasonalise it.

However the challenge is I have to even seasonalise it with the week sales target by the company i.e. 14,000,000. There are approx 200 stores with a category of "Large Stores" should match to the week target i.e. $14M and the 1H target given to the respective stores.

I am able to one or the other. Either can seasonalise using target of $1.3M for the store, or I can take a proportion of sales i.e LY sales in wk 1 $ 43,983/13,800,000*14,000,000. Take it down to all the "Large Store" categories and will reconcile to the 14,000,000, but this would then not reconcile to the 1H target of $ 1.3M.


Appreciate any help with this. Melv.
 

Attachments

Its not overly clear what you are trying to achieve

Proratering This years forecast 1.322M against last years weekly totals 43,983/1,271,681. is quite a valid method

The Totals of the weeks will equal 1.322M when summed

It is unclear how you want to use the 1.4M?

because If you want 1.4/1.322 the resultant sum will not equal 1.322, it will equal 1.4

Similarly you cannot divide the weekly totals eg: 43983/1.4M because that will result in different ratios to last year as you are dividing by a larger number?
 
Hui,

Agreed, the $1.3M for a given store can be phased with LY seasonality.

However company has weekly target for i.e. 250 stores. which is $14M.
For example
If I do the above step with seasonality for a given store and repeat the process for 250 stores. Once this exercise is over if I add the sales for week 1 it will not come to $14M, it may come to $14.5M....

So the dilema is I have to match to the store target i.e $1.3M and do the exercise for 250 stores, but at the end of the day I have to ensure that all the week 1's for the stores even reconcile to $14M.

The only way I can imagine is once the seasonality per store is over, whatever is the week difference of 250 stores tot he total flush it to all the stores.

Melv
 
So the formula for D7 will be:
=(D6/$AD$6)*$AD$7*(D6/sum(all stores D6 cells for week 1))*14M

Now doing this will give you 14M for week 1, but the Total for the Store 1 won't equal 1.322 anymore

You can't have a solution that solves both directions for all stores
 
Perfect, that's exactly what I have done. But thought maybe there was a way to match both. That's Hui. Appreciate it.

Melv.
 
You must log in or register to reply here.
Back
Top