Book Description
Exploit the flexibility of Excel to the max by using it to manage and analyze your financial data. These hands-on recipes will give you the techniques you need to go from casual user to power user.
- Harness the power of Excel to help manage your business finances
- Build useful financial analysis systems on top of Excel
- Covers normalizing, analysing, and presenting financial data
- Clear and practical with straight forward, step-by-step instructions
In Detail
Excel is one of the mostused software tools in the world and just about every business has a copy somewhere. Despite its power and flexibility it is not always clear how to use it to perform some of the most important tasks in any business: organizing, analysing, and presenting financial information.
Excel 2010 Financials Cookbook contains a rich collection of useful techniques for handling financial data in Excel. From integrating data from a variety of different sources, through organazing and analyzing financial data, to presenting it in a variety of graphical forms, this book has you covered.
The book deals first with "normalizing" financial data - that is, bringing data from a number of different sources into a single format where you can analyze them together. Then you'll learn techniques for managing and analyzing the data before discovering ways to present it graphically. The book then looks at Excel's built in features for financial analysis, and even shows how you can combine the built in features to build your own analysis functions.
Table of Contents
- Excel 2010 Financials Cookbook
- Excel 2010 Financials Cookbook
- Credits
- About the Author
- About the Reviewers
- www.PacktPub.com
- Preface
- 1. Normalizing Financial Data within Excel
- Introduction
- Setting up an Excel spreadsheet
- Correcting Excel calculations
- Removing formulas from a list of numbers
- Highlighting the blanks in a list of data
- Making printing easier to read
- Splitting financial data
- Combining financial data
- Redefining the data format
- Grouping transaction details in a statement of accounts
- Displaying financial summary formulas within their cells
- 2. Analyzing Financial Data—Staying in the Grid
- Introduction
- Analyzing with an IF statement
- Advancing what IF analysis scenarios
- Discounting proration by payment date
- Collecting user sales data across multiple sheets
- Calculating total sales by day of the week
- Directing entry with conditional messages
- Validating user interaction
- Locking cells in the grid
- Calculating total number of payments in a list by an individual
- 3. Moving Beyond the Grid—Financial Data Via an Interface
- Introduction
- Personalizing your splash screen
- Entering data via a form
- E-mailing an invoice from Excel
- Adding username and password options for securing financial functions
- Providing customers with on-demand help by using VB macros and a user form
- Calculating loan terms using Excel
- Creating a dashboard for financial functions while minimizing Excel
- 4. Using Graphs for Financial and Statistical Analysis
- 5. Representing Data without Graphs
- 6. Building Financial Functions into Excel
- Introduction
- Determining standard deviation for assessing risk
- Analyzing benefits between interest and payment investments
- Calculating the number of payments in a loan
- Calculating the depreciation of assets
- Calculating the future versus current value of your money
- Determining the difference between effective and nominal interest rates
- Identifying the profitability of an investment
- Calculating and planning for inventory requirements
- 7. Augmenting Excel Functions with Customized Data Mining