Financial Analysis: Analyzing the Bottom Line with Excel

Learn how to analyze the bottom line (or profitability) of your company, by analyzing your gross margin and operating expenses with Excel.

Learn how to analyze the bottom line (or profitability) of your company and identify areas of under- or overperformance with Excel. Rudolph Rosenberg, author of the Financial Analysis series, shows how to use the data your company generates every day to break down your gross margin and operating expenses. He introduces the tools and techniques you can use in Excel to prepare and analyze that data, including PivotTables and the invaluable SUMIF function. Plus, you'll learn to identify and analyze sales and marketing spending, staff expenditures, and productivity separately, so you can get a really clear picture of your data from multiple angles.

Also check out the companion course, Financial Analysis: Analyzing the Top Line with Excel.

Introduction

• Welcome
• What you need to know
• Using the exercise files

1. Analyzing Gross Margin

• The basics of gross margin analysis
• Finding the data points
• Preparing the data
• Categorizing expenditures
• Allocating costs by category
• Calculating standard costs
• Using standard costs to calculate gross margin
• Adding standard costs to your revenue database
• Calculating gross margin
• Performing a price/mix analysis
• Analyzing overall gross margin performance
• The gross margin percentage levers

2. Analyzing Operating Expenses

• The basics of operating expenses analysis
• Finding the data points
• Analyzing overall operating expenses performance
• Analyzing sales and marketing
• Analyzing nonsales staff expenditures
• Analyzing productivity
• Analyzing general expenses

Conclusion

• Next steps