Microsoft Office Tutorials and References

In Depth Information

**Entering Data and Formulas with AutoFill**

Entering Data and Formulas with AutoFill

Diane and Glenn hope to purchase a home in the next three years. Currently, they have

$4,000 in their main savings account, and they plan to open a second account reserved

for saving toward a down payment on a home. You will enter the initial balance for the

main account and set up the home account with an initial balance of $0 into the Budget

worksheet.

To enter the savings account information:

◗

1.
If you took a break after the previous session, make sure the Drake Family Budget

workbook is open and the Budget worksheet is active.

◗

2.
In cell K5, enter
4,000
(the starting amount in the main savings account).

◗

3.
In cell K6, enter
(the starting amount in the home savings account).
0

◗

4.
In cell K7, enter the formula
=K5+K6
to sum the total initial amount in both

accounts. See Figure 3-18.

Figure 3-18

Savings account information

initial
savings

savings will be

divided into a

Main account and

a Home account

Diane wants to learn how much the couple could add to their savings accounts each

month. To ﬁ nd out, you must ﬁ rst determine the couple’s monthly net cash ﬂ ow, which

is equal to the amount of money they earn each month after paying all of their expenses.

You will start by formatting the cells where you’ll enter this data and calculating the net

cash ﬂ ow during the month of January.

To calculate the net cash flow for January:

◗

1.
Merge and center the range
A33:B33
, and then right-align the cell contents.

◗

2.
In the merged cell A33, enter
Net Cash Flow
.

◗

3.
In cell C33, enter the formula
=C21–C32
. This formula subtracts total expenses

from total income for the month of January. The resulting −2,060 indicates a

projected shortfall of $2,060 for the month of January.

◗

4.
Apply conditional formatting to cell C33 to highlight the cell with a red fill and

red text if the value in the cell is less than 0. Months with negative cash flow will

be highlighted on the worksheet. See Figure 3-19.