🏆 Cashflow and Networth Analysis Workbook (Excel)

[jackmansean64]

Overview

The Cashflow and Networth Analysis Workbook provides detailed insights into your personal finances by analyzing your spending, income, assets, and liabilities over time. It enhances your understanding of your financial health and progress towards financial independence through powerful data visualizations. Designed for both Tiller users and standalone users, this workbook operates without VBA or macros for easy accessibility.

I’ve been developing this workbook since I switched from Mint to Tiller a couple of years ago. It mirrors and builds upon many of Mint’s features and is partially inspired by the approach outlined in one of my favorite personal finance books, ‘Your Money or Your Life.’

Cashflow and Networth Analysis Sheet Map

Installation and Documentation

The Cashflow and Networth Analysis Workbook is hosted on my blog as a downloadable file. It’s built directly upon the Tiller Foundation Template but can also be used independently by importing your financial data manually.

The workbook is for Microsoft Excel for Desktop only. It’s compatible with both Windows and MacOS, though I haven’t extensively tested it on MacOS.

Setup

1. Download the workbook from my blog link above.
2. Set up the workbook and foundation sheets using Tiller’s Guides.
3. Copy-paste any existing transactions, balance history records, categories, or accounts into the foundation sheets. Make sure the source data matches the column layout and formatting of the destination tables.
4. Use the Tiller Money Feeds add-on to pull your current transactions and balance data (Tiller Users Only).
5. Perform a data refresh.
   

Initially, the workbook will contain placeholder data, which should be replaced with your actual financial data without deleting the placeholder rows to maintain the workbook’s integrity. See the documentation on my blog for more details.

Usage

Day-to-Day Operations:

1. Update your workbook with new transactions and balance history records as they occur.
2. Categorize your transactions manually or use Tiller’s auto-categorization features.
3. Refresh the data in the workbook to update the dashboards
4. Review and analyze your financial data. The typical process I follow after updating the workbook at the end of the month is to:
   • Review the Year-to-Date Summary sheet to see my current networth and my income, spending, and savings rate for the past month and the year so far.
   • Analyze the prior month’s spending in detail using the Spending Dashboard and intermediate sheets.
   • Review the Cashflow and Networth Summary Dashboard to review and reflect on my progress towards financial independence.
5. Explore the dashboards to find answers to questions you might have about your finances such as:
   • What does my typical monthly spending look like?
   • How has my spending on groceries changed over time?
   • What was my savings rate last year?

Permissions

This workbook is available for you to copy, use, and modify under the condition that any redistribution of the workbook or its derivatives include a direct acknowledgment of my authorship and a link back to my blog.

Give Me Feedback (Please)

I plan to continually improve this workbook and your feedback will help guide me!

Notes

This template is designed to be robust and user-friendly, but it may require occasional troubleshooting due to the complexities of Excel and the limitations of my approaches. See the Troubleshooting section of the documentation on my blog for tips on resolving common issues.

FAQ

Q: How often should I refresh the data in my workbook?
A: Whenever you make changes to the contents of the foundation sheets (i.e. transactions, balance history, categories, and accounts).

Q: What should I do if I encounter data refresh errors or other issues?
A: Check the troubleshooting section for steps to identify and resolve common issues. If you’re still stuck, feel free to send me an email at jackmansean64@gmail.com or post a comment in this thread and I’ll try my best to help you resolve your issue. Questions and feedback are highly encouraged!

Q: Are you continuing to update the workbook?
A: Yes! Updated versions will be provided in the blog post linked above. See the About sheet in the workbook for a revision history.

Q: Can I integrate your your workbook with my existing workbook?
A: No, or at least not easily. This workbook was not designed as an add-on. It would be easier to use this workbook as a starting point and add other worksheets onto it.

[jackmansean64]

Hello everyone!

I wanted to extend a big thank you to Tiller for featuring my Cashflow and Networth Analysis Workbook in the recent Tiller Money Memo newsletter. It’s truly exciting to see so many new downloads and interest in the tool I’ve developed!

While the increased attention has been fantastic, I’d love to hear from you all. Any feedback, suggestions, or questions you might have after using the workbook would be incredibly valuable. Your insights are crucial for further improvements and ensuring the workbook is functional in a variety of scenarios.

Also, if you find the workbook helpful, please consider voting on this post to show your appreciation. Engaging with this post through replies and votes can help increase its visibility, making the tool available to more users.

Thank you again for your interest and support!

[Clint.C]

Thank you @jackmansean64 for extending your Cashflow and Networth Analysis Workbook to the Tiller community.

I would love to use it and include it within my Tiller based financials. If I am understanding the current architecture, it is a stand-alone spreadsheet. It requires me to copy over my data from the Tiller Money template to take advantage of using.

Do you have any plans to make your Cashflow and Networth Analysis an add-on community based solution that would integrate directly into the Tiller Money template?

Clint

[jackmansean64]

Thanks for the question @Clint.C!

I suppose I’m not clear on the difference between a stand-alone spreadsheet and an add-on community based solution. I took a quick look for articles in the community defining these but couldn’t find an answer. If there is additional criteria that my workbook needs to meet to be classified more officially I’m all ears and certainly interested in trying to meet them!

My workbook is built upon the Tiller Foundation Template sheets (Accounts, Transactions, etc.), is fully compatible with the Tiller Money Feeds plugin, and I believe meets all the requirements outlined in the Builder’s Guide for Microsoft Excel. Is there anything I’m missing?

The reason I added the comment above about my workbook not easily integrating with an existing workbook is due to its scope and the perceived effort to integrate all 21 sheets and make the other required tweaks to the foundation sheets (I added a few custom columns). My workbook also heavily relies on the Excel Data Model and Pivot Tables, so I’m not sure how easily those can be brought over (I haven’t tested it). So, I figured it would just be easier for users to copy their data into my workbook along with any other sheets that they want to use.

However, if there is demand for my workbook to integrate with an existing one I could certainly try that out and if it’s doable, update my guidelines with instructions to do so.

[Clint.C]

@jackmansean64 Sean, thank you for your response and questions

When I first saw your CashFlow and Networth Analysis Workbook I assumed it was going to be a “plugin” or add-on. After reading through your detailed write-up and map, I quickly realized there was a lot more going on than just a dashboard. You have done a fantastic job with the design, write-up and providing a useful dashboard. I appreciate your time and effort to develop and provide this solution.

My initial thoughts and concerns were:

1. Using two templates
2. Manually copying of data.

@jackmansean64

Is there anything I’m missing?

I am just a Tiller Money user as my software developer and I.T. architect days ended a long time ago. Being relatively new to Tiller, I am not in any position to determine how your solution should or does fit or meet the Tiller standards and guidelines. Although, I did read through the Builders Guides and other supporting reference developer documentation during my trial period. I wasn’t planning to build any solutions. I wanted to gain an high-level understanding of the support for community solutions. I value these solutions and they are a part of what make Tiller so powerful and unique.

@jackmansean64

I’m all ears and certainly interested in trying to meet them!

Since you asked, I’ll share my initial thought on what might make your dashboard a more seamless and automated reporting solution Tiller users. What do you think about having an automated “refresh process”. This process would “add-on” somewhat like the Tiller Money Feeds add-on tool. It would conceptually slide in-between the users existing Tiller Money Template and your template as-is on your map. It would be a read-only process against the users template data and refresh your template tables.

@jackmansean64

So, I figured it would just be easier for users to copy their data into my workbook along with any other sheets that they want to use.

This process would allow the user to use their copy of the Tiller Money template supplied and maintained by Tiller. They would then view your template for the reporting dashboard. This process would also remove the need for the user to do any manual copying of data to initially populate and then refresh the data.

Obviously, easy for me to say. Again, thank you for your creative work. I do appreciate it.

Clint

[jackmansean64]

Thanks so much for the feedback and suggestion!

I’m not sure I fully understand why a refreshing tool would be necessary since users can already sync their data with my workbook via their Tiller Money Feed and refresh their data across all their synced workbooks. I’m pretty sure Tiller supports multi-workbook syncing… Let me know if I’m mistaken.

Regardless, it sounds like yourself (and likely other users) are nonetheless hindered by the initial setup of manually copying data over to use my workbook. Fair enough! I think a native Excel add-on would be cool but probably overkill. It only took me a few minutes to write a prototype python app that can copy data from one Tiller workbook to another. Would that help with the initial setup?

[Clint.C]

Hi Jack,

@jackmansean64

Let me know if I’m mistaken.

I wouldn’t say you are mistaken. It is just having two Tiller templates. Or one template, if we switch over to your copy. Remember, we have new Tiller users who are not spreadsheet savvy. The simpler, less manual work will reach a larger audience. For me, my view in using your tool, is one of a reporting view off of the Tiller hosted template (vs hosted by you).

@jackmansean64

native Excel add-on would be cool but probably overkill. It only took me a few minutes to write a prototype python app that can copy data from one Tiller workbook to another. Would that help with the initial setup?

Yes, it would help with the initial setup. Especially if it is integrated and not a manual step. Again, I am thinking of the larger audience from new Tiller and spreadsheet users to experienced users. I’m not familiar with python, so I can’t speak to using that vs an Excel add-in.

If the only feedback you are receiving is from me on this subject, then I would hold off on doing any work on the tool. I very much appreciate your effort to provide a bridge from Tiller to your CashFlow and Networth Analysis Workbook.

As background:
I’m experienced with using the basic and some intermediate features of Excel. I started out as a new Tiller user with the Tiller Excel template since I was not familiar the Google Sheets. After getting up and running and comfortable with the Tiller on Excel, I took the time to setup the Tiller Sheets template as a learning exercise. I followed a workflow that provided most of the steps, but missed a few sub-steps. I was able to figure it out and get the environment working. I added-in some Tiller and community solutions that didn’t exist on the Excel side. So, I had two templates to sync, similar to what you have setup (or one if I were to use just your template). I ultimately decided to just use the Excel template for my first year, even though there are some Sheets add-ins I really like and could use. My hope is that the Excel environment will catch up with the Sheets environment.

Thanks,

Clint

[buzzmaster1]

I just downloaded it and will try to set it up. I share the concerns about updating 2 files and also that this runs only on PC and not on the web. But let me get it running and come back with feedback.

[buzzmaster1]

Right off the bat, I see that the transactions sheet is rather different in column location and does not have all of the Tiller columns (in addition to my custom columns) How should I handle that?

[jackmansean64]

Thanks so much for the feedback!

@buzzmaster1

I share the concerns about updating 2 files

Just to make sure I’m understanding correctly, you aren’t a fan of merging your existing workbook with mine nor are you a fan of maintaining two workbook simultaneously?

@buzzmaster1

this runs only on PC and not on the web

Do you mean via Google Sheets or via Excel Online? Unfortunately, my solution is tied very closely to advanced Excel functionality so Google Sheets is a non-starter. However, having it work with Excel Online may be possible, I just haven’t tested it. If there is demand for Excel Online support I could certainly look into it!

@buzzmaster1

the transactions sheet is rather different in column location and does not have all of the Tiller columns (in addition to my custom columns) How should I handle that?

Which Tiller columns are you referring to? I just verified that my transaction sheet has all of the same columns as the foundation template transactions sheet and in the same order. The only difference should be that my sheet adds two custom columns ‘Labels’ and ‘Notes’.

Your custom transaction sheet may very well differ from the official Tiller transaction sheet (and by extension my transaction sheet) if you’ve been making changes to it. In terms of how to handle this, that would be via one of the two solutions I mentioned above:

1. Merge your existing Tiller workbook with mine. This would require copying your existing data and customizations from your current workbook over to mine. Depending on how customized your workbook is this may be straightforward or it may be difficult if you can’t remember all the changes you’ve made or if they aren’t nicely encapsulated in their own sheets.
2. Maintain your existing workbook alongside my workbook. This would require copying your existing data over to my workbook, and then using Tiller to fill both workbooks going forward.

As Clint and I discussed above, if copying data between workbooks is the main hurdle I could provide an app to handle that. You would just need to provide the file paths of the two workbooks and it would copy all the foundation sheet data from one to the other. @Clint.C as I was typing this out I realized that my assumption that you could sync two workbooks automatically with Tiller only works for transactions and balance history, it doesn’t sync categories and accounts since those are workbook specific. So, a refresh tool might be more helpful for the two workbook arrangement than I originally thought…

[buzzmaster1]

So I am hesitant to merge the 2 since it may become too cumbersome.

Also, my transaction sheet differs. I have added custom columns (Tags, Notes, Subcategory) and the positions of my columns differ from yours. My transaction sheet also has (Reconcile Date, Categorized Date, Categorized Date 2, Categorized Date 3) which I did not add so I assumed were data used/created by Tiller.

I am only on Excel, not Sheets. I was referring to the online version of excel. I travel frequently with just an iPad Pro which I currently use to update the sheet via the online version of Excel. I would not need to update your sheet as frequently but if I were to merge the 2 it could be an issue.

[jackmansean64]

@buzzmaster1

My transaction sheet also has (Reconcile Date, Categorized Date, Categorized Date 2, Categorized Date 3) which I did not add so I assumed were data used/created by Tiller.

Ah okay interesting. I haven’t seen these columns before and as far as I can tell they aren’t part of the foundation template. Maybe they were brought in as part of one of the official add-on templates or another community solution?

@buzzmaster1

So I am hesitant to merge the 2 since it may become too cumbersome.

It certainly could if you have a large or complicated workbook already.

Overall, I’m not quite sure how I can help you. It sounds like you might have a complex, highly customized Tiller workbook already, so expanding into additional complex solutions like mine without losing any existing functionality is not going to be easy. Such is the nature of software.

If you’re just looking to take my workbook for a test run, trying it out separately from your current workbook seems like the best option. At that point, if you really like it and want to adopt it, then apart from maintaining multiple workbooks (which doesn’t seem like it would be that bad to me) or merging workbooks and attempting to manage the complexity, the only other solution that I can think of would be to scrutinize your current customizations to consider if they’re all providing value. Maybe you can simplify your workbook to the point where copying a few sheets into my workbook becomes feasible.

[buzzmaster1]

Ok, so the transactions format needs to look like yours in both column names and orders? I can attempt to create a program to import my data over, as a starting point.

KH

[jackmansean64]

@buzzmaster1

so the transactions format needs to look like yours in both column names and orders?

When you copy your transaction data into my workbook you just need to ensure that you copy the data from the correct source column to the correct destination column, and that the data is formatted in the same way. The ordering of the columns shouldn’t matter, as all references should be via table column names and not absolute cell references (if you do find that the order matters, please let me know as it’s likely a bug!) See my documentation for more information.

@buzzmaster1

I can attempt to create a program to import my data over, as a starting point.

I still don’t quite understand why copy-pasting wouldn’t be the easiest option here. All you need to do is select the relevant data from your current workbook (you can use ctrl+shift+down and ctrl+shift+right to select all the data in a column/row), and then copy-paste that into the new workbook. If the columns are out of order you can copy paste individual columns or groups of columns by clicking the column header letter to select the column and then right-click, copy, then paste into the new workbook:

Apologies if you know all this already, but that’s how I do it and it only takes me a minute to copy all my foundation data from one workbook to another (I had to do this many times when I was developing this workbook template). This only needs to happen once for your transactions and balance history, and then only periodically for your accounts and categories if you make changes.

[buzzmaster1]

Okay, I will try copy and paste to start.
I was starting with the account column and see that too has rather different columns than my sheet. So to be clear I need to copy one column at a time for every sheet? I can do that one time for the initial setup but definitely would want to have an “Import Program” to do this on an ongoing basis.

[jackmansean64]

Interesting… my accounts sheet is identical to the foundation template so something must have modified yours. You shouldn’t need to copy EVERY column individually unless every single column is next to a different column than mine. You can copy groups that are in the same order right? Like if my columns are ABCDEF and yours are ABCEDF then you can copy ABC together and then E,D,F separately.

You would only need to copy the accounts and categories sheet going forward right? Tiller can automatically sync your transactions and balance history across multiple workbooks. Regardless, as I mentioned above a program would be nice for those sheets. I’ve already written something to do that so if you want it let me know and I’ll publish it here.

[buzzmaster1]

Yes, please publish it!! Thanks for your help. While Tiller can update across multiple workbooks, that is not a viable solution since I need to do additional manual work after each Tiller update to clean descriptions and categories.

[buzzmaster1]

So in accounts, I did see that we had the same columns, so I tried doing a copy but got an error message. For your reference, I will include the error message here and also a comparison of our columns.

[jackmansean64]

You’re totally right, it appears that copying the entire column like I showed in the screenshot doesn’t work with these sheets for some reason. My bad! You just need to copy the cells instead. So, rather than clicking on the column header just select the specific cells you need. You can click and drag to highlight the cells, or click the first cell in the column and press ctrl+shift+down to highlight the rest of the data in that column and copy it. Then, you should be able to paste the data into the new workbook by right clicking on the first cell in the column and pasting.

[buzzmaster1]

I manually copied everything over this morning. On my initial look, it seems to have worked. In the future, it would not be a viable method of updating. I would definitely need to create an automated tool. I will spend some time looking at the sheet and revert back with thoughts and questions. Thanks again for your help.