Excel 2019 Bible

333

C H A P T E R

15

Using Formulas for Financial

Analysis

IN THIS CHAPTER

Performing common business calculations

Leveraging Excel’s financial functions

S

preadsheets 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, it’s still a required tool in business.

In this chapter, you’ll look at some formulas commonly used in accounting, finance, and other areas

of businesses.

Performing Common Business Calculations

This section provides a reference for some of the more common business- and financial-oriented for-

mulas that you may be asked to create when working as a business analyst using Excel.

This book’s website, http://www.wiley.com/go/excel2019bible, includes a copy of the sample

workbook for this chapter. The file is named Financial Analysis.xlsx.

Calculating gross profit margin and gross profit margin percent

Gross margin is the money left over after subtracting the cost of goods sold from the 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 the cost of goods sold from the revenue. For gross margin per-

cent, divide the gross margin by revenue. Figure 15.1 shows the financial statements of a manufac-

turing company. Gross margin is shown in cell C5, and gross margin percent is shown in cell D5.

Gross Margin: =C3-C4

Gross Margin Percent: =C5/$C$3

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.