Modify the Basic Accounting Monthly and Annual Reports on Your Free Spreadsheet

It is much easier to give away (delete) fund accounts and line item accounts than to receive (add) more. However, you will probably require more than less...just remember...adding on requires a little more patience and work....

That being said....Let's begin modifying the monthly and annual report to better fit our individual needs...

Look at the bottom tabs...click on the brown tab named “Jan”.

Personalizing the Heading:

Font

1. Select the Heading. (Click on cell A2 and holding the button down go down to cell A4)
2. Click on the font color drop down list (the A next to the fill color icon) and choose font color.
3. Click on the font drop down list and choose font.
4. Click on the font size drop down list (next to the font) to choose size.

Inserting a Logo or Picture :

1. Insert a couple of columns by selecting columns A & B and clicking insert.
2. Select cell B3,on the Insert tab, in the Illustrations group, click picture.
3. A pop up box that says Insert Picture will appear, if your picture or logo is not in file they opened, click on the drop down list at the end of the bar that says Look in: and find your file.
4. Click on picture or logo and click insert.
5. Grab hold of the resizing little white circles on the picture and resize to desired size. (I always grab the right bottom resizing handle.)
6. Click in the middle of the picture (pointer will turn to a 4 way arrow) and move pix where you want it.
7. With the picture still selected you will notice in the ribbon bar above you see a pink Picture Tools and under it Format. There is a lot of fun stuff you can do to your picture in this tab. In the example picture above...in the Picture Styles group...I clicked on the arrow in Picture Effects, Soft Edges, 10 point to make my picture blend in better.
8. I also could have faded it out by clicking the Recolor arrow in the Adjust group, then click on the 3rd icon under Color Modes and washed it out ...or changed the whole color under Light Variations. Play with it till you get it how you want it.

Modify Fund Accounts:

Delete Columns(Fund Accounts):

1. To take out one fund account: Select column headings H & I and delete.
2. Formulas automatically adjusted. Repeat for remaining months.
3. Go to Annual Report, select column E & F to take out one fund. Formula adjusted.
4. To take out all but one fund account, select all the remaining columns and delete, also delete the heading: Fund Acc#1 (no need for it with only one fund). Repeat for remaining months and annual report.

Add Columns (Fund Accounts) to the Monthly Report :

1. To add 2 more funds, select columns E-H, click insert.
2. Select columns J-L, click copy.
3. Select cell F1, hit enter.
4. Rename fund accounts and select different font colors.
5. To add 1 column, do steps above but select only columnsE & F.
6. Repeat for remaining monthly reports.
7. The annual report is a little more difficult to modify with additional columns, see the steps below for adding more fund accounts to the Annual Report.

Add Columns (Fund Accounts) to the Annual Report :

1. Do steps 1-4 above.
2. Now...since this is a view-only sheet...you are going to have to reset some formulas
3. Select cell D11. Grab the Fill Handle and pull across to cell L11.
4. Select cell D12. Repeat step 3.
5. Select cell D16. Repeat step 3.
6. Repeat for every expense line item in column D.
7. Now to get rid of the formulas in the blank columns: Select cell E11, hold down on your button and highlight cells in column E down to cell E23, press delete on your keyboard.
8. Repeat for columns G, I, & K.
9. If you don’t like the solid dividing line in rows 12 &23, you can break them up by selecting cell E12,on the Home tab, in the Font group, click on the borders icon arrow, choose No Border, go down to E23, select and just click on border icon (the no border should still be showing).
10. Repeat for the rest of the blank columns.
11. Select cell F29, hit the equal sign on your keyboard, go back to the first page of the workbook GL-Jan, select cell K5, hit enter to pick up fund account #2’s beginning balance.
12. Repeat to pick up the rest of the fund accounts beginning balances.

Modify Line Accounts (Revenue, Expenses, etc.):

Go back to January’s monthly report.

Delete Rows (line accounts):

1. To delete one of the two income line accounts, simply select row 11, click delete.
2. To delete one or more of the expense accounts, simply select row 17, (highlight as many rows as you want to delete), click delete.
3. Repeat above steps for remaining monthly reports and annual report.

Add Rows (line accounts):

1. To insert one or more rows in the income line accounts, simply select row 12, click insert for as many rows as you want to add.
2. Select first cell in total column, grab hold of the fill handle and pull down to last income account line.
3. To insert one or more of expense accounts, select row with second expense account and click insert for as many rows as you want to add.
4. Repeat step 2.
5. Repeat above steps for remaining monthly reports.
6. Adding rows (line accounts) in a little more difficult in the annual report:
7. Repeat steps 1 and 2 to add more income accounts.
8. Select cell D12.
9. Grab hold of the fill handle and pull down to fill out new income accounts.
10. Repeat for each fund account.
11. Repeat step 3 to add the required expense accounts.
12. Select first cell in fund account #1 column, grab hold of the fill handle and pull down to fill out new expense accounts.

Whew! We’re finished! If I missed anything please contact me.

Return to Home from Free Spreadsheet