Using Curve-fit for time-phased budget app in Excel

[James Nasty]

am currently working on a time-phased budgeting app in excel for a
client. I'm supposed to deliver the app to him by the close of
business TODAY. I thought it was completed until I was told yesterday
that my output calculations weren't right. Here's what the
requirements of
the project are:

"I need to time-phase budgets. generally, there are two scenarios I
work
in.

The first is - there are no actuals or costs to date. I provide the
number of periods to spread the budget over, I provide the amount to be
spread, and I provide the "shape of the curve"....that is - if I said
"50%" - then, at the 50% mark of 'time' (i.e., periods); 50% of the
"amount" will have been spent; if I said "30%", then at the 50% mark of
time, 30% of amount will have been spent.

The second scenario is a situation whereby I've already incurred
actuals or costs-to-date within my budget - and now I need to
time-phase the remaining budget over the remaining number of periods."

I was just informed that the slope of the curve he refers to is
supposed to be an actual curve. I thought it was just used to spread
budgeted amounts evenly among certain ranges of periods. I asked the
client for a little more insight into how this curve things is to be
calculated and he told me that he had an app that did this before and
it used the solver in Excel and the curve-fit function. I did not find
a curve fit function.

I have been furiously searching the net since trying to find out more
about this curve-fit function or concept. If you can provide me with
any more insight into this problem, i would greatly appreciate it.

 

[Mike Middleton]

James -

Regarding "the solver in Excel and the curve-fit function," once you have
specified a functional form for the curve (linear, polynomial, etc.), you
could set up a worksheet that (a) has tentative values for the function
parameters (e.g., slope, intercept) in cells, (b) uses references to those
cells for the function to compute fitted values for each data point, (c)
computes deviations (actual - fitted), and (d) summarizes the fit with sum
of squared deviations.

Then, in Solver, the target cell to be minimized is the cell containing the
sum of squared deviations, and the changing cells are the cells containing
the tentative parameters. This approach can be used to fit any functional
form to actual data.

Alternatively, Excel's Chart Trendline feature can be used to fit standard
functions like linear, polynomial, exponential, and power. Tushar Mehta has
a tutorial at
http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm

- Mike
www.mikemiddleton.com

 

[Mike Middleton]

James -

If it's not linear, i.e., "spread budgeted amounts evenly among certain
ranges of periods," then you need more information about how it should be
"spread," i.e, the functional form.

- Mike
www.mikemiddleton.com

 

[James Nasty]

A few more terms i have encountered on my journey to a solution are
Ogive functions, gaussian distribution, cumulative distribution
function. any help and enlightenment is appreciated.

 

[Michael R Middleton]

James Nasty -

A few more terms i have encountered on my journey to a solution are Ogive
functions, gaussian distribution, cumulative distribution function. any
help and enlightenment is appreciated. <

"Gaussian distribution" is another name for the normal distribution
(bell-shaped density function).

"Cumulative distribution function" accumulates frequency, probability, or
density corresponding to values of a frequency distribution, probability
mass function, or probability density function.

"Ogive" refers to the shape of a cumulative distribution. For example, the
ogive is an S-shaped cumulative distribution for the bell-shaped normal
density function.

For more information and alternative definitions, refer to any basic
statistics book or type the phrase into www.google.com.

- Mike Middleton