Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
James Gross IV
 
Posts: n/a
Default Finding y-Values in budget curve app w/ excel

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

  #2   Report Post  
Tushar Mehta
 
Posts: n/a
Default

Remember to check the responses to the multipost in .charting 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


  #3   Report Post  
PY & Associates
 
Posts: n/a
Default

We proposed a two parts automation with distribution per period as linear.
We tested it and is working fine.

"James Gross IV" wrote:

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to assign character (text) values to y-axis in a Excel chart? cy Charts and Charting in Excel 1 July 16th 05 10:39 PM
How do I create a Bell Curve Graph in Excel fratton Charts and Charting in Excel 2 March 17th 05 07:39 PM
how do i get excel to automatically compute cell values talil Excel Discussion (Misc queries) 2 March 17th 05 05:49 PM
How do i create Lorenz curve in Excel???? Judit Charts and Charting in Excel 3 January 20th 05 07:56 PM
Missing values in Excel Line Chart mw55309 Charts and Charting in Excel 1 January 19th 05 01:10 PM


All times are GMT +1. The time now is 03:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"