Excel Challenge - AMP Super Simulator

durand26

New Member
Joined
Sep 21, 2008
Messages
4
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Can't see the chart on that website. Could you maybe post a screenshot (or a link to a screenshot ?)
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,542
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

New Member
Joined
Sep 21, 2008
Messages
4
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" o:preferrelative="t"> <v:fill color2="white [7]"></v:fill><v:stroke color2="white [7]"><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><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><v:path o:extrusionok="f"></v:path><o:lock v:ext="edit" aspectratio="t"></o:lock></v:rect>
 

Watch MrExcel Video

Forum statistics

Threads
1,100,214
Messages
5,473,202
Members
406,851
Latest member
Debbren

This Week's Hot Topics

Top