J
James Gross IV
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
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