Balancing Budget Between Cost Types

[Jeffrey Mahoney]

This is not my typical post. I'm usually dealing with VBA code. This seems simple and then it's not.

In each Cost Type Table in Column C I have formulas that split the remainder Unit Budget up evenly for the remaining months. If the Month is in the past, it uses the Actual Costs for the Month. It is designed to forecast the Spend Per Month until the month has passed. If a manual override value is placed in a future month, it forces the monthly budget value to use that instead of the average remaining budget. The manual override value can increase or decrease the current monthly budget. But in any case, the rest of the months that don't have manual override values, balance with the unit budget. The Cost Type budget total will always be the Unit Budget unless the Actual Costs are higher. That all works perfectly

Here is my issue
I need to split my total Current Budget up between the four different Cost Types. I have been manually changing the Unit Budget depending on the actuals that come in weekly. Then I changed the Unit Budget amounts so they would be a percentage of the actuals. The problem comes when a Manual Override value is put in. How do I account for potential override values?

I realize this is a lot to look at in one setting. I need a fresh eye on this. Talking it out is imperative.

Jeff


My Current Budget

Excel 2010
	I
4	Current Budget
5	$ 500,000
Test Project

The Budget Control and Totals for all Cost Type Tables

Excel 2010
	B	C	D	E	F	G
7		Internal Labor	Contract Labor	Materials	Rental	Total
8	Unit Budget:	$ -	$ 286,915	$ 32,388	$ 180,697	$ 500,000
9	Monthly Budget Total:	$ -	$ 286,915	$ 32,388	$ 180,697	$ 500,000
10	Actuals Total:	$ -	$ 18,630	$ 2,103	$ 11,733	$ 32,466
11	Spend Plan Balance:	$ -	$ 268,285	$ 30,285	$ 168,964	$ 467,534
12	Forecast Spend:	$ -	$ 286,915	$ 32,388	$ 180,697	$ 500,000
13	% of Total Spend	0.0%	57.4%	6.5%	36.1%	100.0%
Test Project

A Cross section of Row 8 with the formulas

Excel 2010
	B	C	D	E	F	G
8	Unit Budget:	$ -	$ 286,915	$ 32,388	$ 180,697	$ 500,000
Test Project

Cell Formulas
Range	Formula
C8	=C13*Current_BUDGET
D8	=D13*Current_BUDGET
E8	=E13*Current_BUDGET
F8	=F13*Current_BUDGET
G8	=SUM(C8:F8)

Named Ranges
Name	Refers To	Cells
'Test Project'!Current_BUDGET	='Test Project'!$I$5

This is one of the Cost Type Tables. They are all formatted the same. The is the Contract Labor Table

Excel 2010
	B	C	D	E	F	G	H
31	Contract Labor
32	Month Avg: $23,910	Budget $286,915	Manual Override	Actuals (Cumulative)	Spend per Month	Spend Running Average	Forecast
33	January	$ 533		$ 533	$ 533	$ 533	$ 533
34	February	$ 27		$ 560	$ 27	$ 280	$ 27
35	March	$ 8,603		$ 9,163	$ 8,603	$ 3,054	$ 8,603
36	April	$ 22,000	$ 22,000	$ 18,630	$ 9,467	$ 6,032	$ 22,000
37	May	$ 31,969			$ -	$ -	$ 31,969
38	June	$ 31,969			$ -	$ -	$ 31,969
39	July	$ 31,969			$ -	$ -	$ 31,969
40	August	$ 31,969			$ -	$ -	$ 31,969
41	September	$ 31,969			$ -	$ -	$ 31,969
42	October	$ 31,969			$ -	$ -	$ 31,969
43	November	$ 31,969			$ -	$ -	$ 31,969
44	December	$ 31,969			$ -	$ -	$ 31,969
45	Totals:	$ 286,915	$ 22,000	$ 18,630	$ 18,630	$ 1,289	$ 286,915
Test Project

 

[AlanY]

see below

 

[AlanY]

have a look to see if this work for you

Excel 2012
	A	B	C	D	E	F	G
31	Contract Labor
32	Month	Budget	Manual Override	Actuals	Spend	Spend Running Average	Forecast
33	Avg: $23,910	$286,915		(Cumulative)	per Month
34	January	$533		$533	$533	$533	$533
35	February	$560		$560	$27	$280	$27
36	March	$9,163		$9,163	$8,603	$3,054	$8,603
37	April	$22,000	$22,000	$18,630	$9,467	$6,032	$22,000
38	May	$31,969			-	-	$31,969
39	June	$31,969			-	-	$31,969
40	July	$31,969			-	-	$31,969
41	August	$31,969			-	-	$31,969
42	September	$31,969			-	-	$31,969
43	October	$31,969			-	-	$31,969
44	November	$31,969			-	-	$31,969
45	December	$31,969			-	-	$31,969
46	Totals:	$286,915	$22,000	$18,630	$18,630	$1,289	$286,915
Sheet2

Cell Formulas
Range	Formula
B34	=IF(ISBLANK(D34),($B$33-SUMIF($D$34:$D$45,">0",$G$34:$G$45))/COUNTBLANK($D$34:$D$45),CHOOSE(COUNT(C34:D34),D34,C34))