How to Set Up a Household Budget Spreadsheet
- 1). Create a new spreadsheet document in your preferred program. You can organize the sheet in two columns for easier printing or in one column to read easier.
- 2). Widen your A column to about an inch by moving your cursor to the right side of the A and dragging it to the right.
- 3). Type the following words into your A column from A3 to A12: INCOME, Wages & Tips, Interest Income, Dividends, Gifts Received, Refunds/Reimbursements, Transfer from Savings, Other, Other, Total INCOME. Leave A13 blank and continue with A14 through A28: Mortgage/Rent, Electricity, Gas/Oil, Water/Sewer/Trash, Phone, Cable/Satellite, Internet, Furnishings/Appliances, Lawn/Garden, Home Supplies, Maintenance, Improvements, Other, Total Home Expenses. Leave A29 blank.
- 4). Continue typing in the A column with other categories as indicated in your home such as Daily Living, Children, Transportation, Health, Insurance, Education, Charity/Gifts, Savings, Obligations, Business Expenses, Entertainment, Pets, Subscriptions, Vacations, and Miscellaneous. Download the template in the Resource section for some ideas for category breakdowns.
- 5). Type "Budget" into cell B3, "Actual" into cell C3, and "Difference" into cell D3. Type "=C4-B4" into cell D4. Copy that cell by holding down "Ctrl" and hitting the "C" key. Drag your mouse to select cells D5 to D11. Paste the formula by hitting "Ctrl" and the "P" key. This will make it so that you can find the difference between your usual wages and what you actually received. Type the formula "=B15-C15" in cell D15 and copy and paste the formula in all the D cells below D15 to get the actual expenses for the current month.
- 6). Create a summation for your income section and for each expenses section by selecting all the cells in one row and clicking the Sum key from the top menu. Do this for all column B, C, and D expense sections.
- 7). Create a summary section in columns F, G, H, and I, by typing "Monthly Budget Summary" into F3, "Total Income" into F5, "Total Expenses" into F6, "Net" into F7, "Budget" into G4, "Actual" into H4, and "Difference" into I4. Make cell G5 equal to the value of B12 and make H5 equal to the value of C12. For the cells of G6 and H6 type in a formula that adds all the sums of each section of expenses. Your formula would look like "=B15+B41+B52" for G6 and like "=C15+C41+C52" for H6.
- 8). Type in the formula "=H5-G5" in cell I5 and the formula "=G6-H6" in cell I6. Type in the formula "=G5-G6" in cell G7, the formula "=H5-H6" in H7, and type "=H7-G7" in cell I7. This will show your budgeted net gain or loss, your actual net gain or loss, and the difference between the two. Type in your income and expense numbers that month to determine your cash flow for the month.
Source...