Book Image

Excel 2010 Financials Cookbook

By : Andre Odnoha
Book Image

Excel 2010 Financials Cookbook

By: Andre Odnoha

Overview of this book

<p>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.<br /><br />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.<br /><br />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.</p>
Table of Contents (14 chapters)
Excel 2010 Financials Cookbook
Credits
About the Author
About the Reviewers
www.PacktPub.com
Preface
Index

Creating a stem and leaf plot


Stem and leaf plots are an invaluable method for charting frequency. While a stem and leaf plot is not a true graph compared to bar graphs, pie charts, and so on, it is still used as a method for graphing/charting the frequency of a data within a population. Financial analysts can use this type of chart to determine inventory levels over a specific date range.

In this recipe, you will learn how to create a stem and leaf plot to chart the number of products sold over the course of several months.

How to do it...

Begin with inventory levels listed in Column A:

We will now need to create the stems. The stems are the first number of the inventory levels (for example, 49 would have stem of 4):

  1. 1. Enter the formula =INT(A2/10) into cell C2, and copy down to cell C24 to calculate all of the stems:

    We will now create the leaves for the stem and leaf plot. The leaves are the remaining numbers after the stem.

  2. 2. In cell D2, enter the formula =MID(A2,LEN(C2)+1,10) and copy...