Cannot figure out how to interpolate revenue values with Excel

[hackman2007]

Hey everyone, I have a question on data interpolation with Excel 2007. Normally, this wouldn't be too much of an issue for me, but for some reason I cannot figure it out.

I'm working on an Income Statement, which is designed for 5 years. I have values 1 and 5, which are given to me (B7: 3,500,000 and F7: 5,200,000). I'm trying to figure out the other 3 years of revenue assuming a growth trend. The values should fill the series B7:F7.

Can anyone help me out on this?

Thanks.

 

[Gerald Higgins]

Is the interpolation straight line ?

In which case, isn't it

=$b7+(($f7-$b7)/4)

Or maybe it isn't linear, but then I think you need to give us more information.

 

[hackman2007]

Yes, the interpolation is straight line.

However, when I run that formula into my spreadsheet, it doesn't return the correct value. The correct value in C7 would be $3,864,131. The formula returns $3,925,000.

 

[Gerald Higgins]

Sorry, my formula should have been

=B7+(($F7-$B7)/4)

which in this case gives a standard increment of 425,000, which is what I meant by straight line.

But I think you mean, a standard percentage increase each year, yes ?

 

[hackman2007]

Okay, maybe it's not linear.

What kind of information would you need for non-linear?

 

[c_m]

try graphing the points and then right click on the data points, go to "Add Trendline"

 

[hackman2007]

If the interpolation is not linear, I think you need more than 2 points to fit an appropriate curve.
If more data is not available, assumptions can always be made. Like exponential, logarithmic growth?

Well, every year the amount goes up slightly, which would lead me to assume maybe exponential?

In case anyone is curious as to what I'm working with, here is link to my workbook.

http://rapidshare.com/files/161663807/Case09_JoshuaKampschmidt_2.xlsx.html

 

[c_m]

I looked at your spreadsheet and by looking at the expenses, they are approximately following logarithmic trend. So I am assuming the revenues will too in opposite direction.
Try the following formula in cell C7:

=INTERCEPT({3500000,5200000},LN({1;5}))+SLOPE({3500000,5200000},LN({1;5}))*LN(VALUE(RIGHT(C6,1)))

and drag it through E7.

Does it seem okay?

 

[hackman2007]

I'm really starting to hate this spreadsheet

Anyways, the formula did not work. It still makes the value of Revenue in Year 2 go too high. Looking at the correct values, it should be the following:

Year 1: $3,500,000
Year 2: $3,864,131
Year 3: $4,266,146
Year 4: $4,709,985
Year 5: $5,200,000

If you haven't guessed by now, this is a challenge problem for an Excel class.

Would using the Trend or Forecast functions work?

 

[c_m]

so i guess its exponential trend..huh?