Excel Profit and Loss Template Generator

About this template

A profit and loss statement answers one question: after everything the business earned and everything it spent, what was left? This builds the standard three-section layout - revenue at the top, the direct cost of producing what was sold beneath it, and the running costs of the business beneath that - with the profit lines between them worked out by formula.

It suits a small business, a freelancer with real costs, a shop, a studio, or anyone who has to show a bookkeeper or a bank what the year looked like. It is a summary, not a ledger: one figure per line per month, entered from your records or from a bank statement conversion, not a list of individual transactions.

Reading down the sheet with the default lines: row 3 is the header with the months across and Total at the right. Rows 6 to 8 are the revenue lines and row 9 is Total revenue. Rows 12 to 14 are cost of sales and row 15 their total. Row 17 is Gross profit and row 18 Gross margin %. Rows 21 to 27 are operating expenses and row 28 their total. Row 30 is Operating profit and row 31 Net margin %. The two margin rows read as percentages of revenue.

The line-item cells are cream and empty. Everything else - the four totals, the two profit rows, the two margins and the whole Total column - is a formula shaded pale blue-grey. Overwriting a formula cell breaks that column for that row, so type only into the cream cells and let the rest follow.

How to use it

1. Choose the line items on this page - Three lists: revenue lines, cost of sales lines and operating expense lines. Use the names from your chart of accounts if you have one, so the statement matches your bookkeeping. Cost of sales is what you spend to produce what you sold; everything else the business spends is operating expenses.
2. Type each month's figures into the cream cells - Positive numbers throughout - the sheet subtracts costs from revenue itself, so a cost typed as a negative number would be added back. Leave a month blank until it has happened.
3. Read the profit and margin rows - Gross profit is revenue less cost of sales, and gross margin is that as a share of revenue: what is left of each unit of sales after producing it. Operating profit takes the running costs off gross profit, and net margin is that as a share of revenue. A falling gross margin with steady revenue means production is getting dearer; a falling net margin with steady gross margin means overheads are growing.
4. Add a line item without breaking the totals - Insert a row inside a section - between two existing lines, never directly above the section's total row - and Excel widens the SUM to include it. Then copy the Total column formula from the row above so the new line sums across the year.
5. Compare months in the Total column - The Total column sums every line across the year and recalculates the profit rows and margins from those annual totals. The annual gross margin is therefore weighted by revenue, not a simple average of the monthly margins.
6. Hand it to a bookkeeper or accountant - The layout is the one they will expect, and because every figure is a formula they can trace a number back to the lines that make it up. File > Save As PDF for a fixed copy to send with a loan application or tax return.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =SUM(B6:B8) - Total revenue for the first month: the revenue lines added up. The same formula shape, with its own rows, gives Total cost of sales in row 15 and Total operating expenses in row 28.
• =B9-B15 - Gross profit in row 17: total revenue minus total cost of sales for the month.
• =IF(B9=0,0,B17/B9) - Gross margin % in row 18: gross profit divided by revenue, formatted as a percentage. The IF returns 0 when revenue is blank or zero, so an unfilled month shows 0% instead of a division error.
• =B17-B28 - Operating profit in row 30: gross profit minus total operating expenses. Net margin % in row 31 divides this by revenue with the same zero guard.
• =SUM(B6:M6) - The Total column on a line item: that line summed across the twelve months. Every revenue, cost and expense line carries one, and the totals, profit and margin rows in the Total column are computed from those annual figures.

Customising it

• Add a budget comparison by copying the sheet, naming the copy Budget, and putting =Actual!B9-Budget!B9 on a third sheet for the variance on any row.
• Show quarters: insert a column after every third month with =SUM(B6:D6) on each line, then hide the month columns when you want the quarterly view.
• Flag a loss-making month: select the Operating profit row and use Conditional Formatting > Highlight Cells Rules > Less Than 0 with a red fill.
• Track a cost as a share of revenue by adding a row under the expenses with =B21/B9 formatted as a percentage - rent or salaries as a percentage of sales is often the number a lender asks about.
• Change the currency after the fact with Format Cells; the formulas hold plain numbers and the symbol is only a display format.