Charting time series based on different scenarios

**_keith** · 01-05-2011, 03:21 AM

Hi everyone

I have created a spreadsheet that has about some editable fields (5 at the moment), it then does a 'rent vs buy mortgage analysis' and produces some calculations for say 20 months (i.e. like a time data series, with about 5 data points each month for 20 months).

Instead of the current 5 input fields I want is to drive the calculation of a range of scenarios. So each scenario would contain different values for each of the 5 inputs (perhaps fed in from a table in another sheet?). Then I want to chart the results.

It's basically charting the time series data for different scenarios on the same chart.

I don't really know enough about excel to know where to start, is it supported out of the box? Do I need to write VBA? I don't even know the key words to help on my Google search!

Any help appreciated, thanks

**6StringJazzer** · 01-05-2011, 11:59 AM

It would be helpful for you to attach the workbook you have now to get us started. I would like to see how you have your data organized, and a better description of what you want your chart to look like.

Calculating data for multiple scenarios and plotting the results of those scenarios on a graph are two separate issues, and I'm not sure which one of those is giving you trouble.

In any case, it is quite straigtforward in Excel to plot multiple lines on the same chart, using data in different places in the workbook. I'm guessing what you want to do is totally out-of-the-box.

**_keith** · 01-05-2011, 02:53 PM

Thanks for your help, really appreciated.

I've attached the workbook. Basically I want the fields in yellow to be in a table and these would be the scenarios (currently it only allows for 1 scenario, i.e. the current values of the fields in yellow). Then I want to chart the fields in blue. I could easily chart the fields in blue as is, however what i really want to to compare against scenarios. e.g. if the interest rate was x vs y, if the property prices was x vs y etc. And I want these on the same chart.

From a 'business point of view', the calculations are really only a start, not comprehensive and I haven't really checked them yet, but I just want to know how to do more advanced excel.

I'm quite happy to research on excel, I don't mind writing some VBA if I have to, its just I'm not sure whats supported or what keywords I need to point me in the right direction. So any pointers on excel functionality like this, or keywords much appreciated.

**6StringJazzer** · 01-06-2011, 01:58 PM

I understand exactly what your data is all about, very standard stuff. But I am having trouble understanding what you want on your graph. You said you want to plot the data in blue, that's 13 columns of data, over 120 periods, 5 scenarios. I don't think you want 65 lines on one chart?

Do you just want to plot bottom-line results, and not the whole amortization schedule? Which values are you interested in?

Also, I can't resist mentioning that the correct spelling is "principal"

**_keith** · 01-06-2011, 05:06 PM

Originally Posted by 6StringJazzer

Do you just want to plot bottom-line results, and not the whole amortization schedule? Which values are you interested in?

You right actually, its probably only the Equity columns, I guess at the end of the day that's the bottom line. So step 1 would be a line chart showing 2 series, equity if you bought (basically column J value) and equity if you rented (column Q values). That's pretty easy, just a couple of clicks. But If I wanted to compare these 2 series for a mortgage with an interest rate of 3, vs an interest rate of 6. In total that'd be 4 lines on the chart, mortgage equity @ 3%, rent equity @ 3% AND mortgage equity @ 6%, rent equity @ 6%.

I thought if I could add another couple of columns to the red table (say columns G, H I) and put different values for each of the settings, then you could see how they play off against each other on the chart. But without having repeating sets of blue tables, I don't know how I could do this.

Again thanks for the pointers!.

Originally Posted by 6StringJazzer

Also, I can't resist mentioning that the correct spelling is "principal"

That my friend, is why I'm a programmer

**6StringJazzer** · 01-06-2011, 10:02 PM

OK, I've figured this out except for one thing, which I'll get to in a minute.

I have created a new sheet that has all the same parameters, one row per scenario. You can add scenarios as needed, but also need to add them to the chart (I neglected to make this a list, which would allow that to happen automatically).

Note that in your amortization table, you increased the home value by the same pound amount each month; that is, you applied the property value increase rate to the original value each time, instead of the current value. I corrected that in my sheet.

Where I got stuck was calculating the tax on savings each month, so for now I left it out. I am sure there is a way to do that with a formula but I need bit of quiet time to figure that out.

But this gives you the idea.

**6StringJazzer** · 01-06-2011, 10:10 PM

Where are my manners? Welcome to the forum!

**_keith** · 01-08-2011, 06:41 AM

Fantastic, I'm looking at your work now. And I don't know why it didn't occur to me to just make one table with the specific values I want to plot. I just had it in my mind that I needed to plot the amortization table, and do it for different scenarios. But the separate table with the exact specifics allows one to chart the results much easier.

Thanks for all your help, you've been excellent