One of several financial measurements a company uses in evaluating its financial performance is gross margin. It shows what a company is earning after costs (for products and/or services) as is a basic measurement as to a company's profitability. Gross margin can simply be calculated by using revenue and gross profit, and an Excel spreadsheet is an excellent tool to do your analysis across months, quarters and years.
Measure gross margin as a percentage. You will need to first calculate your gross profit, which requires you have your company's revenue (or sales) information and cost of sales or products and services.
Calculate gross profit by taking total gross or sales revenue, minus total sales costs. In the Excel spreadsheet, across the top columns (Cells A1, B1 and so on), label them months, quarters or years--whatever periods of time you are going to measure.
Label the first row Gross Revenue (Cell A2), the second row Sales Costs (Cell A3), the third row Gross Profit (Cell A4) and the fourth row Gross Margin (Cell A5).
Input your data. In the Gross Revenue row, input the data relevant to each column. For example, if you have labelled each column Quarter 1, Quarter 2, and so on, input the gross revenue for the first quarter.
Do the same with your Sales Costs, inputting the corresponding data in that row. You should now have two rows of data, one under the other.
Calculate your gross profit. Let's say the spreadsheet cells for Quarter 1's data are Gross Revenue-Cell B2 and Sales Costs-Cell B3. In Cell B4, input the following formula =B2-B3. This will subtract the sales costs from the gross revenue to calculate gross profit.
Copy the same formula to the other gross profit cells in each column. Excel automatically changes the cell information to correspond to that column, so the next column would say =C2-C3.
Divide your gross profit by your gross or sales revenue to get your percentage gross margin. In the gross margin row, in Cell B5, you will input a formula which divides your gross profit by your revenue to calculate gross margin as a percentage. Input the formula =B4/B2 (gross profit divided by gross revenue). This will give you a decimal-based percentage.
Copy the same formula to the other gross margin cells in each column. Excel automatically changes the cell information to correspond to that column, so the next column would say =C4/C2.
Note that gross margin measures profit before operating expenses, such as paying salaries and turning on the lights.
Tips and warnings
- Note that gross margin measures profit before operating expenses, such as paying salaries and turning on the lights.