Excel Charting for Cash Receipts and Disbursements PDF

g g p Learning Objective Use a spreadsheet application to chart cash receipts and cash disbursements for a given month. Lesson Intr...

g g p Learning Objective Use a spreadsheet application to chart cash receipts and cash disbursements for a given month. Lesson Introduction Now that you have learned how to create a cash budget, let’s apply this knowledge to the real world. Cash budgeting is used not only to estimate the cash balance each month but also to analyze any patterns in cash flows. Understanding these patterns allows a business to negotiate short-term loan terms with financial institutions and to build a strategy for managing cash flows. In this lesson, you are going to learn the basic Excel environment for creating charts and learn how to create column charts in Excel to manage cash receipts and disbursements. The Excel Environment for Working with Charts The Excel menu scheme is dynamic. You can customize the menus to fit your needs and your personal work habits. The menu schemes also change depending on the tasks that you perform. This is certainly true of the charting menu options—most of these options only appear when you work with charts. Figure 4.1: Inserting a Chart in Excel To access these menu items, you must first insert a chart into your spreadsheet. To insert a chart into a worksheet, click on the Insert menu tab. You will notice that there are a number of different types of charts that you can add to a worksheet. At this point, any of the chart options will suffice to demonstrate different chart options available in Excel. To add a chart, click on the icon that represents the type of chart you wish to insert and select the specific chart layout you wish to use (see previous figure). Once you have added a chart to your workbook, the “Chart Tools” menu items appear (see the following figure). The “Chart Tools” menu items are available when a chart is selected or highlighted in your worksheet. If you click away from the chart to select any other element of the worksheet (for example, a cell), you will notice that the “Chart Tools” menu items disappear. They reappear when a chart is selected. Figure 4.2: Charting Menu Options There are two menu tabs associated with the “Chart Tools” menu scheme: “Design” and “Format.” The menu items in both of these groups contain all the tools you will need to design a chart from scratch or to modify a chart. Design The “Design” tab of the “Chart Tools” menu scheme contains the menu items used to make high-level manipulations to your chart. The next figure depicts the menu items in the “Design” tab. The “Design” tab contains the items you would use to change the chart type, select or modify the data depicted in the chart, select from various templates the layout and color scheme of the chart, and select the location of the chart in the workbook. Since you will use most of these items to create and modify charts, a detailed discussion of these elements will occur later in the tutorial. Figure 4.3: “Design” Tab of the Charting Menu Options Within the “Design” tab, the “Add Chart Element” icon allows you to change important detail elements of your chart, such as titles, legends, data labels, and axes. The following figure highlights the various chart options that are available in this menu scheme. Figure 4.4: “Add Chart Elements” Menu Options The “Axes” group of menu items is used to format the appearance of each of the axes in your chart. You can choose how to display the units for each axis and the intercept for each axis. The “Chart Title” and “Axis Titles” menu items allow you to change where and how each of the titles in your chart will appear. You can also use these menu items to remove a title from your chart. Likewise, the “Legend” menu item allows you to change the positioning and the display options for the chart legend. Other menu items allow you to control more detailed elements of your charts or perform and display statistical analyses. For example, you can use “Data Labels” and “Data Table” menu items to display the numeric values for the various elements of your chart, and you can add a  trendline to demonstrate statistical relationships in your data. Formatting The “Format” menu items are used to modify the colors and textual elements of your charts. The figure below depicts the menu items available in the “Format” tab of the “Chart Tools” menu options. While these elements can make your charts more professional looking, you will not address these options in this lesson. Instead, you will focus on how to build and modify charts. Figure 4.5: “Format” Tab of the Charting Menu Options Working with Column Charts Assume that you are preparing a monthly cash budget for Timmins Company from the previous lesson. You also want to provide a few column charts for Timmins Company to analyze its cash receipts and disbursements. In order to create column charts, you first need to have the data in a worksheet. Download the data below to follow along as we create a column chart. Timmins Company Cash Budget Download Material The following is a screenshot of the cash budget for Timmins Company prepared in a worksheet:  Figure 4.6: 20X2 Cash Budget for Timmins Company Column charts are used to compare different categories of items or the same category over time. Column charts depict the types of items (or different times) across the horizontal axis and the values for each category as a column rising into the vertical axis. The figure below is an example of a column chart that compares the total cash collections for Timmins Company based on the cash budget.  Figure 4.7: Timmins Company Total Cash Collections Insert the chart into the worksheet by selecting a 2-D column chart from the “Column” menu item in the “Charts” group of the “Insert” menu tab (see figure below). While the chart used in this lesson is a two-dimensional column chart, choosing any of the other column chart options would achieve a similar result. Notice that you can select from two-dimensional and three-dimensional options as well as two-dimensional and three-dimensional bar charts. Figure 4.8: Inserting a Two-Dimensional Column Chart It is now time to select the data for the chart. In most cases, Excel will then populate your chart when it is inserted into the worksheet. While this lesson demonstrates the processes involved with specifying each element of your chart, you will likely find that if you highlight the right set of data in your worksheet before you insert your chart, Excel will automatically create a chart that is very close to your desired result. Here, assume that the correct data has not already been added to the chart. In this case, you must specify the data to be used in the chart. The entire process for selecting the data to be used in the chart is depicted on the following steps. First, you click on “Select Data” in the “Data” group of the “Design” tab of the “Chart Tools” menu options. The “Select Data Source” dialog window will be displayed. Figure 4.9: “Select Data” Menu The “Select Data Source” dialog window is divided into two areas where the “Legend Entries (Series)” and the “Horizontal (Category) Axis Labels” are defined. A data series is a set of data that you want to include on your chart. You may need to remove unwanted series from the “Legend Entries (Series)” area if you preselect the data before inserting a chart.  Figure 4.10: “Select Data Source” Window To create the ending cash balance above, you need to add a data series that includes the ending cash balance for each month in 20X2. Click “Add” in the “Legend Entries (Series)” area to bring up the “Edit Series” dialog window. You can use the dialog window to add the ending cash for each month. The name of this series, “Cash Budget,” can be referenced in cell B31. The values for this series is contained in the range of cells E31:G31. You will click OK to exit the “Edit Series” window and return to the “Select Data Source” window. Figure 4.11: Entering a Data Series Figure 4.12: Ending Cash Balance Cells in the Worksheet Next, you will define the horizontal axis labels to include the month labels stored in the cell range C10:G10. This is done by clicking “Edit” in the “Horizontal (Category) Axis Labels” area and entering the appropriate range in the “Axis Labels” dialog window.  Figure 4.13: “Select Data Source” Window Figure 4.14: Month Labels in the Worksheet Figure 4.15: “Axis Labels” Window After reviewing the data, you have selected for the chart, click OK to exit the “Select Data Source” dialog window. At this point, the appropriate data has been selected for the chart, and it is time to modify the title, legend, and data labels for the chart. Figure 4.16: Review the Chart Data and Click OK  Figure 4.17: Finished Column Chart A chart legend is included to help the user differentiate between the various data series depicted in a chart. Because there is only one data series in the ending cash balance chart, it makes the most sense to remove the legend from the chart. At this point, most of the editing you will need to do can be accomplished by clicking on the large plus icon outside the chart you created or the “Add Chart Elements” button under the “Chart Design” menu. Both will display the same menu items. Click on the plus icon and uncheck the box next to the “Legend” menu item. This will remove the legend from the chart. Figure 4.18: Remove the Chart Legend The title of the chart should read “Total Cash Collections.” To change the title of the chart, click on the title to select it and then click again to edit the text. It is worth noting that not only are you able to edit the text that is displayed as the title, but you can also modify the size and other font properties using the menu items on the “Home” menu tab in Excel.  Figure 4.19: Changing the Chart Title Finally, you will add data labels to the chart to display the totals for each month near the top of each of the columns in the chart. To do this, click on the “Add Chart Element” menu or the plus icon and then select “Data Labels.” To place the labels right above each column, select the “Outside End” option. Figure 4.20: Selecting Outside End Data Labels You have now created a simple column chart for ending cash balance. It is useful to create such charts for both cash receipts and cash disbursements. These charts help you see any patterns in cash flows and provide a basis for adjusting the operations of a business and minimizing exposure to potential cash shortages. Similarly, you can create a column chart for the company’s total cash disbursements. Click on a cell so that the cursor is no longer selecting the Timmins Company Total Cash Collections chart. Insert a chart into the worksheet by selecting a 2-D column chart from the “Column” menu item in the “Charts” group of the “Insert” menu tab again. Follow the same process as you did for the Total Cash Collection chart, using the appropriate cell references for the cash disbursements data. In the “Edit Series” window, the series name should reference cell B32, and the series values should be E32:G32, which is the data series for total cash disbursements. Figure 4.21: “Edit Series” for Total Cash Disbursements After creating the column chart for cash disbursements, make sure the title of the chart is Timmins Company Total Cash Disbursements. The figure below shows the Timmins Company Total Cash Collections and Total Cash Disbursements charts side by side. Figure 4.22: Timmins Company Cash Collections and Cash Disbursements Charts Notice in the figure above that Timmins Company’s total cash disbursements in January are visually larger than the company’s total cash collections, even though this is not the case numerically. In order for the charts to present cash collections and cash disbursements in a meaningful way for comparison, it is best to display the collections and disbursements right next to each other for each month. To do this, first click on a cell so that the cursor is no longer selecting the Timmins Company Total Cash Collections chart. Insert a new chart into the worksheet by selecting a 2-D column chart from the “Column” menu item in the “Charts” group of the “Insert” menu tab. In the Legend Entries (Series) field in the Select Data Source window, add two series: Total Cash Collections and Total Cash Disbursements.  Figure 4.23: Select Data Source Window with Both Collections and Disbursements After adding the correct information in the Legend Entries (Series) and Horizontal (Category) Axis Labels fields, click OK. The resulting chart should look like the next figure. Figure 4.24: Column Chart of Cash Collections and Disbursements There are a few things missing on this chart: no one but the creator knows what this chart is about because there is no title, and there is no way to tell which color represents collections and which represents disbursements. Title the chart Cash Collections vs. Disbursements and then add data labels to the outside end. In addition, click the “Add Chart Element” button in the “Chart Layouts” menu of “Design” tab. Click “Legend,” and then click “Bottom.”  Figure 4.25: Adding a Legend The legend shows that the blue columns represent total cash collections and the orange columns represent total cash disbursements. Anyone looking at the chart will now be able to interpret what the bars represent. Figure 4.26: Total Cash Collections vs. Disbursements As indicated on the chart, Timmins Company must have positive total cash flows each month because cash collections are higher than cash disbursements. If this is not the case for some reason, an analyst can always go back to the table and analyze why cash collections were higher than cash disbursements for some months. The analyst can then balance out the company’s budget or decide to take out a short-term loan from a bank.  The difference between the bar height of collections and disbursements represents the total cash flows for the month. It may be useful for an analyst to create a column chart like the following one to display the total cash flows for each month. Figure 4.27: Timmins Company Total Cash Flows When to Use Column Charts As mentioned before, column charts are particularly useful when comparing different categories of items or the same category over time. You could also construct a column, chart, for example, to compare revenues or even certain types of expenses over a period of time. This would be an example of comparing different categories of items. Column charts are not particularly useful when comparing a large number of categories. A good rule of thumb would be to limit the number of categories (or time intervals) examined to 10 or less. It bears noting that column charts are very similar to bar charts. A bar chart is essentially a column chart rotated 90 degrees to the right. Everything that you have learned about column charts also applies to bar charts. Lesson Summary Excel can be used to create charts to analyze cash budgeting and provide a basis for adjusting business operations. Column charts compare a limited number of categories or time series, pie charts compare parts of a category to a whole, and line charts compare values of a particular category over time. You learned how to create column charts, edit labels, and change axis titles.