How to make a Profit and Loss Account in Excel
In order to make decisions which will affect the direction and development of your business in total confidence, you must do so having carried out in-depth research beforehand, arming you with all the necessary facts. The same rule applies when managing your finances. That’s why every UK business owner should regularly record their Income and Operating Expenses, in order to generate reliable Profit and Loss statements for their business. Sometimes referred to as P&L statements, this is an accounting method that enables you to ascertain whether or not your business is in profit, as well as if any additional support is required. Of course, you can install software to automate the process, but you can also choose to do it manually in Excel. So if you’re working with a tight budget and have the time, here’s how to make a Profit and Loss Account in Excel.
What details does a Profit and Loss Statement require?
Profit and Loss Statements provide you with an account of how well your business has performed during a specific time frame. This is achieved by incorporating your income, operating expenses, gains and losses. As such, in order to generate a reliable Profit and Loss statement that clearly represents your business’ profitability, you’ll need to gather and log details including:
- Income: sale of goods and/or services, subscriptions, customer fee payments, fully paid business-to-business (B2B) invoices and any other sources of income attributed to your primary operations.
- Operating Expenses: staff wages, cost of supplies, utility bills, equipment repairs, insurance, purchases and any other expenses relevant to running your day-to-day operations.
- Gains: Capital gifted to your business, received from lawsuits or from other sources that aren’t generated as a result of your primary operations.
- Losses: Money lost from your business which doesn’t arrive as a result of your day-to-day operations, such as environmental damage to your premises, interest incurred and the sale of assets for less than their original purchase price (depreciation).
Do your Profit and Loss statements show that your business is loss-making? Need urgent access to capital support? Apply for Working Capital Finance or learn more about how your business could benefit.
What format do my income statements need to be in?
In accordance with Generally Accepted Accounting Principles (GAAP), when choosing a format for your business’ Profit and Loss accounts, you need to use a method that clearly displays aspects such as gains, losses and the cost of your goods and/or services in a suitable manner. As such, you have two options available:
Single-Step Income Statement
Single-step Income Statements are often considered the simpler of the two formats and is often adopted by SMEs which run small-scale operations. It’s also a method of handling your business’ profits and losses using the formula:
Net Profit = (Income + Gains) - (Operating Expenses + Losses)
However, as your operations grow in size and complexity, using this format may no longer be appropriate. This is because 3rd parties (for example lenders) may want to see more detailed information which is expanded and compartmentalized rather than compressed, making easier to perform calculations for aspects such as your Gross Margin. Nevertheless, here’s how to make profit and loss account in excel using this format.
Revenues and Gains
Cost of supplies
Multi-Step Income Statement
On the other hand, if your day-to-day operations have grown in complexity and/or you’re an established company, you may want to consider using the Multi-Step Income Statement format instead. This method gives a more concise run down of your business’ revenues, expenses, gains, and losses, as well as displaying multiple subtotals. Plus, another reason for why you may prefer using this format is that makes it easier for 3rd parties to ascertain details such as your Gross Margin. So if you’re to get on top of your business's finances, here’s how to make profit and loss account in excel using this format instead.
Cost of goods/services sold
Other Sources Of Income
e.g. cash gifts to your business
e.g interest paid to your business
Total Other Income
Need help supporting your business’ finances?
Keeping a close watch on how well your business is performing is a vital responsibility that cannot be understated. Yet in spite of the necessity, managing your business' finances effectively can prove challenging, especially when you have other aspects to contend with as well. However, that’s why it’s important for you and your accountant to regularly log and review your business’ Profit and Loss statements, giving you a clearer insight into whether you’re profitable or if changes to how you operate are required. But if you need additional support in the form of capital for episodes such as seasonal trading periods, uneven cash flow or late customer payments, you could reinforce your financial position by applying for Working Capital Finance. All you need to do is source an agreement which is appropriate for your business’ needs.
At Rangewell, we’re an Access to Finance specialist working with over 300 lenders to offer you a comprehensive overview of more than 23,000 business finance products. Our services are free to use for business owners and their advisors and we’ll also guide you through the application process. So if your Profit and Loss statements show that your business is loss-making and needs urgent support, apply for Working Capital Finance today or find out more with Rangewell.