How To Calculate Gross Profit Margin Using Excel

Gross profit margin shows the company's status, reflecting the amount of cash left from revenues after accounting costs of goods sold or services given. It enables the company executives and accountants to determine if they have enough profit and liquidated cash for future investments and savings.

You can compute the gross profit margin of a company through excel by following these easy steps.

  • Know what a gross profit is. It is the revenue minus the total sales in a specific period. For example, in July 2010, your company a $4000 worth of products for $6000, the company earns a gross profit margin of $2000. The gross profit percentage for this is 50%.
  • Note the gross profit of your company or business in a particular period. You may note the company's gross profit annually, biannually, quarterly monthly or bi-monthly. This depends on the company's requirement, the company needs and the volume of products or services sold by the company. This will reflect the company's revenue during that time. Example, the company's revenue is $2000 in a month.
  • Open a workbook in your Microsoft Excel program. Using your computer, click the windows icon. Choose the Windows Excel program. Click "File" and choose "New workbook". Name your new work book, example "GPM July 2010".
  • Encode the sales of goods or services sold data in a column. Example, in calculating gross profit margin of a business that has a sales of $6000, with a goods costing $ 4000, you may encode the following data in the respective cells: A1: SALES; B1: $6000; A2: COST OF GOODS; B2: $4000; A3: GROSS PROFIT; A4: GROSS PROFIT MARGIN.
  • Write the formula for gross profit. Click B3. At the formula bar, encode "=B1-B2". This will calculate the gross profit. The gross profit is sales less sold goods. The gross profit for this example is $2000.
  • Formula for the Gross Profit Margin. Encode "(B3/B1)*100" at the formula bar for the cell B4. This will calculate the company's gross profit margin. For this example, the business's gross profit margin is 33.3%, this means that33.3% of the company's revenue generation goes to profit, while 66.7% goes to company expenses for sales production.
  • Do the same steps for the rest of eleven months. Since the formula you did is for a monthly gross profit margin report, use the same steps for the eleven remaining months to get an annual gross profit margin.
  • Keep a note of your monthly gross profit margin. Do this for several months to see the growth and progress of your business and its actual revenues.

A gross margin profit more than 30% is a good indicator that your business is in the right track. Keep your overhead expenses such as electricity, water, equipment within your computed range to benefit from the high gross profit.


Share this article!

Follow us!

Find more helpful articles: