Excel 2010 Financials Cookbook — Save 50%
Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook
Using graphs for financial and statistical analysis allows you to analyze and present data in a meaningful and functional way utilizing graphs that even Excel didn't know it could do. In this article by Andre Odnoha, author of Excel 2010 Financials Cookbook, you will learn to not only add reusable methods to automate graph production, but also how to create graphs and graphing sets that are not native to Excel.
In this article, you will learn the following recipes:
 Charting financial frequency trending with a histogram
 Creating a stem and leaf plot
 Creating a Box and Whisker Plot
 Using a graph overlay for profit and expenses
 Graphing the principal of a loan automatically
Read more about this book 
(For more resources on this subject, see here.)
Introduction
Graphing is one of the most effective ways to display datasets, financial scenarios, and statistical functions in a way that can be understood easily by the users. When you give an individual a list of 40 different numbers and ask him or her to draw a conclusion, it is not only difficult, it may be impossible without the use of extra functions. However, if you provide the same individual a graph of the numbers, they will most likely be able to notice trending, dataset size, frequency, and so on. Despite the effectiveness of graphing and visual modeling, financial and statistical graphing is often overlooked in Excel due to difficulty, or lack of native functions.
In this article, you will learn to not only add reusable methods to automate graph production, but also how to create graphs and graphing sets that are not native to Excel. You will learn to use box and whisker plots, histograms to demonstrate frequency, stem and leaf plots, and other methods to graph financial ratios and scenarios.
Charting financial frequency trending with a histogram
Frequency calculations are used throughout financial analysis, statistics, and other mathematical representations to determine how often an event has occurred. Determining the frequency of financial events in a transaction list can assist in determining the popularity of an item or product, the future likelihood of an event to reoccur, or frequency of profitability of an organization. Excel, however, does not create histograms by default.
In this recipe, you will learn how to use several functions including bar charts and FREQUENCY functions to create a histogram frequency chart within Excel to determine profitability of an entity.
Getting ready
When plotting histogram frequency, we are using frequency and charting to determine the continued likelihood of an event from past data visually. Past data can be flexible in terms of what we are trying to determine; in this instance, we will use the daily net profit (Sale income Versus Gross expenses) for a retail store. The daily net profit numbers for one month are as follows:
$150, $237, $94.75, $1,231, $876, $455, $349, $173, $34, $234, $110, $83, $97,
$129, $34, $456, $1010, $878, $211, $34, $142, $87, $312
How to do it...
Utilizing the profit numbers from above, we will begin by adding the data to the Excel worksheet:
 Within Excel, enter the daily net profit numbers into column A starting on row 2 until all the data has been entered:
We must now create the boundaries to be used within the histogram. The boundary numbers will be the highest and the lowest number thresholds that will be included within the graph. The boundaries to be used in this instance will be of $1500, and $1500.
These boundaries will encompass our entire dataset, and it will allow padding on the higher and lower ends of the data to allow for variation when plotting larger datasets encompassing multiple months or years worth of profit.
We must now create bins that we will chart against the frequency. The bins will be the individual datapoints that we want to determine the frequency against. For instance, one bin will be $1500, and we will want to know how often the net profit of the retail location falls within the range of $1500. The smaller the bins chosen, the larger the chart area.
You will want to choose a bin size that will accurately reflect the frequency of your dataset without creating a large blank space. Bin size will change depending on the range of data to be graphed.
 Enter the chosen bin number into the worksheet in Column C. The bins will be a $150 difference from the previous bin.
The bin sizes needed to include an appropriate range in order to illustrate the expanse of the dataset completely. In order to encompass all appropriate bin sizes, it is necessary to begin with the largest negative number, and increment to the largest positive number:
The last component for creating the frequency histogram actually determines the frequency of net profit to the designated bins. For this, we will use the Excel function FREQUENCY.
 Select rows D2 through D22, and enter the following formula:
=FREQUENCY(A:A,C2:C22)
 After entering the formula, press Shift + Ctrl + Enter to finalize the formula as an array formula.
Excel has now displayed the frequency of the net profit for each of the designated bins:
The information for the histogram is now ready. We will now be able to create the actual histogram graph.
 Select rows C2 through D22.
 With the rows selected, using the Excel ribbon, choose Insert  Column:
 From the Column dropdown, choose the Clustered Column chart option:
Excel will now attempt to determine the plot area, and will present you with a bar chart. The chart that Excel creates does not accurately display the plot areas, due to Excel being unable to determine the correct data range:
 Select the chart. From the Ribbon, choose Select Data:
From the select Data Source window that has appeared, you will change the Horizontal Axis to include the Bins column (column C), and the Legend Series to include the Frequency (column D).
 Excel will also create two series entries within the Legend Series panel; remove Series2 and select OK:
Excel now presents the histogram graph of net profit frequency:
 Using the Format graph options on the Excel Ribbon, reduce the bar spacing and adjust the horizontal label to reduce the chart area to your specifications:
Using the histogram for financial analysis, we can now determine that the major trend of the retail location quickly and easily, which maintains a net profit within $0  $150, while maintaining a positive net profit throughout the month.
How it works...
While a histogram graph/chart is not native to Excel, we were able to use a bar/column chart to plot the frequency of net profit within specific bin ranges.
The FREQUENCY function of Excel follows the following format:
=FREQUENCY(Data Range to plot, Bins to plot within)
It is important to note that within the data range, we chose the range A:A. This range includes all of the data within the A column. If arbitrary or unnecessary data unrelated to the histogram were added into column A, this range would include them. Do not allow unnecessary data to be added to column A, or use a limited range such as A1:A5 if your data was only included in the first five cells of column A.
We entered the formula by pressing Shift + Ctrl + Enter in order to submit the formula as an array formula. This allows Excel to calculate individual information within a large array of data.
The graph modifications allowed the bins to show frequency in the vertical axis, or indicate how many times a specific number range was achieved. The horizontal axis displayed the actual bins.
There's more...
The amount of data for this histogram was limited; however, its usefulness was already evident. When this same charting recipe is used to chart a large dataset (for example, multiple year data), the histogram becomes even more useful in displaying trends.
Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook 
Read more about this book 
(For more resources on this subject, see here.)
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):
 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.
 In cell D2, enter the formula =MID(A2,LEN(C2)+1,10) and copy this formula down to cell D24:
The stems and leaves are now created. In order to allow Excel to place the leaves in the correct order and next to the correct stem, we must determine the position of each leaf relative to its corresponding stem.
 In cell E2, enter the formula =COUNTIF($A$2:$A24,"<"&A2) COUNTIF($A$2:$A$24, "<"&INT(A2D2))+COUNTIF(A$2:A2,A2) and copy this formula down to cell D24:
The data within column C is formatted as text. Excel does not allow proper calculation of text, so it is important to change the text formatting into numbers to allow calculation.
 Select column C, rightclick on the column heading of C, and choose copy.
 Rightclick in cell C2, and choose Paste Special  Values. After the paste has been performed, the original formulas will be gone, and only the values remain. Change the values to numbers.
All of the information we need to prepare the plot is ready.
 Select cells C2 through D22, then choose Insert  Pivot Table from the Ribbon toolbar:
 From the create pivot table wizard, choose OK to accept the default configuration and placement of the pivot chart onto a new worksheet:
 In the pivot chart designer, drag the Stem label into the Row panel, the Location label into the Column panel, and the Leaves label into the Values panel:
The stem and leaf plot is now taking shape.
 From the ribbon, choose Design, and for each of the options of Subtotals, Grand Totals, Row Headers, and Column Headers, choose Remove or Do Not Show, to format the plot area properly:
The finished product is a formatted stem and leaf Plot for product Inventory:
From this stem and leaf plot, you can determine that the inventory must exceed the range of 50 products to allow for proper inventory levels.
How it works...
The first formula within this recipe utilizes the INT function of Excel. The INT function takes the value of a number and rounds that number down to the closest integer.
The formula entered into column C, =INT(A2/10) takes the value of A2 divided by 10, and rounds down the integer. This provides the first number for any given inventory level.
Next, the formula =MID(A2,LEN(C2)+1,10) takes the value of A2, then starting at the position of 2, provides the remainder. The position of 2 is calculated by taking the length of the result of the first formula. We use the length to allow for triple digit stems. If we entered the number 2, rather than calculating length, in a number such as 123, Excel would return 23, rather than the 3, which is the true leaf.
The formula for the location =COUNTIF($A$2:$A24,"<"&A2)COUNTIF($A$2:$A$24,"<"&INT(A2D2))+COUNTIF(A$2:A2,A2) first finds how may numbers in the dataset are less than A2, then subtracts that from how many are less than the stem multiplied by 10. Last, it adds the number of times the value of A2 exists. This accounts for duplicate numbers.
With all of the calculations complete, we use the Excel Pivot Chart function to group the stems together in rows, and then list each corresponding leaf in the correct position.
There's more...
The stem and leaf plot is similar to a histogram with regards to how the data is displayed; however, the stem and leaf plot provides the ability to retain the original values. Rather than seeing frequency with a bar, you are able to determine not only the frequency, but also what values make up that frequency.
Box and whisker plot
A box and whisker plot is a common graphical summary function for viewing five separate data values in a single graph image. Five number summaries are invaluable methods for displaying useful information about a large set of data. In financial analysis, the box and whisker plot will display the average, minimum, maximum, and upper and lower quartiles of financial transactions. Having this information will allow a quick estimate of financial strength. Despite its usefulness, the box and whisker plot is not a function or graph type that is native to Excel.
In this recipe, you will create a box and whisker plot from the fivenumber summary of annual transactions for a retail location over a 3year period.
How to do it...
We will start by utilizing a fivenumber summary and corresponding labels:
 Select cells A1 through F4.
 From the Excel ribbon toolbar, choose Insert  Charts  Other Charts:
 In the Other Charts dropdown, choose Stock  Volume – Open – High – Low – Close:
 In the resulting graph, select the blue bar that appears below the first box in the graph:
 Within the Design tab of the Excel Ribbon, select Change Chart Type:
 In the choose chart window, select the Line with Markers chart type, and then select OK:
 From the Excel ribbon, choose Layout  Axes  Secondary Vertical Axis, and select None:
 Click on the line you just created within the box graph, right click, and choose Format Data Series:
 In the formatting window, choose Marker, and change the type to Builtin, line type, with a size of 10:
 Under Marker Fill, change the fill to Solid fill:
 Lastly, change the Line Color to No line, and click on Close:
 On the graph, select one of the boxes, and under Format fill, change the fill type to No Fill:
The box and whisker plot is now complete. Within the plot, you can easily determine the median transactions throughout a given year, as well as determine the consistent averages, despite the upper and lower thresholds.
How it works...
The box and whisker plot displays the upper and lower quartiles within the box. The whiskers extend to the maximum and minimum prices on either end of the quartiles. Finally, the median is displayed within the box providing the graphical representation of price fluctuations within the median.
There's more...
When selecting the stock graph used in this recipe, if the data is not organized exactly as shown above, Excel will display an information box explaining how the data must be formatted. You will need to reformat the data exactly as shown and in the same order for Excel to allow you to proceed in graph creation:
Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook 
Read more about this book 
(For more resources on this subject, see here.)
Creating a graph overlay for profit and expenses
Often, when displaying information in Excel, one type of graph or chart used exclusively does not always show the full picture. When charting profit and expenses, a column chart will assist in displaying comparison between years, while a line chart will assist in displaying trends. Taking this onestep further, overlaying charts will allow both representations to be displayed within the same graph. Like many other financial graphing requirements, Excel does not have a Chart Overlay or Graph Merge wizard or builtin function for accomplishing this.
In this recipe, you will learn how to use native functions to create a chart overlay.
How to do it...
We will start with a simple worksheet displaying profit and expenses for one year with a monthly frequency:
 Select cells A3 through C15.
 From the Excel Ribbon choose Insert  Column  Clustered Column.
Excel now inserts a basic column chart onto the worksheet with a colored bar for each data set of profit and expenses:
 Select any of the columns for Expenses.
 Once selected, from the Ribbon Chart Tools Group, choose Design  Change Chart Type:
 From the Change Chart Type window, choose a Line chart, and click on OK:
Excel now converts Expenses into a line along the previous data points, creating an overlay of chart types:
How it works...
While Excel does not provide an actual function for performing overlays or merging two charts, utilizing a chart change within a data series, we can achieve a similar result.
In this recipe, we modified the chart type for only the Expenses data series to be represented as a line while maintaining the columns for profit. This provides a much more visually effective method of displaying the difference between profit and expense.
This overlay provides graphical analysis to show that while the profit increased throughout the year, expenses actually maintained a steady decline.
There's more...
We could add extra data series to this chart and continue to modify the chart type to increase the chart overlays.
Even more...
In this recipe, the data points fell within a shared series, allowing the two charts to share the same Yaxis; however, you can still graph and overlay charts with differing points and a nonshared Yaxis.
Graphing the principal of a loan automatically
Loans are an essential portion of finance. When calculating a loan it is often beneficial to graph portions of the loan to demonstrate how those aspects may be affected by the loan terms. Loans are not calculated easily without the assistance of a calculator, and principal is not a consistent number. To create a graph based on the loan principle accurately, it is necessary to first create an amortization schedule, and calculate interest, and so on. This process can be quite time consuming and requires recalculation when experimenting with loan parameters.
In this recipe, you will learn to use the VBA code to build an automatic amortization schedule and loan principal graph that can be used multiple times to adjust loan parameters.
Getting ready
For this recipe, we will use an already created UserForm with input for principal, interest, and term. Calculating loan terms in Excel is not covered in this recipie.
How to do it...
We will start by opening the Visual Basic Editor (VBE) to code manipulation:
 Within Excel, press Alt + F11 on your keyboard. Once in the VBE, double click on the frmLoan UserForm to open the UserForm within the code window:
 Using the UserForm toolbox, select the CommandButton tool, and draw a button on the bottom of the Loan Calculator form, just to the left of the Calculate button:
 With the new button still selected, in the Properties window to the lower lefthand side panel of the VBE, change the Caption property to Graph This:
 Double click on the CommandButton labeled Graph This.
Once doubleclicked, Excel will open the code editor and add the code for the button click event. Within this code, we will be utilizing specific sheet names such as sheet1; it is important that we are truly using sheet1 to prevent errors in Excel.
 Enter the following code:
Private Sub CommandButton1_Click()
Dim x As Integer
Dim y As Double
Dim z As Integer
x = 0
y = txtTerm.Value + 1
z = 4
Sheets("Sheet1").Activate
Range("A1").Select
ActiveCell.Value = txtPrincipal.Value
Range("A2").Select
ActiveCell.Value = txtTerm.Value
Range("A3").Select
ActiveCell.Value = txtInterest.Value
Do Until x = y
Range("A" & z).Select
ActiveCell.FormulaR1C1 = x
x = x + 1
z = z + 1
Loop
Range("B4").Select
ActiveCell.FormulaR1C1 = "=PMT(R3C[1]/R2C[1],R[2]C[
1],R[3]C[1])"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=IPMT(R3C1/R2C1,RC1,R2C1,R1C1)"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=PPMT(R3C1/R2C1,RC1,R2C1,R1C1)"
Range("D4").Select
ActiveCell.FormulaR1C1 = txtPrincipal.Value
Range("D5").Select
ActiveCell.FormulaR1C1 = "=R[1]C4+RC3"
Range("B5:D5").Select
Selection.AutoFill Destination:=Range("B5:D6"),
Type:=xlFillDefault
Range("B5:D6").Select
Range("A5:D6").Select
Selection.AutoFill Destination:=Range("A5:D76"),
Type:=xlFillDefault
Range("A5:D76").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet1").
Range("A:A").Range("D:D")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub  The aboveentered code should be formatted as follows, within the VBE code window:
 Save your work, and close the VBE.
 While in Excel, from the Ribbon, choose View  Macros  View Macros. From the Macro window, choose the Calculate_Loan macro, and choose Run:
The Loan Calculator now displays to the user. Once all of the loan parameters have been entered, the user can click on Calculate to see the loan payment:
If the payment is acceptable, the user can click on Graph This. Upon clicking the button, Excel builds an amortization schedule on Sheet1, and creates the loan principal graph:
Summary
In this article we how to use graphs for representation of financial and statistical analysis. In the next article we will cover Adding animations to Excel graphs.
Further resources on this subject:
 Excel 2010 Financials: Adding Animations to Excel Graphs [Article]
 Load Testing Using Visual Studio 2008 [Article]
 How to Manage Content in a List in Microsoft Sharepoint [Article]
 Sage ACT! 2011: Creating a Quick Report [Article]
 Tips and Tricks: Report Page in IBM Cognos 8 Report Studio [Article]
About the Author :
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, ecommerce, and content management.
Books From Packt


Post new comment