What’s the problem?
- You’re running your company from month to month, but you still worry whether you will be able to pay your VAT and the bills.
The Answer
- Get your projected income and expenditure planned and on to your computer. Follow this simple guide to setting up your own cash planner.
- Month 1 – Sheet 1 – tab 1
- Open a new Excel sheet and label it “Cash Plan.” Label the tab at the bottom the first 3 letters of the month.
- Start with your current month, with each week finishing with a Sunday date. The first column will house your income and expenditure names. The second column will hold your bank balance, so begin your weeks from the third column.
- The weeks go across the top with a total column on the right.
- The income and expenditure lines go down the side.
- Under Income you will need each client listed individually. (You can also cluster clients by service/product type so that you can see which service/product is doing best)
- Put a total line at the bottom of Income.
- If you pay VAT, you will need a line under Total Income for VAT collected.
- Under Expenditure you will need to list
- Your drawings,
- Staff costs,
- Insurance costs,
- Staff NI payments,
- Bank charges/fees,
- Office costs,
- Telephone/broadband costs,
- Other costs listed individually if they are regular
- Accountancy fees,
- Credit card fees
- Contingency/sundry.
- Put a total line at the bottom
- If you pay VAT, you will need a line under Total Expenditure for VAT paid.
- The bottom line is the Bank balance
- Subsequent months
- Copy the page you have just created on to the next tab/sheet and repeat
- Link each page together so that the bank balance flows through.
- I would recommend creating 12 months so that you can plan short, medium and longer-term and know where you’ll be at your year end.
- Adding formulas to the pages
- Create a formula to total each column for income and expenditure.
- Create a VAT line and amount under the income section with a formula that calculates 20% of the value in the total column and then totals it to the total column on the right.
- Create a formula in each week for the Bank balance. Take the bank balance at the bottom of column 2 – add week 1 income – subtract week 1 expenditure. Now copy that formula across the line.
- Enter in the bottom left cell your current bank balance and check that it is mirrored all the way to the end of your spreadsheets. This confirms that no error has occurred as you copied across.
- Populating the pages
- Income
- List current, expected and forecast clients down the first column
- Add net values (without VAT) in the appropriate payment week for each client
- To the right you should have totals for each client
- At the bottom you should now have the total income per week and the VAT amount collected.
- Expenditure
- Mirror what you just did for income
- Repeat for each page going through the year
- Income
- NB
- Try to be as realistic as you can going forward with your income forecast because if you forecast is high you may get bad news later in the year but could have spent against that forecast.
- If you plan a lower forecast, you will be financially safer.
- Conclusion
- You have completed you own cash planner and can see and plan your potential future with more certainty than ever before.
- If you’re having trouble getting this set up, give us a call Freephone 0808-172-1900 and we can help you with your problem.