Profit and Loss Statement Template

Start with the money coming in. Type monthly revenue for subscriptions, professional services and API add-ons in rows 10 to 12. In row 13, use SUM to add the three streams. For January that gives $112,000.

Next, enter your direct delivery costs in rows 15 to 17: hosting, customer support and third-party software. Add them up in row 18, which gives $28,200 for January.

Now click C19. Build Gross Profit by taking total revenue (C13) and subtracting total COGS (C18). Click the two cells while you type, and Excel draws a colored box around each one. Press Enter, then drag the fill handle across to column N.

January ends at $83,800, a 74.8% gross margin. In plain words, about 75 cents of every dollar is left to pay for everything else.

Operating expenses (OPEX) are the costs of running the company, whether or not one more customer signs. Enter them by team in rows 21 to 24: research and engineering, sales and marketing, general and administrative, and legal.

In C25, use SUM on the four cells right above it (C21 to C24), then fill it across to column N. January comes to $59,000.

Select C25:O25 and press Ctrl+1 to open Format Cells. On the Number tab, pick Currency, set Decimal places to 0 and keep the $ symbol. Make the row bold so totals stand out from inputs.

Why a range and not four cells joined with plus signs? If you insert a new row for insurance between rows 21 and 24, the range grows by itself. Insert inside the range, not below row 24, or Excel will not include it.

Now for the two lines everyone asks about. Operating Income, often called EBIT (earnings before interest and taxes), goes in row 26. Take Gross Profit from row 19 and subtract Total Operating Expenses from row 25. January leaves $24,800.

Row 27 estimates tax at 21% of operating income and wraps the result in ROUND so you get whole dollars: $5,208 for January. Row 28 is Net Income, which is operating income minus that tax: $19,592.

In this template the 21% sits inside each formula. In your own model, type the rate in its own cell and point to it.

Finish by selecting C28:O28 and choosing Top and Double Bottom Border from the Borders menu on the Home tab. Accountants read that double line as the end of the statement.

Dollar amounts alone do not say whether a cost is getting out of hand. A percent-of-revenue column fixes that. In column P, divide each yearly total in column O by total revenue in O13. For Gross Profit, that means O19 divided by O13.

Add dollar signs to the revenue cell so it reads $O$13. They lock the reference, so it stays on O13 when you copy the formula down. Then format column P as Percentage with one decimal place.

Now the story is easy to see: direct costs take 24.3% of revenue, sales and marketing takes 17.3%, and net income is 20.4%.

For an optional alert, select P19 and open Home > Conditional Formatting > New Rule. Choose Format only cells that contain, set Cell Value to less than 0.7, and pick a light red fill. The cell turns red if gross margin falls below a 70% target.

Last, make the top zone useful. Link each big card number to its yearly total: B5 to O13, E5 to O19, H5 to O26 and L5 to O28. Now the cards change whenever the ledger changes.

The small lines under the numbers (row 6) are typed text here. To make them update too, build them with TEXT and cell references.

Then add the trend column. Select Q13 and go to Insert > Sparklines > Line. Set Data Range to C13:N13 and keep $Q$13 as the Location Range, then click OK. Repeat for the other rows you want to track. The preview uses column sparklines for the profit rows, which make month-to-month comparisons easier.