More Rent Spreadsheets

Document Sample
More Rent Spreadsheets Powered By Docstoc

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
                                                           Starting                                      $837.50
     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

2.    Modify your spreadsheet as follows.

      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.

f)   To present your spreadsheet in the final form, select format options (currency, headings, etc.)
     appropriate for each element. Also incorporate elements of formatting like different types of font,
     alignment, borders, and background color for some of the cells.

Shared By:
Description: More Rent Spreadsheets document sample