In an earlier post, you can read here, I wrote about how our family took control of our finances and, in just a few short years, nearly assured our retirement (after all, nothing about retirement is 100% certain). It took no little work to accomplish, like any worthy task. Along our journey we developed what we believe is a fairly comprehensive Excel® workbook to manage all of our financial accounts. If you are interested in using it, you can download it here.
Because it took me so long to finalize the formula in the DASH, I threw some hasty instructions together but it’s as simple to use as it is to balance your checkbook. I will work on more detailed instructions for future blog posts so stay tuned if you need more help. These instructions assume you have a working knowledge of Excel®.
The workbook is composed of four basic worksheets (two of which are used repeatedly, but four basic worksheets nonetheless).
I’ll bid you adieu here, but read on to learn how the workbook functions.
Please feel free to email me, contact me on social media, or comment on this post if you have any questions!
The Mighty Dashboard
The first worksheet titled DASH (short for DASHBOARD) provides a summary of all expenses by month. It is organized by INCOME, DEDUCTIONS, SAVINGS, RETIREMENT, and EXPENSES with subtotals and an end of month balance to show you if you over- or under- spent your earnings for the month. Save for your income data, the DASH is automatic. When you balance your accounts, all your transactions are summed into the respective category under the respective month. You will only need to enter your paycheck information manually which is entered under INCOME, DEDUCTIONS and RETIREMENT. The sections where you are required to enter data are highlighted blue. The remainder of the rows are controlled by drop down lists in column A that correspond directly to the CATEGORIES we will cover later. This maintains the integrity of the formula if you re-arrange, add, or remove any rows.
Check Registers with a Powerful Feature
The ACCT_# (where # is 1 through 7) worksheets are automatic account ledgers or registers that you can use for any cash account including checking, savings, and credit cards. [If you change the names of the ACCT_# worksheets, you will need to update them in the IND worksheet. See below.] You must use the drop down CATEGORIES to automatically populate the DASH. If you record a transaction between your accounts, be intentional about the CATEGORIES or the transaction will populate in the DASH twice. I usually avoid this by using the OWN-OWNED ASSET drop down selection for the account to which the money is added.
Liquid, Retirement, and Debt Subtotals
The three ABC_SUB sheets summarize the subtotals of the three categories of monetary assets: LIQ (liquid), RET (retirement), DEBT (loans). They are similar to the worksheet below, save that they do not include the assets nor a yearly summary. You must manually enter your data into the ABC_SUB worksheets but your entries will populate into the TOTAL worksheet which is displayed below. To calculate your total NET WORTH in the TOTAL worksheet, you will need to enter the estimated value of your assets. Precision isn’t important here – a ballpark figure will do. The ABC_SUB and TOTAL worksheets track changes from month to month, as well as the total annual change.
The final IND worksheet is what I call the index. However, INDEX is an actual formula so I caution you about changing this worksheet’s name as it could create a problem in the formulas. The IND is where you may make changes to the DESCRIPTION, CATEGORIES (if you so desire), and record changes to the ACCT_# worksheets. You may also change which worksheets you wish to populate the DASH by adjusting the TABLE in column C. [For instance, we have worksheets for our children’s savings accounts, but we do not want the children’s account information collated in the DASH so those worksheets are not included in the table in column C.]