About Financial Planning Software Integration
(is listed below. The financial planning software modules are on the right-side column)
Confused? It Makes More Sense if You Start at the Home Page
Free Downloads and Money Tools
|How to Integrate these Financial Planning Software Modules to Share Input, Calculation, and Results Data with Any Program that
Can Talk with MS Excel
This page is about, and for, manually integrating the individual modules, and does not apply to the fully-integrated financial planner, which comes already hard-wired together to be automatically integrated
First a Summary of the Overall Concepts
Integration is the technical term used when the different "modules" (parts of the overall whole) are linked, and thus are able to share data with each other. Integration makes it so things can automatically "talk with each other."
What people want most is to have the input fields linked so they won't have to input basic data (like names and ages) more than once, and the end results linked so they can print one module's presentation pages after making changes in another module.
This financial planner software has the best of both worlds - first it's modular, so you don't have spend a lot of money buying everything to get just the one or few functions you want. You also don't have to spend more time inputting data that a particular module doesn't need. In other words, if you have one of the expensive vendor's financial plan software, and just want to make a simple college funding report, you'll have to input tons of data that has nothing to do with college funding. So individual modules will always have their place.
Next, if you want the modules to share data, just like the most comprehensive, integrated, and expensive financial planning software, you can easily do that too, just by following simple directions.
You also have the ability to integrate many times more - because with Excel, you can share data between modules all you want to. Even the most expensive financial plan software will only integrate to a point, and then you can't make it share data any more no matter what you do.
Even though these Excel spreadsheets are all separate modules, it's simple and easy to get them all to be integrated, just like software costing thousands annually to maintain, or our Integrated Financial Planner. It just takes a few more steps when you initially start working on a new client. For do-it-yourself investors, it only takes doing these few steps once (financial planners may have to do this for each client). Once you do it once, then things will stay like that, so no more work is usually needed.
The bottom-line is you can trade doing a few minutes more work per client for hundreds, to thousands, of dollars in annual savings. You'll also have less bugs / crashes / reboots / spam / time wasted on support calls, etc.
The directions below are worded for financial planners using these financial planning software modules to do work for their clients. The same steps pertain to investors using the money tools for themselves.
Once you understand the basic concept of Excel cell references, you can use them to quickly and easily share data between all of these software modules.
All of this will work just fine as long as you don't rename the workbooks / spreadsheets / sheets, delete them, move anything to a different folder, or rename / move folders, after you link the data. So basically after you link everything, all you have to do is not manually mess anything up, and everything will work fine.
When you change the data on one spreadsheet, the changes will then automatically flow into the other spreadsheets, whether they are open at the time or not. Closed workbooks won't calculate anything, so it's always best to keep all workbooks open that you're using (and/or have shared data in them), so they can re-calculate if needed after their input data changes.
All of this is called "cell referencing" and is basically the Microsoft way of doing the exact same thing to integrate as other software platforms. You can share any data you want to - client's names, ages, investment values, cash flow numbers, so you won't have to input the same things over and over.
How to Integrate Toolsformoney's Modules just like Expensive Integrated Financial Software
First, decide what types of work you're going to do for the client. That will tell you which of the modules you'll be using. For example, if you're going to use the Asset Allocation Software to run an investment asset allocation report, College Planning Calculator to show what's needed to send kids to college, Life Insurance Need Analysis to see how much life insurance they really need, and an overall financial plan showing what their financial future / retirement (using RP, or either version of RWR) will look like before and after your brilliant recommendations, you'd use these four modules, combined with the Cash Flow Projector. You can also choose to not use the CFP, and integrate the other four workbooks just into RWR, which will save time.
The best thing to do first is to create the asset allocation.
Let's assume here that you'll be using the Budget Planner and CFP.
Once you know the tools you'll be using, copy these five spreadsheets into a unique folder. Once all of the files are all in one happy spot on your hard drive, the rest is automatic, and will almost never cause you any maintenance or problems. Tips on organizing files for financial planning clients are here.
After setting up files and folders, the first phase of the financial planning integration process is to account for the very large, one-time, expenses of life. So if you have big future expenses, like children's college, you'd first crunch the numbers using the College Funding Software.
Then integrate them into the Cash Flow Projector. It doesn't matter if you populate the CFP with input data before or after you integrate the college planner, but unless you're used to the CFP, it may be better to start off by inputting all of the CFP data first. Then names, years, and ages will be there to make things less confusing.
So after running the college planner, then do a current snapshot of incomes and expenses using the family budget planner. Then you project these income and expenses amounts into the future using the Cash Flow Projector. Then you integrate the present and future cash flows of the college planner into the Cash Flow Projector.
Then in the year of retirement, you start using the detailed annual Cash Flow Projector expense amounts as direct income goal inputs into RWR (or RP).
Then as the assets input into RWR are used up to fund retirement, these changes in asset values can integrate into the Net Worth Projector. Then when you change anything in the college planner (like Grandma makes a $1,000 gift for tuition), everything will change, all the way up to a $1,000 net worth increase.
You can also use these numbers to show the changes in investment values in the Asset Allocation Software.
Then you can change the assumed rates of return in detail using the Future Portfolio Forecasting sheets of the Asset Allocation Software. Then the bottom-line overall rates of return calculated by the asset allocation software can be input into RWR's rate of return on assets input fields.
The Actual Integration Directions for the Worst-Case Scenario
This is an example of a worst-case integration scenario. If all of this sounds like something you'd be doing, then please don't do that! The IFP is the program you want, as all of this comes pre-hard-wired together, so you won't have to do any integration at all. If only some of this sounds like something you'd be doing, then the modules are what you want, because they're both cheaper and much easier to deal with than the IFP.
Make a unique folder that you won't have to move, delete, rename, nor otherwise fiddle with for storing these financial planner files later.
Do whatever you usually do to put all five spreadsheets in this folder: Life Insurance Calculator, Asset Allocation, College Planner, Cash Flow Projector, and RWR (Single or Dual, or RP works fine too).
Please note the links above as they will be referenced in the text below.
Open all five files with Excel at the same time.
First, run the asset allocation report. When it's done you'll have the totals for personal and qualified investments in the Current section. For example, after the asset allocation report is done, you'll have the total amount of personal investments in cell B15 and the total amount of qualified investment in cell B41 (these are the totaler cells in the demo so they won't be these exact cells you'll have once you've done your input).
Now when you're using the RWR retirement planner, you can use this data as total account values input into the asset sheets.
Assuming you want to input all of the personal investment assets into RWR's Oldest Asset #1:
Go to cell A6 in RWR's Oldest Asset #1, and enter an equal sign. Do not press enter - leave it hanging in limbo.
Next go to the Task Bar and click on that open workbook (if that wasn't set up, which you can learn how to do here, then go to View, Switch Windows, and select that workbook from there), and switch to the Asset Allocation workbook.
Now go to the Asset Allocator sheet.
Click on cell B15 and press enter.
Cell A6 of the RWR asset sheet (asset value) will now get its data from cell B15 of the asset allocation workbook (total non-qualified investments).
If you tinker with the asset allocation workbook later, and the total in cell B15 changes, it will automatically be updated in RWR, whether it's open at the time or not (it's best to keep all files open at the same time when you're doing this).
Do the same thing when you make a new RWR asset for the qualified money (IRA's, 401ks, etc.). More than likely, all of the qualified retirement assets will have about the same rate of return, and they are all probably taxed the same way, so it's fine to lump them all together while using the Flexible payout option. You can account for everything separately, and in as much detail as you want to, but this is easier and faster and the end results will all be about the same (try it and see the differences).
Repeat the process above used for the qualified assets, but this time click on cell B41 instead of B15 of the asset allocation workbook (this cell will be different after your input into the asset allocator, it's just B41 on the demo so you can see what's going on).
If you want to divide the investments up into more than one RWR asset, then you can use Excel's simple summing feature to do that. For example, say you want the John's Work 401(k) and Mary's IRA listed in the asset allocation report to be two different RWR assets:
In the demo, the IRA's investment totals are listed in cells B26 - B29 and the 401(k)'s assets are listed in cells B21 - B23.
In RWR, input an equal sign into cell A6 of the asset sheet.
Now you can either use the summing function manually, or let Excel to it automatically. To do it manually, you would input: "=sum(" and then go to the asset allocation workbook.
Click on B21 so the workbook name and all are entered into the formula. Then input: ":b23)" and press enter.
Now the contents of A6 will just be the sum of all of the investments in John's 401(k). A more experienced Excel user would have just entered "=" into RWR's cell A6, switched to the other workbook, highlighted the data range by dragging the mouse from cells B21:B23, and pressed enter.
Using these simple functions allows you to control how data is shared between the workbooks. As you can see, you can control everything as precisely as you want to once you figure it out.
Next, let's assume that you want to run an RWR scenario showing the husband passing away at some future year.
Run a capital needs analysis illustration using the life insurance calculator.
If you want to include the lump sum needs to fund college in the life insurance software:
- First run the college funding report (it's good to always do this second)
Now college needs are input into the life insurance needs software. So if the husband passes today, and you bought that much life insurance, that kid will still be able to go to college.
When everything is done, the total amount of life insurance needed (and assuming they buy this much) is listed in cell I27 of the Current Needs sheet.
Now make a new proposed investment in RWR for this.
For the RWR asset's market value, again go to cell A6 for that new asset.
Enter =, then go to the life insurance calculator, and click on cell I27 of the Current Needs sheet.
Now make the RWR asset become effective in the year you want to assume the husband passing away. You now have proceeds of the life insurance face value death benefit going into the retirement forecasting section of the financial plan. If you used the CFP, then you'd also want to stop all earned incomes from the husband when he passes in that year.
Be sure to not count college, and other expenses, twice. If you input miscellaneous expenses in RWR, ensure you don't account for them in the Cash Flow Projector too. The best place to do all of that is in the Cash Flow Projector. So if you're using the Cash Flow Projector, try not to use the manual income and expense input areas of RWR at all. All of that is just a quick and dirty way to bypass the CFP to get into the ballpark in RWR.
For example, once you've ran the asset allocation part of the college funding tool (using the Asset Allocator sheet of the College software), you'd just go to the Input sheet. Then for the initial balance needed to go into cell A9, you'd just input an equal sign there, then switch to the Asset Allocator sheet and click on cell B24, then press enter.
If you want the college cash flows to be included in the RWR report (not using the Cash Flow Projector), you would just reference the cash flows in column BB of a college planning tool 529 Calculation sheet into one of the six manual income / expense fields in the RWR Summing & Input sheet (starting in column BA of a Summing & Input sheet). Just ensure you're keeping the years all the same.
If you're using the Multiple College Calculator, and have more than one student, then use the annual expense values on either the All Kid 529 Presentation or All Kid DIY Presentation sheet, starting in cell H93.
To integrate total present values of college funding into the life insurance needs calculator: Open the life insurance needs software, and click on cell A35 of the Input sheet.
Input the equal sign, then open the single college funding calculator and click on either cell E23, E25, or P23 or P24 of s results sheet like the All Kid 529 Presentation sheet. Press enter. Now the life insurance needs will reflect changes in the college calculator. If there is more than one child, then repeat using cells A35-A39 of the life insurance software Input sheet. You can also add them all up and reference groups of input any way you want to (using the Multiple-Student College Planner).
The Monte Carlo simulations will all run just fine in both the college planners, and in RWR, after integration.
How to integrate the detailed asset values and annual cash flow numbers from the Net Worth Calculator/Net Worth Projector, Budget/Cash Flow Projector, into RWR is explained in detail in their directions here and here, respectively.
Feel free to experiment. If you make a mistake, just press escape, and then maybe control Z to undo and try again.
So as you can see, with just a little learning of basic Excel formulas and functions, you can integrate these financial planning modules as much, or as little as you want to, and everything will work just as slick as other integrated financial planning software you may be used to.
The point is that you can save hundreds to thousands of dollars per year, and get better control and Real World results, and total control over printing, with our software.
If you bought support, please send e-mail about any questions you may have about integrating the modules, then we'll work on it until it's all good.
This page probably needs to be twice as long to cover all of these details, but people seem to be getting with the program and are not asking questions anymore, so we stopped here because it seems like they all are understanding well enough. So if you do come across something that has to do with integration that's not here, and it should be, then you'll get a freebie as a thank you reward.
|Financial Planning Software Modules For Sale
(are listed below)
Our Unique Financial Services
Miscellaneous Pages of Interest
© Copyright 1997 - 2013 Tools For Money, All Rights Reserved