Math (excel?) question

[coolVariable]

I have a string of numbers that together can represent a graph. Is there any way I can essentially calculated smaller increments on that graph based on the existing data?
E.g. the numbers represent timing assumptions for cash flows on an annual basis. Can I calculate monthly timing assumptions based on the annual data?

E.g.: I have the following annual data
0.0% 21.7% 63.3% 6.3% 2.0% 2.0% 1.5% 1.0% 1.0% 1.0% 0.3%

Obviously, I cannot simply divide each number by 12 to get the monthly numbers, since the data represents a curve (in year 2 it probably goes from 0% in the 1st month to a number, so that the total sum represents 21.7% ... and since the year after is 63.3% the numbers probably increase each month).

How can I calculate this?

 

[imported_Imp]

No comprendo.

Are you trying to fit a curve to your data, best-fit line, or a mathematical approximation? I remember that Excel has a function-analysis/numerical approximation function, but it's an extra function you have to install off the disk (if you skimmed).

 

[gwai lo]

It sounds like interpolation, except you only have a single data point..

If you know the general trend of the data, you can solve for them, you'll again need two data point in order to solve for the coefficients.

edit: where "them" = the functions that model what you're looking for

Are these points for years? or months? I'm sort of confused..

 

[Stojakapimp]

Use Matlab....it'll make your life easier

 

[ggnl]

you can use excel to create a regression line with the data you already have and then use that formula to interpolate other points

 

[calvinbiss]

Originally posted by: ggnl
you can use excel to create a regression line with the data you already have and then use that formula to interpolate other points

This. You need the function that creates your curve, then you can plug in whatever value of x you like.

 

[coolVariable]

Originally posted by: calvinbiss

Originally posted by: ggnl
you can use excel to create a regression line with the data you already have and then use that formula to interpolate other points

This. You need the function that creates your curve, then you can plug in whatever value of x you like.

This is exactly what I need.
How do I do this in excel?

 

[ggnl]

Originally posted by: coolVariable

Originally posted by: calvinbiss

Originally posted by: ggnl
you can use excel to create a regression line with the data you already have and then use that formula to interpolate other points

This. You need the function that creates your curve, then you can plug in whatever value of x you like.

This is exactly what I need.
How do I do this in excel?

Put the data in a scatter graph. Then right click on any point and select Add Trendline. Choose the type of trendline that best fits your data set (highest R^2 value). Then under the trendline options check the box that says Display equation on chart. This will give you a linear equation that you can plug data into.

 

[coolVariable]

Originally posted by: ggnl

Originally posted by: coolVariable

Originally posted by: calvinbiss

Originally posted by: ggnl
you can use excel to create a regression line with the data you already have and then use that formula to interpolate other points

This. You need the function that creates your curve, then you can plug in whatever value of x you like.

This is exactly what I need.
How do I do this in excel?

Put the data in a scatter graph. Then right click on any point and select Add Trendline. Choose the type of trendline that best fits your data set (highest R^2 value). Then under the trendline options check the box that says Display equation on chart. This will give you a linear equation that you can plug data into.

None of those fits it well.
Essentially I want the function for the graph that is displayed when you choose graph => scatter =>smooth lines.

 

[simpletron]

first, you need to change the data from yearly outlay to the cummalitive sum of all previous years, so 0 21.7 85 91.3 93.3 95.3 96.8 97.8 98.8 99.8 100.1

then you need to create a cubic spline that goes through that data. this would give you a smooth curve for the cummalitive sum, that is calculatable everywhere and passes though all data points. then you just take the differences between monthly totals to figure out individual monthly outlays.

the problem with this is the only way I know how to calculate the spine function involves taking the inverse of 11x11 matrix and then multipy that by 11x1 matrix, which I have no clue how to do in excel.

also there is no spline function in the standard excel library, but maybe you can find one on-line.

 

[coolVariable]

Originally posted by: simpletron
first, you need to change the data from yearly outlay to the cummalitive sum of all previous years, so 0 21.7 85 91.3 93.3 95.3 96.8 97.8 98.8 99.8 100.1

then you need to create a cubic spline that goes through that data. this would give you a smooth curve for the cummalitive sum, that is calculatable everywhere and passes though all data points. then you just take the differences between monthly totals to figure out individual monthly outlays.

the problem with this is the only way I know how to calculate the spine function involves taking the inverse of 11x11 matrix and then multipy that by 11x1 matrix, which I have no clue how to do in excel.

also there is no spline function in the standard excel library, but maybe you can find one on-line.

Sorry - doesn't make any sense to me.
In laymans terms?

 

[IHateMyJob2004]

10% would be:
1.10

If you want to break up that annual gain into the monthly equivalent, it is 1.10 ^ (1/12)

Do this in a table then graph the table.

Or you might be after a best fit curve using he annual data. I honestly don't totally get what you are doing.

 

[Ricochet]

Linear regression might not work. Try polynomial.

 

[Scarpozzi]

Statistics Class? Operations Management?

All my books always had a nice Excel how-to insert that explained how to work the problems for that chapter. It made it easier to figure out what to do for whatever type of problem we had to work on.

If you can identify what kind of problem it is you're doing (what skills you need to understand). It will make it easier to accomplish because you'll be able to google the name of the method or problem type.

 

[Fenixgoon]

you want interpolation and/or polynomial curve fitting.

 

[simpletron]

first, I assumed that the data you gave was the yearly total for a given year. for example during year 2 or 3(depending on whether you start at year 0 or 1) you spent 63.3%.

the cubic spline is a twice differentiable piecewise function that passes through all data points. what that means is the function is continous, its derivative is conitinous aka the curve is smooth with no sharp changes in direction. peicewise in this case means there will be a function for each interval(year 1 to 2, year 2 to 3,...) and that function is at most a cubic because it's the cubic spline. the cubic spline is a method for interpolation, meaning it is good for estimating for values between data points.

given my first assumption, if you use the cummalitve sum + cubic spline, you will be able to estimate the total spending up to any month in between the start and end. and thus after you figure out the spline, to find the spending for a particular month you just take that month total and subtract the previous months total.

now on to how to figure out the cubic spline - the math.
X = [ 0 1 2 3 4 5 6 7 8 9 10] (the year)
Y = [ 0 21.7 85 91.3 93.3 95.3 96.8 97.8 98.8 99.8 100.1] (the cummalitive sum)
let Xi and Yi be the ith data point starting at zero, so Y0 = 0, Y1 = 21.7, Y2 = 85,...

first, solve this
[ D0 ] _ [ 2 1 0 0 0 0 0 0 0 0 0]^-1 [3*(Y1-Y0)]
[ D1 ] _ [ 1 4 1 0 0 0 0 0 0 0 0] __ [3*(Y2-Y0)]
[ D2 ] _ [ 0 1 4 1 0 0 0 0 0 0 0] __ [3*(Y3-Y1)]
[ D3 ] _ [ 0 0 1 4 1 0 0 0 0 0 0] __ [3*(Y4-Y2)]
[ D4 ] _ [ 0 0 0 1 4 1 0 0 0 0 0] __ [3*(Y5-Y3)]
[ D5 ] = [ 0 0 0 0 1 4 1 0 0 0 0] _* [3*(Y6-Y4)]
[ D6 ] _ [ 0 0 0 0 0 1 4 1 0 0 0] __ [3*(Y7-Y5)]
[ D7 ] _ [ 0 0 0 0 0 0 1 4 1 0 0] __ [3*(Y8-Y6)]
[ D8 ] _ [ 0 0 0 0 0 0 0 1 4 1 0] __ [3*(Y9-Y7)]
[ D9 ] _[ 0 0 0 0 0 0 0 0 1 4 1] __ [3*(Y10-Y8)]
[ D10 ]_ [ 0 0 0 0 0 0 0 0 0 1 2] __ [3*(Y10-Y9)]
this is the matrix math that I don't know how to do in excel if you have a graphing calculator just calculate this and put the infomation into excel. all the underscores are just place holders.

now the function for the interval Xi to X(i+1) is
Ai + Bi*(x-Xi)+Ci*(x-Xi)^2+Di*(x-Xi)^3, where
Ai = Yi
Bi = Di
Ci = 3*(Y(i+1)-Yi) - 2*Di -D(i+1)
Di = 2*(Yi-Y(i+1)) +Di +D(i+1)
notation: Y(i+1) is the next Y after Yi, not Y*(i+1)

so after you do this, you should have 10 cubic functions, one for each interval, to find the total spending to a particular month, add 1/12 for each month to the year and input it into the correct cubic function. for example, april of the fourth year, put 3.333333 into the function for the interval 3 to 4. after you figure the monthly total for each month, I would plot total monthly estimates and the total yearly data on top of each other to see how it looks and check the math, they should overlay and the total monthly data should form a smooth curve that passes though the yearly. then do the subtractions to get the individual monthly outlays.

 

[QED]

http://www.ozgrid.com/Excel/ex...te-cubic-curve-fit.htm

Use this to get the cubic spline for your points in Excel.

Set your x-values in terms of months, so the (x,y) points you are plotting are:

0:0
12: 0
24: 21.7
36: 85.0
48: 91.3
60: 93.3
72: 95.3
84: 96.8
96: 97.8
108: 98.8
120: 99.8
132: 100.1

You can then use the spline or interpolate functions to get values for when x =1, x=2, (etc).

You can then take the difference between consecutive months to find the growth for that particular month.

 

[Paperdoc]

You're essentially asking Math to do your thinking for you, without realizing the assumptions you bring to the problem. With a set of (x.y) points where x is time (year or month) and y is estimated performance (maybe your data represent year-over-year growth of revenues, maybe not), you can always find a mathematical formula to fit the data to within some tolerance level. A Regression Line as commonly used (and this is what Excel's Regression does) presumes a LINEAR mathematical model (or first-degree polynomial) is the best fit for the data. That is, it presumes the data can be adequately represented by a formula that looks like

y = a(1) x + a(0)

where a(1) is the coefficient of x to the power 1 (that is, the slope of the resulting straight line), and a(0) is the coefficient of x to the zeroth power (or, it's the intercept).

In more general terms, you can get a formula to fit your data much more closely by using a polynomial of higher degree. For example, a fifth-degree polynomial looks like

y = a(5) x^5 + a(4) x^4 + a(3) x^3 + a(2) x^2 + a(1) x + a(0)

The more terms you use, the better the fit to all your data, up to a point. If you have n data points, a polynomial of (n-1)th degree (which will have n coefficients) will fit your data EXACTLY. But so what? All you've managed to so is produce a smooth curving line that fits your data points.

Another very simple way is to draw straight lines from each point to the next one. Between any two points you just presume a linear interpolation. This fits all your points EXACTLY again, but it just doesn't have the smooth curves we all like to see.

If you are doing a business plan and the data represent year-over-year performance improvements, but you want to break it down to monthly detailed projections, your real task is to determine where the results come from. With absolutely no knowledge of the underlying processes at all, you might well be tempted to just make up a smooth curve that joins your yearly anchor points and follow it because it looks good. But if this really is a business plan, I sure hope you know more detail than that. Are you selling a new design of cross-country skis? I bet the sales projections jump up wildly in November through February, and are pretty small in July! So you need to apply educated judgment to derive the monthly detailed numbers. And I guess you want to be sure the year-end results you generate match the given anchor points. Otherwise you'll have a harder time making someone else accept your projections.

 

[BrokenVisage]

bumped because you used 'math' instead of 'maths'..

 

[coolVariable]

This is all way too complicated for me.
For a non=math person.
How can this be done in excel.

They are cash flow timings for money flowing in.
Out of 100%, x comes in year 1, y year 2, etc.
Instead of the data x for year 1, I need x1 for the first half year in year 1 and x2 the second half, etc.
x1 + x2 obviously need to equal x.
x1 and x2 also need to follow the overall trend described by the total dataset.

When you do a scatter graph in excel with a smooth line it creates a really nice curve.
I essentially need the function for that smooth line so that I can look up additional values on the x-axis.
None of the trendlines in excel (where it is easy to identify the function) comes even close.

 

[imported_Imp]

Right click on your data points after you have created your chart, then "Add Trendline". Try the various curve fitting methods, and keep moving numbers until you get a nice fit through most points.

When you right-click the trendline, go to Format Trendline -> Options -> Display Equation.


Edit: Woops, ggnl already suggested this.

 

[coolVariable]

Originally posted by: Imp
Right click on your data points after you have created your chart, then "Add Trendline". Try the various curve fitting methods, and keep moving numbers until you get a nice fit through most points.

When you right-click the trendline, go to Format Trendline -> Options -> Display Equation.


Edit: Woops, ggnl already suggested this.

Trendlines don't fit the graph very well (surprisingly, since it is a quite simple graph) - see my post above yours.

 

[jiggahertz]

Originally posted by: Stojakapimp
Use Matlab....it'll make your life easier

>> help interp1

excel is for kids