Excel 2007: Financial Analysis

Introduction

• Welcome
• Using the exercise files
• Disclaimer

1. Designing a Financial Worksheet

• Separating inputs and formulas
• Avoiding common mistakes
• Tracing formula precedents and dependents
• Evaluating Excel formulas step by step

2. Analyzing Cash Flows Using Excel Tables and Pivot Tables

• Tracking income and expenses using an Excel table
• Creating a Pivot Table from table data
• Pivoting a Pivot Table
• Filtering a Pivot Table
• Adding Pivot Table columns to enhance data analysis
• Tracking cash flow using a Pivot Chart

3. Analyzing Financial Statements

• Reading a corporate financial statement
• Introducing common-sizing strategies for analyzing financial statements
• Creating common-sized income statements
• Creating common-sized balance sheets
• Calculating percentage changes in financial statements

4. Calculating Profitability Ratios

• Calculating earnings per share
• Calculating return on equity and return on assets
• Calculating gross profit margin and net profit margin

5. Calculating Liquidity and Activity Ratios

• Calculating the current ratio and quick ratio
• Calculating the average collection period
• Calculating inventory turnover

6. Calculating Leverage Ratios

• Calculating the equity ratio
• Calculating the debt ratio
• Calculating the times interest earned ratio

7. Calculating the Effect of Interest Rates on Loan Repayment

• Calculating simple interest and compound interest
• Applying nominal versus effective interest rates (APR versus APY)
• Calculating the number of days between events

8. Determining Future Cash Flows

• Computing the future value of an investment
• Calculating present value
• Calculating net present value
• Calculating internal rate of return
• Calculating NPV and IRR for uneven input periods (XNPV and XIRR)

9. Forecasting Future Growth

• Projecting future results using the Forecast function
• Performing quick forecasts using the Fill handle
• Adding a trendline to a chart

10. Determining Amortization and Depreciation Schedules

• Introducing amortization
• Calculating payments on a fully amortized loan
• Calculating payments on a partially amortized loan (balloon payments)
• Calculating interest and principal components of loan repayments
• Introducing depreciation
• Calculating straight line depreciation
• Calculating declining balance depreciation
• Calculating double declining balance depreciation

11. Analyzing Bond Investments

• Introducing bonds and bond terminology
• Calculating a bond's yield
• Calculating the value of zero coupon bonds
• Pricing bonds to be offered to investors

Conclusion

• Goodbye