This section reviews the fundamental skills for entering formulas into an Excel worksheet. The objective used for this chapter is the construction of a personal cash budget. Most financial advisors recommend that all households construct and maintain a personal budget to achieve and maintain strong financial health. Organizing and maintaining a personal budget is a skill you can practice at any point in your life. Whether you are managing your expenses during college or maintaining the finances of a family of four, a personal budget can be a vital tool when making financial decisions. Excel can make managing your money a fun and rewarding exercise.

Figure 2.1 shows the completed workbook that will be demonstrated in this chapter. Notice that this workbook contains four worksheets. The first worksheet, Budget Summary, contains formulas that utilize or reference the data in the other three worksheets. As a result, the Budget Summary worksheet serves as an overview of the data that was entered and calculated in the other three worksheets of the workbook. Sheets two through four are ones we will return to later in the semester with our chapter on Financial functions.

Figure 2.1 Completed Personal Budget Workbook

Creating a Basic Formula

Download Data File: CH2 Data

Formulas are used to calculate a variety of mathematical outputs in Excel and can be used to create virtually any custom calculation required for your objective. Furthermore, when constructing a formula in Excel, you use cell locations that, when added to a formula, become cell references. This means that Excel uses, or references, the number entered into the cell location when calculating a mathematical output. As a result, when the numbers in the cell references are changed, Excel automatically produces a new output. This is what gives Excel the ability to create a variety of what-if scenarios, which will be explained later in the chapter.

To demonstrate the construction of a basic formula, we will begin working on the Budget Detail worksheet in the Personal Budget workbook, which is shown in Figure 2.2. To complete this worksheet, we will add several formulas and functions. Table 2.1 provides definitions for each of the spend categories listed in the range A3:A11. When you develop a personal budget, these categories are defined on the basis of how you spend your money. It is likely that every person could have different categories or define the same categories differently. Therefore, it is important to review the definitions in Table 2.1 to understand how we are defining these categories before proceeding.

Table 2.1 Spend Category Definitions

The first formula that we will add to the Budget Detail worksheet will calculate the Monthly Spend values. The formula will be constructed so that it takes the values in the Annual Spend column and divides them by 12. This will show how much money will be spent per month for each of the categories listed in Column A. The following explains how this formula is created:

1. Open the Data file named CH2 Data and use the File/Save As command to save it with the new name CH2 Personal Budget.
2. Click the Budget Detail worksheet tab to open the worksheet.
3. Click cell C3.
4. Type an equal sign =. When the first character entered into a cell location is an equal sign, it signals Excel to perform a calculation or produce a logical output.
5. Type D3. This adds D3 to the formula, which is now a cell reference. Excel will use whatever value is entered into cell D3 to produce an output.
6. Type the slash symbol /. This is the symbol for division in Excel. As shown in Table 2.2 the mathematical operators in Excel are slightly different from those found on a typical calculator.
7. Type the number 12. This divides the value in cell D3 by 12. In this formula, a number, or constant, is used instead of a cell reference because it will not change. In other words, there will always be 12 months in a year.
8. Press the ENTER key.

Table 2.2 Excel Mathematical Operators

Figure 2.3 shows how the formula appears in cell C3 before you press the ENTER key. Figure 2.4 shows the output of the formula after you press the ENTER key. The Monthly Spend for Household Utilities is $250 because the formula is taking the Annual Spend in cell D3 and dividing it by 12. If the value in cell D3 is changed, the formula automatically produces a new output. We are calculating the spend per month for each category because people often get paid and are billed for these items on a monthly basis. This formula allows you to compare your monthly income to your monthly bills to determine whether you have enough income to pay these expenses.

Relative References (Copying and Pasting Formulas)

Once a formula is typed into a worksheet, it can be copied and pasted to other cell locations. For example, Figure 2.4 shows the output of the formula that was entered into cell C3. However, this calculation needs to be performed for the rest of the cell locations in Column C. Since we used the D3 cell reference in the formula, Excel automatically adjusts that cell reference when the formula is copied and pasted into the rest of the cell locations in the column. This is called relative referencing and is demonstrated as follows:

1. Click cell C3.
2. Place the mouse pointer over the Auto Fill Handle.
3. When the mouse pointer turns from a white block plus sign to a black plus sign, click and drag down to cell C11. This pastes the formula into the range C4:C11.
4. Double click cell C6. Notice that the cell reference in the formula is automatically changed to D6.
5. Press the ENTER key.

Figure 2.5 shows the outputs added to the rest of the cell locations in the Monthly Spend column. For each row, the formula takes the value in the Annual Spend column and divides it by 12. You will also see that cell D6 has been double-clicked to show the formula. Notice that Excel automatically changed the original cell reference of D3 to D6. This is the result of relative referencing, which means Excel automatically adjusts a cell reference relative to its original location when it is pasted into new cell locations. In this example, the formula was pasted into eight cell locations below the original cell location. As a result, Excel increased the row number of the original cell reference by a value of one for each row it was pasted into.