Excel 2010 Financials Cookbook

Table of Contents

• Preface

• Chapter 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

• Chapter 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

• Chapter 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

• Chapter 4: Using Graphs for Financial and Statistical Analysis
• Introduction
• Charting financial frequency trending with a histogram
• Creating a stem and leaf plot
• Creating a box and whisker plot
• Creating a graph overlay for profit and expenses
• Graphing the principal of a loan automatically
• Adding animations to Excel graphs
• Adding a graph to e-mail automatically

• Chapter 5: Representing Data without Graphs
• Introduction
• Visualizing payments with a graphical calendar
• Creating a five-number summary for analysis
• Calculating hours with a visual timesheet
• Analyzing financial data via repetition
• Adding mini graphs within the data
• Creating a table of financial information

• Chapter 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

• Chapter 7: Augmenting Excel Functions with Customized Data Mining
• Introduction
• Highlighting user edits and storing the versions
• Adding financial tools to the Excel ribbon
• Completing web forms
• Importing data lists from the Web
• Counting by colors instead of numbers
• Converting dates with code
• Analyzing financial data with pop-up tools

• Index