Excel Challenge - AMP Super Simulator

[durand26]

Hi,

AMP, an Australian financial services firm, has built a retirement calculator on the web that made me hear a "klunk" sound when my jaw bounced off the ground.

It has a bar graph with a second Y axis. The second Y axis features a horizontal line with a spin button on the end that moves the horizontal line up and down. Can this even be done in Excel?

It also features customised scroll bars, which (as far as I know) can't be done in Excel.

Here is the website. Look upon its works, ye mighty, and despair:

www.amp.com.au/supersimulator

Sometimes, what pushes us to be better Excel users is seeing something amazing, and wanting to know how to do it. Could this graph be produced in Excel? Or, to be really cutting edge spreadsheet developers, do we have to start mucking about in HTML?

Durand

 

[ravishankar]

Hi
I know charts with 2nd Y axis and scrolling bar are possible in excel ( I have done it myself). would be interested to know if other features mentioned are possible
Ravi

 

[durand26]

Thanks Ravi,

So how do you add a moving bar to a chart?

Durand

 

[Gerald Higgins]

Can't see the chart on that website. Could you maybe post a screenshot (or a link to a screenshot ?)

 

[shemayisroel]

I use them all the time, here are some examples on the how to...

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=518

http://www.angelawolff.co.uk/Excel-Expert/scrolling.htm

 

[iliace]

All of that is possible in Excel, you just have to know the formulas. An easy way would be assign spin buttons to some input cells for the underlying source data. For the text box with the total savings (above the line), you'd probably need a macro.

I'm not familiar with the Super concept, unfortunately, so I don't really understand how it's calculating, and what exactly it's calculating.

 

[durand26]

Thanks guys,

Here's a screenshot of the site I was talking about:

"Superannuation" (or "Super") is the Australian term meaning money locked away in a special account that you can't touch until you retire. Over here, employers are required to take 9% of your salary and put it into your superannuation account. The theory is, by the time you retire, you'll have saved enough to give you an income stream. The problem is, how do you know if you've got enough?

So the calculator is designed to work out what your income stream will be, given your age, your salary, how much you've already got in superannuation, etc. In the example above, the orange line shows that the client wants to retire on 72% of their current salary. The 3 columns show the projected money they'll have when they retire, in good, bad and average markets. The fact that the orange line is so much higher than the top of the columns in this example means they're not going to get there.

The nifty thing about the graph is that you can click and drag the orange line up and down, and as you do, the Target Super Savings text box stays in line with the orange line, and the orange numbers change. You can also use the sliders on the right to change your assumptions and see how it affects the columns representing your income stream.

But here's what's stumped me:

How do you get the label above the orange line to move up and down with the orange line? I've tried putting text in the data label, but that didn't work. I've pasted a text box on top of the graph, but don't know how to move it. Does anyone know? Thanks in advance.
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:rect id=_x0000_s1025 style="Z-INDEX: 1; LEFT: 56.69pt; WIDTH: 260.79pt; POSITION: absolute; TOP: 283.46pt; HEIGHT: 187.09pt; mso-wrap-distance-left: 2.88pt; mso-wrap-distance-top: 2.88pt; mso-wrap-distance-right: 2.88pt; mso-wrap-distance-bottom: 2.88pt" stroked="f" fillcolor="white [7]" o:cliptowrap="t" insetpen="t" strokecolor="black [0]" filled="f" oreferrelative="t"> <v:fill color2="white [7]"></v:fill><v:stroke color2="white [7]"><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:left v:ext="view" color2="white [7]" color="black [0]"></o:left><o:top v:ext="view" color2="white [7]" color="black [0]"></o:top><o:right v:ext="view" color2="white [7]" color="black [0]"></o:right><o:bottom v:ext="view" color2="white [7]" color="black [0]"></o:bottom><o:column v:ext="view" color2="white [7]" color="black [0]"></o:column></v:stroke><v:imagedata cropright="30283f" cropleft="3352f" cropbottom="20747f" croptop="8172f" o:title="" src="file:///C:\DOCUME~1\DurandS\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.png"></v:imagedata><v:shadow color="#ccc [4]"></v:shadow><vath o:extrusionok="f"></vath><o:lock v:ext="edit" aspectratio="t"></o:lock></v:rect>