Amalgamating two formulas to achieve the goal

[davids4500]

I have a budget planner and in the spreadsheet in cell D17 I have this formula:- (=IF(MONTH(C3)=12,'Sales&ExpensesInfo'!M4,IF(MONTH(C3)=1,'Sales&ExpensesInfo'!B3,IF(MONTH(C3)=2,'Sales&ExpensesInfo'!C3,IF(MONTH(C3)=3,'Sales&ExpensesInfo'!D3,IF(MONTH(C3)=4,'Sales&ExpensesInfo'!E3,IF(MONTH(C3)=5,'Sales&ExpensesInfo'!F3,IF(MONTH(C3)=6,'Sales&ExpensesInfo'!G3,IF(MONTH(C3)=7,'Sales&ExpensesInfo'!H3,IF(MONTH(C3)=8,'Sales&ExpensesInfo'!I3,IF(MONTH(C3)=9,'Sales&ExpensesInfo'!J3,IF(MONTH(C3)=10,'Sales&ExpensesInfo'!K3,IF(MONTH(C3)=11,'Sales&ExpensesInfo'!L3))))))))))))))

The budget Planner has 12 parts to it, one for each month.
As everything is based on the current month which is always at the top and first, then the above formula makes sure that the corrct sales data in the correct place.
However, i need to be able to manually change the sales figures(without touching the formula) due to circumstances. This is by placing a proposed figure in E17. I used to use this formula in D17(=IF(AND(E17 = 0,'Sales&ExpensesInfo'!L4>1),'Sales&ExpensesInfo'!L4,IF(AND(E17 > 1,'Sales&ExpensesInfo'!L4>1),E17)), when I entered the sales figures indivdually each month.
I could enter a new sales target in E17 and this would alter the figure in D17.
How do i incorporate this formula or this process into the top formula (=IF(AND(E17 = 0,'Sales&ExpensesInfo'!L4>1),'Sales&ExpensesInfo'!L4,IF(AND(E17 > 1,'Sales&ExpensesInfo'!L4>1),E17))
Can anyone help please. I hope so.
Much appreciated
David

 

[StephenCrump]

You can simplify the D17 formula (let's call it YourFormula) to:

=IF(MONTH(C3)=12,'Sales&ExpensesInfo'!M4,INDEX('Sales&ExpensesInfo'!C3:L3,MONTH(C3)-1))

I'm not clear on your second point, but perhaps something along these lines?

=IF(AND(E17<>0,'Sales&ExpensesInfo'!L4<>0),E17,YourFormula)

 

[davids4500]

Dear Stephen.
Thank you
i am not sure if I can change my formula, but I am sure there is a better way of writing it.
My excel spreadsheet as a budget, has Dec as the top part of the template and then January, Feb, march descending for 12 months. It has Dec at the top becuase it is the current month. net month January will be the top and then Febraury with Decmeber at the bottom and everything fills in automatically from excel sql into our database. SO each level needs to have all the information so that the correct information is sources and placed in the correct fields.
I hope that help you undertsand what i am trying to achieve.
Thank you
David

 

[kvsrinivasamurthy]

Can this be changed
=IF(MONTH(C3)=12,'Sales&ExpensesInfo'!M4,
to
=IF(MONTH(C3)=12,'Sales&ExpensesInfo'!M3,
by changing in sheet "Sales&ExpensesInfo"

 

[StephenCrump]

I hope that help you undertsand what i am trying to achieve.

Based on Post #1, I assumed your formula was working, and you just needed help with the manual override. But now I am not even clear what the question is.

Unfortunately, posting a picture doesn't help at all. Are you able to use XL2BB to provide some sample data to illustrate?

Your formula doesn't seem to relate to the layout posted. Is it working? What exactly do you need help with?

 

[davids4500]

Dear Stephen

I have attached the spreadsheet pertaining to the two areas where i need help with the 2 formulas mentioned at the beginning of the message board.

The 12-month cash flow forecaster always has the current month at the top and is based on the date in B4. So, at present it is December, next month it will be January, February etc with December on the bottom.

The formula works but I need to add the extra part of the formula so i can adjust the sales forecast manually without touching the formula.

The previous December sales formula is entered in C8(all Good). The projected sales formula for December is entered in D9 and I need to amalgamate the 2 formulas for the correct outcome. E9 is where I could write a suggested new sales figure and it would adjust the figure in D9.
I have put the numbers 1, 2 in the F column to show the 2 formulas I am working on with MrExcel

I hope that is clearer.

David

MonthlyOverviewFiguresforC&S.xlsx
	A	B	C	D	E	F
1		CASH FLOW PROJECTION
2		Date OF Update	Saturday, 21 December 2024
3		Current Month	December-2024
4		Dec-2024	December-2024
5		Income		Amount
6
7		SALES
8		Sales for Dec 2023	$52,954.19			1
9		Projected Sales for Dec 2024		$44,128.49		2
10		Actual Sales To date this month		$93,054.16
17		Sales to Achieve		-$48,925.67
18		% of Sales as Proforma	30%	-$14,677.70
19		Total Cash Outcome For December
44		CASH OUTCOME
45
46		Next Month	January-2025
57		Income
58		SALES
59		Sales for Jan 2024	$83,059.41			1
60		Projected Sales for Jan 2025		$44,128.49	$0.00	2
61		Actual Sales		$0.00
62		Sales to achieve		$44,128.49
63		% of Sales As Proforma	50%	$22,064.25
64
65		Next Month	February-2025
66		Income
67
68		SALES
69		Sales for Feb 2024	$65,499.90			1
70		Projected Sales for Feb 2025		$74,753.47	$50.00	2
71		Actual Sales		$0.00
72		Sales to achieve		$74,753.47
73		% of Sales as Proforma	40%	$29,901.39
74		Total Income/Cash December
75
98		Total of All Stock Purchasing		$0.00
99		CASH OUTCOME		-$42,685.68
100
101		Next Month	March-2025
102		Income
103		SALES
104		Sales for Mar 2024	$39,808.95			1
105		Projected Sales for Mar 2025		$74,753.47	$0.00	2
106		Actual Sales		$0.00
107		Sales to achieve		$74,753.47
108		% Of Sales As proforma	50%	$37,376.73
109		Total Income For Mar 2025		#REF!
110
111		Expenses
112		Expenses Already Posted for Mar 2025		$0.00	(P&L report)
113		Projected Expenses for Mar 2025		$37,709.27	(P&L report)
114		Outstanding		$37,709.27
115		Sub total other creditors		-$13,671.57
116		Superannuation Payment Quarterly	(Paid April,July,Oct,Jan)
117		Total Expenses		#REF!
118		Cash On Hand Sub total		#REF!
119
120		Next Month	April-2025
121		Income
122		Sales for Apr 2024	$60,145.84			1
123		Projected Sales for Apr 2025		$58,949.91	$0.00	2
124		Actual Sales		$0.00
125		Sales to achieve		$58,949.91
126		% Of Sales As proforma	20%	$11,789.98
127		Total Income/Cash August
BUdget

Cell Formulas
Range	Formula
C2	C2	=TODAY()
C3	C3	=B4
B4	B4	=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
C4	C4	=B4
C8	C8	=IF(MONTH(C3)=11,'Sales&ExpensesInfo'!L5,IF(MONTH(C3)=12,'Sales&ExpensesInfo'!M5,IF(MONTH(C3)=1,'Sales&ExpensesInfo'!B4,IF(MONTH(C3)=2,'Sales&ExpensesInfo'!C4,IF(MONTH(C3)=3,'Sales&ExpensesInfo'!D4,IF(MONTH(C3)=4,'Sales&ExpensesInfo'!E4,IF(MONTH(C3)=5,'Sales&ExpensesInfo'!F4,IF(MONTH(C3)=6,'Sales&ExpensesInfo'!G4,IF(MONTH(C3)=7,'Sales&ExpensesInfo'!H4,IF(MONTH(C3)=8,'Sales&ExpensesInfo'!I4,IF(MONTH(C3)=9,'Sales&ExpensesInfo'!J4,IF(MONTH(C3)=10,'Sales&ExpensesInfo'!K4,IF(MONTH(C3)=11,'Sales&ExpensesInfo'!L4,IF(MONTH(C3)=12,'Sales&ExpensesInfo'!M4))))))))))))))
B8	B8	=CalculationsInfo!J18&" "&TEXT(EDATE(B4,-12),"mmm yyyy")
B9	B9	=CalculationsInfo!J17&" "&TEXT(EDATE(B4, 0),"mmm yyyy")
D9	D9	=IF(AND(E9<>0,'Sales&ExpensesInfo'!L4<>0),E9,IF(MONTH(C3)=12,'Sales&ExpensesInfo'!M4,INDEX('Sales&ExpensesInfo'!C3:L3,MONTH(C3)-1)))
D10	D10	=CalculationsInfo!H18
D17	D17	=SUM(D9-D10)
D18,D126,D108,D73,D63	D18	=SUM(D17*C18)
B19	B19	=CONCATENATE("Total Cash Outcome For ", TEXT(B4, "mmmm"))
C46	C46	=DATE(YEAR(B4),MONTH(B4)+1,DAY(B4))
C59	C59	=IF(MONTH(C46)=11,'Sales&ExpensesInfo'!L5,IF(MONTH(C46)=12,'Sales&ExpensesInfo'!M5,IF(MONTH(C46)=1,'Sales&ExpensesInfo'!B4,IF(MONTH(C46)=2,'Sales&ExpensesInfo'!C4,IF(MONTH(C46)=3,'Sales&ExpensesInfo'!D4,IF(MONTH(C46)=4,'Sales&ExpensesInfo'!E4,IF(MONTH(C46)=5,'Sales&ExpensesInfo'!F4,IF(MONTH(C46)=6,'Sales&ExpensesInfo'!G4,IF(MONTH(C46)=7,'Sales&ExpensesInfo'!H4,IF(MONTH(C46)=8,'Sales&ExpensesInfo'!I4,IF(MONTH(C46)=9,'Sales&ExpensesInfo'!J4,IF(MONTH(C46)=10,'Sales&ExpensesInfo'!K4,IF(MONTH(C46)=11,'Sales&ExpensesInfo'!L4,IF(MONTH(C46)=12,'Sales&ExpensesInfo'!M4))))))))))))))
B59	B59	=CalculationsInfo!$J$18&" "&TEXT(EDATE(B4,-11),"mmm yyyy")
B60	B60	=CalculationsInfo!J17&" "&TEXT(EDATE(B4, 1),"mmm yyyy")
D60	D60	=IF(AND(E60<>0,'Sales&ExpensesInfo'!L4<>0),E60,IF(MONTH(C3)=12,'Sales&ExpensesInfo'!M4,INDEX('Sales&ExpensesInfo'!C3:L3,MONTH(C3)-1)))
D62,D125,D107,D72	D62	=SUM(D60-D61)
C65	C65	=DATE(YEAR(B4),MONTH(B4)+2,DAY(B4))
C69	C69	=IF(MONTH(C65)=11,'Sales&ExpensesInfo'!L5,IF(MONTH(C65)=12,'Sales&ExpensesInfo'!M5,IF(MONTH(C65)=1,'Sales&ExpensesInfo'!B4,IF(MONTH(C65)=2,'Sales&ExpensesInfo'!C4,IF(MONTH(C65)=3,'Sales&ExpensesInfo'!D4,IF(MONTH(C65)=4,'Sales&ExpensesInfo'!E4,IF(MONTH(C65)=5,'Sales&ExpensesInfo'!F4,IF(MONTH(C65)=6,'Sales&ExpensesInfo'!G4,IF(MONTH(C65)=7,'Sales&ExpensesInfo'!H4,IF(MONTH(C65)=8,'Sales&ExpensesInfo'!I4,IF(MONTH(C65)=9,'Sales&ExpensesInfo'!J4,IF(MONTH(C65)=10,'Sales&ExpensesInfo'!K4,IF(MONTH(C65)=11,'Sales&ExpensesInfo'!L4,IF(MONTH(C65)=12,'Sales&ExpensesInfo'!M4))))))))))))))
B69	B69	=CalculationsInfo!$J$18&" "&TEXT(EDATE(B4,-10),"mmm yyyy")
B70	B70	=CalculationsInfo!$J$17&" "&TEXT(EDATE(B4, 2),"mmm yyyy")
D70	D70	='Sales&ExpensesInfo'!B3
D98	D98	=SUM(D89+D90+D94+D95+D96+D97)
D99	D99	=SUM(D86+D98)
C101	C101	=DATE(YEAR(B4),MONTH(B4)+3,DAY(B4))
C104	C104	=IF(MONTH(C101)=11,'Sales&ExpensesInfo'!L5,IF(MONTH(C101)=12,'Sales&ExpensesInfo'!M5,IF(MONTH(C101)=1,'Sales&ExpensesInfo'!B4,IF(MONTH(C101)=2,'Sales&ExpensesInfo'!C4,IF(MONTH(C101)=3,'Sales&ExpensesInfo'!D4,IF(MONTH(C101)=4,'Sales&ExpensesInfo'!E4,IF(MONTH(C101)=5,'Sales&ExpensesInfo'!F4,IF(MONTH(C101)=6,'Sales&ExpensesInfo'!G4,IF(MONTH(C101)=7,'Sales&ExpensesInfo'!H4,IF(MONTH(C101)=8,'Sales&ExpensesInfo'!I4,IF(MONTH(C101)=9,'Sales&ExpensesInfo'!J4,IF(MONTH(C101)=10,'Sales&ExpensesInfo'!K4,IF(MONTH(C101)=11,'Sales&ExpensesInfo'!L4,IF(MONTH(C101)=12,'Sales&ExpensesInfo'!M4))))))))))))))
B104	B104	=CalculationsInfo!$J$18&" "&TEXT(EDATE(B4,-9),"mmm yyyy")
B105	B105	=CalculationsInfo!$J$17&" "&TEXT(EDATE(B4, 3),"mmm yyyy")
D105	D105	=IF(AND(E105 = 0,'Sales&ExpensesInfo'!B3>1),'Sales&ExpensesInfo'!B3,IF(AND(E105 > 1,'Sales&ExpensesInfo'!B3 >1),E105))
B109	B109	=CalculationsInfo!$J$21&" "&TEXT(EDATE($C$101, 0),"mmm yyyy")
D109	D109	=SUM(#REF!+#REF!+D108)
B112	B112	=CalculationsInfo!$J$20&" "&TEXT(EDATE(C101,0 ),"mmm yyyy")
B113	B113	=CalculationsInfo!$J$19&" "&TEXT(EDATE(C101,0 ),"mmm yyyy")
D113	D113	=IF(MONTH(C101)=12,'Sales&ExpensesInfo'!M12,IF(MONTH(C101)=1,'Sales&ExpensesInfo'!B11,IF(MONTH(C101)=2,'Sales&ExpensesInfo'!C11,IF(MONTH(C101)=3,'Sales&ExpensesInfo'!D11,IF(MONTH(C101)=4,'Sales&ExpensesInfo'!E11,IF(MONTH(C101)=5,'Sales&ExpensesInfo'!F11,IF(MONTH(C101)=6,'Sales&ExpensesInfo'!G11,IF(MONTH(C101)=7,'Sales&ExpensesInfo'!H11,IF(MONTH(C101)=8,'Sales&ExpensesInfo'!I11,IF(MONTH(C101)=9,'Sales&ExpensesInfo'!J11,IF(MONTH(C101)=10,'Sales&ExpensesInfo'!K11,IF(MONTH(C101)=11,'Sales&ExpensesInfo'!L11,IF(MONTH(C101)=12,'Sales&ExpensesInfo'!M11)))))))))))))
D114	D114	=D113-D112
D115	D115	=CalculationsInfo!H97
D116	D116	=IF(MONTH(C101)=1,Superannuation[SUM],IF(MONTH(C101)=4,Superannuation[SUM],IF(MONTH(C101)=7,Superannuation[SUM],IF(MONTH(C101)=10,Superannuation[SUM],""))))
D117	D117	=SUM(D114-D115-#REF!-#REF!-#REF!)
D118	D118	=SUM(D109-D117)
C120	C120	=DATE(YEAR(B4),MONTH(B4)+4,DAY(B4))
C122	C122	=IF(MONTH(C120)=11,'Sales&ExpensesInfo'!L5,IF(MONTH(C120)=12,'Sales&ExpensesInfo'!M5,IF(MONTH(C120)=1,'Sales&ExpensesInfo'!B4,IF(MONTH(C120)=2,'Sales&ExpensesInfo'!C4,IF(MONTH(C120)=3,'Sales&ExpensesInfo'!D4,IF(MONTH(C120)=4,'Sales&ExpensesInfo'!E4,IF(MONTH(C120)=5,'Sales&ExpensesInfo'!F4,IF(MONTH(C120)=6,'Sales&ExpensesInfo'!G4,IF(MONTH(C120)=7,'Sales&ExpensesInfo'!H4,IF(MONTH(C120)=8,'Sales&ExpensesInfo'!I4,IF(MONTH(C120)=9,'Sales&ExpensesInfo'!J4,IF(MONTH(C120)=10,'Sales&ExpensesInfo'!K4,IF(MONTH(C120)=11,'Sales&ExpensesInfo'!L4,IF(MONTH(C120)=12,'Sales&ExpensesInfo'!M4))))))))))))))
B122	B122	=CalculationsInfo!$J$18&" "&TEXT(EDATE(B4, -8),"mmm yyyy")
B123	B123	=CalculationsInfo!$J$17&" "&TEXT(EDATE(B4, 4),"mmm yyyy")
D123	D123	=IF(AND(E123 = 0,'Sales&ExpensesInfo'!C3>1),'Sales&ExpensesInfo'!C3,IF(AND(E123 > 1,'Sales&ExpensesInfo'!C3 >1),E123))

 

[StephenCrump]

Thanks, that looks helpful.

But I'm hoping someone else can jump in and help now, as I'm away for a couple of weeks, and not shackled to a computer (yay!)

 

[davids4500]

Can this be changed
=IF(MONTH(C3)=12,'Sales&ExpensesInfo'!M4,
to
=IF(MONTH(C3)=12,'Sales&ExpensesInfo'!M3,
by changing in sheet "Sales&ExpensesInfo"

Yes it can but at some stage it will come across a different row becuase the sales data for 5 years is stored thereand each year sits on the previous...
David