The ultimate solution from ExcelIdea.com :
- A dashboard of ready to use analyses and models,
- Examples of formulas used in financial analyses,
- Get ideas on how to design an analysis.
This spreadsheet has a place on your computer desktop.
- You will access it when you need to have a quick and accurate analysis of the data.
- You will open it when you want to see examples on how to use various excel formulas.
- You will need it as a reference when you need an idea on how to design a spreadsheet.
There are two tabs in the document:
1. List, which includes analysis / report name, its description, and a list of formulas that has been used in it. The list serves as navigation to the actual report. Just click on the analysis name and you will be redirected to the analysis itself.
2. Dashboard, which has all the analyses and reports that are listed in the ‘List’ tab. Just plug in the numbers in cells formatted in blue font to see the results in formulas, rations, and graphs.
Here is the list of what you will find inside:
1. Forecast using existing values
Excel formulas used: =FORECAST; =ROUND; UNIT PRICE CALCULATIONS, GRAPHS
2. Forecast using trend analysis
Excel formulas used: =TREND; % CHANGE
3. Forecast using growth pattern
Excel formulas used: =GROWTH; % GROWTH
4. Low / Mid / High Estimates
Proforma Income Statement: Sales, Cost of Sales, Growth Margin, Net Profit
5. Historical Sales Data Analysis
Excel formulas used: =MAX; =MIN; =AVERAGE; =COUNTIF; SUMIF; % growth change, VALIDATION, drop-down list feature; GRAPHS
6. Historical Sales, Cost, and Net Profit Analysis
Excel formulas used: BRIDGE ANALYSIS, RATE & VOLUME, % Gross Margin, % Net Profit, =IF, =MAX; =MIN; =AVERAGE
7. Actual to Goal / Benchmark Analysis
Actual to Benchmark difference comparison
8. Financial Statement Analysis to Benchmark Ratios
Return on Equity; Earnings per share; Net Profit Margin; Return on Assets; Return on total invested capital; Quick Ration; Current Ration; Days' sales in inventory; Inventory Turnover
9. Depreciation calculation
Excel formulas used: =DB, =DDB, =SLN, =SYD
10. Future Value of an investment
Excel formulas used: =FV
11. Present Value of an investment
Excel formulas used: =PV
12. Calculate payment for a loan
Excel formulas used: =PMT
13. Calculate Rate for a Loan or Investment
Excel formulas used: =RATE
14. Calculate Number of Payments for a loan or investment
Excel formulas used: =NPER
15. Calculate Interest Payment for a period
Excel formulas used: =ISPMT
16. Calculate Payment on a Principal for a given period
Excel formulas used: =PPMT
17. Monthly data analysis
Excel formulas used: =VLOOKUP, =CHOOSE, =CONCATENATE, =MATCH, VALIDATION, =IF, =ISNA
18. OTHER examples; instruction
Excel formulas used: =CELL, =NOW, =HYPERLINK