Finding y-values in budget curve app with Excel & VBA
 

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

Some one contacted me by email about the exact same problem! If that
was not you using different names for email and NG posts, that other
person is apparently getting multiple people involved in solving
her/his problem. Having been burnt in the past by people indicating
they would pay once they got a solution and then refusing because
"Sorry, but your solution doesn't work on my computer" or "Yes, your
solution does what I said it should but it is not what I wanted" or
"Yes, your solution works but I also solved it myself" all I did was
provide an example of what the solution would look like and a rate.

--
Regards,

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

In article . com,
says...
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

Remember to check the responses to the multipost in .misc and
..programming.

--
Regards,

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

In article . com,
says...
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

I am not trying to burn anyone. I have a very dissappointed client who
was excepting this project to be complete in a week and I am now on my
third week. Its sad that there are dishonest people on these boards
and everywhere in life. Thats why my father has told me since a young
age to always get signed agreement/contracts when money is involved.
Furthermore, I have never offered to pay anyone for a solution, I am
simply looking to be pointed in the right direction because i do not
know too much about math and statistics.