iV  %\kVbQDQsmATChLE7Te%L4 1 5L AeQzt; > 9 _~\!<F7mZ}D~^Rj{1<<SZQa]<_O=,NK U^M u\[D,RGI<\g)OZ ;B'!GbYJ%zDVeGl A5g81|L^Az ^XVFak=@dkGtNZG,8A*G՟; " OWC ^~Qo6A;Πx;elnL.C'O;)I[AT; #KiY% }vn~E BbOZY@8>RXUT 0gX\V E,#V: 270 [Qd]\'=5 reC@YFyz+#>G@,FUGU Q5ZG!; .<#9>: }_+VFI3pKOEl-JYTG' ^F ߫sB ; YoO9GRL3Q^LWU)^vb6HM8OF,bg gdTju+8OF;ESPwsg:7#h\gZRA?1 w  M% !y{}Z=X X XrEE6!_VGoKR2]C*6: |E ^WM.wT!VALaKB  Nj<E{3,,!!P`aOLMDž@( OFwOWO S'B#ao 4 BZ+D+:G AK 3][R6 gSP_JW /Q';^$) I,T@HS @}3j^A_ Q\t&oNOMT) R| MY Y6 \g\W3&.UB IR*zynpMER(Vh_SU2JS2NqTva8 害) 8HTI2 KP3Ls]  ġ=\ NG d15\[o/]@O/KLW4F CLAH֬1^CBKC<_j%ng$ l2UEyaCV#<$TEj^uIs"SfVM*+6z0I_C0KF]xKd[sDCMy[JI$-\J6w[5m% 1Q>WQ-V42HO^=E,fA}c12HCN09Zddt6cPDZ ],FK ,äv\ [ B6Ͷ6 CFu=Q{%`F \5jK, ikM[K . >NIT4> iABS%D +/P==M_B^$f;/IQWjJem_*H+:4 +{jg@WX1'x=>2[^:GQ]ȿZyi-,CJN`˱} \  9=Ap=NZB JJsHISJ%  @XBo1 ^] sBu9+  _#CJKF@YfLM;M]2.,B~u@JLA(~PMs;P]N;? \ @/TRF7i]ji[JHo&to[ZZ1UX: %sI[BnG8V/y[eN G ^cHEdJ#O2&m I{ERN+_P3IE\Ua)& ]2wz~%"XLO mP.^ <^ARpR9:&tY] kKPh=nt  SbZM.AL> `FU ږM"& 0 r%("H8CHO9 (Sa;3pfVA9 BJg. ?P;-$c$tbϪ3G]DQ^V^G^_P\.SI@@4== "[]P%QRZGGTdV (71pBFH; p*WPL smWPSFt.DvT]%4=YD9R[ofF{ LK["PKN!A dG I]|pH < lVw >cTVH1,6X#d]acnQQ6'EWEDaErU%^5.D8 kUAYo|$)/7YCTYxA@rl tV(j}G{PiB6Y.lW].I@ێ'WgQW9%"HCL=BFJ mA H\ 8{EP41P /9\KrS\H`0K{ IP a>1WrrRUK YHUjIWJZ@ZSzDFy8E5mY&f[_^ YX?w(g91CK1B \[3'B$G(A &PSL<OmLG@vUO?sB?qL.5Ix%q]V$HsGTW  o9hPV F{TNFZRwNeulqakG$A  ZPDOXH)qJAWc8fvRN9YOLA * $IC 1lQ42 YLWR@dsAmXTUS\"\YHZDO{5BD?:^,IVUF ujUk5&h$3 9  7JGC8oVeLKRQ93PCUH`UGGHY0YE&:X! N3B Ř$ PD]O}QP-WUjJK\/FR7QZ_b][ rDsNnes=wdtjOWRJ= ZE4W:3HCEPJ\^1S;1NvX+63GGJ]FsScDC}?BABe^SY=KGD]\Ul">]XTMfE@4MB[,3\!C C @a[t:C`>Gv[ HXaF4WFDY.5Zi5"ZѾzZNEUokN75WECqStp RQ;q;N N] 'K -Gn &=WI^O'C-QGm$!.IA=isv X&0T2c]DYrDUSyVIc`DXgb7e //~a[AUrWAFlV?DM+Q|{i2YDVRHykIQSUBK#;CAϙoOPQV !/\CLs"^F L,`@ nI%4<_#EW\ F]m8SVE{HJHG$GeX`%f.d\cb(V"zN-qSX B8 |\F\LyS= ZWGSLPF9`F|WtB[^ont1VbrJNU RgsgwaroL!1AE?=,$Kg% D{F0M=2|P\&AA!BIG#F/A)s/nav_images/k21contact.gif';" onmouseout="image9.src='images/nav_images/kcontact.gif';">  
   

April 2008
 

 

Home Financial Records

By Richard Kennon, Editor, Amador Computer Users Group, CA
www.acug.org  editor(at)acug.net

Obtained from APCUG with the author's permission for publication by APCUG member groups.

Dick Johnson gave us an excellent presentation on Personal Finances with emphasis on record keeping and check writing. I, like Dick, have used QuickBooks to write my checks and keep my financial records. Recently, a friend chided me for not using BillPay at my bank. I had looked into this a long time ago and determined that my bank charged a little more than it cost to write a check and mail it so no good. After my friend prodded me to join the 21st century, I checked with my bank, again, only to discover that if they changed the name of my checking account, BillPay would be free. Done. So, I write many fewer checks than before. Perhaps I can write them by hand! (Gasp) That is reason #1 for this effort.

Reason #2 is that the average household (at least mine) does not need the sophistication of an accounting program to keep household records. About the only thing we need besides a check record (i.e. stubs) is a way to collect some information for the IRS each year. Twenty or twenty five years ago, I wrote such a program in Basic and used it for most of those intervening years. But, Bill Gates decided that he would no longer let me run Basic programs using his latest operating system. That program also printed the checks on an old Epson 24-pin printer that served wonderfully for at least twenty years. That is when I turned to QuickBooks.

These two intersecting forces of minimal accounting and lowered demand for check writing led me to wonder if, just maybe, an Excel spread sheet might do the job. Yes, I think it will. This thinking has resulted in a spread sheet that looks suspiciously like a check record in your check book. There are four added columns and they are Account, Memo, Cleared and Reconciled. Maybe account is too grand a term. What I mean it to be is a code name that may apply to a number of different checks written to a number of different payees. This might be like lumping the electric, gas, water and phone bills under Utilities. Or, lumping Red Cross, American Cancer Society and Yosemite Fund under Contributions. But, then, one might write enough checks to one organization (i.e. Church at The Cross) to make that a separate Account like Church. The Cleared and Reconciled columns are useful when it comes time to see if you and the bank agree. This is such an anxious moment that I use smiley faces to indicate it is done. Here is an example of what the spread sheet looks like. It is assuredly a fictitious account because I dont think I know anyone with this much money.




I had to scrunch it together to print it here. It looks better on the Excel spreadsheet where I set it up to print in Landscape orientation so there is plenty of room for about 48 lines per page. There are lots more accounts you will want to use such as Taxes, Medical, Insurance, House Payments, etc. I am a terrible organizer so I am sure you can do a better job of this than I can. Think mostly of what you need for outside demands like the IRS. Dont think in terms of detailed budgets because, admit it, you are not going to follow through with that. It seems as if I should be much more organized than I really am when I use QuickBooks or other accounting program. That is why I have a big Miscellaneous group in our personal records. That is none of the IRSs business.

One nice thing about Excel is that when you start to type something you have typed above in the same column, Excel finishes the entry for you. So, we two-fingered typists can create entries more quickly later in the year! I have anticipated that you and I will make less than 400 entries per year so have activated 400 lines on each sheet. Of course, the spreadsheet does the calculations automatically. The sheet is locked except for the columns where entries are required.

Now, how do we find out how much we gave to charity for the IRS? On the spreadsheet there is a little down arrow by each of the column headings that can be searched. Like this.




I have clicked on the Account column arrow and a choice box has dropped down. I have selected Miscellaneous and once I did that, this display popped up.



Do that for any other account name or payee name and you will have the information you need for preparing your income tax. You can print this sub-list in the usual way. Now, to get out of this, click the arrow in the same column and then click on (All). The original full list will show up. You can do that operation in either the Payee or Account column and you will have all you need to find out how much money you have spent in any given category. For some fun, you can click on the arrows in the Check Number column and search for a check number or all the checks over some number or under a certain number. You can do the same in the Date column. This may come in handy when the sheet grows large late in the year and you want to find an entry made on or before or after a certain date.




I did that here on the Check # column. Then I selected (Custom). That sounds a little scary, doesnt it? Its not. The next thing to pop up is:



You can choose is greater than or any other description of what you want to find. Then, on the right you type in the amount, say 500. Click OK and all the checks with numbers greater than 500 will pop up. Again, close this out by clicking the arrow and then (All).

A word of caution be consistent. That is, do not call the electric company PG&E one time and Pacific Gas & Electric another time. Do not call an account Charity one time and Contributions another time. Keep it simple. Life would be simpler if we did not have to pay so many bills and it is also simpler if you use fewer accounts. Miscellaneous is a wonderful account.

Printing may also present a challenge. If you click View>Page Break Preview, you will see that 400 lines cover eight pages. If you just click File>Print>OK, you will get the pages you want plus all the blank pages up to eight. So, In order to get just what you want, click on View>Page Break Preview first to see what pages you want. Then you can select just those pages in the print window.

I will be glad to share this spreadsheet with anyone who wants to look at it more closely with the usual disclaimers about it is not my fault if you go bankrupt and so forth. You can copy the last blank sheet as many times as you wish so the workbook can last forever.

If you are interested in constructing your own spreadsheet, here are some particulars that might be of interest. What I call the search option is accessed by clicking on Data> Filter> AutoFilter. If you do this while in the Payee heading, it will put the arrows and the function in all the contiguous headings to the right. I put a blank column after the Memo heading so it would stop there. The formula in the Balance column below the Beginning Balance is (are you ready for this?). =IF(OR(E9<>"", F9<>""), H8-E9+F9, ""). This is not so complicated. The classic IF statement says something like this: IF(something is TRUE, then do this, else do that). Now, the something is true part says if cell E9 is not equal (<>) to blank () or cell F9 is not equal (<>) to blank (). That is, if either of the two cells under the Amount heading or under the Deposit heading is not blank then do some arithmetic. That arithmetic is subtract the Amount of the check and add the amount of the Deposit to the previous Balance, H8-E9+F9, in line number 9. The else do that is leave this cell blank (). That is why all the cells below the active row are blank but will light up with a balance as soon as an entry is made. Just click on the + in the lower right corner and drag this formula down 400 rows. Isnt this slick?

With BillPay in full swing, I find that I write many fewer checks but I still have to keep a record, dont I? The bank record is handy on the web but it only shows those charges that have cleared. I want to know whether I am solvent if they all clear. So I need a record. My accounting needs are mostly limited to keeping a ready record for tax purposes. Budgeting is not my thing. For these reasons a check record with the addition of an Accounts and a Memo column is satisfactory. The frosting on the cake is the added feature of being able to isolate various accounts and payees for whatever reason. While I may have made the transition to the 21st Century, I have also simplified my life somewhat by not being dependent on Intuit to write my checks for me and demand that I account for every last penny. How often is it that computers simplify your life?

This article may be used in any non-profit publication if credit is given the author and the Amador Computer Users Group of Jackson, California.

This article has been provided to APCUG by the author solely for publication by APCUG member groups. All other uses require the permission of the author (see e-mail address above).
This article has been provided personally by the author solely for publication by APCUG member groups. All other uses require the permission of the author (see e-mail address above).

Join KIPCUG Announcements

Know what's happening every month

Name:
E-Mail:

 


 

 


 

 


 

 
 

Click here to view more articles that may interest you.

  
  Top of Page  
This site generously
provided by
Win.Net
Website Comments:
Brian Miller or John Henderson
 apcug web site winner