Forum Discussion
Bank Reconciliations
https://1drv.ms/x/c/b2bb91cdde8a04e3/EZbkiD-dMXZLi4zuPta1jagBTeoj-ucSi9hOtf7KTddDDw?e=I1ggBT
Hello Mathetes,
Thanks for your reply. I have not heard of this before your reply. It will require some study on my part.
I have attached a link to what I have created to date, you may care to comment.
I am trying to create a Cashflow Calculator that a novice can use without too much preliminary knowledge on their part. All I want them to do is add a Row if necessary and enter the cell values as required from their monthly bank statement or transaction list.
The idea is to give the user a bird's-eye view over a period of 12 months of the flows of money - income and expenses through their bank account. It gives the user a month to month perspective as not all months are the same. The historical aspect allows the user to see how they are managing cashflow and where they can improve. Are they trending with an increasing or reducing bank balance. The 12 month view always reminds them of where they started and where they are from month to month.
Any comments?
My response is still much the same as before. All you are using Excel for is the simple stuff.
The user--those novices you're ostensibly creating this for--will need to do the more complicated (still fairly simple, I recognize) bit of adding all individual expenses in each month for, say, :Food"; same for "Entertainment" or any other expense categories like clothing, laundry, gas -- i.e., MOST daily expenses. It would be fairly easy to get from a credit card or check register the once/per/month rent check, or electric bill or water bill. But the reality is that there are a lot of daily transactions that also need to be tracked, and your approach is asking the user to do the hard work of summarizing in those more daily expense categories into a monthly total.
The Pivot Table (as in the still very simple example I gave you) will take a detailed database of daily transactions, and summarize them by month by category. That is to say, it does all the hard work of summarizing.
Use Excel to do the heavy lifting.
For what it's worth, to fill in the detailed transactions in my personal workbook that I alluded to in my first message, I just download each credit card's monthly statement (which is detailed) and my bank account's monthly statement. I do need to add the budget categories to each line, and sometimes the credit card statement helps by separating out "Gas" or "Restaurant" ...but they always include payee--which is the main thing I rely on to categorize each line.
So, yes, it still involves work, but it's work in categorizing; NOT work (summing and tabulating) that Excel can do far more quickly and reliably. Your approach is asking novices to do almost all of both types of work.
Study the Pivot Table. Play with it on your own. It will change your life.