Create an Excel Spreadsheet Annual Budget

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

  1. Open Excel. Double-click the green X on the dock, or open the Applications folder and then the Microsoft Office folder, before clicking Excel.
  2. Open a New Workbook. Title the top, leftmost worksheet, "Actuals".
  3. Into cell B1, type the date 01/31/16, or the current year if other than 2015.
  4. Select cell range B1:M1. Do Edit > Fill > Series Rows Date > Month > Step_Value 1 > OK
  5. Select 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.
  6. Select range B1:N1, right click, and hit "Format Cells.". Select the "Custom" option under the Number tab. Pick mmmm.
  7. Enter in cell N1 the label Year-to-Date.
  8. Enter in cell N4 the formula, =sum(B4:M4). Then Edit > Copy the formula and Edit > Paste it to cell range N4:N110.
  9. Enter the following labels to cells A1:A110. They should cover all of the budget items you need:
    • ANNUAL BUDGET
    • INCOME:
      • Source 1 - Net Paycheck(s), excl. taxes, 401K, etc.
      • Source 2 - Trust Income
      • Source 3 - Dividend Income
      • Source 4 - Interest Income, excluding Savings
      • Source 5 - Schedule C Income
      • Source 6 - Home Rental Income
      • Source 7 - Other Income, from Investments/Other
      • Refinance (REFI) Loan Receipt
      • Misc. Income (Yard Sales, etc.)
      • Other Assets Sold for Cash
      • Other Assets Sold for Cash Installments
      • Gifts Converted to Cash
      • TOTAL INCOME
    • SAVINGS RECAP:
      • Beginning Balance
      • Add: From Source 1 - withdrawable 401K/Other
      • Add: Regular and Other Savings Contributions
      • Deduct: Withdrawals (new trust: gain, sale of home)
      • Add: Interest Earned
      • Deduct: Fees & Charges
      • Ending Balance
    • PRINCIPAL and OPTIONS:
      • Trust Balance
      • Non-withdrawable 401K/Pension Balance
      • Other "untouchable" Principal balance(s)
      • Stock Options, Unexercised, at est. market value
      • TOTAL PRINCIPAL and OPTIONS
      • TOTAL SAVINGS, PRINCIPAL and OPTIONS
    • Equity additions & Expenses:
      • Home - Mortgage Interest /Rent, w/ REFI int.
      • Home - Equity, Repairs & Improvement, w/ REFI Princ.
      • Remodeling
      • Roof
      • Driveway
      • Home - Maintenance
      • Yardcare & Gutters Maintenance
      • Sewage Line Maintenance
      • Home- Property Tax
      • Home - Insurance
      • Home - Other Mortgage-related Exp.
      • Home - Rental Portion Improvements & Repairs
      • Home - Rental Portion Maintenance
      • Home - Rental Portion Property Tax
      • Home - Rental Portion Insurance
      • Home - Rental Portion - Other Expense
      • Auto Payment(s) - Interest
      • Auto Payment(s) -Equity
      • Auto Insurance w/ GroceriesTransport
      • Auto Gas - w/ GroceriesTransport
      • Auto Oil & Maint. w/ GroceriesTransport
      • Auto Repairs w/ GroceriesTransport
      • Auto License, Fees, Registration Expenses
      • Auto Depreciation/Obsolescence
      • Sys: Macs, Phone, TV, Printer Ink & Ppr
      • Sys: Software & Hardware Equity
      • Sys: Other Tech Accessories
      • Sys: Depreciation/Obsolescence
      • Supplies
      • Groceries, Rx & Comestibles (non-deductible)
      • Moving Expense
      • Credit Card - Interest payments
      • Long-term Loan Repayments, e.g. Educatnl. Princ.
      • Long-term Loan Repayments, Interest
      • Short-term Loan Repayments, Principal
      • Short-term Loan Repayments, Interest
      • Utilities: Garbage & Recycling
      • Utilities: Gas & Electric
      • Utilities: Water
      • Medical: Transport, Tests & Procedures
      • Dental: Including Transport
      • Vision & Eyewear, w/ Transport
      • Chronic Conditions Counseling, w/ Transport
      • Legal Fees/Retainer, etc.
      • Other Professnl Fees, Dues, Subscrptns, Mmbrshps
      • Career/Professional Library +/or Software, Aids
      • Education & Training Expense Unreimbursed
      • Donations: Church and Other Tax-deductible
      • Donations: Non-deductible
      • Gifts
      • Sched. C - Accounting / Bookkeeping Expense
      • Sched. C - Payroll Expenses
      • Sched. C - Production & Pkg'g Expenses … or
      • Sched. C - COGS & Supplies Inventory Exp'd.
      • Sched. C - UPS / Freight / S&H and Mail Charges
      • Sched. C - Admin, Sys & Communications Expense
      • Sched. C - Mktg / Promotional / Selling Expense
      • Sched. C - Meals & Entertainment Expense
      • Sched. C - Travel Expense
      • Sched. C - Facilities Maintenance Expense
      • Sched. C - Licenses, Fees, Registration Expenses
      • Sched. C - Other Internet Expenses
      • Sched. C - Other Expense
      • Other Tax-Deductible Expense
      • Other Non-Deductible Expense
      • Miscellaneous Expense (= Supplies?)
      • Equity additions & Expenses:
    • ANNUAL BUDGET RECAP:
      • Cash On Hand: OVER (SHORT), Beginning Balance
      • Total Income
      • LESS: Regular Savings Contribution
      • ADD: Withdrawals from Savings (except new home)
      • LESS: Equity additions & Expenses
      • Cash 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.)
  10. Do 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.
  11. Copy the January formulas over to columns C:N.
  12. Click 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.
  13. Copy 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.
  14. Remember 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.
  15. Consider 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.

Tips

  • It will help you to get the Loan Amortization Sheets for any loans you take out or payments with an interest portion you are making. You can also find information on how to amortize a loan in Excel via the article Prepare Amortization Schedule in Excel.
  • 1 You may wish to list your Gross Paycheck and all deductions from it, esp. if you have large deductible employee contributions to a company health plan, etc., or you wish to keep track of certain other dues / contributions / withholdings, etc. or perhaps employer reimbursements for education expenses, uniforms, etc.
  • 2 COGS = Cost of Goods Sold = Beginning Inventory + Purchases = Total Available, Less: Ending Inventory = Cost of Goods Sold, per Unit type, i.e. a separate ledger is kept to track the purchases, inventories and COGS of each unit type, unless it is a Job-Order process. Freight Out is added to COGS and the Cost of Sales Returns is estimated (when the Allowance for Sales Returns is estimated) and returned against COGS if advised so by your CPA, depending on your experience level with returns. Freight In is part of Beginning Inventory Cost if absorbed by you, the buyer. Discounts for Terms Taken such as "2% 10 Days, Net 30" is deducted only if the full cost is shown in Beginning Inventory (you don't want to double count the discounts taken -- some companies show it in the Finance section, rather than the Cost section, since it is a Cash Flow managerial decision). Cost of Goods Manufactured is composed of Raw Materials, Work In Process and Finished Goods Inventories tracking, perhaps with allocation of Direct and Indirect Labor, Overhead, etc. -- see a recent Cost Accounting text and/or take a class and/or have your accountant/CPA set up the process and accounts for you.
  • You may want to break out the following expenses as well:
    • Prescriptions / Co-Pays - deductible
    • Uniforms & Dry Cleaning - deductible
    • Laundry / Dry Cleaning - non-deductible

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Create_an_Excel_Spreadsheet_Annual_Budget&oldid=322214"