Financial Planning Software: Directions for making Excel pie charts.

Step 1: Determine how many accounts you'll be working with. For example, each IRA would be a separate account. Basically everything that has a separate financial statement is a separate account.

Open the spreadsheet that has the same number of accounts. For example, if the husband and wife each have a 401(k), an IRA, and then a joint investment account - go to the sheet named Asset Distribution - Five Accounts.xls

The point of these tools is to input what they currently have, so you can see what percentages are currently in each asset class (for each account, and when all of the accounts are combined). Then you shuffle things around in the proposed section to arrive at a better mix of asset classes. Then when they engage do you to do it, you make the trades. There are no limitations (or guidance) on what types of assets, or asset classes, you can use.

Step 2: Find the cell where it says account name, and change it to the name of the client's account. For example, John Sample's WidgetsRus 401(k). Do the same thing in the proposed section by scrolling down to that cell and changing it (some of the bigger tools have cell references to automatically do this). Do all of the account name inputs in both the current and proposed sections first to help avoid confusion later.

Step 3: Get out whatever data you have on the client's account(s), and first separate them all by accounts.

When you have all of their accounts separated, decide which asset class each asset in the account should be assigned to (these are all judgment calls on your part).

Nothing is protected on these sheets, so you can change the names of the asset classes to whatever you want them to be.

It helps to make paper copies, so you can just write the asset class names you want each asset classified into on the copied page next to each asset.

Step 4: Input each asset's name into row D to the right of the asset class you've decided to put it in.

Then input the dollar amounts held into column G.

Percentage data, and data used to make the pies, will automatically populate columns I, K, & M.

Repeat until all of the first account data are input. All of the #DIV/0! symbols will go away when everything is populated.

It's up to you whether you want to leave unused asset class rows blank, or delete them. If you leave them blank, then you'll have the pie chart problems below. If you delete them, then you'll have to spend a lot of time checking formulas to make sure all of the references go to the new locations. It's easier to leave them because it helps with the presentations (by making the clients aware of all of the viable assets classes to invest in), you'll use up more asset classes in the proposed section, and once you get the hang of formatting the pie charts, it will end up being less work. On the other hand, if your methodology is such that you're always using the same (lesser number) asset classes, then you probably would want to delete the asset classes you won't be using, do all of the formula renaming, and then saving the new templates so it will open up to your way of doing things all of the time.

Step 5: When everything is input into the Current section, check to make sure all of the Totals and Grand Totals are correct. There are numerous easily made input errors that will make it not total correctly.

Make sure the percentage data in column L adds up to 100%. If it doesn't then you input data wrong somewhere.

Step 6: Don't worry about formatting the pie charts now, continue to the Proposed section.

Input your recommendations, starting with the first account.

You're free to do pretty much anything you want, as long as the bottom-line dollar amounts are the same for each account. There is a check number at the bottom of each proposed account in column L to tell you how much you're off. When you're good, it will read $0.

You can actually shuffle things around the way you want to in the Real World. Be aware of minimum trade amounts, availability of funds, and if assets are not to be sold (by client wishes or account constraints, etc.). There are numerous reasons why assets can't be sold and why ones you want to buy, can't be, etc. It's up to you to figure all of this out - before you present it to the client.

The point is to make both an overall picture, and an account-by-account asset allocation that better matches the clients' lives better than before. There is no calculation tool to tell you what you should be shooting for (which is why it's called asset distribution and not asset allocation), so just make it up as you go.

Step 7: Once you've reached a mix that you like, then use the check formulas in columns J and L to make sure they all say $0 and 100%. If they don't then you made a mistake that needs to be fixed.

Step 8: Printing. Use Print Preview to first tell how many total pages Excel thinks there are to print. Then go to the Print... menu so you can tell it to print half that many. The point here is to not waste time and paper printing anything left of column I. You'll have to do the usual tinkering with rows and columns to make it look perfect.

Now download this small spreadsheet to deal with Excel's pie chart problems:

For downloads, right click on a link below, then choose "Save (Target) As..." to save to a folder on your hard drive. Then open it with MS Excel. Sometimes the WPP's server doesn't work well with weird browsers, or it just may not work if it's not configured right. Please send e-mail if you have any problems, and it will be sent to you.

Click here to download an asset distribution demo file to see what it looked like when you started, and to see what is should look like when you're done

This Section is to Help You Format Excel Pie Charts

You can use this tutorial when you make any complex pie chart in Excel.

Tip: Always WAIT for your computer to get done thinking before you move on to the next step. Even with brand new super-fast computers, Excel is very slow at this and if you don't, it may crash on you (making you start all over again).

The best way to tell if it's done, is to wait until your display makes a change of some kind that reflects what your last action was. Most of the time, you can tell it's done when the yellow text box comes up telling you where you are. Or the cursor will change. Wait for it every time!

This tutorial uses the Asset Distribution Tool as an example, because it has the most leaders on pie charts (has more pie slices). The more rows/empty rows of data the chart has to read to make the pie, the more you'll have to tinker with it to make it look right.

There are two sheets in the workbook: Pie Charts Before and Pie Charts After Go to the before pie sheet.

See how both pies are both sized wrong, and the leader lines showing what the slices are totally out of whack? That's the problems this is trying to help you solve.

Please note that this is an MS Excel problem and has nothing to do with our programming.

Here's The Step-by-step Solution

The following directions are using the above download as an example. You may want to follow these steps using the Pie Charts Before sheet.

Do the top pie first - it's easier and will warm you up for the hard stuff.

Click 1/8 inch to the top left of the pie. In other words, follow the line separating the red and orange slices, and click just a little bit outside the pie (but still within the chart).

If you didn't do it right, then everything will be selected. If you did it right, just the pie will be selected, and the pie will now be encircled by a box with corner handles that you can click and drag on.

Click on the corner handle that is in the direction that you want to drag it to. The point is to first make it bigger. In this case, the top left handle was clicked on, and then dragged in the NW direction to make it bigger.

Now that it's the right size, you want to format the text at the end of each leader line. In other words, make the text the right size, and put it in a spot that looks good.

The leader line is the line stemming from the pie slide to the text. You can turn them all off, or separately by pie slice, if you want to, by deselecting that checkbox in one of the chart options menus (get there by right clicking the pie).

The first thing to do is see if Excel is smart enough to do it itself. Right click on the center of the pie until all of the slices and the text/leader lines are highlighted.

Click on the outside of the pie until just the text and leader lines are highlighted. Press the delete key to delete all of the text and leader lines. If the whole pie deletes, then you selected the whole pie, and not just the text/leaders. If so, press Control Z to undo.

Then right click again, and choose, Format Data Series. Choose Data Labels. Click the Show Label and Percent button. Most of this will do the trick, but if not, detailed directions are below.

If any of the text is outside of the working area, then you'll have to click on the pie, and drag it in the opposite direction so you can see and drag the text close to the pie so you can deal with it, then move the whole pie back. Sometimes you'll need to make the pie smaller to get to the rogue leader line and text label.

Click on any text at the end of a leader line. They will all highlight. Now right click on any of the selected items, and a menu will come up. Choose Format Data Labels, then Font, and then click the font size you want. Now they are all the right size (instead of some being different).

Older versions of Excel with older computers crash when you do these things, so save your work every time you make a change! After a few crashes, you'll get the point. Save now!

Now comes the tricky part - moving each leader line/text label to the right locations. First click on things until they are all highlighted at the same time. Wait until what you clicked on is done before clicking on something else!

When they are all selected, then click on them one at a time. If it looks like it wants you to edit the text, then press escape or click somewhere else, or it will be deleted.

Click on each item and drag it to the right location. Don't let up after you click on it. You first have to have them all selected by clicking on any of them, and then you have to click and hold each one and move it at the same time. When you let up with one, then you can do the others. Save your work after they are all where you want them (and if there's more than five, you may want to save mid-way).

If you want to delete items with 0% then you have to do this when that item only is selected, or they will all go away. When it's selected, press the delete key once. Try hard not to make a mistake. The problem is that if you make a mistake, and then undo, they will all usually go back to being screwed up, then it will crash.

If at any time you make a mistake like this, where you click undo (or Control Z), and it didn't go back to the way it was, save your work because the next thing it will do is crash.

Now, when you've conquered all of that, you may want to change the colors of the each slice. Click on the pie anywhere, and it will be selected. Now aim well and click on just the one slice you want to change. It will highlight. Now Right click on the highlighted slice, and the menu will come up where you can Format Data Point, and then go to Patterns and change your color.

Okay, that was the easy part. Now on to the hard part - the Current Asset Breakdown pie chart.

This is really hard because of all the blank rows in your data range. These charts are getting their data from column K (the asset class and investment vehicle's name), and the percentage held in column M.

Now you can see all of the blank rows in that data range. This makes two pie chart problems: First, it just looks like a mess, because Excel doesn't know not to make a leader line and text in the pie for blank rows (zero values). So you'll need to delete them all, one at a time.

Second, Excel gets confused, and will want to do all kinds of stupid things while you work through this - including wanting to crash every other step. So save your work after every couple of steps.

First, repeat the step above and make the overall pie the right size, and then make the text the right size. Try 8 point first.

The first thing you'll notice, is that it's very hard to find the right place to click on the pie to drag it and make it bigger. So here is what to do about that: Click outside of the pie so you're on a cell (click on any cell in column N to be sure. Now you can go to View, Zoom, 200%. Yes, 200%. Now you can scroll around until you can get a grip on the pie. Make it MUCH bigger. Now you'll have to click on an outside cell again, this time use a cell in column A.

Now reset your Zoom back to 75%. Don't freak if your pie is way too big now. At least now you can do something with it. Grab it again and make it the size you want. Save your work!

Now click on the text labels and then right click, and change the font size to 8. Save your work now again.

Now you can mess with the data labels. Click outside the pie to reset the selection mode (anywhere in column N again).

Now click on the text labels. They all are selected. Now try to only pick the ones with text, and not the ones with 0%. Just click once, and then move them to where you want them to be. Save your work frequently.

After you've moved all of the valid text labels to where you want them to be, you'll need to delete all of the useless ones that say 0%.

If you don't care that they are there, then this would be a good time to stop and let sleeping dogs lie, as deleting them all is the hardest part.

Here's how to delete them: First, in order to delete them, you'll need to move them one at a time, away from the pie. The ones that are away from the pie, you can delete by selecting them one at a time, and then press the delete key.

Remember to not press delete unless you're sure that only ONE 0% is selected! If not, all of your leads will go away, and they will all be messed up after you undo (then it will crash).

After you delete each one, the whole chart will be selected. So you need to click on a text lead, which selects them all, and then single click on another 0% lead. Save after deleting each one.

At the moment, there are still several that are close to the pie, you won't be able to do anything with them. To make things even worse, there are probably several of these critters all lumped together too (in a tree). You won't know until you try to move them away from the pie, and they won't budge.

This worst case scenario is the "tree of 0% data labels." This is where you get a line with several zero data labels connected to them. This is annoying because it's very hard to separate them, which is needed to delete them one at a time. The only advice is to click below where the tree first comes out of the pie, to be able to select them individually. Do a lot of waiting, and save your work often, because the evil tree will want to crash more here than anywhere else.

To be more specific: Grab a 0% lead that's close to the pie and drag it away from the pie. Several more 0% lines may appear. Start at the top one, and single click on it until only it is highlighted. Press the delete key. After it figures it out, it will go away. You need to wait for it to go away before you move on, or it will crash.

Repeat until all of the 0% leads are gone. Now you are free to format the colors and make it look perfect, with much less risk of it crashing.

This may sound like a major pain, but after a few times, you'll remember the steps and it won't take long at all.

Please remember that this is an MS Excel problem that can't be fixed, and isn't even in the latest version of Excel, so it's not particular to these financial plan tools.

Also please keep in mind, that at least you have the ability to control the charts. Most financial plan software won't even let you change anything.

Product Information

Fully Integrated Financial Planning Software

Menu of Retirement Planning Software

Asset Allocation Overview with Our Historical Returns

Asset Allocation Software

Asset Allocation Models with Our Historical Returns

Our Monthly Updated Mutual Fund Picks

Financial Plan Tools

Family Budgeting Calculator

Financial Planning Fact Finders

Investment Policy Statement Software