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!

 

[tmakonco]

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.

<br />

Book1
	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
Sheet1

<br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=B3/DAY(<font color="Blue">EOMONTH(<font color="Red">DATEVALUE(<font color="Green">B2&" 1, "&$B$1</font>),0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B8</th><td style="text-align:left">=MIN(<font color="Blue">7,EOMONTH(<font color="Red">B7,0</font>)-B7+1</font>)*INDEX(<font color="Blue">$4:$4,MATCH(<font color="Red">TEXT(<font color="Green">B7,"mmm"</font>),$2:$2,0</font>)</font>) + MAX(<font color="Blue">0,B7-EOMONTH(<font color="Red">B7,0</font>)+6</font>)*INDEX(<font color="Blue">$4:$4,MATCH(<font color="Red">TEXT(<font color="Green">B7,"mmm"</font>),$2:$2,0</font>)+1</font>)</td></tr></tbody></table></td></tr></table>

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

I'm very late here but I have the same problem trying to split a monthly forecast.
I'm not sure I understand the formula well and how to place it in my spreadsheet. In fact I'm not sure i see the formula at all, all i see is the formula string and not the example which from your response it sounded like you included AlphaFrog. I would really appreciate your help.

 

[AlphaFrog]

I updated the BB Code for the tables. They should be visible in the original posts now.

 

[sucett]

Similar question - but if instead of a Monthly Total I had a Monthly Average that I needed to split between weeks.