You will learn to create an annual budget on an Excel spreadsheet that is sensitive to many Income Tax Schedule A itemized deductions, as well as Schedule C for a small business (which applies these days to a lot of people). The included example is designed primarily for a couple in semi-retirement, both working; it thus includes accounts for younger and older citizens pertinent to their inclusions and deductions on their annual 1040 and state tax returns. If you follow the steps below to set up your budget, you can tweak the inputs depending on your needs, and create a plan suited to your specific case.
Steps
1Open Excel. Double-click the green X on the dock, or open the Applications folder and then the Microsoft Office folder, before clicking Excel.
2Open a New Workbook. Title the top, leftmost worksheet, “Actuals”.
3Into cell B1, type the date 01/31/16, or the current year if other than 2015.
4Select cell range B1:M1. Do Edit > Fill > Series Rows Date > Month > Step_Value 1 > OK
5Select columns B:N, right click, and hit “Format Cells”. Select the “Custom” option under the Number tab. Pick $#,##0.00;-$#,##0.00 with column width 1.
6Select range B1:N1, right click, and hit “Format Cells.”. Select the “Custom” option under the Number tab. Pick mmmm.
7Enter in cell N1 the label Year-to-Date.
8Enter in cell N4 the formula, =sum(B4:M4). Then Edit > Copy the formula and Edit > Paste it to cell range N4:N110.
9Enter the following labels to cells A1:A110. They should cover all of the budget items you need:ANNUAL BUDGETINCOME:Source 1 – Net Paycheck(s), excl. taxes, 401K, etc.Source 2 – Trust IncomeSource 3 – Dividend IncomeSource 4 – Interest Income, excluding SavingsSource 5 – Schedule C IncomeSource 6 – Home Rental IncomeSource 7 – Other Income, from Investments/OtherRefinance (REFI) Loan ReceiptMisc. Income (Yard Sales, etc.)Other Assets Sold for CashOther Assets Sold for Cash InstallmentsGifts Converted to CashTOTAL INCOMESAVINGS RECAP:Beginning BalanceAdd: From Source 1 – withdrawable 401K/OtherAdd: Regular and Other Savings ContributionsDeduct: Withdrawals (new trust: gain, sale of home)Add: Interest EarnedDeduct: Fees & ChargesEnding BalancePRINCIPAL and OPTIONS:Trust BalanceNon-withdrawable 401K/Pension BalanceOther “untouchable” Principal balance(s)Stock Options, Unexercised, at est. market valueTOTAL PRINCIPAL and OPTIONSTOTAL SAVINGS, PRINCIPAL and OPTIONSEquity additions & Expenses:Home – Mortgage Interest /Rent, w/ REFI int.Home – Equity, Repairs & Improvement, w/ REFI Princ.RemodelingRoofDrivewayHome – MaintenanceYardcare & Gutters MaintenanceSewage Line MaintenanceHome- Property TaxHome – InsuranceHome – Other Mortgage-related Exp.Home – Rental Portion Improvements & RepairsHome – Rental Portion MaintenanceHome – Rental Portion Property TaxHome – Rental Portion InsuranceHome – Rental Portion – Other ExpenseAuto Payment(s) – InterestAuto Payment(s) -EquityAuto Insurance w/ GroceriesTransportAuto Gas – w/ GroceriesTransportAuto Oil & Maint. w/ GroceriesTransportAuto Repairs w/ GroceriesTransportAuto License, Fees, Registration ExpensesAuto Depreciation/ObsolescenceSys: Macs, Phone, TV, Printer Ink & PprSys: Software & Hardware EquitySys: Other Tech AccessoriesSys: Depreciation/ObsolescenceSuppliesGroceries, Rx & Comestibles (non-deductible)Moving ExpenseCredit Card – Interest paymentsLong-term Loan Repayments, e.g. Educatnl. Princ.Long-term Loan Repayments, InterestShort-term Loan Repayments, PrincipalShort-term Loan Repayments, InterestUtilities: Garbage & RecyclingUtilities: Gas & ElectricUtilities: WaterMedical: Transport, Tests & ProceduresDental: Including TransportVision & Eyewear, w/ TransportChronic Conditions Counseling, w/ TransportLegal Fees/Retainer, etc.Other Professnl Fees, Dues, Subscrptns, MmbrshpsCareer/Professional Library +/or Software, AidsEducation & Training Expense UnreimbursedDonations: Church and Other Tax-deductibleDonations: Non-deductibleGiftsSched. C – Accounting / Bookkeeping ExpenseSched. C – Payroll ExpensesSched. C – Production & Pkg’g Expenses … orSched. C – COGS & Supplies Inventory Exp’d.Sched. C – UPS / Freight / S&H and Mail ChargesSched. C – Admin, Sys & Communications ExpenseSched. C – Mktg / Promotional / Selling ExpenseSched. C – Meals & Entertainment ExpenseSched. C – Travel ExpenseSched. C – Facilities Maintenance ExpenseSched. C – Licenses, Fees, Registration ExpensesSched. C – Other Internet ExpensesSched. C – Other ExpenseOther Tax-Deductible ExpenseOther Non-Deductible ExpenseMiscellaneous Expense (= Supplies?)Equity additions & Expenses:ANNUAL BUDGET RECAP:Cash On Hand: OVER (SHORT), Beginning BalanceTotal IncomeLESS: Regular Savings ContributionADD: Withdrawals from Savings (except new home)LESS: Equity additions & ExpensesCash On Hand: OVER (SHORT),Ending Balance(Note: if you’re SHORT per your Budget, you need to spend less, get a loan and/or to make more money.)
10Do the math. Follow the subsection instructions for January as to whether to add all the items in a subsection (like INCOME or SAVINGS) or subtract some. Then bring the subsection total or line item down to the ANNUAL BUDGET RECAP bottom section and add or subtract accordingly — careful, some are reversed from Savings because a contribution to Savings is a deduction from Cash on Hand, and a Withdrawal from Savings adds to Cash on Hand.
11Copy the January formulas over to columns C:N.
12Click Edit > Move or Copy Sheet. When you have your copy, retitle it Budget. Again make another copy of the Actual sheet and title it OVER (SHORT). Subtract your Actuals from Budget to arrive at OVER (SHORT) by clicking in cell B4 of OVER (SHORT) and entering the formula, =Budget!B4-Actuals!B4.
13Copy and paste that formula from cell B4 to cell range B4:N110. Clear any blank rows you may have inserted so you don’t end up with cluttering zeroes.14Remember that if you insert or delete a row line-item on Actuals or Budget, you must insert or delete the same row also on both of the other two sheets as well, and adjust the formula(s) accordingly.
15Consider the notes for this example:This couple / family receives at least 1 paycheck, more probably at least 2.They also have a supplemental trust left to them.They have other investments and are pretty thrifty.They own a small business they report on Schedule C of Form 1040.They own their own home and rent out a room to also supplement their income; the tenant also does housework and cooking, etc. in lieu of cash rent.They have recently refinanced their home to take advantage of low interest rates and to do a remodel, driveway repaving and fix the roof.However, the refi wasn’t as much as they wanted, so they’ve had yard sales and also sold some recreational vehicles and assets of their youth — they’re semi-retired — and they also sold one of their two cars. They’re going to either buy a smaller home or retire to a senior community — they haven’t decided which yet though.They do have a fair retirement “nest egg” built up though, that they’d like to avoid drawing down from for as long as possible.So now, one of them pays a friend to transport her to doctor’s and dental appointments, grocery shopping, etc., and this was all worked out on a mileage basis, with amounts factored in for repairs, insurance and fees, etc.Her husband, though working and helping her run the small business, went back to school, and will need to start paying back an educational loan this year. His employer covered most of the cost of the texts and school supplies but not the laptop or home PC he bought, which he also uses in his home business, and keeps a career library on and other professional software (which is deductible above what was reimbursed by the employer).The couple has certain medical, visual and other physiological issues — all deductible, as well as the transportation to and from the offices.The couple is active politically but these expenditures are not deductible in most cases.The couple requires assistance with certain aspects of the bookkeeping and accounting for the small business, and they also have hired a part-time production assistant, so they needed help coping with all the payroll, insurance and human resources issues as well.The new company will operate partially over the internet via a website, which is under development, for a “one-time” fee, which is deductible.They plan to use Excel to help budget both their business and personal financial activities, by copying these accounts to a second and third worksheet. The leftmost tab or worksheet will contain Actual amounts, the middle worksheet will contain Budget amounts, and the rightmost worksheet will compute the difference as OVER (UNDER) amounts. With this workbook, a Cash Flows worksheet, a Balance Sheet and a Profit & Loss Statement, they will have the reporting they need to not only stay on top of their business but also take all the deductions on their tax forms to which they are legally entitled, so long as they maintain their files, ledgers and receipts in good order.You will note that, because this document is designed for people in semi-retirement, it contains many tax items faced by other people with fewer years of work behind them (but may be missing a huge item like day care), as well as addressing many of the concerns of many senior citizens.The following is the Budget for the semi-retired couple. Note that items which have a Beginning Balance and Ending Balance have the Beginning Balance transferred over to the far right YTD column, which otherwise sums across, except for the Ending Balance line, which sums down vertically. Mostly, the Year to Date column sums across horizontally. Months April to October are filled in but hidden.
