In this example I’ve shaded the income green, expenditure red and the remaining balance blue. We can use these figures to create a basic income and expenditure (profit and loss) report.
· Open a new sheet in your spreadsheet and copy the income and expenditure headings into column A.
· Next copy the highlighted green and red figures into column B.
· In column C add a formula that will add up your income and expenditure. If you have included a column for total income and expenditure in your cash book (the figures shown in darker green and dark red in my example), they should match the calculated total income and expenditure on your new spreadsheet. To make the total expenditure figure negative try adding “-“ between “=” and “Sum” in your formula.
· Finally, below the total income and total expenditure figures add a formula that adds the income and negative expenditure figures together (note: if you did not change the expenditure figure into a negative, you will need to subtract this from the income figure).
It should look like this.
Did you notice how the profit or loss figure is different to the balance brought down figure? This is because the figure you have calculated is the profit or loss figure for this period of time whereas the balance brought down figure should match the figure in your bank account and includes any profit or loss left from the preceding periods (the starting balance in the example above). This figure is used on your balance sheet.
You can make a Profit or loss statement every week or month. By comparing the figures, you can see any trends early and take action before problems become too big.
In the example above, the business owner took action in August, as soon as they had the figures for July and could see they had made a significantly smaller profit than in previous months. Because they had the monthly figures, they are now on track to return their monthly profits to the expected level early in the new year, minimising the impact of the unexpected summer slump. If they had not had the figures or taken prompt action, it’s possible the business would have been making a loss by the end of August and could have finished the year in the red, unable to pay the year-end tax bill.
If you need help to understand your figures; drop me an email.