Excel 2019 Bible

CHAPTER 15Using Formulas for Financial Analysis

IN THIS CHAPTER

• Performing common business calculations
• Leveraging Excel's financial functions

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, 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 formulas that you may be asked to create when working as a business analyst using Excel.

This book's website, 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 percent, divide the gross margin by revenue. Figure 15.1 shows the financial statements of a manufacturing company. Gross margin is shown in cell C5, and gross ...