maths/excel question help

simon334

I am plotting sales targets for the next three years.
if I start from this January with 10,000 euros with a target for end of year 1 of 370,000, of year 2 750,000 and year 3 1,000,000.

I would like to know what the turnover each month should be to reach to reach target.

In excel i plotted months 1-36 in one column then the accumaliative totals in next column @ months 1 10,000, 12 370,000, 24 1,120,000 and 36 2,120,000 and then produced a graph x,y scatter.

I produced a trend line (power Line) which follows the curve nicely and it has given me the formula y=9799.7x1.4889 the x bit is written in smaller text and at high level in the line so i think it is to the power of or somthing like that?

Can I take this formula and use it some how to plot the the cumulative totals for the months in between months 1,12,24,36?. And or use it to work out turnover per month requirments.

This is getting a bit much for me, hoping a maths head out there can help.

Thanks

simon

MathsManiac

The attached has three possible answers for you. The first uses the fitted power model you found. The second uses a polynomial of degree 2, and the third is a polynomial of degree 3.

See which you like best and delete the others.

This isn't a very flexible spreadsheet, by the way; it won't update itself if you change the original targets. That would be a bit more complicated.

Will that do?

Delphi91

There's a quicker way to do it, if I understand what you're asking.

Put 10,000 in a cell corresponding to January.
Put 370,000 in a cell 11 cells down, corresponding to December.
Select from the first to the second.
Click on EDIT, then Fill, then Series.... You'll probably find that Excel has already filled in the relevant data for the step value.
Click Ok and hey presto, you have your increasing monthly turnover totals.

Repeat for each year.

MathsManiac

Delphi91 wrote: »

There's a quicker way to do it, if I understand what you're asking.

Put 10,000 in a cell corresponding to January.
Put 370,000 in a cell 11 cells down, corresponding to December.
Select from the first to the second.
Click on EDIT, then Fill, then Series.... You'll probably find that Excel has already filled in the relevant data for the step value.
Click Ok and hey presto, you have your increasing monthly turnover totals.

Repeat for each year.

Yes, but that will give you a piecewise linear function, not the smooth function that the OP indicated he wanted.

Delphi91

MathsManiac wrote: »

Yes, but that will give you a piecewise linear function, not the smooth function that the OP indicated he wanted.

True....