Auto Populate an Annual Budget

[WayneR]

Using Excel 2010

First and most importantly, I need to auto populate a spreadsheet representing a full year / 365 days from a data set.

Secondly, if a specific data item changes, whether the amount changes or the payment day changes, I need it to automatically update the spreadsheet through the entire year.

Does the following require a massive 'If' formula that needs to be applied to every destinaton cell?

Data Set:

Days of Month Payment Schedule......Vendor A.....Vendor B.....Vendor C
..................1................................ $100........... $50........... $10
..................2................................ $150........... $75........... $20
..................3.................................$ 0..............$20............$16
..................4.................................$ 0..............$ 0.............$ 0 (No payments on the 4th)
..................5.................................$ 30............$60.............$10
.................etc

The calendar below will show all 365 days while the payment schedule above may only have 15 days where there are required payment entries, so there is no need to list all 31 days in the data set, (you could eliminate the 4th in the example above), unless thats the best way to set it up.

Result Needed:
Need to auto populate the following spreadsheet from the data set above (to populate entire year), i.e., if the day is the 1st, the auto entry for Vendor A is $100, Vendor B $50 and for Vendor C $10 for every month, per the data set above.

365 Day Calendar of Scheduled Payments

Date........Vendor A......Vendor B......Vendor C
1/1/14
1/2/14
1/3/14
etc

2/1/14
2/2/14
2/3/14
ect

3/1/14
3/2/14
3/3/14
etc

If you could provide an appropriate formula, I would appreciate it. Thank you.

 

[konew1]

You need to post a bit more info. Does the "Data Set - Days of Month payment schedule" list the days 1 to 31 and the amounts apply to every month? If that is correct then does it follow that on the 1st day of every month the payments are 100+50+10 = 160? What happens to payments on 31st when you are calculating for a month with 30 days (or February)? Do you want payments due on a particular day to show on that day even if it is a weekend date in some months or a holiday date in your jurisdiction?

 

[WayneR]

You need to post a bit more info. Does the "Data Set - Days of Month payment schedule" list the days 1 to 31 and the amounts apply to every month? If that is correct then does it follow that on the 1st day of every month the payments are 100+50+10 = 160? What happens to payments on 31st when you are calculating for a month with 30 days (or February)? Do you want payments due on a particular day to show on that day even if it is a weekend date in some months or a holiday date in your jurisdiction?

Excellent question.

The data set does not list all 31 days in a month, as some days do not require any payments. If it is easier to list all 31 days in the datat set, even though half of them will have $0 for paymetns, we can do it that way if you think its easier.

Yes, payments are listed on the same date for every month. If payments are due on the first of every month, they are listed on the 1st, regardless of the day of the week, or holidays.

Payments are tied to specific dates but, if a payment is due on the 31st, but the month only has 28 or 30 days, then the payment has to defualt to the last day of the month. Example: For payments typically due on the 31st....the result would have the payment due on Jan 31st, Feb 28th, March 31st, April 30th etc.

Great clarifiying questions! Thanks.

 

[konew1]

Hi
You could try this. Maintain your data set list of payments and the new total will come across to the "YearList" page. Just note: If midway through the 2014 year you change the payments due to some suppliers then the "results" in the yearList will change for all months, including the historic months, not just for the future.

I changed your data to put in payments due on 28-31 each month.
I suggest you list all possible 31 days so that it is easy to maintain if a new payment is scheduled.

Copy the formula from B2 across all of the supplier columns and down all of the date rows.

Excel Workbook
	A	B	C	D
1	Days of Month Payment Schedule
2
3		Vendor A	Vendor B	Vendor C
4	1	100	50	10
5	2	150	75	20
6	3	0	20	18
7	4	0	0	0
8	5	30	60	10
9	29	1	4	7
10	30	2	5	8
11	31	3	6	9
DataSet

Excel Workbook
	A	B	C	D
1	Date	Vendor A	Vendor B	Vendor C
2	1/01/2014	100	50	10
3	2/01/2014	150	75	20
4	3/01/2014	0	20	18
5		0	0	0
6		0	0	0
7	1/02/2014	100	50	10
8	2/02/2014	150	75	20
9	3/02/2014	0	20	18
10	28/02/2014	6	15	24
11		0	0	0
12	1/03/2014	100	50	10
13	2/03/2014	150	75	20
14	3/03/2014	0	20	18
YearList

 

[WayneR]

Thanks. I'll give this a try later this evening and will let you know how it goes.

Wayne

Hi
You could try this. Maintain your data set list of payments and the new total will come across to the "YearList" page. Just note: If midway through the 2014 year you change the payments due to some suppliers then the "results" in the yearList will change for all months, including the historic months, not just for the future.

I changed your data to put in payments due on 28-31 each month.
I suggest you list all possible 31 days so that it is easy to maintain if a new payment is scheduled.

Copy the formula from B2 across all of the supplier columns and down all of the date rows.

DataSet

*	A	B	C	D
1	Days of Month Payment Schedule	*	*	*
2	*	*	*	*
3	*	Vendor A	Vendor B	Vendor C
4	1	100	50	10
5	2	150	75	20
6	3	0	20	18
7	4	0	0	0
8	5	30	60	10
9	29	1	4	7
10	30	2	5	8
11	31	3	6	9

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4


YearList

*	A	B	C	D
1	Date	Vendor A	Vendor B	Vendor C
2	1/01/2014	100	50	10
3	2/01/2014	150	75	20
4	3/01/2014	0	20	18
5	*	0	0	0
6	*	0	0	0
7	1/02/2014	100	50	10
8	2/02/2014	150	75	20
9	3/02/2014	0	20	18
10	28/02/2014	6	15	24
11	*	0	0	0
12	1/03/2014	100	50	10
13	2/03/2014	150	75	20
14	3/03/2014	0	20	18

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 122px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula B2 =SUMIFS(DataSet!B$4:B$11,DataSet!$A$4:$A$11,DAY(YearList!$A2))+(DAY($A2)+1<>DAY($A2+1))*SUMIFS(DataSet!B$4:B$11,DataSet!$A$4:$A$11,">+"&DAY(YearList!$A2)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

[WayneR]

I'm definitely doing something wrong.

I can't seem to get the formula to reference correctly. Here's how I laid it out.

I'd like the data set (first set of static monthly budget items) to fill the Year budget (specific dates 1/1/14, 1/2/14 etc.)

Days of Month
	V A	VB	V C
1	10	11	12
2
3
4
5		52
6
7
8	80	81	82
9
10
11
12
13
14
15
16
17
18
19
20	200	201	202
21
22
23
24
25
26
27
28
29	290	291	292
30
31	310	311	312
date	va	vb	vc
1/1/2014	0	0	0
1/2/2014	0	0	0
1/3/2014	0	0	0
1/4/2014	0	0	0
1/5/2014	0	0	0
1/6/2014	0	0	0
1/7/2014	0	0	0
1/8/2014	0	0	0
1/9/2014	0	0	0
1/10/2014	0	0	0
1/11/2014	0	0	0
1/12/2014	0	0	0
1/13/2014	0	0	0
1/14/2014	0	0	0
1/15/2014	0	0	0
1/16/2014	0	0	0

<COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

 

[konew1]

Are all the zeros in the columns headed VA VB VC all the result of formula? If so, then post the formula. What else have you tried? Did you try the formula I posted previously?

 

[WayneR]

_________________________

I tried to paste your formula into the first empty cell in the YearList, after changing the formula references to reflect its location at B15 but definitely did something wrong.

Also, whenever I try to paste the formula, the system opens my document window and wants me to ...'Update Values: Data Set'

Can you adjust the formula to fit the following layout? Should I paste the adjusted formula into the B15 cell, copy across and then copy the row down?


Your Origianl Formula:
=SUMIFS(DataSet!B$4:B$11,DataSet!$A$4:$A$11,DAY(YearList!$A2))+(DAY($A2)+1<>DAY($A2+1))*SUMIFS(DataSet!B$4:B$11,DataSet!$A$4:$A$11,">+"&DAY(YearList!$A2))
My Adjusted Formula based on the spreasheet below:
=SUMIFS(DataSet!B$4:B$11,DataSet!$A$4:$A$11,DAY(YearList!$A15))+(DAY($A15)+1<>DAY($A15+1))*SUMIFS(DataSet!B$4:B$11,DataSet!$A$4:$A$11,">+"&DAY(YearList!$A15))

<o>When I change or add an entry on the Data Set, it should automatically update the YearList with an entry for that vendor, on the same calendar day for every month.

A	B	C	D		1<o></o>​
Days of Month Payment Schedule		Data Set<o></o>			2<o></o>​
	Vendor A<o></o>	Vendor B<o></o>	Vendor C<o></o>	<o> </o>	3<o></o>​
1	100	150	175		4<o></o>​
2					5<o></o>​
3					6<o></o>​
4	200	250	275		7<o></o>​
5					8<o></o>​
29					9<o></o>​
30					10<o></o>​
31	300	325	375		11<o></o>​
					12<o></o>​
		Year List<o></o>			13<o></o>​
Date	Vendor A<o></o>	Vendor B<o></o>	Vendor C<o></o>	<o></o>	14<o></o>​
1/1/2014					15<o></o>​
1/2/2014					16<o></o>​
1/3/2014					17<o></o>​
1/4/2014					18<o></o>​
1/5/2014					19<o></o>​
1/6/2014					20<o></o>​
1/7/2014					21<o></o>​
1/8/2014					22<o></o>​
1/9/2014					23<o></o>​
1/10/2014					24<o></o>​
1/11/2014					25<o></o>​
1/12/2014					26<o></o>​
1/13/2014					27<o></o>​
1/14/2014					28<o></o>​
1/15/2014					29<o></o>​
1/16/2014					30<o></o>​
1/17/2014					31<o></o>​
1/18/2014					32<o></o>​
1/19/2014					33<o></o>​
1/20/2014					34<o></o>​
1/21/2014					35<o></o>​
1/22/2014					36<o></o>​
1/23/2014					37<o></o>​
1/24/2014					38<o></o>​
1/25/2014					39<o></o>​
1/26/2014					40<o></o>​
1/27/2014					41<o></o>​
1/28/2014					42<o></o>​
1/29/2014					43<o></o>​
1/30/2014					44<o></o>​
1/31/2014					45<o></o>​

<TBODY>
</TBODY>

</o>

 

[konew1]

I set up 2 different sheets in my excel file, named DataSet! and YearList! Your postings make it look like you only use one sheet. Please confirm your layout.

Just picked up typo on formula
=SUMIFS(DataSet!B$4:B$11,DataSet!$A$4:$A$11,DAY(YearList!$A15))+(DAY($A15)+1<>DAY($A15+1))*SUMIFS(DataSet!B$4:B$11,DataSet!$A$4:$A$11,">="&DAY(YearList!$A15))

 

[WayneR]

I set up 2 different sheets in my excel file, named DataSet! and YearList! Your postings make it look like you only use one sheet. Please confirm your layout.

Just picked up typo on formula
=SUMIFS(DataSet!B$4:B$11,DataSet!$A$4:$A$11,DAY(YearList!$A15))+(DAY($A15)+1<>DAY($A15+1))*SUMIFS(DataSet!B$4:B$11,DataSet!$A$4:$A$11,">="&DAY(YearList!$A15))

_____________

Yes, I am using only one spreadsheet. The data set is listed above the calendar.

Can it be done that way or do I need to set it up as two different spreadsheets?