Redistribute Budget Gap Over Remaining Months

[Horsefly]

Hello, gang-

Searched and searched on the forum for this function, but I can't find what I'm after. Perhaps bad search terms? Maybe such a post doesn't exist...

Either way, what I seek is a standard formula that will help me create a "moving target" for each month in a Fiscal Year.

Here are the parameters:
1. Each month will have a Budget
2. Each month will have an Actual
3. The difference in BUD to Actual will be the Gap (can be positive or negative)
4. This Gap must be distributed equitably over remaining months to ensure business managers know how much revenue is needed to hit the target.
5. This Gap is additive, meaning that the Gap value spread over remaining months is constantly revised to reflect cumulative gaps over remaining months. (As the count of Actual values increases with each month, the spread over remaining months decreases)

Attached is an example. The row I'm solving for is Yellow. Basically, I need the sum of the Revised Forecast (See: O25) to equal the Original Forecast (See: O25), correcting for new actuals that may be higher or lower than Original Forecast. I can do it for the first month, but can't nail it down for subsequent months...

Clear as mud?

1 Forecast Gap Redistribution Example.xlsx

[shg]

Welcome to the forum.

Row\Col	B	C	D	E	F	G	H	I	J	P	Q	R
2			Q1			Q2			Q3
3			JAN	FEB	MAR	APR	MAY	JUN	JUL	TOTAL
4	Base		$ 66,952	$ 66,952	$ 66,952	$ 66,952	$ 66,952	$ 66,952	$ 66,952	$ 803,420
5	UroCuff Hardware		$ 11,000	$ 11,000	$ 11,000	$ 22,000	$ 22,000	$ 22,000	$ 11,000	$ 165,000
6	UroCuff Disposables		$ 9,000	$ 9,000	$ 18,000	$ 36,000	$ 36,000	$ 54,000	$ 63,000	$ 612,000
7	Spanner Disposables		$ 7,875	$ 11,025	$ 15,750	$ 31,500	$ 37,800	$ 47,250	$ 55,125	$ 551,250
8	Total		$ 94,827	$ 97,977	$ 111,702	$ 156,452	$ 162,752	$ 190,202	$ 196,077	$ 2,131,670
9	Gap		$ 57,311									D9 and across: =IF(D15=0, "", SUM($D8:D8) - SUM($D15:D15))
10	Adjusted		$ 37,516	$ 100,733	$ 114,845	$ 160,854	$ 167,331	$ 195,553	$ 201,594	$ 2,131,670		D10 and across: =IF(D15 <> 0, D15, D8 / ($P8 - SUM($C8:C8)) * ($P8 - SUM($C10:C10)))
11
12	UroCuff Hardware		$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
13	UroCuff Disposables		$ 25,966	$ -	$ -	$ -	$ -	$ -	$ -	$ 25,966
14	Spanner Disposables		$ 11,550	$ -	$ -	$ -	$ -	$ -	$ -	$ 11,550
15	Total		$ 37,516	$ -	$ -	$ -	$ -	$ -	$ -	$ 37,516
16
17	Florida North East		$ 18,475	$ 19,525	$ 24,100	$ 39,017	$ 41,117	$ 50,267	$ 52,225	$ 552,950
18	Florida South East		$ 34,508	$ 35,558	$ 40,133	$ 55,050	$ 57,150	$ 66,300	$ 68,258	$ 745,350
19	Florida West		$ 41,843	$ 42,893	$ 47,468	$ 62,385	$ 64,485	$ 73,635	$ 75,593	$ 833,370
20	Total		$ 94,827	$ 97,977	$ 111,702	$ 156,452	$ 162,752	$ 190,202	$ 196,077	$ 2,131,670
21	Gap		$ 57,311	$ 52,100	$ 47,364	$ 42,628	$ 37,891	$ 33,155
22	Adjusted		$ 37,516	$ 103,187	$ 116,438	$ 161,188	$ 167,488	$ 194,938	$ 201,010	$ 2,131,670
23
24	Florida North East		$ 4,175	$ -	$ -	$ -	$ -	$ -	$ -	$ 4,175
25	Florida South East		$ 15,463	$ -	$ -	$ -	$ -	$ -	$ -	$ 15,463
26	Florida West		$ 17,878	$ -	$ -	$ -	$ -	$ -	$ -	$ 17,878
27	Total		$ 37,516	$ 103,187	$ 116,438	$ 161,188	$ 167,488	$ 194,938	$ -	$ 780,755

Note the inserted column, and that columns K:O are hidden for compactness.

[Horsefly]

Thanks for the help.

I understand the logic in most of what you have provided, but can't understand the use of (inserted) column C.

What does SUM($C8:C8) represent if there are no values in this cell?

[shg]

Total annual sales prior to January, which is zero.

One column over, it's total annual sales prior to February.

[Horsefly]

Bingo. Thanks.

[shg]

You're welcome.