top of page

Sales & Profit Tools

These tools will help you track sales by product or date, calculate total and monthly revenue, analyze profits with margins and growth per sale. You’ll also learn to set the right prices with markup formulas and determine your break-even point so you know exactly when your business starts making money. If you understand them, you'll be able to use the templates with ease.

=SUMIF( )

Sort Sales by Product or Month:

​​

  • What it does: Adds up only the sales that meet a specific condition, like all sales for "Glazed Donut" or all sales in January
     

  • Parameters: Range to check (product names), Criteria ("Glazed Donut"), Range to sum (sales amounts)
     

  • Why it’s useful: Lets you instantly see totals for specific items without manually filtering data

=VLOOKUP( ) & =XLOOKUP( )

  • What it does: Looks up a value in one table and pulls related data from another column or range

​

  • Parameters: Lookup value (product name), Table/range to search, Column/range to return.​

​

  • Why it’s useful: Lets you quikcly get data without searching for it manually

Profit Margin(%)

  • Definition: The percentage of the selling price that is profit, after costs are removed

​

  • Formula: (Selling Price – Cost) / Selling Price

​

  • Example: If your handmade candle sells for $15 but costs $6 to produce, Excel shows a 60% margin. This helps you compare profitability, sometimes cheaper items make more money percentage-wise than the expensive ones.

Break-Even Analysis

  • Definition: The number of units you need to sell before you start making a profit

​

  • Formula: Break-Even Units = Fixed Costs / (Selling Price – Variable Cost)

    • Varibale Costs(change with changes in the production volume ex: direct ingredients

    • Fixed Costs ex: rent, insurance

​

  • Example: If you run a coffee cart with $1,000 in monthly rent and each coffee sells for $4 but costs $1.50 to make, Excel shows you need to sell 400 coffees to break even. Every sale after that is pure profit.

PivotTables: Quick Sales Summaries

  • What it does: Groups and totals your sales data automatically, showing results by product, month, or salesperson ( more commonly used alternative to =sumif( ) )

​

  • Parameters: Choose Rows (what to group by) and Values (what to sum, count, or average)

​

  • How to set it up:

    • Select your sales data

    • Go to Insert

    • PivotTable

    • Pick where you want it to appear

    • Drag a field (like “Product”) into Rows and a field (like “Total Sales”) into Values

​

  • Why it’s useful: Turns long sales lists into clear reports in seconds 

Gross Profit Per Sale

  • Definition: The amount of money you earn from each sale after subtracting the cost of making or buying the item

​

  • Formula: (Selling Price – Cost) * Quantity Sold

​

  • Example: If you sell a loaf of bread for $3 and it costs $1.20 to make, Excel calculates the $1.80 profit per loaf. Selling 50 loaves in a day means $90 profit. This shows which products bring in the most money after costs.

Markup vs Margin: Setting up the Right Prices

  • Set selling prices based on your costs and profit goals

​

  • Margin Formula: Price = Cost / (1 – Target Margin)

    • Margin is profit as a percentage of the selling price

​

  • Markup Formula: Price = Cost × (1 + Markup)

    • Markup is profit as a percentage of the cost

​

When to Use:

  • Use margin when you’re focused on the percentage of your selling price that’s profit, common in retail and service industries where you need to hit target margins​

  • Use markup when you prefer to add a fixed percentage to your costs, common in manufacturing or wholesale pricing.

​

  • Example: If a cake costs $5 to make and you want a 40% margin, Excel calculates a $8.33 selling price. With a 40% markup, Excel calculates the price at $7

All the tools mentioned here have ready-to-use versions in the Templates page, just download and start using them

bottom of page