Excel 2010 Financials Cookbook

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.

$23.99

$39.99

RRP $23.99

RRP $39.99

eBook

Print + eBook

Want this title & more?

$16.99 p/month

Subscribe to PacktLib

Enjoy full and instant access to over 2000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

Book Details

ISBN 139781849691185

Paperback260 pages

About This Book

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

Who This Book Is For

This book is for anybody who wants to use Excel to manage their revenues, budgets, and other financials. It's especially suitable for small businesses that already use Excel and want to get more from it.

Table of Contents

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

What You Will Learn

Normalize financial data in Excel, so that data from different sources can be easily compared

Organize your money and finances in the best way to enable easy and powerful analysis of that data

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.

Authors

Andre Odnoha

Andre Odnoha has been working within the business and technology field for many years combining his knowledge of business and information technology. With his knowledge in both software and hardware related to computer systems and a Bachelor's degree in Business Administration, Andre works to combine and utilize technology from a business perspective.

Andre has over 11 years of experience in computer software and hardware and has programmed numerous software applications designed for medical billing, e-commerce, and content management.

Alerts & Offers

Series & Level

We understand your time is important. Uniquely amongst the major publishers, we seek to develop and publish the broadest range of learning and information products on each technology. Every Packt product delivers a specific learning pathway, broadly defined by the Series type. This structured approach enables you to select the pathway which best suits your knowledge level, learning style and task objectives.

Learning

As a new user, these step-by-step tutorial guides will give you all the practical skills necessary to become competent and efficient.

Beginner's Guide

Friendly, informal tutorials that provide a practical introduction using examples, activities, and challenges.

Essentials

Fast paced, concentrated introductions showing the quickest way to put the tool to work in the real world.

Cookbook

A collection of practical self-contained recipes that all users of the technology will find useful for building more powerful and reliable systems.

Blueprints

Guides you through the most common types of project you'll encounter, giving you end-to-end guidance on how to build your specific solution quickly and reliably.

Mastering

Take your skills to the next level with advanced tutorials that will give you confidence to master the tool's most powerful features.

Starting

Accessible to readers adopting the topic, these titles get you into the tool or technology so that you can become an effective user.

Progressing

Building on core skills you already have, these titles share solutions and expertise so you become a highly productive power user.