Converting Data from Monthly to Weekly

[sweetmetrics]

I've had trouble searching for this and I am thinking this is relatively easy to figure out but I'm stuck!

Excel Version: Excel for Mac 2011
Data: 2012 Monthly Sales Goals (12 columns)

What I am trying to do: Convert my 2012 monthly sales goals to weekly sales goals for reporting. My definition of a "week" is Sunday - Saturday and has an integer value of 1 - 53.

So for example, the month of March 2012 starts out on Thursday, in the middle of Week 9. Therefore, my goal for Week 9 should be a value based on February's monthly goal and March's monthly goal. This is important because (for example's sake) that February's sales goal is $10,000 and March's sales goal is $15,000.

I am thinking the solution may lie in breaking down the monthly data to a daily level for the year (1 - 366) then summarizing it by weeks (1-53). So does anyone know a efficient way to do this? Or perhaps a different way altogether?

Thank you in advance for your help!

 

[smiler]

hi,

you say you have 12 columns, one for each month, how many rows do you have percolumn, is it a row per day for that month ?

How is your data laid out?

 

[AlphaFrog]

This creates a table where the monthly sales goal is converted to a daily sales goal for each day in a month (Sales Goal divided by the number of days in the month).

The daily sales goals are then looked up for each week. If a week spans two months (yellow cells), the days of the week that fall within each month is looked up.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year	2012
2	Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	Monthly Sales Goal	$ 10,000	$ 10,500	$ 11,000	$ 11,500	$ 12,000	$ 12,500	$ 13,000	$ 13,500	$ 14,000	$ 14,500	$ 15,000	$ 15,500
4	Daily Sales Goal	$ 322.58	$ 362.07	$ 354.84	$ 383.33	$ 387.10	$ 416.67	$ 419.35	$ 435.48	$ 466.67	$ 467.74	$ 500.00	$ 500.00
5
6		Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12
7		Jan 1, 2012	Jan 8, 2012	Jan 15, 2012	Jan 22, 2012	Jan 29, 2012	Feb 5, 2012	Feb 12, 2012	Feb 19, 2012	Feb 26, 2012	Mar 4, 2012	Mar 11, 2012	Mar 18, 2012
8	Weekly Sales Goal	$ 2,258.06	$ 2,258.06	$ 2,258.06	$ 2,258.06	$ 2,416.02	$ 2,534.48	$ 2,534.48	$ 2,534.48	$ 2,512.79	$ 2,483.87	$ 2,483.87	$ 2,483.87

Worksheet Formulas Cell Formula B4 =B3/DAY(EOMONTH(DATEVALUE(B2&" 1, "&$B$1),0)) B8 =MIN(7,EOMONTH(B7,0)-B7+1)*INDEX($4:$4,MATCH(TEXT(B7,"mmm"),$2:$2,0)) + MAX(0,B7-EOMONTH(B7,0)+6)*INDEX($4:$4,MATCH(TEXT(B7,"mmm"),$2:$2,0)+1)

In this example, the formulas in B4 and B8 are copied in the columns to the right.

 

[sweetmetrics]

AlphaFrog,

WOW! Your solution worked flawlessly. Needless to say, I would have never came up with anything so elegant and simple. Thank you so very much!

 

[sweetmetrics]

hi,

you say you have 12 columns, one for each month, how many rows do you have percolumn, is it a row per day for that month ?

How is your data laid out?

Smiler,

Thank you for offering to help. I sincerely appreciate your willingness to try to help me work through my problem.

 

[AlphaFrog]

You're welcome and thanks for the feedback.

 

[stephaniedoiron]

This creates a table where the monthly sales goal is converted to a daily sales goal for each day in a month (Sales Goal divided by the number of days in the month).

The daily sales goals are then looked up for each week. If a week spans two months (yellow cells), the days of the week that fall within each month is looked up...

A little late, but I have a similar problem that these formulas seam to be resolving. However, my forecasts continue later than just the present year. When I add the columsn for the months for 2015 after the 2014 months, the formulas don't seam to be working properly... I changed the cell reference for the 2015 months so they go to the cell that indicates the proper year (2015 instead of 2014), but there still seams to be a problem somewhere... Any suggestions?

Thanks!

 

[AlphaFrog]

The original formulas match the Month-only of a given week to the month in row 2. There was no year in row 2 so the first week for 2015 would match the first Jan it found in row 2 regardless of the year.

The solution below works for multiple month-years;

Put an actual date in row 2 for the first of each month. e.g
B2 = Jan -1-2014
C2 = Feb-1-2014...etc.
It must be the 1st of each month in row 2

You can format the dates to display with any date format you like. The display format doesn't matter.

I put the start date in B1 and just used the EDATE function in C2 and beyond to increase each date by one month.

The other formulas are similar to the originals, but have been adjusted to match both the month and year in row 2

	A	B	C	D	L	M	N	O	P
1	Year	2014				Year	2015
2	Month	Jan 2014	Feb 2014	Mar 2014	Nov 2014	Dec 2014	Jan 2015	Feb 2015	Mar 2015
3	Monthly Sales Goal	$1	$2	$3	$11	$12	$13	$14	$15
4	Daily Sales Goal	$0.03	$0.07	$0.10	$0.37	$0.39	$0.42	$0.50	$0.48
5
6		Week 1	Week 2	Week 3	Week 11	Week 12	Week 13	Week 14	Week 15
7		1-Jan-14	8-Jan-14	15-Jan-14	12-Mar-14	19-Mar-14	26-Mar-14	2-Apr-14	9-Apr-14
8	Weekly Sales Goal	$0.23	$0.23	$0.23	$0.68	$0.68	$0.71	$0.93	$0.93

Worksheet Formulas Cell Formula C2 =EDATE(B2,1) D2 =EDATE(C2,1) B4 =B3/DAY(EOMONTH(B2,0)) B8 =MIN(7,EOMONTH(B7,0)-B7+1)*INDEX($4:$4,MATCH(B7-DAY(B7)+1,$2:$2,0)) + MAX(0,B7-EOMONTH(B7,0)+6)*INDEX($4:$4,MATCH(B7-DAY(B7)+1,$2:$2,0)+1)

 

[stephaniedoiron]

The original formulas match the Month-only of a given week to the month in row 2. There was no year in row 2 so the first week for 2015 would match the first Jan it found in row 2 regardless of the year.

The solution below works for multiple month-years...

Thank you so much, it worked perfectly! You saved me a lot of time.

 

[Jackson_hollon]

I think I came too late. I have the same issue with stephaniedoiron only different is I need to use workdays in the month instead of actual days in a month?

Thanks for the help.