Finding y-values in budget curve app w/ Excel & VBA

[James Nasty]

I've been seeking a solution to finish a development project for a
client. The project involves dispersing a total amount for a
time-phased budget. The user inputs the following:
- Total_Budget_Amount (total dollars for the whole budget term)
- Number_of_Periods (the number of months for the budget term)
- Percent_Budget_Spent (the percent of the total budget spent at
Percent_Periods_Passed)
- Percent_Periods_Passed (the percent of periods that have passed where
the sum of all expenditures for the previously completed periods equals
the Percent_Budget_Spent * Total_Budget_Amount)
.... Percent_Budget_Spent and Percent_Periods_Passed are supposed to
make up the slope of the curve

Once those four values have been provided to the excel/vba app,
individual dollar amounts to be spent at each period are to be
calculated and output to the worksheet range "Per_Period_Amount". The
second part of the app should accept some actual incurred costs data
for a given number of periods in the budget term. Then the application
is to uses all the provided information to disperse the remaining funds
over the remaining periods in the same fashion as before.

I have looked up and tried many different fucntions and algorithms
(Levenberg-Marquardt, normal distribution, Ogive, and cubic spline to
name a few) with no success in reaching the right output. I am not a
very mathematically and statistically smart individual. I dont know
what kind of function or equation to use. Every function that I try
either wants known x's and known y's, want the equation or is just way
over my head. The client has told me that he believes the problem can
be solved with the Solver in Excel. All I know is that at
(Number_of_Periods * Percent_Budget_Passed), the Sum of
Per_Period_Amounts should equal= (Total_Budget_Amount *
Percent_Budget_Spent). I would apprecuate all help that may lead me to
the promised land of project completion.

The following is some example output from the client:

$100000 for 6 periods at 60% spent / 40% periods passed

period $ for period percent of whole budget spent per period
1 9,986 9.9860%
2 23,730 23.7300%
3 26,285 26.2850% sum at 40% completion =
$60,001
4 21,228 21.2280%
5 13,606 13.6060%
6 5,165 5.1650%
total 100,000

 

[Guest]

The solver in Excel does not need to know the actual formula; it uses
iteration and convergence to find a solution. You tell it what cell it is to
check and what is the goal for that cell's value(you can choose to minimize
the value, maximize it, or reach a specific value - I would guess you want
the third, and that that would be zero at the end of the overall budget).
You can then tell it what cells it can modify (what are your variables). It
will use its own optimization algorithm to adjust those values to try to find
a solution. You don't need to know anything about the actual solution, you
just need to have a well-behaved function underlying the process (in other
words, no discontinuities or "infinities").

For more see the help file: "Define and solve a problem by using Solver"

 

[Tushar Mehta]

Remember to check the responses to the multipost in .charting and
..misc.
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[James Gross IV]

I've tried using the solver and i am getting an error w/ no output. I
think the error is because one of the constraints checks the sum of a
certain number of periods that havent been given a value yet by the
solver. Any suggestions on how to fix this or do this another way?
Here is my code.

Dim m As integer
Dim Per_Period_Budget_Range As Range

'Set Up Range for num periods
With Range("A8")
For i = 1 To Range("Num_Periods").Value
.Offset(i, 0).Value = i
.Offset(i, 1).NumberFormat = "$##,###,##0.00"
.Offset(i, 1).Value = 0
Next
End With

'Set up range for solution
With Range("Budgeted_Dollars")
Set Per_Period_Budget_Range = Range(.Offset(1, 0),
..Offset(Range("Num_Periods").Value, 0))
Per_Period_Budget_Range.Name = "Per_Period_Budget"
.Offset((1 + Range("Num_Periods").Value), 0).Name = "Total"
Range("Total").Formula = "=Sum(Per_Period_Budget)"
m = Application.RoundDown((Range("Num_Periods").Value *
Range("Percent_Completed").Value), 0)
.Offset(m, 2).Name = "Max_Period"
Range("Max_Period").NumberFormat = "$##,###,##0.00"
Range("Max_Period").FormulaR1C1 = "=Sum(R[-" & m - 1 &
"]C[-2]:R[0]C[-2])"
.Offset(m, 3).Name = "Max_Spent"
Range("Max_Spent").NumberFormat = "$##,###,##0.00"
Range("Max_Spent").Formula = "=Product(Budget_Amount,
Percent_Spent)"

End With

SolverReset
SolverOK SetCell:=Range("Total"), MaxMinVal:=3,
ValueOf:=Range("Budget_Amount"), ByChange:=Range("Per_Period_Budget")
SolverAdd cellRef:=Range("Max_Period"), Relation:=2,
FormulaText:="Max_Spent"
SolverOptions AssumeLinear:=True, AssumeNonneg:=True
SolverSolve

 

[PY & Associates]

Is it too late to let us try please?

 

[PY & Associates]

I propose to solve your problem in two step automation.

First step is needed because there is no budget yet.
User inputs number of period and total budget amount
He then triggers program to prepare columns as follow
Nr of Period / % Budget / $ Budget

The program lays out budget percentage linearly and gives three column sums
User goes back to adjust % Budget column and the column sum reminds him if
total is 100%

Second step is tracking
User input %TimeSpent, %BudgetSpent
He triggers program to compute
NrOfPeriodSpent, with fraction
%BudgetSpent / NrOfPeriodSpent =%BudgetSpentPerUnit
lay from first row down %BudgetSpentPerUnit, last row is fraction or zero

%BudgetBal =1 - %BudgetSpent
NrOfPeriodBal, with fraction
%BudgetBal / NrOfPeriodBal==%BudgetBalPerUnit
lay from last row up %BudgetBalPerUnit, top row is fraction or zero

Sum last two columns