How to Work Out Gross Profit in Microsoft Excel

Working out the calculation for marking up a figure or working out gross profit is not the easiest of tasks with a pencil, paper and calculator, let alone translating that into an Excel spreadsheet using formulas.

There is a fundamental difference between mark-up vs profit, and figures can be very different if you are not doing the correct calculation.

Calculating Gross Profit

If you owned a stationery shop and bought some nice pens for a cost price of £10 each, you'd obviously want to sell these on at a profit. You could mark up the cost price by 25% but this does not mean you are getting 25% profit once you have sold the item. By marking it up by 25%, the new price would be £12.50 but if you work it out after the money has gone in the till, the £2.50 profit only represents 20% of the total sale price so you have actually made 20% GP.

To earn 25% gross profit, the pen would need to be sold for around £13.33 - but how do we work this out? Let's take a look at another example and use Excel to calculate the different outcomes.

Let's say you have a product with a value of £100 in B2 and wanted to mark-up that price by 20% - you could simply create the formula:

=B2*1.2
(or =B2*120%)

The Right Formula

This would only give you an extra 20% of the original cost, however. If you were to sell the product for £120, you would only return 16.6666667% gross profit because 20% of the sale price (£120) is actually £24.

If you wanted to return 20% profit of the sales price, you would have to do a different calculation:

=B2/(1-0.2)
(or =B2/0.8)

This formula would return £125. 20% of £125 is £25, leaving your cost price of £100. That £25 is pure profit - go and buy yourself a new t-shirt! (Don't forget your overheads, though.)