Statistical Functions in Microsoft Excel

Sorting Data (Multiple Levels)

Data file: Continue with CH2 Personal Budget.

The Budget Detail worksheet shown in Figure 16 now produces several mathematical outputs through formulas and functions. The outputs allow you to analyze the details and identify trends regarding budgeting and spending. Before drawing conclusions from this worksheet, we will sort the data based on the Percent of Total column. Sorting is a powerful tool that enables you to analyze key trends in any data set. Sorting will be covered thoroughly in a later chapter but is briefly introduced here. For the Budget Detail worksheet, we want to set multiple levels for the sort order. This is accomplished through the following steps:

1. Highlight the range A2:F11 in the Budget Detail worksheet
   
   
2. Click the Data tab in the Ribbon
   
   
3. Click the Sort button in the Sort & Filter group of commands; this opens the Sort dialog box, as shown in Figure 17
   
   
4. Click the down arrow next to the "Sort by" box
   
   
5. Click the Percent of Total option from the drop-down list
   
   
6. Click the down arrow next to the sort Order box
   
   
7. Click the Largest to Smallest option
   
   
8. Click the Add Level button; this allows you to set a second level for any duplicate values in the Percent of Total column
   
   
9. Click the down arrow next to the "Then by" box
   
   
10. Select the LY Spend option. Leave the Sort Order as Smallest to Largest
    
    
11. Click the OK button at the bottom of the Sort dialog box
    
    
12. Save the Ch2 Personal Budget file
    
    

Figure 17 Sort Dialog Box

Figure 18 shows the Budget Detail worksheet after it has been sorted. Notice that there are three identical values in the Percent of Total column. This is why a second sort level had to be created for this worksheet. The second sort level arranges the values of 8.4% based on the values in the LY Spend column in ascending order. Excel allows you to set as many sort levels as necessary for the data in a worksheet.

Figure 18 Budget Detail Worksheet after Sorting

Now that the Budget Detail worksheet is sorted, a few key trends can be easily identified. The worksheet clearly shows that the top three categories as a percentage of total budgeted spending for the year are Taxes, Household Utilities, and Food. All three categories are necessities (or realities) of life and typically require a significant amount of income for most households.

Looking at the Percent Change column, we can see how our planned spending is expected to change from last year. This is perhaps the most important column on the worksheet because it allows you to assess whether your plan is realistic. You will see that there are no changes planned for Taxes and Household Utilities.

While Taxes can change from year to year, it is not too difficult to predict what they will be. In this case, we assume that there are no changes to the tax costs for our budget. We are also planning no change in Household Utilities. These costs can fluctuate from year to year as well. However, you can reduce costs, such as using less electricity, turning off heat when no one is in the house, keeping track of your wireless minutes so you do not exceed the maximum allowed in your plan, and so on. As a result, there is no change in planned spending for Household Utilities because we will assume that any rate increases will be offset by a decrease in usage.

The third item that is not planned to change is insurance. Insurance policies for cars and homes can change, but as is true for taxes, the changes are predictable. Therefore, we are assuming no changes in our insurance policy.

The first big change noticeable in the worksheet is the Food and Entertainment categories in rows 5 and 6 (see definitions in Table 1). The Percent Change column indicates an 11.1% decrease in Entertainment spending and an 11.1% increase in Food spending. This is logical because if you plan to eat in restaurants less frequently, you will be eating at home more frequently. Although this makes sense in theory, it may be hard to do in practice. Dinners and parties with friends may be tough to turn down. However, the entire process of maintaining a budget is based on discipline, and it certainly takes a significant amount of discipline to plan targets for yourself and stick to them.

A few other points to note are the changes in the Gasoline and Vacation categories. If you commute to school or work, the gas price can significantly impact your budget. It is important to be realistic if gas prices are increasing, and you should reflect these increases in your budget. To compensate for the increased spending on gas, the spending plan for vacations has been reduced by 25%. Budgeting often requires a certain degree of creativity. Although the Vacation budget has been reduced, there is still money you can set aside to make plans for spring break or winter break.

Finally, the budget shows a 19.8% decrease in Miscellaneous spending. This was defined as a group containing several expenses, such as textbooks, school supplies, software updates, and so on (see Table 1). You may be able to reduce your spending in this category if you can use items such as online textbooks. This reduction in spending can free up funds for Clothes, a spending category that has increased by 20%.