101 Ready-to-Use Excel Formulas

Chapter 7: Common Business and Financial Formulas

Spreadsheets got their start in the accounting and finance departments back when it was all done with paper and pencil. And even though Excel has grown far beyond a simple electronic ledger sheet, that ledger sheet is still a required tool in business. In this chapter, you look at some formulas commonly used in accounting, finance, and other areas of businesses.

You can download the files for all the formulas at www.wiley.com/go/101excelformula.

Formula 67: Calculating Gross Profit Margin and Gross Profit Margin Percent

Gross margin is the money left over after subtracting cost of goods sold from revenue. It’s the amount of sales that the business uses to cover overhead and other indirect costs. To compute the gross margin, simply subtract cost of goods sold from revenues. For gross margin percent, divide the gross margin by revenue.

Figure 7-1 shows the financial statements of a manufacturing company. Gross margin appears in cell C5 and gross margin percent appears in cell D5.

Gross Margin: =C3-C4Gross Margin Percent: =C5/$C$3

Figure 7-1: A financial statement for a manufacturing company.

How it works

The gross margin formula simply subtracts cell C4 from cell C3. The gross margin percent divides C5 by C3, but note that the C3 ...