Phasing Problem

[BAMSteve]

Good Afternoon all, hopefully you may be able to help

I am trying to phase daily sales trends in particular months and days

For example in March i have 875 orders, there are 31 days in the month giving me a average of 62 orders a day. the bit i want to phase is that certain days of the week perform better. when i look at a week i see sales typically split as follows:

Sunday 16% of sales
Monday 16% Of Sales
Tuesday 11% of Sales
Wednesday 11% of Sales
Thursday 12% of Sales
Friday 19% of Sales
Saturday 15% of Sales

what formula could i add that would take the average daily figure for the month and then associate a uplift or decrease depending on what day of the week it is but still total 875 for the full month?

Thank you in advance for any assistance you may be able to give.

best Regards

Steve

 

[Gerald Higgins]

Hi, welcome to the board.

I think this is really a maths question, not an Excel question.
In the sense that there are lots of ways to do this in Excel, but first you need to decide how exactly you want the maths for this to work.

By the way, 875 orders in March, spread over 31 days, is, I think, 28.226... orders per day, not 62.

Anyway, here's a suggestion -
If sales were totally flat across the week, each day would make up 1/7th of the total, or 14.2857%.

So, what you COULD do, is to calculate Sunday's sales, take daily average of 62 or 28.226 or whatever it really is, multiply by 16, and divide by 14.2857.
And so on for the other days.

 

[NARAYANK991]

Hi ,

If you want a detailed Excel setup , try out the following :

1. Place the total monthly sales figure , 875 , in some cell ; let us say it is in A1.

2. In some other area of the worksheet place the table of day-wise sales percentages ; let us say you have the days Sunday , Monday ,... in the range K3:K9 ; next to it , in L3:L9 enter the values of 16 , 16 , 11 , 11 , 12 , 19 , 15.

3. In the range A3:A33 , enter the dates 01-03-2017 (if it is March) , 02-03-2017 ,..., 31-03-2017.

4. In B3 enter the following formula , and copy down :

=$A$1*VLOOKUP(TEXT(A3,"dddd"),$K$3:$L$9,2,FALSE)/(100*SUMPRODUCT(--(TEXT($A$3:$A$33,"ddd")=TEXT(A3,"ddd"))))

What this does is multiply the total sales figure for the month by the percentage of sales for that day of the week , and divide by the total number of that day of the week in the month.

 

[shg]

	A​	B​	C​	D​
1​	Weekday​	Rel %​	Count​
2​	Sun	16%​	4​	C2: =SUMPRODUCT(--(TEXT(WEEKDAY($A$13:$A$43), "ddd") = A2))
3​	Mon	16%​	4​
4​	Tue	11%​	4​
5​	Wed	11%​	5​
6​	Thu	12%​	5​
7​	Fri	19%​	5​
8​	Sat	15%​	4​
9​			4.42​	C9: =SUMPRODUCT(B2:B8, C2:C8)
10​
11​		Sales​
12​	Day​	875​		B12: Input
13​	Wed 01 Mar​	21.8​		B13: =VLOOKUP(TEXT(WEEKDAY(A13), "ddd"), $A$2:$B$8, 2, FALSE) * B$12/C$9
14​	Thu 02 Mar​	23.8​
15​	Fri 03 Mar​	37.6​
16​	Sat 04 Mar​	29.7​
17​	Sun 05 Mar​	31.7​
18​	Mon 06 Mar​	31.7​
19​	Tue 07 Mar​	21.8​
20​	Wed 08 Mar​	21.8​
21​	Thu 09 Mar​	23.8​
22​	Fri 10 Mar​	37.6​
23​	Sat 11 Mar​	29.7​
24​	Sun 12 Mar​	31.7​
25​	Mon 13 Mar​	31.7​
26​	Tue 14 Mar​	21.8​
27​	Wed 15 Mar​	21.8​
28​	Thu 16 Mar​	23.8​
29​	Fri 17 Mar​	37.6​
30​	Sat 18 Mar​	29.7​
31​	Sun 19 Mar​	31.7​
32​	Mon 20 Mar​	31.7​
33​	Tue 21 Mar​	21.8​
34​	Wed 22 Mar​	21.8​
35​	Thu 23 Mar​	23.8​
36​	Fri 24 Mar​	37.6​
37​	Sat 25 Mar​	29.7​
38​	Sun 26 Mar​	31.7​
39​	Mon 27 Mar​	31.7​
40​	Tue 28 Mar​	21.8​
41​	Wed 29 Mar​	21.8​
42​	Thu 30 Mar​	23.8​
43​	Fri 31 Mar​	37.6​
44​	Check​	875.0​		B44: =SUM(B13:B43)

Note that the ratio of sales on, for example, any Sunday to any Tuesday, is 16/11