Step 3: what Data from last year do you need in this year's workbook?
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.
- Make sure both workbooks (last year and this new year) are open on your device
- Go to the P&L sheet for last year
- Right-click the P&L name tab
- Select Move or Copy
- In the field under To book: select your Cash Book for the new year (see Image below)
- 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)
- 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)
- Then select OK.
- Doing this does not in any way affect this year's data.
- 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.
- 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
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 2019 balances - type them into the Opening Balances column for 2020 (see image below).
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
Home > Excel Cash Book > Financial Year Tips