Document Sample
excelexam Powered By Docstoc
					                                         BCIS1305 – BUSINESS APPLICATIONS
                                                       EXCEL TEST
A friend has asked you to move to Dallas for the summer. Your friend tells you that you will be able to find a summer job,
and that the two of you can share an apartment. After several trips to Dallas to look for work, you find that the best job you
will be able to get is with the ACME Brick Company at a gross salary of $2850 per month for June, July, and August. You
plan to return to Kilgore College next fall.
The problem statement is: Can you afford to move to Dallas for the summer and how much money will you be able to
save for college during the summer?
Design a spreadsheet to show how much money you will have at the end of the summer.
1.      The top part of the spreadsheet should show your gross income, taxes, retirement, and net income for each
        month - June, July, and August - and the totals for the entire summer. The middle portion should show monthly
        and one time expenses for each month. The figure below gives a partial view of how the spreadsheet might look.
        You should have totals for rows and columns.

                 xxxxxxxxx              xxxxxxxx xxxxxxxxx
                 xxxxxxxxx              xxxxxxxx xxxxxxxxx
                 xxxxxxxxx              xxxxxxxx xxxxxxxxx
                 Net Income

                 xxxxxxxx               xxxxxxxx   xxxxxxxx
                 xxxxxxxx               xxxxxxxx   xxxxxxxx
                 xxxxxxxx               xxxxxxxx   xxxxxxxx
                 xxxxxxxx               xxxxxxxx   xxxxxxxx

                 TOT. Exp
                 xxxxxxxx               xxxxxxxx xxxxxxxx

                 Total left over
2.      Expense information to set up your summer budget (all monthly charges unless stated otherwise).
        a) Gross income 2850.00 monthly
        b) rent expense $600 per month.
        c) Food $300.00
        d) Gasoline $200.00
        e) Entertainment $150.00
        f)   Clothing $100.00
        g) Telephone $35.00
        h) Health Club dues $75.00
        i)   Car Payment $475.00
        j)   income tax is 18% of gross income (Calculation: tax = .18 * gross pay)
        k) social security retirement is 7% of gross income.
        l)   The net income is the gross income minus the income tax and retirement.
        m) Your share of the one-time apartment deposit is $450 (all deposits are paid in June and are non-refundable)
        n) Your share of the one-time telephone deposit is $120.
        o) auto insurance $425 (One time charge paid in July).
3.    The final row of the spreadsheet must have only the amount left over at the end of the summer (put in totals
      column). Conditionally format this ONE cell as follows:
      a)       If cell is less than 0, use the light red fill with dark red text option
      b)       if cell is greater than 0, use the light green fill with dark green text option
4.    In the same row, but one column to the right of this final value (from step 3 above), use an IF function to display
      “Yes! You can!” if value is positive, or “No! You can’t” if value is negative.
5.    Fit the entire spreadsheet on the screen so that it will print on a single sheet of paper.
6.    Format the spreadsheet as follows:
          Apply the “Trek” theme.
          Column headings should be centered.
          Have all numbers (except dates) formatted to show 2 decimal places and appropriate commas. Dollar signs
           are to be used ONLY if it is in a Totals row or column.
          Give the spreadsheet an appropriate title, centered.
          Use cell references (cell addresses) with formulas and functions. Have all calculations done by the
           spreadsheet program.
          Left justify your name, section, and date in the 1st three rows of the spreadsheet, not in a header.
7.    Save the spreadsheet under the name: SSONE. HIGHLY recommend putting into a separate folder in your
      personal folder.
           o   Print a copy of the spreadsheet and a second displaying the formulas.
           o The spreadsheet must be in portrait orientation.
           o The formula printout must be in landscape orientation-USE FIT-TO-ONE PAGE, if necessary.

8.    Save the SSONE as SSTWO. You should now have the original spreadsheet saved on disk and a copy
      SSTWO. We will make some changes to this spreadsheet (SSTWO) and do some "WHAT IF" analysis.
      Note: This has nothing to do with the IF function. Just make the following changes to the first
9.    Suppose a third friend moves in with you and your roommate. This would change your portion of the expenses.
      Make the following changes:
          Rent $400
          apartment deposit $300
          telephone deposit $80.00.
          Food budget $200
10.   Print SSTWO
          Print the spreadsheet in portrait orientation.
          Print in formula view in landscape view
11.   Hand in:
          test instruction sheets (hand in separately)
          Print out of Spreadsheet SSONE
          Print out of Spreadsheet SSONE Formulas
          Print out of Spreadsheet SSTWO
          Print out of Spreadsheet SSTWO Formulas
          Staple the 4 sheets together – in order.
12.   Zip both spreadsheets into one zip file – use your first initial and last name for the filename. Email the zip file to .

Shared By: