【財務預算管理?】了解自己的小金庫money-manager

Vertex42?MoneyManager,,,,

INSTRUCTIONS,,?2010-2017Vertex42LLC,,

,ForExcel2010orLater,HELP,,>>ExcelTips

Introduction,,,,

,TheVertex42?MoneyManagercanbeaverysimplemoneymanagementtool.Likemost,,,

,"spreadsheetapplications,youshouldknowthatspreadsheetsareerror-prone.Itiseasyto",,,

,"makemistakes,accidentallydeletethingsthatshouldnotbedeleted,forgettocopyformulas,",,,

,"etc.IfyouarecomfortableusingExcel,knowhowtoidentifyandfixformulaswhenneeded,",,,

,"understandhowtousebasicrowoperations(delete,copy,insertedcopiedrows,etc.),are",,,

,"okaywiththelevelofriskyoutakeonbyusingaspreadsheet,andfollowtheinstructions",,,

,"andguidelines,youshouldfindthisspreadsheetveryuseful.",,,

,,,,,

,GeneralTips,,,

,"-Exceptforcustomizingthecategories,younormallyonlyeditcellswithagrayborder:",InputCell,,

,-Someofthelabelsincludecellcomments(markedwithlittleredtriangles)toprovide,,,

,extrahelpinformation.,Label,,

,"-Youcanaddyourowncellcomments!ThisisespeciallyusefulintheBudgetworksheet,",,,

,whereyoucancreatenotestoexplainirregularorvariableexpenses.,,,

,"-Ifyousee""#####""inacell,widentheCOLUMNtodisplaythecellcontents.",,,

,"-Thisworkbookusesalotofconditionalformatting.Lookup""conditionalformatting""inthe",,,

,Excelhelpsystem(F1)ifyouwanttoknowhowitworks.,,,

,"-Youcanaddalimitedamountofsecuritybypasswordprotectingyourworkbook,butthat",,,

,canbeeasilybypassedbyanyonewithmaliciousintent.Thesecurityofyourdataisyour,,,

,responsibility.,,,

,-Backupyourfileregularlytoavoidlosingdata!ExcelfilesDOgetcorruptedoccasionally.,,,

,,,,,

Step1,CustomizeCategories(ordon't),,,

,Theeasiestwaytogetstartedistojustusethedefaultsetofbudgetcategories.,,,

,"Ifyouneedtomakechangestothecategories,makesurethatyoumakecorresponding",,,

,changesinALLtheotherworksheets.,,,

,"TIP:Ifyouaregoingtocustomizethecategories,itmaybeeasiertostartfromtheBudget",,,

,worksheetandthenmakesurethattheReportandCategoriesworksheetscorrespond.,,,

,"A.IfyouADDorDELETECategories,makesurethatyouuserowoperations(copyingor",,,

,deletingentirerows)tohelpensurethatallformulasarecopiedcorrectly.Alwaysinsert,,,

,newrowsbetweenthefirstandlastiteminthecategory.Ifyouinsertarowimmediately,,,

,"abovethe""Total""or""Subtotal""row,formulaswillnotstretchtoincludetherowyouinserted.",,,

,B.Categorynamesinalltheworksheetsmustmatchexactly.Conditionalformattingisused,,,

,tohighlightthecategorynamesifthecategoriesarenotfoundintheCategoriesworksheet.,,,

,C.NoDuplicateCategoryNames,,,

,"Usingduplicatecategorynameslike""Other""willresultinerrors,butyoumaynoteasily",,,

,noticetheerrors.MakesurethateachcategoryisUNIQUE.,,,

,D.DoNotRemoveorAddMajorCategories,,,

,"Ifyouaddorremovemajorcategories(likeHousing,Food,Health,etc.),manyformulas",,,

,willneedtobeupdatedanditisverylikelyyou'llmisssomethingandendupintroducing,,,

,"errors.TrytousethedefaultsetofcategoriesorRENAMEthecategories,butavoidadding",,,

,orremovingthemajorcategories.,,,

,,,,,

Step2,DefineYourListofAccounts,,,

,EditthelistofaccountsthatyouwanttoincludeintheTransactionHistoryworksheetby,,,

,editingtheAccountsworksheet.ThelistwillpopulatethedropdownboxintheAccounts,,,

,columnoftheTransactionsworksheet.,,,

,,,,,

,"TIP:Ifyoustarteachaccountnamewithadifferentletter,Excel'sautocompletefeature",,,

,willmakeenteringtheaccountmuchfasterforyou.,,,

,,,,,

,Goals:TheaccountlistincludesaplacetolistaGoalandthe%columnshowsyourprogress,,,

,towardsthatgoal.Thismaybeusefulforlistingsavingsfundgoalsorcheckreserveamounts.,,,

,,,,,

Step3,DefineYourYearlyBudget,,,

,DefineyourbudgetusingtheBudgetworksheet.Editonlythecellswiththegrayoutline.,HowtoBudget:BudgetingTips,,

,SeeVortipsonbudgeting.,Vertex42Blog:Budgeting,,

,,,,,

,"-ForaFinancialYearthatdoesnotstartinJanuary:Beforeenteringyouryearlybudget,",,,

,"gototheReportworksheetandenterthe""YearBegins""date.Thiswillupdatethemonth",,,

,labelsintheBudgetworksheet.,,,

,"-YoucancopyandpastetheinputcellswithintheBudgetworksheetasneeded.Forexample,",,,

,"enteranaveragefuelcostinJanuary,thencopythevaluetoothermonths.",,,

,"-Useformulastodobasiccalculationslike""=245/6""todivide245by6or""=34*2""tomultipy",,,

,"34by2,or""=34+12+45""toaddabunchofnumbers.Formulasareenteredusingthe",,,

,"equals""=""sign.",,,

,,,,,

Step4,RecordTransactions,,,

,TheTransactionsworksheetletsyourecordtransactionsformultipleaccounts.Thinkofa,,,

,PAYMENTasmoneyleavingtheaccountandaDEPOSITasmoneyenteringtheaccount.,,,

,Examplesofdifferenttypesoftransactionsaregivenbelow.,,,

,,,,,

,"IMPORTANT:Youmustcopyandpasteentirerowswhenaddingnewrows,toensurethatall",,,

,"theformatting,datavalidation,andformulasgetcopiedcorrectly.",,,

,THEMOSTCOMMONERRORisinsertinganewrowandforgettingtocopyformulasdown.,,,

,"Whenyouinsertablankrow,someformattingiscopiedfromtherowaboveitautomatically,",,,

,butformulasanddatavalidationarenotcopied.YoucanpressCTRL+dafterinsertinganew,,,

,rowtoquicklycopyallformulasandformattingfromtherowimmediatelyabove.,,,

,,,,,

,Usethescreenshotbelowasaguideforhowtoentertransactions.,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,"Date:Toquicklyenterthecurrentdate,usethekeyboardshortcutCTRL+;",2017/5/16,,

,"Thelistofdatestotherightisusedtopopulatethedrop-downbox.Forotherdates,",2017/5/15,,

,"youwillneedtoenterthedatemanually.Foradateinthecurrentyear,youcanusethe",2017/5/14,,

,shortcutofjustenteringthemonth/daylike5/16.,2017/5/13,,

,,2017/5/12,,

,,2017/5/11,,

,,2017/5/10,,

,,2017/5/9,,

,,,,,

,"Num:Thiscolumnisusuallyusedtolistthechecknumber,butyoucanalsouseittoenter",,,

,"""DEP""fordeposit,""TXFR""fortransfer,""EFT""forelectronicfundstransfer,""ACH""for",,,

,"AutomatedClearingHousetransactions,etc.",,,

,,,,,

,Category:TheCategoryfieldisessentialtothefunctionalityofthisworkbook.TheCategory,,,

,isadropdownlistthatgetsitsinfofromtheCategoriesworksheet.,,,

,,,,,

,TheCategorycolumnusesconditionalformattingtohighlightthecategoryPINKifthe,[Transfer],:NormalbackgroundcolortoidentifyTransfers,

,"categoryisnotfoundintheCategoriesworksheet,BROWNifthecategoryisnotfoundin",[Balance],:NormalbackgroundcolortoidentifyBeginningBalance,

,"theYearlyorMonthlyworksheet,andGRAYifthecategoryisblank,[Transfer],or[Balance].",BlahBlah,:CategorynotfoundintheCategoriesworksheet,

,"Iftheformattingisn'tcopiedcorrectly,you'lllosethisdouble-checkfeature.Soagain,this",BlahBlah,:CategorynotfoundintheBudgetorReportworksheets,

,spreadsheetisnoterror-proof.,,,

,,,,,

,"IMPORTANT:Ifyouhavechosenacategorysuchas""Ted'sFund""andthenlaterremove",,,

,"""Ted'sFund""fromthelistintheCategoriesworksheet,itwillnotbechangedintheTransactions",,,

,tableautomatically.Youwillneedtomakesurethatyoufindalltherecordsthathaveused,,,

,"""Ted'sFund""astheCategoryandchangethemtosomethingelse.",,,

,,,,,

,Step4-0:DeletetheSampleRows,,,

,,,,,

,Step4-1:Enterthebeginningbalance(s):,,,

,TheBeginningBalanceonJANUARY1stforeachaccountshouldbethefirstlinesinthe,,,

,"register.IfanaccounthasaNegativebalance(likeaCreditCard),enterthebalanceasa",,,

,"PositivevalueinthePAYMENTcolumn.IfanaccounthasaPositivebalance,enterthebalance",,,

,asaPositivevalueintheDEPOSITcolumn.UsetheAccountBalancecolumntocompareto,,,

,thebalanceshownonyourbankorcreditcardstatements.,,,

,,,,,

,Step4-2:AddNewTransactions:,,,

,Youwillprobablyfindthatthefastestwaytoaddnewtransactionsiscopyandpastesimilar,,,

,"previoustransactions.Youcanselectoneormorerows,copythem,andthenpastethem",,,

,"belowthelasttransaction.Then,youjustneedtoeditthecellsthatneedtobechanged.",,,

,,,,,

,TIP:AlwaysleavethelastrowinthetableBLANKsothatyoucaneasilyaddnewrows.To,,,

,"addnewrows,selectthelastrowofthetable(row57intheimagebelow)anddragthefill",,,

,handledowntocopytherowdowntocreateasmanynewrowsasyouneed.,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,RecordingaSPLITTransaction,,,

,"Ifasingletransactionneedstobeallocatedtomultiplebudgetcategories,youneedtocreate",,,

,aSPLITtransaction.Youcandothisbysplittingthetransactionintomultipletransactions-,,,

,"oneforeachcategory.YoucanusetheMEMOfieldtoindicatethatthetransactionisa""Split"".",,,

,,,,,

,ACCOUNTDATENUMPAYEEMEMOCATEGORYPAYMENT,,,

,Checking1/1/102032TargetSplitClothing23.10,,,

,Checking1/1/102032TargetSplitGroceries45.15,,,

,Checking1/1/102032TargetSplitSupplies25.04,,,

,,,,,

,"Tip:Ifyouwanttoverifythetotalamountofthesplittransaction,youcandoaquick",,,

,"calculationofftothesideofthetableusinganExcelformula,like""=SUM(H13:H15)""",,,

,,,,,

,Recordinga[Transfer]BetweenSpendingAccounts,,,

,"Recordatransferbylistingtwotransactions(onePAYMENT,oneDEPOSIT)tooffseteachother.",,,

,"Choose""[Transfer]""astheCategoryforbothtransactions.Forexample,a$250creditcard",,,

,paymentwouldberecordedasatransferFROMyourcheckingTOyourcreditcardaccount:,,,

,,,,,

,ACCOUNTDATENUMPAYEECATEGORYPAYMENTDEPOSIT,,,

,CreditCard1/1/10TXFR[FromChecking][Transfer]150.00,,,

,Checking1/1/10TXFR[ToCreditCard][Transfer]150.00,,,

,,,,,

,"NOTE:Theaboveexampleassumesthatthe""payment""toyourCreditCardistopayoffthe",,,

,chargesthatyouhavealreadyrecordedearlierintheTransactionHistorytableforthe,,,

,CreditCardaccount.IfyouareNOTrecordingindividualCreditCardtransactionsusingthe,,,

,"Transactionsworksheet,orpartofthe$150.00wastopaydownanoutstandingdebt,thena",,,

,"creditcardpaymentwouldlooklikeoneofthefollowing,where""CreditCard#1""isacategory",,,

,underObligations.,,,

,,,,,

,"Example1:NotusingCreditCard#1anymore,butstillowemoneyonit.",,,

,ACCOUNTDATENUMPAYEECATEGORYPAYMENT,,,

,Checking1/1/10CreditCardCreditCard#1150.00,,,

,,,,,

,Example2:Aportionofacreditcardpaymentusedtopaydown$25.00ofoutstandingdebt.,,,

,ACCOUNTDATENUMPAYEEMEMOCATEGORYPAYMENTDEPOSIT,,,

,CreditCard1/1/10TXFR[FromChecking][Transfer]150.00,,,

,Checking1/1/10TXFR[ToCreditCard]Split[Transfer]125.00,,,

,Checking1/1/10TXFR[ToCreditCard]SplitCreditCard#125.00,,,

,,,,,

,Recordinga[Transfer]ToSAVINGS,,,

,"Whenbudgeting,youtreatatransfertoSavingsasanexpense.You'llnoticeintheBudget",,,

,worksheetthattherearemultiplesavingsgoalslistedassub-categoriesunderthemain,,,

,"""ToSavings""category.InsteadofjustthrowingmoneyintoSavings,youshouldallocatethe",,,

,"moneytospecificsavingsgoalsbasedonpercentages.Forexample,50%toyourEmergency",,,

,"Fund,25%toRetirement,etc.YoudothisbyrecordingthetransferasaSPLITtransaction:",,,

,"Intheexamplebelow,theCATEGORYfortheSavingsaccounttransactionis""[Transfer]""",,,

,whiletheCATEGORYfortheCheckingaccounttransaction(s)arebudgetsub-categories.,,,

,,,,,

,ACCOUNTDATENUMPAYEECATEGORYPAYMENTDEPOSIT,,,

,Savings1/1/10TXFR[FromChecking][Transfer]200.00,,,

,Checking1/1/10TXFR[ToSavings]EmergencyFund100.00,,,

,Checking1/1/10TXFR[ToSavings]Retirement50.00,,,

,Checking1/1/10TXFR[ToSavings]CollegeFund50.00,,,

,,,,,

,RecordingaReturnorRefund,,,

,Averycommonapproachforrecordingreturnsorrefundsistorecordthemasanegative,,,

,"expense.Asanexample,let'ssayyoupurchasedablenderfromadepartmentstoreand",,,

,"allocatedthepaymenttoyour""Appliances""category.Afterreturningtheblender,thestore",,,

,"creditsyourcard.Insteadofenteringthecreditasadeposit,entertheamountasanegative",,,

,paymentasshownbelow.Doingthiswillcreditthecorrectbudgetcategoryinsteadof,,,

,treatingthereturnasincome.,,,

,,,,,

,OriginalTransaction:,,,

,ACCOUNTDATENUMPAYEECATEGORYPAYMENTDEPOSIT,,,

,CreditCard1/1/10TheBigStoreAppliances50.00,,,

,,,,,

,RecordingtheReturn:,,,

,ACCOUNTDATENUMPAYEECATEGORYPAYMENTDEPOSIT,,,

,CreditCard1/5/10TheBigStoreAppliances-50.00,,,

,,,,,

,"BudgetingforKnownFutureExpenses(e.g.car,holiday,vacation,etc.)",,,

,"Forlargepaymentsthataremadeonceeveryfewmonthsoronceayear,itiscommonto",,,

,estimateamonthlybudgetamountandtransferthemonthlyamountintoatemporary,,,

,savingsaccountsothatyouhaveenoughmoneyavailablewhenyouneedtopaythebill.,,,

,Thetransfersarerecordedasexpensesatthetimethemoneyistransferredasexplained,,,

,"aboveinthesection""Recordinga[Transfer]toSAVINGS.""",,,

,,,,,

,"Ifyouarerecordingthosetransferstosavingseachmonthasanexpense,thenwhatdo",,,

,youdowhenitcomestimetopaythebill?Youdon'twanttorecordtheexpensetwice.,,,

,,,,,

,IfyoucanpaydirectlyfromyourSavingsaccount:,,,

,Youhavealreadyrecordedthepaymentsasexpenseswhenyoutransferredthemoneyto,,,

,"Savings,soleavetheCategoryblankwhenrecordingthepayment.Thepaymentwillnot",,,

,showupinyourbudgetreportasecondtime(becausetheCategorycolumnisblank).,,,

,,,,,

,ACCOUNTDATENUMPAYEECATEGORYPAYMENTDEPOSIT,,,

,Savings6/1/14chkABCInsurance198.00,,,

,,,,,

,Ifyoucan'tpaydirectlyfromyourSavingsaccount:,,,

,"FirsttransfertheamountsavedfromSavingstoChecking,recordingthetransferasa",,,

,"credittotheexpensecategory.Then,recordthepaymentfromCheckinglikenormal.",,,

,,,,,

,1.RecordtheTransferfromSavingstoCheckingasaCredittotheExpenseCategory,,,

,ACCOUNTDATENUMPAYEECATEGORYPAYMENTDEPOSIT,,,

,Savings6/1/14TXFR[ToChecking][Transfer]200.00,,,

,Checking6/1/14TXFR[FromSavings]Insurance200.00,,,

,,,,,

,2.RecordthePaymentfromChecking:,,,

,ACCOUNTDATENUMPAYEECATEGORYPAYMENTDEPOSIT,,,

,Checking6/1/141023ABCInsuranceInsurance198.00,,,

,,,,,

,"Intheaboveexample,theactualpaymentof198.00waslowerthanthepreviouslybudgeted",,,

,"totalexpenseof200.00,soyoucouldendupseeingavalueof-2.00intheActualcolumn",,,

,ofthereportworksheet.Thisissimilartowhatyoumightseeifyoureceivedarefundor,,,

,returnforsomethingyoupaidinapreviousmonth.,,,

,,,,,

,BudgetingforKnownFutureExpenses-WithoutTransfersBetweenAccounts,,,

,Toallocatemoneytoasavingsorexpensefundwithouttransferringthemoneytoadifferent,,,

,"account,youwillneedtoaddapairofoffsettransactionsforeachallocation.Thefirstentry",,,

,includesthebudgetcategoryandthesecondentrydoesnot.Thepaymentanddepositamounts,,,

,"offseteachother,sotheaccountbalancedoesnotchange.Whenyoumaketherealpayment,",,,

,youleavetheCategoryblank.Thefollowingshowswhatthismightlooklikeforaquarterly,,,

,"healthinsurancepayment,withmonthlybudgetallocations.",,,

,ACCOUNTDATENUMPAYEECATEGORYPAYMENTDEPOSIT,,,

,Checking1/1/10[Allocation]HealthInsurance200.00,,,

,Checking1/1/10[Allocation]200.00,,,

,Checking2/1/10[Allocation]HealthInsurance200.00,,,

,Checking2/1/10[Allocation]200.00,,,

,Checking3/1/10[Allocation]HealthInsurance200.00,,,

,Checking3/1/10[Allocation]200.00,,,

,Checking3/15/10ABCInsurance600.00,,,

,,,,,

,TrackingtheBalanceofSavingsandExpenseFunds,,,

,"Ifyouchoosetokeepallocatedfundswithinyourspendingaccount,youmaywanttokeep",,,

,trackofhowmuchinyourspendingaccountisfreetobeusedforotherpurposesandhow,,,

,muchhasbeenallocatedtofutureexpenses.Thereareacouplemethodsfordoingthat:,,,

,,,,,

,Method1:DefineaVirtualSub-AccountintheAccountsworksheet,,,

,"Usingthismethod,yourecordentriesintheTransactionsworksheetjustasyouwouldifyou",,,

,weretransferringrealmoneybetweenaccounts.Yourbankstatementwillnotshowthese,,,

,transactionsbecauseyouareonlylistingtheminyourownspreadsheet.Whenyoureconcile,,,

,"withyourbankstatements,remembertoaddthebalancesforthesub-accountstothebalance",,,

,ofthemainaccount.,,,

,,,,,

,Method2:UseaSeparateWorksheettoTrackSavingsandExpenseFunds,,,

,YoucanusetheGoalsworksheettotrackanyfundbalancethatyouwantto.Youwillneedto,,,

,updatetheGoalsworksheetinadditiontorecordingtheallocationsintheTransactions,,,

,worksheet.TheGoalsworksheetisonlyfortrackingthebalanceandisnotlinkedtoyour,,,

,otherbudgetworksheets.,,,

,,,,,

,"Step4-3:Record""Cleared""Transactions.",,,

,"Whenyouseethatchargeshavebeenprocessedor""cleared""byyourbank,youshould",,,

,"entera""c""inthereconcile(R)columnforthattransaction.",,,

,,,,,

,TheClearedBalanceinthetransactionhistorytableshowstheAccountbalanceforthe,,,

,"transactionsmarked""R""forreconciledor""c""forcleared.Thisallowsyoutocomparethe",,,

,ClearedBalancewiththecurrentbalanceshownonyourbankstatements.,,,

,,,,,

,TheAccountBalancereflectsyouractualoreffectivebalanceandistheoneyoushouldbe,,,

,lookingattostayonbudget.TheClearedBalanceisforcomparingtoyourbankandcredit,,,

,"cardstatements.Forexample,whenyouwriteachecktoafriend,itwon'tshowupinyour",,,

,"bankaccountuntiltheycashit.Youshouldrecordthetransactionimmediately,tohelpyou",,,

,"stayonbudget,butuntilthecheckshowsuponyourbankstatement,yourClearedBalance",,,

,"willbedifferentfromtheAccountBalance(untilyouentera""c""or""R""inthereconcilecolumn).",,,

,,,,,

,Step4-4:ReconcileYourAccounts,,,

,Youshouldreconcileyouraccountsatleastoneamonth.Reconcilingisalsocommonlyknown,,,

,"as""balancingyourcheckbook"".",,,

,A.Getyourbankandcreditcardstatements,,,

,B.Reviewyourstatementsforerrorsandfraudulentcharges,,,

,"C.YourstatementsmayincludeATMfees,bankf