Document Sample

```					                                                 Spreadsheets

Lab 4

1.    Open a new Excel spreadsheet document. First, we will add some data to it. Copy and paste the data in
the table below into your spreadsheet. Save the file as lab4 on your H drive in lab.

Assume that you have just been given a part-time job at USC. You will receive \$405.00 on the 1st and
the 15th of each month, starting February 1. You have also decided to move into an apartment with
your friends. Your share of the rent is \$300.00 due on the first day of each month. Make up data to
continue the spreadsheet for three more months. Remember to include your paycheck and regular
expenses such as your share of the rent, electric bill (SCEG), cell phone bill (AT&T), gas (Shell), food,
books, clothes, entertainment, etc. Include at least eight entries for each of the additional months.
Remember to save the file frequently.

Year         Month     Day      Check #    Destination/   Explanation        Withdrawal      Deposit    Balance
Origin
Starting                                      \$837.50
Balance
2010        2         4                Uncle David    Graduation                         \$200.00
2010        2         6                Aunt Jane      Graduation                         \$25.00
2010        2         9       406      Foot Locker    Tennis shoes       \$72.33
2010        2        12       407      Office Dpt     Supplies           \$48.98
2010        2        15       408      SCEG           Utilities          \$146.52
2010        2        20       409      AT&T           Phone              \$74.29
2010        2        21      ATM       Shell          Gas                \$35.37
2010        2        22      ATM       Cash           Atlanta trip       \$150.00
2010        2        24                Mom            For textbooks                      \$300.00
2010        2        25       410      Target         Alarm clock        \$15.42
2010        2        30      ATM       Cash                              \$100.00

a)    Calculate the balance for each entry. Enter the formula in the cell under the initial balance to
calculate the balance of the first entry. Since empty cells will be treated as zero, your balance
formula can simply add the deposit and subtract the withdrawal from the previous balance. Use the
automatic fill handle technique to apply the formula to all the rest of the cells in the balance
column. It is easy to enter a formula incorrectly, so always spot check your answers.

b) Making corrections: You just remembered that you did not enter an ATM withdrawal of \$50.00 on
02/14/2010. Also, the money that your mom gave you for the textbooks was deposited on the
02/18/2010, not 02/24/2010, so that entry needs to be moved up and the date changed. Make these
corrections. Update the balance column as necessary.

c)    For each month calculate the maximum balance, the minimum balance, the average balance, total
expenses, and the total amount deposited. Use the MAX, MIN, AVERAGE, and SUM functions.

d) You are thinking of buying a netbook to use for schoolwork now that you have a job. Use the IF
function to determine whether you can afford the netbook based on the first three months of your
checkbook activity. If your minimum balance over the first three months does not drop below
\$400, output a message which indicates that you can afford the netbook. If not, output a message
which indicates that you cannot afford the netbook now.
e)   You want to monitor your cell phone expenses. Add a filter for the category Destination/Origin.
Select “AT&T” as the filter criterion to view only your cell phone expenses over the three months.
Leave the data in filtered form when you submit your file.