How to Create Excel Calendar for Specific Year with Automatic Date Marker

How to 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

Open Excel.Double-click the green X on the dock, or open the Applications folder and then the Microsoft Office folder, before clicking Excel.

Open a New Workbook.Title the top, leftmost worksheet, "Actuals".

Into cell B1, type the date 01/31/16, or the current year if other than 2015.

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.)

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.

Copy the January formulas over to columns C:N.

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.

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.

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.

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 containActualamounts, the middle worksheet will containBudgetamounts, and the rightmost worksheet will compute the difference asOVER (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.

Community Q&A

Search

Unanswered Questions

In a budget, when you want to ensure that the total on the far right column equals the total at the bottom row, what is the IF formula?

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

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.

1You 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.

2COGS = 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 arecentCost Accounting text and/or take a class and/or have your accountant/CPA set up the process and accounts for you.

Video: How to Make a Budget in Excel - Part 1

How to Create an Excel Spreadsheet Annual Budget images

2019 year - How to Create an Excel Spreadsheet Annual Budget pictures

How to Create an Excel Spreadsheet Annual Budget advise photo

How to Create an Excel Spreadsheet Annual Budget photo

How to Create an Excel Spreadsheet Annual Budget new pics

photo How to Create an Excel Spreadsheet Annual Budget

Watch How to Create an Excel Spreadsheet Annual Budget video

Communication on this topic: How to Create an Excel Spreadsheet Annual , how-to-create-an-excel-spreadsheet-annual/ , how-to-create-an-excel-spreadsheet-annual/