Financial Year Tips

get your excel cash book ready for the new year

These financial year tips will help users of my Excel Cash Book (both the easy version and the one with the balance sheet).

If you have come to the end of your financial year and your Cash Book is right up to date, you may now be wondering how you are supposed to start the new financial year.

The steps below will help you with that.

Step One: Start a new cash book

There are two ways to start a new cash book for the new financial year:-

  1. Copy your prior cash book and remove last year's data, or
  2. Download the blank template again and set it up for the new financial year.

There are pros and cons for both options so you will have to weigh up which one suits you best.

Pros to copying last year's cashbook: You will already have in it all your Accounts names/headings and the months in the order you require. If you added a lot of new account columns then it already has the right number of columns for you.

Cons to copying last year's cashbook: You will need to delete out all the income and expense transactions that you entered within the monthly sheets. If you deleted unused rows within the monthly sheets you may find you need more rows this year which means inserting new rows and updating formulas.

Pros to downloading a fresh, blank template: You won't have to work through every month deleting out last year's data.

Cons to downloading a fresh, blank template: You have to put your months into the order you require them again. You have to type in all your Account names/headers again, but you may not see this as a con.

How to copy last year's cash book

  1. Open up last year's cash book
  2. Click File > Save As > find the folder on your device where you want to save it 
  3. Rename the workbook for your new financial year: I would include the financial year in the name of the cash book, for example My Company 2021
  4. Save it.
  5. Starting in month 1, select all the cells with data you entered last year, and delete the data. See the image below showing an example of Income, all the yellow cells are the ones you would select and delete, leaving the headings and Total Money In column alone - I have faded them out.
  6. Do the same for your Expense accounts.
  7. If you are using the cash book with balance sheet, do the same with the other funds received or paid out data.
  8. Do these steps within each month.

last Year's Income Date: Before

Financial Year TipsFinancial Year Tips for Excel Cash Book

Last Year's Income Data: After

Step 2: Accessing a fresh download

Excel Cash Book Easy users can email me - I will send you a link to get the latest version of this workbook.

Cash Book Excel (with Balance Sheet) users, please locate your email with your original purchase order. Click on the link in that email which will take you to the Downloads. If you have run out of Downloads please email me with your Purchase Order Number and I will reset the Downloads for you. 

Step 3: what Data from last year do you need in this year's workbook?

Bank Balance

Financial Year Tips for Excel Cash Book : Bank Balance

You will need your opening bank balance.

If your new year starts with January, your January opening balance is the same as your Closing Bank Balance from December.

In this example, it is 1,480.00.

Profit and Loss Summary

You don't have to have any of last year's data in the new workbook, because profit and loss accounts always start at 0.00 in the new year.

However, you still may want to move a copy of your P&L summary from last year to the new year so you've always got it handy without having to go and find last year's cash book to see it.

  1. Make sure both workbooks (last year and this new year) are open on your device
  2. Go to the P&L sheet for last year
  3. Right-click the P&L name tab
  4. Select Move or Copy 
  5. In the field under To book: select your Cash Book for the new year (see Image below)
  6. In the area headed Before sheet: scroll down to the bottom and select (move to end). Note: These sheets are all the ones Excel can find in your new work book. (see Image below)
  7. Tick in the box next to Create a copy - make sure it's ticked so that Excel just copies it instead of moving it. (see Image below)
  8. Then select OK.
  9. Doing this does not in any way affect this year's data.
  10. Find the P&L in your new year workbook and rename it so you know which P&L is for last year and which one is for this year.
  11. If you are using the cash book with balance sheet you will have a bunch of extra sheets after the P&L, so just choose the sheet that you want last year's P&L to go in front of (step 6) instead of moving it to the end.
Financial Year Tips for Excel Cash Book: Copy P&L

Balance Sheet

If you are using the Balance Sheet, and have been keeping it in balance, then you must enter the balances from the end of last year into this year's Balance Sheet. (If you are not too worried about it and are leaving it to your Accountant, then don't bother with this step).

If your year-end is December, use the December 2022 balances - type them into the Opening Balances column for 2023 (see image below).  Last year's Current Earnings must be added to Retained Earnings.

If you want to, you can also copy across last year's Balance Sheet using the same steps as for the P&L above.

If you are having trouble getting the Opening Balances column to balance, double check your formulas. If you need extra help with this, please contact me.

Financial Year Tips for Excel Cash Book: Balance Sheet Opening Balances

If you want year-end tips you can read these tips.

Share this page

Facebook Comments

Have your say about what you just read!
Leave me a comment in the box below.