Set Up Church Financial Records Using Microsoft Excel
It is important that churches create and maintain accurate financial records. The records can be used to monitor regular expenses against unexpected charges such as sanctuary renovations and van or bus repairs. If your church operates community outreach programs such as No Longer Bound or Weed and Seed, you can also use the financial records to apply for a state or federal grant.
Instructions
Create Categories and Monitor Spending
1. Create topical categories. Open a blank spreadsheet. At the top of the spreadsheet, type expense category headings down the rows in Column A, starting with Row 2 (cell A2). Bold the category headings by clicking "Control + B" on your computer keyboard. For example, you could type "Payroll Expenses." Below each category heading, type a detailed expense item name. Do not bold the detailed expense item names. Put each detailed expense type on a separate row. For example, under the bolded category "Payroll Expenses," you might have "Pastor John Doe's Salary" in cell A3, "Church Secretary Pay" in cell A4 and "Organist Pay" in cell A5. Place a blank space between categories. Another bolded expense category you might create would be "Office Supplies." Type nonbolded detailed expense types such as "Copy Paper" in cell A8, "Envelopes" in cell A9 and "Postage Stamps" in cell A10 beneath the "Office Supplies" category. Create a category heading and detailed expense type for every payment or expense the church will pay throughout the year, even if it is only a one time payment.
2. Record worship service collection monies received. Insert two blank rows below the last detailed expense type. Create a bolded category titled "Tithes and Offerings." Record monies received during Sunday and weekly church service programs in this field.
3. Create tracking dates. Type the name of each month starting with "January" across the top of the spreadsheet beginning with Column B and Row 1 (cell B1) to track monthly expenses. For example, after you type "January" in cell B1, type "February" in cell C1, "March" in cell D1 and so on until you type all months of the year across row 1 of the spreadsheet columns.
4. Set limits. Type the column header "Annual Budget" in cell N1. Input financial spending limits for each expense category on the Excel spreadsheet starting with cell N2. Type a financial budget or spending limit for each church related category. Type the column header "Total Spent" in cell O2. Type the header "Spent vs. Budget" in cell P1.
5. Build in formulas. Input "=SUM(B2:M2)" in cell O2 to track total monies spent for the first category January through December. Copy and paste the formula down column O for all expense categories.
Type "=O2-N2" in cell P2 to track current spend against the set budget. Copy and paste the formula down column P for all expense categories.
6. Monitor monthly expenses against the budget. At the start of each month input the amount of money to the cents that your church spent for each expense category and detailed type. Review the formulaic updates in Column P that automatically calculate and show how your ministerial and community outreach expenses are tracking against your annual budget.
Tags: detailed expense, expense category, cell track, detailed expense type, expense type