When you are running a business or balancing your personal budget, one of the most time consuming and tedious tasks that you will have to complete is accounting for your income and outgo. This information needs to be collected for analysis, and also for tax reasons if for a business. Many people choose to hire accountants to handle these duties, but if you would like to create your own expense and income spreadsheets to track your business and household activities, Excel makes it easy for you to enter new information and perform calculations.
Open a new Excel spreadsheet. Double-click the tab at the bottom of your blank worksheet, which is probably named "Sheet 1" by default. Name this first worksheet "Income," then click the tab next to it and rename the second worksheet "Expenses."
Label the top columns in your income worksheet as follows: Date in A1, Description (identify the transaction, for example payment for 10 widgets) in B1, Payer (the party that sent payment) in C1, amount in D1, Invoice Number (if applicable) in E1, Form of Payment (check, cash, credit card) in F1, and Total (where you will keep a running total of your income) in G1. You can name the columns the same for your expense worksheet, except leave out the "Invoice Number" column and change "Payer" to "Payee," and rename "Form of Payment" to "Paid From" so that you can track how the expense was paid. You will keep a running total of your expenses in G1.
Enter your first entry under either the "Income" or "Expenses" spreadsheet. Your income could be a check you received on an invoice or cash received from a customer at your store. An expense entry could be a purchase from a supply store, advertising payment, or a rental payment. It is dire that you include the full date, including month, day, and year under the date column for both sheets. You may need to keep track of income and expenses by quarter, and you definitely will need to know the year in which income was received.
Press "Enter" to begin the second entry. When you finished filling in information on that second line, enter the following formula in the final column, which should be cell G2: "=(G1+D1)" minus the quotation marks. This is the case for both the income and expense spreadsheets.
Select cell G2 and press "CTRL+C" to copy it. Then select the entire G column by clicking on the letter "G" at the top of your worksheet. Right-click the column and select "Paste Special." Choose the box that says paste "Formulas" and then "OK." That formula will now automatically calculate the running total for every income or expense entry you make in both sheets.
Create a third worksheet labelled "Returns" to track income that gets reversed due to product being sent back. There is a space on your business tax forms for "Returns and Allowances." If you're a business, in some cases an income entry might be a promise to pay (invoice) depending on your accounting methods.
Tips and warnings
- Create a third worksheet labelled "Returns" to track income that gets reversed due to product being sent back. There is a space on your business tax forms for "Returns and Allowances."
- If you're a business, in some cases an income entry might be a promise to pay (invoice) depending on your accounting methods.